Funkcja INDEX może być używana samodzielnie, ale zagnieżdżenie w niej funkcji PODAJ. POZYCJĘ tworzy zaawansowane wyszukiwanie.
Ta zagnieżdżona funkcja jest bardziej elastyczna niż WYSZUKAJ. PIONOWO i może szybciej generować wyniki.
W tym artykule wyjaśniono, jak używać funkcji INDEKS i PODAJ. POZYCJĘ jednocześnie we wszystkich wersjach programu Excel, w tym w programie Excel 2019 i Microsoft 365.
Jakie są funkcje INDEKS i PODAJ. POZYCJĘ?
INDEKS i PODAJ. POZYCJĘ to funkcje wyszukiwania programu Excel. Chociaż są to dwie całkowicie oddzielne funkcje, których można używać samodzielnie, można je również łączyć w celu tworzenia zaawansowanych formuł.
Funkcja INDEX zwraca wartość lub odwołanie do wartości z określonego zaznaczenia. Na przykład można go użyć do znalezienia wartości w drugim wierszu zestawu danych lub w piątym wierszu i trzeciej kolumnie.
Chociaż INDEKS może być używany samodzielnie, zagnieżdżenie PODAJ. POZYCJĘ w formule czyni go nieco bardziej użytecznym. Funkcja PODAJ. POZYCJĘ wyszukuje określony element w zakresie komórek, a następnie zwraca względną pozycję elementu w zakresie. Na przykład może służyć do określenia, że określone imię jest trzecią pozycją na liście nazwisk.
Image
INDEKS i DOPASUJ Składnia i argumenty
Tak należy napisać obie funkcje, aby program Excel je zrozumiał:
=INDEX(tablica, numer_wiersza, [numer_kolumny])
tablica to zakres komórek, których będzie używać formuła. Może to być jeden lub więcej wierszy i kolumn, na przykład A1:D5. Jest to wymagane.
row_num to wiersz w tablicy, z którego ma zostać zwrócona wartość, na przykład 2 lub 18. Jest to wymagane, chyba że występuje numer kolumny.
column_num to kolumna w tablicy, z której ma zostać zwrócona wartość, na przykład 1 lub 9. Jest to opcjonalne.
lookup_value to wartość, którą chcesz dopasować w lookup_array. Może to być liczba, tekst lub wartość logiczna wpisana ręcznie lub przywoływana przez odwołanie do komórki. Jest to wymagane.
lookup_array to zakres komórek do przejrzenia. Może to być pojedynczy wiersz lub pojedyncza kolumna, na przykład A2:D2 lub G1:G45. Jest to wymagane.
match_type może mieć wartość -1, 0 lub 1. Określa, w jaki sposób szukana_wartość jest dopasowywana do wartości w szukanej_tablicy (patrz poniżej). 1 jest wartością domyślną, jeśli ten argument zostanie pominięty.
Który typ dopasowania użyć
Typ dopasowania
Co robi
Reguła
Przykład
1
Znajduje największą wartość, która jest mniejsza lub równa search_value.
Wartości lookup_array muszą być umieszczone w porządku rosnącym (np. -2, -1, 0, 1, 2; lub A-Z; lub FALSE, TRUE.
lookup_value to 25, ale brakuje jej w lookup_array, więc zamiast tego zwracana jest pozycja następnej najmniejszej liczby, np. 22.
0
Znajduje pierwszą wartość, która jest dokładnie równa search_value.
Wartości lookup_array mogą być w dowolnej kolejności.
lookup_value to 25, więc zwraca pozycję 25.
-1
Znajduje najmniejszą wartość, która jest większa lub równa search_value.
Wartości lookup_array muszą być umieszczone w kolejności malejącej (np. 2, 1, 0, -1, -2).
lookup_value to 25, ale brakuje jej w lookup_array, więc zamiast tego zwracana jest pozycja następnej największej liczby, np. 34.
Użyj 1 lub -1, gdy potrzebujesz przeprowadzić przybliżone wyszukiwanie wzdłuż skali, na przykład gdy masz do czynienia z liczbami i gdy aproksymacje są w porządku. Pamiętaj jednak, że jeśli nie określisz match_type, 1 będzie wartością domyślną, co może przekrzywić wyniki, jeśli naprawdę chcesz dokładnego dopasowania.
Przykładowe formuły INDEKS i PODAJ. POZYCJĘ
Zanim przyjrzymy się, jak połączyć INDEKS i PODAJ. POZYCJĘ w jedną formułę, musimy zrozumieć, jak te funkcje działają samodzielnie.
INDEX Przykłady
=INDEX(A1:B2, 2, 2)
=INDEX(A1:B1, 1)
=INDEX(2:2, 1)=INDEKS(B1:B2, 1)
Image
W tym pierwszym przykładzie są cztery formuły INDEKS, których możemy użyć do uzyskania różnych wartości:
=INDEX(A1:B2, 2, 2) przegląda A1:B2 w celu znalezienia wartości w drugiej kolumnie i drugim wierszu, czyli Stacy.
=INDEX(A1:B1, 1) przegląda A1:B1 w celu znalezienia wartości w pierwszej kolumnie, którą jest Jon.
=INDEX(2:2, 1) przegląda wszystko w drugim wierszu, aby znaleźć wartość w pierwszej kolumnie, czyli Tim.
=INDEX(B1:B2, 1) przegląda B1:B2 w celu zlokalizowania wartości w pierwszym wierszu, czyli Amy.
Przykłady dopasowania
=PODZIEL("Stacjonarny", A2:D2, 0)
=PODZIEL(14, D1:D2)
=POZYCJA(14, D1:D2, -1)=DOPASUJ(13, A1:D1, 0)
Image
Oto cztery proste przykłady funkcji PODAJ. POZYCJĘ:
=MATCH("Stacy", A2:D2, 0) wyszukuje Stacy w zakresie A2:D2 i zwraca 3 jako wynik.
=MATCH(14, D1:D2) wyszukuje 14 w zakresie D1:D2, ale ponieważ nie zostało znalezione w tabeli, funkcja PODAJ. POZYCJĘ znajduje następną największą wartość to jest mniejsze lub równe 14, co w tym przypadku wynosi 13, co znajduje się na pozycji 1 w wyszukiwanej_tablicy.
=MATCH(14, D1:D2, -1) jest identyczny z powyższą formułą, ale ponieważ tablica nie jest w porządku malejącym, jak -1 wymaga, otrzymujemy błąd.
=PODZIEL(13, A1:D1, 0) szuka 13 w pierwszym wierszu arkusza, który zwraca 4, ponieważ jest to czwarty element w tej tablicy.
Przykłady dopasowania indeksu
Oto dwa przykłady, w których możemy połączyć INDEKS i PODAJ. POZYCJĘ w jednej formule:
Znajdź odwołanie do komórki w tabeli
=INDEKS(B2:B5; PODAJ. POZYCJĘ(F1, A2:A5))
Image
Ten przykład dotyczy zagnieżdżania formuły PODAJ. POZYCJĘ w formule INDEKS. Celem jest zidentyfikowanie koloru przedmiotu za pomocą numeru przedmiotu.
Jeżeli spojrzysz na obraz, możesz zobaczyć w wierszach "Oddzielne" sposób, w jaki formuły zostałyby napisane samodzielnie, ale ponieważ je zagnieżdżamy, dzieje się tak:
MATCH(F1, A2:A5) szuka wartości F1 (8795) w zestawie danych A2:A5. Jeśli odliczymy kolumnę, zobaczymy, że to 2, więc właśnie to odkryła funkcja PODAJ. POZYCJĘ.
Tablica INDEX to B2:B5, ponieważ ostatecznie szukamy wartości w tej kolumnie.
Funkcja INDEKS może być teraz przepisana w ten sposób, ponieważ 2 jest tym, co znalazło PODAJ. POZYCJĘ: INDEKS(B2:B5, 2, [kolumna_num]).
Ponieważ kolumna_num jest opcjonalna, możemy ją usunąć w następujący sposób: INDEKS(B2:B5, 2).
Teraz jest to jak normalna formuła INDEKS, w której znajdujemy wartość drugiej pozycji w B2:B5, która jest czerwona.
W tym przykładzie PODAJ. POZYCJĘ i INDEKS wykonujemy wyszukiwanie dwukierunkowe. Chodzi o to, żeby zobaczyć, ile pieniędzy zarobiliśmy na zielonych przedmiotach w maju. Jest to bardzo podobne do powyższego przykładu, ale dodatkowa formuła PODAJ. POZYCJĘ jest zagnieżdżona w INDEKS.
MATCH(G1, A2:A13, 0) to pierwszy element rozwiązany w tym wzorze. Szuka G1 (słowo „maj”) w A2:A13, aby uzyskać konkretną wartość. Nie widzimy tego tutaj, ale jest 5.
PODZIEL(G2, B1:E1, 0) to druga formuła PODAJ. POZYCJĘ i jest bardzo podobna do pierwszej, ale zamiast tego szuka G2 (słowo „Zielony”) w nagłówkach kolumn w B1:E1. Ten rozwiązuje się do 3.
Możemy teraz przepisać formułę INDEKS w ten sposób, aby zwizualizować to, co się dzieje: =INDEX(B2:E13, 5, 3). To szuka w całej tabeli, B2:E13, piątego wiersza i trzeciej kolumny, która zwraca 180 USD.
Zasady dopasowania i indeksowania
Podczas pisania formuł za pomocą tych funkcji należy pamiętać o kilku rzeczach:
MATCH nie rozróżnia wielkości liter, więc wielkie i małe litery są traktowane tak samo podczas dopasowywania wartości tekstowych.
MATCH zwraca N/A z wielu powodów: jeśli match_type wynosi 0, a lookup_value nie jest znalezione, jeśli match_type to -1, a lookup_array nie jest w kolejności malejącej, jeśli match_type to 1, a lookup_array nie jest w kolejności rosnącej kolejność i jeśli lookup_array nie jest pojedynczym wierszem lub kolumną.
Możesz użyć symbolu wieloznacznego w argumencie szukana_wartość, jeśli typ_dopasowania wynosi 0, a szukana_wartość jest ciągiem tekstowym. Znak zapytania pasuje do dowolnego pojedynczego znaku, a gwiazdka do dowolnej sekwencji znaków (np.np. =MATCH("Jo", 1:1, 0)). Aby użyć funkcji PODAJ. POZYCJĘ do znalezienia rzeczywistego znaku zapytania lub gwiazdki, wpisz najpierw ~.
INDEKS zwraca REF! jeśli row_num i column_num nie wskazują komórki w tablicy.
Powiązane funkcje programu Excel
Funkcja PODAJ. POZYCJĘ jest podobna do funkcji WYSZUKAJ, ale PODAJ. POZYCJĘ zwraca pozycję elementu zamiast samego elementu.
WYSZUKAJ. PIONOWO to kolejna funkcja wyszukiwania, której można używać w programie Excel, ale w przeciwieństwie do PODAJ. POZYCJĘ, która wymaga INDEKSU do zaawansowanych wyszukiwań, formuły WYSZUKAJ. PIONOWO wymagają tylko tej jednej funkcji.
Funkcja DZIEŃ programu Excel zwraca datę jako liczbę całkowitą, co jest przydatne w niektórych sytuacjach finansowych. Poznaj składnię, argumenty i sposób korzystania z funkcji DZIEŃ w programie Excel
Funkcja INDEKS programu Excel to formuła, która pobiera wartość z listy lub tabeli na podstawie danych o lokalizacji wprowadzonych do formuły. Oto jak używać formuły INDEKS wraz z przykładami
Funkcja LICZ.JEŻELI w programie Excel zlicza komórki spełniające określone kryteria, co może być przydatne w wielu przypadkach. Oto jak używać LICZ.JEŻELI z pojedynczymi kryteriami w programie Excel
