Поиск позиции ЧИСЛА в EXCEL с выводом значения из соседнего столбца
14 апреля 2013 г.
- Группы статей
- Поиск позиции
- Условное форматирование
Для нахождения позиции значения в столбце, с последующим выводом соответствующего значения из соседнего столбца в EXCEL, существует специальная функция ВПР() , но для ее решения можно использовать также и другие функции.
Пусть в диапазоне А4:В15 имеется таблица с перечнем товаров и их артикулами (кодами).
Задача
Требуется, введя в ячейку D4 код товара (число), вывести в другой ячейке название этого товара. Решение приведено в файле примера .
Решение
Алгоритм решения задачи следующий:
- находим в списке кодов значение, совпадающее с критерием;
- определяем номер позиции (строку) найденного значения;
- выводим значение из соседнего столбца той же строки.
Для решения этого типа задач в EXCEL существует специальная функция ВПР() , но для ее решения можно использовать и другие функции.
Решение | Если несколько значений удовл. критерию |
= ВПР($D$4;A4:B15;2;ЛОЖЬ) | берется первое сверху |
= ИНДЕКС(B4:B15; ПОИСКПОЗ($D$4;A4:A15;0);1) | берется первое сверху |
= СМЕЩ($B$3; ПОИСКПОЗ($D$4;$A$4:$A$15;0);0;1;1) | берется первое сверху |
= ДВССЫЛ(АДРЕС(НАИБОЛЬШИЙ( ЕСЛИ(($A$4:$A$15=$D$4); СТРОКА($A$4:$A$15));1);2)) | берется последнее сверху |
= ПРОСМОТР($D$4;$A$4:$A$15;$B$4:$B$15) | если столбец отсортирован по возрастанию, то берется последнее сверху, если нет, то результат непредсказуем |
= БИЗВЛЕЧЬ(A3:B15;2;F15:F16) | возвращается ошибка # ЧИСЛО ! |
Для функции ВПР() требуется, чтобы столбец, по которому производится поиск, был левее столбца, который используется для вывода. Обойти это ограничение позволяет, например, вариант с использованием функций ИНДЕКС() и ПОИСКПОЗ() . Эквивалентная формула приведена в статье о функции ВПР() (см. статью http://excel2.ru/articles/funkciya-vpr-v-ms-excel-vpr ).
Задача подразумевает, что искомое значение гарантировано будет найдено. В случае, если требуется найти ближайшее значение, читайте статью http://excel2.ru/articles/poisk-chisla-blizhayshego-k-zadannomu-sortirov...
Примечание . Как показано в статье Как EXCEL хранит дату и время , любая дата в EXCEL – это число. Следовательно, приведенное выше решение будет работать и в случае, если в столбце А будут находиться даты.
Также задача подразумевает, что диапазон поиска содержит неповторяющиеся значения. В самом деле, если критерию удовлетворяет сразу несколько значений, то из какой строки выводить соответствующее ему значение из соседнего столбца? Если все же диапазон поиска содержит повторяющиеся значения, то второй столбец из таблицы выше поясняет какое значение будет выведено (обычно возвращается первое сверху значение, удовлетворяющее критерию).
Примечание . Если диапазон поиска содержит повторяющиеся значения и требуется вернуть не одно, а все значения, удовлетворяющие критерию, то читайте статью Запрос на основе Элементов управления формы .
Совет: Если в диапазон поиска постоянно вводятся новые значения, то для исключения ввода дубликатов следует наложить определенные ограничения (см. статью Ввод неповторяющихся значений ). Для визуальной проверки наличия дубликатов можно использовать Условное форматирование (см. статью Выделение повторяющихся значений ). Для организации динамической сортировки пополняемого диапазона поиска можно использовать идеи из статьи Сортированный список .
Аналогичный поиск текстовых значений приведен в статье Поиск позиции ТЕКСТового значения с выводом соответствующего значения из соседнего столбца .
Комментарии