Функциите на Excel могат да бъдат много мощни, ако се научите да комбинирате различни формули.Това, което някога е изглеждало невъзможно, изведнъж може да стане като детска игра.
Един такъв пример е намирането на най-близката стойност в набор от данни на Excel.
Има няколко полезни функции за търсене в Excel (като VLOOKUP и INDEX MATCH), които могат да намерят най-близките值(както е показано в примера по-долу).
Но най-хубавото е, че можете да комбинирате тези функции за търсене с други функции на Excel, за да направите много повече (включително намиране на най-близкото съвпадение със стойност за търсене в несортиран списък).
В този урок ще ви покажа как да използвате формула за търсене, за да намерите най-близкото съвпадение със стойност за търсене в Excel.
Има много различни сценарии, при които трябва да намерите най-близкото съвпадение (или най-близката стойност на съвпадението).
Ето един пример, който ще разгледам в тази статия:
- Намерете комисионни въз основа на продажбите
- Намерете най-добрия кандидат (въз основа на най-близкия опит)
- Намерете датата на следващото събитие
да започваме!
Съдържание
Намерете комисионни (потърсете най-близката стойност на продажбите)
Да предположим, че имате набор от данни като този по-долу, където искате да намерите комисионните за всички търговци.
Комисионните се разпределят въз основа на стойността на продажбата.Това се изчислява с помощта на таблицата вдясно.
Например, ако общите продажби на продавача са 5000, комисионната е 0%, а ако общите му продажби са 15000 5, комисионната е XNUMX%.
За да получите комисионна, трябва да намерите най-близкия диапазон на продажбите, който е точно под стойността на продажбите.Например за 15000 10,000 продажби комисионната е 5 25000 (или 20,000%), а за 7 XNUMX продажби комисионната е XNUMX XNUMX (или XNUMX%).
За да намерите най-близката стойност за продажба и да получите комисионна, можете да използвате приблизително съвпадение във VLOOKUP.
Следната формула може да направи това:
=VLOOKUP(B2;$E$2:$F$6,2,1;XNUMX;XNUMX)
Имайте предвид, че в тази формула последният параметър е 1, което казва на формулата да използва приблизително търсене.Това означава, че формулата ще премине през стойността на продажбите в колона E и ще намери стойността точно под стойността за търсене.
След това формулата VLOOKUP ще даде комисионната за тази стойност.
注意: За да направите това, трябва да сортирате данните във възходящ ред.
Намерете най-добрия кандидат (въз основа на най-близкия опит)
В горния пример данните трябва да бъдат сортирани във възходящ ред.Но може да има случаи, когато данните не са сортирани.
Така че нека вземем пример и да видим как можем да използваме комбинации от формули, за да намерим най-близкото съвпадение в Excel.
По-долу е даден примерен набор от данни, където трябва да намеря имената на служителите, чийто трудов опит е най-близо до желаната стойност.Очакваната стойност в този случай е 2.5 години.
Имайте предвид, че данните не са сортирани.Освен това най-близките преживявания може да са по-малко или повече от дадено преживяване.Например 2 години и 3 години са еднакво близки (0.5 години разлика).
Ето формулата, която ни дава резултата:
=INDEX($A$2:$A$15,MATCH(MIN(ABS(D2-B2:B15)),ABS(D2-$B$2:$B$15),0))
Номерът в тази формула е да промените масива за търсене и стойностите за търсене, за да намерите най-малката емпирична разлика между желаните и действителните стойности.
Нека първо разберем как бихте го направили ръчно (и след това ще обясня как работи тази формула).
Когато правите това ръчно, преминавате през всяка клетка в колона B и намирате разликата между опита, който искате, и опита, който имате.След като имате всички разлики, намирате най-малката и получавате името на този човек.
Точно това правим с тази формула.
Нека обясня.
Стойността за търсене във формулата MATCH е MIN(ABS(D2-B2:B15)).
Този раздел ви дава минималната разлика между дадения опит (т.е. 2.5 години) и всички останали опитности.В този случай връща 0.3
Имайте предвид, че използвам ABS, за да съм сигурен, че търся най-близкия (вероятно повече или по-малко от даденото преживяване).
Сега тази минимална стойност става нашата стойност за търсене.
Масивът за търсене във функцията MATCH е ABS(D2-$B$2:$B$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})
Функцията MATCH намира позицията 0.3 в този масив, която е позицията на името на човека с най-близък опит.
След това функцията INDEX използва номера на заданието, за да върне името на лицето.
Забележка: Ако има няколко кандидати с еднакъв минимален опит, горната формула ще даде името на първия съвпадащ служител.
Намерете датата на следващото събитие
Ето още един пример, в който можете да използвате формула за търсене, за да намерите следващата дата на събитие въз основа на текущата дата.
По-долу е наборът от данни, където имам имена на събития и дати на събития.
Това, което искам, е името на следващото събитие и датата на събитието на това предстоящо събитие.
Ето формулата, която дава името на предстоящото събитие:
=INDEX($A$2:$A$11,MATCH(E1,$B$2:$B$11,1)+1)
Следната формула ще даде дати на предстоящите събития:
=INDEX($B$2:$B$11,MATCH(E1,$B$2:$B$11,1)+1)
Нека обясня как работи тази формула.
За да получи датата на събитието, функцията MATCH търси в колона B текущата дата.В този случай не търсим точно съвпадение, а приблизително съвпадение.Следователно последният параметър на функцията MATCH е 1 (открива най-голямата стойност, по-малка или равна на стойността за търсене).
Така че функцията MATCH ще върне позицията на клетката, чиято дата е по-малка или равна на текущата дата.Така че в този случай следващото събитие ще бъде в следващата клетка (тъй като списъкът е сортиран във възходящ ред).
Така че, за да получите датата на предстоящото събитие, просто добавете една към позицията на клетката, върната от функцията MATCH, и тя ще ви даде позицията на клетката на следващото събитие.
След това тази стойност се дава от функцията INDEX.
За да получите името на събитието, използвайте същата формула и променете диапазона във функцията INDEX от колона B на колона A.
Този пример ми дойде на ум, когато един приятел направи заявка.Той изброява рождените дни на всички свои приятели/роднини в колона и иска да знае, че предстои следващият рожден ден (и името на човека).
Горните три примера показват как да използвате формулата за търсене, за да намерите най-близката стойност в Excel.
