Pozrite si video - Doplňte v programe Excel
Mnohokrát sa môžete stretnúť s množinou údajov, kde je údajmi vyplnená iba jedna bunka a bunky pod ňou sú prázdne až do nasledujúcej hodnoty.
Niečo, ako je uvedené nižšie:
Aj keď tento formát niektorým ľuďom 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.
A toto má jednoduchú opravu!
V tomto návode vám ukážem, ako rýchlo vyplňte bunky v programe Excel až do ďalšej vyplnenej hodnoty.
Môžete to ľahko vykonať pomocou jednoduchej techniky špeciálneho dialógového okna Go-To, VBA alebo Power Query.
Začnime teda!
Metóda 1 - Vyplňte pomocou Prejdite na Špeciálne + Vzorec
Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete vyplniť údaje v stĺpci A a stĺpci B.
V stĺpci B je cieľom vyplniť „Tlačiareň“ do poslednej prázdnej bunky pod ňou a potom, keď sa spustí „Skener“, vyplniť do nižšie uvedených buniek „Skener“, kým nie sú bunky prázdne.
Podľa nižšie uvedených krokov prejdite na položku Špeciálne, aby ste vybrali všetky prázdne bunky a vyplnili ich podľa jednoduchého vzorca:
- Vyberte údaje, ktoré chcete vyplniť (v našom prípade A1: D21)
- Prejdite na kartu „Domov“
- V skupine Úpravy kliknite na ikonu „Nájsť a vybrať“ (v rozbaľovacej ponuke sa zobrazí niekoľko ďalších možností)
- Kliknite na možnosť „Prejsť na“
- V dialógovom okne Choď na, ktoré sa otvorí, kliknite na tlačidlo Špeciálne. Otvorí sa dialógové okno „Prejsť na špeciálne“
- V dialógovom okne Prejsť na špeciálne kliknite na prázdne miesta
- Kliknite na tlačidlo OK
Vyššie uvedené kroky by vybrali všetky prázdne bunky v množine údajov (ako je uvedené nižšie).
V prázdnych bunkách, ktoré sú vybraté, by ste si všimli, že jedna bunka je svetlejšia ako zvyšok výberu. Táto bunka je aktívna bunka, do ktorej zadáme vzorec.
Nerobte si starosti s tým, kde je bunka vo výbere, pretože naša metóda by v každom prípade fungovala.
Ďalej sú uvedené kroky na vyplnenie údajov do vybraných prázdnych buniek:
- Na klávesnici stlačte kláves rovnajúci sa (=). Do aktívnej bunky sa vloží znamienko rovnosti
- Stlačte kláves so šípkou nahor. Týmto sa vloží odkaz na bunku nad aktívnu bunku. Napríklad v našom prípade je B3 aktívna bunka a keď vykonáme tieto dva kroky, zadá do bunky = B2
- Podržte kláves Control a stlačte kláves Enter
Vyššie uvedené kroky by automaticky vložili všetky prázdne bunky s hodnotou nad nimi.
Aj keď to môže vyzerať ako príliš veľa krokov, po zvládnutí tejto metódy budete môcť v programe Excel rýchlo vyplniť údaje v priebehu niekoľkých sekúnd.
Teraz sú pri použití tejto metódy ďalšie dve veci, o ktoré sa musíte postarať.
Previesť vzorce na hodnoty
Prvým z nich je zaistiť, aby ste vzorce previedli na hodnoty (aby ste mali statické hodnoty a aby sa vám veci v prípade zmeny údajov v budúcnosti nepokazili).
Nasleduje video, ktoré vám ukáže, ako rýchlo previesť vzorce na hodnoty v programe Excel.
Zmeňte formát dátumu
V prípade, že vo svojich údajoch používate dátumy (ako som ja v príklade údajov), všimli by ste si, že vyplnené hodnoty v stĺpci dátumu sú čísla, nie dátumy.
Ak je výstup v stĺpci dátumu v požadovanom formáte dátumu, ste v poriadku a nemusíte robiť nič iné.
Ak však nie sú v správnom formáte dátumu, musíte vykonať menšiu úpravu. Aj keď máte správne hodnoty, stačí zmeniť formát, aby sa v bunke zobrazoval ako dátum.
Nasledujú kroky, ako to urobiť:
- Vyberte stĺpec s dátumami
- Kliknite na kartu Domov
- V skupine Číslo kliknite na rozbaľovaciu ponuku Formát a potom vyberte formát dátumu.
Ak potrebujete vyplnenie vykonať raz za čas, odporúčam vám použiť túto špeciálnu a vzorcovú techniku Go-To.
Napriek tomu, že má niekoľko krokov, je jednoduchý a poskytuje vám výsledok priamo vo vašej množine údajov.
Ale v prípade, že to musíte robiť dosť často, odporúčam vám pozrieť sa na metódy VBA a Power Query, ktoré sú popísané ďalej.
Metóda 2 - Vyplňte pomocou jednoduchého kódu VBA
Skutočne jednoduchý kód makra VBA môžete použiť na rýchle vyplnenie buniek v programe Excel až do poslednej prázdnej hodnoty.
Kód VBA stačí do súboru pridať iba raz a potom môžete kód ľahko opakovane použiť viackrát v tom istom zošite alebo dokonca vo viacerých zošitoch vo svojom systéme.
Nasleduje kód VBA, ktorý prejde každou bunkou vo výbere a vyplní všetky bunky, ktoré sú prázdne:
„Tento kód VBA je vytvorený spoločnosťou Sumit Bansal zo servera TrumpExcel.com Sub FillDown () pre každú vybratú bunku If cell =" "Then cell.FillDown End If Next End Sub
Vyššie uvedený kód používa slučku For na prechod každou bunkou vo výbere.
V slučke For som použil podmienku If-Then, ktorá kontroluje, či je bunka prázdna alebo nie.
V prípade, že je bunka prázdna, je naplnená hodnotou z vyššie uvedenej bunky a v prípade, že nie je prázdna, potom slučka for bunku jednoducho ignoruje a prejde na ďalšiu.
Teraz, keď máte kód VBA, ukážem vám, kam tento kód vložiť do programu Excel:
- Vyberte údaje, ktoré chcete vyplniť
- Kliknite na kartu Vývojár
- Kliknite na ikonu Visual Basic (alebo môžete použiť klávesovú skratku ALT + F11). V programe Excel sa otvorí editor jazyka Visual Basic
- V editore jazyka Visual Basic vľavo by ste mali Project Explorer. Ak to nevidíte, kliknite v ponuke na možnosť „Zobraziť“ a potom kliknite na položku Project Explorer
- V prípade, že máte otvorených viac zošitov programu Excel, Project Explorer vám ukáže všetky názvy zošitov. Vyhľadajte názov zošita, kde máte údaje.
- Kliknite pravým tlačidlom myši na ľubovoľný objekt v zošite, prejdite na položku Vložiť a potom kliknite na položku Modul. Tým sa vloží nový modul pre tento zošit
- Dvakrát kliknite na „modul“, ktorý ste práve vložili do vyššie uvedeného kroku. Otvorí sa okno s kódom pre tento modul
- Skopírujte a prilepte kód VBA do okna kódu
- Umiestnite kurzor kamkoľvek do kódu a potom spustite kód makra kliknutím na zelené tlačidlo na paneli s nástrojmi alebo pomocou klávesovej skratky F5
Vyššie uvedené kroky spustia kód VBA a vaše údaje budú vyplnené.
Ak chcete tento kód VBA v budúcnosti použiť znova, musíte tento súbor uložiť ako zošit programu Excel s povolenými makrami (s príponou .XLSM)
Ďalšou vecou, ktorú môžete urobiť, je pridať toto makro na panel s nástrojmi Rýchly prístup, ktorý je vždy viditeľný a budete k nemu mať prístup jediným kliknutím (v zošite, kde máte kód v backende).
Takže keď budete mať nabudúce údaje, ktoré musíte vyplniť, stačí urobiť výber a potom kliknúť na tlačidlo makra na paneli s nástrojmi Rýchly prístup.
Toto makro môžete tiež pridať do osobného zošita makra a potom ho použiť v ľubovoľnom zošite vo vašom systéme.
Kliknutím sem získate ďalšie informácie o osobných zošitoch makier a o tom, ako do nich pridať kód.
Metóda 3 - Vyplňte pomocou Power Query
Power Query má vstavanú funkciu, ktorá vám umožňuje vyplnenie jediným kliknutím.
Použitie Power Query sa odporúča vtedy, ak ho používate na transformáciu údajov alebo kombinovanie údajov z viacerých pracovných hárkov alebo viacerých zošitov.
Ako súčasť vášho existujúceho pracovného toku môžete v Power Query použiť možnosť vyplnenia na rýchle vyplnenie prázdnych buniek.
Ak chcete používať Power Query, odporúča sa, aby boli vaše údaje vo formáte tabuľky Excel. Ak svoje údaje nemôžete previesť do tabuľky programu Excel, budete musieť pre tieto údaje vytvoriť pomenovaný rozsah a potom tento pomenovaný rozsah použiť v Power Query.
Nasleduje súbor údajov, ktoré som už skonvertoval na tabuľku programu Excel. Môžete to urobiť tak, že vyberiete množinu údajov, prejdete na kartu Vložiť a potom kliknete na ikonu Tabuľka.
Nasledujú kroky na použitie Power Query na vyplnenie údajov do nasledujúcej hodnoty:
- Vyberte ľubovoľnú bunku v súbore údajov
- Kliknite na kartu Údaje
- V skupine Získať a transformovať údaje kliknite na položku „Z hárka“. Tým sa otvorí editor Power Query. Upozorňujeme, že prázdne bunky v Power Query zobrazujú hodnotu „null“
- V editore Power Query vyberte stĺpce, pre ktoré chcete vyplniť. Ak to chcete urobiť, podržte kláves Control a potom kliknite na hlavičku stĺpcov, ktoré chcete vybrať. V našom prípade by to boli stĺpce Dátum a Produkt.
- Kliknite pravým tlačidlom myši na niektorú z vybratých hlavičiek
- Prejdite na možnosť „Vyplniť“ a potom kliknite na „Dole“. Tým by sa vyplnili údaje vo všetkých prázdnych bunkách
- Kliknite na kartu Súbor a potom kliknite na položku „Zavrieť a načítať“. Tým sa do pracovného zošita s výslednou tabuľkou vloží nový pracovný hárok
Aj keď to môže znieť trochu prehnane, jednou veľkou výhodou použitia Power Query je, že vám umožní rýchlo aktualizovať výsledné údaje v prípade, že sa pôvodné údaje zmenia.
Ak napríklad k pôvodným údajom pridáte ďalšie záznamy, nemusíte vyššie uvedené kroky opakovať. Na výslednú tabuľku môžete jednoducho kliknúť pravým tlačidlom myši a potom kliknúť na položku Obnoviť.
Aj keď metóda Power Query funguje dobre, je najlepšie ju použiť, keď už vo svojom pracovnom postupe Power Query používate.
Ak máte iba množinu údajov, do ktorej chcete vyplniť prázdne bunky, bolo by vhodnejšie použiť špeciálnu metódu Prejsť na alebo metódu VBA (popísanú vyššie).
Toto sú tri jednoduché spôsoby, ktoré môžete použiť vyplňte prázdne bunky do ďalšej hodnoty v programe Excel.
Dúfam, že ste našli tento návod užitočný.
