Formuła ogólna
=MATCH(bigtext,range)
Podsumowanie
Aby uzyskać ostatnią pozycję względną (tj. Ostatni wiersz, ostatnią kolumnę) dla danych tekstowych (z pustymi komórkami lub bez), możesz użyć funkcji PODAJ.POZYCJĘ. W przedstawionym przykładzie formuła w D5 to:
=MATCH(REPT("z",255),B4:B11)
Wyjaśnienie
Ta formuła wykorzystuje funkcję PODAJ.POZYCJĘ w trybie przybliżonego dopasowania, aby zlokalizować ostatnią wartość tekstową w zakresie. Przybliżone dopasowanie włączane przez ustawienie przez trzeci argument w PODAJ.POZYCJI na 1 lub pominięcie tego argumentu, który domyślnie wynosi 1.
Wartość wyszukiwania to tak zwany „duży tekst” (czasami w skrócie „duży tekst”), który jest celowo wartością „większą” niż jakakolwiek wartość, która pojawi się w zakresie. Podczas pracy z tekstem, który sortuje alfabetycznie, oznacza to wartość tekstową, która zawsze pojawi się na końcu alfabetycznej kolejności sortowania.
Ponieważ ta formuła dopasowuje tekst, chodzi o skonstruowanie wartości wyszukiwania, która nigdy nie pojawi się w rzeczywistym tekście, ale będzie ostatnia. W tym celu używamy funkcji REPT, aby powtórzyć literę „z” 255 razy. Liczba 255 reprezentuje największą liczbę znaków, na jaką pozwala funkcja MATCH w wartości wyszukiwania.
Jeśli MATCH nie może znaleźć tej wartości, „cofnie się” do ostatniej wartości tekstowej w zakresie i zwróci pozycję tej wartości.
Uwaga: to podejście działa dobrze w przypadku pustych komórek w zakresie, ale nie jest niezawodne w przypadku mieszanych danych zawierających zarówno liczby, jak i tekst.
Ostatnia pozycja względna a numer ostatniego wiersza
Podczas tworzenia zaawansowanych formuł tworzących zakresy dynamiczne często konieczne jest określenie 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. Uwaga: chcemy, aby ostatnia pozycja względna w danym zakresie, a nie numer wiersza w arkuszu:
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 wyjaśnia pojęcie „ostatniej względnej pozycji”.
