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

Jak przeoczyć znaleźć pierwszą, drugą lub n-tą wartość dopasowania w programie Excel?

Przypuśćmy, że masz dwie kolumny z produktami i ilościami, jak pokazano na poniższym zrzucie ekranu. Co byś zrobił, aby szybko dowiedzieć się, jaka jest ilość pierwszego lub drugiego banana?

Tutaj funkcja vlookup może pomóc w rozwiązaniu tego problemu. W tym artykule pokażemy, jak przeoczyć znaleźć pierwszą, drugą lub n-tą wartość dopasowania za pomocą funkcji Vlookup w programie Excel.

Vlookup znajdź pierwszą, drugą lub n-tą wartość dopasowania w programie Excel za pomocą formuły

Łatwo pomiń znajdź pierwszą wartość dopasowania w Excelu za pomocą Kutools for Excel


Vlookup znajdź pierwszą, drugą lub n-tą wartość dopasowania w programie Excel

Wykonaj następujące czynności, aby znaleźć pierwszą, drugą lub n-tą wartość dopasowania w programie Excel.

1. W komórce D1 wprowadź kryteria, które chcesz pominąć, tutaj wpisuję Banan.

2. Tutaj znajdziemy pierwszą wartość dopasowania banana. Wybierz pustą komórkę, taką jak E2, skopiuj i wklej formułę =INDEX($B$2:$B$6,MATCH(TRUE,EXACT($D$1,$A$2:$A$6),0)) do paska formuły, a następnie naciśnij Ctrl + przesunięcie + Wchodzę klucze jednocześnie.

Uwagi: W tej formule $ B $ 2: $ B $ 6 to zakres pasujących wartości; 2 $ A $: 6 $ to przedział ze wszystkimi kryteriami przeoczenia; $ D $ 1 to komórka zawierająca określone kryteria podglądu.

Następnie otrzymasz pierwszą wartość dopasowania banana w komórce E2. Dzięki tej formule możesz uzyskać tylko pierwszą odpowiednią wartość na podstawie kryteriów.

Aby uzyskać dowolne n-te wartości względne, możesz zastosować następującą formułę: =INDEX($B$2:$B$6,SMALL(IF($D$1=$A$2:$A$6,ROW($A$2:$A$6)-ROW($A$2)+1),1)) + Ctrl + przesunięcie + Wchodzę klucze razem, ta formuła zwróci pierwszą dopasowaną wartość.

Uwagi:

1. Aby znaleźć drugą wartość dopasowania, zmień powyższy wzór na =INDEX($B$2:$B$6,SMALL(IF($D$1=$A$2:$A$6,ROW($A$2:$A$6)-ROW($A$2)+1),2)), a następnie naciśnij Ctrl + przesunięcie + Wchodzę klucze jednocześnie. Zobacz zrzut ekranu:

2. Ostatnia liczba w powyższym wzorze oznacza n-tą wartość dopasowania kryteriów podglądu. Jeśli zmienisz ją na 3, otrzyma trzecią wartość dopasowania, a zmienisz ją na n, zostanie znaleziona n-ta wartość dopasowania.


Vlookup znajdź pierwszą wartość dopasowania w programie Excel za pomocą Kutools for Excel

YMożesz łatwo znaleźć pierwszą wartość dopasowania w programie Excel bez pamiętania formuł z rozszerzeniem Poszukaj wartości na liście formuła formuła Kutools dla programu Excel.

Przed złożeniem wniosku Kutools dla programu ExcelProszę pobierz i zainstaluj najpierw.

1. Wybierz komórkę do zlokalizowania pierwszej pasującej wartości (np. Komórka E2), a następnie kliknij Kutools > Pomocnik formuły > Pomocnik formuły. Zobacz zrzut ekranu:

3. w Pomocnik formuły okno dialogowe, skonfiguruj w następujący sposób:

  • 3.1 W Wybierz formułę pole, znajdź i wybierz Poszukaj wartości na liście;
    Wskazówki: Możesz sprawdzić Filtruj wprowadź określone słowo w polu tekstowym, aby szybko przefiltrować formułę.
  • 3.2 W Tablica_tabeli zaznacz pole tabela zawierająca pierwsze pasujące wartości wartości.;
  • 3.2 W Wyszukaj_wartość wybierz komórkę zawierającą plik Kryteria zwrócisz pierwszą wartość na podstawie;
  • 3.3 W Kolumna wybierz kolumnę, z której zwrócisz dopasowaną wartość. Możesz też wpisać numer kolumny bezpośrednio w polu tekstowym, tak jak potrzebujesz.
  • 3.4 Kliknij OK przycisk. Zobacz zrzut ekranu:

Teraz odpowiednia wartość komórki zostanie automatycznie wypełniona w komórce C10 na podstawie wyboru z listy rozwijanej.

  Jeśli chcesz skorzystać z bezpłatnego okresu próbnego (30-dzień) tego narzędzia, kliknij, aby go pobrać, a następnie przejdź do wykonania operacji zgodnie z powyższymi krokami.


Najlepsze narzędzia biurowe

Kutools dla programu Excel rozwiązuje większość problemów i zwiększa produktywność o 80%

  • Ponowne użycie: Szybko włóż złożone wzory, wykresy i wszystko, czego używałeś wcześniej; Szyfruj komórki z hasłem; Utwórz listę mailingową i wysyłaj e-maile ...
  • 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 bez utraty danych; Podziel zawartość komórek; Połącz zduplikowane wiersze / kolumny... 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 ...
  • 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...
  • Ponad 300 zaawansowanych funkcji. Obsługuje Office / Excel 2007-2021 i 365. Obsługuje wszystkie języki. Łatwe wdrażanie w przedsiębiorstwie lub organizacji. Pełne funkcje 30-dniowa bezpłatna wersja próbna. 60-dniowa gwarancja zwrotu pieniędzy.
karta kte 201905

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!
officetab dół
Sortuj komentarze według
Komentarze (43)
Brak ocen. Oceń jako pierwszy!
Ten komentarz został zminimalizowany przez moderatora na stronie
Cześć, czy możesz dostarczyć wideo dla powyższej formuły, aby dopasować drugą, trzecią wartość danych?
Ten komentarz został zminimalizowany przez moderatora na stronie
Dziękuję bardzo!!!
Ten komentarz został zminimalizowany przez moderatora na stronie
A jeśli banan może być żółty lub zielony, jak możemy użyć tego wzoru, aby pokazać odpowiednią ilość w oparciu o dwie wartości (zamiast jednej w tej chwili)? Dzięki za pomoc!
Ten komentarz został zminimalizowany przez moderatora na stronie
Jeśli wynik wartości to #LICZBA! czy mógłbyś mi pokazać formułę do dodania, aby wróciła do wyniku ZERO. Dziękuję Ci
Ten komentarz został zminimalizowany przez moderatora na stronie
Po prostu dodaj JEŻELI.BŁĄD(twoja formuła, wynik, który chcesz zwrócić), na przykład formuła to =sum(A1:A6), a następnie przekonwertuje się na =JEŻELIBŁĄD(suma(A1:A6),""), będzie zwróć puste, jeśli wynikiem jest błąd, taki jak #LICZBA!.
Ten komentarz został zminimalizowany przez moderatora na stronie
Pomóż mi znaleźć maksymalną wartość banana za pomocą wzoru . Czyli do wyświetlenia 300
Ten komentarz został zminimalizowany przez moderatora na stronie
Jak znaleźć maksymalną wartość Banana
Ten komentarz został zminimalizowany przez moderatora na stronie
=Sumproduct(((A2:A6)=D1)*((B2:B6)=Max(B2:B6))*(B2:B6))
Ten komentarz został zminimalizowany przez moderatora na stronie
[quote]=Sumproduct(((A2:A6)=D1)*((B2:B6)=Max(B2:B6))*(B2:B6))Przez Ferdhy'ego[/cytat] doceniam twoją pomoc FERDHY. wypróbowałem formułę , ale ponieważ max(B2:B6) to 500 (pomarańczowy), otrzymana wartość to 0.
Ten komentarz został zminimalizowany przez moderatora na stronie
Cześć, po prostu użyj tego =SUMPRODUCT(MAX(((A2:A8)=D1)*(B2:B8))) po zmianie w D1 i włożeniu Banana, powinieneś otrzymać 300 , jeśli włożysz Orange, otrzymasz 500 , Ferdy
Ten komentarz został zminimalizowany przez moderatora na stronie
Możesz także użyć:
=max(jeśli(A2:A6=D1, B2:B6)) + Ctrl + Shift + Enter
Ten komentarz został zminimalizowany przez moderatora na stronie
Jak mogę automatycznie zwiększyć ostatnią liczbę podczas przeciągania formuły w dół: =INDEX($B$2:$B$6,SMALL(IF($D$1=$A$2:$A$6,ROW($A$2:$A6)2) -ROW($A$1)+2),XNUMX)),
Ten komentarz został zminimalizowany przez moderatora na stronie
Drogi Warthogb,

Jeśli chcesz automatycznie uzyskać wszystkie dopasowane wartości, przeciągając formułę w dół, zastosuj następującą formułę tablicową:



=JEŻELIBŁĄD(INDEKS($B$2:$B$7;MAŁY(JEŻELI($D$1=$A$2:$A$7;WIERSZ($A$2:$A$7))-WIERSZ($A$2)+1), 1+(WIERSZ(A1)-1))), "") + Ctrl + Shift + Enter
Ten komentarz został zminimalizowany przez moderatora na stronie
Crystal, wielkie dzięki, miałam szansę dopiero dzisiaj 27 zobaczyć twoją pomoc, zrobię formułę jeszcze dzisiaj :)
Ten komentarz został zminimalizowany przez moderatora na stronie
Cześć,
Zastosowałem tę formułę, ale w moim przypadku zamiast nazwy produktu mam cyfry. Kiedy przeciągam formułę w dół, aby znaleźć następną liczbę na liście, pojawia się błąd.

liczba zdarzeń liczba zdarzeń nieparzystych tylko liczba zdarzeń
1 0.3 1 0.3
1 2.5 1 2.5
2 1.1 3 #LICZBA
2 0.5 3 #LICZBA
3 0
3 0.2
3 1
Ten komentarz został zminimalizowany przez moderatora na stronie
Droga Abby,
Funkcja przeciągania w dół może działać tylko dla tych samych wartości podglądu. Ale w twoim przypadku wartości przeglądania są różne (1 i 3).
Użyj tej formuły tablicowej: =IFERROR(INDEX($B$2:$B$8,SMALL(IF($C$4=$A$2:$A$8,ROW($A$2:$A$8))-ROW($A) $2)+1),1+(ROW(A1)-1))), "") + Ctrol + Shift + Enter i przeciągnij formułę w dół, aby uzyskać wszystkie dopasowane wartości w tej samej wartości podglądu, jak pokazano na poniższym zrzucie ekranu.
Ten komentarz został zminimalizowany przez moderatora na stronie
Świetny samouczek! Działa jak urok, nawet na wielu arkuszach w tym samym pliku! Wielkie dzięki!!
Ten komentarz został zminimalizowany przez moderatora na stronie
Moja bieżąca formuła to {=IFERROR(INDEX(Arkusz3!$C$2:$C$596,MAŁA(JEŻELI(Arkusz3!$A$2:$A$596=Arkusz2!A19,ROW(Arkusz3!$A$2:$A$596)) -ROW(INDEX(Arkusz3!$A$2:$A$596,1,1))+1),P19)),0)} Ale jak mam zastosować to z wieloma kryteriami, powiedzmy dwoma dopasowaniami?
Ten komentarz został zminimalizowany przez moderatora na stronie
Mój problem jest podobny
pinguj mnie, jeśli znajdziesz rozwiązanie
Ten komentarz został zminimalizowany przez moderatora na stronie
utwórz kolumnę pomocniczą, która łączy twoje kryteria, a następnie użyj konkatenacji jako kryterium!

Mam nadzieję, że to zadziała!
Ten komentarz został zminimalizowany przez moderatora na stronie
Mam mały problem z tą formułą, nie działa dokładnie w moim przypadku:
=INDEKS($B$2:$B$6,MAŁY(JEŻELI($D$1=$A$2:$A$6,WIERSZ($A$2:$A$6)-WIERSZ($A$2)+1),1) ) + Ctrl + Shift + Enter

co, jeśli kryteria, których szukam, nie są za każdym razem dokładnie takie same (Banana), ale raczej stają się częścią frazy (Republika Bananowa) i tak dalej; co wtedy? Zmieniając liczbę „n” na końcu tej formuły, otrzymuję „#NUM!” odpowiedź. Mam kolumnę słownictwa, w której chcę wyszukać ich znaczenie w drugiej kolumnie, a wpisując jedno słowo, muszę uzyskać wszystkie wystąpienia tego słowa w dowolnej frazie, która ma zostać wymieniona. Jakaś pomoc w tej sprawie?
Dzięki,
RG
Ten komentarz został zminimalizowany przez moderatora na stronie
Czy można użyć tego wzoru do sprawdzenia, czy liczba znajduje się między dwiema liczbami. Poniżej moja formuła. Próba sprawdzenia, czy wpis z osobami i kwota znajduje się między innymi zestawami komórek (przykład: 50,000 74,999 USD i XNUMX XNUMX USD)


=ArrayFormula(INDEX('4 — Lista darczyńców'!$B$2:$B$1000,SMALL(IF('4 — Lista darczyńców'!$F$2:$F$1000>=D$2,ROW('4 — Lista darczyńców) '!$F$2:$F$1000)-ROW('4 — Lista darczyńców'!$F$2)+1),$A6)))
Ten komentarz został zminimalizowany przez moderatora na stronie
Szanowna Pani/Pan,

Mam problem:
Znałem ilość produktu, chcę poznać nazwę produktu o pierwszej lub drugiej wartości dopasowania 200, co byś zrobił?
Wielkie dzięki !

Sima Van Naritha
Ten komentarz został zminimalizowany przez moderatora na stronie
Dzień dobry,
Zakładając, że wartość 200 lokalizuje w komórce F2, wypróbuj tę formułę: = VLOOKUP (F2, IF ({1,0},$B$2:$B$7,$A$2:$A$7),2,0).
Mam nadzieję, że to może pomóc. Dzięki za komentarz.
Ten komentarz został zminimalizowany przez moderatora na stronie
jeśli członek został wezwany 1 października (zbiór danych z października) i nie został wskrzeszony, ponownie cce zadzwoniło do niego 15 listopada (zestaw danych z listopada). Członek zostaje reaktywowany 16 listopada. Podczas sprawdzania ożywienia za pomocą VLookup ustawia TAK dla obu wpisów z października i listopada. Jak tego uniknąć, należy pokazywać „TAK” dla wpisu z listopada, kiedy został on reaktywowany, a także pozostawić wpis z października jako „NIE”.
Ten komentarz został zminimalizowany przez moderatora na stronie
Czy można znaleźć średnią z nieunikalnych danych. A może byłoby możliwe posiadanie listy rozwijanej w komórce z różnymi wartościami?
Ten komentarz został zminimalizowany przez moderatora na stronie
Dobry dzień,
Przepraszamy, nie mogę Ci jeszcze w tym pomóc. Dziękuję za Twój komentarz.
Ten komentarz został zminimalizowany przez moderatora na stronie
A jeśli chcesz przedostatni, przedostatni, n-ty, po prostu dodaj licznik (policz liczbę zdarzeń, które już się wydarzyły) i odejmij go odpowiednio przez 0,1,n.

Dziękuję bardzo! Długo tego szukałem
Ten komentarz został zminimalizowany przez moderatora na stronie
Dobry dzień,
Przepraszamy, nie mogę Ci jeszcze w tym pomóc. Dziękuję za Twój komentarz.
Ten komentarz został zminimalizowany przez moderatora na stronie
Jeśli pierwszy lub dowolny inny wpis dla kolumny B „banan” był pustą komórką, której nie wymagam, to jakie zmiany są wymagane w tej formule, aby pominąć pustą komórkę w kolumnie B.
Ten komentarz został zminimalizowany przez moderatora na stronie
Przepraszam, używam tej formuły
=INDEX($B$2:$B$6,SMALL(IF($D$1=$A$2:$A$6,ROW($A$2:$A$6)-ROW($A$2)+1),1))
Ten komentarz został zminimalizowany przez moderatora na stronie
ROZWIĄZANY:
=SMALL(IF(A2:A7=D1,IF(B2:B7<>"",B2:B7)),1)

Jeśli wymagana jest druga lub trzecia liczba, zamień ),2) na 3 lub 1

Ta formuła nie wymaga indeksu, ponieważ bezpośrednio patrzy na wartość w Cell
Ten komentarz został zminimalizowany przez moderatora na stronie
Korekta do poprzedniej formuły:
Wartość była mniejsza lub większa.

Zaktualizowana formuła
=INDEX($B$2:$B$7,SMALL(IF($A2:$A6=$D$1,IF($B$2:$B$7<>"",ROW($A2:$A6)-ROW($A2)+1)),1))

To pomija pustą komórkę i umieszcza wartość niepustej komórki. Zamień +1 na +2 lub +3 dla drugiej lub trzeciej wartości
Ten komentarz został zminimalizowany przez moderatora na stronie
Idealne wyjaśnienie, dzięki.
Ten komentarz został zminimalizowany przez moderatora na stronie
Mam scenariusz... Jak mogę uzyskać ostatnią cenę czegokolwiek w celach informacyjnych... Przykład: Pierwsza cena banana wynosiła 200... Przy zakupie po raz drugi; Muszę wyświetlić 200 w komórce oczekiwanej ceny, a następnie, jeśli kupię to w dniu 220, ustawię tę wartość ręcznie jako 220... Za każdym razem, gdy będę kupować banana; Muszę wyświetlić 220 z ostatniej ceny zakupu
Ten komentarz został zminimalizowany przez moderatora na stronie
Try =INDEX($B$2:$B$6,XMATCH(TRUE,EXACT($D$1,$A$2:$A$6),0,-1))

Jest to zasadniczo odwrócenie kolejności wyszukiwania i zwrócenie pierwszego dopasowania za pomocą funkcji XMATCH.

Lepiej późno niż wcale, mam nadzieję, że komuś pomoże :)
Nie ma tu jeszcze żadnych komentarzy
Pokaż więcej
Zostaw swój komentarz
Publikowanie jako gość
×
Oceń ten post:
0   Postacie
Sugerowane lokalizacje

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