Поиск ближайшего значения в массиве и всех дубликатов в Excel
В данном примере Excel будем искать ячейки с наиболее близкими значениями к какому-то числу, выбранному пользователем (меньшими, большими, равными - без разницы. Важно, чтобы они были как можно ближе к искомому значению).
Как найти ближайшее большее значение по формуле в Excel
Начнем с простой таблицы, в которой имеется список имен и соответствующие им баллы.
Сразу стоит отметить что для некоторых имен число баллов повторяются.
Хотелось бы, чтобы Excel вернул значения баллов, которые являются наиболее близкими к числу, введенному в исходной ячейке G2 рабочего листа, а также и имена, соответствующие тем значениям.
По одному запросу нужно получить ближайшее значение числа баллов и соответствующее ему имя.
Поиск ближайшего значения без массива в Excel
Одним из простых способов решения проблемы является использование вспомогательного столбца. В ячейках этого столбца будут находиться абсолютные значения разности исходного числа и баллов из списка.
Разумеется, решение нашей проблемы будет найдено в строке, в которой это значение является наименьшим.
Чтобы выбрать соответствующее значение и соответствующее ему имя, достаточно использовать следующие формулы с использованием функций ИНДЕКС и ПОИСКПОЗ. Для ближайшего значения:
Для имени соответствующему ближайшему значению:
Где столбец «D» - наш вспомогательный столбец, а столбец «B» - столбец с именами. Сразу же добавлю (для ясности), что столбец «C» является столбцом со значениями баллов.
Поиск ближайшего значения в массиве Excel
Решение «хардкор» с использованием формул массива (для любителей и тех, кто просто хочет потренироваться в создании формул массива).
Поиск ближайшего значения в массиве (CTRL+SHIFT+ENTER):
Поиск соответствующего ему имени в массиве (CTRL+SHIFT+ENTER):
Повторяющиеся ближайшие значения в Excel
Два способа, которые показаны выше, возвращают только одно значение. Поэтому, когда нескольким именам соответствуют равные значения баллов, формула возвращает только первое имя из списка.
Итак, каким же образом можно заставить Excel вернуть список всех имен с интересующими нас значениями баллов при наличии дубликатов ближайших значений?
Есть два решения с использованием вспомогательного столбца. Первое без, а второе с использованием формул массива.
Сначала подготовим для себя вспомогательный столбец. Первая ячейка будет содержать формулу:
которую затем перетягиваем (копируем) в другие ячейки вспомогательного столбца.
Формула должна возвращать номер строки, в которой находится значение, наиболее близкое к искомому. В противном случае возвращает пробел.
Без использования формул массива
Вспомогательный столбец уже готов, мы можем вернуться к нашему поиску.
В первой ячейке диапазона, в котором вы хотите иметь список всех имен, введите следующую формулу:
а в ячейку ниже, которая будет возвращать следующее имя, введите формулу:
которую затем скопируйте еще ниже. В результате все выглядит более или менее так, как показано на рисунке ниже. Как видите, в результате работы указанных формул вы получаете список всех имен, которые соответствуют искомому критерию.
Поиск дублирующийся ближайших значений в массиве Excel
В завершении то же самое, но с использованием формул массива (мы используем вспомогательный столбец, описанный ранее).
Выбираем диапазон ячеек, в которых мы хотим иметь список имен (например, G15:G19) и используем формулу массива:
Формула точно вернет то, что вы ожидаете. Во всех «не соответствующих результатам» ячейках формула вернет код ошибки. При необходимости их легко удалить или поместить в аргументы функции ЕСЛИОШИБКА.
