Usuwanie pustych wierszy i kolumn w danych

Puste wiersze i kolumny mogą w wielu przypadkach być utrapieniem w tabelach. Standardowe funkcje sortowania, filtrowania, podsumowywania, tworzenia tabel przestawnych itp. postrzegają puste wiersze i kolumny jako podział tabeli, bez pobierania danych znajdujących się dalej za nimi. Jeśli jest wiele takich luk, to ręczne ich usunięcie może być bardzo kosztowne, a usuwanie wszystkich na raz „luzem” za pomocą filtrowania, ponieważ filtr również „potyka się” podczas przerw.

Przyjrzyjmy się kilku sposobom rozwiązania tego problemu.

Metoda 1. Wyszukaj puste komórki

Może nie jest to najwygodniejsze, ale zdecydowanie warto wspomnieć o najłatwiejszym sposobie.

Załóżmy, że mamy do czynienia z taką tabelą zawierającą w środku wiele pustych wierszy i kolumn (podświetlonych dla jasności):

Załóżmy, że jesteśmy pewni, że pierwsza kolumna naszej tabeli (kolumna B) zawsze zawiera nazwę miasta. Wtedy puste komórki w tej kolumnie będą oznaką niepotrzebnych pustych wierszy. Aby szybko je wszystkie usunąć, wykonaj następujące czynności:

  1. Wybierz zakres z miastami (B2:B26)
  2. Wciśnij klawisz F5 a następnie naciśnij Atrakcja (Przejdź do Specjalne) lub wybierz na karcie Strona główna — Znajdź i wybierz — Wybierz grupę komórek (Strona główna — Znajdź i wybierz — Przejdź do opcji specjalnych).
  3. W oknie, które się otworzy, wybierz opcję Puste komórki (Puste) i naciśnij OK – należy zaznaczyć wszystkie puste komórki w pierwszej kolumnie naszej tabeli.
  4. Teraz wybierz na karcie Strona główna Command Usuń – Usuń wiersze z arkusza (Usuń — Usuń wiersze) lub naciśnij skrót klawiaturowy Ctrl+minus – a nasze zadanie rozwiązane.

Oczywiście możesz pozbyć się pustych kolumn dokładnie w ten sam sposób, używając jako podstawy nagłówka tabeli.

Metoda 2: Wyszukaj puste wiersze

Jak być może już się zorientowałeś, poprzednia metoda zadziała tylko wtedy, gdy nasze dane koniecznie zawierają w pełni wypełnione wiersze i kolumny, do których można się przyczepić podczas wyszukiwania pustych komórek. Ale co, jeśli nie ma takiej pewności, a dane mogą również zawierać puste komórki?

Spójrz na poniższą tabelę, na przykład, dla takiego właśnie przypadku:

Tutaj podejście będzie nieco trudniejsze:

  1. Wprowadź w komórce A2 funkcję COUNT (ILE.LICZB), który obliczy liczbę wypełnionych komórek w wierszu po prawej stronie i skopiuje tę formułę do całej tabeli:
  2. Wybierz komórkę A2 i włącz filtr za pomocą polecenia Dane – Filtr (Dane — Filtr) lub skrót klawiaturowy Ctrl+Shift+L.
  3. Odfiltrujmy zera według kolumny obliczeniowej, czyli wszystkich wierszy, w których nie ma danych.
  4. Pozostaje wybrać przefiltrowane linie i usunąć je za pomocą polecenia Strona główna — Usuń —' Usuń wiersze z arkusza (Strona główna — Usuń — Usuń wiersze) lub skrót klawiaturowy Ctrl+minus.
  5. Wyłączamy filtr i otrzymujemy nasze dane bez pustych linii.

Niestety tej sztuczki nie da się już wykonać z kolumnami – Excel nie nauczył się jeszcze filtrować według kolumn.

Metoda 3. Makro do usuwania wszystkich pustych wierszy i kolumn z arkusza

Możesz również użyć prostego makra, aby zautomatyzować to zadanie. Naciśnij skrót klawiaturowy inny+F11 lub wybierz z zakładki wywoływacz - Visual Basic (Deweloper — Edytor Visual Basic). Jeśli karty wywoływacz nie jest widoczny, możesz go włączyć Plik – Opcje – Konfiguracja wstążki (Plik — Opcje — Dostosuj wstążkę).

W otwartym oknie edytora Visual Basic wybierz polecenie menu Wstaw – moduł i w pustym module, który się pojawi, skopiuj i wklej następujące wiersze:

   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 Wtedy If rng To Nic To ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count Jeśli Application.CountA(Columns(r)) = 1 Wtedy jeśli rng jest niczym, a następnie ustaw rng = Columns(r) Inaczej Ustaw rng = Union(rng, Columns( r)) Koniec, jeśli następny r Jeśli nie rng to nic, to rng.Delete End Sub

Zamknij edytor i wróć do Excela. 

Teraz naciśnij kombinację inny+F8 lub przycisk Makra Zakładka wywoływacz. W oknie, które zostanie otwarte, pojawi się lista wszystkich makr aktualnie dostępnych do uruchomienia, w tym właśnie utworzone makro. UsuńPuste. Wybierz i kliknij przycisk run (biegać) – wszystkie puste wiersze i kolumny w arkuszu zostaną natychmiast usunięte.

Metoda 4: Power Query

Innym sposobem rozwiązania naszego problemu i bardzo częstym scenariuszem jest usunięcie pustych wierszy i kolumn w dodatku Power Query.

Najpierw załadujmy naszą tabelę do edytora Power Query Query. Możesz przekonwertować go na dynamiczny „inteligentny” za pomocą skrótu klawiaturowego Ctrl+T lub po prostu wybrać nasz zakres danych i nadać mu nazwę (na przykład Dane) na pasku formuły, konwertując na nazwany:

Teraz używamy polecenia Dane – Pobierz dane – Z tabeli/zakresu (Dane – Pobierz dane – Z tabeli/zakresu) i ładujemy wszystko do Power Query:

Wtedy wszystko jest proste:

  1. Puste linie usuwamy za pomocą polecenia Home – Zmniejsz linie – Usuń linie – Usuń puste linie (Home – Remove Rows – Remove empty rows).
  2. Kliknij prawym przyciskiem myszy nagłówek pierwszej kolumny Miasto i wybierz polecenie Unpivot Other Columns z menu kontekstowego. Nasz stół będzie, jak to technicznie poprawnie nazwano, znormalizowany – przeliczone na trzy kolumny: miasto, miesiąc i wartość z przecięcia miasta i miesiąc z oryginalnej tabeli. Specyfiką tej operacji w dodatku Power Query jest to, że pomija puste komórki w danych źródłowych, czego potrzebujemy:
  3. Teraz wykonujemy operację odwrotną – wynikową tabelę zamieniamy z powrotem na dwuwymiarową, aby przywrócić jej pierwotną postać. Wybierz kolumnę z miesiącami i na zakładce Transformacja wybierz drużynę Kolumna obrotowa (Przekształć — kolumna obrotowa). W oknie, które się otworzy, jako kolumnę wartości wybierz ostatnią (Wartość), a w opcjach zaawansowanych – operację Nie agreguj (Nie agreguj):
  4. Pozostaje przesłać wynik z powrotem do programu Excel za pomocą polecenia Strona główna — Zamknij i załaduj — Zamknij i załaduj w… (Strona główna — Zamknij i załaduj — Zamknij i załaduj do…)

  • Co to jest makro, jak działa, gdzie skopiować tekst makra, jak uruchomić makro?
  • Wypełnianie wszystkich pustych komórek na liście wartościami komórek nadrzędnych
  • Usunięcie wszystkich pustych komórek z danego zakresu
  • Usuwanie wszystkich pustych wierszy w arkuszu za pomocą dodatku PLEX

Dodaj komentarz