Намерете най -близкото съвпадение в Excel с помощта на формули

Функциите на Excel могат да бъдат изключително мощни, ако се справите с комбинирането на различни формули. Нещата, които може би изглеждаха невъзможни, изведнъж ще започнат да изглеждат като детска игра.

Един такъв пример е да се намери най -близкото съвпадение на стойност за търсене в набор от данни в Excel.

В Excel има няколко полезни функции за търсене (като VLOOKUP & INDEX MATCH), които могат да намерят най -близкото съвпадение в няколко прости случая (както ще покажа с примери по -долу).

Но най -хубавото е, че можете да комбинирате тези функции за търсене с други функции на Excel, за да направите много повече (включително намиране на най -близкото съвпадение на стойност за търсене в несортиран списък).

В този урок ще ви покажа как намирате най -близкото съвпадение на стойност за търсене в Excel с формули за търсене.

Намерете най -близкото съвпадение в Excel

Може да има много различни сценарии, при които трябва да потърсите най -близкото съвпадение (или най -близката съвпадаща стойност).

По -долу са примерите, които ще разгледам в тази статия:

1. Намерете размера на комисионната въз основа на продажбите
2. Намерете най -добрия кандидат (въз основа на най -близкия опит)
3. Намиране на следващата дата на събитието

Да започваме!

Щракнете тук, за да изтеглите примерния файл

Намерете комисионна (търсите най -близката стойност на продажбите)

Да предположим, че имате набор от данни, както е показано по -долу, където искате да намерите комисионните за целия персонал по продажбите.

Комисионната се определя въз основа на стойността на продажбата. И това се изчислява с помощта на таблицата вдясно.

Например, ако продавач направи общите продажби от 5000, тогава комисионната е 0%, а ако той/тя направи общите продажби от 15000, тогава комисионната е 5%.

За да получите процента на комисионна, трябва да намерите най -близкия диапазон на продажба, малко по -нисък от стойността на продажбата. Например, при стойност от 15000 продажби, комисионната ще бъде 10 000 (което е 5%), а при продажба от 25 000, комисионната ще бъде 20 000 (което е 7%).

За да намерите най -близката стойност на продажбата и да получите комисионната, можете да използвате приблизителното съвпадение във VLOOKUP.

Формулата по -долу ще направи това:

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

Обърнете внимание, че в тази формула последният аргумент е 1, който казва на формулата да използва приблизително търсене. Това означава, че формулата ще премине през стойностите на продажбите в колона Е и ще намери стойността, която е просто по -ниска от стойността за търсене.

Тогава формулата 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))

Номерът в тази формула е да промените масива за търсене и стойността за търсене, за да намерите минималната разлика в опита в необходимите и действителните стойности.

Нека първо разберем как бихте го направили ръчно (и тогава ще обясня как работи тази формула).

Когато правите това ръчно, ще преминете през всяка клетка в колона В и ще намерите разликата в опита между това, което се изисква, и това, което човек има. След като имате всички разлики, ще намерите тази, която е минимална, и ще извлечете името на този човек.

Точно това правим с тази формула.

Нека обясня.

Стойността за търсене във формулата 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 за връщане на името на лицето.

Забележка: В случай, че има няколко кандидати с еднакъв минимален опит, горната формула ще даде името на първия съвпадащ служител.

Намерете следващата дата на събитието

Това е друг пример, при който можете да използвате формули за търсене, за да намерите следващата дата на събитие въз основа на текущата дата.

По -долу е наборът от данни, в който има имена на събития и дати на събитието.

Това, което искам, е името на следващото събитие и датата на събитието за това предстоящо събитие.

По -долу е формулата, която ще даде името на предстоящото събитие:

= ИНДЕКС ($ A $ 2: $ A $ 11, МАТЧ (E1, $ B $ 2: $ B $ 11,1) +1)

И по -долу формулата ще даде датата на предстоящото събитие:

= ИНДЕКС ($ B $ 2: $ B $ 11, МАТЧ (E1, $ B $ 2: $ B $ 11,1) +1)

Позволете ми да обясня как работи тази формула.

За да получи датата на събитието, функцията MATCH търси текущата дата в колона B. В този случай не търсим точно съвпадение, а приблизително. И следователно последният аргумент на функцията MATCH е 1 (който намира най -голямата стойност, която е по -малка или равна на стойността за търсене).

Така че функцията MATCH ще върне позицията на клетката, която има датата, която е просто по -малка или равна на текущата дата. Така че следващото събитие, в този случай, ще бъде в следващата клетка (тъй като списъкът е сортиран във възходящ ред).

Така че, за да получите датата на предстоящото събитие, просто добавете една към позицията на клетката, върната от функцията MATCH, и тя ще ви даде позицията на клетката на следващата дата на събитието.

След това тази стойност се дава от функцията INDEX.

За да получите името на събитието, се използва същата формула и диапазонът във функцията INDEX се променя от колона B на колона A.

Щракнете тук, за да изтеглите примерния файл

Идеята за този пример ми дойде, когато приятел се обърна с молба. Той имаше списък с всички рождени дни на своите приятели/роднини в колона и искаше да знае следващия рожден ден (и името на човека).

Това са три примера, които показват как да намерите най -близката съвпадаща стойност в Excel с помощта на формули за търсене.

Може да харесате и следните съвети/уроци по Excel

• Вземете последния номер от списък с помощта на функцията VLOOKUP.
• Вземете множество стойности за търсене без повторение в една клетка.
• VLOOKUP Vs. ИНДЕКС/МАТЧ
• ИНДЕКСЕН СЪВМЕСТ НА Excel
• Намерете последната поява на стойност за търсене списък в Excel
• Намерете и премахнете дубликати в Excel
• Условно форматиране.