Użyj INDEX i MATCH dla prostych zapytań do bazy danych w Excelu

Pierwotnie program Excel nie został zaprojektowany jako prawdziwa baza danych. Jego wczesne funkcje bazy danych były ograniczone pod względem ilości i jakości. A ponieważ każdy rekord w bazie danych Excela jest widoczny na ekranie jednocześnie - co oznacza wszystko w pamięci naraz - bazy danych Excela musiały być naprawdę małe: wiele pól z niewielką liczbą rekordów lub kilka pól z dużą ilością rekordów; i minimalne obliczenia.

VLOOKUP (pionowe) i HLOOKUP (poziome) były jedynymi funkcjami dostępnymi do zapytania bazy danych o określone informacje. Na przykład można wyszukiwać i wyodrębniać wszystkie rekordy, które zawierały sprzedaż, o wartości 1000 USD, ale mniejszej niż 5000 USD, ale tylko w przypadku plików płaskich (tylko jedna macierz bazy danych).

Tabele przestawne zostały opracowane, aby użytkownicy mogli tworzyć relacyjne bazy danych, które są łatwiej wyszukiwać, zużywać mniej pamięci i zapewniać dokładniejsze wyniki. Jeśli jednak nie masz lub nie potrzebujesz relacyjnej bazy danych, ale potrzebujesz bardziej wydajnych i niezawodnych funkcji bazy danych, spróbuj na początek.

[Czytaj dalej: Twój nowy komputer potrzebuje tych 15 bezpłatnych, doskonałych programów]

Dopasowanie indeksu, dopasowania i indeksu

W Excelu funkcja INDEX zwraca element z określonej pozycji (na liście, tabeli, bazie danych).

Funkcja MATCH zwraca pozycję wartości (na liście , tabela, baza danych). A użyte razem funkcje INDEX-MATCH sprawiają, że wydobywanie danych z tabeli jest proste.

Składnia funkcji INDEX to: INDEX (tablica, nr_wiersza, [numer kolumny]). Tablica to zakres komórek, z którymi pracujesz. Argument liczba_wierszowa to oczywiście numer wiersza w zakresie zawierającym dane, których szukasz. Column_num to numer kolumny w zakresie zawierającym dane, których szukasz. Formuła INDEX nie rozpoznaje liter kolumn, więc musisz używać liczb (licząc od lewej).

Składnia dla funkcji MATCH to: MATCH (lookup_value, lookup_array, [match_type]). Wartość szukana to liczba lub tekst, którego szukasz, która może być wartością, wartością logiczną lub odwołaniem do komórki. Pole lookup_array to zasięg komórek, z którymi pracujesz. Parametr match_type określa funkcję MATCH - czyli dopasowanie ścisłe lub najbliższy.

A. Funkcja INDEX

W naszym przykładzie słynny Commodore James Norrington ma arkusz kalkulacyjny, który śledzi wszystkie pirackie statki na Karaibach. Lista Norringtona jest uporządkowana przez formacje bojowe statków, które pasują do jego morskich map tego obszaru. Kiedy widzi, że statek się rozwija, wprowadza formułę indeksu do swojego arkusza kalkulacyjnego, aby mógł zidentyfikować statek i jego zdolności. W tym pierwszym pytaniu Norrington chce poznać typ statku, który posuwa się naprzód.

1. Wybierz lokalizację (komórkę lub zakres komórek) dla swoich zapytań (tj. Funkcje i wyniki), a następnie przenieś kursor do tej komórki. Na przykład: dowolna komórka w wierszu 18.

2. Wprowadź funkcję INDEKS (poprzedzona znakiem równości) plus nawias otwierający, a następnie wyróżnij (lub wpisz) zakres bazy danych / tabeli w następujący sposób: = INDEKS (A2: I16

Uwaga: Jeśli chcesz bezwzględne odniesienie (które w tym przypadku oznacza zakodowanie formuły tak, że gdy / jeśli jest kopiowana, zakres nie jest zmieniany), naciśnij F4 raz po każdym odwołaniu do komórki Możesz również podświetlić zakres: Wystarczy nacisnąć F4 raz po wybraniu pełnego zakresu , a kompletne bezwzględne symbole referencyjne są dodawane.

3. Następnie wprowadź przecinek, aby oddzielić argumenty (czyli oddzielne formuły formuł), następnie wprowadź numer wiersza i przecinek, a następnie numer kolumny (tak , musi to być liczba, a nie zwykła kolumna) i prawy nawias (lub po prostu naciśnij Enter i pozwól Excelowi dodać nawias końcowy) Pełna formuła wygląda następująco: = INDEX ($ A $ 2: $ I $ 16 , 15,2).

Uwaga: Numeracja wierszy zaczyna się od pierwszego numeru w zakresie, a nie od pierwszego numeru w arkuszu kalkulacyjnym. n choć statek piratów Cavallerii znajduje się w wierszu 16 programu Excel, w naszym wzorze jest to wiersz 15, ponieważ nasz zakres zaczyna się na A2 i przechodzi przez I16. Jeśli A2 jest wierszem 1, to A16 to wiersz 15).

4. Zauważ, że typ statku, którego szukał Norrington, to War Sloop.

JD Sartain

Użyj funkcji indeksu do zlokalizowania określonych informacji w twojej bazie danych.

B. Zakresy INDEX

Teraz możemy zrobić o wiele więcej dzięki tej tabeli bazy danych. Nie musisz na nowo definiować zasięgu za każdym razem, gdy chcesz coś wiedzieć. Aby to ułatwić, zdefiniujemy zakres raz, a następnie nadamy mu nazwę. Następnie możemy po prostu wpisać nazwę zakresu w naszych formułach.

1. Przejdź do A2 i zaznacz zakres od A2 do I16.

2. Na karcie Formuły wybierz pozycję Zdefiniuj nazwę z grupy Zdefiniowane nazwy.

3. W wyskakującym oknie dialogowym wprowadź nazwę zakresu w polu Nazwa pola

4. Następnie wprowadź zakres (w którym znajduje się zakres), który jest albo skoroszytem, ​​albo jednym z arkuszy w skoroszycie.

5. Wprowadź komentarz, jeśli to konieczne.

6. Na koniec sprawdź, czy w polu Odnośniki do wyświetlana jest poprawna nazwa i zakres, a następnie kliknij przycisk OK .

7. Jeśli chcesz sprawdzić, czy Twój zakres jest rzeczywiście zapisany w Excelu, wypróbuj ten mały test: naciśnij Ctrl + G (polecenie GoTo). Wybierz Ships w oknie dialogowym GoTo, a następnie kliknij OK , a Excel ponownie podświetli zakres A2: I16.

Jak zdefiniować i zapisać zakres

C. INDEKS z formułami SUMA i ŚREDNIA

Norrington ocenia zdolności bojowe floty. Najpierw chce wiedzieć, ile armat ma pirat, średnią liczbę dział na statek i całkowitą liczbę załóg obsługujących wszystkie pirackie statki. Wprowadza następujące formuły:

1. = SUMA (INDEKS (Statki, 8)) wynosi 334, całkowitą liczbę dział i

2. = ŚREDNIA (INDEKS (Statki, 8)) wynosi 22,27 lub około 22,27 armonów na statek.

3. = SUMA (INDEKS (Statki, 7)) wynosi 2350, całkowita liczba wszystkich załóg na wszystkich statkach.

Dlaczego jest tam przecinek, spacja, przecinek między Statkami a liczbą 8 i co oznaczają te liczby? Ships to Range (po którym następuje przecinek), argument Row jest pusty (lub spacja), ponieważ Norrington chce wszystkich wierszy, a 8 oznacza 8 kolumnę (która jest kolumną H, Cannons).

Niektórzy mogą zapytać , dlaczego nie wystarczy wpisać SUMA i / lub AVERAGE formuły na dole tych kolumn? W tym małym arkuszu kalkulacyjnym tak, byłoby to równie łatwe. Jeśli jednak arkusz kalkulacyjny ma 5000 wierszy i 300 kolumn, należy użyć INDEKSU

JD Sartain

03 formuł INDEKSU używając SUMA i ŚREDNIA.

Po nazwie zakresu, Norrington może otworzyć pusty arkusz kalkulacyjny w ten sam skoroszyt i zapisuj jego zapytania (formuły) w kolumnie B (które pokazują wyniki zamiast formuł) z opisem, który definiuje te zapytania w kolumnie A. (Uwaga: Kolumna C pokazuje rzeczywiste formuły znajdujące się w kolumnie B).

Nie musi widzieć swojej ogromnej bazy 5000 rekordów ani czekać przez kilka sekund, podczas gdy formuły obliczają. Może uzyskać wszystkie potrzebne informacje z arkusza zapytań. Pamiętaj, że im większy arkusz kalkulacyjny, tym wolniej działa, zwłaszcza jeśli istnieje wiele formuł.

JD Sartain

04 Arkusz informacji o piracie statku powietrznego komandora Jamesa Norringtona.

D. INDEX MECZ z MAX

Teraz, Norrington chce wiedzieć, ilu piratów znajduje się na najbardziej zaludnionym statku, a który to statek? Używa INDEKSU z formułą MAX, aby zdobyć jak największą liczbę piratów, ale musi również wiedzieć, który statek je niesie. Dlatego używa formuły INDEX / MATCH z MAX, aby dowiedzieć się, który statek ma najwięcej piratów na pokładzie.

1. = MAX (INDEX (Statki, 7)) to 300, największa liczba piratów na jednym ze statków

2. = INDEX ($ A 2 $: 16 $, MATCH (MAX (statki), $ G 2: G $ 16, 0)) jest równy Royal James, statkowi o największej liczbie piratów na pokładzie

3. = INDEKS (F $ 2: $ F 16 $, MECZ (MAX (statki), G $ 2: G 16 USD, 0)) równa się Stede Bonnet, Kapitan Royal James z piracką załogą 300

JD Sartain

Użyj INDEX-MATCH i MAX, aby pobrać określone informacje z bazy danych.