В 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
