Jak utworzyć formułę wyszukiwania w programie Excel z wieloma kryteriami

Co wiedzieć

• Najpierw utwórz funkcję INDEKS, a następnie uruchom zagnieżdżoną funkcję PODAJ.POZYCJĘ, wprowadzając argument szukana_wartość.
• Następnie dodaj argument Lookup_array, a następnie argument Match_type, a następnie określ zakres kolumn.
• Następnie przekształć zagnieżdżoną funkcję w formułę tablicową, naciskając klawisze Ctrl + Shift + Enter. Na koniec dodaj wyszukiwane hasła do arkusza.

W tym artykule wyjaśniono, jak utworzyć formułę wyszukiwania korzystającą z wielu kryteriów w programie Excel w celu znalezienia informacji w bazie danych lub tabeli danych przy użyciu formuły tablicowej. Formuła tablicowa polega na zagnieżdżeniu funkcji PODAJ.POZYCJĘ w funkcji INDEKS. Informacje dotyczą programów Excel dla Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 i Excel dla komputerów Mac.

Postępuj zgodnie z samouczkiem

Aby wykonać czynności opisane w tym samouczku, wprowadź przykładowe dane w następujących komórkach, jak pokazano na poniższym obrazku. Wiersze 3 i 4 pozostają puste, aby pomieścić formułę tablicową utworzoną podczas tego samouczka. (Należy pamiętać, że ten samouczek nie obejmuje formatowania widocznego na obrazie).

• Wprowadź górny zakres danych do komórek od D1 do F2.
• Wprowadź drugi zakres do komórek od D5 do F11.

Utwórz funkcję INDEKS w programie Excel

Funkcja INDEKS jest jedną z niewielu funkcji programu Excel, która ma wiele formularzy. Funkcja ma formę tablicy i formę odniesienia. Array Form zwraca dane z bazy danych lub tabeli danych. Formularz referencyjny podaje odwołanie do komórki lub lokalizację danych w tabeli.

W tym samouczku do znalezienia nazwy dostawcy widżetów tytanu zamiast odwołania komórki do tego dostawcy w bazie danych używany jest formularz tablicy.

Wykonaj następujące kroki, aby utworzyć funkcję INDEKS:

1. Wybierz komórkę F3, aby uczynić ją komórką aktywną. W tej komórce zostanie wprowadzona funkcja zagnieżdżona.
2. Przejdź do Formuły.

   

3. Wybierz Wyszukiwanie i odniesienie, aby otworzyć listę rozwijaną funkcji.
4. Wybierz INDEKS, aby otworzyć okno dialogowe Wybierz argumenty.
5. Wybierz tablica,numer_wiersza,numer_kolumny.
6. Wybierz OK, aby otworzyć okno dialogowe Argumenty funkcji. W programie Excel dla komputerów Mac zostanie otwarty Konstruktor formuł.
7. Umieść kursor w polu tekstowym Tablica.
8. Zaznacz komórki od D6 do F11 w arkuszu, aby wprowadzić zakres w oknie dialogowym.
   Pozostaw otwarte okno dialogowe Argumenty funkcji. Formuła nie jest skończona. Uzupełnisz formułę zgodnie z poniższymi instrukcjami.
   

Uruchom zagnieżdżoną funkcję PODAJ.POZYCJĘ

Podczas zagnieżdżania jednej funkcji w innej nie można otworzyć konstruktora formuł drugiej lub zagnieżdżonej funkcji w celu wprowadzenia niezbędnych argumentów. Funkcja zagnieżdżona musi być wprowadzona jako jeden z argumentów pierwszej funkcji.

Podczas ręcznego wprowadzania funkcji argumenty funkcji są oddzielone od siebie przecinkiem.

Pierwszym krokiem do wprowadzenia zagnieżdżonej funkcji PODAJ.POZYCJĘ jest wprowadzenie argumentu szukana_wartość. Lookup_value to lokalizacja lub odwołanie do komórki dla wyszukiwanego terminu, który ma zostać dopasowany w bazie danych.

Lookup_value akceptuje tylko jedno kryterium wyszukiwania lub termin. Aby wyszukać wiele kryteriów, rozszerz wartość Lookup_value, łącząc lub łącząc dwa lub więcej odwołań do komórek za pomocą symbolu ampersand (&).

1. W oknie dialogowym Argumenty funkcji umieść kursor w polu tekstowym Numer_wiersza.
2. Wprowadź DOPASOWANIE (.
3. Wybierz komórkę D3, aby wprowadzić odwołanie do tej komórki w oknie dialogowym.
4. Wprowadź & (znak ampersand) po odwołaniu do komórki D3, aby dodać drugie odwołanie do komórki.
5. Wybierz komórkę E3, aby wprowadzić drugie odwołanie do komórki.
6. Wprowadź , (przecinek) po odwołaniu do komórki E3, aby uzupełnić wpis argumentu funkcji PODAJ.POZYCJĘ.

   
   W ostatnim kroku samouczka Lookup_values ​​zostaną wprowadzone do komórek D3 i E3 arkusza.

Uzupełnij zagnieżdżoną funkcję PODAJ.POZYCJĘ

Ten krok obejmuje dodanie argumentu Lookup_array dla zagnieżdżonej funkcji PODAJ.POZYCJĘ. Lookup_array to zakres komórek, które funkcja PODAJ.POZYCJĘ przeszukuje w celu znalezienia argumentu Lookup_value dodanego w poprzednim kroku samouczka.

Ponieważ w argumencie Lookup_array zidentyfikowano dwa pola wyszukiwania, to samo należy zrobić dla Lookup_array. Funkcja PODAJ.POZYCJĘ przeszukuje tylko jedną tablicę dla każdego określonego terminu. Aby wprowadzić wiele tablic, użyj znaku ampersand w celu połączenia tablic.

1. Umieść kursor na końcu danych w polu tekstowym Numer_wiersza. Kursor pojawi się po przecinku na końcu bieżącego wpisu.
2. Zaznacz komórki od D6 do D11 w arkuszu, aby wprowadzić zakres. Ten zakres jest pierwszą tablicą przeszukiwaną przez funkcję.
3. Wprowadź & (znak ampersand) po odwołaniu do komórki D6:D11. Ten symbol powoduje, że funkcja przeszukuje dwie tablice.
4. Zaznacz komórki od E6 do E11 w arkuszu, aby wprowadzić zakres. Ten zakres jest drugą tablicą przeszukiwaną przez funkcję.
5. Wprowadź , (przecinek) po odwołaniu do komórki E3, aby uzupełnić wpis argumentu funkcji PODAJ.POZYCJĘ.

   

6. Pozostaw okno dialogowe otwarte do następnego kroku samouczka.

Dodaj argument typu DOPASUJ

Trzecim i ostatnim argumentem funkcji PODAJ.POZYCJĘ jest argument typ_dopasowania. Ten argument mówi programowi Excel, jak dopasować Lookup_value do wartości w Lookup_array. Dostępne opcje to 1, 0 lub -1.

Ten argument jest opcjonalny. Jeśli zostanie pominięty, funkcja używa wartości domyślnej 1.

• Jeśli typ_dopasowania = 1 lub zostanie pominięty, funkcja PODAJ.POZYCJĘ znajduje największą wartość, która jest mniejsza lub równa wartości szukanej. Dane Lookup_array muszą być posortowane w porządku rosnącym.
• Jeśli typ_dopasowania = 0, funkcja PODAJ.POZYCJĘ znajduje pierwszą wartość równą wartości szukanej. Dane Lookup_array można sortować w dowolnej kolejności.
• Jeśli typ_dopasowania = -1, funkcja PODAJ.POZYCJĘ znajduje najmniejszą wartość, która jest większa lub równa wartości szukanej. Dane Lookup_array muszą być posortowane w kolejności malejącej.

Wprowadź te kroki po przecinku wprowadzonym w poprzednim kroku w wierszu Row_num w funkcji INDEKS:

1. Wprowadź 0 (zero) po przecinku w polu tekstowym Numer_wiersza. Ta liczba powoduje, że zagnieżdżona funkcja zwraca dokładne dopasowania do terminów wprowadzonych w komórkach D3 i E3.
2. Wprowadź ) (zamykający nawias okrągły), aby zakończyć funkcję PODAJ.POZYCJĘ.

   

3. Pozostaw okno dialogowe otwarte do następnego kroku samouczka.

Zakończ funkcję INDEKS

Funkcja PODAJ.POZYCJĘ została wykonana. Czas przejść do pola tekstowego Numer_kolumny w oknie dialogowym i wprowadzić ostatni argument funkcji INDEKS. Ten argument informuje program Excel, że numer kolumny należy do zakresu od D6 do F11. W tym zakresie znajduje się informacja zwrócona przez funkcję. W tym przypadku dostawca tytanowych widżetów.

1. Umieść kursor w polu tekstowym Numer_kolumny.
2. Wpisz 3 (liczbę trzy). Ta liczba nakazuje formule szukać danych w trzeciej kolumnie zakresu od D6 do F11.

   

3. Pozostaw okno dialogowe otwarte do następnego kroku samouczka.

Utwórz formułę tablicową

Przed zamknięciem okna dialogowego przekształć zagnieżdżoną funkcję w formułę tablicową. Ta tablica umożliwia funkcji wyszukiwanie wielu terminów w tabeli danych. W tym samouczku dopasowane są dwa terminy: Widżety z kolumny 1 i Tytan z kolumny 2.

Aby utworzyć formułę tablicową w programie Excel, naciśnij jednocześnie klawisze CTRL, SHIFT i ENTER. Po naciśnięciu funkcja jest otoczona nawiasami klamrowymi, wskazując, że funkcja jest teraz tablicą.

1. Wybierz OK, aby zamknąć okno dialogowe. W programie Excel dla komputerów Mac wybierz pozycję Gotowe.
2. Wybierz komórkę F3, aby wyświetlić formułę, a następnie umieść kursor na końcu formuły na pasku formuły.
3. Aby przekonwertować formułę na tablicę, naciśnij klawisze CTRL + SHIFT + ENTER.
4. W komórce F3 pojawia się błąd #N/ D. To jest komórka, w której wprowadzono funkcję.
5. W komórce F3 pojawia się błąd #N/D, ponieważ komórki D3 i E3 są puste. D3 i E3 to komórki, w których funkcja szuka szukanej wartości. Po dodaniu danych do tych dwóch komórek błąd zostaje zastąpiony informacją z bazy danych.

   

Dodaj kryteria wyszukiwania

Ostatnim krokiem jest dodanie wyszukiwanych terminów do arkusza. Ten krok pasuje do terminów Widżety z kolumny 1 i Tytan z kolumny 2.

Jeśli formuła znajdzie dopasowanie dla obu terminów w odpowiednich kolumnach w bazie danych, zwraca wartość z trzeciej kolumny.

1. Wybierz komórkę D3.
2. Wejdź do Widżetów.
3. Wybierz komórkę E3.
4. Wpisz Tytan i naciśnij klawisz Enter.
5. Nazwa dostawcy, Widgets Inc., pojawia się w komórce F3. Jest to jedyny wymieniony dostawca, który sprzedaje Titanium Widgets.
6. Wybierz komórkę F3. Funkcja pojawi się na pasku formuły nad arkuszem. W tym przykładzie jest tylko jeden dostawca widżetów tytanowych. Jeśli było więcej niż jeden dostawca, funkcja zwraca dostawcę wymienionego jako pierwszy w bazie danych.
   
{=INDEX(D6:F11,MATCH(D3&E3,D6:D11&E6:E11,0),3)}