Формула ИНДЕКС и ПОИСКПОЗ с несколькими условиями поиска в Excel

Двухмерная таблица — это прямоугольный диапазон ячеек, то есть неразрывный диапазон состоящий из нескольких строк и столбцов. Для выборки значений из двухмерных таблиц легко и удобно использовать эффективную формулу из комбинации функции ИНДЕКС и ПОИСКПОЗ. Главным недостатком такой формулы является тот факт, то ее можно применять только для двухмерных прямоугольных таблиц в неразрывном диапазоне ячеек. Но на своем поле эта формула чувствует себя как рыба в воде и создает шикарные инструменты для поиска по нескольким условиям пользователя.

Выборка значений формулой ИНДЕКС и ПОИСКПОЗ с несколькими условиями

Ниже на рисунке представленная таблица с данными о продажах по регионам за 4 года. Каждая строка представляет собой отдельную область региона, а каждый столбец – отдельный год. Допустим пользователь должен по двум условиям сделать выборку значений из таблицы:

1. Выбрать регион.
2. Указать год.

В результате должен получить значение ячейки из соответственной строки и столбца таблицы. Как в таблице Пифагора:

Большинству пользователей Excel хорошо известна формула из функций ИНЕДКС и ПОИСКПОЗ. В отличие от других формул, здесь используется сразу две функции ПОИСКПОЗ во втором и третьем аргументе функции ИНДЕКС. Так как поиск выполняется по двум условиям. В третьем аргументе «Номер столбца» функции ИНДЕКС нет постоянного числа (константы), а вместо него функция ПОИСКПОЗ, которая динамически изменяет значение.

Поисковая функция ПОИСКПОЗ возвращает позицию найденного значения в списке. На рисунке сейчас выбран регион «Северный», значит функция возвращает значение 3, так как этот регион находится на третьем месте в списке. Это же число на данный момент является значением второго аргумента функции ИНДЕКС. Год 2011 найден в строке заголовка таблицы. Так как это вторая позиция в списке, то функция ПОИСКПОЗ возвращает число 2 – для третьего аргумента. Функция ИНДЕКС на основе чисел 3 и 2 возвращаемых через функцию ПОИСКПОЗ возвращает соответственное значение указанным критериям выборки пользователем.



Самый простой способ выборки значений по нескольким условиям в Excel

Но что, если все несколько условий должны искать только по вертикали в столбцах таблицы?

Самый простой способ использовать несколько условий для функции ИНДЕКС - это в параметрах функции ПОИСКПОЗ (MATCH) использовать сцепление диапазонов с помощью функции СЦЕПИТЬ или просто символ «&». Как для первого аргумента функции ПОИСКПОЗ (MATCH), так и для второго:

Этот же метод работает и пре горизонтальном поиске значений. Просто во втором аргументе функции ПОИСКПОЗ мы указываем диапазоны не для столбцов, а для строк.

Альтернативная формула для ИНДЕКС и ПОИСКПОЗ по нескольким условиям

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

1. Указаны регион и год (как в предыдущем примере).
2. Указывать только регион.
3. Указывать только год.
4. Вообще ничего не указывать ни одного критерия выборки.

Теперь будет выполняться новая формула ИНДЕКС и ПОИСКПОЗ с несколькими условиями. Измененная формула и все равно должна предоставлять правильные итоговые результаты ни в чем не ограничивая своего пользователя.

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

Скачать пример ИНДЕКС и ПОИСКПОЗ с несколькими условиями поиска в Excel

Общая структура модифицированной формулы такая же, как и в предыдущем примере. Изменено только несколько деталей. Диапазон, определенный функцией ИНДЕКС, теперь охватывает и строку листа №9 и столбец F. Так же модифицированы обе функции ПОИСКПОЗ и дополнительно расположены в аргументах функций ЕСЛИОШИБКА. Эта же функция в формуле позволяет возвращать общую сумму чисел по строкам или по столбцам благодаря охвату итоговых значений в строке B9:F9 и в столбце F3:F9.

Заменителем в функции ЕСЛИОШИБКА выступает функция СЧЕТЗ. Данная функция позволяет посчитать количество непустых ячеек, которые содержат как числа, так и текстовое содержимое. То есть возвращает номер последней строки или столбца в диапазоне таблицы. В принципе можно обойтись и без этой функции вписав неизменяемые числа количества строк и столбцов вручную. Но если в будущем планируется добавлять в таблицу новые строки и столбцы формула будет выдавать неточные итоговые результаты. А так благодаря функции СЧЕТЗ формула будет сама динамически подсчитывать общее количество строк и столбцов при заполнении дополнительными данными таблицы отчета по продажам.

На рисунке изображена та же таблица, но пользователь не указал критерий выборки «Год» в ячейке B12. Так как заголовки строк и столбцов не содержат пустых ячеек старая формула возвращает ошибку с кодом #Н/Д! В тоже время в новой измененной формуле контроль над ситуацией принимает функция ЕСЛИОШИБКА и возвращает значение из своего второго аргумента «Значение если ошибка». Таким способом в функцию ИНДЕКС просто передается номер последнего столбца. Если же не будет указан регион, а год будет указан функция ИНДЕКС будет принимать от функции ЕСЛИОШИБКА номер последней строки в исходной таблице и отображать содержимое ячейки F7 с итоговой суммой.