Note: The other languages of the website are Google-translated. Back to English

Jak wstawiać puste wiersze, gdy zmienia się wartość w programie Excel?

Przypuśćmy, że masz zakres danych, a teraz chcesz wstawić puste wiersze między danymi po zmianie wartości, abyś mógł oddzielić sekwencyjne te same wartości w jednej kolumnie, jak pokazano na poniższych zrzutach ekranu. W tym artykule omówię kilka sztuczek, które pomogą Ci rozwiązać ten problem.

Wstaw puste wiersze, gdy wartość zmienia się za pomocą funkcji Suma częściowa

Wstaw puste wiersze, gdy wartość zmienia się za pomocą kolumn pomocniczych

Wstaw puste wiersze, gdy wartość zmienia się wraz z kodem VBA

Wstaw określoną liczbę pustych wierszy, gdy wartość zmienia się dzięki zaawansowanej funkcji


Wstaw puste wiersze, gdy wartość zmienia się za pomocą funkcji Suma częściowa

Z Koszt produktów funkcję, możesz wstawić puste wiersze między danymi, gdy wartość zmieni się w następujący sposób:

1. Wybierz zakres danych, którego chcesz użyć.

2, Kliknij Dane > Koszt produktów otworzyć Koszt produktów okno dialogowe iw programie Koszt produktów w oknie dialogowym, wykonaj następujące czynności:

1: Wybierz nazwę kolumny, w której chcesz wstawić puste wiersze na podstawie zmiany wartości poniżej Przy każdej zmianie w sekcji;
2: Wybierz Liczyć z Zastosowanie funkcji Lista rozwijana;
3: Sprawdź nazwę kolumny, do której chcesz wstawić sumę częściową w pliku Dodaj sumę częściową do skrzynka na listy

3. Następnie kliknij OK, sumy częściowe zostały wstawione między różne produkty, a symbole konspektu są wyświetlane po lewej stronie tabeli, patrz zrzut ekranu:

4. Następnie kliknij cyfrę 2 u góry symbolu konspektu, aby wyświetlić tylko sumy częściowe.

5. Następnie wybierz dane zakresu sumy częściowej i naciśnij Alt +; klawisze skrótów, aby wybrać tylko widoczne wiersze, zobacz zrzut ekranu:

6. Po wybraniu widocznych rzędów dopiero potem naciśnij Usuń na klawiaturze, a wszystkie wiersze sum pośrednich zostały usunięte.

7. Następnie kliknij dowolną inną komórkę i wróć Dane > Rozgrupuj > Wyczyść zarys aby usunąć Kontury, zobacz zrzut ekranu:

8. Symbole konspektu zostały wyczyszczone od razu i widać, że puste wiersze zostały wstawione między danymi po zmianie wartości, patrz zrzut ekranu:

9. W końcu możesz usunąć kolumnę A według potrzeb.


Wstaw podział strony, puste wiersze, dolną krawędź lub kolor wypełnienia, gdy wartość szybko się zmienia

Jeśli masz Kutools dla programu Excel's Rozróżnij różnice funkcję, możesz szybko wstawić podział strony, puste wiersze, dolną ramkę lub kolor wypełnienia, gdy wartość zmieni się zgodnie z potrzebami. Zobacz poniższe demo.         Kliknij, aby pobrać Kutools dla programu Excel!


Wstaw puste wiersze, gdy wartość zmienia się za pomocą kolumn pomocniczych

W kolumnach pomocniczych możesz najpierw wstawić formułę, a następnie zastosować Znajdowanie i zamienianie funkcja, w końcu wstaw puste wiersze między zmieniającymi się wartościami. Wykonaj następujące czynności:

1. W pustej komórce C3 wprowadź tę formułę = A3 = A2, aw komórce D4 wprowadź tę formułę = A4 = A3zobacz zrzut ekranu:

2. Następnie wybierz C3: D4 i przeciągnij uchwyt wypełniania do zakresu, w którym chcesz zastosować te formuły, a otrzymasz Prawdziwy or Fałszywy w komórkach, patrz zrzut ekranu:

3. A następnie naciśnij Ctrl + F klawisze, aby otworzyć Znajdowanie i zamienianie w wyskakującym oknie dialogowym wprowadź FAŁSZYWY do Znajdź co pole tekstowe pod Znajdź kartę i kliknij Opcje , aby rozwinąć to okno dialogowe, i wybierz Wartości z Zaglądać rozwijana lista, zobacz zrzut ekranu:

4. Kliknij Znajdź wszystko , a następnie naciśnij Ctrl + aby zaznaczyć wszystkie wyniki wyszukiwania, wszystkie komórki FALSE zostały zaznaczone jednocześnie, zobacz zrzut ekranu:

6. Zamknij Znajdowanie i zamienianie W następnym kroku możesz wstawić puste wiersze, klikając Strona Główna > wstawka > Wstaw rzędy arkuszy, a puste wiersze zostały wstawione do danych, gdy wartość zmienia się na podstawie kolumny A, patrz zrzuty ekranu:

7. Na koniec możesz usunąć kolumny pomocnika C i D według potrzeb.


Wstaw puste wiersze, gdy wartość zmienia się wraz z kodem VBA

Jeśli masz dość korzystania z powyższych metod, oto kod, który może również pomóc w wstawianiu pustych wierszy między zmienionymi wartościami naraz.

1. Przytrzymaj ALT + F11 klawisze, aby otworzyć Okno Microsoft Visual Basic for Applications.

2, Kliknij wstawka > Modułi wklej następujący kod w Okno modułu.

Kod VBA: wstaw puste wiersze, gdy zmienia się wartość

Sub InsertRowsAtValueChange()
'Update by Extendoffice
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
For i = WorkRng.Rows.Count To 2 Step -1
    If WorkRng.Cells(i, 1).Value <> WorkRng.Cells(i - 1, 1).Value Then
        WorkRng.Cells(i, 1).EntireRow.Insert
    End If
Next
Application.ScreenUpdating = True
End Sub

3. Następnie naciśnij F5 klucz do uruchomienia tego kodu, a pojawi się okno zachęty umożliwiające wybranie jednej kolumny danych, do której chcesz wstawić puste wiersze, gdy zmieni się wartość, na której się opiera, zobacz zrzut ekranu:

4. A następnie kliknij OK, puste wiersze zostały wstawione między danymi, gdy wartość zmienia się na podstawie kolumny A.


Wstaw określoną liczbę pustych wierszy, gdy wartość zmienia się dzięki zaawansowanej funkcji

Jeśli spróbujesz powyższych kłopotliwych metod, tutaj przedstawię przydatne narzędzie, Kutools dla programu Excel's Rozróżnij różnice może pomóc we wstawieniu podziału strony, pustych wierszy, dolnej krawędzi lub koloru wypełnienia, gdy wartość komórki zmienia się szybko i łatwo.

Porady:Aby to zastosować Rozróżnij różnice Po pierwsze, należy pobrać plik Kutools dla programu Excel, a następnie szybko i łatwo zastosuj tę funkcję.

Po zainstalowaniu Kutools dla programu Excel, zrób tak:

1, Kliknij Kutools > utworzony > Rozróżnij różnicezobacz zrzut ekranu:

2, w Rozróżnij różnice według kluczowych kolumn w oknie dialogowym, wykonaj następujące operacje:

  • Wybierz zakres danych, którego chcesz użyć, a następnie wybierz kolumnę kluczową, na podstawie której chcesz wstawić puste wiersze;
  • Następnie sprawdź Pusty wiersz opcja z Opcje i wprowadź liczbę pustych wierszy, które chcesz wstawić.

3. Następnie kliknij Ok przycisk, a określona liczba pustych wierszy została wstawiona do danych, jeśli wartość komórki ulegnie zmianie, zobacz zrzuty ekranu:

Kliknij, aby pobrać Kutools dla programu Excel i bezpłatną wersję próbną teraz!


Najlepsze narzędzia biurowe

Kutools dla programu Excel rozwiązuje większość problemów i zwiększa produktywność o 80%

  • Ponowne użycie: Szybko włóż złożone wzory, wykresy i wszystko, czego używałeś wcześniej; Szyfruj komórki z hasłem; Utwórz listę mailingową i wysyłaj e-maile ...
  • Pasek Super Formula (łatwo edytować wiele wierszy tekstu i formuły); Układ do czytania (łatwe odczytywanie i edytowanie dużej liczby komórek); Wklej do filtrowanego zakresu...
  • Scal komórki / wiersze / kolumny bez utraty danych; Podziel zawartość komórek; Połącz zduplikowane wiersze / kolumny... Zapobiegaj zduplikowanym komórkom; Porównaj zakresy...
  • Wybierz Duplikat lub Unikalny Wydziwianie; Wybierz puste wiersze (wszystkie komórki są puste); Super Find i Fuzzy Find w wielu zeszytach ćwiczeń; Losowy wybór ...
  • Dokładna kopia Wiele komórek bez zmiany odwołania do formuły; Automatyczne tworzenie odniesień do wielu arkuszy; Wstaw punktory, Pola wyboru i nie tylko ...
  • Wyodrębnij tekst, Dodaj tekst, Usuń według pozycji, Usuń przestrzeń; Tworzenie i drukowanie podsumowań stronicowania; Konwertuj zawartość komórek i komentarze...
  • Super filtr (zapisz i zastosuj schematy filtrów do innych arkuszy); Zaawansowane sortowanie według miesiąca / tygodnia / dnia, częstotliwości i innych; Specjalny filtr pogrubieniem, kursywą ...
  • Połącz skoroszyty i arkusze robocze; Scal tabele na podstawie kluczowych kolumn; Podziel dane na wiele arkuszy; Konwersja wsadowa xls, xlsx i PDF...
  • Ponad 300 zaawansowanych funkcji. Obsługuje Office / Excel 2007-2021 i 365. Obsługuje wszystkie języki. Łatwe wdrażanie w przedsiębiorstwie lub organizacji. Pełne funkcje 30-dniowa bezpłatna wersja próbna. 60-dniowa gwarancja zwrotu pieniędzy.
karta kte 201905

Karta Office wprowadza interfejs z zakładkami do pakietu Office i znacznie ułatwia pracę

  • Włącz edycję i czytanie na kartach w programach Word, Excel, PowerPoint, Publisher, Access, Visio i Project.
  • Otwieraj i twórz wiele dokumentów w nowych kartach tego samego okna, a nie w nowych oknach.
  • Zwiększa produktywność o 50% i redukuje setki kliknięć myszką każdego dnia!
officetab dół
Sortuj komentarze według
Komentarze (15)
Brak ocen. Oceń jako pierwszy!
Ten komentarz został zminimalizowany przez moderatora na stronie
Mam kolumnę z liczbami 5,4,3,2 i 1 (dużo ich) i chcę oddzielić Piątki od pozostałych, np.: 5 5 5 4 3 2 1 5 5 5 5 5 4 3 2 1 Liczby są w kolejności opisu i zawsze są cztery poniżej pięciu, więc spróbowałem: Sub InsertRowsAtValueChange() 'Update 20140716 Dim Rng As Range Dim WorkRng As Range On Error Resume Next xTitleId = "KutoolsforExcel" Set WorkRng = Application.Selection Ustaw WorkRng = Application.InputBox("Zakres", xTitleId, WorkRng.Address, Type:=8) Application.ScreenUpdating = False For i = WorkRng.Rows.Count To 2 Step -1 If WorkRng.Cells(i, 1). Value = 5 And WorkRng.Cells(i - 1, 1).Value = 4 Then WorkRng.Cells(i, 1).EntireRow.Insert End If Next Application.ScreenUpdating = True End Sub Ale to nie działało. Możesz mi pomóc?
Ten komentarz został zminimalizowany przez moderatora na stronie
Formuła działa świetnie, jednak jest kilka przykładów, w których nie rozpoznaje
Ten komentarz został zminimalizowany przez moderatora na stronie
Dziękuję bardzo, szukałem wszędzie i było to jedyne rozwiązanie, które zadziałało dla mnie, które było również w prostym języku angielskim. Wszystkie inne witryny z samouczkami Excela nie podzieliły tego krok po kroku tak, jak Ty, dziękuję bardzo. dzięki : )
Ten komentarz został zminimalizowany przez moderatora na stronie
Bardzo dziękuję za opisanie wielu rozwiązań problemu. Użyłem Visual Basic i zmieniłem [quote]WorkRng.Cells(i, 1).EntireRow.Insert[/quote] na [quote]With WorkRng.Cells(i, 1).EntireRow.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlMedium End With[/quote] Spowoduje to narysowanie między wierszami linii ze zmieniającymi się wartościami zamiast pustych linii.
Ten komentarz został zminimalizowany przez moderatora na stronie
Gdzie w kodzie musiałbym zmodyfikować, aby zawierał więcej niż jeden wiersz, muszę dodać 10 po każdej przerwie... Dzięki
Ten komentarz został zminimalizowany przez moderatora na stronie
Witam wszystkich dziękuję!! to niesamowite, czy możecie mi też pozwolić, jak wstawić 2 wiersze, gdy wartość zmieni się w VBA lub w programie Excel.
Ten komentarz został zminimalizowany przez moderatora na stronie
Cześć, te są prawie przydatne! Pierwsza metoda nie działa w moim przypadku, ponieważ po jawnym wykonaniu tych kroków dane, które usuwam w okienkach sum częściowych, usuwają całe posortowane przeze mnie kolumny. W drugiej metodzie, gdy przejdę do kroku, w którym wstawiam wiersze arkusza, wiersze są wstawiane POWYŻEJ komórek FALSE, co powoduje rozbicie danych, ale ostatni wybór z każdej grupy jest następnie dodawany do grupy poniżej. Jakakolwiek rada???
Ten komentarz został zminimalizowany przez moderatora na stronie
[cytat]Cześć wszystkim dziękuję!! to niesamowite, czy możecie mi też pozwolić, jak wstawić 2 wiersze, gdy wartość zmieni się w VBA lub w programie Excel.Przez Hudsona[/cytat] Proszę dać mi znać, jak wstawić więcej niż 1 wiersz.
Ten komentarz został zminimalizowany przez moderatora na stronie
Od jakiegoś czasu korzystam z własnego rozwiązania.
1. Wstaw kolumnę pomocniczą do kolumny A
2. W A2 wpisz „jeśli(B2=B1,A1,A1+1)”
3. Skopiuj tę formułę do ostatniego wiersza
4. Skopiuj wszystkie wypełnione komórki w kolumnie A i wklej je specjalnie (wartości)
5. Skopiuj ponownie wszystkie komórki i wklej je do kolumny A w pierwszej niewypełnionej komórce (np. jeśli masz 104 wiersze danych plus wiersz nagłówka, wkleisz je do komórki A106)
6. Kliknij Dane i usuń duplikaty (tylko w komórkach, które właśnie wkleiłeś w kroku 5; nie we wszystkich wierszach)
7. Sortuj całą kolumnę A
8. Usuń kolumnę A

Wydaje się, że to wiele kroków, ale zajmuje tylko kilka sekund.
Ten komentarz został zminimalizowany przez moderatora na stronie
Kod VBA zadziałał za pierwszym razem i zrobił dokładnie to, co próbowałem zrobić. Dziękuję bardzo!
Ten komentarz został zminimalizowany przez moderatora na stronie
RE: wstawiaj puste wiersze, gdy zmienia się wartość z kodem vba
Czy istnieje sposób, aby zapisać zakres i nie musieć go wybierać za każdym razem, gdy go uruchamiam?
Ten komentarz został zminimalizowany przez moderatora na stronie
Cześć,
Jest to bardzo przydatne w przypadku, gdy muszę wstawić 1 wiersz, ale jeśli muszę wstawić 145 wierszy za każdym razem, gdy dane zmieniają się w kolumnie spwcific, jak mogę to zrobić?
Ten komentarz został zminimalizowany przez moderatora na stronie
Cześć, Hassanie,
Aby wstawić wiele pustych wierszy, gdy wartość zmieni się w określonej kolumnie, należy zastosować następujący kod VBA:

Uwaga: W poniższym kodzie należy zmienić numer 99 zgodnie z potrzebami, na przykład po wstawieniu 145 pustych wierszy należy zmienić numer 99 na 144. Spróbuj, mam nadzieję, że może ci to pomóc!

Sub InsertRowsAtValueChange()
Dim Rng jako zakres
Dim WorkRng jako zakres
On Error Resume Next
xTitleId = "Kutools for Excel"
Ustaw WorkRng = Application.Selection
Ustaw WorkRng = Application.InputBox("Zakres", xTitleId, WorkRng.Address, Type:=8)
Application.ScreenUpdating = Fałsz
Dla i = WorkRng.Rows.Count to 2 Krok -1
Jeśli WorkRng.Cells(i, 1)).Value <> WorkRng.Cells(i - 1, 1).Value Wtedy
Zakres(PracaRng.Komórki(i, 1). Cały Wiersz, WorkRng.Komórki(i + 99, 1). Cały Wiersz).Wstaw
End If
Dalej
Application.ScreenUpdating = True
End Sub
Ten komentarz został zminimalizowany przez moderatora na stronie
Bardzo pomocne. Kod, który pokazuje skyyang powyżej, działał idealnie. Tylko upewnij się, że w danych nie ma już spacji.

Nie rozumiem VBA, ale uważam, że jeśli chcesz dodać więcej wierszy pod danymi, które już miały odstępy, powinien istnieć sposób na zignorowanie spacji.

Czy można dodać wiersz, aby zignorować lub pominąć puste wiersze? To może sprawić, że ten kod będzie bardziej uniwersalny i powtarzalny, jeśli zajdzie taka potrzeba. Również funkcja usuwania podobna do tej może być przydatna, więc cofanie nie jest konieczne.
Ten komentarz został zminimalizowany przez moderatora na stronie
Czy metodę VBA można dostosować do ignorowania pustych komórek? Mam plik, do którego muszę wstawić wiersze na podstawie dwóch różnych kolumn, ale kiedy uruchamiam makro w drugiej kolumnie, kończę z trzema pustymi wierszami, w których pierwsze makro uruchamia wstawione wiersze.
Czy może działać na dwóch kolumnach jednocześnie?
Nie ma tu jeszcze żadnych komentarzy
Zostaw swój komentarz
Publikowanie jako gość
×
Oceń ten post:
0   Postacie
Sugerowane lokalizacje

Bądż na bieżąco

Prawa autorskie © 2009 - www.extendoffice.com. | Wszelkie prawa zastrzeżone. Zasilany przez ExtendOffice, | Mapa strony
Microsoft i logo Office są znakami towarowymi lub zastrzeżonymi znakami towarowymi Microsoft Corporation w Stanach Zjednoczonych i / lub innych krajach.
Chronione przez Sectigo SSL