Note: The other languages of the website are Google-translated. Back to English

Jak zwrócić wiele pasujących wartości na podstawie jednego lub wielu kryteriów w programie Excel?

Zwykle wyszukanie określonej wartości i zwrócenie pasującego elementu jest łatwe dla większości z nas przy użyciu funkcji WYSZUKAJ.PIONOWO. Ale czy kiedykolwiek próbowałeś zwrócić wiele pasujących wartości na podstawie jednego lub więcej kryteriów, jak pokazano na zrzucie ekranu? W tym artykule przedstawię kilka formuł do rozwiązania tego złożonego zadania w programie Excel.

Za pomocą formuł tablicowych zwraca wiele pasujących wartości na podstawie jednego lub wielu kryteriów


Za pomocą formuł tablicowych zwraca wiele pasujących wartości na podstawie jednego lub wielu kryteriów

Na przykład chcę wyodrębnić wszystkie nazwiska, które mają 28 lat i pochodzą ze Stanów Zjednoczonych, zastosuj następującą formułę:

1. Skopiuj lub wprowadź poniższą formułę do pustej komórki, w której chcesz zlokalizować wynik:

=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

Uwagi: W powyższym wzorze, B2: B11 to kolumna, z której zwracana jest pasująca wartość; F2, C2: C11 to pierwszy warunek i dane w kolumnie, które zawierają pierwszy warunek; G2, D2: D11 to drugi warunek oraz dane kolumny zawierające ten warunek, zmień je według swoich potrzeb.

2. Następnie naciśnij Ctrl + Shift + Enter klucze, aby uzyskać pierwszy pasujący wynik, a następnie wybierz pierwszą komórkę formuły i przeciągnij uchwyt wypełniania w dół do komórek, aż zostanie wyświetlona wartość błędu, teraz wszystkie pasujące wartości są zwracane, jak pokazano na zrzucie ekranu:

Wskazówki: Jeśli potrzebujesz tylko zwrócić wszystkie pasujące wartości na podstawie jednego warunku, zastosuj poniższą formułę tablicową:

=IFERROR(INDEX($B$2:$B$11, SMALL(IF($F$2=$D$2:$D$11, ROW($D$2:$D$11)-ROW($D$2)+1), ROW(1:1))),"" )


Więcej artykułów względnych:

  • Zwróć wiele wartości wyszukiwania w jednej komórce oddzielonej przecinkami
  • W programie Excel możemy zastosować funkcję WYSZUKAJ.PIONOWO, aby zwrócić pierwszą dopasowaną wartość z komórek tabeli, ale czasami musimy wyodrębnić wszystkie pasujące wartości, a następnie oddzielić je określonym separatorem, takim jak przecinek, myślnik itp. W jednym komórka, jak pokazano na poniższym zrzucie ekranu. Jak możemy uzyskać i zwrócić wiele wartości wyszukiwania w jednej komórce oddzielonej przecinkami w programie Excel?
  • Wyszukaj i zwróć wiele pasujących wartości jednocześnie w Arkuszu Google
  • Normalna funkcja Vlookup w arkuszu Google może pomóc w znalezieniu i zwróceniu pierwszej pasującej wartości na podstawie podanych danych. Ale czasami może być konieczne pominięcie i zwrócenie wszystkich pasujących wartości, jak pokazano na poniższym zrzucie ekranu. Czy masz jakieś dobre i łatwe sposoby rozwiązania tego zadania w arkuszu Google?
  • 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.
  • Wyszukaj i zwróć wiele wartości pionowo w programie Excel
  • 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óć pasujące dane między dwiema wartościami w programie Excel
  • W programie Excel możemy zastosować normalną funkcję Vlookup, aby uzyskać odpowiednią wartość na podstawie podanych danych. Ale czasami chcemy pominąć i zwrócić pasującą wartość między dwiema wartościami, jak pokazano na poniższym zrzucie ekranu, jak poradzić sobie z tym zadaniem w programie Excel?

 


  • 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ół

 

Sortuj komentarze według
Komentarze (25)
Brak ocen. Oceń jako pierwszy!
Ten komentarz został zminimalizowany przez moderatora na stronie
Próbowałem dokładnie tej samej formuły; skopiowane 100%. Jedyne, co zmieniłem, to dopasowanie i zwrócenie danych. Kiedy używam tej formuły, program Excel mówi „Wprowadziłeś zbyt wiele argumentów dla tej funkcji).=INDEX('2020 Volume Report'!$B$3:$B$100,SMALL(IF(COUNTIF($A$1,'2020 Volume) Raport'!$A$3:$A$100)*COUNTIF($A$3,'Raport wolumenu 2020'!$D$3:$D$100),ROW('Raport wolumenu 2020'!$A$3:$G$100)- MIN(ROW('2020 Raport wolumenu'!$A$3:$G$100))+1,"0"),ROW(A1),KOLUMNA(A1))
Ten komentarz został zminimalizowany przez moderatora na stronie
Cześć, czy możesz podać tutaj swoje dane i błąd formuły jako screesnhot?
Ten komentarz został zminimalizowany przez moderatora na stronie
Witam, Jak mogę go użyć do stanu poziomego.
Ten komentarz został zminimalizowany przez moderatora na stronie
Co to jest „0” po +1 we wzorze? Nie ma tego w przykładzie pierwszym.
Ten komentarz został zminimalizowany przez moderatora na stronie
Cześć, próbowałem tej samej formuły. otrzymuję wynik, ale po podaniu CSE nie zapewnia on żadnych wielokrotnych odpowiedzi
Ten komentarz został zminimalizowany przez moderatora na stronie

Ten komentarz został zminimalizowany przez moderatora na stronie
Jeśli chodzi o Zwracanie wielu pasujących wartości na podstawie jednego lub wielu kryteriów za pomocą formuł tablicowych: dlaczego jeśli mam dane gdziekolwiek indziej oprócz rozpoczynania od A1, to nie działa, mimo że aktualizuję wszystkie odwołania do komórek w formule?
Ten komentarz został zminimalizowany przez moderatora na stronie
W pierwszym przykładzie, jaka zmiana formuły byłaby potrzebna, aby zwrócić każdy, kto miał mniej niż 28 lat?
Ten komentarz został zminimalizowany przez moderatora na stronie
Cześć,

Zastanawiałem się, czy w ogóle można wpisać drugie kryterium, ale z tego samego zakresu co kryterium pierwsze,

Na przykład w powyższym przykładzie chciałbym wyszukać nazwiska osób zarówno z Ameryki, jak i z Francji Tak więc komórka F3 będzie zawierała Francję, Scarlett i Andrew również zapełnią się na liście w kolumnie G

Z góry dziękujemy za pomoc.
Ten komentarz został zminimalizowany przez moderatora na stronie
Cześć Nick,

Miło, że mogłem pomóc. Jeśli chcesz uzyskać nazwiska osób z Ameryki i Francji, radzę skorzystać z naszego wzoru dwukrotnie, aby uzyskać wynik. Zobacz zrzut ekranu, w F2 i G2 są wartości „Stany Zjednoczone” i „Francja”. Zastosuj formułę =JEŻELIBŁĄD(INDEKS($B$2:$B$11; MAŁY(JEŻELI($F$2=$D$2:$D$11);WIERSZ($D$2:$D$11))-WIERSZ($D$2)+1 ), ROW(1:1))),"" ), aby uzyskać wyniki dla Ameryki. I zastosuj formułę =IFERROR(INDEX($B$2:$B$11, SMALL(IF($G$2=$D$2:$D$11), WIER($D$2:$D$11)-WIERSZ($D$2)+ 1), ROW(1:1))),"" ), aby uzyskać wyniki dla Francji. To proste. Proszę spróbować.

Pozdrawiamy,
Mandy
Ten komentarz został zminimalizowany przez moderatora na stronie
Kiedy używam drugiej formuły i przeciągam w dół, nic się nie pojawia. Wynik formuły (fx) mówi, że powinien coś zwracać, ale jest pusty. Jak to poprawić?
Ten komentarz został zminimalizowany przez moderatora na stronie
Witaj Alisio,

Miło, że mogłem pomóc. Wypróbowałem drugą formułę w artykule i przeciągnąłem ją w dół, reszta wyników została zwrócona. Myślę, że mogą być dwa powody twojego problemu. Po pierwsze, może zapomnisz nacisnąć klawisze Ctrl + Shift + Enter, aby wprowadzić formułę. Po drugie, pasujący wynik to tylko jeden, więc żadne inne wyniki nie są zwracane. Proszę o czek.

Pozdrawiamy,
Mandy
Ten komentarz został zminimalizowany przez moderatora na stronie
Halo,
Próbowałem użyć wzoru i albo generuje on wartość 0, albo dołączony obraz
Ten komentarz został zminimalizowany przez moderatora na stronie
Witam, Milku
Twój zrzut ekranu pokazuje oprogramowanie WPS w wersji MAC, więc nie jestem pewien, czy nasza formuła jest dostępna.
Przesłałem tutaj plik Excela, możesz spróbować sprawdzić, czy może poprawnie obliczyć w Twoim środowisku.
Dziękuję Ci!
Ten komentarz został zminimalizowany przez moderatora na stronie
Cześć,
co byłoby potrzebne do rozwinięcia pierwszej formuły w następującym przypadku:
Niektóre identyfikatory są puste (np. komórka A5 jest pusta) i chciałbym dodatkowy warunek wyprowadzający wiersze tylko wtedy, gdy identyfikatory nie są puste. (Tak więc wyjściem powinni być James i Abdul.
Dzięki!
Ten komentarz został zminimalizowany przez moderatora na stronie
Witaj, Jo,
Aby rozwiązać swój problem, zastosuj poniższą formułę:
=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11)*($A$2:$A$11<>0), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

Proszę ry, mam nadzieję, że może ci to pomóc!
Ten komentarz został zminimalizowany przez moderatora na stronie
Cześć,

jeśli w komórce H1 napiszę "Nazwa" i chcę połączyć to z formułą, jak to działa?
Wtedy mógłbym napisać "ID" w komórce H1 i automatycznie otrzymałbym wynik: AA1004; DD1009; PP1023 (dla pierwszej formuły)

Z góry dziękuję!
Ten komentarz został zminimalizowany przez moderatora na stronie
Witaj Marie
Przepraszam, nie rozumiem, o co chodzi w twoim pierwszym problemie, czy mógłbyś wyjaśnić swój problem jaśniej i szczegółowo? Możesz też wstawić tutaj zrzut ekranu, aby opisać swój problem.
Jeśli chodzi o drugie pytanie, wystarczy zmienić odwołanie do komórki w następujący sposób:
=INDEX($A$2:$A$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

Pamiętaj, aby nacisnąć Ctrl + Shift + Enter klucze razem.
Proszę spróbować, mam nadzieję, że może ci to pomóc!
Ten komentarz został zminimalizowany przez moderatora na stronie
Hej, dziękuję za formułę. Działało to dla „stałych” wartości/tekstu jako kryteriów. Jednak jednym z kryteriów, które próbuję zastosować, jest warunek (wartości <> 0 ), ale nie działa opisana formuła. Czy wiecie, co powinienem zmienić, aby dostosować formułę, abym mógł mieć warunek jako jedno z kryteriów, proszę?

Najlepiej,

John
Ten komentarz został zminimalizowany przez moderatora na stronie
Witaj, Marcusie
Aby rozwiązać swój problem, zapoznaj się z tym artykułem:
https://www.extendoffice.com/documents/excel/6393-excel-vlookup-function.html#b3-2
Istnieje kilka szczegółowych wyjaśnień tego zadania. Wystarczy zmienić kryterium na własne.
Dziękuję Ci!
Ten komentarz został zminimalizowany przez moderatora na stronie
Cześć,

Po pierwsze, dziękuję za udostępnienie!

Czy możesz podać rozwiązanie poniższej sprawy:

Mam 3 kolumny (A: Zawiera informacje referencyjne, B: Zawiera informacje do przeszukania, C: Wynik wyszukiwania)

URL obrazu znajduje się poniżej

https://ibb.co/VHCd09K

Kolumna A-------------------------Kolumna B------------ Kolumna C
Nazwa pliku -------------------------Nazwa-----Nazwa pliku, nazwa dokumentu, Nazwa elementu, nazwa
Zmieniony element------Element--------------Zmieniony element, nazwa elementu, identyfikator elementu
Lokalizacja kolumny
Nazwa dokumentu
Nazwa elementu
Imię lub Nazwa Firmy
Wszystkie produkty
Gwarancja
Nachylenie
Identyfikator elementu

To, czego potrzebuję, to wyszukanie w kolumnie A dowolnego częściowego dopasowania z komórką B2 (Nazwa) lub B3 (Element) i uzyskanie wyniku w jednej komórce,

Dziękuję, Behzadzie
Ten komentarz został zminimalizowany przez moderatora na stronie
Witaj, Behzadzie
Może poniższa funkcja zdefiniowana przez użytkownika może ci pomóc.
Public Function ConcatPartLookUp(rngInput As Range, rngSource As Range, Optional strDelimiter As String, Optional blCaseSensitive)
Dim rng As Range
If strDelimiter = "" Then strDelimiter = ","
If IsMissing(blCaseSensitive) Then
    blCaseSensitive = False
Else
    blCaseSensitive = True
End If
For Each rng In rngSource
    If blCaseSensitive Then
        If InStr(1, rng.Value, rngInput.Value, vbBinaryCompare) > 0 Then ConcatPartLookUp = ConcatPartLookUp & strDelimiter & rng.Value
    Else
        If InStr(1, rng.Value, rngInput.Value, vbTextCompare) > 0 Then ConcatPartLookUp = ConcatPartLookUp & strDelimiter & rng.Value
    End If
Next
If Len(ConcatPartLookUp) > 0 Then ConcatPartLookUp = Mid(ConcatPartLookUp, 2, Len(ConcatPartLookUp))
End Function


Po skopiowaniu i wklejeniu tego kodu, a następnie użyj tej formuły:=ConcatPartLookUp(B2,$A$2:$A$8) aby uzyskać pożądany rezultat.
Proszę spróbować, mam nadzieję, że może ci to pomóc!
Ten komentarz został zminimalizowany przez moderatora na stronie
Cześć,

Dzięki za opublikowanie tych przykładów.
Próbuję to zaimplementować w moim własnym arkuszu, ale nie działa to (może dlatego, że używam europejskiej wersji programu Excel)?

Chcę otrzymać daty dni, w których miałam zmiany lub przepracowałam „kilka” (>0) godzin dla klienta.

Tak więc w I3 jest nazwa, aw J3 miesiąc. K3 i L3 to zmiany (1 jest przepracowane) i godziny (nie wiem jak to ustawić, powinno być więcej niż zero)

Oczekiwane przeze mnie wyniki to:
Przesunięcia: I7 i I8
godziny: J7

Więc przepracowałem ponad 0 godzin dla „osoby 2” w październiku 3-10-2022
miał zmiany dla osoby 2 w dniach '10-10-2022' i 28-10-2022

Kiedy dodam '=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2,$C$2:$C$11))*COUNTIF($G$2,$D$2:$D$11), ROW ($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))'w moim arkuszu programu Excel nie zezwala na przecinek między różnymi częściami formuły.
Więc muszę je zmienić na ';'.
Ale kiedy próbuję, zawsze wyświetla się: „#NAME?”

Czy ktoś może mi w tym pomóc?

Z poważaniem,

Bas
[img]https://drive.google.com/file/d/1iIPQKuj_PNhqWyWlwJ4IQTqGNEd6B9Hw/view?usp=share_link[/img]
Ten komentarz został zminimalizowany przez moderatora na stronie
Cześć, jeśli istnieją zduplikowane wartości (np. dwa adams), jak mogę się upewnić, że zwraca tylko 1 adam, a nie 2?
Ten komentarz został zminimalizowany przez moderatora na stronie
Witaj Bobby,
Aby wyodrębnić tylko unikalne pasujące wartości, należy zastosować poniższą formułę:
Po wklejeniu formuły naciśnij Ctrl + Shift + Enter klucze razem, aby uzyskać prawidłowy wynik.
=JEŻELI.BŁĄD(INDEKS($B$2:$B$5; PODAJ.POZYCJĘ(0;LICZ.JEŻELI(H1:$H$1;$B$2:$B$5)+JEŻELI($D$2:$D$5<>$G$2; 1 , 0)+JEŻELI($C$2:$C$5<>$F$2, 1, 0), 0)), "")

Proszę spróbować, mam nadzieję, że może ci to pomóc!
Nie ma tu jeszcze żadnych komentarzy

Bądż na bieżąco

Prawa autorskie © 2009 - www.extendoffice.com. | Wszelkie prawa zastrzeżone. Zasilany przez ExtendOffice, | Mapa strony
Microsoft i logo Office są znakami towarowymi lub zastrzeżonymi znakami towarowymi Microsoft Corporation w Stanach Zjednoczonych i / lub innych krajach.
Chronione przez Sectigo SSL