Funkcja INDEX w programie Microsoft Excel
Jedną z najbardziej przydatnych funkcji programu Excel jest operator INDEX. Wyszukuje dane w zakresie na przecięciu określonego wiersza i kolumny, zwracając wynik do wstępnie wyznaczonej komórki. Ale pełne możliwości tej funkcji ujawniają się podczas używania jej w złożonych formułach w połączeniu z innymi operatorami. Rozważmy różne opcje jego zastosowania.
Spis treści
Korzystanie z funkcji INDEX
Operator INDEX odnosi się do grupy funkcji z kategorii "Referencje i tablice" . Ma dwie odmiany: dla tablic i dla referencji.
Wariant dla tablic ma następującą składnię:
=ИНДЕКС(массив;номер_строки;номер_столбца)
W takim przypadku można użyć dwóch ostatnich argumentów we wzorze, albo razem, albo dowolnego z nich, jeśli tablica jest jednowymiarowa. W przypadku zakresu wielowymiarowego należy zastosować obie wartości. Należy również wziąć pod uwagę, że pod numerem wiersza i kolumny nie ma to być liczba na współrzędnych arkusza, ale kolejność wewnątrz określonej tablicy.
Składnia dla wariantu odniesienia wygląda następująco:
=ИНДЕКС(ссылка;номер_строки;номер_столбца;[номер_области])
Możesz użyć tylko jednego argumentu z dwóch tutaj: "Numer linii" lub "Numer kolumny" . Argument "Numer obszaru" jest na ogół opcjonalny i ma zastosowanie tylko wtedy, gdy w operację zaangażowanych jest kilka zakresów.
W ten sposób operator szuka danych w określonym zakresie podczas określania wiersza lub kolumny. Ta funkcja jest bardzo podobna do operator , ale w przeciwieństwie do niej można wyszukiwać prawie wszędzie, a nie tylko w lewej kolumnie tabeli.
Metoda 1: użycie operatora INDEX dla tablic
Najpierw przeanalizujmy algorytm używania operatora INDEX dla tablic w najprostszym przykładzie.
Mamy tabelę wynagrodzeń. W pierwszej kolumnie wyświetlane są nazwiska pracowników, w drugim - data płatności, aw trzecim - kwota zarobków. Musimy wypisać nazwisko pracownika w trzeciej linii.
- Wybierz komórkę, w której będzie wyświetlany wynik przetwarzania. Klikamy ikonę "Wstaw funkcję" , która jest umieszczona natychmiast po lewej stronie linii formuły.
- Istnieje procedura aktywacji kreatora funkcji . W kategorii "Referencje i tablice" tego narzędzia lub "Lista pełnotekstowa" szukamy nazwy "INDEX" . Po znalezieniu tego operatora wybierz go i kliknij przycisk "OK" , który znajduje się w dolnej części okna.
- Otworzy się małe okno, w którym wybierzesz jeden z typów funkcji: "Array" lub "Link" . Potrzebujemy opcji "Array" . Znajduje się pierwszy i jest domyślnie podświetlony. Musimy więc kliknąć przycisk "OK" .
- Otwiera okno argumentów funkcji INDEX . Jak wspomniano powyżej, ma on trzy argumenty i odpowiednio trzy pola do wypełnienia.
W polu "Tablica" należy podać adres przetworzonego zakresu danych. Może być młotkowany ręcznie. Aby jednak uprościć to zadanie, postępujemy inaczej. Umieść kursor w odpowiednim polu, a następnie zakreśl cały zakres danych tabelarycznych na arkuszu. Następnie adres zakresu pojawi się natychmiast w polu.
W polu "Numer linii" wpisujemy numer "3" , ponieważ pod warunkiem musimy zdefiniować trzecią nazwę na liście. W polu "Numer kolumny" ustaw numer "1" , ponieważ kolumna z nazwami jest pierwszą w wybranym zakresie.
Po dokonaniu wszystkich ustawień kliknij przycisk "OK" .
- Wynik przetwarzania jest wyświetlany w komórce określonej w pierwszym akapicie tego podręcznika. Jest to nazwa wyprowadzona, która jest trzecią na liście w wybranym zakresie danych.
Rozmontowaliśmy użycie funkcji INDEX w wielowymiarowej tablicy (kilka kolumn i wierszy). Jeśli zakres byłby jednowymiarowy, wypełnienie danych w oknie argumentów byłoby jeszcze łatwiejsze. W polu "Tablica" tej samej metody co powyżej określamy jej adres. W takim przypadku zakres danych składa się tylko z wartości w jednej kolumnie "Nazwa" . W polu "Numer linii" określamy wartość "3" , ponieważ musimy znać dane z trzeciej linii. Pole "Numer kolumny" może pozostać puste, ponieważ mamy jednowymiarowy zakres, w którym używana jest tylko jedna kolumna. Kliknij przycisk "OK" .
Wynik będzie dokładnie taki sam jak powyżej.
To był najprostszy przykład, aby zobaczyć, jak działa ta funkcja, ale w praktyce ta wersja jej użycia jest nadal rzadko używana.
Lekcja: Kreator funkcji w programie Excel
Metoda 2: aplikacja w połączeniu z operatorem MATCH
W praktyce funkcja INDEX jest często używana w połączeniu z argumentem MATCH . Pakiet INDEX - MATCHING to potężne narzędzie pracy w Excelu, które jest bardziej elastyczne dzięki funkcjonalności niż jej najbliższy analog - operator VPR .
Główną funkcją funkcji MATCH jest określanie liczby w kolejności określonej wartości w wybranym zakresie.
Składnia instrukcji POSITION jest następująca:
=ПОИСКПОЗ(искомое_значение, просматриваемый_массив, [тип_сопоставления])
- Żądaną wartością jest wartość, której pozycja w zakresie, którego szukamy;
- Zeskanowana tablica jest zakresem, w którym znajduje się ta wartość;
- Typ odwzorowania to parametr opcjonalny, który określa, czy wyszukiwać wartości dokładnie, czy w przybliżeniu. Szukamy dokładnych wartości, więc ten argument nie jest używany.
Za pomocą tego narzędzia można zautomatyzować wprowadzanie argumentów "Numer linii" i "Numer kolumny" w funkcji INDEKS .
Zobaczmy, jak można to zrobić konkretnym przykładem. Pracujemy z tą samą tabelą, co powyżej. Osobno mamy dwa dodatkowe pola - "Nazwa" i "Suma". Konieczne jest, aby przy wprowadzeniu nazwy pracownika suma pieniędzy zarobionych przez niego była automatycznie wyświetlana. Zobaczmy, jak można to zaimplementować w praktyce, używając funkcji INDEKS i MATCH .
- Przede wszystkim dowiemy się, jakiego rodzaju wynagrodzenie otrzymuje pracownik Parfenov DF, a my wpisujemy jego nazwisko w odpowiednim polu.
- Wybierz komórkę w polu "Kwota" , w której zostanie wyświetlony wynik końcowy. Rozpoczynamy okno argumentów funkcji INDEX dla tablic.
W polu "Tablica" wpisujemy współrzędne kolumny, w której znajdują się sumy wynagrodzeń pracowników.
Pole "Numer kolumny" pozostaje puste, ponieważ jako przykład wykorzystujemy zakres jednowymiarowy.
Ale w polu "Numer linii" musimy tylko napisać funkcję MECZ . Dla jego zapisu stosujemy się do omówionej powyżej składni. Natychmiast w polu wpisujemy nazwę operatora "SZUKAJ" bez cudzysłowów. Następnie natychmiast otwórz nawias i podaj współrzędne żądanej wartości. Są to współrzędne komórki, w której oddzielnie zapisaliśmy nazwisko pracownika Parfenova. Wpisz średnik i podaj współrzędne zakresu, który przeglądasz. W naszym przypadku jest to adres kolumny z nazwiskami pracowników. Następnie zamykamy wspornik.
Po wprowadzeniu wszystkich wartości, kliknij przycisk "OK" .
- Wynik kwoty zarobków Parfenov DF po przetworzeniu jest wyświetlany w polu "Kwota".
- Teraz, jeśli w polu "Nazwa" zmieniamy treść z "Parfenov DF" , na przykład "Popova MD" , wtedy wartość płac w polu "Sum" zmieni się automatycznie.
Metoda 3: Przetwarzanie wielu tabel
Teraz zobaczmy, jak za pomocą operatora INDEX można przetworzyć kilka tabel. Do tych celów zostanie zastosowany dodatkowy argument "Numer obszaru" .
Mamy trzy tabele. Każda tabela pokazuje płace pracowników na jeden miesiąc. Naszym zadaniem jest znaleźć wynagrodzenie (trzecia kolumna) drugiego pracownika (druga linia) za trzeci miesiąc (trzeci obszar).
- Wybierz komórkę, w której wynik zostanie wyprowadzony i otwórz Kreatora funkcji w zwykły sposób, ale wybierając typ operatora, wybieramy widok referencyjny. Potrzebujemy tego, ponieważ ten typ obsługuje pracę z argumentem "Numer obszaru" .
- Otwiera okno argumentów. W polu "Link" musimy podać adresy wszystkich trzech zakresów. Aby to zrobić, umieść kursor w polu i wybierz pierwszy zakres, naciskając lewy przycisk myszy. Następnie wstawiamy średnik. Jest to bardzo ważne, ponieważ jeśli natychmiast przejdziesz do wyboru następnej tablicy, jej adres po prostu zastąpi współrzędne poprzedniej. Tak więc, po wprowadzeniu średnika, wybieramy następujący zakres. Następnie ponownie wstaw średnik i wybierz ostatnią tablicę. Wszystkie wyrażenia znajdujące się w polu "Odniesienie" są nawiasami.
W polu "Numer linii" wskazujemy numer "2" , ponieważ szukamy drugiego nazwiska na liście.
W polu "Numer kolumny" wskazujemy cyfrę "3" , ponieważ kolumna wynagrodzeń jest trzecią kolumną w każdej tabeli.
W polu "Numer obszaru" umieszczamy numer "3" , ponieważ musimy znaleźć dane w trzeciej tabeli, która zawiera informacje o zarobkach za trzeci miesiąc.
Po wprowadzeniu wszystkich danych kliknij przycisk "OK" .
- Następnie wyniki obliczeń są wyświetlane w poprzednio wybranej komórce. Pokazuje kwotę wynagrodzenia drugiego pracownika (Safronov VM) za trzeci miesiąc.
Metoda 4: Obliczanie kwoty
Formularz referencyjny nie jest często używany jako formularz tablicowy, ale może być używany nie tylko podczas pracy z kilkoma zakresami, ale także z innymi potrzebami. Na przykład można go użyć do obliczenia sumy w połączeniu z operatorem SUM .
Podczas dodawania sumy SUM ma następującą składnię:
=СУММ(адрес_массива)
W naszym konkretnym przypadku kwota zarobków wszystkich pracowników miesięcznie może być obliczona przy użyciu następującej formuły:
=СУММ(C4:C9)
Ale możesz ją nieco zmodyfikować za pomocą funkcji INDEX . Wtedy będzie wyglądać tak:
=СУММ(C4:ИНДЕКС(C4:C9;6))
W tym przypadku we współrzędnych początku tablicy rozpoczyna się komórka, z którą zaczyna. Ale we współrzędnych wskazujących koniec tablicy, używany jest operator INDEX . W tym przypadku pierwszy argument operatora INDEX wskazuje na zasięg, a drugi - na ostatni - na szósty.
Lekcja: Przydatne funkcje programu Excel
Jak widać, funkcja INDEX może być używana w Excelu do rozwiązywania bardzo różnych zadań. Chociaż rozważaliśmy daleko od wszystkich możliwych opcji jego zastosowania, ale tylko te najbardziej popularne. Istnieją dwa typy tej funkcji: referencyjna i dla tablic. Najskuteczniejszy może być stosowany w połączeniu z innymi operatorami. Formuły stworzone w ten sposób mogą rozwiązać najtrudniejsze problemy.