Sledujte video - Vyplňte v Excelu
Mnohokrát se můžete setkat se sadou dat, kde je daty vyplněna pouze jedna buňka a buňky pod ní jsou prázdné až do další hodnoty.
Něco, jak je uvedeno níže:
Přestože tento formát některým lidem funguje, problém s tímto druhem dat spočívá v tom, že jej nemůžete použít k vytváření kontingenčních tabulek ani ve výpočtech.
A to má snadnou opravu!
V tomto tutoriálu vám ukážu, jak rychle vyplňte buňky v aplikaci Excel až do další vyplněné hodnoty.
Můžete to snadno provést pomocí jednoduché speciální metody dialogového okna Přejít na, VBA nebo Power Query.
Začněme tedy!
Metoda 1 - Vyplňte pomocí Přejít na Speciální + Vzorec
Předpokládejme, že máte sadu dat, jak je uvedeno níže, a chcete vyplnit data ve sloupci A a ve sloupci B.
Ve sloupci B je cílem vyplnit „Tiskárnu“ do poslední prázdné buňky pod ní a poté, když se spustí „Skener“, vyplňte do níže uvedených buněk „Skener“, dokud nebudou buňky prázdné.
V níže uvedených krocích přejděte na položku Speciální a vyberte všechny prázdné buňky a vyplňte je pomocí jednoduchého vzorce:
- Vyberte data, která chcete vyplnit (v našem příkladu A1: D21)
- Přejděte na kartu „Domů“
- Ve skupině Úpravy klikněte na ikonu „Najít a vybrat“ (v rozevíracím seznamu se zobrazí několik dalších možností)
- Klikněte na možnost „Přejít na“
- V dialogovém okně Přejít na, které se otevře, klikněte na tlačítko Speciální. Otevře se dialogové okno „Přejít na speciální“
- V dialogovém okně Přejít na speciální klikněte na Mezery
- Klikněte na OK
Výše uvedené kroky by vybrali všechny prázdné buňky v sadě dat (jak je uvedeno níže).
V prázdných buňkách, které jsou vybrány, byste si všimli, že jedna buňka je světlejší než zbytek výběru. Tato buňka je aktivní buňka, do které zadáme vzorec.
Nedělejte si starosti s tím, kde je buňka ve výběru, protože naše metoda by v každém případě fungovala.
Níže jsou uvedeny kroky k vyplnění dat ve vybraných prázdných buňkách:
- Stiskněte klávesu rovná se (=) na klávesnici. Tím se do aktivní buňky vloží znaménko rovná se
- Stiskněte klávesu se šipkou nahoru. Tím se vloží odkaz na buňku buňky nad aktivní buňku. Například v našem případě je aktivní buňkou B3 a když provedeme tyto dva kroky, do buňky zadá = B2
- Podržte klávesu Control a stiskněte klávesu Enter
Výše uvedené kroky by automaticky vložily všechny prázdné buňky s hodnotou nad nimi.
I když to může vypadat jako příliš mnoho kroků, jakmile se této metodě věnujete, budete moci rychle vyplnit data v aplikaci Excel během několika sekund.
Nyní jsou při používání této metody další dvě věci, o které se musíte postarat.
Převést vzorce na hodnoty
Prvním z nich je zajistit, abyste převáděli vzorce na hodnoty (abyste měli statické hodnoty a aby se věci v případě, že v budoucnu změníte data, nepokazily).
Níže je video, které vám ukáže, jak rychle převést vzorce na hodnoty v Excelu.
Změňte formát data
V případě, že ve svých datech používáte data (jako já v příkladových datech), všimli byste si, že vyplněné hodnoty ve sloupci data jsou čísla a ne data.
Pokud je výstup ve sloupci data v požadovaném formátu data, jste dobří a nemusíte dělat nic jiného.
Pokud ale nejsou ve správném formátu data, musíte provést menší úpravu. I když máte správné hodnoty, stačí změnit formát, aby se v buňce zobrazoval jako datum.
Níže jsou uvedeny kroky, jak to provést:
- Vyberte sloupec s daty
- Klikněte na kartu Domů
- Ve skupině Číslo klikněte na rozevírací nabídku Formát a poté vyberte formát data.
Pokud potřebujete toto vyplnění provést jednou za čas, doporučuji použít tuto speciální a vzorovou techniku Go-To.
Ačkoli to má několik kroků, je to jednoduché a dává vám výsledek přímo ve vaší datové sadě.
Ale v případě, že to musíte dělat poměrně často, doporučuji vám podívat se na metody VBA a Power Query, které jsou popsány dále.
Metoda 2 - Vyplňte pomocí jednoduchého kódu VBA
Můžete použít opravdu jednoduchý kód makra VBA k rychlému vyplnění buněk v aplikaci Excel až do poslední prázdné hodnoty.
Stačí přidat kód VBA do souboru pouze jednou a poté můžete kód snadno znovu použít vícekrát ve stejném sešitu nebo dokonce ve více sešitech ve vašem systému.
Níže je kód VBA, který projde každou buňku ve výběru a vyplní všechny buňky, které jsou prázdné:
'Tento kód VBA je vytvořen společností Sumit Bansal z dílčí FillDown společnosti TrumpExcel.com pro každou buňku ve výběru If cell = "" Then cell.FillDown End If Next End Sub
Výše uvedený kód používá smyčku For k procházení každou buňkou ve výběru.
Ve smyčce For jsem použil podmínku If-Then, která kontroluje, zda je buňka prázdná nebo ne.
V případě, že je buňka prázdná, je naplněna hodnotou z buňky výše a v případě, že není prázdná, pak smyčka for buňku jednoduše ignoruje a přesune se na další.
Nyní, když máte kód VBA, ukážu vám, kam tento kód vložit do aplikace Excel:
- Vyberte data, která chcete vyplnit
- Klikněte na kartu Vývojář
- Klikněte na ikonu Visual Basic (nebo můžete použít klávesovou zkratku ALT + F11). Tím se otevře editor jazyka Visual Basic v aplikaci Excel
- V editoru jazyka Visual Basic na levé straně byste měli Project Explorer. Pokud to nevidíte, klikněte v nabídce na možnost „Zobrazit“ a poté klikněte na Průzkumník projektů
- V případě, že máte otevřeno více sešitů aplikace Excel, Project Explorer vám zobrazí všechny názvy sešitů. Vyhledejte název sešitu, kde máte data.
- Klikněte pravým tlačítkem na libovolný objekt v sešitu, přejděte na Vložit a poté klikněte na Modul. Tím se vloží nový modul pro tento sešit
- Poklepejte na modul „Modul“, který jste právě vložili do výše uvedeného kroku. Otevře se okno s kódem pro daný modul
- Zkopírujte a vložte kód VBA do okna kódu
- Umístěte kurzor kamkoli do kódu a poté spusťte kód makra kliknutím na zelené tlačítko na panelu nástrojů nebo pomocí klávesové zkratky F5
Výše uvedené kroky by spustily kód VBA a vaše data by byla vyplněna.
Chcete-li tento kód VBA v budoucnu znovu použít, musíte tento soubor uložit jako sešit Excelu s povolenými makry (s příponou .XLSM)
Další věc, kterou můžete udělat, je přidat toto makro na panel nástrojů Rychlý přístup, který je vždy viditelný a budete k němu mít přístup jediným kliknutím (v sešitu, kde máte kód v backendu).
Až tedy příště budete mít data, která potřebujete vyplnit, vše, co musíte udělat, je provést výběr a poté kliknout na tlačítko makra na panelu nástrojů Rychlý přístup.
Toto makro můžete také přidat do osobního sešitu maker a poté ho použít v jakémkoli sešitu ve vašem systému.
Kliknutím sem si přečtete více o osobních sešitech maker a o tom, jak do nich přidat kód.
Metoda 3 - Vyplňte pomocí Power Query
Power Query má vestavěnou funkci, která vám umožňuje vyplnění jediným kliknutím.
Používání Power Query se doporučuje, pokud ho stejně používáte k transformaci dat nebo kombinování dat z více listů nebo více sešitů.
Jako součást stávajícího pracovního postupu můžete použít možnost vyplnění v Power Query k rychlému vyplnění prázdných buněk.
Chcete -li používat Power Query, doporučujeme, aby vaše data byla ve formátu tabulky Excel. Pokud nemůžete svá data převést do tabulky aplikace Excel, budete muset pro data vytvořit pojmenovaný rozsah a poté tento pojmenovaný rozsah použít v Power Query.
Níže mám soubor dat, který jsem již převedl do tabulky aplikace Excel. Můžete to udělat tak, že vyberete datovou sadu, přejdete na kartu Vložit a poté kliknete na ikonu Tabulka.
Níže uvedené kroky k použití Power Query k vyplnění dat do další hodnoty:
- Vyberte libovolnou buňku v sadě dat
- Klikněte na kartu Data
- Ve skupině Získat a transformovat data klikněte na „Z listu“. Tím se otevře editor Power Query. Prázdné buňky by v Power Query zobrazovaly hodnotu „null“
- V editoru Power Query vyberte sloupce, pro které chcete vyplnit. Chcete -li to provést, podržte klávesu Control a poté klikněte na záhlaví sloupců, které chcete vybrat. V našem případě by to byly sloupce Datum a Produkt.
- Klikněte pravým tlačítkem na kterékoli z vybraných záhlaví
- Přejděte na možnost „Vyplnit“ a poté klikněte na „Dolů“. Tím by se vyplnily údaje ve všech prázdných buňkách
- Klikněte na kartu Soubor a poté klikněte na „Zavřít a načíst“. Tím se do sešitu s výslednou tabulkou vloží nový list
I když tato metoda může znít přehnaně, jednou velkou výhodou používání Power Query je, že vám umožní rychle aktualizovat výsledná data v případě, že se změní původní data.
Pokud například k původním datům přidáte další záznamy, nemusíte výše uvedené kroky provádět znovu. Na výslednou tabulku můžete jednoduše kliknout pravým tlačítkem a poté kliknout na Obnovit.
Přestože metoda Power Query funguje dobře, je nejlepší ji použít, když již ve svém pracovním postupu Power Query používáte.
Pokud máte pouze datovou sadu, do které chcete vyplnit prázdné buňky, bylo by vhodnější použít speciální metodu Přejít na nebo metodu VBA (popsanou výše).
Toto jsou tři jednoduché způsoby, které můžete použít vyplňte prázdné buňky až do další hodnoty v aplikaci Excel.
Doufám, že jste našli tento návod užitečný.
