Funkcie Excelu môžu byť veľmi výkonné, ak sa naučíte kombinovať rôzne vzorce.To, čo sa kedysi zdalo nemožné, sa zrazu môže stať detskou hrou.
Jedným z takýchto príkladov je nájdenie najbližšej hodnoty v množine údajov Excelu.
V Exceli je niekoľko užitočných vyhľadávacích funkcií (napríklad VLOOKUP a INDEX MATCH), ktoré dokážu nájsť najbližšie值(ako je uvedené v príklade nižšie).
Najlepšie na tom však je, že tieto funkcie vyhľadávania môžete skombinovať s inými funkciami programu Excel a urobiť tak oveľa viac (vrátane nájdenia najbližšej zhody s hodnotou vyhľadávania v nezoradenom zozname).
V tomto návode vám ukážem, ako použiť vyhľadávací vzorec na nájdenie najbližšej zhody s vyhľadávanou hodnotou v Exceli.
Existuje mnoho rôznych scenárov, v ktorých musíte nájsť najbližšiu zhodu (alebo hodnotu najbližšej zhody).
Tu je príklad, ktorému sa budem venovať v tomto článku:
- Nájdite sadzby provízií na základe predaja
- Nájdite najlepšieho kandidáta (na základe najbližších skúseností)
- Nájdite dátum nasledujúceho podujatia
Začnime!
Obsah
Nájdite sadzby provízií (hľadajte najbližšiu hodnotu predaja)
Predpokladajme, že máte množinu údajov, ako je tá nižšie, kde chcete nájsť sadzby provízií pre všetkých predajcov.
Provízie sa rozdeľujú na základe hodnoty predaja.Toto sa vypočíta pomocou tabuľky vpravo.
Napríklad, ak je celkový predaj predajcu 5000 0, provízia je 15000 % a ak je jeho celkový predaj 5 XNUMX, provízia je XNUMX %.
Ak chcete získať províznu sadzbu, musíte nájsť najbližší rozsah predaja, ktorý je tesne pod hodnotou predaja.Napríklad pri 15000 10,000 predajoch je provízia 5 25000 (alebo 20,000 %) a pri 7 XNUMX predajoch je provízia XNUMX XNUMX (alebo XNUMX %).
Ak chcete nájsť najbližšiu hodnotu predaja a získať províznu sadzbu, môžete použiť približné párovanie vo VLOOKUP.
Môže to urobiť nasledujúci vzorec:
=VLOOKUP(B2;$E$2:$F$6,2,1;XNUMX;XNUMX)
Všimnite si, že v tomto vzorci je posledný parameter 1, ktorý hovorí, že vzorec má použiť približné vyhľadávanie.To znamená, že vzorec prejde hodnotu predaja v stĺpci E a nájde hodnotu tesne pod hodnotou vyhľadávania.
Vzorec VLOOKUP potom poskytne sadzbu provízie pre túto hodnotu.
注意: Ak to chcete urobiť, musíte zoradiť údaje vo vzostupnom poradí.
Nájdite najlepšieho kandidáta (na základe najbližších skúseností)
Vo vyššie uvedenom príklade je potrebné údaje zoradiť vzostupne.Môžu sa však vyskytnúť prípady, keď údaje nie sú zoradené.
Vezmime si teda príklad a uvidíme, ako môžeme použiť kombinácie vzorcov na nájdenie najbližšej zhody v Exceli.
Nižšie je uvedený príklad súboru údajov, kde potrebujem nájsť mená zamestnancov, ktorých pracovné skúsenosti sú najbližšie k požadovanej hodnote.Predpokladaná hodnota je v tomto prípade 2.5 roka.
Upozorňujeme, že údaje nie sú zoradené.Navyše, najbližších zážitkov môže byť menej alebo viac ako daný zážitok.Napríklad 2 roky a 3 roky sú si rovnako blízke (0.5-ročný rozdiel).
Tu je vzorec, ktorý nám dáva výsledok:
=INDEX($A$2:$A$15,MATCH(MIN(ABS(D2-B2:B15)),ABS(D2-$B$2:$B$15),0))
Trik v tomto vzorci spočíva v zmene vyhľadávacieho poľa a vyhľadávacích hodnôt, aby ste našli najmenší empirický rozdiel medzi požadovanými a skutočnými hodnotami.
Poďme najprv pochopiť, ako by ste to urobili ručne (a potom vám vysvetlím, ako tento vzorec funguje).
Keď to urobíte manuálne, prejdete každú bunku v stĺpci B a nájdete rozdiel medzi skúsenosťami, ktoré chcete, a skúsenosťami, ktoré má.Akonáhle budete mať všetky rozdiely, nájdete ten najmenší a získate meno tejto osoby.
To je presne to, čo robíme s týmto vzorcom.
Nechaj ma vysvetliť.
Hodnota vyhľadávania vo vzorci MATCH je MIN(ABS(D2-B2:B15)).
V tejto časti nájdete minimálny rozdiel medzi danou praxou (tj 2.5 roka) a všetkými ostatnými skúsenosťami.V tomto prípade vráti 0.3
Všimnite si, že používam ABS, aby som sa uistil, že hľadám najbližšieho (pravdepodobne viac alebo menej ako daný zážitok).
Teraz sa táto minimálna hodnota stane našou vyhľadávacou hodnotou.
Vyhľadávacie pole vo funkcii MATCH je ABS(D2-$B$2:$B$15).
Získame tak pole čísel, od ktorých sa odčíta 2.5 (potrebné skúsenosti).
所以現在我們有一個查找值 (0.3) 和一個查找數組 ({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 pozícia mena osoby s najbližšou skúsenosťou.
Funkcia INDEX potom použije číslo úlohy na vrátenie mena osoby.
Poznámka: Ak existuje viacero kandidátov s rovnakou minimálnou praxou, vyššie uvedený vzorec poskytne meno prvého zodpovedajúceho zamestnanca.
Nájdite dátum nasledujúceho podujatia
Tu je ďalší príklad, kde môžete použiť vyhľadávací vzorec na nájdenie ďalšieho dátumu udalosti na základe aktuálneho dátumu.
Nižšie je množina údajov, kde mám názvy udalostí a dátumy udalostí.
Chcem názov nasledujúcej udalosti a dátum udalosti tejto nadchádzajúcej udalosti.
Tu je vzorec, ktorý dáva názov nadchádzajúcej udalosti:
=INDEX($A$2:$A$11,MATCH(E1,$B$2:$B$11,1)+1)
Nasledujúci vzorec poskytne dátumy nadchádzajúcej udalosti:
=INDEX($B$2:$B$11,MATCH(E1,$B$2:$B$11,1)+1)
Dovoľte mi vysvetliť, ako tento vzorec funguje.
Ak chcete získať dátum udalosti, funkcia MATCH hľadá v stĺpci B aktuálny dátum.V tomto prípade nehľadáme presnú zhodu, ale približnú zhodu.Preto je posledným parametrom funkcie MATCH 1 (nájde najväčšiu hodnotu menšiu alebo rovnú hodnote vyhľadávania).
Takže funkcia MATCH vráti pozíciu bunky, ktorej dátum je menší alebo rovný aktuálnemu dátumu.Takže v tomto prípade bude nasledujúca udalosť v nasledujúcej bunke (keďže zoznam je zoradený vzostupne).
Takže ak chcete získať dátum nadchádzajúcej udalosti, stačí pridať jeden k pozícii bunky vrátenej funkciou MATCH a získate pozíciu bunky nasledujúceho dátumu udalosti.
Túto hodnotu potom udáva funkcia INDEX.
Ak chcete získať názov udalosti, použite rovnaký vzorec a zmeňte rozsah vo funkcii INDEX zo stĺpca B na stĺpec A.
Tento príklad mi prišiel na um, keď priateľ podal žiadosť.V stĺpci uvádza narodeniny všetkých svojich priateľov/príbuzných a chce vedieť, že sa blížia ďalšie narodeniny (a meno osoby).
Vyššie uvedené tri príklady ukazujú, ako použiť vyhľadávací vzorec na nájdenie najbližšej hodnoty v Exceli.
