Примеры формул с функциями ИНДЕКС и ПОИСКПОЗ СУММПРОИЗВ в Excel
Функция ИНДЕКС предназначена для создания массивов значений в Excel и чаще всего используется в паре с другими функциями. Рассмотрим на конкретных примерах формул с комбинациями функции ИНДЕКС и других функций для поиска значений при определенных условиях.
Поиск значений по столбцам таблицы Excel
Необходимо выполнить поиск значения в таблице и отобразить заголовок столбца с найденным значением. Готовый результат должен выглядеть следующим образом:
Небольшое упражнение, которое показывает, как в Excelе можно добиться одинакового результата при использовании совершенно разных функций.
Итак, у нас есть таблица, и мы бы хотели, чтобы формула Excel отвечала на вопрос, в каком столбце (или строке, однако в нашем примере я использую столбец), находится искомое слово. Думаю, что анимация, расположенная выше, полностью показывает задачу.
Формула массива или функции ИНДЕКС и СУММПРОИЗВ
Пример 1. Первая идя для решения задач типа – это при помощи какого-то вида цикла поочерёдно прочитать каждую ячейку из нашей таблицы и сравнить её с искомым словом. Если их значения совпадают, то отображается имя соответствующего столбца.
Когда речь идет о циклах Excel (за исключением VBA, конечно, потому что там дело намного проще), на ум приходят в первую очередь следующие решения:
- Итерационные (итеративные) вычисления.
- Формулы массива (или такие функции, как СУММПРОИЗВ, которые работают с массивами, хотя мы их не подтверждаем с помощью Ctr + Shift + Enter, как в случае с классическими формулами массива).
Первое решение выглядит следующем образом:
Рассмотрим ближе каким образом эта формула работает:
($A$2:$D$9=F2)
Данный фрагмент формулы необходимо сравнивать значение каждой ячейки таблицы с искомым словом. Для этого формула создаёт в памяти компьютера массив значений, равный размерам нашей таблицы, заполненную нулями (фактически значениями ЛОЖЬ, но через какое-то время мы будем умножать эти значения, и Excel автоматически преобразует их на ноль). Нули там, где значение в таблице не равно искомому слову. Как не трудно догадаться, в случае нахождения искомого слова, в памяти компьютера в соответствующем месте будет цифра 1.
СТОЛБЕЦ($A$2:$D$9)
В то же самое время параллельно, в памяти компьютера создаётся другой массив значений (тех же размеров, что и размеры нашей исходной таблицы), содержащая номера столбцов для каждой из ячеек нашей таблицы. Затем одна таблица умножается на другую. Выглядит это более или менее так, как показано на рисунке ниже (пример для искомого выражения «Прогулка в парке»).
В результате мы получаем массив с нулями везде, где значения в нашей таблице не соответствуют искомому выражению, и номер столбца, в котором соответствующее значение было найдено.
СУММ(($A$2:$D$9=F2)*СТОЛБЕЦ($A$2:$D$9))
Наконец, все значения из нашей таблицы суммируются (в нашем примере в результате мы получаем «3» то есть номер столбца, в котором находится искомое выражение). Остальное просто выбирает и показывает значения из строки заголовка, что осуществляется при помощи функции ИНДЕКС.
Отложим в сторону итерационные вычисления и сосредоточимся на поиске решения, основанного на формулах массива.
Пример формулы поиска значений с функциями ИНДЕКС и ПОИСКПОЗ
Пример 2. Вместо формулы массива мы можем использовать формулу, основанную на функции СУММПРОИЗВ.
Принцип работы в основном такой же, как и в первом случае. Мы используем здесь просто другую функцию Excel, и вся формула не должна подтверждаться как формула массива. Однако на этот раз мы создаём в памяти компьютера столько же массивов значений, сколько столбов в нашей таблице. Каждый раз, в таком единичном массиве нули находятся везде, где искомое значение не было найдено. Однако здесь вместо использования функции СТОЛБЕЦ, мы перемножаем каждую такую временную таблицу на введённый вручную номер столбца.
Пример формулы функций ИНДЕКС и НЕ
Пример 3. Третий пример – это также формула массива.
Все точно так же, как и в первом решении, только запись немного отличается. Я рекомендую самостоятельно сравнить эти формулы с целью лучше усвоить материал.
И наконец-то, несколько иной (не табличный) подход. Как правило, каждый раз, когда необходимо что-то искать в Excel, моментально на ум приходит пара функций: ИНДЕКС и ПОИСКПОЗ. Результат поиска выглядит так:
Формула построена из блоков и имеет более или менее следующий вид:
1*НЕ(ЕОШИБКА(ПОИСКПОЗ(F5;A2:A9;0)))
Изменяются только выделенные фрагменты. Для каждого столбца с помощью функции ПОИСКПОЗ, мы проверяем удалось ли найти в столбце искомое выражение. Если нет, то функция возвращает ошибку. С помощью функции ЕОШИБКА мы проверяем выдала ли функция ПОИСКПОЗ ошибку. Если да, то мы получаем значение ИСТИНА. Быстро меняем её на ЛОЖЬ и умножаем на введённый вручную номер столбца (значение ИСТИНА заменяем на ЛОЖЬ, потому что нас не интересует ошибка, возвращаемая функцией ПОИСКПОЗ. Также ищем варианты (столбцы), в котором формула не выдала ошибку. Потому что это будет означать, что искомое значение было найдено. Немного запутано, но я уверен, что анализ формул позволит вам быстро понять ход мыслей.
