Функции Excel могут быть очень мощными, если вы научитесь комбинировать различные формулы.То, что когда-то казалось невозможным, может вдруг стать детской игрой.
Одним из таких примеров является поиск ближайшего значения в наборе данных Excel.
В Excel есть несколько полезных функций поиска (например, ВПР и ПОИСКПОЗ ПО ИНДЕКСУ), которые могут найти ближайший值(как показано в примере ниже).
Но самое приятное то, что вы можете комбинировать эти функции поиска с другими функциями Excel, чтобы делать гораздо больше (включая поиск ближайшего совпадения с искомым значением в несортированном списке).
В этом руководстве я покажу вам, как использовать формулу поиска, чтобы найти наиболее близкое совпадение со значением поиска в Excel.
Существует множество различных сценариев, в которых вам нужно найти ближайшее совпадение (или значение ближайшего совпадения).
Вот пример, который я расскажу в этой статье:
- Найти комиссионные ставки на основе продаж
- Найдите лучшего кандидата (на основе ближайшего опыта)
- Найдите дату следующего события
Давайте начнем!
Содержание:
Найдите комиссионные ставки (ищите ближайшую стоимость продаж)
Предположим, у вас есть набор данных, подобный приведенному ниже, в котором вы хотите найти комиссионные ставки для всех продавцов.
Комиссионные распределяются в зависимости от стоимости продажи.Это рассчитывается с помощью таблицы справа.
Например, если общий объем продаж продавца составляет 5000, комиссия составляет 0%, а если его/ее общий объем продаж составляет 15000, комиссия составляет 5%.
Чтобы получить комиссионную ставку, вам нужно найти ближайший диапазон продаж, который находится чуть ниже стоимости продажи.Например, за 15000 10,000 продаж комиссия составляет 5 25000 (или 20,000%), а за 7 XNUMX продаж комиссия составляет XNUMX XNUMX (или XNUMX%).
Чтобы найти ближайшую стоимость продажи и получить комиссию, вы можете использовать приблизительное сопоставление в ВПР.
Это может сделать следующая формула:
= ВПР (B2; $ E $ 2: $ F $ 6,2,1)
Обратите внимание, что в этой формуле последний параметр равен 1, что говорит формуле использовать приблизительный поиск.Это означает, что формула пройдет через значение продаж в столбце E и найдет значение чуть ниже искомого значения.
Затем формула ВПР даст комиссионную ставку для этого значения.
注意: Для этого нужно отсортировать данные по возрастанию.
Найдите лучшего кандидата (на основе ближайшего опыта)
В приведенном выше примере данные необходимо отсортировать в порядке возрастания.Но могут быть случаи, когда данные не отсортированы.
Итак, давайте возьмем пример и посмотрим, как мы можем использовать комбинации формул для поиска ближайшего совпадения в 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 и находите разницу между желаемым опытом и опытом, который есть у человека.Когда у вас есть все различия, вы находите наименьшее из них и получаете имя этого человека.
Это именно то, что мы делаем с этой формулой.
Позволь мне объяснить.
Искомое значение в формуле ПОИСКПОЗ — МИН(АБС(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, которая является позицией имени человека с самым близким опытом.
Затем функция ИНДЕКС использует номер задания, чтобы вернуть имя человека.
Примечание. Если есть несколько кандидатов с одинаковым минимальным опытом, приведенная выше формула даст имя первого подходящего сотрудника.
Найдите дату следующего события
Вот еще один пример, в котором вы можете использовать формулу поиска, чтобы найти следующую дату события на основе текущей даты.
Ниже приведен набор данных, в котором у меня есть названия событий и даты событий.
Что я хочу, так это название следующего события и дату этого предстоящего события.
Вот формула, дающая название предстоящему событию:
=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)
Позвольте мне объяснить, как работает эта формула.
Чтобы получить дату события, функция ПОИСКПОЗ ищет текущую дату в столбце B.В данном случае мы ищем не точное совпадение, а приблизительное.Следовательно, последний параметр функции ПОИСКПОЗ равен 1 (она находит наибольшее значение, меньшее или равное искомому значению).
Таким образом, функция ПОИСКПОЗ вернет позицию ячейки, дата которой меньше или равна текущей дате.Таким образом, в этом случае следующее событие будет в следующей ячейке (поскольку список отсортирован по возрастанию).
Итак, чтобы получить дату предстоящего события, просто добавьте единицу к позиции ячейки, возвращаемой функцией ПОИСКПОЗ, и она даст вам позицию ячейки даты следующего события.
Затем это значение задается функцией ИНДЕКС.
Чтобы получить имя события, используйте ту же формулу и измените диапазон в функции ИНДЕКС со столбца B на столбец A.
Этот пример пришел мне в голову, когда друг сделал запрос.Он перечисляет дни рождения всех своих друзей/родственников в столбце и хочет знать, что приближается следующий день рождения (и имя человека).
В приведенных выше трех примерах показано, как использовать формулу поиска для поиска ближайшего значения в Excel.
