Znajduj pozycje funkcją MATCH, pobieraj wartości po pozycji funkcją INDEX i łącz je w wyszukiwaniach dwukierunkowych.
Wyszukiwanie · 5 krótkich ćwiczeń w żywym arkuszu: sam wpisujesz każdą formułę i odpowiadaj na pytania, które każą przewidywać wyniki lub wykrywać błędy. Każda odpowiedź jest sprawdzana od razu.
Co będziesz ćwiczyć
Znajdowanie pozycji wartości funkcją MATCH
Zwracanie wartości według wiersza i kolumny funkcją INDEX
Łączenie INDEX i MATCH w jednym wyszukiwaniu
Wyszukiwanie wartości na przecięciu wiersza i kolumny
PODAJ.POZYCJĘ (Angielski: MATCH)(co; gdzie; 0) zwraca pozycję wartości na liście (1 = pierwsza). Zero oznacza dopasowanie dokładne.
INDEKS (Angielski: INDEX)(zakres; wiersz; kolumna) zwraca wartość na podanej pozycji wewnątrz zakresu.
Umieść PODAJ.POZYCJĘ (Angielski: MATCH) wewnątrz INDEKS (Angielski: INDEX), a pozycja zostanie znaleziona za ciebie: wyszukiwanie działa w dowolnym kierunku.
Użyj dwóch funkcji PODAJ.POZYCJĘ (Angielski: MATCH), jednej dla wiersza i jednej dla kolumny, do wyszukiwania dwukierunkowego.
PODAJ.POZYCJĘ (Angielski: MATCH) znajduje, gdzie coś jest na liście. INDEKS (Angielski: INDEX) pobiera element z podanej pozycji. Razem tworzą elastyczne wyszukiwanie, które potrafi szukać w lewo, nie psuje się po wstawieniu kolumn i obsługuje tabele dwukierunkowe.
Dwa elementy
Weź małą tabelę stanów magazynowych: A1:C1 zawiera nagłówki Item, Price i Stock; A2:A4 zawiera Pen, Ruler i Stapler; B2:B4 zawiera ceny 1.5, 2.25 i 7; C2:C4 zawiera stany 40, 15 i 8.
=PODAJ.POZYCJĘ("Ruler";A2:A4;0)
Zwraca 2, pozycję Ruler w zakresie A2:A4.
=INDEKS(B2:B4;2)
Zwraca 2.25, drugi element z B2:B4.
Zero w PODAJ.POZYCJĘ (Angielski: MATCH) oznacza dopasowanie dokładne. Pozycja liczona jest w podanym zakresie, a nie w arkuszu, dlatego wynik to 2, a nie 3.
Łączenie obu funkcji
Niech PODAJ.POZYCJĘ (Angielski: MATCH) wyznaczy pozycję i przekaże ją do INDEKS (Angielski: INDEX).
=INDEKS(B2:B4;PODAJ.POZYCJĘ("Stapler";A2:A4;0))
Zwraca 7, cenę zszywacza.
Ponieważ zakres wyniku i zakres wyszukiwania są oddzielne, klucz może stać po prawej stronie odpowiedzi. Aby znaleźć element, który ma 15 sztuk na stanie:
=INDEKS(A2:A4;PODAJ.POZYCJĘ(15;C2:C4;0))
Zwraca Ruler. WYSZUKAJ.PIONOWO (Angielski: VLOOKUP) tego nie potrafi.
Wyszukiwanie dwukierunkowe
INDEKS (Angielski: INDEX) przyjmuje też numer wiersza i kolumny. Użyj jednego PODAJ.POZYCJĘ (Angielski: MATCH) dla każdego z nich, a odczytasz komórkę, w której przecina się etykieta wiersza z etykietą kolumny.
Tekst nagłówków może pochodzić z komórek, więc para list rozwijanych zamienia to w mały raport.
Więcej zastosowań tej pary
PODAJ.POZYCJĘ (Angielski: MATCH) ma inne typy dopasowania. Przy 1 znajduje największą wartość, która nie przekracza szukanej, na liście posortowanej rosnąco. Przy -1 znajduje najmniejszą wartość, która nie jest od niej mniejsza, na liście posortowanej malejąco. To wersje przybliżone, przydatne przy przedziałach.
INDEKS (Angielski: INDEX) z numerem wiersza 0 zwraca całą kolumnę, więc =SUMA(INDEKS(B2:C4;0;2)) dodaje drugą kolumnę bloku: 63 sztuki na stanie. A =INDEKS(A2:A4;ILE.NIEPUSTYCH(A2:A4)) pobiera ostatni element listy, która stale się wydłuża, tutaj Stapler.
Dlaczego ludzie to lubią i jakie są pułapki
Wstawianie i usuwanie kolumn nie psuje formuły, ponieważ operuje ona na zakresach, a nie na numerze kolumny.
Brakujący klucz zwraca #N/D. Owiń formułę w JEŻELI.ND (Angielski: IFNA), aby pokazać własny komunikat.
Pilnuj, aby zakresy były wyrównane. Jeśli zakres INDEKS (Angielski: INDEX) zaczyna się w wierszu 2, a zakres PODAJ.POZYCJĘ (Angielski: MATCH) w wierszu 3, dostaniesz wartość sąsiada.
Nie pomijaj 0 w PODAJ.POZYCJĘ (Angielski: MATCH). Domyślne 1 oznacza wyszukiwanie przybliżone, które wymaga posortowanych danych.
Gdy dostępne jest X.WYSZUKAJ (Angielski: XLOOKUP), proste wyszukiwania są krótsze. INDEKS (Angielski: INDEX) i PODAJ.POZYCJĘ (Angielski: MATCH) nadal działają we wszystkich wersjach i warto je znać.