Přejít k hlavnímu obsahu

Vyhledejte nejbližší hodnotu shody s více kritérii

V některých případech možná budete muset vyhledat nejbližší nebo přibližnou hodnotu shody na základě více než jednoho kritéria. S kombinací INDEX, MATCH a IF funkce, můžete to rychle provést v aplikaci Excel.


Jak vyhledat hodnotu nejbližší shody s více než jedním kritériem?

Jak ukazuje následující snímek obrazovky, musíte najít správnou osobu pro práci na základě dvou kritérií „hlavní je Počítač“A„ pracovní zkušenost je 15 let “.

Poznámka: Aby to fungovalo správně, je-li tam duplicitní major, pracovní zkušenosti těchto duplicitních majorů by měly být seřazeny vzestupně.

1. Vyberte prázdnou buňku pro výstup výsledku, potom do ní zkopírujte níže uvedený vzorec a stiskněte Ctrl + směna + vstoupit klávesy pro získání výsledku.

=INDEX(D3:D8,MATCH(G5,IF(B3:B8=G4,C3:C8),1))

Poznámky: v tomto vzorci:

  • D3: D8 je rozsah sloupců obsahuje výsledek, který hledáte;
  • G5 obsahuje druhé kritérium (zkušenost číslo 15), na kterém budete hledat hodnotu;
  • G4 obsahuje první kritérium (Počítač), na kterém vyhledáváte hodnotu;
  • B3: B8 je rozsah buněk odpovídajících prvním kritériím;
  • C3: C8 je rozsah buněk odpovídajících druhým kritériím;
  • Číslo 1 je přibližné vyhledávání, což znamená, že pokud nelze najít přesnou hodnotu, najde největší hodnotu, která je menší než vyhledávací hodnota;
  • Tento vzorec musí být zadán jako maticový vzorec s Ctrl + směna + vstoupit klíče.

Jak tento vzorec funguje

Tento vzorec lze rozdělit na několik komponent:

  • IF(B3:B8=G4,C3:C8): funkce IF zde vrací výsledek jako {9;13;FALSE;FALSE;FALSE;FALSE}, který vychází z testování hodnot v B3: B8, aby se zjistilo, zda odpovídají hodnotám v G4. Pokud existuje shoda, vrátí odpovídající hodnotu, jinak vrátí FALSE. Zde najdete dva zápasy a čtyři neshody.
  • Vzorec pole =MATCH(G5,{9;13;FALSE;FALSE;FALSE;FALSE},1): funkce MATCH najde pozici čísla 15 (hodnota v G5) v rozsahu C3: C8. Protože číslo 15 nelze najít, odpovídá nejbližší nejmenší hodnotě 13. Výsledek je tedy 2.
  • A =INDEX(D3:D8,2): Funkce INDEX vrací hodnotu druhé buňky v rozsahu D3: D8. Konečným výsledkem je tedy Amy.

Související funkce

Funkce Excel IF
Funkce IF je jednou z nejjednodušších a nejužitečnějších funkcí v sešitu aplikace Excel. Provádí jednoduchý logický test, který v závislosti na výsledku porovnání, a vrací jednu hodnotu, pokud je výsledek PRAVDA, nebo jinou hodnotu, pokud je výsledek FALSE.

Funkce Excel MATCH
Funkce Microsoft Excel MATCH hledá konkrétní hodnotu v rozsahu buněk a vrátí relativní pozici této hodnoty.

Funkce Excel INDEX
Funkce INDEX vrací zobrazenou hodnotu na základě dané polohy z rozsahu nebo pole.


Související články

Průměrné buňky na základě více kritérií
V aplikaci Excel může být většina z nás obeznámena s funkcemi COUNTIF a SUMIF, mohou nám pomoci spočítat nebo sčítat hodnoty na základě kritérií. Ale pokusili jste se někdy vypočítat průměr hodnot na základě jednoho nebo více kritérií v aplikaci Excel? V tomto výukovém programu najdete podrobné příklady a vzorce, které vám to usnadní.
Kliknutím se dozvíte více ...

Počítat buňky, pokud je splněno jedno z více kritérií
Tento kurz sdílí způsoby, jak počítat buňky, pokud obsahují X nebo Y nebo Z… atd. V aplikaci Excel.
Kliknutím se dozvíte více ...

Počítat jedinečné hodnoty na základě více kritérií
V tomto článku uvádíme několik příkladů, jak počítat jedinečné hodnoty založené na jednom nebo více kritériích v listu s podrobnými metodami krok za krokem.
Kliknutím se dozvíte více ...


Nejlepší kancelářské nástroje produktivity

Kutools pro Excel - pomůže vám vyniknout před davem

Populární funkce: Najít, zvýraznit nebo identifikovat duplikáty  |  Odstranit prázdné řádky  |  Kombinujte sloupce nebo buňky bez ztráty dat  |  Kolo bez vzorce ...
Super VLookup: Více kritérií  |  Vícenásobná hodnota  |  Přes Multi-Sheets  |  Fuzzy vyhledávání...
Adv. Rozbalovací seznam: Snadno rozevírací seznam  |  Závislý rozbalovací seznam  |  Vícenásobný výběr rozevíracího seznamu...
Správce sloupců: Přidejte konkrétní počet sloupců  |  Přesunout sloupce  |  Přepnout stav viditelnosti skrytých sloupců  Porovnejte sloupce s Vyberte stejné a různé buňky ...
Doporučené funkce: Zaměření mřížky  |  Návrhové zobrazení  |  Velký Formula Bar  |  Správce sešitů a listů | Knihovna zdrojů (Automatický text)  |  Výběr data  |  Zkombinujte pracovní listy  |  Šifrovat/dešifrovat buňky  |  Odesílat e-maily podle seznamu  |  Super filtr  |  Speciální filtr (filtr tučné/kurzíva/přeškrtnuté...) ...
Top 15 sad nástrojů12 Text Tools (doplnit text, Odebrat znaky ...)  |  50+ Graf Typ nemovitosti (Ganttův diagram ...)  |  40+ Praktické Vzorce (Vypočítejte věk na základě narozenin ...)  |  19 Vložení Tools (Vložte QR kód, Vložit obrázek z cesty ...)  |  12 Konverze Tools (Čísla na slova, Přepočet měny ...)  |  7 Sloučit a rozdělit Tools (Pokročilé kombinování řádků, Rozdělit buňky Excelu ...)  |  ... a více

Kutools pro Excel se může pochlubit více než 300 funkcemi, Zajištění toho, že to, co potřebujete, je jen jedno kliknutí...


Záložka Office - Povolte čtení a úpravy na záložkách v Microsoft Office (včetně Excelu)

  • Jednu sekundu přepnete mezi desítkami otevřených dokumentů!
  • Snižte stovky kliknutí myší každý den, sbohem s myší rukou.
  • Zvyšuje vaši produktivitu o 50% při prohlížení a úpravách více dokumentů.
  • Přináší efektivní karty do Office (včetně Excelu), stejně jako Chrome, Edge a Firefox.
Comments (1)
Rated 0.5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
Keep yrs at "15" and switch major to "science"...formula busts. This is not a robust formula...
Rated 0.5 out of 5
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations