Przejdź do głównej zawartości

Wyszukuj i zwracaj wiele wartości na podstawie jednego lub wielu kryteriów

Zwykle możesz użyć funkcji Vlookup, aby uzyskać pierwszą odpowiednią wartość, ale czasami chcesz zwrócić wszystkie pasujące rekordy na podstawie określonego kryterium. W tym artykule omówię, jak przeglądać i zwracać wszystkie pasujące wartości pionowo, poziomo lub do jednej komórki.

Wyszukaj i zwróć wszystkie odpowiadające wartości w pionie

Wyszukaj i zwróć wszystkie odpowiadające wartości w poziomie

Wyszukaj i zwróć wszystkie odpowiednie wartości do jednej komórki

Wyszukaj i zwróć wszystkie odpowiadające wartości w pionie

Aby zwrócić wszystkie pasujące wartości w pionie na podstawie określonego kryterium, zastosuj następującą formułę tablicową:

1. Wprowadź lub skopiuj tę formułę do pustej komórki, w której chcesz wyprowadzić wynik:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF($E$2=$A$2:$A$20, ROW($A$2:$A$20)-ROW($A$2)+1), ROW(1:1))),"" )

Note: W powyższym wzorze, C2:C20 czy kolumna zawiera pasujący rekord, który chcesz zwrócić; A2: A20 czy kolumna zawiera kryterium; i E2 jest konkretnym kryterium, na podstawie którego chcesz zwrócić wartości. Zmień je według swoich potrzeb.

2. Następnie naciśnij Ctrl + Shift + Enter klucze razem, aby uzyskać pierwszą wartość, a następnie przeciągnij uchwyt wypełniania w dół, aby uzyskać wszystkie potrzebne rekordy, patrz zrzut ekranu:

Porady:

Aby wyświetlić pionowo i zwrócić wszystkie pasujące wartości na podstawie bardziej szczegółowych wartości w pionie, zastosuj poniższą formułę i naciśnij Ctrl + Shift + Enter klawiatura.

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($E$2=$A$2:$A$20))*(--($F$2=$B$2:$B$20))), ROW($A$2:$A$20)-ROW($A$2)+1), ROW(1:1))),"" )

Wyszukaj i zwróć wszystkie odpowiadające wartości w poziomie

Jeśli chcesz, aby pasujące wartości były wyświetlane w kolejności poziomej, pomocna może być poniższa formuła tablicowa.

1. Wprowadź lub skopiuj tę formułę do pustej komórki, w której chcesz wyprowadzić wynik:

=IFERROR(INDEX($C$2:$C$20,SMALL(IF($F$1=$A$2:$A$20,ROW($A$2:$A$20)-ROW($A$2)+1),COLUMN(A1))),"")

Note: W powyższym wzorze, C2: C20 czy kolumna zawiera pasujący rekord, który chcesz zwrócić; A2: A20 czy kolumna zawiera kryterium; i F1 jest konkretnym kryterium, na podstawie którego chcesz zwrócić wartości. Zmień je według swoich potrzeb.

2. Następnie naciśnij Ctrl + Shift + Enter klucze razem, aby uzyskać pierwszą wartość, a następnie przeciągnij uchwyt wypełniania w prawo, aby uzyskać wszystkie potrzebne rekordy, patrz zrzut ekranu:

Porady:

Aby Vlookup i zwrócić wszystkie pasujące wartości na podstawie bardziej szczegółowych wartości w poziomie, zastosuj poniższą formułę i naciśnij Ctrl + Shift + Enter klawiatura.

=IFERROR(INDEX($C$2:$C$20,SMALL(IF(1=((--($F$1=$A$2:$A$20))*(--($F$2=$B$2:$B$20))),ROW($A$2:$A$20)-ROW($A$2)+1),COLUMN(A1))),"")

Wyszukaj i zwróć wszystkie odpowiednie wartości do jednej komórki

Aby pominąć i zwrócić wszystkie odpowiednie wartości do pojedynczej komórki, należy zastosować następującą formułę tablicową.

1. Wprowadź lub skopiuj poniższą formułę do pustej komórki:

=TEXTJOIN(", ",TRUE,IF($A$2:$A$20=F1,$C$2:$C$20,""))

Note: W powyższym wzorze, C2: C20 czy kolumna zawiera pasujący rekord, który chcesz zwrócić; A2: A20 czy kolumna zawiera kryterium; i F1 jest konkretnym kryterium, na podstawie którego chcesz zwrócić wartości. Zmień je według swoich potrzeb.

2. Następnie naciśnij Ctrl + Shift + Enter klucze razem, aby uzyskać wszystkie pasujące wartości w jednej komórce, patrz zrzut ekranu:

Porady:

Aby Vlookup i zwrócić wszystkie pasujące wartości na podstawie bardziej szczegółowych wartości w pojedynczej komórce, zastosuj poniższą formułę i naciśnij Ctrl + Shift + Enter klawiatura.

=TEXTJOIN(", ",TRUE,IF(($A$2:$A$20=F1)*($B$2:$B$20=F2),$C$2:$C$20,""))

Uwaga: Ta formuła została pomyślnie zastosowana tylko w programie Excel 2016 i nowszych wersjach. Jeśli nie masz programu Excel 2016, wyświetl tutaj żeby to zdjąć.

Więcej względnych artykułów Vlookup:

  • Wyszukaj i zwróć wiele wartości z listy rozwijanej
  • W programie Excel, jak można pominąć i zwrócić wiele odpowiednich wartości z rozwijanej listy, co oznacza, że ​​po wybraniu jednego elementu z rozwijanej listy wszystkie jego względne wartości są wyświetlane jednocześnie, jak pokazano na poniższym zrzucie ekranu. W tym artykule przedstawię rozwiązanie krok po kroku.
  • Vlookup, aby zwrócić puste zamiast 0 lub nie dotyczy w programie Excel
  • Zwykle po zastosowaniu funkcji vlookup w celu zwrócenia odpowiedniej wartości, jeśli pasująca komórka jest pusta, zwróci ona 0, a jeśli pasująca wartość nie zostanie znaleziona, zostanie wyświetlony błąd # N / A value. Zamiast wyświetlać wartość 0 lub # N / A, jak sprawić, by wyświetlała pustą komórkę?
  • Vlookup, aby zwrócić wiele kolumn z tabeli programu Excel
  • W arkuszu programu Excel można zastosować funkcję Vlookup, aby zwrócić pasującą wartość z jednej kolumny. Ale czasami może być konieczne wyodrębnienie dopasowanych wartości z wielu kolumn, jak pokazano na zrzucie ekranu. W jaki sposób można uzyskać odpowiednie wartości w tym samym czasie z wielu kolumn za pomocą funkcji Vlookup?
  • Przeglądaj wartości w wielu arkuszach roboczych
  • W programie Excel możemy łatwo zastosować funkcję vlookup, aby zwrócić pasujące wartości w pojedynczej tabeli arkusza. Ale czy kiedykolwiek zastanawiałeś się, jak przeglądać wartości w wielu arkuszach roboczych? Przypuśćmy, że mam następujące trzy arkusze z zakresem danych, a teraz chcę uzyskać część odpowiednich wartości na podstawie kryteriów z tych trzech arkuszy.
  • Pasek Super Formula (łatwo edytować wiele wierszy tekstu i formuły); Układ do czytania (łatwe odczytywanie i edytowanie dużej liczby komórek); Wklej do filtrowanego zakresu...
  • Scal komórki / wiersze / kolumny i przechowywanie danych; Podziel zawartość komórek; Połącz zduplikowane wiersze i sumę / średnią... Zapobiegaj zduplikowanym komórkom; Porównaj zakresy...
  • Wybierz Duplikat lub Unikalny Wydziwianie; Wybierz puste wiersze (wszystkie komórki są puste); Super Find i Fuzzy Find w wielu zeszytach ćwiczeń; Losowy wybór ...
  • Dokładna kopia Wiele komórek bez zmiany odwołania do formuły; Automatyczne tworzenie odniesień do wielu arkuszy; Wstaw punktory, Pola wyboru i nie tylko ...
  • Ulubione i szybkie wstawianie formuł, Zakresy, wykresy i obrazy; Szyfruj komórki z hasłem; Utwórz listę mailingową i wysyłaj e-maile ...
  • Wyodrębnij tekst, Dodaj tekst, Usuń według pozycji, Usuń przestrzeń; Tworzenie i drukowanie podsumowań stronicowania; Konwertuj zawartość komórek i komentarze...
  • Super filtr (zapisz i zastosuj schematy filtrów do innych arkuszy); Zaawansowane sortowanie według miesiąca / tygodnia / dnia, częstotliwości i innych; Specjalny filtr pogrubieniem, kursywą ...
  • Połącz skoroszyty i arkusze robocze; Scal tabele na podstawie kluczowych kolumn; Podziel dane na wiele arkuszy; Konwersja wsadowa xls, xlsx i PDF...
  • Grupowanie tabel przestawnych według numer tygodnia, dzień tygodnia i więcej ... Pokaż odblokowane, zablokowane komórki w różnych kolorach; Podświetl komórki, które mają formułę / nazwę...
karta kte 201905
  • Włącz edycję i czytanie na kartach w programach Word, Excel, PowerPoint, Publisher, Access, Visio i Project.
  • Otwieraj i twórz wiele dokumentów w nowych kartach tego samego okna, a nie w nowych oknach.
  • Zwiększa produktywność o 50% i redukuje setki kliknięć myszką każdego dnia!
officetab dół
Comments (8)
No ratings yet. Be the first to rate!
Ahmed Abosamaha
Ahmed Abosamaha
This comment was minimized by the moderator on the site
gents

would you please share with me the formula of the three ways but for data in horizontal table.
Reply
0
0
skyyang
skyyang     Ahmed Abosamaha
This comment was minimized by the moderator on the site
Hello, Ahmed,
To solve your problem, please apply the below formulas:
Get the results vertically: =IFERROR(INDEX($B$2:$K$2, SMALL(IF($A$5=$B$1:$K$1, COLUMN($B$1:$K$1:$K$1)-COLUMN($B$1)+1), ROW(1:1))),"" )
Get the results horizontally: =IFERROR(INDEX($B$2:$K$2, SMALL(IF($D$5=$B$1:$K$1, COLUMN($B$1:$K$1:$K$1)-COLUMN($B$1)+1), COLUMN(A1))),"" )
Ge the results in one cell: =TEXTJOIN(", ",TRUE,IF($B$1:$K$1=J5,$B$2:$K$2,""))
Note: These formulas are array formulas, you should press Ctrl + Shift + Enter keys together to get the correct result.

https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-vlookup-data.png

Pease try, hope this can help you!
Reply
Report
0
0
Ahmed Abosamaha
Ahmed Abosamaha     skyyang
This comment was minimized by the moderator on the site
thanks for your greet support and quick response
Reply
0
0
itika
itika
This comment was minimized by the moderator on the site
how do u get all rows to fill up like in yr video without pressing ctrl+shift+enter
Reply
0
0
Taia
Taia
This comment was minimized by the moderator on the site
After I extend the data, some of the cells is showing a "0" instead of blank
Reply
0
0
Adam
Adam     Taia
This comment was minimized by the moderator on the site
It means that there is missing data or the wrong type of data in the cell(s) from the column that is being referrenced. For instance, Im working with a bunch of data and I want all ID numbers for workers under a certain supervisor. However, in the table column being referenced with all workers' ID numbers, some cells had the workers last name in it and not their ID numbers. Those exact cells produced 0s when the entire formula was put in, and this is because it was text within a column that is mostly numbers.
Reply
0
0
Win E
Win E
This comment was minimized by the moderator on the site
How can you tweak the formula so that you have multiple outputs in column D? E.g. I want to populate column D with each of the countries, such that all of their respective cities will be returned in E? The only work around I have can see is manually changing $D$2
Reply
0
0
Jeff
Jeff
This comment was minimized by the moderator on the site
Thank you very much, you save lots of work here!





Regarding your formula {=TEXTJOIN(", ",TRUE,IF($A$2:$A$15=D2,$B$2:$B$15,"")},

I changed a little : (=TEXTJOIN(CHAR(10),TRUE,IF($A$2:$A$15=D2,$B$2:$B$15,""))}





Regards,



Jeff
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations