Üres sorok és oszlopok eltávolítása az adatokból

Az üres sorok és oszlopok sok esetben fájdalmat okozhatnak a táblázatokban. A szabványos rendezési, szűrési, összegzési, pivot táblák készítésére szolgáló funkciók stb. az üres sorokat és oszlopokat táblázattörésként érzékelik, anélkül, hogy a mögöttük lévő adatokat felvennék. Ha sok ilyen rés van, akkor ezek manuális eltávolítása nagyon költséges lehet, és nem fog működni, ha szűréssel egyszerre „tömegesen” távolítjuk el, mert a szűrő is „megbotlik” a szünetekben.

Nézzünk több módszert a probléma megoldására.

1. módszer. Keressen üres cellákat

Lehet, hogy nem ez a legkényelmesebb, de mindenképpen a legegyszerűbb módot érdemes megemlíteni.

Tegyük fel, hogy egy ilyen táblázattal van dolgunk, amely sok üres sort és oszlopot tartalmaz (az áttekinthetőség kedvéért kiemelve):

Tegyük fel, hogy biztosak vagyunk abban, hogy táblázatunk első oszlopa (B oszlop) mindig egy város nevét tartalmazza. Ekkor az üres cellák ebben az oszlopban a szükségtelen üres sorok jelei lesznek. Az összes gyors eltávolításához tegye a következőket:

1. Tartomány kiválasztása városokkal (B2:B26)
2. Nyomja meg a gombot F5 majd nyomja meg a gombot Kiemel (Ugrás a Különleges oldalra) vagy válassza ki a lapon Kezdőlap — Keresés és kiválasztás — Cellacsoport kiválasztása (Kezdőlap — Find&Select — Ugrás a különleges oldalra).
3. A megnyíló ablakban válassza ki a lehetőséget Üres cellák (Üresek) és nyomja meg az OK – táblázatunk első oszlopában lévő összes üres cellát ki kell jelölni.
4. Most válassza ki a lapon Kezdőlap parancs Törlés – Sorok törlése a lapról (Törlés – sorok törlése) vagy nyomja meg a billentyűparancsot Ctrl+mínusz – és a feladatunk megoldódott.

Természetesen az üres oszlopoktól is pontosan ugyanúgy megszabadulhatunk, a táblázat fejlécet használva alapul.

2. módszer: Keressen üres sorokat

Amint arra már rájöttél, az előző módszer csak akkor működik, ha adataink szükségszerűen tartalmaznak teljesen kitöltött sorokat és oszlopokat, amelyekre az üres cellák keresésekor rá lehet akasztani. De mi van akkor, ha nincs ilyen bizalom, és az adatok üres cellákat is tartalmazhatnak?

Vessen egy pillantást például a következő táblázatra egy ilyen esetre:

Itt a megközelítés egy kicsit bonyolultabb lesz:

1. Írja be az A2 cellába a függvényt COUNT (COUNTA), amely kiszámítja a kitöltött cellák számát a jobb oldali sorban, és lemásolja ezt a képletet a teljes táblázatba:
2. Válassza ki az A2 cellát, és kapcsolja be a szűrőt a paranccsal Adatok – Szűrő (Adatok – Szűrő) vagy billentyűparancsot Ctrl+műszak+L.
3. Szűrjük ki a nullákat a számított oszlop alapján, azaz minden olyan sort, ahol nincs adat.
4. Marad a szűrt sorok kiválasztása és törlése a paranccsal Kezdőlap — Törlés -' Sorok törlése a lapról (Kezdőlap — Törlés — Sorok törlése) vagy billentyűparancsot Ctrl+mínusz.
5. Kikapcsoljuk a szűrőt, és üres sorok nélkül kapjuk meg adatainkat.

Sajnos ezt a trükköt oszlopokkal már nem lehet megcsinálni – az Excel még nem tanulta meg az oszlopok szerinti szűrést.

3. módszer. Makró a lap összes üres sorának és oszlopának eltávolításához

A feladat automatizálásához egy egyszerű makrót is használhat. Nyomja meg a billentyűparancsot más+F11 vagy válasszon a lapról fejlesztő — Visual Basic (Fejlesztő – Visual Basic szerkesztő). Ha lapok fejlesztő nem látható, ezen keresztül engedélyezheti Fájl – Beállítások – Szalagbeállítás (Fájl — Beállítások — Szalag testreszabása).

A megnyíló Visual Basic szerkesztő ablakban válassza ki a menüparancsot Beszúrás – Modul és a megjelenő üres modulba másolja és illessze be a következő sorokat:

   Sub DeleteEmpty() Dim r As Long, rng As Range 'удаляем пустые строки For r = 1 To ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count If Application.CountA(Rows(r)) = 0 rng Semmi, akkor Set rng = Sorok(r) Else Set rng = Unió(rng, Sorok(r)) Vége, ha Következő r Ha nem rng Semmi Akkor rng.Delete 'удаляем пустые столбцы Set rng = Semmi For r = 1 To ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count If Application.CountA(Columns(r)) = 0 then If rng Semmi then Set rng = Columns(r) Else Set rng = Union(rng, Columns( r)) End If Next r Ha nem rng Nincs semmi Akkor rng.Delete End Sub  

Zárja be a szerkesztőt, és térjen vissza az Excelhez. 

Most nyomja meg a kombinációt más+F8 vagy gombot makrók lap fejlesztő. A megnyíló ablak felsorolja az összes jelenleg futtatható makrót, beleértve az imént létrehozott makrót is. DeleteEmpty. Válassza ki, és kattintson a gombra futás (fuss) – a lap összes üres sora és oszlopa azonnal törlődik.

4. módszer: Power Query

A probléma megoldásának másik módja és egy nagyon gyakori forgatókönyv az üres sorok és oszlopok eltávolítása a Power Queryben.

Először töltsük be a táblázatunkat a Power Query Query Editorba. A Ctrl+T billentyűkombinációval dinamikus „okossá” alakíthatja, vagy egyszerűen válassza ki az adattartományunkat és adjon neki nevet (pl. dátum) a képletsorban, konvertálva a namedre:

Most az Adatok – Adatok beszerzése – Táblázatból/tartományból (Adatok – Adatok – Táblázatból/tartományból) parancsot használjuk, és mindent betöltünk a Power Querybe:

Akkor minden egyszerű:

1. Az üres sorokat a Kezdőlap – Sorok csökkentése – Sorok törlése – Üres sorok törlése (Kezdő – Sorok eltávolítása – Üres sorok eltávolítása) paranccsal töröljük.
2. Kattintson a jobb gombbal az első Város oszlop fejlécére, és válassza a helyi menüből a Más oszlopok elforgatása parancsot. A táblázatunk a következő lesz, ahogyan technikailag helyesen nevezik, normalizált – három oszlopra konvertálva: város, hónap és érték a város metszéspontjából és hónap az eredeti táblázatból. Ennek a műveletnek a sajátossága a Power Queryben, hogy kihagyja az üres cellákat a forrásadatokban, amire szükségünk van:
3. Most a fordított műveletet hajtjuk végre – a kapott táblázatot visszafordítjuk kétdimenzióssá, hogy visszaállítsuk eredeti formáját. Válassza ki a hónapokat tartalmazó oszlopot és a lapon Átalakítás válassz csapatot Pivot oszlop (Átalakítás – Pivot Oszlop). A megnyíló ablakban értékoszlopként válassza ki az utolsót (Érték), a speciális opcióknál pedig a műveletet Ne összesítse (Ne összesíts):
4. Az eredményt vissza kell tölteni az Excelbe a paranccsal Kezdőlap — Bezárás és betöltés — Bezárás és betöltés… (Kezdőlap – Bezárás&Betöltés – Bezárás&Betöltés…)

• Mi az a makró, hogyan működik, hova kell másolni egy makró szövegét, hogyan kell futtatni egy makrót?
• A lista összes üres cellájának kitöltése a szülőcellák értékeivel
• Az összes üres cella eltávolítása egy adott tartományból
• A munkalap összes üres sorának eltávolítása a PLEX kiegészítővel