Многие инструменты Эксель не особо популярны среди пользователей и поэтому выпадают из их поля зрения, хотя могут быть полезными при решении некоторых задач. Рассмотрим пример использования пересечения диапазонов в Excel.
При работе с диапазонами ячеек мы часто имеем дело с оператором двоеточие, который позволяет определить прямоугольный диапазон, в котором указывается верхняя левая и правая нижняя ячейки.
Также в формулах иногда применяется оператор точка с запятой, который позволяет объединить несколько прямоугольных диапазонов или ячеек.
Но есть еще один оператор, который крайне редко используется и поэтому не многие пользователи Excel вообще о нем знают - это оператор пробел. Он позволяет определить пересечение диапазонов, то есть при его использовании возвращается диапазон, состоящий из ячеек, входящих в оба указанных диапазона.
Правда тут есть нюанс - если в пересечение попадает более одной ячейки и ваш Эксель поддерживает динамические массивы, то на выходе вы получите именно диапазон всех ячеек пересечения. Если же вы пользуетесь Экселем 2019 и более старыми версиями, то на выходе получите лишь значение верхней левой ячейки пересечения. В этом случае для получения всего диапазона необходимо воспользоваться формулой массива, то есть нажать сочетание клавиш Ctrl+Shift+Enter.
Ну а теперь давайте рассмотрим пример практического применения пересечения диапазонов.
Есть таблица с данными, в которой есть заголовки строк и столбцов.
Например, необходимо в дашборд включить блок, в котором из выпадающих списков выбираются соответствующие значения - Город и Заказчик, и из основной таблицы подтягивается значение, которое находится на пересечении соответствующего столбца и строки.
В принципе, это классическая задача для аналога функции ВПР - связки функций ИНДЕКС и ПОИСКПОЗ, но решить задачу можно и с помощью пересечения диапазонов и давайте так и поступим.
Сначала определим диапазоны, с которыми будем работать. Для этого присвоим им имена и проще всего это сделать следующим образом - выделяем всю таблицу с данными и заголовками и через вкладку Формулы создаем имена для всего выделения.
В диалоговом окне задаем, что имена нужно создать на базе первой строки и первого столбца значений, то есть на основе заголовков исходной таблицы.
Теперь откроем Диспетчер имен и увидим, что для каждого столбца и строки исходной таблицы был создан соответствующий именованный диапазон.
Но при создании именованных диапазонов обязательно стоит учитывать то, что имя диапазона не может начинаться с цифры, а значит если у вас один из заголовков столбца содержит числовые значения, например, даты, то этот способ в данном случае не подойдет, так как в имени перед числовым значением автоматически добавится символ подчеркивания. Также нужно учитывать, что не все символы можно использовать в имени. Так, например, если в исходной таблице будут заголовки "О'КЕЙ" ( вместо "ОКЕЙ") и "Санкт-Петербург", то в имени недопустимые символы будут автоматически заменены подчеркиванием и мы получим диапазоны с именами "О_КЕЙ" и "Санкт_Петербург". Обычно эти нюансы не влияют на использование именованных диапазонов в формулах, но при решении нашей задачи они существенны, поэтому их нужно учитывать.
Итак, диапазоны готовы и чтобы найти значение, находящееся на пересечении двух диапазонов достаточно ввести их названия через пробел.
Ну а чтобы привязать формулу к выпадающим спискам просто на них сошлемся.
Но в таком виде получим ошибку, ведь в формуле участвуют не ссылки на нужные нам диапазоны, а ссылки на ячейки выпадающих списков, которые не пересекаются. Чтобы преобразовать значения ячеек с выпадающими списками в ссылки, воспользуемся функцией ДВССЫЛ.
Теперь все будет работать должным образом и при выборе значений в выпадающих списках будут подтягиваться правильное значение из основной таблицы.
Чтобы скачать курс по основам Microsoft Excel нажмите на эту ссылку
Ссылки на мои ресурсы по Excel
★ YouTube-канал по Excel и Word
★ Телеграм