Obsah
Prázdné řádky a sloupce mohou v tabulkách v mnoha případech potrápit. Standardní funkce pro řazení, filtrování, sumarizaci, vytváření kontingenčních tabulek atd. vnímají prázdné řádky a sloupce jako zlom tabulky, aniž by sbíraly data umístěná dále za nimi. Pokud existuje mnoho takových mezer, může být jejich ruční odstranění velmi nákladné a nebude fungovat odstranit všechny najednou „hromadně“ pomocí filtrování, protože filtr také „zakopne“ při přestávkách.
Podívejme se na několik způsobů, jak tento problém vyřešit.
Metoda 1. Hledání prázdných buněk
Možná to není nejpohodlnější, ale rozhodně stojí za zmínku nejjednodušší způsob.
Předpokládejme, že máme co do činění s takovou tabulkou, která obsahuje mnoho prázdných řádků a sloupců (pro přehlednost zvýrazněno):
Předpokládejme, že jsme si jisti, že první sloupec naší tabulky (sloupec B) vždy obsahuje název města. Pak prázdné buňky v tomto sloupci budou známkou zbytečných prázdných řádků. Chcete-li je všechny rychle odstranit, postupujte takto:
- Vyberte rozsah s městy (B2:B26)
- Stiskněte klávesu F5 a potom stiskněte tlačítko Zdůraznit (Přejít na speciál) nebo vyberte na kartě Domů — Najít a vybrat — Vyberte skupinu buněk (Domů — Najít a vybrat — Přejít na speciální).
- V okně, které se otevře, vyberte možnost Prázdné buňky (Prázdné) a stiskněte OK – měly by být vybrány všechny prázdné buňky v prvním sloupci naší tabulky.
- Nyní vyberte na kartě Domů Příkaz Smazat – Odstraní řádky z listu (Smazat — Smazat řádky) nebo stiskněte klávesovou zkratku Ctrl+bez – a náš úkol je vyřešen.
Prázdných sloupců se samozřejmě můžete zbavit úplně stejným způsobem, jako základ použijete záhlaví tabulky.
Metoda 2: Hledání prázdných řádků
Jak jste již možná zjistili, předchozí metoda bude fungovat pouze v případě, že naše data nutně obsahují plně vyplněné řádky a sloupce, které lze při hledání prázdných buněk zavěsit. Ale co když taková spolehlivost neexistuje a data mohou obsahovat také prázdné buňky?
Podívejte se například na následující tabulku, kde je přesně takový případ:
Zde bude přístup trochu složitější:
- Do buňky A2 zadejte funkci COUNT (COUNTA), který spočítá počet vyplněných buněk v řádku vpravo a zkopíruje tento vzorec dolů do celé tabulky:
- Vyberte buňku A2 a zapněte filtr příkazem Data – Filtr (Data — Filtr) nebo klávesovou zkratkou Ctrl+směna+L.
- Vyfiltrujme nuly podle počítaného sloupce, tedy všechny řádky, kde nejsou žádná data.
- Zbývá vybrat vyfiltrované řádky a smazat je příkazem Domů — Odstranit -' Odstraní řádky z listu (Domů — Smazat — Smazat řádky) nebo klávesovou zkratkou Ctrl+bez.
- Vypneme filtr a získáme naše data bez prázdných řádků.
Tento trik už se sloupci bohužel dělat nelze – Excel se ještě nenaučil filtrovat podle sloupců.
Metoda 3. Makro pro odstranění všech prázdných řádků a sloupců na listu
K automatizaci této úlohy můžete také použít jednoduché makro. Stiskněte klávesovou zkratku Další+F11 nebo vyberte z karty vývojka — Visual Basic (Vývojář — Visual Basic Editor). Pokud tabulátory vývojka není viditelný, můžete jej povolit prostřednictvím Soubor – Možnosti – Nastavení pásu karet (Soubor — Možnosti — Přizpůsobit pás karet).
V okně editoru jazyka Visual Basic, které se otevře, vyberte příkaz nabídky Vložit – Modul a do prázdného modulu, který se objeví, zkopírujte a vložte následující řádky:
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 Is Nothing Then Set rng = Rows(r) Else Set rng = Union(rng, Rows(r)) End If Next r If Not rng Is Nothing Then rng.Delete 'удаляем пустые столбцы Set rng = Nothing For r = 0 To 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
Zavřete editor a vraťte se do Excelu.
Nyní stiskněte kombinaci Další+F8 nebo tlačítko makra Karta vývojka. V okně, které se otevře, se zobrazí seznam všech maker, která jsou aktuálně dostupná ke spuštění, včetně makra, které jste právě vytvořili. SmazatEmpty. Vyberte jej a klikněte na tlačítko Běh (běh) – všechny prázdné řádky a sloupce na listu budou okamžitě odstraněny.
Metoda 4: Power Query
Dalším způsobem, jak vyřešit náš problém a velmi častým scénářem, je odstranění prázdných řádků a sloupců v Power Query.
Nejprve načteme naši tabulku do editoru dotazů Power Query. Můžete jej převést na dynamický „smart“ klávesovou zkratkou Ctrl+T nebo jen vybrat náš datový rozsah a pojmenovat jej (např. Data) v řádku vzorců, převod na pojmenované:
Nyní použijeme příkaz Data – Získat data – Z tabulky / rozsahu (Data – Získat data – Z tabulky / rozsah) a vše nahrajeme do Power Query:
Pak je vše jednoduché:
- Prázdné řádky mažeme příkazem Home – Reduce lines – Delete lines – Delete empty lines (Home – Remove Rows – Remove empty lines).
- Klikněte pravým tlačítkem na záhlaví prvního sloupce Město a z kontextové nabídky vyberte příkaz Unpivot Other Columns. Náš stůl bude, jak se tomu odborně správně říká, normalizovaná – převedeno do tří sloupců: město, měsíc a hodnota z průsečíku města a měsíce z původní tabulky. Zvláštností této operace v Power Query je, že přeskakuje prázdné buňky ve zdrojových datech, což je to, co potřebujeme:
- Nyní provedeme opačnou operaci – výslednou tabulku převedeme zpět na dvourozměrnou, abychom ji vrátili do původní podoby. Vyberte sloupec s měsíci a na kartě Proměna vybrat tým Kontingenční sloupec (Transformace – otočný sloupec). V okně, které se otevře, jako sloupec hodnot vyberte poslední (Value) a v rozšířených možnostech – operaci Neagregovat (Neshrnovat):
- Zbývá nahrát výsledek příkazem zpět do Excelu Domů — Zavřít a načíst — Zavřít a načíst v… (Domů — Zavřít&Načíst — Zavřít&Načíst do…)
- Co je makro, jak funguje, kam zkopírovat text makra, jak makro spustit?
- Vyplnění všech prázdných buněk v seznamu hodnotami nadřazených buněk
- Odstranění všech prázdných buněk z daného rozsahu
- Odstranění všech prázdných řádků v listu pomocí doplňku PLEX