W tym artykule dowiemy się, jak wyszukać wiele wystąpień wartości w programie Excel.
Wyszukać wartości za pomocą opcji rozwijanej?
W tym miejscu rozumiemy, w jaki sposób możemy wyszukać różne wyniki za pomocą formuły tablicowej funkcji INDEKS. Wystarczy wybrać wartość z listy, a odpowiedni wynik będzie tam.
Formuła ogólna
| {=INDEKS(tablica,MAŁY(JEŻELI(wartość_wyszukiwana=zakres_wyszukiwanych_wartości;WIERSZ(zakres_wyszukiwanych_wartości)-WIERSZ(pierwsza komórka z zakresu szukanej_wartości)+1);WIERSZ(1:1)))} |
Szyk: Zakres, z którego chcesz pobrać dane.
szukana_wartość: Twoja wartość lookup_value, którą chcesz filtrować.
search_value_range: Zakres, w którym chcesz filtrować szukaną_wartość.
Pierwsza komórka w zakresie szukana_wartość: jeśli zakres wyszukiwania_wartości wynosi $A$5:$A$100, to jest to $A$5.
Ważny: Wszystko powinno być odwołanie bezwzględne. szukana_wartość może być względny zgodnie z wymaganiami.
Wprowadź go jako formułę tablicową. Po napisaniu formuły użyj klawiszy CTRL+SHIFT+ENTER, aby przekształcić ją w formułę tablicową.
Przykład wyszukiwania wielu wyników
Mam dane ucznia w zasięgu A2:E14. W komórce G1 mam listę rozwijaną wartości regionu, np. Środkowy, Wschód, Północ, Południe i Zachód. Teraz chcę, niezależnie od tego, który region mam w G1, lista wszystkich uczniów z tego regionu powinna być wyświetlana w kolumnie H.
Aby wyszukać wiele wartości w programie Excel, zidentyfikujmy nasze zmienne.
Szyk: $C$2:$C$14
szukana_wartość : 1 GBP
search_value_range : $A$2:$A$14
Pierwsza komórka zakresu szukanej_wartości : 2$ A$
Zgodnie z powyższymi danymi, nasz wzór na pobieranie wielu wartości w programie Excel będzie wyglądał następująco:
| {=JEŻELIBŁĄD(INDEKS($C$2:$C$14,MAŁY(JEŻELI($G$1=$A$2:$A$14;WIERSZ($A$2:$A$14))-WIERSZ($A$2)+1) ,ROW(1:1)),"Brak więcej wartości")} |
To jest formuła tablicowa. Nie używaj tylko Enter po wpisaniu formuły. Użyj razem CTRL + SHIFT + ENTER.
Teraz zrozummy, JAK TO DZIAŁA.
Formuła może wydawać się skomplikowana, ale pomysł jest prosty. Musimy uzyskać numer indeksu każdego wystąpienia wartości, a następnie pobrać wartości za pomocą funkcji INDEX programu Excel.
Stąd głównym wyzwaniem jest uzyskanie tablicy numerów indeksów szukana_wartość. Aby uzyskać numery indeksu, użyliśmy funkcji JEŻELI i WIERSZ. Formuła jest rzeczywiście całkowicie złożona, podzielmy ją.
Chcemy pobrać wartości z kolumny student, więc nasza tablica dla funkcji INDEX to 2 CAD: 14 CAD.
Teraz musimy podać numery wierszy z $A$2:$A$14 (wartość wyszukiwania), w której istnieje wartość G1 (na razie powiedzmy G1 ma w sobie centralne miejsce).
JEŻELI ($G$1=$A$2:$A$14,WIERSZ($A$2:$A$14): Teraz ta część zwraca numer wiersza, jeśli wartość licznika komórki wynosi G1 (centralny) w zakresie $A$2:$A$14 inaczej wraca FAŁSZYWE. W tym przykładzie zwróci
{2;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;7;8;FAŁSZ;FAŁSZ;11;FAŁSZ;FAŁSZ;FAŁSZ}.
Teraz, ponieważ powyższa tablica zawiera numery wierszy z pierwszego wiersza (1:1) i potrzebujemy wierszy zaczynających się od naszej tablicy (A2:A14). Aby to zrobić, używamy -ROW($A$2)+1 w formule JEŻELI. Zwróci to liczbę wierszy przed rozpoczęciem naszej tablicy.
W tym przykładzie jest to -1. Jeśli zaczynał od A3, zwróciłby -2 i tak dalej. Ta liczba zostanie odjęta od każdej liczby w tablicy zwróconej przez IF. Więc wreszcie JEŻELI($G$1=$A$2:$A$14,WIERSZ($A$2:$A$14)-WIERSZ($A$2)+1) to przełoży się na {1;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;6;7;FAŁSZ;FAŁSZ;10;FAŁSZ;FAŁSZ;FAŁSZ}.
Następnie ta tablica jest otoczona funkcją SMALL. Ta funkcja zwraca N-tą najmniejszą wartość w podanej tablicy. Teraz mamy MAŁY({2;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;7;8;FAŁSZ;FAŁSZ;11;FAŁSZ;FAŁSZ;FAŁSZ},WIERSZ(1:1). ROW(1:1) zwróci 1. Dlatego powyższa funkcja zwróci pierwszą najmniejszą wartość w tablicy, czyli 2.
Po skopiowaniu tej formuły do poniższych komórek ROW(1:1) zmieni się w ROW(2:2) i zwróci drugą najmniejszą wartość w tablicy, czyli 7 i tak dalej. Dzięki temu funkcja najpierw zwraca pierwszą znalezioną wartość. Ale jeśli chcesz najpierw uzyskać ostatnią znalezioną wartość, użyj funkcji LARGE zamiast funkcji SMALL.
Teraz używając wartości zwróconych powyżej funkcji, funkcja INDEKS łatwo zwraca każdą pasującą wartość z zakresu.
Możesz także wykonać wyszukiwanie dokładnych dopasowań za pomocą funkcji INDEKS i PODAJ.POZYCJĘ w programie Excel. Dowiedz się więcej o tym, jak wykonywać wyszukiwanie z uwzględnieniem wielkości liter za pomocą funkcji INDEKS I PODAJ.POZYCJĘ w programie Excel. Możesz także wyszukać częściowe dopasowania za pomocą symboli wieloznacznych w programie Excel.
Mam nadzieję, że ten artykuł na temat wyszukiwania wielu wystąpień wartości w programie Excel jest objaśniający. Więcej artykułów na temat obliczania wartości i powiązanych formuł programu Excel znajdziesz tutaj. Jeśli podobały Ci się nasze blogi, podziel się nimi ze znajomymi na Facebooku. A także możesz śledzić nas na Twitterze i Facebooku. Chcielibyśmy usłyszeć od Ciebie, daj nam znać, jak możemy ulepszyć, uzupełnić lub unowocześnić naszą pracę i uczynić ją lepszą dla Ciebie. Napisz do nas na e-mail.
Użyj funkcji WYSZUKAJ.PIONOWO z co najmniej dwóch tabel przeglądowych | Aby wyszukać z wielu tabel, możemy zastosować podejście IFERROR. Aby wyszukać z wielu tabel, przyjmuje błąd jako przełącznik do następnej tabeli. Inną metodą może być podejście Jeśli.
Jak wykonać wyszukiwanie z uwzględnieniem wielkości liter w programie Excel? | funkcja WYSZUKAJ.PIONOWO programu Excel nie rozróżnia wielkości liter i zwróci pierwszą dopasowaną wartość z listy. INDEX-MATCH nie jest wyjątkiem, ale można go zmodyfikować, aby rozróżniać wielkość liter. Zobaczmy, jak…
Wyszukaj często pojawiający się tekst z kryteriami w programie Excel | Wyszukiwanie najczęściej pojawia się w tekście w zakresie, w którym używamy funkcji INDEX-MATCH z funkcją MODE. Oto metoda.
Popularne artykuły :
Jak korzystać z funkcji IF w programie Excel? : Instrukcja IF w programie Excel sprawdza warunek i zwraca określoną wartość, jeśli warunek jest PRAWDA, lub zwraca inną określoną wartość, jeśli jest FAŁSZ.
Jak korzystać z funkcji WYSZUKAJ.PIONOWO w programie Excel : Jest to jedna z najczęściej używanych i popularnych funkcji programu Excel, która służy do wyszukiwania wartości z różnych zakresów i arkuszy.
Jak korzystać z funkcji SUMA.JEŻELI w programie Excel : To kolejna ważna funkcja deski rozdzielczej. Pomaga to zsumować wartości w określonych warunkach.
Jak korzystać z funkcji LICZ.JEŻELI w programie Excel : Policz wartości z warunkami za pomocą tej niesamowitej funkcji. Nie musisz filtrować danych, aby zliczyć określone wartości. Funkcja Countif jest niezbędna do przygotowania deski rozdzielczej.