Поиск позиции ТЕКСТа в EXCEL с выводом значения из соседнего столбца

history

Для нахождения позиции значения в столбце, с последующим выводом соответствующего значения из соседнего столбца в 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)

если столбец отсортирован по возрастанию, то берется последнее сверху, если нет, то результат непредсказуем

= СУММПРОИЗВ((A4:A15=D4)*(B4:B15))

соответствующие значения суммируются

= СУММЕСЛИ(A4:A15;D4;B4:B15)

соответствующие значения суммируются

= БИЗВЛЕЧЬ(A3:B15;2;F15:F16)

возвращается ошибка # ЧИСЛО !

Для функции ВПР() требуется, чтобы столбец, по которому производится поиск, был левее столбца, который используется для вывода. Обойти это ограничение позволяет, например, вариант с использованием функций ИНДЕКС() и ПОИСКПОЗ() . Эквивалентная формула приведена в статье о функции ВПР() .

Задача подразумевает, что диапазон поиска содержит неповторяющиеся значения. В самом деле, если критерию удовлетворяет сразу несколько значений, то из какой строки выводить соответствующее ему значение из соседнего столбца? Если все же диапазон поиска содержит повторяющиеся значения, то второй столбец из таблицы выше поясняет какое значение будет выведено (обычно возвращается первое значение, удовлетворяющее критерию).

Если диапазон поиска содержит повторяющиеся значения и требуется вернуть не одно, а все значения, удовлетворяющие критерию, то читайте статью Запрос на основе Элементов управления формы .

Совет : Если в диапазон поиска постоянно вводятся новые значения, то для исключения ввода дубликатов следует наложить определенные ограничения (см. статью Ввод неповторяющихся значений ). Для визуальной проверки наличия дубликатов можно использовать Условное форматирование (см. статью Выделение повторяющихся значений ).

Для организации динамической сортировки пополняемого диапазона поиска можно использовать идеи из статьи Сортированный список .


Комментарии

Только для авторизованных пользователей

Аноним, 28 апреля 2017 г.
Подскажите как найти ячейку в таблице с заданным текстом а потом вывести номер строи и столбца таблицы, в которой эта ячейка находится?
Михаил, 29 апреля 2017 г.
Обычно ищут только по одному столбцу. В этом случае решение зависит от того, что содержится в столбце. Если текстовые значения в столбце без повторов, то используйте функцию ПОИСКПОЗ(). см. статью http://excel2.ru/articles/funkciya-poiskpoz-v-ms-excel-poiskpoz Если повторы есть, то это значит, что потребуется вывести сразу несколько значений. Можно использовать Автофильтр http://excel2.ru/articles/avtofiltr-v-ms-excel Расширенный фильтр http://excel2.ru/articles/rasshirennyy-filtr-v-ms-excel Предварительно нужно сделать столбец с номерами строк, используйте функцию СТРОКА() Если нужно сделать формулами, то лучше использовать формулы массива http://excel2.ru/articles/formuly-massiva-v-ms-excel-znakomstvo Например, =ЕСЛИ(B69:B71=C68;СТРОКА();"") вернет номера строк ячеек диапазона B69:B71, если в нем содержатся значения из ячейки C68. Потом этот массив с номерами строк можно отсортировать, убрать лишние значения "" и вывести в диапазон. Также можно посмотреть примеры из этих разделов: http://excel2.ru/gruppy-statey/poisk-pozicii http://excel2.ru/gruppy-statey/poisk-tekstovyh-znacheniy
Аноним, 18 сентября 2017 г.
Пожалуйста помогите, у меня взрывается мозг, не могу найти формулу) В общем вводные такие: на одном листе 2 солбца - в ондом столбце код, в другом наименование, соответствующее коду. на втором листе только эти коды без наименования и они расположены в по горизонтали.!.... как перенести наименования, чтобы они соответствовали коду? огромное спасибо заранее.
(только для авторизованных пользователей)

© Copyright 2013 - 2021 Excel2.ru. All Rights Reserved