Формула логического отношения

Добавлено: 19.10.2017, 01:57 / Просмотров: 65374

формула логического отношения Хитрости »

11 Июль 2016       Дмитрий       10098 просмотров

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

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

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

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

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

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

  Tips_All_VlookupAllVals.xls (84,5 KiB, 1 667 скачиваний)

  • Первое и самое главное - формула вводится в ячейку как формула массива. Это значит, что для приложенного к статье примера необходимо будет выделить ячейку J12, записать в неё формулу и завершить ввод нажатием сразу трех клавиш: Ctrl+Shift+Enter. После этого скопировать ячейку, выделить диапазон J13:J39 и вставить туда скопированную ячейку.
  • За отбор ФИО указанного отдела отвечает этот кусок: ЕСЛИ($I=$C:$C1;СТРОКА($C:$C1)-11)
    IF($I=$C:$C1;ROW($C:$C1)-11)
    Как видно здесь идет сравнение отдела, записанного в $I со всеми отделами в таблице: $C:$C1. Если отдел совпадает, то функция возвращает номер строки СТРОКА($C:$C1). Но нам нужен не номер строки на листе, а номер строки внутри таблицы(потому что в формулу ИНДЕКС(INDEX) у нас передан диапазон $A:$G1 и если передать в его второй аргумент(номер_строки) 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:$G1. На листе это ячейка A14. Т.е. третья сверху ячейка столбца А в диапазоне $A:$G1 - Александров Иван.
    ячейка J12
    шаг 1: =ИНДЕКС($A:$G1;НАИМЕНЬШИЙ(3);1)
    шаг 2: =ИНДЕКС($A:$G1;3;1)
    шаг 3: Александров Иван
    ячейка J13
    шаг 1: =ИНДЕКС($A:$G1;НАИМЕНЬШИЙ(6);1)
    шаг 2: =ИНДЕКС($A:$G1;6;1)
    шаг 3: Александров Петр

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

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

  Tips_All_VlookupAllVals.xls (84,5 KiB, 1 667 скачиваний)

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

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

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

Access Multex Outlook Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Настройка Печать Поиск данных Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx анализ данных ссылки


Закрыть ... [X]

Что такое вода? Вода в природе. Формула воды Родителей не выбирают пословица


Формула логического отношения ВПР с возвратом всех значений Excel для всех
Формула логического отношения Число судьбы и Таро - ключи к личности
Формула логического отношения Выработка
Формула логического отношения Cached
Формула логического отношения DollClothes Одежда для кукол barbie своими руками
Формула логического отношения Выкройка женских джинсов от Анастасии Корфиати