Excel INDEKS Funkcja - Przykłady formuł + DARMOWE wideo

Funkcja INDEKS Excela (przykłady + wideo)

Kiedy używać funkcji INDEKS Excela?

Funkcji Excel INDEX można użyć, gdy chcesz pobrać wartość z danych tabelarycznych i masz numer wiersza i numer kolumny punktu danych. Na przykład w poniższym przykładzie możesz użyć funkcji INDEX, aby uzyskać znaki „Tomka” w fizyce, gdy znasz numer wiersza i numer kolumny w zestawie danych.

Co zwraca

Zwraca wartość z tabeli dla określonego numeru wiersza i numeru kolumny.

Składnia

=INDEKS (tablica, numer_wiersza, [numer_kolumny])
=INDEKS (tablica, numer_wiersza, [numer_kolumny], [numer_obszaru])

Funkcja INDEKS ma 2 składnie. W większości przypadków używana jest pierwsza, jednak w przypadku wyszukiwań trójstronnych używana jest druga (opisana w przykładzie 5).

Argumenty wejściowe

• tablica - a zakres komórek lub stała tablicowa.
• numer_wiersza - numer wiersza, z którego ma zostać pobrana wartość.
• [liczba_kolumny] - numer kolumny, z której ma zostać pobrana wartość. Chociaż jest to argument opcjonalny, ale jeśli nie podano numeru wiersza, należy go podać.
• [numer_obszaru] - (Opcjonalnie) Jeśli argument tablica składa się z wielu zakresów, ta liczba zostanie użyta do wybrania odwołania ze wszystkich zakresów.

Dodatkowe uwagi (nudne rzeczy… ale ważne, aby wiedzieć)

• Jeśli numer wiersza lub numer kolumny wynosi 0, zwraca odpowiednio wartości całego wiersza lub kolumny.
• Jeśli funkcja INDEKS jest używana przed odwołaniem do komórki (np. A1:), zwraca odwołanie do komórki zamiast wartości (zobacz przykłady poniżej).
• Najczęściej używany wraz z funkcją PODAJ.POZYCJĘ.
• W przeciwieństwie do funkcji WYSZUKAJ.PIONOWO, funkcja INDEKS może zwrócić wartość z lewej strony wartości wyszukiwania.
• Funkcja INDEX ma dwie formy - Array form i Reference form
  • „Formularz tablicy” to miejsce, w którym pobierasz wartość na podstawie numeru wiersza i kolumny z danej tabeli.
  • „Formularz referencyjny” to sytuacja, w której istnieje wiele tabel i używasz argumentu numer_obszaru, aby wybrać tabelę, a następnie pobrać z niej wartość za pomocą numeru wiersza i kolumny (patrz przykład na żywo poniżej).

Funkcja INDEKS Excela - Przykłady

Oto sześć przykładów użycia funkcji INDEKS Excela.

Przykład 1 - Znajdowanie znaków Toma w fizyce (dwukierunkowe wyszukiwanie)

Załóżmy, że masz zbiór danych, jak pokazano poniżej:

Aby znaleźć oceny Toma w fizyce, użyj poniższego wzoru:

=INDEKS($B$3:$E$10,3,2)

Ta formuła INDEKS określa tablicę jako $B3:$E$10, która zawiera znaki dla wszystkich przedmiotów. Następnie używa numeru wiersza (3) i numeru kolumny (2), aby pobrać oceny Toma z fizyki.

Przykład 2 - Dynamiczna wartość LOOKUP za pomocą funkcji PODAJ.POZYCJĘ

Nie zawsze jest możliwe ręczne określenie numeru wiersza i numeru kolumny. Możesz mieć ogromny zestaw danych lub możesz chcieć uczynić go dynamicznym, aby automatycznie identyfikował imię i / lub temat określony w komórkach i dawał poprawny wynik.

Coś, jak pokazano poniżej:

Można to zrobić za pomocą kombinacji funkcji INDEKS i PODAJ.POZYCJĘ.

Oto wzór, który sprawi, że wartości wyszukiwania będą dynamiczne:

=INDEKS($ B$3:$E$10;DOPASUJ($G$5;$A$3:$A$100));MATCH($H$4$B$2:$E$2;0))

W powyższym wzorze zamiast na stałe kodować numer wiersza i numer kolumny, używana jest funkcja PODAJ.POZYCJĘ, aby uczynić ją dynamiczną.

• Dynamiczny numer wiersza jest podany przez następującą część formuły - MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0). Skanuje nazwiska uczniów i identyfikuje wartość wyszukiwania (w tym przypadku 5 USD). Następnie zwraca numer wiersza wartości wyszukiwania w zestawie danych. Na przykład, jeśli wartością wyszukiwania jest Matt, zwróci 1, jeśli to Bob, zwróci 2 i tak dalej.
• Dynamiczny numer kolumny jest podany przez następującą część formuły - MATCH($H$4,$B$2:$E$2,0). Skanuje nazwy podmiotów i identyfikuje wartość wyszukiwania (w tym przypadku $ H $ 4). Następnie zwraca numer kolumny wartości wyszukiwania w zestawie danych. Na przykład, jeśli wartością wyszukiwania jest Math, zwróci 1, jeśli jest to Fizyka, zwróci 2 i tak dalej.

Przykład 3 - Używanie list rozwijanych jako wartości wyszukiwania

W powyższym przykładzie musimy ręcznie wprowadzić dane. Może to być czasochłonne i podatne na błędy, zwłaszcza jeśli masz ogromną listę wartości wyszukiwania.

Dobrym pomysłem w takich przypadkach jest utworzenie rozwijanej listy wartości wyszukiwania (w tym przypadku mogą to być nazwiska uczniów i przedmioty), a następnie po prostu wybierz z listy. Na podstawie wyboru formuła automatycznie zaktualizuje wynik.

Coś, jak pokazano poniżej:

To sprawia, że ​​jest to dobry komponent pulpitu nawigacyjnego, ponieważ możesz mieć ogromny zestaw danych z setkami uczniów na zapleczu, ale użytkownik końcowy (powiedzmy, że nauczyciel) może szybko uzyskać oceny ucznia z danego przedmiotu, po prostu dokonując wyborów z menu rozwijane.

Jak to zrobić:

Formuła zastosowana w tym przypadku jest taka sama jak w przykładzie 2.

=INDEKS($ B$3:$E$10,DOPASOWAĆ($G$5,$A$3:$A$10,0);MATCH($H$4,$B$2:$E$2,0))

Wartości wyszukiwania zostały przekonwertowane na listy rozwijane.

Oto kroki, aby utworzyć rozwijaną listę programu Excel:

• Wybierz komórkę, w której chcesz wyświetlić listę rozwijaną. W tym przykładzie w G4 chcemy nazwiska uczniów.
• Przejdź do Dane -> Narzędzia danych -> Walidacja danych.
• W oknie dialogowym sprawdzania poprawności danych na karcie ustawień wybierz opcję Lista z listy rozwijanej Zezwalaj.
• W źródle wybierz $ A $ 3: $ A $ 10
• Kliknij OK.

Teraz będziesz mieć listę rozwijaną w komórce G5. Podobnie możesz stworzyć jeden w H4 dla badanych.

Przykład 4 - Zwracanie wartości z całego wiersza/kolumny

W powyższych przykładach użyliśmy funkcji Excel INDEX, aby wykonać wyszukiwanie dwukierunkowe i uzyskać pojedynczą wartość.

A co jeśli chcesz zdobyć wszystkie stopnie ucznia. Dzięki temu możesz znaleźć maksymalny/minimalny wynik tego ucznia lub łączną liczbę punktów uzyskanych ze wszystkich przedmiotów.

W prostym języku angielskim chcesz najpierw uzyskać cały rząd wyników dla ucznia (powiedzmy Boba), a następnie w ramach tych wartości określić najwyższy wynik lub sumę wszystkich wyników.

Oto sztuczka.

W funkcji INDEKS Excela, po wprowadzeniu numer kolumny jako 0, zwróci wartości całego wiersza.

Więc wzór na to byłby następujący:

=INDEKS($B$3:$E$10;DOPASUJ($G$5;$A$3:$A$10,0));0)

Teraz ta formuła. jeśli zostanie użyty w takim stanie, zwróci #ARG! błąd. Podczas gdy wyświetla błąd, w zapleczu zwraca tablicę zawierającą wszystkie wyniki dla Toma - {57,77,91,91}.

Jeśli wybierzesz formułę w trybie edycji i naciśniesz F9, zobaczysz zwróconą przez nią tablicę (jak pokazano poniżej):

Podobnie, w oparciu o wartość wyszukiwania, gdy numer kolumny jest określony jako 0 (lub pozostaje pusty), zwraca wszystkie wartości w wierszu dla wartości wyszukiwania

Teraz, aby obliczyć całkowity wynik uzyskany przez Toma, możemy po prostu użyć powyższego wzoru w funkcji SUMA.

= SUMA (INDEKS ($ B $ 3: $ E $ 10; DOPASUJ ($ G $ 5; $ A $ 3: $ A $ 10, 0); 0), 0)

W podobnych liniach, aby obliczyć najwyższy wynik, możemy użyć MAX/LARGE, a do obliczenia minimum możemy użyć MIN/SMALL.

Przykład 5 - Wyszukiwanie trójdrożne za pomocą funkcji INDEX/MATCH

Funkcja Excel INDEX jest zbudowana do obsługi wyszukiwania trójstronnego.

Co to jest wyszukiwanie trójstronne?

W powyższych przykładach wykorzystaliśmy jedną tabelę z wynikami dla uczniów z różnych przedmiotów. Jest to przykład wyszukiwania dwukierunkowego, ponieważ do pobrania wyniku używamy dwóch zmiennych (nazwisko ucznia i temat).

Załóżmy teraz, że za rok uczeń ma trzy różne poziomy egzaminów, test jednostkowy, egzamin śródsemestralny i egzamin końcowy (to właśnie miałem, gdy byłem studentem).

Wyszukiwanie trójstronne to możliwość uzyskania ocen ucznia z określonego przedmiotu z określonego poziomu egzaminu. To uczyniłoby to wyszukiwanie trójstronne, ponieważ istnieją trzy zmienne (nazwisko studenta, nazwisko przedmiotu i poziom egzaminu).

Oto przykład wyszukiwania trójstronnego:

W powyższym przykładzie oprócz wybrania imienia studenta i nazwy przedmiotu możesz również wybrać poziom egzaminu. Na podstawie poziomu egzaminu zwraca pasującą wartość z jednej z trzech tabel.

Oto wzór użyty w komórce H4:

=INDEKS (($ B 3 USD: 7 USD E, 11 USD: 15 USD, 19 USD: 23 USD), DOPASUJ ($ G 4 USD, 3 USD A 7, 0 USD), DOPASUJ ($ H $3,$B$2:$E$2,0),JEŻELI($H$2="Test jednostkowy",1,JEŻELI($H$2="Średniookresowy",2,3)))

Rozłóżmy tę formułę, aby zrozumieć, jak to działa.

Ta formuła ma cztery argumenty. INDEKS to jedna z tych funkcji w programie Excel, która ma więcej niż jedną składnię.

=INDEKS (tablica, numer_wiersza, [numer_kolumny])
=INDEKS (tablica, numer_wiersza, [numer_kolumny], [numer_obszaru])

Jak dotąd we wszystkich powyższych przykładach używaliśmy pierwszej składni, ale aby przeprowadzić wyszukiwanie trójstronne, musimy użyć drugiej składni.

Zobaczmy teraz każdą część formuły opartą na drugiej składni.

• array - ($ B $ 3: $ E $ 7, $ B $ 11: $ E $ 15, $ B $ 19: $ E $ 23): Zamiast używać pojedynczej tablicy, w tym przypadku użyliśmy trzech tablic w nawiasach.
• row_num - MATCH ($ G $ 4, $ A $ 3: $ A 7, 0 $): Funkcja MATCH służy do znalezienia pozycji nazwiska ucznia w komórce $ G $ 4 na liście nazwisk studentów.
• col_num - MATCH ($ H $ 3, $ B $ 2: $ E $ 2, 0): Funkcja MATCH służy do znalezienia pozycji nazwy podmiotu w komórce $ H $ 3 na liście nazw podmiotu.
• [area_num] - IF($H$2=”Test jednostkowy”,1,IF($H$2=”Midterm”,2,3)): Wartość numeru obszaru mówi funkcji INDEX, którą tablicę wybrać. W tym przykładzie w pierwszym argumencie mamy trzy tablice. Jeśli wybierzesz Test jednostkowy z listy rozwijanej, funkcja JEŻELI zwróci 1, a funkcje INDEKS wybierają pierwszą tablicę z trzech tablic (czyli $ B $ 3: $ E $ 7).

Przykład 6 - Tworzenie odniesienia za pomocą funkcji INDEKS (dynamiczne nazwane zakresy)

Jest to jedno dzikie użycie funkcji INDEKS Excela.

Weźmy prosty przykład.

Mam listę nazwisk, jak pokazano poniżej:

Teraz mogę użyć prostej funkcji INDEX, aby uzyskać nazwisko na liście.

Oto wzór:

=INDEKS($A$2:$A$9,COUNTA($A$2:$A$9))

Ta funkcja po prostu zlicza liczbę komórek, które nie są puste i zwraca ostatni element z tej listy (działa tylko wtedy, gdy na liście nie ma pustych miejsc).

Teraz, co tu przychodzi magia.

Jeśli umieścisz formułę przed odwołaniem do komórki, formuła zwróci odwołanie do komórki o pasującej wartości (zamiast samej wartości).

=A2:INDEKS($A$2:$A$9;COUNTA($A$2:$A$9))

Można oczekiwać, że powyższa formuła zwróci =A2:”Josh” (gdzie Josh jest ostatnią wartością na liście). Jednak zwraca = A2: A9, a zatem otrzymujesz tablicę nazw, jak pokazano poniżej:

Jednym z praktycznych przykładów, w których ta technika może być pomocna, jest tworzenie dynamicznych nazwanych zakresów.

To wszystko w tym samouczku. Próbowałem omówić główne przykłady użycia funkcji INDEX Excela. Jeśli chcesz zobaczyć więcej przykładów dodanych do tej listy, daj mi znać w sekcji komentarzy.

Uwaga: starałem się jak najlepiej sprawdzić ten samouczek, ale jeśli znajdziesz jakieś błędy lub błędy ortograficzne, daj mi znać 🙂

Funkcja INDEKS Excela - samouczek wideo

• Funkcja WYSZUKAJ.PIONOWO Excela.
• Funkcja WYSZUKAJ.POZIOMO Excela.
• Excel ADR.Funkcja.
• Funkcja PODAJ.POZYCJĘ Excela.
• Funkcja przesunięcia Excela.

Możesz również polubić następujące samouczki programu Excel:

• WYSZUKAJ.PIONOWO vs. INDEKS/DOPASUJ
• Dopasowanie indeksu Excel
• Wyszukaj i zwróć wartości w całym wierszu/kolumnie.