Odstránenie prázdnych riadkov a stĺpcov v údajoch

Prázdne riadky a stĺpce môžu v tabuľkách v mnohých prípadoch potrápiť. Štandardné funkcie na triedenie, filtrovanie, sumarizáciu, vytváranie kontingenčných tabuliek atď. vnímajú prázdne riadky a stĺpce ako zlom tabuľky, bez toho, aby zbierali dáta nachádzajúce sa ďalej za nimi. Ak je takýchto medzier veľa, ich ručné odstránenie môže byť veľmi nákladné a nebude fungovať odstrániť všetky naraz „hromadne“ pomocou filtrovania, pretože filter bude „zakopávať“ aj pri prestávkach.

Pozrime sa na niekoľko spôsobov, ako tento problém vyriešiť.

Metóda 1. Vyhľadajte prázdne bunky

Toto nemusí byť najpohodlnejšie, ale rozhodne stojí za zmienku najjednoduchší spôsob.

Predpokladajme, že máme čo do činenia s takouto tabuľkou, ktorá obsahuje veľa prázdnych riadkov a stĺpcov vo vnútri (zvýraznené kvôli prehľadnosti):

Predpokladajme, že sme si istí, že prvý stĺpec našej tabuľky (stĺpec B) vždy obsahuje názov mesta. Potom budú prázdne bunky v tomto stĺpci znakom nepotrebných prázdnych riadkov. Ak chcete rýchlo odstrániť všetky, postupujte takto:

  1. Vyberte rozsah s mestami (B2:B26)
  2. Stlačte kláves F5 a potom stlačte tlačidlo Zdôrazniť (Prejsť na špeciálne) alebo vyberte na karte Domov — Nájsť a vybrať — Vyberte skupinu buniek (Domov — Nájsť a vybrať — Prejsť na špeciálne).
  3. V okne, ktoré sa otvorí, vyberte možnosť Prázdne bunky (prázdne miesta) a stlačte OK – mali by byť označené všetky prázdne bunky v prvom stĺpci našej tabuľky.
  4. Teraz vyberte na karte Domov povel Odstrániť – vymaže riadky z hárka (Vymazať — Vymazať riadky) alebo stlačte klávesovú skratku ctrl+bez – a naša úloha je vyriešená.

Samozrejme, prázdnych stĺpcov sa môžete zbaviť úplne rovnakým spôsobom, pričom ako základ použijete hlavičku tabuľky.

Metóda 2: Vyhľadajte prázdne riadky

Ako ste už možno pochopili, predchádzajúci spôsob bude fungovať iba vtedy, ak naše údaje nevyhnutne obsahujú úplne vyplnené riadky a stĺpce, na ktoré sa dá pri hľadaní prázdnych buniek zavesiť. Čo ak však takáto dôvera neexistuje a údaje môžu obsahovať aj prázdne bunky?

Pozrite sa napríklad na nasledujúcu tabuľku, kde nájdete presne takýto prípad:

Tu bude prístup trochu zložitejší:

  1. Do bunky A2 zadajte funkciu COUNT (COUNTA), ktorý vypočíta počet vyplnených buniek v riadku napravo a skopíruje tento vzorec nadol do celej tabuľky:
  2. Vyberte bunku A2 a zapnite filter príkazom Údaje – Filter (Údaje – Filter) alebo klávesová skratka ctrl+smena+L.
  3. Vyfiltrujme nuly podľa vypočítaného stĺpca, teda všetky riadky, kde nie sú žiadne údaje.
  4. Zostáva vybrať filtrované riadky a odstrániť ich príkazom Domov — Odstrániť -' Odstránenie riadkov z hárka (Domov — Odstrániť — Odstrániť riadky) alebo klávesová skratka ctrl+bez.
  5. Vypneme filter a získame naše údaje bez prázdnych riadkov.

Bohužiaľ, tento trik sa už nedá robiť so stĺpcami – Excel sa ešte nenaučil filtrovať podľa stĺpcov.

Metóda 3. Makro na odstránenie všetkých prázdnych riadkov a stĺpcov na hárku

Na automatizáciu tejto úlohy môžete použiť aj jednoduché makro. Stlačte klávesovú skratku ostatné+F11 alebo vyberte z karty vývojka — Visual Basic (Vývojár — Visual Basic Editor). Ak karty vývojka nie je viditeľný, môžete ho povoliť Súbor – Možnosti – Nastavenie pásu (Súbor — Možnosti — Prispôsobiť pás s nástrojmi).

V okne editora Visual Basic, ktoré sa otvorí, vyberte príkaz ponuky Vložiť – Modul a do prázdneho modulu, ktorý sa zobrazí, skopírujte a prilepte nasledujúce riadky:

   Sub DeleteEmpty() Dim r As Long, rng As Range 'удаляем пустые строки For r = 1 To ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count If Application.Count) ThenA (Rows(Rows) rng Nie je nič Potom nastavte rng = Riadky(r) Inak Nastavte rng = Union(rng, Riadky(r)) Koniec Ak Ďalej r Ak nie rng Nie je nič Potom rng.Delete 'удаляем пустые столбцы Nastaviť rng = Nič Pre r = 0 Komu ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count If Application.CountA(Columns(r)) = 1 Then If rng Is Nothing Then Set rng = Columns(r) Else Set rng = Union(rng, Columns( r)) End If Next r If Not Rng Is Nothing Then rng.Delete End Sub

Zatvorte editor a vráťte sa do Excelu. 

Teraz stlačte kombináciu ostatné+F8 alebo tlačidlo Makrá pútko vývojka. V okne, ktoré sa otvorí, sa zobrazí zoznam všetkých makier, ktoré máte momentálne k dispozícii na spustenie, vrátane makra, ktoré ste práve vytvorili. DeleteEmpty. Vyberte ho a kliknite na tlačidlo beh (beh) – všetky prázdne riadky a stĺpce na hárku budú okamžite odstránené.

Metóda 4: Power Query

Ďalším spôsobom, ako vyriešiť náš problém a veľmi bežným scenárom, je odstránenie prázdnych riadkov a stĺpcov v Power Query.

Najprv načítajme našu tabuľku do editora dotazov Power Query. Môžete ho previesť na dynamický „smart“ klávesovou skratkou Ctrl+T alebo stačí vybrať náš rozsah údajov a pomenovať ho (napr. dátum) na paneli vzorcov, pričom sa konvertuje na pomenované:

Teraz použijeme príkaz Data – Get data – From table / range (Data – Get Data – From table / range) a všetko načítame do Power Query:

Potom je všetko jednoduché:

  1. Prázdne riadky vymažeme príkazom Home – Reduce lines – Delete lines – Delete empty lines (Home – Remove Rows – Remove empty lines).
  2. Kliknite pravým tlačidlom myši na hlavičku prvého stĺpca Mesto a z kontextovej ponuky vyberte príkaz Unpivot Other Columns. Náš stôl bude, ako sa odborne správne nazýva, normalizovaná – prevedené do troch stĺpcov: mesto, mesiac a hodnota z priesečníka mesta a mesiaca z pôvodnej tabuľky. Zvláštnosťou tejto operácie v Power Query je, že preskakuje prázdne bunky v zdrojových údajoch, čo potrebujeme:
  3. Teraz vykonáme opačnú operáciu – výslednú tabuľku premeníme späť na dvojrozmernú, aby sme ju vrátili do pôvodnej podoby. Vyberte stĺpec s mesiacmi a na karte Premena vybrať tím Kontingenčný stĺpec (Transformácia – otočný stĺpec). V okne, ktoré sa otvorí, ako stĺpec hodnôt vyberte poslednú (Hodnotu) a v rozšírených možnostiach operáciu Neagregovať (Nespájať):
  4. Zostáva nahrať výsledok späť do Excelu pomocou príkazu Domov — Zavrieť a načítať — Zavrieť a načítať v… (Domov — Zavrieť&Načítať — Zavrieť&Načítať do...)

  • Čo je makro, ako funguje, kam skopírovať text makra, ako spustiť makro?
  • Vyplnenie všetkých prázdnych buniek v zozname hodnotami rodičovských buniek
  • Odstránenie všetkých prázdnych buniek z daného rozsahu
  • Odstránenie všetkých prázdnych riadkov v pracovnom hárku pomocou doplnku PLEX

Nechaj odpoveď