INDEX-MATCH stało się bardziej popularnym narzędziem dla programu Excel, ponieważ rozwiązuje ograniczenia funkcji WYSZUKAJ.PIONOWO i jest łatwiejsze w użyciu. Funkcja PODAJ INDEKS w programie Excel ma wiele zalet w porównaniu z funkcją WYSZUKAJ.PIONOWO:

1. INDEX i MATCH są bardziej elastyczne i szybsze niż Vlookup
2. Możliwe jest wykonywanie wyszukiwania poziomego, wyszukiwania pionowego, wyszukiwania dwukierunkowego, wyszukiwania w lewo, wyszukiwania z rozróżnianiem wielkości liter, a nawet wyszukiwania w oparciu o wiele kryteriów.
3. W przypadku posortowanych danych funkcja INDEX-MATCH jest o 30% szybsza niż WYSZUKAJ.PIONOWO. Oznacza to, że w przypadku większego zbioru danych 30% szybciej ma większy sens.

Zacznijmy od szczegółowych koncepcji każdego INDEKSU i DOPASOWANIA.

Funkcja INDEKS

Funkcja INDEKS w Excelu jest bardzo potężnym jednocześnie elastycznym narzędziem, które pobiera wartość z danego miejsca w zakresie. Inaczej mówiąc, zwraca zawartość komórki określoną przez przesunięcie wiersza i kolumny.

Składnia:

=INDEX(reference, [row], [column])>

Parametry:

odniesienie: tablica komórek, do których ma zostać przesunięte. Może to być pojedynczy zakres lub cały zbiór danych w tabeli danych. wiersz [opcjonalnie]: Liczba przesuniętych wierszy. Oznacza to, że jeśli wybierzemy zakres odniesienia tabeli jako A1:A5, wówczas komórka/treść, którą chcemy wyodrębnić, będzie znajdować się w odległości pionowej. Tutaj dla wiersza A1 będzie 1, dla wiersza A2 = 2 i tak dalej. Jeśli podamy wiersz = 4, wyodrębni A4. Ponieważ wiersz jest opcjonalny, więc jeśli nie określimy żadnego numeru wiersza, wyodrębnione zostaną całe wiersze z zakresu odniesienia. W tym przypadku są to A1 do A5. kolumna [opcjonalnie]: Liczba kolumn przesuniętych. Oznacza to, że jeśli wybierzemy zakres odniesienia tabeli jako A1: B5, wówczas komórka/treść, którą chcemy wyodrębnić, będzie znajdować się w odległości poziomej. Tutaj dla A1 wiersz będzie 1, a kolumna 1, dla B1 wiersz będzie 1, ale kolumna będzie 2 podobnie dla A2 rząd = 2 kolumna = 1, dla B2 rząd = 2 kolumna = 2 i tak dalej. Jeśli podamy wiersz = 5 i kolumnę 2, wówczas wyodrębni B5. Ponieważ kolumna jest opcjonalna, więc jeśli nie podajemy żadnego wiersza, nr. następnie wyodrębni całą kolumnę w zakresie odniesienia. Na przykład, jeśli podamy wiersz = 2 i kolumnę jako puste, wówczas zostanie wyodrębniony (A2:B2). Jeśli nie określimy wiersza i kolumny, wyodrębniona zostanie cała tabela referencyjna (A1:B5).

Tabela referencyjna: Poniższa tabela będzie używana jako tabela referencyjna dla wszystkich przykładów funkcji INDEKS. Pierwsza komórka znajduje się w B3 (FOOD), a ostatnia komórka po przekątnej znajduje się w F10 (180).

Przykłady: Poniżej znajduje się kilka przykładów funkcji Index.

Przypadek 1: Nie ma wzmianki o wierszach ani kolumnach.

Polecenie wejściowe: =INDEKS(B3:C10)

Przypadek 2: Wspomniane są tylko wiersze.

Polecenie wejściowe: =INDEKS(B3:C10;2)

Przypadek 3: Wymienione są zarówno wiersze, jak i kolumny.

Polecenie wejściowe: =INDEKS(B3:D10;4,2)

Przypadek 4: Wspomniane są tylko kolumny.

Polecenie wejściowe: =INDEKS(B3 : D10 , , 2)

Problem z funkcją INDEX: Problem z funkcją INDEKS polega na konieczności określenia wierszy i kolumn dla danych, których szukamy. Załóżmy, że mamy do czynienia ze zbiorem danych uczenia maszynowego składającym się z 10000 wierszy i kolumn, wówczas bardzo trudno będzie wyszukać i wyodrębnić dane, których szukamy. Oto koncepcja funkcji dopasowania, która identyfikuje wiersze i kolumny na podstawie pewnego warunku.

Funkcja DOPASUJ

Pobiera pozycję elementu/wartości w zakresie. Jest to mniej wyrafinowana wersja funkcji WYSZUKAJ.PIONOWO lub WYSZUKAJ.POZIOMO, która zwraca jedynie informacje o lokalizacji, a nie rzeczywiste dane. W funkcji MATCH nie jest rozróżniana wielkość liter i nie ma znaczenia, czy zakres jest poziomy, czy pionowy.

Składnia:

=MATCH(search_key, range, [search_type])>

Parametry:

search_key: Wartość do wyszukania. Na przykład 42, Koty lub I24. zakres: Jednowymiarowa tablica, która ma zostać przeszukana. Może to być pojedynczy wiersz lub pojedyncza kolumna. np.->A1:A10, A2:D2 itd. typ_wyszukiwania [opcjonalne]: Metoda wyszukiwania. = 1 (domyślnie) znajduje największą wartość mniejszą lub równą parametrowi search_key, gdy zakres jest posortowany w kolejności rosnącej.
• = 0 pozwala znaleźć dokładną wartość, gdy zakres nie jest posortowany.
• = -1 znajduje najmniejszą wartość większą lub równą parametrowi klucz_wyszukiwania, gdy zakres jest sortowany w kolejności malejącej.

Numer wiersza lub numer kolumny można znaleźć za pomocą funkcji dopasowania i można go użyć w funkcji indeksu, więc jeśli istnieją jakieś szczegóły dotyczące elementu, wszystkie informacje można wyodrębnić o elemencie, znajdując wiersz/kolumnę elementu za pomocą dopasowania następnie zagnieżdżanie go w funkcji indeksu.

Tabela referencyjna: Poniższa tabela będzie używana jako tabela referencyjna dla wszystkich przykładów funkcji PODAJ. Pierwsza komórka znajduje się w B3 (FOOD), a ostatnia komórka po przekątnej to F10 (180)

Przykłady: Poniżej znajduje się kilka przykładów funkcji MATCH-

Przypadek 1: Typ wyszukiwania 0, oznacza Dokładne dopasowanie.

Polecenie wejściowe: =PODAJ(POŁUDNIOWE INDYJE,C3:C10,0)

Przypadek 2: Typ wyszukiwania 1 (domyślny).

Polecenie wejściowe: =PODAJ(Południowoindyjskie,C3:C10)

pawandeep rajan

Przypadek 3: Typ wyszukiwania -1.

Polecenie wejściowe: =PODAJ(POŁUDNIOWE INDYJE,C3:C10,-1)

INDEX-MATCH Razem

W poprzednich przykładach statyczne wartości wierszy i kolumn podano w funkcji INDEKS. Załóżmy, że nie ma wcześniejszej wiedzy na temat położenia wierszy i kolumn, więc położenie wierszy i kolumn można określić za pomocą funkcji DOPASUJ. Jest to dynamiczny sposób wyszukiwania i wyodrębniania wartości.

Składnia:

 =INDEX(Reference Table , [Match(SearchKey,Range,Type)/StaticRowPosition],  [Match(SearchKey,Range,Type)/StaticColumnPosition])>

Tabela referencyjna: Wykorzystana zostanie poniższa tabela referencyjna. Pierwsza komórka znajduje się w B3 (FOOD), a ostatnia komórka po przekątnej to F10 (180)

Przykład: Załóżmy, że zadaniem jest znalezienie kosztu Masala Dosa. Wiadomo, że kolumna 3 przedstawia koszt pozycji, ale pozycja Masala Dosa w wierszu nie jest znana. Problem można podzielić na dwa etapy –

Krok 1: Znajdź pozycję Masala Dosa, korzystając ze wzoru:

 =MATCH('Masala Dosa',B3:B10,0)>

Tutaj B3:B10 oznacza Żywność w kolumnie, a 0 oznacza Dokładne dopasowanie. Zwróci numer wiersza Masala Dosa.

Krok 2: Znajdź koszt Masala Dosa. Użyj funkcji INDEX, aby znaleźć koszt Masala Dosa. Podstawiając powyższą funkcję MATCH zapytaj wewnątrz funkcji INDEX w miejscu, w którym wymagana jest dokładna pozycja Masala Dosa, a numer kolumny kosztu wynosi 3, co jest już znane.

=INDEX(B3:F10, MATCH('Masala Dosa', B3:B10 , 0) ,3)>

Wyszukiwanie na dwa sposoby razem z INDEX-MATCH

W poprzednim przykładzie pozycja kolumny atrybutu Koszt została zakodowana na stałe. Nie było to więc w pełni dynamiczne.

Przypadek 1: Załóżmy, że nie ma wiedzy na temat numeru kolumny Koszt, wówczas można go obliczyć za pomocą wzoru:

 =MATCH('Cost',B3:F3,0)>

Tutaj B3:F3 reprezentuje kolumnę nagłówka.

Przypadek 2: Gdy wartość wiersza i kolumny jest podawana za pomocą funkcji MATCH (bez podawania wartości statycznej), wówczas nazywa się to wyszukiwaniem dwukierunkowym. Można to osiągnąć za pomocą wzoru:

 =INDEX(B3:F10, MATCH('Masala Dosa',B3:B10, 0) , MATCH('Cost' ,B3:F3 ,0))>

Lewe wyszukiwanie

Jedną z kluczowych zalet INDEKS i DOPASUJ w porównaniu z funkcją WYSZUKAJ.PIONOWO jest możliwość wykonania wyszukiwania w lewo. Oznacza to, że możliwe jest wyodrębnienie pozycji wiersza elementu na podstawie dowolnego atrybutu po prawej stronie oraz wartości innego atrybutu po lewej stronie.

Załóżmy na przykład, że kupuj żywność, której koszt powinien wynosić 140 Rs. Pośrednio mówimy: kup Biryani. W tym przykładzie znany jest koszt 140 Rs, konieczne jest wydobycie Żywności. Ponieważ kolumna Koszt jest umieszczona na prawo od kolumny Żywność. Jeśli zostanie zastosowana funkcja WYSZUKAJ.PIONOWO, nie będzie możliwe przeszukiwanie lewej strony kolumny Koszt. Dlatego przy użyciu WYSZUKAJ.PIONOWO nie można uzyskać nazwy żywności.

Aby przezwyciężyć tę wadę, można zastosować funkcję INDEX-MATCH.
Krok 1: Najpierw wyodrębnij pozycję wiersza o koszcie 140 Rs, korzystając ze wzoru:

 =MATCH(140, D3:D10,0)>

Tutaj D3: D10 reprezentuje kolumnę Koszt, w której odbywa się wyszukiwanie numeru wiersza Koszt 140 Rs.

Krok 2: Po uzyskaniu numeru wiersza następnym krokiem jest użycie funkcji INDEX w celu wyodrębnienia nazwy żywności za pomocą wzoru:

 =INDEX(B3:B10, MATCH(140, D3:D10,0))>

Tutaj B3:B10 reprezentuje kolumnę żywności, a 140 to koszt artykułu spożywczego.

Wyszukiwanie uwzględniające wielkość liter

Sama funkcja DOPASUJ nie uwzględnia wielkości liter. Oznacza to, że istnieje nazwa żywności DHOKLA i funkcja DOPASUJ jest używana z następującym wyszukiwanym słowem:

1. Dhokla
2. dhokla
3. DhokLA

Wszystko zwróci pozycję wiersza DHOKLA. Jednakże funkcji DOKŁADNY można używać z INDEKSEM i PODAJNIKIEM, aby przeprowadzić wyszukiwanie uwzględniające wielkie i małe litery.

Dokładna funkcja: Funkcja Excel EXACT porównuje dwa ciągi tekstowe, biorąc pod uwagę duże i małe litery, i zwraca PRAWDA, jeśli są takie same, lub FAŁSZ, jeśli nie. W EXACT rozróżniana jest wielkość liter.

Przykłady:

EXACT(DHOKLA,DHOKLA): To zwróci True. EXACT(DHOKLA,Dhokla): To zwróci False. EXACT(DHOKLA,dhokla): To zwróci False. EXACT(DHOKLA,DhOkLA): Zwróci wartość False.

Przykład: Załóżmy, że zadaniem jest wyszukanie rodzaju żywności Dhokla, ale z uwzględnieniem wielkości liter. Można to zrobić za pomocą wzoru-

 =INDEX(C3:C10, MATCH(TRUE , EXACT('Dhokla', B3:B10) ,0))>

Tutaj funkcja EXACT zwróci True, jeśli wartość w kolumnie B3:B10 pasuje do Dhokli w tym samym przypadku, w przeciwnym razie zwróci False. Teraz funkcja MATCH zostanie zastosowana w kolumnie B3:B10 i wyszuka wiersz z wartością Dokładna PRAWDA. Następnie funkcja INDEX pobierze wartość z kolumny C3:C10 (kolumna typu żywności) w wierszu zwróconym przez funkcję MATCH.

Wyszukiwanie według wielu kryteriów

Jednym z najtrudniejszych problemów w Excelu jest wyszukiwanie na podstawie wielu kryteriów. Innymi słowy, wyszukiwanie pasujące do więcej niż jednej kolumny jednocześnie. W poniższym przykładzie funkcje INDEX i MATCH oraz logika boolowska służą do dopasowania 3 kolumn:

1. Żywność.
2. Koszt.
3. Ilość.

Aby wyodrębnić całkowity koszt.

Przykład: Załóżmy, że zadaniem jest obliczenie całkowitego kosztu makaronu

Jedzenie: makarony. Koszt: 60. Ilość: 1.

Zatem w tym przykładzie istnieją trzy kryteria przeprowadzenia Dopasowania. Poniżej znajdują się kroki wyszukiwania na podstawie wielu kryteriów:

Krok 1: Najpierw dopasuj kolumnę Żywność (B3:B10) do Pasta, korzystając ze wzoru:

 'PASTA' = B3:B10>

Spowoduje to konwersję wartości B3:B10 (kolumna żywności) na wartość logiczną. To prawda, gdy jedzenie to makaron, w przeciwnym razie fałsz.

Krok 2: Następnie dopasuj kryteria kosztu w następujący sposób:

 60 = D3:D10>

Spowoduje to zastąpienie wartości D3:D10 (kolumna kosztu) wartościami logicznymi. To prawda, gdy koszt = 60, w przeciwnym razie fałsz.

Krok 3: Następnym krokiem jest dopasowanie trzeciego kryterium, czyli Ilość = 1, w następujący sposób:

 1 = E3:E10>

Spowoduje to zastąpienie kolumny E3:E10 (kolumna ilości) wartością Prawda, gdzie Ilość = 1, w przeciwnym razie będzie to wartość Fałsz.

Krok 4: Pomnóż wynik pierwszego, drugiego i trzeciego kryterium. Będzie to przecięcie wszystkich warunków i przekonwertuje wartość logiczną Prawda/Fałsz na 1/0.

Krok 5: Teraz wynikiem będzie kolumna zawierająca 0 i 1. Tutaj użyj funkcji DOPASUJ, aby znaleźć numer wiersza kolumn zawierających 1. Ponieważ jeśli kolumna ma wartość 1, oznacza to, że spełnia wszystkie trzy kryteria.

Krok 6: Po uzyskaniu numeru wiersza użyj funkcji INDEX, aby uzyskać całkowity koszt tego wiersza.

 =INDEX(F3:F10, MATCH(1, ('Pasta'=B3:B10) * (60=D3:D10) * (1=E3:E10) , 0 ))>

Tutaj F3:F10 reprezentuje kolumnę kosztów całkowitych.