✅ Знайдіть найближчу відповідність у Excel за допомогою формул

Зміст

Знайдіть найближчу відповідність в Excel

Функції Excel можуть бути надзвичайно потужними, якщо вам вдасться поєднати різні формули. Те, що могло здатися неможливим, раптом почне виглядати як дитяча гра.

Одним з таких прикладів є пошук найближчого збігу значення пошуку в наборі даних у Excel.

У Excel є кілька корисних функцій пошуку (наприклад, VLOOKUP & INDEX MATCH), які можуть знайти найближчу відповідність у кількох простих випадках (як я покажу з прикладами нижче).

Але найкраще те, що ви можете поєднати ці функції пошуку з іншими функціями Excel, щоб зробити набагато більше (включаючи пошук найближчого збігу значення пошуку в несортованому списку).

У цьому підручнику я покажу вам, як ви знаходите найближчу відповідність значення пошуку в Excel за допомогою формул пошуку.

Знайдіть найближчу відповідність в Excel

Може бути багато різних сценаріїв, коли вам потрібно шукати найближчу відповідність (або найближче відповідне значення).

Нижче наведено приклади, які я розгляну в цій статті:

Знайдіть ставку комісії на основі продажів
Знайдіть найкращого кандидата (на основі найближчого досвіду)
Знайдіть дату наступної події

Давайте розпочнемо!

Натисніть тут, щоб завантажити файл прикладу

Знайти ставку комісії (шукаю найближчу вартість продажів)

Припустимо, у вас є набір даних, як показано нижче, де ви хочете знайти ставки комісійних для всього торгового персоналу.

Комісія призначається виходячи з вартості продажу. І це обчислюється за допомогою таблиці праворуч.

Наприклад, якщо продавець здійснює загальний обсяг продажів 5000, то комісія становить 0%, а якщо він/вона здійснює загальний обсяг продажів 15000, то комісія складає 5%.

Щоб отримати комісійну ставку, вам потрібно знайти найближчий діапазон продажів трохи нижче, ніж вартість продажу. Наприклад, для вартості продажу 15000, комісія складе 10000 (що становить 5%), а для вартості продажу 25000, комісія складе 20 000 (що становить 7%).

Щоб знайти найближчу вартість продажу та отримати комісійну ставку, ви можете використати приблизну відповідність у VLOOKUP.

Формула нижче зробить це:

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

Зауважте, що у цій формулі останній аргумент дорівнює 1, який вказує формулі використовувати приблизний пошук. Це означає, що формула пройде через значення продажів у стовпці E і знайде значення, яке трохи нижче від вартості пошуку.

Тоді формула VLOOKUP дасть комісійну ставку для цього значення.

Примітка: Щоб це працювало, вам потрібно сортувати дані у порядку зростання.

Натисніть тут, щоб завантажити файл прикладу

Знайдіть найкращого кандидата (на основі найближчого досвіду)

У наведеному вище прикладі дані потрібно сортувати за зростанням. Але можуть бути випадки, коли дані не сортуються.

Тож давайте розглянемо приклад і подивимося, як ми можемо знайти найближчу відповідність у Excel за допомогою комбінації формул.

Нижче наведено зразок набору даних, де мені потрібно знайти ім’я працівника, який має досвід роботи, найближчий до бажаного значення. Бажана величина в цьому випадку через 2,5 роки.

Зауважте, що дані не сортуються. Крім того, найближчий досвід може бути меншим або більшим, ніж досвід. Наприклад, 2 роки та 3 роки однаково близькі (різниця 0,5 року).

Нижче наведена формула, яка дасть нам результат:

= ІНДЕКС ($ A $ 2: $ A $ 15, МАТЧ (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