Formuła ogólna
(=MATCH(2,1/(range"")))
Podsumowanie
Aby uzyskać ostatnią pozycję względną (tj. Ostatni wiersz, ostatnią kolumnę) dla mieszanych danych, które mogą zawierać puste komórki, możesz użyć funkcji PODAJ.POZYCJĘ, jak opisano poniżej.
Uwaga: to jest formuła tablicowa i należy ją wprowadzić za pomocą Control + Shift + Enter.
W pokazanym przykładzie formuła w E5 to:
(=MATCH(2,1/(B4:B10"")))
Ostatnia * względna * pozycja, a nie wiersz w arkuszu
Podczas tworzenia bardziej zaawansowanych formuł często konieczne jest ustalenie ostatniej lokalizacji danych na liście. W zależności od danych może to być ostatni wiersz z danymi, ostatnia kolumna z danymi lub przecięcie obu. Chcemy, aby ostatnia * pozycja względna * w podanym zakresie, a nie numer wiersza w arkuszu:
Wyjaśnienie
Ta formuła używa funkcji PODAJ.POZYCJĘ skonfigurowanej do znajdowania pozycji ostatniej niepustej komórki w zakresie.
Działając od wewnątrz, tablica wyszukiwania wewnątrz MATCH jest zbudowana w następujący sposób:
=1/(B4:B10"")) =1/(TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE) =(1;#DIV/0!;1;#DIV/0!;1;1;#DIV/0!)
Uwaga: wszystkie wartości w tablicy to 1 lub # DIV / 0! błąd.
PODAJ.POZYCJĘ jest następnie ustawiane tak, aby dopasować wartość 2 w „trybie przybliżonego dopasowania”, przez pominięcie trzeciego argumentu.
Ponieważ wartość wyszukiwania 2 nigdy nie zostanie znaleziona, PODAJ.POZYCJĘ zawsze znajdzie ostatnią 1 w tablicy wyszukiwania, która odpowiada ostatniej niepustej komórce.
To podejście będzie działać z każdym rodzajem danych, w tym liczbami, tekstem, datami itp. Działa również z pustymi ciągami tekstowymi zwracanymi przez takie formuły:
=IF(A1<100,"")
Zakres dynamiczny
Możesz użyć tej formuły, aby utworzyć zakres dynamiczny z innymi funkcjami, takimi jak INDEKS i PRZESUNIĘCIE. Zobacz linki poniżej, aby uzyskać przykłady i wyjaśnienia:
- Zakres dynamiczny z INDEX i COUNTA
- Zakres dynamiczny z OFFSET i COUNTA
Inspiracją dla tego artykułu była doskonała książka Mike'a Girvina Control + Shift + Enter, w której Mike wykonuje świetną robotę wyjaśniając pojęcie „ostatniej względnej pozycji”.
