✅ Nájdite najbližšiu zhodu v programe Excel pomocou vzorcov

Obsah

Nájdite najbližšiu zhodu v Exceli

Funkcie Excelu môžu byť mimoriadne výkonné, ak sa zorientujete v kombinovaní rôznych vzorcov. Veci, ktoré sa mohli zdať nemožné, by zrazu začali vyzerať ako detská hra.

Jedným z takýchto príkladov je nájsť najbližšiu zhodu vyhľadávanej hodnoty v množine údajov v programe Excel.

V programe Excel existuje niekoľko užitočných funkcií vyhľadávania (napríklad VLOOKUP & INDEX MATCH), ktoré v niekoľkých jednoduchých prípadoch (ako ukážem na nižšie uvedených príkladoch) nájdu najbližšiu zhodu.

Najlepšie na tom však je, že tieto vyhľadávacie funkcie môžete skombinovať s inými funkciami Excelu, aby ste urobili oveľa viac (vrátane nájdenia najbližšej zhody vyhľadávacej hodnoty v netriedenom zozname).

V tomto tutoriáli vám ukážem, ako pomocou vyhľadávacích vzorcov nájdete najbližšiu zhodu vyhľadávacej hodnoty v programe Excel.

Nájdite najbližšiu zhodu v Exceli

Môže existovať mnoho rôznych scenárov, v ktorých musíte hľadať najbližšiu zhodu (alebo najbližšiu zodpovedajúcu hodnotu).

Nasledujú príklady, ktorým sa budem venovať v tomto článku:

Zistite províziu podľa tržieb
Nájdite najlepšieho kandidáta (na základe najbližšej skúsenosti)
Hľadanie dátumu ďalšej akcie

Začnime!

Kliknutím sem stiahnete ukážkový súbor

Zistite sadzbu provízie (hľadáte najbližšiu hodnotu predaja)

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, kde chcete nájsť provízne sadzby pre všetkých predajcov.

Provízia je priradená na základe predajnej hodnoty. A to sa vypočíta pomocou tabuľky vpravo.

Ak napríklad predajca urobí celkový predaj 5 000, potom je provízia 0% a ak urobí celkový predaj 15 000, potom je provízia 5%.

Ak chcete získať sadzbu provízie, musíte nájsť najbližší rozsah predaja, ktorý je nižší ako hodnota predaja. Napríklad pri predajnej hodnote 15 000 by bola provízia 10 000 (čo je 5%) a pri predajnej hodnote 25 000 by bola provízia 20 000 (čo je 7%).

Ak chcete nájsť najbližšiu hodnotu predaja a získať províziu, môžete použiť približnú zhodu vo VLOOKUP.

Nasledujúci vzorec by to urobil:

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

Všimnite si toho, že v tomto vzorci je posledný argument 1, ktorý hovorí vzorcu, aby použil približné vyhľadávanie. To znamená, že vzorec by prešiel hodnotami predaja v stĺpci E a našiel hodnotu, ktorá je len nižšia ako hodnota vyhľadávania.

Potom vzorec VLOOKUP uvedie províziu pre túto hodnotu.

Poznámka: Aby to fungovalo, musíte mať údaje zoradené vzostupne.

Kliknutím sem stiahnete ukážkový súbor

Nájdite najlepšieho kandidáta (na základe najbližšej skúsenosti)

Vo vyššie uvedenom príklade bolo potrebné údaje zoradiť vzostupne. Môžu však nastať prípady, keď údaje nie sú zoradené.

Pozrime sa teda na príklad a uvidíme, ako môžeme v programe Excel nájsť najbližšiu zhodu pomocou kombinácie vzorcov.

Nasleduje ukážkový súbor údajov, kde musím nájsť meno zamestnanca, ktorý má pracovné skúsenosti najbližšie k požadovanej hodnote. Požadovaná hodnota v tomto prípade za 2,5 roka.

Upozorňujeme, že údaje nie sú zoradené. Tiež najbližší zážitok môže byť buď menší alebo väčší ako zážitok z darovania. Napríklad 2 roky a 3 roky sú rovnako blízke (rozdiel 0,5 roka).

Nasleduje vzorec, ktorý nám poskytne výsledok:

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

Trikom v tomto vzorci je zmeniť vyhľadávacie pole a vyhľadávaciu hodnotu tak, aby našiel minimálny rozdiel v skúsenostiach v požadovaných a skutočných hodnotách.

Poďme najskôr pochopiť, ako by ste to urobili ručne (a potom vysvetlím, ako tento vzorec funguje).

Keď to robíte ručne, prejdete každou bunkou v stĺpci B a zistíte rozdiel v skúsenostiach medzi tým, čo je požadované a tým, ktoré má človek. Keď máte všetky rozdiely, nájdete ten, ktorý je minimálny, a načítajte meno tejto osoby.

Presne to robíme s týmto vzorcom.

Nechaj ma vysvetliť.

Vyhľadávacia hodnota vo vzorci MATCH je MIN (ABS (D2-B2: B15)).

Táto časť vám dáva minimálny rozdiel medzi danou praxou (ktorá je 2,5 roka) a všetkými ostatnými skúsenosťami. V tomto prípade vráti 0,3

Všimnite si toho, že som použil ABS, aby som sa uistil, že hľadám najbližšie (čo môže byť viac alebo menej ako daný zážitok).

Teraz sa táto minimálna hodnota stáva našou vyhľadávacou hodnotou.

Pole vyhľadávania vo funkcii MATCH je ABS (D2- $ B $ 2: $ B $ 15).

To nám dáva pole čísel, od ktorých bolo odpočítaných 2,5 (požadovaná skúsenosť).

Teraz teda máme vyhľadávaciu hodnotu (0,3) a vyhľadávacie 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})

Funkcia MATCH nájde v tomto poli pozíciu 0,3, čo je tiež pozícia mena osoby, ktorá má najbližšie skúsenosti.

Toto číslo pozície potom použije funkcia INDEX na vrátenie mena osoby.

Poznámka: V prípade, že existuje viac uchádzačov s rovnakou minimálnou praxou, vyššie uvedený vzorec uvedie meno prvého zodpovedajúceho zamestnanca.

Nájdite dátum ďalšej udalosti

Toto je ďalší príklad, kde môžete pomocou vyhľadávacích vzorcov nájsť nasledujúci dátum udalosti na základe aktuálneho dátumu.

Nasleduje súbor údajov, kde mám názvy udalostí a dátumy udalostí.

Čo chcem, je názov nasledujúcej akcie a dátum udalosti tejto nadchádzajúcej udalosti.

Nasleduje vzorec, ktorý poskytne názov nadchádzajúcej udalosti:

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

Nasledujúci vzorec bude uvádzať dátum nadchádzajúcej udalosti:

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

Vysvetlím, ako tento vzorec funguje.

Na získanie dátumu udalosti funkcia MATCH hľadá aktuálny dátum v stĺpci B. V tomto prípade nehľadáme presnú, ale iba približnú zhodu. A preto posledný argument funkcie MATCH je 1 (ktorý nájde najväčšiu hodnotu, ktorá je menšia alebo rovná vyhľadávanej hodnote).

Funkcia MATCH by teda vrátila pozíciu bunky, ktorá má dátum, ktorý je aktuálny dátum menší alebo rovný. Takže ďalšia udalosť, v tomto prípade, bude v ďalšej bunke (pretože zoznam je zoradený vzostupne).

Ak chcete získať dátum nadchádzajúcej udalosti, stačí pridať jeden k pozícii bunky vrátenej funkciou MATCH a poskytne vám pozíciu bunky k dátumu nasledujúcej udalosti.

Táto hodnota je potom daná funkciou INDEX.

Na získanie názvu udalosti sa použije rovnaký vzorec a rozsah vo funkcii INDEX sa zmení zo stĺpca B na stĺpec A.

Kliknutím sem stiahnete ukážkový súbor

Myšlienka tohto príkladu mi prišla, keď sa na mňa obrátil priateľ s požiadavkou. V stĺpci mal zoznam všetkých narodenín svojich priateľov/príbuzných a chcel vedieť, ktoré narodeniny sa budú blížiť (a meno osoby).

Toto sú tri príklady, ktoré ukazujú, ako nájsť najbližšiu zodpovedajúcu hodnotu v programe Excel pomocou vyhľadávacích vzorcov.

Tiež by sa vám mohli páčiť nasledujúce tipy/návody pre Excel