Ответ на пост из соседней темы о ВПР по 2-м условиям.
Довольно часто я встречаю решения выполнения поиска в таблице по двум/трём условиям с применением ВПР/ГПР. Подчас решения очень интересные. Однако что ВПР, что ГПР выдают только одно значение, и решения не масштабируемы. А что делать если необходимо из массива выбрать всю строку, которая будет подчиняться набору критериев?
Для этого служит встроенный в Excel механизм работы с таблицами как с базами данных. К сожалению им мало кто пользуется, но он очень прост в освоении. В этой теме попробую про него рассказать.
Итак, есть некая таблица и необходимо из неё выбрать все строки подчиняющиеся неким условиям.
Ход поиска однотипен:
1. Столбцы исходной таблицы имеют уникальные заголовки.
2. Для выборки из данной таблицы создаём таблицу условий. При этом заголовок таблицы условий должен совпадать с названием столбца из исходной таблицы, по которому будет проходить выборка. Если выборка по одному столбцу, но по нескольким условиям, то можно написать всё в один столбец (см.скрин ниже). Если условия по нескольким столбцам - по одному условию на столбец, наименования столбцов могут повторяться.
3. Затем переходим в вкладку "Данные" - "Сортировка и фильтр" - "Дополнительно". В открывшемся окне
3.1. отмечаем "скопировать результат в новое место". В этом случае исходная таблица не изменится.
3.2. Исходный диапазон - таблица исходная, вместе с шапкой
3.3. Диапазон условий - таблица условий, вместе с шапкой
3.4. Адрес ячейки с которой будет заполняться итоговая таблица согласно выборки.
Итоговая таблица при этом никак не связана с исходной. И с ней можно проводить любые операции.
Вынос итоговой таблицы в отдельный лист с помощью меню выполнить нельзя, однако можно через макрос, если полностью указать путь с учётом имени страницы назначения :
=====
Sub Выборка()
Range("B3:B37").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("I3:I6"), _
CopyToRange:=Range("K3"), _
Unique:=False
End Sub
=====
где
Range("B3:B37") - исходная таблица
Range("I3:I6") - таблица критериев
Range("K3") - начало вывода результирующей таблицы
Соответственно можно повесить выполнением макроса на кнопку, менять условия и получать новые выборки.
Вызов функции выборки:
AdvancedFilter (Действие, CriteriaRange, CopyToRange, Уникальный)
Причём работа с таблицами как с базами на этом не ограничивается, в частности она позволяет выполнять быстрые расчёты с учётом критериев. Например находить минимальные, максимальные и средние значения только для строк удовлетворяющих критериям. Заметьте - проблемы с числом критериев нет.
Например, если я хочу просуммировать все значения из столбца "Продажи" с учётом того, что они проводились в марте, и контактов было от 35...36, то я создаю таблицу критериев (см.скрин ниже) и в любой ячейке листа формулу =БДСУММ(B6:G21;F6;B2:В3)
где
B6:G21 - Диапазон исходной таблицы, включая заголовок;
F6 - по какому столбцу буду суммировать;
B2:D3 - при каких условиях суммировать.
Да, сейчас это можно заменить на СУММЕСЛИ, но если будет больше условий? А если суммируется при сложных условиях содержания ячеек?
Насколько способ с использованием баз нагляднее, не так ли?
Варианты задания условий при этом поражают разнообразием. Не всегда можно сходу подобрать условия для СУММЕСЛИ, а тут это просто содержание одной ячейки.
Ну и естественно умные таблицы используются без проблем
==================
Рассмотрим ещё один метод поиска значения по 2-м, 3-м и более критериям без применение ВПР.
Для сокращения пишу для умной таблицы. К тому же она масштабируема, так что добавление участника не приведёт к необходимости корректировки формулы.
Допустим есть таблица исходных данных (Таблица1), в корой представлены список сотрудников (столбец Name) с датой их приёма на работу (Start) увольнения с должности (Stop) и названием должности (Role). При этом если человек принят, но ещё не уволился, то его ячейка Stop пустая. Нужно найти должность человека (F3) на какую то дату (F4). Попробуйте это через ВПР прописать ради интереса. А вот так делается без ВПР:
В ячейке F6 собственно формула поиска.
=ЕСЛИОШИБКА(ПРОСМОТР(2;1/(Таблица1[Name]=F3)/(Таблица1[Start]<=F4)/((Таблица1[Stop]="")+(Таблица1[Stop]>=F4));Таблица1[Role]);"нет данных")
Разберём структуру поиска и задания условий:
Таблица1[Name]=F3 - совпадение имени
Таблица1[Start]<=F4 - дата зачисления на работу меньше даты поиска
(Таблица1[Stop]="")+(Таблица1[Stop]>=F4) - дата поиска или меньше даты увольнения или дата увольнения пустая.
Таблица1[Role] - вывод ячейки для которой все три условия истина.
Как видно формула легко масштабируется под любое количество условий.
Ну вот как то так.
Этим постом я хотел показать, что применение ВПР(ГПР) не всегда оправдано (хотя несомненно знание этих функций обязательно), и есть более простые и лёгкие способы.
