Jak wyszukiwać wiele wartości w programie Excel: krok po kroku

Podczas pracy z dużymi zestawami danych w programie Microsoft Excel znalezienie wielu wartości spełniających kilka warunków może być trudne. Niektóre wbudowane funkcje, np. WYSZUKAJ.PIONOWO, zostały pierwotnie zaprojektowane do pracy z pojedynczymi wartościami.

Kilka funkcji programu Excel można łączyć w celu wyszukiwania wielu wartości. Obejmują one funkcje WYSZUKAJ.PIONOWO, INDEKS, PODAJ.POZYCJĘ i JEŻELI. Bieżące wersje programu Excel mogą używać tablic dynamicznych, podczas gdy starsze wersje używają formuł tablicowych.

Z tego artykułu dowiesz się dokładnie, jak używać tych funkcji w formułach, które znajdują w danych wiele wartości.

Chodźmy!

Spis treści

• Jak korzystać z WYSZUKAJ.PIONOWO z wieloma wartościami
  • Co to są formuły tablicowe?
  • Przykład WYSZUKAJ.PIONOWO
  • Najnowsze wersje Excela
  • Starsza wersja
  • Dokładne dopasowanie a przybliżone dopasowanie
  • Więcej o WYSZUKAJ.PIONOWO
• Jak używać funkcji INDEKS I PODAJ.POZYCJĘ do wyszukiwania wielu wartości
  • Jak używać funkcji INDEKS i PODAJ.POZYCJĘ razem w programie Excel 365
  • Praca ze starszymi wersjami programu Excel
• Jak korzystać z funkcji JEŻELI, aby znaleźć wiele wartości
  • Bieżące wersje programu Excel
  • Starsze wersje Excela
• Jak używać SUMPRODUCT dla wielu kryteriów
• 3 Typowe błędy z funkcjami wyszukiwania
  • 1. #N/D Błędy
  • 2. #REF! Błędy
  • 3. Błędy cykliczne
• Jak używać JEŻELI.BŁĄD z funkcjami wyszukiwania
• 7 zaawansowanych technik wyszukiwania
  • 1. Wyszukiwanie pozycji względnej
  • 2. MAŁA Funkcja
  • 3. Funkcja WIERSZ
  • 4. FILTR Funkcja
  • 5. Kolumny pomocnicze
  • 6. Tablice dynamiczne
  • 7. Power Query
• Końcowe przemyślenia

Jak korzystać z WYSZUKAJ.PIONOWO z wieloma wartościami

Funkcja WYSZUKAJ.PIONOWO jest często używana do znajdowania pojedynczych wartości w zakresie danych. Możesz jednak wyszukać wiele dopasowań w programie Excel za pomocą tej formuły wyszukiwania.

Domyślnie zwróci tylko pierwszą znalezioną pasującą wartość. Można jednak zmodyfikować funkcję, aby zwracała wiele wartości, używając formuły tablicowej.

Co to są formuły tablicowe?

Formuła tablicowa to formuła, która może wykonywać obliczenia na tablicach danych. Nazywa się to formułą tablicową, ponieważ może zwrócić tablicę wyników, a nie pojedynczą wartość.

Istnieje kilka prostych kroków podczas tworzenia formuły tablicowej:

1. Wybierz zakres komórek do przeszukania.

2. Wprowadź formułę na pasku formuły.

3. Naciśnij Ctrl + Shift + Enter, aby zakończyć.

Składnia formuły tablicowej jest podobna do składni zwykłej formuły, ale zawiera nawiasy klamrowe {} wokół formuły. Nawiasy klamrowe wskazują, że formuła jest formułą tablicową i że zwróci tablicę wartości.

Przykłady w tym artykule pokazują, jak poprawnie używać formuł tablicowych.

Przykład WYSZUKAJ.PIONOWO

Nasz przykład ma pięć pozycji w kolumnie A arkusza:

• Jabłko

• Banan

• Chleb

• Marchewka

• wiśnia

Zadanie polega na sprawdzeniu, czy na tej liście znajdują się trzy konkretne owoce: jabłko, banan i wiśnia.

Najnowsze wersje Excela

Składnia funkcji WYSZUKAJ.PIONOWO zależy od używanej wersji programu Excel.

Najnowsze wersje ( Excel 365 lub Excel 2021 ) obsługują tablice dynamiczne. Ta funkcja umożliwia formułom zwracanie wielu wyników, które „rozlewają się” do sąsiednich komórek.

Oto składnia (znak równości rozpoczyna formułę):

=WYSZUKAJ.PIONOWO(wyszukiwana_wartość; tablica_tabeli; numer_indeksu_kolumny; [przeszukiwany_zakres])

• lookup_value: Wartość, którą chcesz wyszukać.

• table_array: Cała tabela danych, którą chcesz przeszukać.

• col_index_num: Numer kolumny tabeli w tablicy_tablicy zawierającej dane, które chcesz zwrócić.

• range_lookup: opcjonalne. Określa, czy chcesz dopasować dokładne, czy przybliżone.

Nasz konkretny przykład wykorzystuje tę formułę:

= WYSZUKAJ.PIONOWO({„jabłko”, „banan”, „wiśnia”}; A1: A5;1; FAŁSZ)

Gdy formuła zostanie wprowadzona do komórki B1, wyniki zostaną przeniesione do komórek C1 i D1. To zdjęcie pokazuje przykład w akcji:

Starsza wersja

Jeśli używasz starszej wersji programu Excel, która nie obsługuje tablic dynamicznych (np. Excel 2019 lub starszy), musisz zastosować nieco inne podejście do formuły tablicowej.

Wykonaj następujące kroki:

1. Kliknij komórkę, w której chcesz wyświetlić wyniki dla pierwszego elementu (np. kolumna B).

2. Wpisz następującą formułę bez naciskania Enter: = WYSZUKAJ.PIONOWO({„jabłko”, „banan”, „wiśnia”}, A1: A5, 1, FAŁSZ)

3. Naciśnij Ctrl + Shift + Enter, aby przekształcić to w formułę tablicową.

4. Skopiuj komórkę B1 i wklej ją do komórki poniżej (lub użyj uchwytów wypełniania).

Gdy używasz Ctrl + Shift + Enter, Excel dodaje nawiasy klamrowe wokół formuły. Oznacza to, że jest to formuła tablicowa.

Dokładne dopasowanie a przybliżone dopasowanie

Domyślnie funkcja WYSZUKAJ.PIONOWO używa dopasowania przybliżonego. Oznacza to, że zwróci najbliższe dopasowanie, jakie może znaleźć, nawet jeśli wartości komórek nie są dokładnym dopasowaniem.

Jeśli chcesz wykonać dokładne dopasowanie, możesz ustawić argument range_lookup na FALSE.

Pamiętaj, że dopasowania przybliżone działają najlepiej w przypadku uporządkowanych wartości liczbowych. Zwykle nie jest to właściwe, gdy wartością komórki jest tekst.

Więcej o WYSZUKAJ.PIONOWO

Jeśli chcesz dowiedzieć się więcej o tej wszechstronnej funkcji, zapoznaj się z tymi artykułami:

• jak porównać dwie kolumny w Excelu za pomocą WYSZUKAJ.PIONOWO

• jak znaleźć rozbieżności za pomocą WYSZUKAJ.PIONOWO

Teraz, gdy masz już skonfigurowaną funkcję WYSZUKAJ.PIONOWO, przyjrzyjmy się dwóm innym funkcjom, które mogą robić to, co robi w inny sposób: INDEKS i PODAJ.POZYCJĘ.

Jak używać funkcji INDEKS I PODAJ.POZYCJĘ do wyszukiwania wielu wartości

Możesz połączyć funkcje INDEKS i PODAJ.POZYCJĘ, aby znaleźć wiele wartości w wielu wierszach.

Funkcja INDEKS w programie Excel zwraca wartość lub odwołanie do komórki w określonym zakresie.

=INDEKS(tablica; numer_wiersza; [numer_kolumny])

• tablica: Zakres komórek, w których ma zostać przeszukana wartość.

• numer_wiersza: Numer wiersza w tablicy, z którego ma zostać zwrócona wartość.

• numer_kolumny: (opcjonalnie) numer kolumny w tablicy, z której ma zostać zwrócona wartość. W przypadku pominięcia funkcja zwraca cały wiersz.

Funkcja PODAJ.POZYCJĘ w programie Excel zwraca pozycję wartości w określonym zakresie.

=POZYCJA.POZYCYJNA(przeszukiwana_wartość; szukana_tablica; [typ_dopasowania])

• szukana_wartość: wartość do wyszukania w szukanej_tablicy.

• szukana_tablica: zakres komórek, w których ma być przeszukiwana wartość szukanej_wartości.

• typ_dopasowania: (Opcjonalnie) Typ dopasowania do wykonania. W przypadku pominięcia funkcja przeprowadza dokładne dopasowanie.

Jak używać funkcji INDEKS i PODAJ.POZYCJĘ razem w programie Excel 365

Aby użyć razem funkcji INDEKS i PODAJ.POZYCJĘ do wyszukania wielu wartości w programie Excel, musisz użyć formuły tablicowej.

Działając z wcześniejszymi przykładowymi danymi, oto formuła w programie Excel 365:

=INDEKS(A1:A5; PODAJ.POZYCJĘ({„jabłko”, „banan”, „wiśnia”}; A1:A5; 0))

Powyższy przykład rozkłada się w następujący sposób:

• INDEKS: zwraca wartość komórki w określonym zakresie na podstawie podanego numeru wiersza i kolumny. W takim przypadku zwróci wartość z zakresu A1:A5.

• A1:A5: Jest to zdefiniowany zakres tabeli, w którym szukasz wartości i z którego zostanie zwrócony wynik.

• PODAJ.POZYCJĘ: wyszukuje określony element w zakresie komórek i zwraca względną pozycję tego elementu w zakresie.

• {„jabłko”, „banan”, „wiśnia”}: jest to stała tablicowa zawierająca wartości, które chcesz wyszukać.

• A1:A5: jest to zakres, w którym funkcja PODAJ.POZYCJĘ wyszuka wartości ze stałej tablicowej.

• 0: to jest typ dopasowania dla funkcji PODAJ.POZYCJĘ. W tym przypadku jest to 0, co oznacza, że ​​szukasz dopasowania ścisłego, a nie bliskiego.

 To zdjęcie pokazuje formułę w akcji:

Praca ze starszymi wersjami programu Excel

Jeśli używasz starszego pliku programu Excel, który nie obsługuje tablic dynamicznych (np. programu Excel 2019 lub starszego), musisz zastosować inne podejście.

Ponieważ starsze wersje nie obsługują formuł „rozlewających się” do sąsiednich komórek, konieczne będzie rozbicie użycia na trzy osobne formuły.

Wykonaj następujące kroki:

1. Kliknij komórkę, w której chcesz otrzymać wynik dla pierwszego elementu (np. komórka B1)

2. Wpisz poniższą formułę:

3. =INDEKS(A1:A5; PODAJ.POZYCJĘ(„jabłko”; A1:A5; 0))

4. Naciśnij klawisz Enter, aby wykonać formułę.

5. Wpisz następującą formułę w komórce B2: =INDEKS(A1:A5; PODAJ.POZYCJĘ(„banan”, A1:A5; 0))

6. Wpisz następującą formułę w komórce B3: =INDEKS(A1:A5; PODAJ.POZYCJĘ(„wiśnia”, A1:A5; 0))

Ten rysunek przedstawia odwołanie do komórki B3:

Funkcje INDEKS i PODAJ.POZYCJĘ nie są jedynymi, które umożliwiają znalezienie wielu wartości. W następnej sekcji przyjrzymy się, jak możesz użyć funkcji JEŻELI jako alternatywy.

Jak korzystać z funkcji JEŻELI, aby znaleźć wiele wartości

Innym sposobem wyszukiwania wartości w wielu komórkach na podstawie określonych kryteriów jest użycie funkcji JEŻELI z innymi funkcjami.

Funkcja JEŻELI umożliwia testowanie wielu warunków i zwracanie różnych wyników w zależności od wyniku tych testów.

Załóżmy na przykład, że masz tabelę danych sprzedaży z kolumnami Produkt i Sprzedaż. Chcesz wyszukać i zsumować kwotę sprzedaży dla dwóch z trzech produktów.

Bieżące wersje programu Excel

Aby znaleźć sumę kolumny Sprzedaż, w której produktem jest „Jabłko” lub „Banan”, korzystając z funkcji JEŻELI, można użyć formuły tablicowej z funkcjami JEŻELI, SUMA i LUB.

Zakładając, że Twoje dane zaczynają się w komórce A1, użyj następującej formuły:

=SUMA(JEŻELI((A2:A4=”Jabłko”)+(A2:A4=”Banan”), B2:B4, 0))

Sekcja (A2:A4=”Jabłko”)+(A2:A4=”Banan”) tworzy tablicę, która ma wartość 1, jeśli komórka w zakresie A2:A4 zawiera „Jabłko” lub „Banan”, a 0 w przeciwnym razie .

Instrukcja IF sprawdza każdy element argumentu tablica. Jeśli wartością jest 1 (tj. produkt to „Jabłko” lub „Banan”), przyjmuje odpowiednią wartość w kolumnie Sprzedaż (zakres B2:B4); w przeciwnym razie zajmuje 0.

Funkcja SUMA sumuje wartości z funkcji JEŻELI, skutecznie sumując wartości sprzedaży zarówno dla „jabłka”, jak i „banana”.

To zdjęcie przedstawia działanie formuły w zakresie wyszukiwania:

Starsze wersje Excela

W programie Excel 2019 lub starszym musisz użyć formuły tablicowej. Wykonaj następujące kroki:

1. Wpisz formułę, ale nie naciskaj Enter.

2. Naciśnij klawisze Ctrl + Shift + Enter, aby utworzyć formułę tablicową.

Program Excel doda nawiasy klamrowe {} wokół formuły, wskazując, że jest to formuła tablicowa.

Następnie przyjrzymy się, jak możesz użyć SUMPRODUCT do wyszukania kilku wartości na podstawie twoich kryteriów. Chodźmy!

Jak używać SUMPRODUCT dla wielu kryteriów

Funkcja SUMPRODUCT umożliwia również wyszukiwanie wielu wartości na podstawie wielu kryteriów.

Ponieważ nie wymaga użycia formuły tablicowej, składnia jest taka sama niezależnie od wersji programu Excel.

Wykorzystując te same dane, co w poprzednim przykładzie, formuła wygląda następująco:

=SUMA ILOCZYN((A2:A4=”Jabłko”)+(A2:A4=”Banan”), B2:B4)

Sekcja (A2:A4=”Jabłko”)+(A2:A4=”Banan”) tworzy tablicę, która ma wartość 1, jeśli komórka w zakresie A2:A4 zawiera „Jabłko” lub „Banan”, a 0 w przeciwnym razie .

Funkcja SUMPRODUCT mnoży elementy tablicy przez odpowiadające im elementy w kolumnie Sales (zakres B2:B4). Następnie sumuje otrzymane wartości, skutecznie sumując wartości sprzedaży zarówno dla „jabłka”, jak i „banana”.

Poniższy wzór pokazuje to w działaniu:

Funkcje programu Excel są niesamowite, gdy działają zgodnie z oczekiwaniami, ale czasami mogą wystąpić błędy. W następnej sekcji omówimy niektóre z typowych błędów i sposoby radzenia sobie z nimi.

3 Typowe błędy z funkcjami wyszukiwania

Funkcje wyszukiwania mogą czasami zwracać błędy, których rozwiązanie może być frustrujące i czasochłonne. Trzy najczęściej spotykane błędy to:

1. #Nie dotyczy Błędy

2. #REF! Błędy

3. Błędy cykliczne

1. #N/D Błędy

Błąd #N/D występuje, gdy szukanej wartości nie można znaleźć w tablicy odnośników.

Istnieje kilka powodów, dla których może wystąpić ten błąd, w tym:

• wyszukiwana wartość jest błędnie wpisana lub niepoprawna.

• tablica odnośników nie jest posortowana w porządku rosnącym.

• szukana wartość nie znajduje się w zbiorze danych.

Gdy wyszukiwana wartość nie znajduje się w zbiorze danych, jest to przydatna informacja. Jednak niedoświadczeni użytkownicy programu Excel mogą pomyśleć, że #N/D oznacza, że ​​coś poszło nie tak z formułą. Następna sekcja pokazuje, jak uczynić to bardziej przyjaznym dla użytkownika.

2. #REF! Błędy

#REF! błąd występuje, gdy tablica wyszukiwania lub tablica zwracana jest usuwana lub przenoszona.

Ten błąd można naprawić, aktualizując odwołania do komórek w funkcji wyszukiwania.

3. Błędy cykliczne

Podczas łączenia funkcji w złożonych formułach program Excel może powiedzieć, że masz odwołanie cykliczne.

Łatwiej będzie je zbadać, korzystając z naszego przewodnika po znajdowaniu odwołań cyklicznych w programie Excel .

Jak używać JEŻELI.BŁĄD z funkcjami wyszukiwania

Funkcja JEŻELI.BŁĄD jest użytecznym narzędziem do obsługi błędów w funkcjach wyszukiwania. Pozwala określić wartość lub formułę, która ma zostać zwrócona, jeśli funkcja wyszukiwania zwróci błąd.

Składnia funkcji JEŻELI.BŁĄD jest następująca:

=JEŻELI.BŁĄD(wartość; wartość_jeśli_błąd)

· wartość: wartość lub formuła, którą chcesz ocenić.

· Wartość_jeśli_błąd: wartość lub formuła do zwrócenia, jeśli pierwszy argument zwróci błąd.

Załóżmy na przykład, że masz funkcję WYSZUKAJ.PIONOWO, która wyszukuje wiele wartości w tabeli. Na poniższym obrazku jedna z wartości nie istnieje w wyszukiwanym zakresie danych.

Jak widać, wyświetlany jest błąd #N/A, co może być mylące dla niedoświadczonych użytkowników programu Excel.

Zamiast tego możesz użyć funkcji JEŻELI.BŁĄD, aby wyświetlić pustą komórkę lub komunikat „Nie znaleziono” z następującą składnią:

=JEŻELI.BŁĄD(WYSZUKAJ.PIONOWO(wartość_przeszukiwana;tablica_tablicy;indeks_kolumny;FAŁSZ); „Nie znaleziono”)

W tym przykładzie, jeśli funkcja WYSZUKAJ.PIONOWO zwróci błąd, funkcja JEŻELI.BŁĄD zwróci zamiast tego komunikat „Nie znaleziono”.

To zdjęcie pokazuje formułę w akcji. Kolumna B zawiera brakującą wartość, podczas gdy kolumny C i D zostały znalezione.

Do tej pory omówiliśmy wiele zagadnień i w końcu jesteś gotowy, aby poznać bardziej zaawansowane techniki wyszukiwania, co jest tematem następnej sekcji.

7 zaawansowanych technik wyszukiwania

Wyszukiwanie wielu wartości w programie Excel może być trudnym zadaniem, zwłaszcza w przypadku dużych zbiorów danych. W przypadku powolnego przetwarzania mogą wystąpić problemy z wydajnością.

Istnieje siedem zaawansowanych technik wyszukiwania, których można użyć, aby proces był łatwiejszy i bardziej wydajny.

• Wyszukiwanie pozycji względnej

• MAŁA funkcja

• Funkcja WIERSZ

• FILTR Funkcja

• Kolumny pomocnicze

• Tablice dynamiczne

• Power Query

1. Wyszukiwanie pozycji względnej

Jednym z najprostszych sposobów wyszukiwania wielu wartości w programie Excel jest użycie wyszukiwania pozycji względnej. Obejmuje to określenie przesunięć wierszy i kolumn z danej komórki w celu zlokalizowania żądanej wartości.

Na przykład, jeśli masz tabelę danych i chcesz wyszukać wartość znajdującą się dwa wiersze w dół i trzy kolumny na prawo od danej komórki, możesz użyć następującej formuły:

=ODSUNIĘCIE(komórka; 2; 3)

2. MAŁA Funkcja

Inną użyteczną techniką wyszukiwania wielu wartości jest użycie funkcji SMALL. Ta funkcja zwraca n-tą najmniejszą wartość w zakresie komórek.

Łącząc tę ​​funkcję z innymi funkcjami wyszukiwania, takimi jak INDEKS i PODAJ.POZYCJĘ, można wyszukiwać wiele wartości na podstawie określonych kryteriów.

Na przykład poniższa formuła wyszukuje drugą najmniejszą wartość w zakresie komórek, które spełniają określony warunek:

=INDEKS(dane; PODAJ.POZYCJĘ(MAŁE(JEŻELI(kryteria;zakres); 2);zakres;0))

3. Funkcja WIERSZ

Funkcja WIERSZ może być również używana do wyszukiwania wielu wartości w programie Excel. Ta funkcja zwraca numer wiersza danej komórki, którego można użyć do odwoływania się do komórek w tabeli danych.

Na przykład poniższa formuła wyszukuje wartość w tabeli na podstawie unikalnego identyfikatora:

=INDEKS(dane; PODAJ.POZYCJĘ(unikatowy_identyfikator; dane[unikalny_identyfikator]; 0); numer_indeksu_kolumny)

4. FILTR Funkcja

Funkcja FILTR nie jest dostępna w starszych wersjach programu Excel.

W programie Excel 365 można go użyć do filtrowania zakresu komórek na podstawie określonych kryteriów i zwracania tylko tych wartości, które spełniają te kryteria. To jest składnia i trzy argumenty:

=FILTRUJ(tablica; uwzględnij; [jeśli_puste])

• tablica: określone dane, które chcesz filtrować.

• include: Argument include to kryteria lub warunki, które chcesz zastosować do tablicy.

• [jeśli_puste] (opcjonalne): Wartość zwracana, jeśli żadne wiersze ani kolumny nie spełniają kryteriów określonych w argumencie dołączania.

Na przykład poniższa formuła działa na przykładowych danych, aby znaleźć dopasowania dla dwóch z trzech elementów w pierwszej kolumnie i zsumować odpowiadające im wartości w drugiej kolumnie.

=SUMA(FILTR(B2:B4; (A2:A4=”Jabłko”)+(A2:A4=”Banan”)))

Ten rysunek pokazuje, ile wierszy jest dopasowanych i sumę różnych wartości:

5. Kolumny pomocnicze

Możesz użyć kolumny pomocniczej, aby połączyć razem wiele pól w ramach funkcji takiej jak WYSZUKAJ.PIONOWO.

Załóżmy, że pracujesz z imionami i nazwiskami w osobnym arkuszu. Możesz połączyć je w kolumnie pomocniczej, do której odwołuje się formuła końcowa.

6. Tablice dynamiczne

Jak wiesz z wcześniejszych przykładów, użytkownicy platformy Microsoft 365 mogą korzystać z tablic dynamicznych do wyszukiwania wielu wartości w programie Excel.

Tablice dynamiczne umożliwiają zwracanie wielu wartości z jednej formuły, co ułatwia wyszukiwanie dużych ilości danych.

7. Power Query

Power Query to potężne narzędzie w programie Excel, którego można używać do zwracania wartości na podstawie wielu kryteriów.

Na przykład ten film znajduje niechlujne dane w arkuszu kalkulacyjnym i czyści je.

Końcowe przemyślenia

Na tym kończymy nasze szczegółowe zapoznanie się ze sztuką wyszukiwania wielu wartości w Excelu! Po opanowaniu funkcji WYSZUKAJ.PIONOWO, INDEKS, PODAJ.POZYCJĘ i formuł tablicowych będziesz przedzierać się przez złożone zestawy danych jak gorący nóż po maśle.

Kluczem jest zrozumienie składni i logiki każdej formuły. Pamiętaj, że te formuły mogą być złożone, dlatego ważne jest, aby poświęcić trochę czasu i dokładnie przetestować swoje formuły, zanim użyjesz ich do analizy ważnych danych.

Excel to potężne narzędzie, ale jest tak dobre, jak dobry jest użytkownik. Kontynuuj więc doskonalenie tych umiejętności, eksperymentuj, a wkrótce staniesz się mistrzem wyszukiwania wielowartościowego. Do następnego razu, przetwarzaj te liczby i spraw, aby program Excel wykonał za Ciebie ciężką pracę!