Обычно формула ИНДЕКС ПОИСКПОЗ используется с одним заданным критерием и поиском в диапазоне из одного столбца. Без объединения значений во вспомогательном столбце или в самой формуле невозможно представить поиск по более чем одному критерию.
В одной из предыдущих статей мы знакомились с таким понятием в Excel как формула массива. В этой же статье мы рассмотрим как с помощью формулы массива можно обеспечить работу формулы ИНДЕКС ПОИСКПОЗ более чем по одному критерию и в более широком диапазоне поиска.
ИНДЕКС ПОИСКПОЗ формула массива
Эта формула позволяет обойти ограничение в один критерий, используя логическую структуру для создания массива единиц и нулей для представления строк, соответствующих всем трем критериям, а затем с помощью ПОИСКПОЗ() для сопоставления с первой найденной единицей.
Разберем сначала для наглядности на основе следующего примера.
На основе имеющихся данных нам требуется найти какой составил объем продаж кофе в феврале по направлению Москва?
Наверняка сразу же приходит в голову создать еще один промежуточный столбец в основной таблице, в котором через амперсанд или формулу СЦЕПИТЬ() объединить искомые критерии в один, а затем использовать все тот же амперсанд или формулу СЦЕПИТЬ() в части формулы ПОИСКПОЗ().
Но зачем нам лишние действия, лишние строки и столбцы в таблице. Используем следующую формулу
{=ИНДЕКС(A2:D13;ПОИСКПОЗ(1;(G1=A2:A13)*(G2=B2:B13)*(G3=C2:C13);0);4)}
Помним, что это формула массива и вводить ее надо через сочетание клавиш Ctrl+Shift+Enter
В результате такой простой операцией мы получим результат. Да, оказывается все просто.
Теперь давайте разберемся, что же мы сделали и как это работает?
И так, общая формула:
{=ИНДЕКС(диапазон A2:D13, ПОИСКПОЗ(1,(G2=диапазон1 A2:A13 ) * (G3 =диапазон2 B2:B13) * ( G3=диапазон3 C2:C13); тип сопоставления точное 0); номер столбца 4)}
Самое интересное в этой формуле это часть с ПОИСКПОЗ(), где в качестве искомого значения для поиска мы используем 1, а в качестве критерия для поиска используем логическую структуру для создания массива единиц и нулей для представления строк, соответствующих всем трем критериям и дальнейшего сопоставления с первой найденной единицей. Временный массив единиц и нулей создается с помощью этого фрагмента:
(G1=A2:A13)*(G2=B2:B13)*(G3=C2:C13)
Здесь мы сравниваем элемент в G1 "Направление" со всеми элементами в одноименном столбце A2:A13 "Направление", G2 "Месяц" со всеми элементами в одноименном столбце B2:B13 "Месяц" и G3 "Продукция" со всеми элементами в одноименном столбце С2:С13 "Продукция". В результате мы получим три массива ИСТИНА / ЛОЖЬ:
{ИСТИНА:ИСТИНА:ИСТИНА:ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ}*{ЛОЖЬ:ЛОЖЬ:ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ}*{ИСТИНА:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ИСТИНА:ЛОЖЬ}
Математическая операция (умножение) преобразует значения ИСТИНА ЛОЖЬ в единицы и нули:
{1:1:1:1:1:1:0:0:0:0:0:0}*{0:0:1:1:0:0:0:0:1:1:0:0}*{1:0:1:0:1:0:1:0:1:0:1:0}
После перемножения соответствующих друг другу единиц и нулей у нас получится вот такой единственный массив с единственной единицей:
{0:0:1:0:0:0:0:0:0:0:0:0}
Можете перепроверить сами умножив между собой первые значения трех массивов, затем вторые значения и так далее.
Невооруженным взглядом видно, что единица у нас одна она соответствует нашему искомому значению для поиска и находится она под третьим порядковым номером, что соответствует строке 3 нашего диапазона.
В ходе процесса вычисления ИНДЕКС ПОИСКПОЗ функция ПОИСКПОЗ возвращает 3 в ИНДЕКС и пред итоговый результат выглядит следующим образом:
{=ИНДЕКС(A2:D13;3;4)}
а ИНДЕКС возвращает окончательный результат 550 рублей.
Совет: используйте F9, чтобы увидеть промежуточный результат вычисления. Просто выделите промежуточное выражение в строке формул и нажмите F9.
