Usuń wiersze na podstawie wartości komórki (lub warunku) w programie Excel (łatwy przewodnik)

Podczas pracy z dużymi zestawami danych może być konieczne szybkie usuwanie wierszy na podstawie zawartych w nich wartości komórek (lub na podstawie warunku).

Rozważmy na przykład następujące przykłady:

1. Masz dane przedstawiciela handlowego i chcesz usunąć wszystkie rekordy dla określonego regionu lub produktu.
2. Chcesz usunąć wszystkie rekordy, w których wartość sprzedaży jest mniejsza niż 100.
3. Chcesz usunąć wszystkie wiersze, w których znajduje się pusta komórka.

Istnieje wiele sposobów na skórowanie tego kota danych w programie Excel.

Wybrana metoda usunięcia wierszy będzie zależeć od struktury danych i wartości komórki lub warunku, na podstawie którego chcesz usunąć te wiersze.

W tym samouczku pokażę Ci wiele sposobów usuwania wierszy w programie Excel na podstawie wartości komórki lub warunku.

Filtruj wiersze na podstawie wartości/warunku, a następnie je usuń

Jednym z najszybszych sposobów usuwania wierszy zawierających określoną wartość lub spełniających dany warunek jest ich filtrowanie. Po przefiltrowaniu danych możesz usunąć wszystkie te wiersze (pozostałe wiersze pozostają nienaruszone).

Filtr Excel jest dość wszechstronny i możesz filtrować na podstawie wielu kryteriów (takich jak tekst, liczby, daty i kolory)

Zobaczmy dwa przykłady, w których możesz filtrować wiersze i je usuwać.

Usuń wiersze zawierające określony tekst

Załóżmy, że masz zestaw danych, jak pokazano poniżej i chcesz usunąć wszystkie wiersze, w których region znajduje się na Środkowym Zachodzie (w kolumnie B).

Chociaż w tym małym zestawie danych możesz ręcznie usunąć te wiersze, często Twoje zestawy danych będą ogromne, a ręczne usuwanie wierszy nie będzie możliwe.

W takim przypadku można przefiltrować wszystkie rekordy, w których region znajduje się na Środkowym Zachodzie, a następnie usunąć wszystkie te wiersze (zachowując pozostałe wiersze w stanie nienaruszonym).

Poniżej znajdują się kroki, aby usunąć wiersze na podstawie wartości (wszystkie rekordy Mid-West):

1. Wybierz dowolną komórkę w zestawie danych, z której chcesz usunąć wiersze
2. Kliknij kartę Dane
3. W grupie „Sortuj i filtruj” kliknij ikonę Filtruj. Spowoduje to zastosowanie filtrów do wszystkich komórek nagłówków w zbiorze danych
4. Kliknij ikonę Filtruj w komórce nagłówka regionu (jest to mała ikona trójkąta skierowanego w dół w prawym górnym rogu komórki)
5. Usuń zaznaczenie wszystkich innych opcji z wyjątkiem opcji Mid-West (szybkim sposobem na to jest kliknięcie opcji Select All, a następnie kliknięcie opcji Mid-West). Spowoduje to przefiltrowanie zbioru danych i wyświetlenie tylko rekordów dla regionu środkowo-zachodniego.
6. Wybierz wszystkie przefiltrowane rekordy
7. Kliknij prawym przyciskiem myszy dowolną z wybranych komórek i kliknij „Usuń wiersz”
8. W otwartym oknie dialogowym kliknij OK. W tym momencie nie zobaczysz żadnych rekordów w zbiorze danych.
9. Kliknij kartę Dane i kliknij ikonę Filtruj. Spowoduje to usunięcie filtra i zobaczysz wszystkie rekordy oprócz usuniętych.

Powyższe kroki najpierw filtrują dane na podstawie wartości komórki (lub mogą to być inne warunki, takie jak po/przed datą lub większe/mniejsze od liczby). Gdy masz już rekordy, po prostu je usuwasz.

Kilka przydatnych skrótów, które warto znać, aby przyspieszyć proces:

1. Control + Shift + L zastosować lub usunąć filtr
2. Kontrola + - (przytrzymaj klawisz kontrolny i naciśnij klawisz minus), aby usunąć wybrane komórki/wiersze

W powyższym przykładzie miałem tylko cztery różne regiony i mogłem ręcznie wybrać i usunąć zaznaczenie z listy Filtr (w krokach 5 powyżej).

Jeśli masz wiele kategorii/regionów, możesz wpisać nazwę w polu tuż nad polem (które ma te nazwy regionów), a program Excel wyświetli tylko te rekordy, które pasują do wprowadzonego tekstu (jak pokazano poniżej). Gdy masz już tekst, na podstawie którego chcesz filtrować, naciśnij klawisz Enter.

Pamiętaj, że po usunięciu wiersza wszystko, co możesz mieć w innych komórkach w tych wierszach, zostanie utracone. Jednym ze sposobów obejścia tego problemu jest utworzenie kopii danych w innym arkuszu i usunięcie wierszy w skopiowanych danych. Po zakończeniu skopiuj go z powrotem w miejsce oryginalnych danych.

Lub

Możesz użyć metod przedstawionych w dalszej części tego samouczka (przy użyciu metody Sort lub metody Znajdź wszystko)

Usuń wiersze na podstawie warunku liczbowego

Tak jak użyłem metody filtrowania do usunięcia wszystkich wierszy zawierających tekst Mid-West, możesz również użyć warunku liczby (lub warunku daty).

Załóżmy na przykład, że mam poniższy zestaw danych i chcę usunąć wszystkie wiersze, w których wartość sprzedaży jest mniejsza niż 200.

Poniżej znajdują się kroki, aby to zrobić:

1. Wybierz dowolną komórkę w danych
2. Kliknij kartę Dane
3. W grupie „Sortuj i filtruj” kliknij ikonę Filtruj. Spowoduje to zastosowanie filtrów do wszystkich komórek nagłówków w zbiorze danych
4. Kliknij ikonę Filtruj w komórce nagłówka Sprzedaż (jest to mała ikona trójkąta skierowanego w dół w prawym górnym rogu komórki)
5. Najedź kursorem na opcję Filtry liczbowe. Spowoduje to wyświetlenie wszystkich opcji filtrów związanych z liczbą w programie Excel.
6. Kliknij opcję „Mniej niż”.
7. W otwartym oknie dialogowym „Autofiltr niestandardowy” wprowadź w polu wartość „200”
8. Kliknij OK. To przefiltruje i pokaże tylko te rekordy, w których wartość sprzedaży jest mniejsza niż 200
9. Wybierz wszystkie przefiltrowane rekordy
10. Kliknij prawym przyciskiem myszy dowolną komórkę i kliknij Usuń wiersz
11. W otwartym oknie dialogowym kliknij OK. W tym momencie nie zobaczysz żadnych rekordów w zbiorze danych.
12. Kliknij kartę Dane i kliknij ikonę Filtruj. Spowoduje to usunięcie filtra i zobaczysz wszystkie rekordy oprócz usuniętych.

Istnieje wiele filtrów liczbowych, których można używać w programie Excel - takich jak mniej niż / większe niż, równe / nie równe, między, top 10, powyżej lub poniżej średniej itp.

Uwaga: Możesz również użyć wielu filtrów. Na przykład możesz usunąć wszystkie wiersze, w których wartość sprzedaży jest większa niż 200, ale mniejsza niż 500. W takim przypadku musisz użyć dwóch warunków filtrowania. Okno dialogowe Autofiltr niestandardowy pozwala na posiadanie dwóch kryteriów filtrowania (AND oraz OR).

Podobnie jak filtry liczbowe, możesz również filtrować rekordy na podstawie daty. Na przykład, jeśli chcesz usunąć wszystkie rekordy z pierwszego kwartału, możesz to zrobić, wykonując te same czynności powyżej. Podczas pracy z filtrami daty program Excel automatycznie wyświetla odpowiednie filtry (jak pokazano poniżej).

Chociaż filtrowanie to świetny sposób na szybkie usuwanie wierszy na podstawie wartości lub warunku, ma jedną wadę - usuwa cały wiersz. Na przykład w poniższym przypadku usunie wszystkie dane znajdujące się na prawo od przefiltrowanego zestawu danych.

Co zrobić, jeśli chcę tylko usunąć rekordy ze zbioru danych, ale chcę zachować pozostałe dane w nienaruszonym stanie.

Nie możesz tego zrobić za pomocą filtrowania, ale możesz to zrobić za pomocą sortowania.

Posortuj zbiór danych, a następnie usuń wiersze

Chociaż sortowanie to kolejny sposób usuwania wierszy na podstawie wartości, ale w większości przypadków lepiej jest użyć metody filtrowania opisanej powyżej.

Ta technika sortowania jest zalecana tylko wtedy, gdy chcesz usunąć komórki z wartościami, a nie całe wiersze.

Załóżmy, że masz zestaw danych, jak pokazano poniżej, i chcesz usunąć wszystkie rekordy, w których region znajduje się na Środkowym Zachodzie.

Poniżej znajdują się kroki, aby to zrobić za pomocą sortowania:

1. Wybierz dowolną komórkę w danych
2. Kliknij kartę Dane
3. W grupie Sortuj i filtruj kliknij ikonę Sortuj.
4. W otwartym oknie dialogowym Sortuj wybierz opcję Region w kolumnie sortowania według.
5. W opcji Sortuj według upewnij się, że wybrana jest opcja Wartości komórek
6. W opcji Zamówienie wybierz od A do Z (lub Z do A, tak naprawdę nie ma znaczenia).
7. Kliknij OK. W ten sposób otrzymasz posortowany zestaw danych, jak pokazano poniżej (posortowany według kolumny B).
8. Wybierz wszystkie rekordy z regionem Środkowy Zachód (wszystkie komórki w wierszach, a nie tylko kolumna regionu)
9. Po wybraniu kliknij prawym przyciskiem myszy, a następnie kliknij Usuń. Otworzy się okno dialogowe Usuń.
10. Upewnij się, że zaznaczona jest opcja „Przesuń komórki w górę”.
11. Kliknij OK.

Powyższe kroki usunęłyby wszystkie rekordy, w których region znajdował się na Środkowym Zachodzie, ale nie usuwa całego wiersza. Tak więc, jeśli masz jakiekolwiek dane po prawej lub lewej stronie swojego zbioru danych, pozostaną one nienaruszone.

W powyższym przykładzie posortowałem dane na podstawie wartości komórki, ale możesz również użyć tych samych kroków do sortowania na podstawie liczb, dat, koloru komórki lub koloru czcionki itp.

Oto szczegółowy przewodnik dotyczący sortowania danych w programie Excel

Jeśli chcesz zachować oryginalną kolejność zestawu danych, ale usunąć rekordy na podstawie kryteriów, musisz mieć sposób na posortowanie danych z powrotem do oryginalnego. Aby to zrobić, przed posortowaniem danych dodaj kolumnę z numerami seryjnymi. Gdy skończysz z usuwaniem wierszy / rekordów, po prostu posortuj według tej dodatkowej kolumny, którą dodałeś.

Znajdź i wybierz komórki na podstawie wartości komórki, a następnie usuń wiersze

Excel ma funkcję Znajdź i zamień, która może być świetna, gdy chcesz znaleźć i zaznaczyć komórki o określonej wartości.

Po wybraniu tych komórek możesz łatwo usunąć wiersze.

Załóżmy, że masz zestaw danych, jak pokazano poniżej i chcesz usunąć wszystkie wiersze, w których region znajduje się na Środkowym Zachodzie.

Poniżej znajdują się kroki, aby to zrobić:

1. Wybierz cały zbiór danych
2. Kliknij kartę Strona główna
3. W grupie Edytowanie kliknij opcję „Znajdź i wybierz”, a następnie kliknij Znajdź (możesz również użyć skrótu klawiaturowego Control + F).
4. W oknie dialogowym Znajdź i zamień wpisz tekst „Mid-West” w polu „Znajdź co:”.
5. Kliknij Znajdź wszystko. Spowoduje to natychmiastowe wyświetlenie wszystkich wystąpień tekstu Mid-West, które program Excel był w stanie znaleźć.
6. Użyj skrótu klawiaturowego Control + A, aby zaznaczyć wszystkie komórki znalezione w programie Excel. Będziesz także mógł zobaczyć wszystkie wybrane komórki w zbiorze danych.
7. Kliknij prawym przyciskiem myszy dowolną z zaznaczonych komórek i kliknij Usuń. Otworzy się okno dialogowe Usuń.
8. Wybierz opcję „Cały wiersz”
9. Kliknij OK.

Powyższe kroki usunęłyby wszystkie komórki, w których wartość regionu to Środkowy zachód.

Uwaga: Ponieważ funkcja Znajdź i zamień może obsługiwać znaki wieloznaczne, możesz ich użyć podczas wyszukiwania danych w programie Excel. Na przykład, jeśli chcesz usunąć wszystkie wiersze, w których region znajduje się na Środkowym Zachodzie lub Południowo-Zachodnim, możesz użyć „*Zachód‘ jako tekst do wyszukania w oknie dialogowym Znajdź i zamień. To da ci wszystkie komórki, w których tekst kończy się słowem Zachód.

Usuń wszystkie wiersze z pustą komórką

Jeśli chcesz usunąć wszystkie wiersze, w których znajdują się puste komórki, możesz to łatwo zrobić za pomocą wbudowanej funkcji w programie Excel.

To jest Przejdź do komórek specjalnych opcja - która pozwala szybko zaznaczyć wszystkie puste komórki. A po wybraniu wszystkich pustych komórek usunięcie ich jest bardzo proste.

Załóżmy, że masz zestaw danych, jak pokazano poniżej, i chcę usunąć wszystkie wiersze, w których nie mam wartości sprzedaży.

Poniżej znajdują się kroki, aby to zrobić:

1. Wybierz cały zestaw danych (w tym przypadku A1:D16).
2. wciśnij F5 klucz. Spowoduje to otwarcie okna dialogowego „Przejdź do” (możesz również uzyskać to okno dialogowe z Strona główna -> Edycja -> Znajdź i wybierz -> Przejdź do).
3. W oknie dialogowym „Przejdź do” kliknij przycisk Specjalne. Spowoduje to otwarcie okna dialogowego „Przejdź do specjalnego”
4. W oknie dialogowym Przejdź do specjalnego wybierz „Puste”.
5. Kliknij OK.

Powyższe kroki zaznaczyłyby wszystkie komórki, które są puste w zestawie danych.

Po wybraniu pustych komórek kliknij prawym przyciskiem myszy dowolną z komórek i kliknij Usuń.

W oknie dialogowym Usuń wybierz opcję „Cały wiersz” i kliknij OK. Spowoduje to usunięcie wszystkich wierszy zawierających puste komórki.

Jeśli chcesz dowiedzieć się więcej o tej technice, napisałem szczegółowy samouczek dotyczący usuwania wierszy z pustymi komórkami. Obejmuje metodę „Przejdź do specjalnego”, a także metodę VBA do usuwania wierszy z pustymi komórkami.

Filtruj i usuwaj wiersze na podstawie wartości komórki (przy użyciu VBA)

Ostatnia metoda, którą pokażę, zawiera trochę VBA.

Możesz użyć tej metody, jeśli często musisz usuwać wiersze na podstawie określonej wartości w kolumnie. Możesz dodać kod VBA raz i dodać go do skoroszytu makr osobistych. W ten sposób będzie dostępny do użytku we wszystkich skoroszytach programu Excel.

Ten kod działa w taki sam sposób, jak opisana powyżej metoda Filter (z wyjątkiem tego, że wykonuje wszystkie kroki w backendzie i oszczędza kilka kliknięć).

Załóżmy, że masz zestaw danych, jak pokazano poniżej i chcesz usunąć wszystkie wiersze, w których region znajduje się na Środkowym Zachodzie.

Poniżej znajduje się kod VBA, który to zrobi.

Sub DeleteRowsWithSpecificText() 'Source:https://trumpexcel.com/delete-rows-based-on-cell-value/ ActiveCell.AutoFilter Field:=2, Criteria1:="Mid-West" ActiveSheet.AutoFilter.Range.Offset (1, 0).Rows.SpecialCells(xlCellTypeVisible).Delete End Sub

Powyższy kod wykorzystuje metodę VBA Autofilter, aby najpierw filtrować wiersze na podstawie określonych kryteriów (czyli „Mid-West”), a następnie wybrać wszystkie przefiltrowane wiersze i je usunąć.

Zauważ, że użyłem Offset w powyższym kodzie, aby upewnić się, że mój wiersz nagłówka nie zostanie usunięty.

Powyższy kod nie działa, jeśli Twoje dane znajdują się w tabeli programu Excel. Powodem tego jest to, że program Excel traktuje tabelę programu Excel jako obiekt listy. Jeśli więc chcesz usunąć wiersze znajdujące się w tabeli, musisz nieco zmodyfikować kod (opisany w dalszej części tego samouczka).

Przed usunięciem wierszy wyświetli monit, jak pokazano poniżej. Uważam to za przydatne, ponieważ pozwala mi dokładnie sprawdzić filtrowany wiersz przed usunięciem.

Pamiętaj, że usuwając wiersze za pomocą VBA, nie możesz cofnąć tej zmiany. Więc używaj tego tylko wtedy, gdy masz pewność, że działa tak, jak chcesz. Dobrym pomysłem jest również przechowywanie kopii zapasowej danych na wypadek, gdyby coś poszło nie tak.

Jeśli Twoje dane znajdują się w tabeli Excel, użyj poniższego kodu, aby usunąć wiersze z określoną wartością:

Sub DeleteRowsinTables() 'Źródło:https://trumpexcel.com/delete-rows-based-on-cell-value/ Dim Tbl As ListObject Set Tbl = ActiveSheet.ListObjects(1) ActiveCell.AutoFilter Field:=2, Criteria1: ="Mid-West" Tbl.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete End Sub

Ponieważ VBA traktuje tabelę Excel jako obiekt listy (a nie zakres), musiałem odpowiednio zmienić kod.

Gdzie umieścić kod VBA?

Ten kod należy umieścić w zapleczu VB Editor w module.

Poniżej znajdują się kroki, które pokażą Ci, jak to zrobić:

1. Otwórz skoroszyt, w którym chcesz dodać ten kod.
2. Użyj skrótu klawiaturowego ALT + F11, aby otworzyć okno edytora VBA.
3. W tym oknie edytora VBA po lewej stronie znajduje się panel „Eksplorator projektu” (który zawiera listę wszystkich skoroszytów i obiektów arkuszy roboczych). Kliknij prawym przyciskiem myszy dowolny obiekt w skoroszycie (w którym chcesz, aby ten kod działał), najedź kursorem na „Wstaw”, a następnie kliknij „Moduł”. Spowoduje to dodanie obiektu Module do skoroszytu, a także otworzy okno kodu modułu po prawej stronie
4. W oknie modułu (które pojawi się po prawej stronie) skopiuj i wklej powyższy kod.

Gdy masz już kod w edytorze VB, możesz go uruchomić za pomocą dowolnej z poniższych metod (upewnij się, że w zbiorze danych zaznaczono dowolną komórkę, na której chcesz uruchomić ten kod):

1. Wybierz dowolny wiersz w kodzie i naciśnij klawisz F5.
2. Kliknij przycisk Uruchom na pasku narzędzi w Edytorze VB
3. Przypisz makro do przycisku lub kształtu i uruchom je, klikając je w samym arkuszu
4. Dodaj go do paska narzędzi szybkiego dostępu i uruchom kod jednym kliknięciem.

W tym artykule możesz przeczytać wszystko o tym, jak uruchomić kod makra w programie Excel.

Uwaga: skoro skoroszyt zawiera kod makra VBA, należy go zapisać w formacie z obsługą makr (xlsm).