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