✅ Najděte nejblíže shodu v aplikaci Excel pomocí vzorců

Obsah

Najděte nejbližší shodu v aplikaci Excel

Funkce Excelu mohou být extrémně výkonné, pokud si poradíte s kombinováním různých vzorců. Věci, které se mohly zdát nemožné, by najednou začaly vypadat jako dětská hra.

Jedním takovým příkladem je nalezení nejbližší shody vyhledávací hodnoty v datové sadě v Excelu.

V aplikaci Excel existuje několik užitečných vyhledávacích funkcí (například VLOOKUP & INDEX MATCH), které v několika jednoduchých případech (jak ukážu na níže uvedených příkladech) najdou nejbližší shodu.

Nejlepší na tom ale je, že tyto vyhledávací funkce můžete kombinovat s jinými funkcemi Excelu a dělat mnohem více (včetně nalezení nejbližší shody vyhledávací hodnoty v netříděném seznamu).

V tomto tutoriálu vám ukážu, jak najdete nejbližší shodu vyhledávací hodnoty v aplikaci Excel s vyhledávacími vzorci.

Najděte nejbližší shodu v aplikaci Excel

Může existovat mnoho různých scénářů, ve kterých je třeba hledat nejbližší shodu (nebo nejbližší odpovídající hodnotu).

Níže jsou uvedeny příklady, kterým se budu věnovat v tomto článku:

Najděte provizi podle prodejů
Najděte nejlepšího kandidáta (na základě nejbližší zkušenosti)
Hledání data příští akce

Začněme!

Kliknutím sem stáhnete ukázkový soubor

Najděte provizní sazbu (hledejte nejbližší hodnotu prodeje)

Předpokládejme, že máte sadu dat, jak je uvedeno níže, kde chcete zjistit provizní sazby všech prodejců.

Provize je přiřazena na základě prodejní hodnoty. A to se vypočítá pomocí tabulky vpravo.

Pokud například prodejce provede celkový prodej 5 000, pak je provize 0% a pokud provede celkový prodej 15 000, pak je provize 5%.

Chcete -li získat sazbu provize, musíte najít nejbližší rozsah prodeje jen nižší než hodnota prodeje. Například pro 15 000 prodejní hodnoty by provize činila 10 000 (což je 5%) a pro prodejní hodnotu 25 000 by byla provizní sazba 20 000 (což je 7%).

Chcete -li najít nejbližší prodejní hodnotu a získat provizní sazbu, můžete použít přibližnou shodu ve VLOOKUP.

Následující vzorec by to udělal:

= VLOOKUP (B2, $ E $ 2: $ F $ 6,2,1)

Všimněte si, že v tomto vzorci je poslední argument 1, který říká, že vzorec má použít přibližné vyhledávání. To znamená, že vzorec by procházel prodejními hodnotami ve sloupci E a našel hodnotu, která je právě nižší než vyhledávací hodnota.

Poté vzorec VLOOKUP uvede provizní sazbu pro tuto hodnotu.

Poznámka: Aby to fungovalo, musíte mít data seřazená vzestupně.

Kliknutím sem stáhnete ukázkový soubor

Najděte nejlepšího kandidáta (na základě nejbližší zkušenosti)

Ve výše uvedeném příkladu bylo nutné data seřadit vzestupně. Mohou však nastat případy, kdy nejsou data tříděna.

Podívejme se tedy na příklad a podívejme se, jak můžeme v aplikaci Excel najít nejbližší shodu pomocí kombinace vzorců.

Níže je ukázková sada dat, kde potřebuji najít jméno zaměstnance, který má pracovní zkušenosti nejblíže požadované hodnotě. Požadovaná hodnota je v tomto případě za 2,5 roku.

Data nejsou tříděna. Také nejbližší zkušenost může být buď menší nebo větší než zkušenost s poskytováním. Například 2 roky a 3 roky jsou oba stejně blízké (rozdíl 0,5 roku).

Níže je uveden vzorec, který nám poskytne výsledek:

= INDEX ($ A $ 2: $ A $ 15, MATCH (MIN (ABS (D2-B2: B15)), ABS (D2- $ B $ 2: $ B $ 15), 0))

Trikem v tomto vzorci je změnit vyhledávací pole a vyhledávací hodnotu tak, aby našel minimální rozdíl zkušeností v požadovaných a skutečných hodnotách.

Pojďme nejprve pochopit, jak byste to udělali ručně (a poté vysvětlím, jak tento vzorec funguje).

Když to děláte ručně, projdete každou buňku ve sloupci B a najdete rozdíl ve zkušenostech mezi tím, co je požadováno, a tím, které má člověk. Jakmile máte všechny rozdíly, najdete ten, který je minimální, a načtěte jméno této osoby.

Přesně to děláme s tímto vzorcem.

Nech mě to vysvětlit.

Vyhledávací hodnota ve vzorci MATCH je MIN (ABS (D2-B2: B15)).

Tato část vám dává minimální rozdíl mezi danou zkušeností (což je 2,5 roku) a všemi ostatními zkušenostmi. V tomto případě vrátí 0,3

Všimněte si, že jsem použil ABS, abych se ujistil, že hledám nejbližší (což může být více nebo méně než daná zkušenost).

Nyní se tato minimální hodnota stává naší vyhledávací hodnotou.

Vyhledávací pole ve funkci MATCH je ABS (D2- $ B $ 2: $ B $ 15).

To nám dává řadu čísel, od kterých bylo odečteno 2,5 (požadovaná zkušenost).

Nyní tedy máme vyhledávací hodnotu (0,3) a vyhledávací pole ({6,8; 0,8; 19,5; 21,8; 14,5; 11,2; 0,3; 9,2; 2; 9,8; 14,8; 0,4; 23,8; 2,9})

Funkce MATCH najde v tomto poli pozici 0,3, což je také pozice jména osoby, která má nejbližší zkušenost.

Toto číslo pozice pak používá funkce INDEX k vrácení jména osoby.

Poznámka: V případě, že existuje více uchazečů, kteří mají stejnou minimální praxi, výše uvedený vzorec uvede jméno prvního odpovídajícího zaměstnance.

Najděte datum příští akce

Toto je další příklad, kde můžete použít vyhledávací vzorce k nalezení dalšího data události na základě aktuálního data.

Níže je soubor dat, kde mám názvy událostí a data událostí.

Chci název další události a datum události pro tuto nadcházející událost.

Níže je uveden vzorec, který dá název nadcházející události:

= INDEX ($ A $ 2: $ A $ 11, MATCH (E1, $ B $ 2: $ B $ 11,1) +1)

A podle následujícího vzorce bude uvedeno datum nadcházející události:

= INDEX ($ B $ 2: $ B $ 11, MATCH (E1, $ B $ 2: $ B $ 11,1) +1)

Dovolte mi vysvětlit, jak tento vzorec funguje.

Chcete -li získat datum události, funkce MATCH hledá aktuální datum ve sloupci B. V tomto případě nehledáme přesnou shodu, ale přibližnou. A proto je posledním argumentem funkce MATCH 1 (která najde největší hodnotu, která je menší nebo rovna vyhledávací hodnotě).

Funkce MATCH by tedy vrátila pozici buňky, která má datum, které je právě menší nebo rovno aktuálnímu datu. Takže další událost, v tomto případě, bude v další buňce (protože seznam je seřazen vzestupně).

Chcete -li tedy získat datum nadcházející události, stačí přidat jednu na pozici buňky vrácenou funkcí MATCH a poskytne vám pozici buňky k datu příští události.

Tato hodnota je pak dána funkcí INDEX.

Chcete -li získat název události, použije se stejný vzorec a rozsah ve funkci INDEX se změní ze sloupce B na sloupec A.

Kliknutím sem stáhnete ukázkový soubor

Myšlenka tohoto příkladu mě napadla, když se na mě obrátil přítel s žádostí. Měl ve sloupci seznam všech narozenin svých přátel/příbuzných a chtěl vědět, že se blíží příští narozeniny (a jméno osoby).

Toto jsou tři příklady, které ukazují, jak pomocí vyhledávacích vzorců najít nejbližší odpovídající hodnotu v aplikaci Excel.

Také by se vám mohly líbit následující Excel tipy/Návody