Celulele combinate sunt problema astăzi, dar acestea sunt celule combinate verticale. Uitați-vă la figura de mai jos. Dacă există 7 rânduri pentru regiunea Midwest, cuvântul Midwest apare doar pe primul rând. Cineva îmbină apoi celelalte șase celule goale cu celula Midwest pentru a crea o celulă îmbinată care are 7 celule înălțime.
Acesta este un mod oribil de a trimite date. Dacă cineva este competent în Excel, ar putea dori să sorteze datele, să adauge subtotale, să utilizeze tabele pivot. Celulele combinate previn acest lucru.
Pentru a înrăutăți lucrurile, primiți în fiecare zi un nou fișier de la sediu structurat astfel. HQ trimite fișiere similare către 500 de sucursale. Fiind doar un avanpost mic, nu aveți prea multe șanse să convingeți sediul central că acesta este un mod oribil de a trimite date.
Articolul de astăzi este despre cum să rezolvați rapid această problemă cu Power Query. Soluția Power Query va fi mai ușoară în zilele 2 până la 9999 decât următorii pași în Excel:
Guru Excel ar putea sugera acești pași în fiecare zi:
- Selectați toate celulele alegând dreptunghiul de deasupra și din stânga A1.
- Faceți clic pe lansatorul de dialog din colțul din dreapta jos al grupului Alignment din fila Acasă.
- Faceți clic de două ori pe caseta Merge Cells pentru a o deselecta.
- Faceți clic pe OK pentru a închide caseta de dialog Formatare celule
- Selectați din sfârșitul coloanei A înapoi la A1.
- Acasă, Găsiți și selectați, Accesați Special, Blanks, OK
- Tastați = Săgeată sus. Apăsați Ctrl + Enter
- Selectați din sfârșitul coloanei A înapoi la A1.
- Ctrl + C pentru copiere. Faceți clic dreapta și lipiți valorile
Dar există o modalitate mult mai ușoară. Noile instrumente Power Query sunt integrate în versiunile Windows ale Office 365 și Excel 2016. Dacă aveți o versiune Windows a Excel 2010 sau Excel 2013, puteți descărca gratuit Power Query de la Microsoft.
Iată strategia cu Power Query:
- Faceți un plan pe care îl veți salva în fiecare zi din registrul de lucru din sediul central în același folder cu același nume.
- Deschideți un nou registru de lucru gol care va conține datele fixe de la sediul central.
-
În registrul de lucru gol, alegeți Date, Obțineți date, Din fișier, Din registru de lucru.
Porniți procesul de interogare Power - Navigați la folder și fișier de la pasul 1.
-
Când se deschide fereastra Power Query, observați că celulele îmbinate au dispărut. Aveți Midwest în rândul 1 și apoi șase celule care conțin „nul”. Selectați această coloană făcând clic pe antetul Regiunii.
Power Query elimină automat celulele îmbinate -
În Power Query, selectați fila Transformare. Alegeți Umplere, Jos. Cuvântul Midwest este copiat de la rândul 1 la rândurile 2 până la 7. Transformări similare se întâmplă în întregul set de date.
Completați celulele nule cu valoarea de mai sus -
Acasă, Închidere și încărcare. Power Query se va închide. În aproximativ 10-20 de secunde, datele curățate din HQ vor apărea într-o nouă foaie de lucru din registrul de lucru.
Întoarceți datele în Excel - Salvați registrul de lucru cu un nume precum CleanedDataFromHQ.xlsx.
- Când datele sunt selectate, ar trebui să vedeți panoul Interogări și conexiuni din partea dreaptă. Dacă nu vedeți panoul, accesați Date, interogări și conexiuni.
-
Faceți clic dreapta pe interogarea din panoul Interogări și conexiuni. Alegeți Proprietăți.
Deschideți Proprietățile pentru această interogare -
Alegeți „Actualizați datele la deschiderea fișierului”. Faceți clic pe OK.
Setați interogarea să ruleze de fiecare dată când deschideți registrul de lucru.
Fluxul dvs. de lucru devine apoi: (a) Primiți registrul de lucru de la sediul central prin e-mail. (b) Salvați atașamentul în folderul corect cu numele corect. (c) Deschideți registrul de lucru CleanedDataFromHQ.xlsx. Noile date se vor încărca în registrul de lucru.
Urmăriți videoclipul
Transcriere video
Aflați Excel din Podcast, episodul 2221: Umpleți celulele combinate.
Hei, bine ai revenit pe netcast. Eu sunt Bill Jelen, am fost în Calumet City, apare Elizabeth și spune că primește acest dosar - dosarul nostru, ca acesta - în fiecare zi și este oribil. Aici, în Coloana A, nu doar că au pus Midwest și au lăsat o grămadă de spații libere, este o celulă îmbinată. Bine? Ei trimit … HQ trimite aceste fișiere în locații din întreaga lume, cu aceste celule ciudate combinate aici. Celulele combinate sunt rele.
Bine, iată cum să scapi de asta. În primul rând, voi face clic dreapta, Mutați sau copiați, voi crea o copie și o voi muta în noua carte. Deci, ne prefacem că este ca și caietul de lucru pe care îl primește Elizabeth. Fișier, Salvați ca și, de fiecare dată când Elizabeth primește unul dintre aceste cărți de lucru, vreau să o pună exact în același loc cu același nume. Bine? Exact același nume - perfect. Deci, acum avem doar un singur fișier, o singură foaie, voi fi închis. Și apoi vom crea un registru de lucru nou-nouț. Voi introduce doar o nouă foaie de lucru aici și această nouă foaie de lucru va fi numită Raport. Și pe acest spațiu gol, raportul nostru va face date, obține date, dintr-un fișier, dintr-un registru de lucru, va indica fișierul în care îl vom salva - deci, cu același nume exact de fiecare dată - faceți clic pe Import , alege prima foaie de lucru,și apoi faceți clic pe Editare. Bine. În primul rând, acest lucru este extraordinar - interogarea de putere nu va avea nimic din aceste prostii de celule de îmbinare, convertesc instantaneu celulele de îmbinare în celule individuale și toate aceste celule sunt nule. Alegeți acea coloană, este deja aleasă aici, apoi Transformați, completați, completați în jos, așa și apoi Acasă, închideți și încărcați. Bine, deci, acolo este cartea mea de lucru.
Acum, văd că unii au câteva coloane suplimentare aici. Permiteți-mi să fac clic dreapta și vom edita.
Și se pare că orice lucru din dreapta costului trebuie să dispară. Cât de multe lucruri în plus acolo? Faceți clic dreapta și eliminați acele coloane - perfect - Închideți și încărcați. Bine, acum, fluxul de lucru devine, de fiecare dată când Elizabeth primește un nou raport de la sediul central, o va salva în același loc cu un nume de încredere sau orice altceva - oricum - îl numim și apoi deschidem acest registru de lucru și apoi aici faceți clic pe Reîmprospătare.
Sau, dacă am vrut să funcționăm automat, faceți clic dreapta aici, mergeți la Proprietăți, chiar în afara ecranului dvs. până la capăt, și apoi spuneți: De fiecare dată când deschid acest fișier reîmprospătați datele. Deci, de fiecare dată când deschid acest registru de lucru, acesta va ieși în noul registru de lucru pe care l-am primit de la sediul central, va înlocui acele celule goale îmbinate cu valorile care ar trebui să fie acolo, iar viața este grozavă așa.
Power Query este acoperit în cartea mea, dar este, de asemenea, tratat în această carte de Ken Puls și Miguel Escobar, M este pentru (DATA) MONKEY.
Bine, deci, Elizabeth: Cum scap de celulele de îmbinare verticale? Sediul meu îmi tot trimite aceste dosare în fiecare zi. Deci, planul: vom salva registrul de lucru într-un loc de încredere, cu un nume de încredere; creați un registru de lucru gol de raportare, Date, Obțineți date, Din fișier, Din registru de lucru, specificați foaia, BAM! Celulele de îmbinare au dispărut. Selectez acea coloană și Completează, Închide & Încarcă; apoi de fiecare dată când obțineți un nou registru de lucru, salvați acel nou registru de lucru în locul de încredere cu același nume de încredere; Deschid registrul de lucru Raportare și Reîmprospătare, sau pur și simplu setăm registrul de lucru Raportare la Reîmprospătare automat la deschidere.
Pentru a descărca registrul de lucru din videoclipul de astăzi este adresa URL din descrierea YouTube.
Vreau să-i mulțumesc Elizabeth pentru că a fost la seminarul meu din Calumet City, vreau să vă mulțumesc pentru că ați trecut pe aici. Ne vedem data viitoare pentru un alt netcast de la.
Descărcați fișierul Excel
Pentru a descărca fișierul Excel: fill-merged-cells-down.xlsx
Power Query este un instrument uimitor - vă permite să rezolvați o varietate de
saeed Alimohammadi
