Пересечение диапазонов в Excel

В Excel имеется 3 оператора работы с диапазонами:

1) Оператор ":" - используется для обозначения прямоугольного диапазона (указывается между левой верхней и правой нижней ячейками). Этот оператор всем нам прекрасно знаком и используется постоянно (ссылки вроде A1:B3 и т.д.)

2) Оператор ";" (или запятая, в зависимости от локальных языковых настроек) - используется для объединения прямоугольных диапазонов. Ссылка вида =A1:B3;C2:D5 представляет собой диапазон, состоящий из двух прямоугольных:

  3) Оператор "Пробел" - используется для определения пересечения диапазонов. В результате возвращается диапазон, состоящий из ячеек, которые входят в оба диапазона, между которыми стоит оператор. На скриншоте ниже результатом работы оператора является диапазон B2:B3. 

 Еще один пример: 

 Если на стыке диапазонов находится больше одной ячейки, то результатом будет диапазон. Если ввести такую формулу в одну ячейку, то в ней будет отображено только левое верхнее значение результирующего диапазона. Чтобы вывести все значения - выделите нужное количество ячеек и введите формулу пересечения как формулу массива (Ctrl+Shift+Enter) 

 Если диапазоны не пересекаются, то формула вернет ошибку #ПУСТО! 

 

Практическое применение

Отличным вариантом использования оператора пересечения является его сочетание с именованными диапазонами. Рассмотрим пример. Имеется лист с вот таким набором данных: 

 В ячейках B13 и B14 реализован простой выпадающий список для выбора торговой марки и города. Необходимо в ячейку ниже выводить соответствующее значение на пересечении марки и города. 

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

Выделите всю таблицу с данными (вместе с шапкой и первой колонкой) и выберите "Формулы" - "Создать из выделенного" (эту команду также можно вызвать сочетание клавиш Ctrl+Shift+F3. В появившемся окне установите следующие галочки и нажмите ОК: 

 Если теперь Вы откроете диспетчер имен ("Формулы" - "Диспетчер имен" или клавиши Ctrl+F3), то увидите, что Excel создал именованный диапазоны для каждой строки и каждого столбца таблицы. 

Имейте в виду, что если в шапке у вас числа (например, годы), то таким способом создать именованные диапазоны не получится, так как по правилам Excel имя не может начинаться с цифры.

 Теперь мы сможем использовать эти имена в формулах. Введите в любую ячейку формулу: =АРИСТОКРАТ Орёл и в результате получите значение 840 (ячейка на пересечении марки и города) 

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

В итоге формула в ячейке B15 (Объем продаж) примет вид:

=ДВССЫЛ(B13) ДВССЫЛ(B14)

Теперь при смене марки и города объем будет подтягиваться автоматически.  

Согласитесь, способ более тонкий, чем ИНДЕКС+ПОИСКПОЗ. Можете впечатлить менее искушенных коллег. 

Файл с примером можете найти на нашем канале по этой ссылке.

Поддержать наш проект и его дальнейшее развитие можно вот здесь.

Ваши вопросы по статье можете задавать через нашего бота обратной связи в Telegram: @ExEvFeedbackBot

С уважением, команда tDots.ru