Sloučené buňky dnes představují problém, ale jedná se o svisle sloučené buňky. Podívejte se na obrázek níže. Pokud pro region Středozápad existuje 7 řádků, slovo Středozápad se zobrazí pouze v prvním řádku. Někdo poté sloučí zbývajících šest prázdných buněk s buňkou na Středozápadě, aby vytvořil sloučenou buňku, která je vysoká 7 buněk.
To je hrozný způsob odesílání dat. Pokud někdo ovládá Excel, může chtít seřadit data, přidat mezisoučty, použít kontingenční tabulky. Sloučené buňky tomu zabraňují.
Aby toho nebylo málo, každý den dostáváte z ústředny nový soubor strukturovaný takto. Velitelství vysílá podobné soubory do 500 poboček. Jelikož jste jen jednou malou základnou, nemáte moc šancí přesvědčit velitelství, že je to hrozný způsob odesílání dat.
Dnešní článek je o tom, jak rychle vyřešit tento problém pomocí Power Query. Řešení Power Query bude ve dnech 2 až 9999 snazší než následující kroky v aplikaci Excel:
Guru aplikace Excel může každý den navrhovat tyto kroky:
- Vyberte všechny buňky výběrem obdélníku nad a nalevo od A1.
- Klikněte na spouštěč dialogů v pravém dolním rohu skupiny Zarovnání na kartě Domů.
- Dvakrát klikněte na políčko Sloučit buňky a zrušte jeho výběr.
- Kliknutím na OK zavřete dialogové okno Formátovat buňky
- Vyberte od konce sloupce A zpět po A1.
- Domů, Najít a vybrat, Přejít na speciální, Prázdné, OK
- Typ = šipka nahoru. Stiskněte Ctrl + Enter
- Vyberte od konce sloupce A zpět po A1.
- Ctrl + C pro kopírování. Klepněte pravým tlačítkem a vložte hodnoty
Existuje však mnohem jednodušší způsob. Nové nástroje Power Query jsou integrovány do verzí Windows Office 365 a Excel 2016 pro Windows. Pokud máte Windows pro Excel 2010 nebo Excel 2013, můžete si Power Query zdarma stáhnout z Microsoftu.
Zde je strategie s Power Query:
- Vytvořte plán, který uložíte denní sešit z ústředí do stejné složky se stejným názvem.
- Otevřete nový prázdný sešit, který bude obsahovat fixní data z ústředí.
-
V prázdném sešitu zvolte Data, Načíst data, Ze souboru, Ze sešitu.
Spusťte proces Power Query - Přejděte do složky a souboru z kroku č. 1.
-
Když se otevře okno Power Query, všimněte si, že sloučené buňky jsou pryč. V řadě 1 máte středozápad a poté šest buněk, které obsahují „null“. Tento sloupec vyberte kliknutím na záhlaví Region.
Power Query automaticky odebere sloučené buňky -
V Power Query vyberte kartu Transformace. Zvolte Fill, Down. Slovo Midwest je zkopírováno z řádku 1 do řádků 2 až 7. Podobné transformace probíhají v celé datové sadě.
Vyplňte prázdné buňky hodnotou shora -
Domů, Zavřít a načíst. Power Query se zavře. Za přibližně 10–20 sekund se vyčištěná data z ústředí objeví v novém listu v sešitu.
Vraťte data do aplikace Excel - Uložte sešit s názvem, jako je CleanedDataFromHQ.xlsx.
- Po výběru dat by se měl na pravé straně zobrazit panel Dotazy a připojení. Pokud panel nevidíte, přejděte do části Data, dotazy a připojení.
-
Klepněte pravým tlačítkem na dotaz na panelu Dotazy a připojení. Vyberte Vlastnosti.
Otevřete Vlastnosti pro tento dotaz -
Vyberte možnost „Obnovit data při otevření souboru“. Klikněte na OK.
Nastavte dotaz tak, aby se spouštěl při každém otevření sešitu.
Váš pracovní postup se poté stane: (a) Přijměte sešit z ústředí e-mailem. (b) Uložte přílohu do správné složky se správným názvem. (c) Otevřete sešit CleanedDataFromHQ.xlsx. Nová data se načtou do sešitu.
Sledovat video
Přepis videa
Naučte se Excel z Podcastu, epizoda 2221: Vyplňte sloučené buňky dolů.
Hej, vítej zpět na netcastu. Jsem Bill Jelen, byl jsem v Calumet City, Elizabeth se objevila a řekla, že dostává tento soubor - náš soubor, jako tento soubor - každý den a je to hrozné. Tady ve sloupci A to není jen to, že dali Midwest a nechali spoustu mezer, je to sloučená buňka. V pořádku? Posílají-- Ústředí posílá tyto soubory do míst po celém světě s těmito šílenými sloučenými buňkami tady. Sloučené buňky jsou zlé.
Dobře, tady je způsob, jak se toho zbavit. Nejprve kliknu pravým tlačítkem, přesunout nebo zkopírovat, vytvořit kopii a přesunout ji do nové knihy. Takže předstíráme, že je to jako sešit, který Elizabeth získává. Uložte, uložte jako a pokaždé, když Elizabeth dostane jeden z těchto sešitů, chci, aby ji dala na přesně stejné místo se stejným jménem. V pořádku? Přesné stejné jméno - perfektní. Takže teď máme ten jediný soubor, jeden list, soubor zavřu. A pak vytvoříme zcela nový sešit. Právě sem vložím nový list a tento nový list se bude jmenovat Zpráva. A na tomto prázdném místě bude naše zpráva dělat Data, Načíst data, Ze souboru, Ze sešitu, přejděte na soubor, do kterého ho uložíme - takže vždy se stejným názvem - klikněte na Importovat , vyberte první list,a poté klikněte na Upravit. V pořádku. Nejprve je to úžasné, mocný dotaz - energetický dotaz nebude mít žádný z těchto nesmyslů slučovacích buněk, okamžitě převádějí slučovací buňky na jednotlivé buňky a všechny tyto buňky jsou nulové. Vyberte sloupec, který je zde již vybrán, a poté Transformovat, Vyplnit, Vyplnit dolů, jako je tento, a poté Domů, Zavřít a načíst. Dobře, takže existuje můj sešit.
Nyní vidím, že někteří mají nějaké sloupce navíc. Klikněte pravým tlačítkem a my to upravíme.
A vypadá to, že cokoli napravo od nákladů musí zmizet. Kolik dalších věcí tam venku? Klepněte pravým tlačítkem a odebrat tyto sloupce - perfektní - Zavřít a načíst. Dobře, nyní se tok práce stává pokaždé, když Elizabeth dostane novou zprávu z ústředí, uloží ji na stejné místo se spolehlivým jménem nebo jakkoli - jakkoli - zavoláme to a poté otevřete tento sešit pak zde klikněte na Obnovit.
Nebo, pokud jsme chtěli pracovat automaticky, klikněte sem pravým tlačítkem, přejděte dolů na Vlastnosti, úplně mimo obrazovku úplně dole a pak řekněte: Pokaždé, když otevřete tento soubor, obnovte data. Takže pokaždé, když otevřu tento sešit, přejde do nového sešitu, který jsme dostali z ústředí, nahradí tyto prázdné sloučené buňky hodnotami, které by tam měly být, a život je skvělý.
Mocnému dotazu se věnuji v mé knize, ale také v této knize od Kena Pulse a Miguela Escobara, M je pro (DATA) MONKEY.
Dobře, Elizabeth: Jak se zbavím vertikálních sloučených buněk? Moje ústředí mi tyto soubory posílá každý den. Takže plán: Uložíme sešit na spolehlivé místo se spolehlivým názvem; vytvořte prázdný sešit pro vykazování, Data, Získat data, Ze souboru, Ze sešitu, určete list, BAM! Sloučené buňky jsou pryč. Vyberu ten sloupec a Fill Down, Close & Load; potom pokaždé, když dostanete nový sešit, uložte tento nový sešit na spolehlivé místo se stejným spolehlivým názvem; I open the Reporting workbook and Refresh, or we just set the Reporting workbook to Refresh automatically when open.
Ke stažení sešitu z dnešního videa je URL v popisu YouTube.
Chci poděkovat Elizabeth za to, že byla na mém semináři v Calumet City, chci vám poděkovat, že jste se zastavili. Uvidíme se příště na dalším netcastu z.
Stáhněte si soubor Excel
Stažení souboru aplikace Excel: fill-merged-cells-down.xlsx
Power Query je úžasný nástroj - umožňuje vám vyřešit různé
saeed Alimohammadi
