Pobierz przykładowy skoroszyt
Ten samouczek nauczy Cię, jak wyszukać ostatnią wartość w kolumnie lub wierszu w programie Excel.
Ostatnia wartość w kolumnie
Możesz użyć funkcji WYSZUKAJ, aby znaleźć ostatnią niepustą komórkę w kolumnie.
| 1 | =WYSZUKAJ(2,1/(B:B");B:B) |
Przejdźmy przez tę formułę.
Część formuły B:B”” zwraca tablicę zawierającą wartości Prawda i Fałsz: {FAŁSZ, PRAWDA, PRAWDA,…}, testowanie każdej komórki w kolumnie B jest puste (FAŁSZ).
| 1 | =WYSZUKAJ(2,1/({FAŁSZ;PRAWDA;PRAWDA;PRAWDA;PRAWDA;PRAWDA;FAŁSZ;… ),B:B) |
Te wartości logiczne są konwertowane na 0 lub 1 i są używane do dzielenia 1.
| 1 | =WYSZUKAJ(2,{#DZIEL/0!;1;1;1;1;1;#DZIEL/0!;,B:B) |
To jest wektor wyszukiwania dla funkcji WYSZUKAJ. W naszym przypadku szukana_wartość to 2, ale największa wartość w szukanym_wektorze to 1, więc funkcja WYSZUKAJ dopasuje ostatnią 1 w tablicy i zwróci odpowiednią wartość w wektorze_wynikowym.
Jeśli masz pewność, że w kolumnie znajdują się tylko wartości liczbowe, dane zaczynają się od wiersza 1, a zakres danych w sposób ciągły, możesz użyć nieco prostszej formuły z funkcjami INDEKS i LICZBA.
| 1 | =INDEKS(B:B;LICZBA(B:B)) |
Funkcja COUNT zwraca liczbę komórek wypełnionych danymi w zakresie ciągłym (4), a funkcja INDEKS podaje wartość komórki w tym odpowiednim wierszu (4.).
Aby uniknąć możliwych błędów, gdy zakres danych zawiera kombinację wartości numerycznych i nienumerycznych, a nawet puste komórki, możesz użyć funkcji WYSZUKAJ razem z funkcjami ISBLANK i NOT.
| 1 | =WYSZUKAJ(2,1/(NIE(NIE(CZYŚĆ(B:B)));B:B) |
Funkcja ISBLANK zwraca tablicę zawierającą wartości True i False, odpowiadające jedynkom i zerom. Funkcja NIE zmienia Prawdę (tj. 1) na Fałsz i Fałsz (tj. 0) na Prawdę. Jeśli odwrócimy tę tablicę wynikową (przy dzieleniu 1 przez tę tablicę), otrzymamy tablicę wynikową zawierającą ponownie #DIV/0! błędy i jedynek, które mogą być użyte jako tablica wyszukiwania (lookup_vector) w naszej funkcji WYSZUKAJ. Funkcjonalność funkcji WYSZUKAJ jest wtedy taka sama jak w naszym pierwszym przykładzie: zwraca wartość wektora wynikowego na pozycji ostatniego 1 w tablicy wyszukiwania.
Jeśli potrzebujesz zwróconego numeru wiersza z ostatnim wpisem, możesz zmodyfikować formułę użytą w naszym pierwszym przykładzie razem z funkcją ROW w wektorze_wynikowym.
| 1 | =WYSZUKAJ(2,1/(B:B"));WIERSZ(B:B)) |
Ostatnia wartość w wierszu
Aby uzyskać wartość ostatniej niepustej komórki w wierszu wypełnionym danymi liczbowymi, możesz użyć podobnego podejścia, ale z różnymi funkcjami: funkcja OFFSET wraz z funkcjami MATCH i MAX.
| 1 | = PRZESUNIĘCIE(odwołanie; wiersze; kolumny) |
| 1 | =PRZESUNIĘCIE(B2,0,PODZIEL(MAKS(B2:XFD2)+1;B2:XFD2,1)-1) |
Zobaczmy, jak działa ta formuła.
Funkcja DOPASUJ
Używamy funkcji PODAJ.POZYCJĘ, aby „policzyć”, ile wartości komórek jest poniżej 1 + maksimum wszystkich wartości w wierszu 2, zaczynając od B2.
| 1 | = PODAJ.(wyszukiwana_wartość, wyszukiwana_tablica, [typ_dopasowania]) |
| 1 | =DOPASUJ(MAKS(B2:XFD2)+1;B2:XFD2,1) |
Szukana_wartość funkcji PODAJ.POZYCJĘ to maksimum wszystkich wartości w wierszu 2 + 1. Ponieważ ta wartość oczywiście nie istnieje w wierszu2, a typ_dopasowania jest ustawiony na 1 (mniejszy lub równy szukanej_wartości), funkcja PODAJ.POZYCJĘ zwróci pozycja ostatniej „sprawdzonej” komórki w tablicy, czyli liczba komórek wypełnionych danymi z zakresu B2:XFD2 (XFD to ostatnia kolumna w nowszych wersjach Excela).
Funkcja przesunięcia
Następnie używamy funkcji OFFSET, aby uzyskać wartość tej komórki, której pozycja została zwrócona przez funkcję MATCH.
| 1 | =PRZESUNIĘCIE(B2,0;C4-1) |
