05 февраля 2019

Excel – просто! Ищем значение на пересечении

#Excel #Обучение

При работе с данными время от времени возникают ситуации, когда нам необходимо найти значение ячейки на пересечении определенных строки и столбца.
Excel – просто! Ищем значение на пересечении

При работе с данными время от времени возникают ситуации, когда нам необходимо найти значение ячейки на пересечении определенных строки и столбца.

В данной статье я Вам расскажу о трёх способах осуществления этой задачи.

1. ИНДЕКС (INDEX)

Данная функция позволяет извлечь значение из диапазона ячеек по номеру строки и столбца.

Аргументы функции выглядят следующим образом:

Excel – просто. Ищем значение на пересечении

Массив – ссылка на диапазон ячеек, в котором нам нужно осуществить поиск;

Номер_строки – строка, из которой нужно извлечь значения;

Номер_столбца – столбец, из которого нужно извлечь значение.

Функция ИНДЕКС также может извлекать значение из разных выделенных областей, но в данной статье я не буду останавливаться на этой возможности.

Схематически работу функции можно изобразить так:

Excel – просто. Ищем значение на пересечении

Для автоматического поиска строки/столбца функцию ИНДЕКС, как правило, дополняют двумя функциями ПОИСКПОЗ (MATCH).

В таком случае формула выглядит примерно так:

=ИНДЕКС($C$7:$F$9;ПОИСКПОЗ($I$5;$B$7:$B$9;0);ПОИСКПОЗ($I$6;$C$6:$F$6;0))

Excel – просто. Ищем значение на пересечении

При смене параметров поиска, значение пересекающейся ячейки будет изменяться автоматически.

2. СУММПРОИЗВ (SUMPRODUCT)

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

Excel – просто. Ищем значение на пересечении

Для решения нашей задачи формула будет выглядеть так:

=СУММПРОИЗВ((C6:F6=I6)*(B7:B9=I5)*(C7:F9))

Простыми словами синтаксис функции можно представить так:

=СУММПРОИЗВ((условие_1)*(условие_2)*(что_суммировать))

Excel – просто. Ищем значение на пересечении

Сумма умножений 0, 1 и значений таблицы извлечёт нам искомое значение.

3. ДВССЫЛ (INDIRECT)

Третий способ, который лично мне очень нравится своей простотой. Единственное условие для его работы – названия в столбцах и строках должны быть без пробелов (их можно удалить или заменить на нижнее подчеркивание «_»).

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

Excel – просто. Ищем значение на пересечении

Если на первый взгляд функция выглядит простой и малополезной, то при дальнейшем её изучении Вы узнаете, что с её помощью мы можем перемещаться по листам, транспонировать таблицы, отбирать чётные (нечётные) строки и многое другое. Ведь для нее аргумент Ссылка_на_ячейку – всего лишь текстовая строка, которую можно изменять формулами.

Чтобы найти значение ячейки на пересечении определенных сроки и столбца нам понадобится сразу две функции ДВССЫЛ.

Для начала нам необходимо создать диапазоны для строк и столбцов.

Выделяем столбцы с заголовками и нажимаем комбинацию клавиш Ctrl+Shift+F3

Excel – просто. Ищем значение на пересечении

Аналогичную операцию проделываем и для строк.

Excel – просто. Ищем значение на пересечении

Сама формула будет иметь такой вид:

=ДВССЫЛ(I5) ДВССЫЛ(I6)

Обратите внимание что функции разделены пробелом.

Excel – просто. Ищем значение на пересечении

Excel даёт нам возможность использовать несколько вариантов решения для одной задачи. Там, где невозможно применить один способ, практически всегда есть возможность подобрать другой. Настоятельно рекомендую Вам изучить функции, рассмотренные в данной статье и Ваша работа с данными, станет намного эффективнее.

 

Автор: Михаил Беленчук, тренер DATA bi

Популярные статьи