Lost your password?


Хитрости »
Основные понятия (27)
Сводные таблицы и анализ данных (10)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (23)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (68)
Разное (43)
Баги и глюки Excel (5)

ВПР с возвратом всех значений

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

  • Вариант 1: все значения необходимо получить в одной ячейке через разделитель(скажем запятую). Такое возможно только через использование VBA(на момент написания статьи). Такую функцию я написал давно: Как сцепить несколько значений в одну ячейку по критерию? СцепитьЕсли
  • . В результате получим что-то вроде: Александров Иван,Александров Петр,Андреев Алексей.

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

Здесь надо сразу оговориться - при помощи непосредственно ВПР(VLookup) это сделать ну никак не получится. Но это можно сделать при помощи её родственников - связки ИНДЕКС(ПОИСКПОЗ)(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 KiB, 25 357 скачиваний)

  • Первое и самое главное - формула вводится в ячейку как формула массива. Это значит, что для приложенного к статье примера необходимо будет выделить ячейку 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 KiB, 25 357 скачиваний)

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


Статья помогла? Поделись ссылкой с друзьями!
  Плейлист   Видеоуроки

Поиск по меткам

Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистика
Обсуждение: 12 комментариев
  1. Павел:

    Огромное спасибо! Очень нужно! Возможно стоит дополнительные теги приписать)

  2. Андрей:

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

  3. Дмтирий:

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

  4. Елена:

    Вариант 1 очень помог! Спасибо большое! :)

  5. Елена Т.:

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

  6. Владимир:

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

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

  7. MakcYan:

    Доброго дня! Могли бы подсказать как эту формулу прописать в пользовательской функции?
    =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. Михаил:

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

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2024 Excel для всех   Войти