Sammenslåtte celler er problemet i dag, men dette er vertikale sammenslåtte celler. Ta en titt på figuren nedenfor. Hvis det er 7 rader for Midtvest-regionen, vises ordet Midtvest bare på første rad. Noen slår deretter sammen de resterende seks blanke cellene med Midtvest-cellen for å lage en sammenslått celle som er 7 celler høy.
Dette er en fryktelig måte å sende ut data på. Hvis noen er dyktige i Excel, vil de kanskje sortere dataene, legge til delsummer, bruke pivottabeller. Sammenslåtte celler forhindrer dette.
For å gjøre saken verre, får du en ny fil fra hovedkvarteret hver dag strukturert slik. HQ sender lignende filer ut til 500 avdelingskontorer. Å være bare en liten utpost, har du ikke mye sjanse til å overbevise hovedkontoret om at dette er en fryktelig måte å sende ut data på.
Dagens artikkel handler om hvordan du raskt kan løse dette problemet med Power Query. Power Query-løsningen vil være enklere på dag 2 til 9999 enn følgende trinn i Excel:
Excel-guruer kan foreslå disse trinnene hver dag:
- Velg alle celler ved å velge rektangelet over og til venstre for A1.
- Klikk på dialogboksen i nedre høyre hjørne av justeringsgruppen i kategorien Hjem.
- Klikk på Merge Cells-boksen to ganger for å fjerne merkingen.
- Klikk OK for å lukke dialogboksen Formater celler
- Velg fra slutten av kolonne A tilbake til A1.
- Hjem, Finn & velg, Gå til Spesial, Tomt, OK
- Type = UpArrow. Trykk Ctrl + Enter
- Velg fra slutten av kolonne A tilbake til A1.
- Ctrl + C for å kopiere. Høyreklikk og lim inn verdier
Men det er en mye enklere måte. De nye Power Query-verktøyene er innebygd i Windows-versjoner av Office 365 og Excel 2016. Hvis du har en Windows-versjon av Excel 2010 eller Excel 2013, kan du laste ned Power Query gratis fra Microsoft.
Her er strategien med Power Query:
- Lag en plan for at du skal lagre hver dags arbeidsbok fra HQ i samme mappe med samme navn.
- Åpne en ny tom arbeidsbok som inneholder de faste dataene fra HQ.
-
Velg Data, Hent data, Fra fil, Fra arbeidsbok i den tomme arbeidsboken.
Start Power Query-prosessen - Bla til mappen og filen fra trinn 1.
-
Når Power Query-vinduet åpnes, legg merke til at de sammenslåtte cellene er borte. Du har Midtvesten i rad 1 og deretter seks celler som inneholder "null". Velg denne kolonnen ved å klikke på Regionoverskriften.
Power Query fjerner automatisk de sammenslåtte cellene -
Velg Power-fanen i Power Query. Velg Fyll, Ned. Ordet Midtvesten er kopiert fra rad 1 til rad 2 til 7. Lignende transformasjoner skjer gjennom datasettet.
Fyll nullcellene med verdien ovenfra -
Hjem, Lukk og last. Power Query lukkes. Om 10-20 sekunder vil de rensede dataene fra HQ vises i et nytt regneark i arbeidsboken.
Returner dataene til Excel - Lagre arbeidsboken med et navn som CleanedDataFromHQ.xlsx.
- Når dataene er valgt, bør du se panelet Spørringer og tilkoblinger på høyre side. Hvis du ikke ser panelet, går du til Data, Queries & Connections.
-
Høyreklikk spørringen i panelet Spørringer og tilkoblinger. Velg Egenskaper.
Åpne Egenskapene for dette spørsmålet -
Velg "Oppdater data når du åpner filen". Klikk OK.
Sett spørringen til å kjøre hver gang du åpner arbeidsboken.
Arbeidsflyten blir da: (a) Motta arbeidsboken fra hovedkontoret på e-post. (b) Lagre vedlegget i riktig mappe med riktig navn. (c) Åpne arbeidsboken CleanedDataFromHQ.xlsx. De nye dataene lastes inn i arbeidsboken.
Se på video
Videoutskrift
Lær Excel fra Podcast, episode 2221: Fyll sammenslåtte celler ned.
Hei, velkommen tilbake til netcast. Jeg er Bill Jelen, jeg var i Calumet City, Elizabeth dukker opp og sier at hun får denne filen - filen vår, som denne filen - hver dag, og den er fryktelig. Her i kolonne A er det ikke bare at de satte Midtvesten og etterlot seg en haug med blanke, det er en sammenslått celle. Ok? De sender - HQ sender disse filene ut til steder rundt om i verden med disse freaking sammenslåtte cellene her borte. Sammenslåtte celler er onde.
Greit, her er hvordan du kan bli kvitt dette. For det første skal jeg høyreklikke, flytte eller kopiere, lage en kopi og flytte den til den nye boka. Så vi later som om det er som arbeidsboken som Elizabeth får. File, Save As, og hver gang Elizabeth får en av disse arbeidsbøkene, vil jeg at hun skal sette den på nøyaktig samme sted med nøyaktig samme navn. Ok? Nøyaktig samme navn - perfekt. Så nå har vi bare den ene filen, det ene arket, jeg skal filen lukkes. Og så skal vi lage en helt ny arbeidsbok. Jeg skal bare sette inn et nytt regneark her, og dette nye regnearket vil hete rapporten. Og når dette er tomt, vil rapporten vår gjøre Data, få data, fra fil, fra en arbeidsbok, peke på filen vi skal lagre den i - så med nøyaktig samme navn hver gang - klikk på Importer , velg det første regnearket,og klikk deretter Rediger. Ok. For det første er dette freaking fantastisk - kraftforespørsel vil ikke ha noe av dette flette celle tullet, de konverterer flette celler umiddelbart til individuelle celler og alle disse cellene er null. Velg den kolonnen, den er allerede valgt her, og deretter Transform, Fill, Fill Down, slik, og deretter Home, Close & Load. Ok, det er arbeidsboken min.
Nå ser jeg at noen har noen ekstra kolonner her ute. La meg høyreklikke, så redigerer vi.
Og det ser ut til at alt til høyre for kostnadene må forsvinne. Hvor mye ekstra ting der ute? Høyreklikk og fjern disse kolonnene - perfekt - Lukk og last. OK, nå blir arbeidsflyten, hver gang Elizabeth får en ny rapport fra hovedkvarteret, vil hun lagre den på det samme stedet med pålitelig navn eller hva som helst - hva vi enn kaller det, og deretter åpne denne arbeidsboken, og Klikk her Oppdater.
Eller hvis vi ønsket å jobbe automatisk, høyreklikker du her, går ned til Egenskaper, rett utenfor skjermen, helt nederst, og deretter sier du: Hver gang jeg åpner denne filen, må du oppdatere dataene. Så hver gang jeg åpner denne arbeidsboken, vil den gå ut til den nye arbeidsboken vi fikk fra hovedkvarteret, den vil erstatte de blanke sammenslåtte cellene med verdiene som burde være der, og livet er flott slik.
Power Query er dekket i boken min, men den er også dekket i denne boka av Ken Puls og Miguel Escobar, M er for (DATA) MONKEY.
Greit, så Elizabeth: Hvordan blir jeg kvitt vertikale flette celler? Hovedkvarteret sender meg disse filene hver dag. Så planen: Vi skal lagre arbeidsboken på et pålitelig sted med et pålitelig navn; lage en tom rapporteringsarbeidsbok, Data, Få data, Fra fil, Fra arbeidsbok, spesifiser arket, BAM! Flettecellene er borte. Jeg velger den kolonnen og Fyll ned, Lukk og last inn; deretter hver gang du får en ny arbeidsbok, lagrer du den nye arbeidsboken på et pålitelig sted med samme pålitelige navn; Jeg åpner rapporteringsarbeidsboken og oppdater, eller vi setter bare rapporteringsarbeidsboken til Oppdater automatisk når den er åpen.
For å laste ned arbeidsboken fra dagens video er URL-en i YouTube-beskrivelsen.
Vil takke Elizabeth for å ha vært på seminaret mitt i Calumet City, jeg vil takke deg for at du var innom. Vi sees neste gang for en ny netcast fra.
Last ned Excel-fil
For å laste ned Excel-filen: fyll-sammenslåtte-celler-ned.xlsx
Power Query er et fantastisk verktøy - det lar deg løse en rekke
saeed Alimohammadi
