✅ Пронађите најближе подударање у Екцелу помоћу формула

Преглед садржаја

Пронађите најближе подударање у програму Екцел

Екцел функције могу бити изузетно моћне ако се снађете у комбиновању различитих формула. Ствари које су се могле чинити немогућим одједном би почеле изгледати као дечја игра.

Један такав пример је проналажење најближег подударања вредности тражења у скупу података у Екцелу.

Постоји неколико корисних функција претраживања у Екцелу (попут ВЛООКУП & ИНДЕКС МАТЦХ), које могу пронаћи најближе подударање у неколико једноставних случајева (као што ћу показати са примерима испод).

Али најбољи део је то што ове функције претраживања можете комбиновати са другим Екцел функцијама како бисте учинили много више (укључујући проналажење најближег подударања вредности тражења на неразврстаној листи).

У овом водичу ћу вам показати како проналазите најближе подударање вредности за претраживање у Екцелу са формулама за претраживање.

Пронађите најближе подударање у програму Екцел

Може постојати много различитих сценарија у којима морате тражити најближе подударање (или најближу одговарајућу вредност).

Испод су примери које ћу приказати у овом чланку:

Нађите стопу провизије на основу продаје
Пронађите најбољег кандидата (на основу најближег искуства)
Проналажење датума следећег догађаја

Хајде да почнемо!

Кликните овде за преузимање датотеке примера

Пронађи стопу провизије (тражи се најближа продајна вредност)

Претпоставимо да имате скуп података као што је приказано испод где желите да пронађете стопе провизије за све продајно особље.

Провизија се додељује на основу продајне вредности. А ово се израчунава помоћу табеле са десне стране.

На пример, ако продавац оствари укупну продају од 5000, онда је провизија 0%, а ако он/она уради укупну продају од 15000, онда је провизија 5%.

Да бисте добили провизију, морате пронаћи најближи распон продаје само нижи од продајне вредности. На пример, за продајну вредност од 15000, провизија би била 10.000 (што је 5%), а за продајну вредност од 25000, провизија би била 20.000 (што је 7%).

Да бисте пронашли најближу вредност продаје и добили провизију, можете да користите приближно подударање у ВЛООКУП -у.

Формула испод би урадила ово:

= ВЛООКУП (Б2, $ Е $ 2: $ Ф $ 6,2,1)

Имајте на уму да је у овој формули последњи аргумент 1, који говори формули да користи приближно тражење. То значи да ће формула проћи кроз продајне вредности у колони Е и пронаћи вредност која је само нижа од тражене вредности.

Тада ће формула ВЛООКУП дати провизију за ову вредност.

Белешка: Да би ово функционисало, морате да сортирате податке у растућем редоследу.

Кликните овде за преузимање датотеке примера

Пронађите најбољег кандидата (на основу најближег искуства)

У горњем примеру, податке је потребно сортирати узлазним редоследом. Али могло би бити случајева да се подаци не сортирају.

Па хајде да размотримо пример и видимо како можемо пронаћи најближе подударање у Екцелу помоћу комбинације формула.

Испод је узорак скупова података у којима морам пронаћи име запосленика које има радно искуство најближе жељеној вриједности. Жељена вредност у овом случају за 2,5 године.

Имајте на уму да подаци нису сортирани. Такође, најближе искуство може бити мање или више од датог. На пример, 2 и 3 године су подједнако блиске (разлика од 0,5 година).

Испод је формула која ће нам дати резултат:

= ИНДЕКС ($ А $ 2: $ А $ 15, МАТЦХ (МИН (АБС (Д2-Б2: Б15)), АБС (Д2- $ Б $ 2: $ Б $ 15), 0))

Трик у овој формули је да промените низ претраге и вредност претраживања да бисте пронашли минималну разлику искуства у потребним и стварним вредностима.

Хајде да прво разумемо како бисте то урадили ручно (а затим ћу објаснити како ова формула функционише).

Када ово радите ручно, проћи ћете кроз сваку ћелију у колони Б и пронаћи разлику у искуству између онога што је потребно и оног које особа има. Када будете имали све разлике, пронаћи ћете ону која је минимална и дохватити име те особе.

Управо то радимо са овом формулом.

Дозволи да објасним.

Тражена вредност у формули МАТЦХ је МИН (АБС (Д2-Б2: Б15)).

Овај део вам даје минималну разлику између датог искуства (које износи 2,5 године) и свих осталих искустава. У овом примеру враћа 0,3

Имајте на уму да сам користио АБС да бих се уверио да тражим најближе (што може бити више или мање од датог искуства).

Сада ова минимална вредност постаје наша вредност за претраживање.

Прегледни низ у функцији МАТЦХ је АБС (Д2- $ Б $ 2: $ Б $ 15).

Ово нам даје низ бројева од којих је одузето 2,5 (потребно искуство).

Дакле, сада имамо вредност тражења (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})

МАТЦХ функција проналази позицију 0,3 у овом низу, што је такође позиција имена особе која има најближе искуство.

Овај број позиције затим користи функција ИНДЕКС за враћање имена особе.

Напомена: У случају да има више кандидата са истим минималним искуством, горња формула ће дати име првог одговарајућег запосленог.

Пронађите датум следећег догађаја

Ово је још један пример где можете користити формуле за тражење да бисте пронашли следећи датум догађаја на основу тренутног датума.

Испод је скуп података где имам називе догађаја и датуме догађаја.

Оно што желим је назив следећег догађаја и датум догађаја за овај предстојећи догађај.

Испод је формула која ће дати назив предстојећег догађаја:

= ИНДЕКС ($ А $ 2: $ А $ 11, МАТЦХ (Е1, $ Б $ 2: $ Б $ 11,1) +1)

И испод формула даје датум предстојећег догађаја:

= ИНДЕКС ($ Б $ 2: $ Б $ 11, МАТЦХ (Е1, $ Б $ 2: $ Б $ 11,1) +1)

Дозволите ми да објасним како ова формула функционише.

Да би добила датум догађаја, функција МАТЦХ тражи тренутни датум у колони Б. У овом случају не тражимо тачно подударање, већ приближан. И стога је последњи аргумент функције МАТЦХ 1 (која налази највећу вредност која је мања или једнака вредности тражења).

Дакле, функција МАТЦХ би вратила позицију ћелије која има датум који је само мањи или једнак тренутном датуму. Дакле, следећи догађај, у овом случају, био би у следећој ћелији (пошто је листа сортирана по растућем редоследу).

Дакле, да бисте добили предстојећи датум догађаја, само додајте један у позицију ћелије коју враћа функција МАТЦХ, и она ће вам дати позицију ћелије следећег датума догађаја.

Ова вредност се затим даје функцијом ИНДЕКС.

За добијање назива догађаја користи се иста формула, а опсег у функцији ИНДЕКС се мења из колоне Б у колону А.

Кликните овде за преузимање датотеке примера

Идеја о овом примеру пала ми је на памет када ми се обратио пријатељ са захтевом. У колони је имао списак свих рођендана својих пријатеља/рођака и желео је да зна следећи рођендан (и име особе).

Ово су три примера који показују како да пронађете најближу одговарајућу вредност у Екцелу помоћу формула за тражење.

Можда ће вам се допасти и следећи Екцел савети/водичи