Pobierz przykładowy skoroszyt
Ten samouczek pokaże, jak znaleźć ostatni niepusty wiersz w zestawie danych w programie Excel i Arkuszach Google.
Znajdź ostatni rząd z danymi
Często warto wiedzieć, w którym wierszu kończą się Twoje dane. Jeśli Twój zakres ma lub może zawierać puste komórki, możesz znaleźć ostatni niepusty wiersz, korzystając z jednej z poniższych metod.
Metoda uniwersalna
Pierwsza metoda wykorzystuje funkcje ROW i MAX i może być używana z dowolnym rodzajem danych:
| 1 | =MAX((B:B"")*(WIERSZ(B:B))) |
Przeanalizujmy tę formułę.
Zaczynamy od testu logicznego na kolumnie danych. Nasza formuła sprawdza całą kolumnę (B:B) i wyprowadza TRUE dla niepustych komórek i FALSE dla pustych komórek.
| 1 | =B:B"" |
Funkcja ROW tworzy numer wiersza danej komórki. Jeśli nie podamy mu konkretnej komórki wejściowej, podaje numer wiersza komórki, w której się znajduje.
| 1 | =WIERSZ() |
Pomnożenie każdego numeru wiersza przez odpowiadające mu wartości PRAWDA (= 1) lub FAŁSZ (= 0) zwraca numer wiersza dla wypełnionej komórki i zero dla pustej komórki.
| 1 | =E3*F3 |
Funkcja MAX podaje maksimum zbioru liczb. W tym przykładzie, ponieważ wszystkie puste komórki generują wartość zero, maksimum to najwyższy numer wiersza.
| 1 | =MAKS(G3:G12) |
Połączenie tych kroków daje nam naszą oryginalną formułę:
| 1 | =MAX((B:B"")*(WIERSZ(B:B))) |
Pamiętaj, że jest to formuła tablicowa, więc jeśli używasz programu Excel 2022 lub starszego, musisz nacisnąć klawisze CTRL + SHIFT + ENTER, aby ją uruchomić.
Metoda dla zakresu tekstu
Jeśli twój (nieciągły) zakres zawiera tylko wartości tekstowe i puste komórki, możesz użyć mniej skomplikowanej formuły zawierającej funkcje POWTÓRZ i PODAJ.POZYCJĘ:
| 1 | =POWTÓRZ(POWT("z",50);B:B) |
Zobaczmy, jak działa ta formuła.
Funkcja POWT
Funkcja REPT powtarza ciąg tekstowy określoną liczbę razy. W tym przykładzie możemy go użyć do utworzenia ciągu tekstowego, który byłby ostatnim na dowolnej posortowanej alfabetycznie liście. Jeśli powtórzymy „z” 50 razy, powinno to zadziałać dla prawie każdej kolumny danych tekstowych; żadne wpisy nie pojawiają się przed „zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz” alfabetycznie.
| 1 | =POWT("z",50) |
Funkcja DOPASUJ
Funkcja MATCH znajduje daną wartość wyszukiwania w tablicy.
Przeszukujemy całą kolumnę danych pod kątem naszego ciągu tekstowego 50-z. Pominięcie danych wejściowych typu dopasowania w funkcji DOPASUJ każe jej znaleźć dopasowanie przybliżone, a nie dokładne.
| 1 | =POWTÓRZ(POWT("z",50);B:B) |
Funkcja PODAJ.POZYCJĘ przeszukuje kolumnę B i szuka naszego ciągu tekstowego składającego się z 50 „z”. Ponieważ go nie znajduje, formuła zwraca pozycję ostatniej niepustej komórki. Ta komórka zawiera ostatnią wartość w tablicy wyszukiwania, która jest mniejsza (lub równa) wartości wyszukiwania.
Pamiętaj, że ta formuła działa tylko wtedy, gdy zakres zawiera wyłącznie tekst i puste komórki (lub przynajmniej wartość ostatniej komórki nie jest liczbowa).
Znajdź ostatni wiersz z danymi w Arkuszach Google
Te formuły działają dokładnie tak samo w Arkuszach Google, jak w programie Excel.
