Flettede celler er problemet i dag, men disse er lodrette flettede celler. Se på nedenstående figur. Hvis der er 7 rækker for Midtvest-regionen, vises ordet Midtvest kun på den første række. Nogen fusionerer derefter de resterende seks blanke celler med Midwest-cellen for at oprette en fusioneret celle, der er 7 celler høj.
Dette er en forfærdelig måde at sende data ud. Hvis nogen er dygtige i Excel, vil de måske sortere dataene, tilføje subtotaler, bruge pivottabeller. Flettede celler forhindrer dette.
For at gøre tingene værre får du en ny fil fra hovedkvarteret hver dag struktureret som denne. HQ sender lignende filer ud til 500 filialer. At være bare en lille forpost, har du ikke meget chance for at overbevise hovedkvarteret om, at dette er en forfærdelig måde at sende data ud.
Dagens artikel handler om, hvordan man hurtigt løser dette problem med Power Query. Power Query-løsningen vil være lettere på dag 2 til 9999 end følgende trin i Excel:
Excel-guruer foreslår måske disse trin hver dag:
- Vælg alle celler ved at vælge rektanglet over og til venstre for A1.
- Klik på dialogstarteren i nederste højre hjørne af justeringsgruppen på fanen Hjem.
- Klik på feltet Flet celler to gange for at fjerne markeringen af det.
- Klik på OK for at lukke dialogboksen Formatér celler
- Vælg fra slutningen af kolonne A tilbage til A1.
- Hjem, Find & Select, Gå til Special, Blanks, OK
- Type = Op-pil. Tryk på Ctrl + Enter
- Vælg fra slutningen af kolonne A tilbage til A1.
- Ctrl + C for at kopiere. Højreklik og indsæt værdier
Men der er en meget lettere måde. De nye Power Query-værktøjer er indbygget i Windows-versioner af Office 365 og Excel 2016. Hvis du har en Windows-version af Excel 2010 eller Excel 2013, kan du downloade Power Query gratis fra Microsoft.
Her er strategien med Power Query:
- Lav en plan om, at du vil gemme hver dags projektmappe fra hovedkvarteret i samme mappe med samme navn.
- Åbn en ny tom projektmappe, der indeholder de faste data fra HQ.
-
I den tomme projektmappe skal du vælge Data, Hent data, Fra fil, Fra projektmappe.
Start Power Query-processen - Gennemse mappen og filen fra trin 1.
-
Når Power Query-vinduet åbnes, skal du bemærke, at de flettede celler er væk. Du har Midtvesten i række 1 og derefter seks celler, der indeholder "null". Vælg denne kolonne ved at klikke på Regionoverskriften.
Power Query fjerner automatisk de flettede celler -
I Power Query skal du vælge fanen Transform. Vælg Udfyld, Ned. Ordet Midtvesten kopieres fra række 1 til række 2 til 7. Lignende tranformationer sker i hele datasættet.
Udfyld nulcellerne med værdien ovenfra -
Hjem, luk og indlæs. Power Query lukkes. På cirka 10-20 sekunder vises de rensede data fra HQ i et nyt regneark i projektmappen.
Returner dataene til Excel - Gem projektmappen med et navn som CleanedDataFromHQ.xlsx.
- Når data er valgt, skal du se panelet Forespørgsler og forbindelser på højre side. Hvis du ikke kan se panelet, skal du gå til Data, forespørgsler og forbindelser.
-
Højreklik på forespørgslen i panelet Forespørgsler og forbindelser. Vælg Egenskaber.
Åbn egenskaberne for denne forespørgsel -
Vælg "Opdater data, når filen åbnes". Klik på OK.
Indstil forespørgslen til at køre, hver gang du åbner projektmappen.
Din arbejdsgang bliver derefter: (a) Modtag projektmappen fra HQ i e-mail. (b) Gem vedhæftet fil i den korrekte mappe med det rigtige navn. (c) Åbn projektmappen CleanedDataFromHQ.xlsx. De nye data indlæses i projektmappen.
Se video
Videoudskrift
Lær Excel fra Podcast, afsnit 2221: Fyld sammenflettede celler ned.
Hej, velkommen tilbage til netcast. Jeg er Bill Jelen, jeg var i Calumet City, Elizabeth dukker op og siger, at hun får denne fil - vores fil, som denne fil - hver dag, og det er forfærdeligt. Herovre i kolonne A er det ikke kun, at de satte Midtvesten og efterlod en masse tomme emner, det er en sammensmeltet celle. I orden? De sender - HQ sender disse filer ud til steder rundt om i verden med disse freaking flettede celler herover. Flettede celler er onde.
Okay, her er hvordan man kan slippe af med dette. For det første skal jeg højreklikke, flytte eller kopiere, oprette en kopi og flytte den til den nye bog. Så vi foregiver, at det er som den projektmappe, som Elizabeth får. Fil, gem som, og hver gang Elizabeth får en af disse arbejdsbøger, vil jeg have hende til at placere den nøjagtigt på samme sted med det samme navn. I orden? Præcis samme navn - perfekt. Så nu har vi bare den eneste fil, det ene ark, jeg arkiverer tæt. Og så skal vi oprette en helt ny projektmappe. Jeg vil bare indsætte et nyt regneark her, og dette nye regneark hedder rapporten. Og på dette blanke vil vores rapport udføre data, hente data, fra fil, fra en projektmappe, pege på den fil, som vi skal gemme den i - så med det nøjagtige samme navn hver eneste gang - klik på Importer , vælg det første regneark,og klik derefter på Rediger. I orden. Først og fremmest er dette freaking fantastisk - magtforespørgsel vil ikke have noget af dette flette celle nonsens, de konverterer øjeblikkeligt flette celler til individuelle celler, og alle disse celler er nul. Vælg den kolonne, den er allerede valgt her, og derefter Transformer, Udfyld, Udfyld sådan, og derefter Hjem, Luk og indlæs. Okay, så der er min projektmappe.
Nu ser jeg nogle har nogle ekstra kolonner herude. Lad mig højreklikke, så redigerer vi.
Og det ser ud til, at alt til højre for omkostningerne skal gå væk. Hvor meget ekstra ting derude? Højreklik og fjern disse kolonner - perfekt - Luk og indlæs. Okay, nu bliver arbejdsstrømmen, hver gang Elizabeth får en ny rapport fra hovedkvarteret, vil hun gemme den på det samme sted med pålideligt navn eller hvad som helst - uanset hvad - vi kalder det og derefter åbne denne projektmappe, og Klik herpå Opdater.
Eller hvis vi ønskede at arbejde automatisk, skal du højreklikke her, gå ned til Egenskaber lige uden for din skærm helt i bunden og derefter sige: Hver gang jeg åbner denne fil, skal du opdatere dataene. Så hver gang jeg åbner denne projektmappe, går den ud til den nye projektmappe, vi har fået fra hovedkvarteret, den erstatter de tomme flettede celler med de værdier, der skal være der, og livet er sådan godt.
Power Query er dækket af min bog, men det er også dækket af denne bog af Ken Puls og Miguel Escobar, M er for (DATA) ABE.
Okay, så Elizabeth: Hvordan slipper jeg af lodrette flette celler? Mit hovedkvarter sender mig disse filer hver dag. Så planen: Vi skal gemme projektmappen på et pålideligt sted med et pålideligt navn; Opret en tom rapporteringsarbejdsbog, Data, Hent data, Fra fil, Fra projektmappe, angiv arket, BAM! Fletningscellerne er væk. Jeg vælger den kolonne og udfylder, lukker og indlæser; derefter hver gang du får en ny projektmappe, skal du gemme den nye projektmappe på det pålidelige sted med det samme pålidelige navn; Jeg åbner rapporteringsarbejdsmappen og opdater, eller vi indstiller rapporteringsarbejdsmappen til opdatering automatisk, når den er åben.
For at downloade projektmappen fra dagens video er URL'en i YouTube-beskrivelsen.
Vil du takke Elizabeth for at have været på mit seminar i Calumet City, vil jeg takke dig for at komme forbi. Vi ses næste gang til endnu en netcast fra.
Download Excel-fil
For at downloade excel-filen: fill-merged-cells-down.xlsx
Power Query er et fantastisk værktøj - det giver dig mulighed for at løse en række forskellige
saed Alimohammadi
