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
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.
