Możesz użyć funkcji Excel PODAJ.POZYCJĘ, aby znaleźć względną pozycję określonej wartości w zakresie komórek lub tablicy.
Funkcja PODAJ.POZYCJĘ jest podobna do funkcji WYSZUKAJ.PIONOWO, ponieważ obie zostały skategoryzowane w ramach funkcji wyszukiwania/odwołania programu Excel. WYSZUKAJ.PIONOWO wyszukuje określoną wartość w kolumnie i zwraca wartość w tym samym wierszu, podczas gdy funkcja PODAJ.POZYCJĘ wyszukuje określoną wartość w zakresie i zwraca pozycję tej wartości.
Funkcja Excel MATCH szuka określonej wartości w zakresie komórek lub tablicy i zwraca względną pozycję pierwszego wystąpienia tej wartości w zakresie. Funkcji PODAJ.POZYCJĘ można również użyć do wyszukania określonej wartości i zwrócenia odpowiadającej jej wartości za pomocą funkcji INDEX (podobnie jak Vlookup). Zobaczmy, jak używać funkcji Excel MATCH, aby znaleźć pozycję wartości wyszukiwania w zakresie komórek.
Funkcja Excel MATCH
Funkcja PODAJ.POZYCJĘ jest wbudowaną funkcją programu Excel i służy głównie do lokalizowania względnej pozycji wartości wyszukiwania w kolumnie lub wierszu.
Składnia funkcji PODAJ.POZYCJĘ:
= DOPASUJ(wyszukiwana_wartość,wyszukiwana_tablica,[typ_dopasowania})Gdzie:
szukana_wartość – Wartość, którą chcesz wyszukać w określonym zakresie komórek lub w tablicy. Może to być wartość liczbowa, tekstowa, logiczna lub odwołanie do komórki zawierające wartość.
lookup_array – Tablice komórek, w których szukasz wartości. Musi to być pojedyncza kolumna lub jeden wiersz.
typ_dopasowania – Jest to parametr opcjonalny, który można ustawić na 0,1 lub -1, a domyślna wartość to 1.
- 0 szuka dokładnego dopasowania, gdy nie zostanie znalezione, zwraca błąd.
- -1 szuka najmniejszej wartości, która jest większa lub równa szukanej_wartość, gdy tablica przeglądowa jest w porządku rosnącym.
- 1 szuka największej wartości, która jest mniejsza lub równa wartości look_up, gdy tablica wyszukiwania w kolejności malejącej.
Znajdź pozycję dokładnego dopasowania
Załóżmy, że mamy następujący zbiór danych, w którym chcemy znaleźć pozycję o określonej wartości.
W tej tabeli chcemy znaleźć pozycję nazwy miasta (Memphis) w kolumnie (A2:A23), więc używamy tego wzoru:
=PODZIEL("memphis";A2:A23,0)Trzeci argument jest ustawiony na „0”, ponieważ chcemy znaleźć dokładne dopasowanie nazwy miasta. Jak widać, nazwa miasta „memphis” we wzorze jest pisana małymi literami, natomiast w tabeli pierwsza litera nazwy miasta jest pisana dużą (Memphis). Mimo to formuła jest w stanie znaleźć pozycję określonej wartości w podanym zakresie. Dzieje się tak, ponieważ funkcja MATCH nie uwzględnia wielkości liter.
Notatka: Jeśli szukana_wartość nie zostanie znaleziona w zakresie wyszukiwania lub jeśli określisz niewłaściwy zakres wyszukiwania, funkcja zwróci błąd #N/D.
Możesz użyć odwołania do komórki w pierwszym argumencie funkcji zamiast bezpośredniej wartości. Poniższa formuła znajduje pozycję wartości w komórce F2 i zwraca wynik w komórce F3.
Znajdź pozycję przybliżonego dopasowania
Istnieją dwa sposoby wyszukiwania przybliżonego lub dokładnego dopasowania wartości wyszukiwania i zwrócenia jej pozycji.
- Jednym ze sposobów jest znalezienie najmniejszej wartości, która jest większa lub równa (następne największe dopasowanie) do określonej wartości. Można to osiągnąć, ustawiając ostatni argument (typ_dopasowania) funkcji na „-1”
- Innym sposobem jest największa wartość, która jest mniejsza lub równa (następne najmniejsze dopasowanie) do podanej wartości. Można to osiągnąć, ustawiając match_type funkcji na „1”
Następny najmniejszy mecz
Jeśli funkcja nie może znaleźć dokładnego dopasowania do określonej wartości, gdy typ dopasowania jest ustawiony na „1”, lokalizuje największą wartość, która jest nieco mniejsza od określonej wartości (co oznacza następną najmniejszą wartość) i zwraca swoją pozycję . Aby to zadziałało, musisz posortować tablicę w kolejności rosnącej, jeśli nie, spowoduje to błąd.
W tym przykładzie używamy poniższej formuły, aby znaleźć następne najmniejsze dopasowanie:
= PODAJ.POZYCJĘ(F2;D2:D23;1)Gdy ta formuła nie może znaleźć dokładnego dopasowania wartości w komórce F2, wskazuje pozycję (16) następnej najmniejszej wartości, tj. 98.
Następny największy mecz
Gdy typ dopasowania jest ustawiony na „-1”, a funkcja PODAJ.POZYCJĘ nie może znaleźć dokładnego dopasowania, znajduje najmniejszą wartość, która jest większa od określonej wartości (co oznacza kolejną największą wartość) i zwraca jej pozycję. Tablica wyszukiwania musi być posortowana w porządku malejącym dla tej metody, w przeciwnym razie zwróci błąd.
Na przykład wprowadź następującą formułę, aby znaleźć następne największe dopasowanie do wartości wyszukiwania:
= PODAJ.POZYCJĘ(F2;D2:D23;-1)Ta funkcja PODAJ.POZYCJĘ wyszukuje wartość w F2 (55) w zakresie wyszukiwania D2:D23, a gdy nie może znaleźć dokładnego dopasowania, zwraca pozycję (16) następnej największej wartości, tj. 58.
Mecz z dziką kartą
Symboli wieloznacznych można używać w funkcji PODAJ.POZYCJĘ tylko wtedy, gdy parametr match_type jest ustawiony na „0”, a wartością wyszukiwania jest ciąg tekstowy. Istnieją symbole wieloznaczne, których możesz użyć w funkcji PODAJ.POZYCJĘ: gwiazdka (*) i znak zapytania (?).
- Znak zapytania (?) służy do dopasowania dowolnego pojedynczego znaku lub litery do ciągu tekstowego.
- Gwiazdka (*) służy do dopasowania dowolnej liczby znaków do łańcucha.
Na przykład użyliśmy dwóch symboli wieloznacznych „?” w funkcji lookup_value (Lo??n) funkcji PODAJ.POZYCJĘ, aby znaleźć wartość, która pasuje do ciągu tekstowego z dowolnymi dwoma znakami (w miejscach symboli wieloznacznych). A funkcja zwraca względną pozycję pasującej wartości w komórce E5.
=PODZIEL("Lo??n";A2:A22,0)
Możesz użyć symbolu wieloznacznego (*) w taki sam sposób jak (?), ale gwiazdka służy do dopasowania dowolnej liczby znaków, podczas gdy znak zapytania służy do dopasowania dowolnego pojedynczego znaku.
Na przykład, jeśli użyjesz „sp*”, funkcja może dopasować się do głośnika, prędkości lub spielberga itp. Ale jeśli funkcja znajdzie wiele/duplikatów wartości pasujących do wartości wyszukiwania, zwróci tylko pozycję pierwszej wartości.
W tym przykładzie wpisaliśmy „Kil*o” w argumencie szukana_wartość. Tak więc funkcja MATCH() wyszukuje tekst, który zawiera „Kil” na początku, „o” na końcu i dowolną liczbę znaków pomiędzy. „Kil*o” pasuje do Kilimandżaro w tablicy, a zatem funkcja zwraca względną pozycję Kilimandżaro, czyli 16.
INDEKS i DOPASUJ
Funkcje MATCH są rzadko używane samodzielnie. Często łączyły się z innymi funkcjami, tworząc potężne formuły. Gdy funkcja MATCH jest połączona z funkcją INDEX, może wykonywać zaawansowane wyszukiwania. Wiele osób nadal woli używać funkcji WYSZUKAJ.PIONOWO do wyszukiwania wartości, ponieważ jest to prostsze, ale DOPASOWANIE DO INDEKSU jest bardziej elastyczne i szybsze niż WYSZUKAJ.PIONOWO.
WYSZUKAJ.PIONOWO może wyszukiwać tylko wartość w pionie, tj. kolumny, podczas gdy kombinacja DOPASUJ INDEKS może wykonywać zarówno wyszukiwania pionowe, jak i poziome.
Funkcja INDEX używana do pobierania wartości w określonej lokalizacji w tabeli lub zakresie. Funkcja PODAJ.POZYCJĘ zwraca względną pozycję wartości w kolumnie lub wierszu. Po połączeniu funkcja PODAJ.POZYCJĘ znajduje numer wiersza lub kolumny (lokalizację) określonej wartości, a funkcja INDEKS pobiera wartość na podstawie tego numeru wiersza i kolumny.
Składnia funkcji INDEKS:
=INDEKS(tablica;numer_wiersza;[numer_kolumny];)W każdym razie zobaczmy, jak działa INDEX MATCH na przykładzie.
W poniższym przykładzie chcemy pobrać wynik „Quiz2” dla studentki „Anne”. W tym celu skorzystamy z poniższej formuły:
=INDEKS(B2:F20;PODZIEL(H2;A2:A20,0));3)INDEX potrzebuje numeru wiersza i kolumny, aby pobrać wartość. W powyższym wzorze zagnieżdżona funkcja PODAJ.POZYCJĘ znajduje numer wiersza (pozycję) wartości „Anne” (H2). Następnie podajemy ten numer wiersza do funkcji INDEKS z zakresem B2:F20 i numerem kolumny (3), który określamy. A funkcja INDEX zwraca wynik „91”.
Dwukierunkowe wyszukiwanie z INDEX i MATCH
Możesz również użyć funkcji INDEKS i PODAJ.POZYCJĘ, aby wyszukać wartość w zakresie dwuwymiarowym (przeszukiwanie dwukierunkowe). W powyższym przykładzie użyliśmy funkcji PODAJ.POZYCJĘ, aby zlokalizować numer wiersza wartości, ale ręcznie wprowadziliśmy numer kolumny. Ale możemy znaleźć zarówno wiersz, jak i kolumnę, zagnieżdżając dwie funkcje PODAJ.POZYCJĘ, jedną w argumencie numer_wiersza, a drugą w argumencie numer_kolumny funkcji INDEKS.
Użyj tej formuły do dwukierunkowego wyszukiwania z INDEKSEM i PODAJ.POZYCJĘ:
=INDEKS(A1:F20;POZYCJA(H2;A2:A20,0);POZYCJA(H3;A1:F1,0))Jak wiemy, funkcja PODAJ.POZYCJĘ może szukać wartości zarówno w poziomie, jak iw pionie. W tej formule druga funkcja PODAJ.POZYCJĘ w argumencie kolumna_num znajduje pozycję Quiz2 (4) i przekazuje ją do funkcji INDEKS. A INDEKS pobiera partyturę.
Teraz wiesz, jak korzystać z funkcji Dopasuj w programie Excel.