Przejdź do głównej zawartości

 Jak pominąć i zwrócić pasującą wartość na przefiltrowanej liście?

Funkcja WYSZUKAJ.PIONOWO może pomóc ci znaleźć i domyślnie zwrócić pierwszą pasującą wartość, niezależnie od tego, czy jest to normalny zakres, czy filtrowana lista. Czasami po prostu chcesz pominąć i zwrócić tylko widoczną wartość, jeśli istnieje filtrowana lista. Jak poradzisz sobie z tym zadaniem w programie Excel?

Wyszukaj i zwróć pasującą wartość na przefiltrowanej liście za pomocą formuł tablicowych


Wyszukaj i zwróć pasującą wartość na przefiltrowanej liście za pomocą formuł tablicowych

Poniższe formuły tablicowe mogą pomóc w znalezieniu i zwróceniu pasującej wartości na przefiltrowanej liście, wykonaj następujące czynności:

Wprowadź tę formułę:

=INDEX(B4:B19,MATCH(1,IF(SUBTOTAL(3,OFFSET(A4:A19,ROW(A4:A19)-ROW(A4),0,1))>0,IF(A4:A19=F2,1)),0)) do komórki, w której chcesz zlokalizować wynik, a następnie naciśnij Ctrl + Shift + Enter klucze razem, a otrzymasz odpowiednią wartość, jak potrzebujesz, patrz zrzut ekranu:

Note: W powyższym wzorze:

F2: jest wartością wyszukiwania, dla której chcesz znaleźć odpowiadającą jej wartość;

A4: A19: to zakres danych zawierający wartość wyszukiwania;

B4: B19: to dane kolumny zawierające wartość wyniku, którą chcesz zwrócić.

Oto inna formuła tablicowa, która również może wyświadczyć ci przysługę, wprowadź tę formułę:

=VLOOKUP(F2,IF(SUBTOTAL(3,OFFSET(A4:A19,ROW(A4:A19)-ROW(A4),0,1))>0,A4:D19),4,0) do pustej komórki, a następnie naciśnij Ctrl + Shift + Enter klucze razem, aby uzyskać poprawny wynik, patrz zrzut ekranu:

Uwaga: W powyższym wzorze:

F2: jest wartością wyszukiwania, dla której chcesz znaleźć odpowiadającą jej wartość;

A4: A19: to zakres danych zawierający wartość wyszukiwania;

A4: D19: to zakres danych, którego chcesz użyć;

liczba 4: to numer kolumny zwracanej przez dopasowaną wartość.

Najlepsze narzędzia biurowe

🤖 Pomocnik AI Kutools: Zrewolucjonizuj analizę danych w oparciu o: Inteligentne wykonanie   |  Wygeneruj kod  |  Twórz niestandardowe formuły  |  Analizuj dane i generuj wykresy  |  Wywołaj funkcje Kutools...
Popularne funkcje: Znajdź, wyróżnij lub zidentyfikuj duplikaty   |  Usuń puste wiersze   |  Łącz kolumny lub komórki bez utraty danych   |   Okrągły bez wzoru ...
Super wyszukiwanie: Wiele kryteriów VLookup    Wiele wartości VLookup  |   Przeglądanie pionowe na wielu arkuszach   |   Wyszukiwanie rozmyte ....
Zaawansowana lista rozwijana: Szybko twórz listę rozwijaną   |  Zależna lista rozwijana   |  Lista rozwijana wielokrotnego wyboru ....
Menedżer kolumn: Dodaj określoną liczbę kolumn  |  Przesuń kolumny  |  Przełącz stan widoczności ukrytych kolumn  |  Porównaj zakresy i kolumny ...
Polecane funkcje: Fokus siatki   |  Widok projektu   |   Duży pasek formuły    Menedżer skoroszytów i arkuszy   |  Biblioteka zasobów (Automatyczny tekst)   |  Selektor dat   |  Połącz arkusze   |  Szyfruj/odszyfruj komórki    Wysyłaj e-maile według listy   |  Super filtr   |   Specjalny filtr (filtruj pogrubienie/kursywa/przekreślenie...) ...
15 najlepszych zestawów narzędzi12 Tekst Tools (Dodaj tekst, Usuń znaki, ...)   |   50 + Wykres rodzaje (Wykres Gantta, ...)   |   40+ Praktyczne Wzory (Oblicz wiek na podstawie urodzin, ...)   |   19 Wprowadzenie Tools (Wstaw kod QR, Wstaw obraz ze ścieżki, ...)   |   12 Konwersja Tools (Liczby na słowa, Przeliczanie walut, ...)   |   7 Połącz i podziel Tools (Zaawansowane wiersze łączenia, Podział komórki, ...)   |   ... i więcej

Zwiększ swoje umiejętności Excela dzięki Kutools for Excel i doświadcz wydajności jak nigdy dotąd. Kutools dla programu Excel oferuje ponad 300 zaawansowanych funkcji zwiększających produktywność i oszczędzających czas.  Kliknij tutaj, aby uzyskać funkcję, której najbardziej potrzebujesz...

Opis


Karta Office wprowadza interfejs z zakładkami do pakietu Office i znacznie ułatwia pracę

  • 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!
Comments (7)
Rated 5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
=VLOOKUP(A2,IF(SUBTOTAL(3,OFFSET(Consolidate!D:E,@ROW(Consolidate!D:E)-ROW(Consolidate!D1),0,1))>0,Consolidate!D:E),2,0)
while using this formula through VBA, i am getting @ in front ROW
This comment was minimized by the moderator on the site
Precisely what I needed thank you so much!
Rated 5 out of 5
This comment was minimized by the moderator on the site
need to use the above for 2 worksheets to look up the filtered data from one worksheet and to use this to replace the data from another worksheet with filtered columns
This comment was minimized by the moderator on the site
Hello, Be,
If you want to lookup and return the matched value from another worksheet, please apply the below formula:
=INDEX(Sheet1!B2:B11,MATCH(1,IF(SUBTOTAL(3,OFFSET(Sheet1!A2:A11,ROW(Sheet1!A2:A11)-ROW(Sheet1!A2),0,1))>0,IF(Sheet1!A2:A11=F2,1)),0))

After pasting the formula, please press Ctrl + Shift + Enter keys together.

Note: In the above formula:
Sheet1: is the main worksheet contains the data you want to use;
B2:B11: is the column data which contains the result value you want to return;
A2:A11: is the data range which contains the lookup value;
F2: is the lookup value you want to find its corresponding value.
This comment was minimized by the moderator on the site
NEED HELP TO USE THE SAME FORMULA IN TWO DIFFERENT WORKBOOKS WITH FILTERED COLUMNS asap for a project to be submitted now, but am getting confused onto what to use for which field
This comment was minimized by the moderator on the site
this Vlookup function doesn't return with all results some values returned with #N/A
This comment was minimized by the moderator on the site
Hello Mohamed Kamal,Sorry to hear that. Please make sure the formula is entered with you pressing the Ctrl + Shift + Enter keys. If the solution can't solve your proplem, please send us the screenshot of the details. Thanks!Sincerely,Mandy
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations