Все, кто сталкивался с функцией ВПР знают, что она умеет возвращать исключительно одно значений - первое найденное. Но что делать, когда надо видеть все значения? К примеру имеется такая таблица:
Таблица
И из этой таблицы необходимо получить фамилии(поле ФИО) всех сотрудников из отдела Сбыта:
Таблица
Тут два варианта:

  • Вариант 1: все значения необходимо получить в одной ячейке через разделитель(скажем запятую). Как и при помощи каких функций и инструментов это можно сделать я описывал детально в статье: Как сцепить несколько значений в одну ячейку по критерию? СцепитьЕсли

    • . В результате получим что-то вроде: Александров Иван,Александров Петр,Андреев Алексей.

  • Вариант 2: каждое значение должно быть в отдельной ячейке. Этот вариант мы и рассмотрим более подробно в данной статье. Причем решать задачу будем без использования макросов - только встроенными функциями

Здесь надо сразу оговориться - при помощи непосредственно ВПР(VLookup) это сделать ну никак не получится.
 
Решение для офиса 365 по подписке
Решение очень простое - использовать функцию динамических массивов ФИЛЬТР(FILTER):
=ФИЛЬТР(A12:A111;C12:C111=I12;"")
=FILTER(A12:A111,C12:C111=I12)
Аргументы функции:

  • A12:A111 - диапазон, в котором записаны все фамилии
  • C12:C111 - диапазон с названиями отделов
  • I12 - критерий. Значение, на основании которого необходимо отбирать значения. В данном случае значение просматривается в столбце с отделами(C12:C111)

    • Работа функции предельно проста: первым аргументом мы указываем столбец или таблицу, которые необходимо отфильтровать на основании условия. Само условие задается вторым аргументом в виде сравнения, примерно как в функции ЕСЛИ(IF): C12:C111=I12. Если значение в столбце С равно значению ячейки I12, то эта строка из диапазона A12:A111 будет отобрана в итоговый массив выводимых на лист значений.

 

Решение универсальное - для всех версий Excel
А тем, у кого не самая "продвинутая" версия Excel, придется делать все это при помощи родственников функции ВПР - связки ИНДЕКС(ПОИСКПОЗ)(INDEX(MATCH)). Плюс еще пара функций для более красивого результата. В сборе функция выглядит так:
=ИНДЕКС($A$12:$G$111;НАИМЕНЬШИЙ(ЕСЛИ($I$12=$C$12:$C$111;СТРОКА($C$12:$C$111)-11);СТРОКА(A1));1)
=INDEX($A$12:$G$111;SMALL(IF($I$12=$C$12:$C$111;ROW($C$12:$C$111)-11);ROW(A1));1)
где:

  • $A$12:$G$111 - таблица с ФИО и всеми данными
  • $I$12 - ячейка с названием отдела
  • $C$12:$C$111 - столбец с названиями отделов в таблице $A$12:$G$111

А теперь начнем разбирать формулу по кусочкам
Предлагаю сразу скачать пример файла с данными и формулами - тогда понять будет проще:

  Tips_All_VlookupAllVals.xls (84,5 КиБ, 27 125 скачиваний)

  • Первое и самое главное - формула вводится в ячейку как формула массива. Это значит, что для приложенного к статье примера необходимо будет выделить ячейку J12, записать в неё формулу и завершить ввод нажатием сразу трех клавиш: Ctrl+Shift+Enter. После этого скопировать ячейку, выделить диапазон J13:J39 и вставить туда скопированную ячейку.
  • За отбор ФИО указанного отдела отвечает этот кусок: ЕСЛИ($I$12=$C$12:$C$111;СТРОКА($C$12:$C$111)-11)
    IF($I$12=$C$12:$C$111;ROW($C$12:$C$111)-11)
    Как видно здесь идет сравнение отдела, записанного в $I$12 со всеми отделами в таблице: $C$12:$C$111. Если отдел совпадает, то функция возвращает номер строки СТРОКА($C$12:$C$111). Но нам нужен не номер строки на листе, а номер строки внутри таблицы(потому что в формулу ИНДЕКС(INDEX) у нас передан диапазон $A$12:$G$111 и если передать в его второй аргумент(номер_строки) 14, то получим не Александров Иван, а Андреев Олег). Поэтому отнимаем от номера строки 11, чтобы было возвращено число 3(а не 14). Т.к. формула записана формулой массива - ЕСЛИ(IF) возвращает массив из номеров строк и логического ЛОЖЬ(FALSE)(если отдел не совпадает):
    шаг 1: ЕСЛИ({ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ};{12:13:14:15:16:17:18:19:20}-11)
    шаг 2: ЕСЛИ({ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ};{1:2:3:4:5:6:7:8:9})
    шаг 3: {ЛОЖЬ:ЛОЖЬ:3:ЛОЖЬ:ЛОЖЬ:6:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ}
  • Т.к. ЕСЛИ(IF) в данном случае возвращает массив значений, включая ненужные нам ЛОЖЬ(FALSE) - применяем НАИМЕНЬШИЙ(SMALL), которая будет брать только числа (сначала 3, затем 6 и т.д.), а логические значения ЛОЖЬ(FALSE) будет игнорировать:
    НАИМЕНЬШИЙ({ЛОЖЬ:ЛОЖЬ:3:ЛОЖЬ:ЛОЖЬ:6:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ};СТРОКА(A1))
    SMALL({ЛОЖЬ:ЛОЖЬ:3:ЛОЖЬ:ЛОЖЬ:6:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ};ROW(A1))
    Функция НАИМЕНЬШИЙ(SMALL) отбирает из указанного массива чисел наименьшее. При этом можно отобрать по рангу - первое наименьшее, второе наименьшее, третье наименьшее и т.д.

    1. с первым аргументом разобрались - это уже отобранные записи, где число - строка в таблице с нужной ФИО и ненужные нам логические ЛОЖЬ(FALSE).
    2. второй аргумент СТРОКА(A1) для каждой следующей строки будет меняться. В приложенном примере первая ячейка с формулой - J12. В ней СТРОКА(A1) равна 1, т.е. НАИМЕНЬШИЙ(SMALL) вернет первое наименьшее из массива чисел - 3. В ячейке J13 это уже будет СТРОКА(A2) и она равна 2, а значит НАИМЕНЬШИЙ(SMALL) вернет второе наименьшее из массива чисел - 6. И т.д. Когда все числа закончатся - НАИМЕНЬШИЙ(SMALL) вернет значение ошибки #ЧИСЛО!(#NUM!)
      ячейка J12
      шаг 1: НАИМЕНЬШИЙ({ЛОЖЬ:ЛОЖЬ:3:ЛОЖЬ:ЛОЖЬ:6:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ};СТРОКА(A1))
      шаг 2: НАИМЕНЬШИЙ({ЛОЖЬ:ЛОЖЬ:3:ЛОЖЬ:ЛОЖЬ:6:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ};1)
      шаг 3: НАИМЕНЬШИЙ(3)
      ячейка J13
      шаг 1: НАИМЕНЬШИЙ({ЛОЖЬ:ЛОЖЬ:3:ЛОЖЬ:ЛОЖЬ:6:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ};СТРОКА(A2))
      шаг 2: НАИМЕНЬШИЙ({ЛОЖЬ:ЛОЖЬ:3:ЛОЖЬ:ЛОЖЬ:6:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ};2)
      шаг 3: НАИМЕНЬШИЙ(6)
  • И последний штрих - все это передается в функцию ИНДЕКС(INDEX). Для ячейки J12 это будет значение ячейки, расположенной на пересечении 3-й строки и 1-го столбца диапазона $A$12:$G$111. На листе это ячейка A14. Т.е. третья сверху ячейка столбца А в диапазоне $A$12:$G$111 - Александров Иван.

    ячейка J12
    шаг 1: =ИНДЕКС($A$12:$G$111;НАИМЕНЬШИЙ(3);1)
    шаг 2: =ИНДЕКС($A$12:$G$111;3;1)
    шаг 3: Александров Иван
    ячейка J13
    шаг 1: =ИНДЕКС($A$12:$G$111;НАИМЕНЬШИЙ(6);1)
    шаг 2: =ИНДЕКС($A$12:$G$111;6;1)
    шаг 3: Александров Петр

И в довершение неплохо бы убрать ошибку в случае, если ни одно значение не соответствует критерию или значений больше, чем ячеек с формулами. Более подробно подобное решение я описывал в этой статье: Как в ячейке с формулой вместо ошибки показать 0
Для всех версий Excel:
=ЕСЛИ(ЕОШ(ИНДЕКС($A$12:$G$111;НАИМЕНЬШИЙ(ЕСЛИ($I$12=$C$12:$C$111;СТРОКА($C$12:$C$111)-11);СТРОКА(A1));1));"";ИНДЕКС($A$12:$G$111;НАИМЕНЬШИЙ(ЕСЛИ($I$12=$C$12:$C$111;СТРОКА($C$12:$C$111)-11);СТРОКА(A1));1))
=IF(ISERR(INDEX($A$12:$G$111;SMALL(IF($I$12=$C$12:$C$111;ROW($C$12:$C$111)-11);ROW(A1));1));"";INDEX($A$12:$G$111;SMALL(IF($I$12=$C$12:$C$111;ROW($C$12:$C$111)-11);ROW(A1));1))
Для Excel 2007 и выше:
=ЕСЛИОШИБКА(ИНДЕКС($A$12:$G$111;НАИМЕНЬШИЙ(ЕСЛИ($I$12=$C$12:$C$111;СТРОКА($C$12:$C$111)-11);СТРОКА(A1));1);"")
=IFERROR(INDEX($A$12:$G$111;SMALL(IF($I$12=$C$12:$C$111;ROW($C$12:$C$111)-11);ROW(A1));1);"")
Плюс к этому в приложенном к статье файле я привел формулу, которая возвращает список всех сотрудников выбранного отдела без повторений и формулу с возможностью просто поменять заголовок в результирующем столбце(в файле это столбец K, ячейка K11) на любой из исходной таблицы и данные будут отображены из этого столбца.
Например, если вместо столбца ФИО записать в K11 Адрес - будут отображены все адреса сотрудников выбранного отдела. Если записать Телефон - все телефоны сотрудников выбранного отдела.

Скачать пример:

  Tips_All_VlookupAllVals.xls (84,5 КиБ, 27 125 скачиваний)

Так же см.:
Как найти значение в другой таблице или сила ВПР
ВПР с поиском по нескольким листам
ВПР_МН

Loading

12 комментариев

  2. Добрый день!
    А можно ли в указанной формуле использовать несколько условий для отбора?
    Скажем, нужно отобрать фамилии(поле ФИО) всех сотрудников из отдела Сбыта у которых оклад 16000р.?

  3. Добрый день. Не могли бы вы прокомментировать формулу, в которой избавляемся от повторов? Что значит <code class="ЕСЛИ(($I$12=$C$12:$C$111)*(СЧЁТЕСЛИ($L$11:L13;$A$12:$A$111)=0"?

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

  6. Попробовал, пока не понял/не понравилось, что если сначала протянуть формулу сверху вниз, взяв данные с другого листа (кстати, работает), но потом - дозаполнить таблицу данных, то есть - добавить еще одну строку, где критерий отбора оказывается выполненным - у меня эти данные в итоговый вывод не добавились, словно я ничего не добавлял. Или есть какая-то хитрость для обновления? В остальном работа функции понравилась.

    1. Владимир, думаю, Вам лучше обратиться на форум, создав новую тему и приложив файл-пример данных, чтобы можно было понять проблему. По одному описанию невозможно понять, почему формула не видит новые данные. Возможно, неверно указан диапазон поиска, возможно, пересчет формул отключен, а может и иная причина.

  7. Доброго дня! Могли бы подсказать как эту формулу прописать в пользовательской функции?
    =INDEX($A$12:$G$111;SMALL(IF($I$12=$C$12:$C$111;ROW($C$12:$C$111)-11);ROW(A1));1)
    "Индекс" и "наименьший" - превратил в Application.WorksheetFunction.INDEX/SMALL
    А вот "если" и "строка" - не могу понять как преобразовать.

  8. Есть третий вариант для новых версий Ексель - функции Объединить + Фильтр.
    Фильтром получаем список записей, и объединяем в строку с заданным разделителем.

Добавить комментарий

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.