Vyplňte prázdne bunky do ďalšej hodnoty v Exceli (3 jednoduché spôsoby)

Mnohokrát sa môžete stretnúť s množinou údajov Excelu, kde je údajmi vyplnená iba jedna bunka a bunky pod ňou sú prázdne až do ďalšej hodnoty.

Ako je ukázané nižšie:

Aj keď tento formát pre niektorých ľudí funguje, problémom tohto druhu údajov je, že ho nemôžete použiť na vytváranie kontingenčných tabuliek ani na výpočty.

Dá sa to ľahko opraviť!

V tomto návode vám ukážem akoRýchlo vyplňte bunky až do ďalšej hodnoty naplnenia v Exceli.

Môžete to urobiť jednoducho pomocou jednoduchých špeciálnych dialógových techník Go-To, VBA alebo Power Query.

Tak poďme na to!

Metóda 1 – Vyplňte pomocou vzorca Prejsť na špeciálne +

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete vyplniť údaje v stĺpcoch A a B.

V stĺpci B je cieľom vyplniť "tlačiareň" až do poslednej prázdnej bunky pod ňou, potom keď sa spustí "skener", potom vyplniť "skener" v bunke pod ňou, kým nebude bunka prázdna.

Pomocou krokov uvedených nižšie prejdite na špeciálne a vyberte všetky prázdne bunky a vyplňte jednoduchým vzorcom:

1. Vyberte údaje, ktoré chcete vyplniť (v našom príklade A1:D21)
2. Prejdite na kartu „Domov“.

3. V skupine úprav kliknite na ikonu Nájsť a vybrať (tým sa zobrazia ďalšie možnosti v rozbaľovacej ponuke)
4. Kliknite na možnosť „Prejsť“.

5. V dialógovom okne Prejsť na, ktoré sa otvorí, kliknite na tlačidlo Špeciálne.Tým sa otvorí dialógové okno „Prejsť na špeciálne“.

6. V dialógovom okne Prejsť na špeciálne kliknite na Prázdne

7. Kliknite na tlačidlo OK

Vyššie uvedené kroky vyberú všetky prázdne bunky v množine údajov (zobrazené nižšie).

Medzi vybratými prázdnymi bunkami si všimnete, že jedna bunka je jasnejšia ako zvyšok výberu.Táto bunka je aktívna bunka, do ktorej chceme zadať vzorec.

Nebojte sa o pozíciu bunky vo výbere, pretože naša metóda bude fungovať v každom prípade.

Tu sú kroky na vyplnenie údajov do vybratých prázdnych buniek:

1. Kliknite na kláves znamienko rovnosti (=) na klávesnici.Tým sa do aktívnej bunky vloží znamienko rovnosti
2. Stlačte kláves so šípkou nahor.Toto vloží odkaz na bunku nad aktívnu bunku.Napríklad v našom prípade je aktívnou bunkou B3 a keď vykonáme tieto dva kroky, do bunky vstúpi =B2
3. Podržte kláves Control a stlačte kláves Enter

Vyššie uvedené kroky automaticky vložia všetky prázdne bunky s vyššie uvedenými hodnotami.

Aj keď sa to môže zdať ako príliš veľa krokov, akonáhle sa do toho pustíte, budete môcť rýchlo vyplniť údaje v Exceli za pár sekúnd.

Teraz sú tu ďalšie dve veci, na ktoré si musíte byť vedomí pri používaní tejto metódy.

Previesť vzorec na hodnotu

Prvým je uistiť sa, že konvertujete vzorce na hodnoty (aby ste mali statické hodnoty a nepokazili sa, keď v budúcnosti zmeníte údaje).

Zmeňte formát dátumu

Ak vo svojich údajoch použijete dátumy (ako som to urobil v príklade údajov), všimnete si, že vyplnené hodnoty v stĺpci dátumu sú čísla namiesto dátumov.

Ak je výstup v stĺpci dátumu v požadovanom formáte dátumu, potom ste v poriadku a nemusíte robiť nič iné.

Ak však tieto dátumy nie sú v správnom formáte, je potrebné ich doladiť.Aj keď máte správnu hodnotu, stačí zmeniť formát tak, aby sa v bunke zobrazil ako dátum.

Tu sú kroky, ako to urobiť:

1. Vyberte stĺpce obsahujúce dátumy
2. Kliknite na kartu Domov
3. V skupine Čísla kliknite na rozbaľovaciu ponuku Formát a vyberte formát dátumu.

Ak potrebujete z času na čas urobiť túto výplň, odporúčam túto špeciálnu a formulovú techniku ​​Go-To.

Hoci má niekoľko krokov, je jednoduchý a poskytuje výsledky v súbore údajov.

Ak to však musíte robiť často, odporúčam vám pozrieť sa na VBA a metódu Power Query opísanú ďalej

Metóda 2 - Vyplňte jednoduchý kód VBA

Na rýchle vyplnenie buniek v Exceli až do poslednej prázdnej hodnoty môžete použiť veľmi jednoduchý kód makra VBA.

Kód VBA musíte do súboru pridať iba raz a potom môžete tento kód jednoducho použiť viackrát v tom istom zošite alebo dokonca vo viacerých zošitoch vo vašom systéme.

Tu je kód VBA, ktorý prejde každou bunkou vo výbere a vyplní všetky prázdne bunky:

Sub FillDown() Pre každú bunku vo výbere If cell = "" Then cell.FillDown End If Next End Sub

Vyššie uvedený kód používa cyklus For na iteráciu cez každú bunku vo výbere.

V slučke For používam podmienku If-Then na kontrolu, či je bunka prázdna.

Ak je bunka prázdna, vyplní sa hodnotou z bunky vyššie, ak nie, cyklus for bunku ignoruje a presunie sa do ďalšej bunky.

Teraz, keď máte kód VBA, dovoľte mi ukázať vám, kam vložiť tento kód v Exceli:

1. Vyberte údaje, ktoré chcete vyplniť
2. Kliknite na kartu vývojár

3. Kliknite na ikonu Visual Basic (alebo môžete použiť klávesovú skratku ALT+F11).Tým sa otvorí editor jazyka Visual Basic v Exceli

4. V editore Visual Basic naľavo budete mať Project Explorer.Ak ho nevidíte, kliknite na možnosť Zobraziť v ponuke a potom kliknite na položku Prieskumník projektu

5. Ak máte otvorených viacero excelových zošitov, Project Explorer zobrazí všetky názvy zošitov.Nájdite názov zošita, kde máte údaje.
6. Kliknite pravým tlačidlom myši na ľubovoľný objekt v zošite, prejdite na položku Vložiť a kliknite na položku Modul.Týmto sa vloží nový modul pre tento zošit

7. Dvakrát kliknite na „Modul“, ktorý ste práve vložili vo vyššie uvedených krokoch.Otvorí sa okno s kódom pre daný modul

8. Skopírujte a prilepte kód VBA do okna kódu
9. Umiestnite kurzor kdekoľvek v kóde a spustite kód makra kliknutím na zelené tlačidlo na paneli nástrojov alebo pomocou klávesovej skratky F5

Vyššie uvedené kroky spustia kód VBA a vaše údaje sa vyplnia.

Ak budete chcieť tento kód VBA v budúcnosti znova použiť, budete musieť tento súbor uložiť ako zošit programu Excel s podporou makier (s príponou .XLSM)

Ďalšia vec, ktorú môžete urobiť, je pridať toto makro na panel s nástrojmi Rýchly prístup, ktorý je vždy viditeľný a k tomuto makru sa dostanete jediným kliknutím (v zošite s kódom na konci).

Takže nabudúce, keď budete mať údaje, ktoré je potrebné vyplniť, stačí urobiť výber a kliknúť na tlačidlo makra na paneli s nástrojmi Rýchly prístup.

Toto makro môžete tiež pridať do svojho osobného zošita makier a potom ho použiť v ľubovoľnom zošite vo vašom systéme.

Kliknutím sem sa dozviete viac o svojom osobnom zošite makier a o tom, ako doň pridať kód.

Metóda 3 – vyplňte pomocou Power Query

mocenský dotazMá vstavanú funkciu, ktorá vám umožní vyplniť ho jediným kliknutím.

Odporúča sa, keď stále používate Power Query na transformáciu údajov alebo kombináciu údajov z viacerých pracovných hárkov alebo viacerých zošitov.

V rámci existujúceho pracovného postupu môžete rýchlo vyplniť prázdne bunky pomocou možnosti Vyplniť v Power Query.

Ak chcete používať Power Query, odporúča sa, aby boli vaše údaje vo formáte excelovej tabuľky.Ak nemôžete konvertovať údaje do excelovej tabuľky, musíte pre údaje vytvoriť pomenovaný rozsah a potom použiť tento pomenovaný rozsah v doplnku Power Query.

Nižšie mám množinu údajov, ktorá bola prevedená na tabuľku Excel.Môžete to urobiť tak, že vyberiete množinu údajov, prejdete na kartu Vložiť a kliknete na ikonu Tabuľka.

Tu sú kroky na použitie Power Query na vyplnenie údajov až po ďalšiu hodnotu:

1. Vyberte ľubovoľnú bunku v množine údajov
2. Kliknite na kartu Údaje

3. V skupine Získať a transformovať údaje kliknite na položku Z pracovného hárka.Tým sa otvorí editor Power Query.Všimnite si, že v prázdnych bunkách sa v Power Query zobrazí hodnota „null“.

4. V editore Power Query vyberte stĺpec, ktorý chcete vyplniť.Ak to chcete urobiť, podržte kláves Control a kliknite na hlavičku stĺpca, ktorý chcete vybrať.V našom príklade to budú stĺpce dátumu a produktu.
5. Kliknite pravým tlačidlom myši na ľubovoľný vybraný titul

6. Prejdite na možnosť Vyplniť a kliknite na tlačidlo Nadol.Tým sa vyplnia všetky prázdne bunky údajmi

7. Kliknite na kartu Súbor a potom kliknite na položku Zavrieť a načítať.Tým sa do zošita vloží nový pracovný hárok s tabuľkou výsledkov

Aj keď tento prístup môže znieť ako prehnaný, jednou z veľkých výhod používania Power Query je, že vám umožňuje rýchlo aktualizovať výsledné údaje, keď sa pôvodné údaje zmenia.

Ak napríklad k pôvodným údajom pridáte ďalšie záznamy, nie je potrebné vykonať všetky vyššie uvedené kroky znova.Stačí kliknúť pravým tlačidlom myši na výslednú tabuľku a kliknúť na tlačidlo Obnoviť.

Hoci metóda Power Query funguje dobre, je najlepšie ju použiť, keď už Power Query používate vo svojom pracovnom postupe.

Ak máte len jednu množinu údajov s prázdnymi bunkami na vyplnenie, je vhodnejšie použiť špeciálnu metódu Prejsť na alebo metódu VBA (popísanú vyššie).

Môžete teda použiť tieto tri jednoduché spôsobyVyplňte prázdne bunky až do ďalšej hodnoty v Exceli.

Dúfam, že vám bol tento návod užitočný.