Obejrzyj wideo - usuń co drugi wiersz w programie Excel
Czasami możesz potrzebować usunąć co drugi wiersz (tj. Alternatywne wiersze) lub co trzeci / czwarty / piąty wiersz w programie Excel.
Jednym z przypadków użycia tego może być sytuacja, gdy masz dane tygodniowe i chcesz mieć dane tylko z tygodni parzystych lub nieparzystych. Innym może być sytuacja, w której otrzymujesz zrzut danych z bazy danych, a wszystkie przydatne dane znajdują się tylko w co drugim lub trzecim wierszu.
Chociaż zawsze masz możliwość ręcznego wybierania i usuwania wierszy, jest to nieefektywne w przypadku dużego zestawu danych.
Są na to lepsze sposoby.
W tym samouczku pokażę Ci kilka sposobów usuwania każdego innego wiersza w programie Excel przy użyciu prostej techniki filtrowania. A jeśli nie masz nic przeciwko używaniu kodu makr VBA, podałem również krótki kod VBA, który robi to jednym kliknięciem. Metody przedstawione w tym samouczku działają we wszystkich wersjach programu Excel (2007, 2010, 2013 i 2016)
Chociaż usunięcie każdego innego wiersza w programie Excel może być dość proste, usunięcie każdej innej kolumny może być wyzwaniem. Dzieje się tak, ponieważ nie można filtrować kolumn w programie Excel (sposób filtrowania wierszy). W ostatniej części tego samouczka pokażę również kilka sposobów usuwania alternatywnych kolumn (lub każdej N-tej kolumny) w programie Excel.Usuń co drugi wiersz, filtrując zestaw danych (przy użyciu formuły)
Gdybyś mógł w jakiś sposób odfiltrować wszystkie parzyste lub nieparzyste wiersze, bardzo łatwo byłoby usunąć te wiersze/rekordy.
Chociaż nie ma wbudowanej funkcji, aby to zrobić, możesz użyć kolumny pomocniczej, aby najpierw podzielić wiersze na nieparzyste i parzyste, a następnie przefiltrować na podstawie wartości kolumny pomocniczej.
Załóżmy, że masz zestaw danych, jak pokazano poniżej, który zawiera dane sprzedaży dla każdego przedstawiciela handlowego dla dwóch regionów (USA i Kanada) i chcesz usunąć dane dla Kanady.
Poniżej znajdują się kroki, aby najpierw przefiltrować, a następnie usunąć co drugi wiersz (który zawiera dane dla Kanady):
- W komórce sąsiadującej z nagłówkiem ostatniej kolumny wprowadź tekst HelperColumn (lub dowolny tekst nagłówka, który ma mieć kolumna pomocnika)
- W komórce poniżej nagłówka kolumny pomocniczej wprowadź następującą formułę: =SIEDEM(WIERSZ()). Skopiuj tę formułę dla wszystkich komórek. Ta formuła zwraca TRUE dla wszystkich parzystych wierszy i FALSE dla wszystkich NIEPARZYSTYCH wierszy
- Wybierz cały zestaw danych (w tym komórki, w których wprowadziliśmy formułę w powyższym kroku).
- Kliknij kartę Dane
- Kliknij ikonę Filtruj. Spowoduje to zastosowanie filtra do wszystkich nagłówków w zbiorze danych
- Kliknij ikonę Filtruj w komórce HelperColumn.
- Usuń zaznaczenie opcji PRAWDA (więc wybrana jest tylko opcja FAŁSZ)
- Kliknij OK. Spowoduje to przefiltrowanie danych i wyświetlenie tylko tych rekordów, w których wartość HelperColumn jest FALSE.
- Wybierz wszystkie filtrowane komórki w kolumnie pomocniczej (z wyjątkiem nagłówka)
- Kliknij prawym przyciskiem myszy dowolną z wybranych komórek i kliknij „Usuń wiersz”
- W otwartym oknie dialogowym kliknij OK. Spowoduje to usunięcie wszystkich widocznych rekordów, a od teraz zobaczysz tylko wiersz nagłówka.
- Kliknij kartę Dane, a następnie kliknij ikonę Filtruj. To usunie filtr i będziesz mógł zobaczyć wszystkie pozostałe rekordy.
Powyższe kroki filtrują co drugi wiersz w zestawie danych, a następnie usuwają te wiersze.
Na tym etapie nie przejmuj się wartościami kolumn pomocniczych. Otrzymane dane zawierają tylko wiersze dla Stanów Zjednoczonych, a wszystkie wiersze z Kanady są usuwane. Możesz teraz usunąć kolumnę pomocnika.
Jeśli chcesz usunąć co drugi wiersz, począwszy od pierwszego wiersza, wybierz opcję TRUE w kroku 7 i usuń zaznaczenie opcji FALSE.
Uwaga: Usunięcie wierszy w programie Excel przy użyciu powyższej metody powoduje również usunięcie wszelkich danych, które możesz mieć w całości (oprócz danych w zestawie danych). Korzystając z tej metody, upewnij się, że po lewej i prawej stronie usuwanego zestawu danych nie ma nic.Chociaż powyższa metoda jest świetna, ma dwie wady:
- Musisz dodać nową kolumnę ( HelperColumn w naszym przykładzie powyżej)
- Może to być czasochłonne, jeśli musisz usunąć alternatywne wiersze, rób to często
Powiązany samouczek: Usuwanie wierszy na podstawie wartości komórki (lub warunku) w programie Excel
Usuń każdy n-ty wiersz, filtrując zestaw danych (przy użyciu formuły)
W powyższej metodzie pokazałem, jak usunąć każdy inny wiersz (wiersz alternatywny) w programie Excel.
Możesz także użyć tej samej logiki, aby usunąć co trzeci lub czwarty wiersz w programie Excel.
Załóżmy, że masz zestaw danych, jak pokazano poniżej i chcesz usunąć wszystkie co trzeci wiersz.
Czynności usuwania co trzeciego wiersza są prawie takie same jak opisane w powyższej sekcji (w przypadku usuwania alternatywnych wierszy). Jedyna różnica dotyczy formuły użytej w kroku 2.
Poniżej znajdują się kroki, aby to zrobić:
- W komórce sąsiadującej z nagłówkiem ostatniej kolumny wprowadź tekst HelperColumn (lub dowolny tekst nagłówka, który ma mieć kolumna pomocnika)
- W komórce poniżej nagłówka kolumny pomocniczej wprowadź następującą formułę: =MOD(WIERSZ();3)=1. Ta formuła zwraca TRUE dla co trzeciego wiersza i FALSE dla każdego drugiego wiersza.
- Wybierz cały zestaw danych (w tym komórki, w których wprowadziliśmy formułę w powyższym kroku).
- Kliknij kartę Dane
- Kliknij ikonę Filtruj. Spowoduje to zastosowanie filtra do wszystkich nagłówków w zbiorze danych
- Kliknij ikonę Filtruj w komórce HelperColumn.
- Usuń zaznaczenie opcji PRAWDA (więc wybrana jest tylko opcja FAŁSZ)
- Kliknij OK. Spowoduje to przefiltrowanie danych i wyświetlenie tylko tych rekordów, w których wartość HelperColumn ma wartość FALSE.
- Zaznacz wszystkie komórki w kolumnie pomocniczej
- Kliknij prawym przyciskiem myszy dowolną z zaznaczonych komórek i kliknij Usuń wiersz
- W otwartym oknie dialogowym kliknij OK. Spowoduje to usunięcie wszystkich widocznych rekordów, a od teraz zobaczysz tylko wiersz nagłówka.
- Kliknij kartę Dane, a następnie kliknij ikonę Filtruj. To usunie filtr i będziesz mógł zobaczyć wszystkie pozostałe rekordy.
Powyższe kroki usunęłyby co trzeci wiersz z zestawu danych, a wynikowe dane uzyskasz, jak pokazano poniżej.
Na tym etapie nie przejmuj się wartościami kolumn pomocniczych. Możesz teraz usunąć kolumnę pomocnika.
Formuła użyta w kroku 2 wykorzystuje funkcję MOD, która podaje resztę, gdy jedna liczba jest dzielona przez drugą. Tutaj użyłem funkcji ROW, aby uzyskać numer wiersza i jest on dzielony przez 3 (ponieważ chcemy usunąć co trzeci wiersz).
Ponieważ nasz zbiór danych zaczyna się od drugiego wiersza (tj. wiersz nr 2 zawiera pierwszy rekord), używam następującej formuły:=MOD(WIERSZ();3)=1
Przyrównuję to do 1 i dla każdego trzeciego wiersza formuła MOD da resztę jako 1.
Możesz odpowiednio dostosować formułę. Na przykład, jeśli pierwszy rekord zestawu danych zaczyna się od trzeciego wiersza, formuła będzie wyglądać tak: =MOD(WIERSZ();3)=2
Automatycznie usuń co drugi wiersz (lub N-ty wiersz) za pomocą makra VBA (szybka metoda)
Jeśli musisz dość często usuwać co drugi wiersz (lub co n-ty), możesz użyć kodu VBA i udostępnić go na pasku narzędzi szybkiego dostępu. Umożliwi to szybkie usuwanie alternatywnych wierszy za pomocą jednego kliknięcia.
Poniżej znajduje się kod, który najpierw prosi o wybranie zakresu, z którego chcesz usunąć alternatywne wiersze, a następnie co drugi wiersz w wybranym zestawie danych.
Sub Delete_Every_Other_Row() Dim Rng As Range Set Rng = Application.InputBox("Wybierz zakres (z wyłączeniem nagłówków)", "Wybór zakresu", Wpisz:=8) For i = Rng.Rows.Count To 1 Step -2 If i Mod 2 = 0 Następnie Rng.Rows(i).Delete End If Next i End Sub
Po uruchomieniu powyższego kodu najpierw zostaniesz poproszony o wybranie zakresu komórek. Po dokonaniu wyboru przejdzie przez każdy wiersz i usunie co drugi wiersz.
Jeśli zamiast tego chcesz usunąć co trzeci wiersz, możesz użyć poniższego kodu:
Sub Delete_Every_Third_Row() Dim Rng As Range Set Rng = Application.InputBox("Wybierz zakres (z wyłączeniem nagłówków)", "Wybór zakresu", Wpisz:=8) For i = Rng.Rows.Count To 1 Step -3 If i Mod 3 = 0 Następnie Rng.Rows(i).Delete End If Next i End Sub
Gdzie umieścić ten kod makra VBA?
Musisz umieścić ten kod w zwykłym module w edytorze VB w Excelu.
Poniżej znajdują się kroki, aby otworzyć edytor Vb, dodać moduł i umieścić w nim kod:
- Skopiuj powyższy kod VBA.
- Przejdź do zakładki Deweloper.
- Kliknij Visual Basic.
- W Edytorze VB kliknij prawym przyciskiem myszy dowolny obiekt skoroszytu.
- Przejdź do Wstaw i kliknij Moduł.
- W module wklej powyższy kod VBA.
- Zamknij edytor VB.
Gdy masz już kod w edytorze VB, możesz użyć następujących metod, aby uruchomić kod:
- Uruchom makro z edytora VB (klikając zielony przycisk odtwarzania na pasku narzędzi edytora VB)
- Umieszczając kursor w dowolnym wierszu kodu i używając klawisza F5
- Przypisując makro do przycisku/kształtu
- Dodając makro do paska narzędzi szybkiego dostępu
Oto szczegółowy samouczek dotyczący uruchamiania makra w programie Excel.
Jeśli musisz to robić często, możesz rozważyć dodanie kodu VBA do skoroszytu makr osobistych. W ten sposób będzie on zawsze dostępny do użytku we wszystkich skoroszytach.
Gdy masz kod VBA w skoroszycie, musisz go zapisać jako plik z obsługą makr (z rozszerzeniem .XLSM)
Uwaga: Ponieważ wszelkie zmiany wprowadzone przez kod VBA są nieodwracalne, najlepiej najpierw wykonać kopię zapasową skoroszytu/arkusza, a następnie uruchomić ten kod.Usuń każdą inną kolumnę (lub każdą N-tą kolumnę)
Usuwanie każdego alternatywnego wiersza lub co trzeciego/czwartego wiersza jest łatwe, ponieważ możesz użyć opcji filtrowania. Wszystko, co musisz zrobić, to użyć formuły, która identyfikuje alternatywne wiersze (lub co trzeci/czwarty wiersz) i filtruje te wiersze.
Niestety, ta sama metodologia nie działa z kolumnami - ponieważ nie możesz filtrować kolumn tak, jak filtrujesz wiersze.
Tak więc, jeśli musisz usunąć co drugą kolumnę (lub co trzecią/czwartą/N-tą), musisz być trochę bardziej kreatywny.
W tej sekcji pokażę dwie metody, których możesz użyć do usunięcia każdej innej kolumny w programie Excel (i możesz użyć tej samej metody, aby usunąć co trzecią / czwartą / N-tą kolumnę, jeśli chcesz).
Usuń alternatywne kolumny za pomocą formuł i metody sortowania
Załóżmy, że masz zestaw danych, jak pokazano poniżej i chcesz usunąć każdą inną kolumnę (z wyjątkiem kolumny nagłówka A)
Sztuczka polegałaby na zidentyfikowaniu alternatywnych kolumn za pomocą formuły, a następnie posortowaniu kolumn na podstawie tej formuły. Po zebraniu posortowanych kolumn możesz je ręcznie wybrać i usunąć.
Poniżej znajdują się kroki, aby usunąć każdą inną kolumnę w programie Excel:
- Wstaw wiersz nad wierszem nagłówka (kliknij prawym przyciskiem myszy dowolną komórkę w nagłówku, kliknij Wstaw, a następnie kliknij opcję Cały wiersz)
- Wprowadź następującą formułę w komórce nad skrajną lewą kolumną w zestawie danych i skopiuj dla wszystkich komórek: =MOD(KOLUMNA();2)
- Przekształć wartości formuły na liczby. Aby to zrobić, skopiuj komórki, kliknij prawym przyciskiem myszy i przejdź do Wklej specjalnie -> Wklej wartości.
- Wybierz cały zestaw danych (z wyłączeniem nagłówków w kolumnie). W tym przykładzie wybrałem B1:G14 (i NIE kolumna A)
- Kliknij kartę Dane
- Kliknij ikonę Sortuj
- W oknie dialogowym Sortuj wprowadź następujące zmiany:
- Kliknij przycisk „Opcje” i w oknie dialogowym, które się otworzy, kliknij „Sortuj od lewej do prawej”, a następnie kliknij OK
- Z listy rozwijanej Sortuj według wybierz Wiersz 1. To jest wiersz z wynikami formuły
- Z listy rozwijanej Kolejność wybierz opcję Od najmniejszej do największej.
- Kliknij przycisk „Opcje” i w oknie dialogowym, które się otworzy, kliknij „Sortuj od lewej do prawej”, a następnie kliknij OK
- Kliknij OK
Powyższe kroki sortują wszystkie kolumny i łączą wszystkie alternatywne kolumny w jednym miejscu (na końcu).
Teraz możesz wybrać wszystkie te kolumny (dla których wartość formuły wynosi 1) i je usunąć.
Chociaż nie jest to najlepsze rozwiązanie, nadal wykonuje pracę.
Jeśli chcesz usunąć co trzecią lub co czwartą kolumnę, musisz odpowiednio dostosować formułę.
Powiązany samouczek: Jak sortować w programie Excel (według wierszy, kolumn, kolorów, dat i liczb)
Usuń alternatywne kolumny za pomocą VBA
Innym szybkim sposobem usunięcia alternatywnych kolumn jest użycie poniższego kodu VBA:
Sub Delete_Every_Other_Column() Dim Rng As Range Set Rng = Application.InputBox("Wybierz zakres (z wyłączeniem nagłówków)", "Wybór zakresu", Wpisz:=8) For i = Rng.Columns.Count To 1 Step -2 If i Mod 2 = 0 Następnie Rng.Columns(i).Delete End If Next i End Sub
Powyższy kod prosi o wybranie zakresu komórek, który ma kolumny. Tutaj musisz wybrać kolumny z wyjątkiem tej, która ma nagłówek.
Po określeniu zakresu komórek z danymi użyje pętli For i usunie każdą inną kolumnę.
Jeśli chcesz usunąć co trzecią kolumnę, możesz użyć poniższego kodu (i odpowiednio dostosować, aby usunąć N-tą kolumnę)
Sub Delete_Every_Third_Column() Dim Rng As Range Set Rng = Application.InputBox("Wybierz zakres (z wyłączeniem nagłówków)", "Wybór zakresu", Type:=8) For i = Rng.Columns.Count To 1 Step -3 If i Mod 3 = 0 Następnie Rng.Columns(i).Delete End If Next i End Sub
Kroki, gdzie umieścić ten kod VBA i jak go używać, omówiono w powyższej sekcji zatytułowanej - „Automatycznie usuń co drugi wiersz (lub N-ty wiersz) za pomocą makra VBA (szybka metoda)”
Mam nadzieję, że ten samouczek programu Excel okazał się przydatny.
