Jak utworzyć formułę wyszukiwania Excel z wieloma kryteriami

2024 Autor: Abigail Brown | [email protected]. Ostatnio zmodyfikowany: 2024-01-07 19:05

Co warto wiedzieć

• Najpierw utwórz funkcję INDEKS, a następnie uruchom zagnieżdżoną funkcję PODAJ. POZYCJĘ, wprowadzając argument Wyszukaj_wartość.
• Następnie dodaj argument Wyszukiwanie_tablicy, po którym następuje argument Typ_dopasowania, a następnie określ zakres kolumn.
• Następnie zamień zagnieżdżoną funkcję w formułę tablicową, naciskając Ctrl+ Shift+ Enter. Na koniec dodaj wyszukiwane terminy do arkusza roboczego.

W tym artykule wyjaśniono, jak utworzyć formułę wyszukiwania korzystającą z wielu kryteriów w programie Excel do wyszukiwania informacji w bazie danych lub tabeli danych przy użyciu formuły tablicowej. Formuła tablicowa obejmuje zagnieżdżenie 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.

Śledź samouczek

Aby wykonać kroki opisane w tym samouczku, wprowadź przykładowe dane do następujących komórek, jak pokazano na poniższym obrazku. Wiersze 3 i 4 są puste, aby pomieścić formułę tablicową utworzoną w tym samouczku. (Zauważ, że ten samouczek nie zawiera formatowania widocznego na obrazku.)

• 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ę referencyjną. Forma tablicy 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 tytanowych używany jest formularz tablicy, a nie odwołanie do komórki do tego dostawcy w bazie danych.

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

1. Wybierz komórkę F3 aby uczynić ją aktywną komórką. W tej komórce zostanie wprowadzona funkcja zagnieżdżona.

2. Przejdź do Formuły.

   

3. Wybierz Wyszukaj i odnieś, aby otworzyć listę rozwijaną funkcji.
4. Wybierz INDEX, 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 Array.

8. Podświetl komórki D6 do F11 w arkuszu, aby wprowadzić zakres do okna dialogowego.

   Pozostaw otwarte okno dialogowe Argumenty funkcji. Formuła nie jest skończona. Uzupełnij formułę w poniższych instrukcjach.

   

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

W przypadku zagnieżdżania jednej funkcji w drugiej nie można otworzyć drugiej lub zagnieżdżonej funkcji konstruktora formuł w celu wprowadzenia niezbędnych argumentów. Zagnieżdżoną funkcję należy wprowadzić 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 Wyszukaj_wartość. Szukana_wartość to lokalizacja lub odwołanie do komórki dla wyszukiwanego terminu, który ma być dopasowany w bazie danych.

Wartość 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 Row_num.
2. Wprowadź MATCH(.
3. Wybierz komórkę D3, aby wprowadzić odwołanie do komórki w oknie dialogowym.
4. Wprowadź & (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. Enter , (przecinek) po odwołaniu do komórki E3, aby zakończyć wprowadzanie argumentu funkcji PODAJ. POZYCJĘ.

   

   W ostatnim kroku samouczka wartości_szukane zostaną wprowadzone do komórek D3 i E3 arkusza roboczego.

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

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

Ponieważ dwa pola wyszukiwania zostały zidentyfikowane w argumencie Lookup_array, 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 &, aby połączyć tablice.

1. Umieść kursor na końcu danych w polu tekstowym Row_num. Kursor pojawia się po przecinku na końcu bieżącego wpisu.
2. Podświetl komórki D6 do D11 w arkuszu, aby wprowadzić zakres. Ten zakres jest pierwszą tablicą przeszukiwaną przez funkcję.
3. Wprowadź & (ampersand) po odwołaniu do komórki D6:D11. Ten symbol powoduje, że funkcja przeszukuje dwie tablice.
4. Podświetl komórki 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 zakończyć wprowadzanie argumentu funkcji PODAJ. POZYCJĘ.

   

6. Pozostaw otwarte okno dialogowe do następnego kroku w samouczku.

Dodaj argument typu dopasowania

Trzecim i ostatnim argumentem funkcji PODAJ. POZYCJĘ jest argument Typ_dopasowania. Ten argument informuje program 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 domyślnej wartości 1.

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

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

1. Wprowadź 0 (zero) po przecinku w polu tekstowym Row_num. Ta liczba powoduje, że funkcja zagnieżdżona zwraca dokładne dopasowania do terminów wprowadzonych w komórkach D3 i E3.

2. Wprowadź ) (nawias zamykający), aby zakończyć funkcję PODAJ. POZYCJĘ.

   

3. Pozostaw otwarte okno dialogowe do następnego kroku w samouczku.

Zakończ funkcję INDEKS

Funkcja DOPASUJ została zakończona. Czas przejść do pola tekstowego Numer_kolumny okna dialogowego 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 informacje zwrócone przez funkcję. W tym przypadku dostawca widżetów tytanowych.

1. Umieść kursor w polu tekstowym Column_num.

2. Wprowadź 3 (cyfra trzy). Ta liczba informuje formułę, że ma szukać danych w trzeciej kolumnie z zakresu od D6 do F11.

   

3. Pozostaw otwarte okno dialogowe do następnego kroku w samouczku.

Utwórz wzór tablicy

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 dopasowywane są dwa terminy: Widgety z kolumny 1 i Tytan z kolumny 2.

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

1. Wybierz OK, aby zamknąć okno dialogowe. W programie Excel dla komputerów Mac wybierz Gotowe.
2. Wybierz komórkę F3, aby wyświetlić formułę, a następnie umieść kursor na końcu formuły na pasku formuł.
3. Aby przekonwertować formułę na tablicę, naciśnij CTRL+ SHIFT+ ENTER.
4. A W komórce F3 pojawia się błąd N/D. To jest komórka, w której wprowadzono funkcję.

5. Błąd N/D występuje w komórce F3, ponieważ komórki D3 i E3 są puste. D3 i E3 to komórki, w których funkcja szuka wartości Lookup_value. Po dodaniu danych do tych dwóch komórek, błąd jest zastępowany informacjami z bazy danych.

   

Dodaj kryteria wyszukiwania

Ostatnim krokiem jest dodanie wyszukiwanych terminów do arkusza. Ten krok pasuje do terminów Widgety z kolumny 1 i Titanium z kolumny 2.

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

1. Wybierz komórkę D3.
2. Wprowadź Widżety.
3. Wybierz komórkę E3.
4. Wpisz Titanium i naciśnij Enter.
5. Nazwa dostawcy, Widgets Inc., pojawia się w komórce F3. Jest to jedyny wymieniony dostawca, który sprzedaje widżety tytanowe.

6. Wybierz komórkę F3. Funkcja pojawia się na pasku formuły nad arkuszem.

   {=INDEKS(D6:F11, PODAJ. POZYCJĘ(D3&E3, D6:D11&E6:E11, 0), 3)}
   W tym przykładzie istnieje tylko jeden dostawca widżetów tytanowych. Jeśli był więcej niż jeden dostawca, dostawca wymieniony jako pierwszy w bazie danych jest zwracany przez funkcję.