Пересечение диапазонов в excel
Вчера мы дали старт марафону 30 функций Excel за 30 дней, начав с функции EXACT (СОВПАД), которая оказалась очень интересной и полезной функцией. Мы рассмотрели несколько примеров ее применения в таблицах Excel.
Сегодня мы изучим функцию AREAS (ОБЛАСТИ), которая малость облегчена в плане своей полезности (это мягко говоря). Она как ленивый шурин в плохой комедии, который лежит на твоём диване и пьёт твоё пиво.
Но даже у бездельника должно быть предназначение. Этот ленивый шурин может служить примером для Ваших детей, как НЕ НАДО себя вести. Что касается функции AREAS (ОБЛАСТИ), с её помощью мы можем увидеть, как работают операторы ссылок в Excel и как они влияют на результаты формул.
Итак, давайте посмотрим информацию и примеры по использованию функции AREAS (ОБЛАСТИ) в Excel. Если у Вас найдутся другие примеры, пожалуйста, поделитесь ими в комментариях. Только не присылайте мне своего шурина!
Функция 02: AREAS (ОБЛАСТИ)
Функция AREAS (ОБЛАСТИ) возвращает количество областей в ссылке. Областью может выступать диапазон смежных ячеек либо одиночная ячейка. Ячейки могут быть пустыми или содержать данные – это не имеет значения для подсчёта количества.
Как можно использовать функцию AREAS (ОБЛАСТИ)?
Функция AREAS (ОБЛАСТИ) имеет не так уж много практических применений, но она является интересным примером того, как работают операторы ссылок в Excel. Вы можете использовать функцию AREAS (ОБЛАСТИ) вот для чего:
- Подсчёт количества областей в диапазоне.
- Подсчёт количества пересечений нескольких диапазонов.
- Вычисление номера какой-либо области для функции INDEX (ИНДЕКС).
Синтаксис AREAS (ОБЛАСТИ)
Синтаксис функции AREAS (ОБЛАСТИ) таков:
- Ссылкой может быть одна ячейка, диапазон или несколько областей.
Операторы ссылок
При записи ссылки Вы можете использовать любой из трёх операторов ссылок:
: | Двоеточие | A1:B4 | диапазон | все ячейки между двух указанных ячеек, включая указанные |
, | Запятая (eng.) | A1,B2 | объединение | объединяет несколько ссылок вместе |
; | Точка с запятой (рус.) | A1;B2 | ||
Пробел | A1 B3 | пересечение | ячейки, общие для указанных ссылок |
Ловушки AREAS (ОБЛАСТИ)
Если Вы используете запятую (или точку с запятой) в функции AREAS (ОБЛАСТИ), чтобы ввести несколько диапазонов ячеек, то необходимо добавить ещё одну пару скобок. Примерно так:
Пример 1: Подсчёт количества областей в диапазоне
Если использовать функцию AREAS (ОБЛАСТИ) с простой ссылкой на диапазон, то результатом будет 1.
Пример 2: Подсчёт количества областей в нескольких ссылках
Вы можете использовать функцию AREAS (ОБЛАСТИ) с несколькими ссылками, чтобы подсчитать общее количество областей. Так как запятая (точка с запятой) используется в Excel, как оператор объединения, придётся добавить пару круглых скобок ко всему выражению в формуле.
Даже, если указанные в ссылке области являются смежными, они все равно учитываются как разные, поэтому результатом формулы будет 2.
Пример 3: Подсчёт количества областей в накладывающихся ссылках
Даже если области в ссылках совпадают или одна из них полностью включает другую, они будут посчитаны отдельно, когда Вы используете оператор объединения.
Область F2 полностью входит в диапазон F2:H2, но они учитываются как отдельные, и результатом формулы будет 2.
Пример 4: Подсчёт областей в пересекающихся ссылках
Если Вы используете пробел, в качестве оператора пересечения, то будет подсчитано только количество пересекающихся областей.
=AREAS(TESTREF01 TESTREF02)
=ОБЛАСТИ(TESTREF01 TESTREF02)
Именованный диапазон TESTREF01 раскрашен голубым, а TESTREF02 – розовым цветом. Эти диапазоны пересекаются в трёх точках, выделенных толстой границей, так что результатом формулы будет 3.
Пример 5: Вычисление номера области для функции INDEX (ИНДЕКС)
Функция INDEX (ИНДЕКС) в ссылочной форме может использовать номер области в качестве последнего аргумента.
В следующем примере TestBlock – это несмежный именованный диапазон, состоящий из 4-х областей. В формуле имя TestBlock является ссылкой, а функция AREAS (ОБЛАСТИ) подсчитывает количество областей в этом диапазоне.
Чтобы получить значение из диапазона TestBlock, которое находится в 5-й строке последней области, используйте такую формулу:
Последняя область — это Day04, а пятое значение в Day04 — это H05. Именно это значение возвратила наша формула.
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
В excel есть возможность присвоения имен ячейкам, таблицам и диапазонам. После присвоения, эти имена можно использовать в формулах, при этом формулы становятся намного понятнее пользователю.
Присвоение имени константе .
Имена можно присваивать и константам, которых даже нет в вашем файле. Например, если в своем прогнозе вы используете рост рынка в размере 5% в следующем году, то этому росту можно присвоить имя. Для этого перейдите на ленте во вкладку Формулы -> Определенные имена -> Задать имя:
Откроется диалоговое окно "Создание имени", в котором необходимо ввести Имя, Область действия этого имени (лист, книга), Примечание (чтобы не забыть, что это за константа), в поле Диапазон необходимо ввести число и нажать ОК:
Теперь эту константу можно использовать во всей книге при расчете формулы, например так:
Обратите внимание, теперь значительно проще понять как был сформирован объем продаж в 2021 году.
Важно : константой может быть не только число, но и текст. Таким образом вы можете упростить ввод часто повторяющихся слов.
Присвоение имени ячейке и формуле.
Порядок присвоения имени ячейке и формуле не сильно отличается от предыдущего примера. Перейдите на ленте в блок Задать имя и в диалоговом окне "Создание имени" на последнем шаге вместо ввода числа введите либо ссылку на ячейку, либо формулу:
Если вы хотите, чтобы ваша именованная формула при расчете использовала значение из ячейки, находящейся левее (например, A4) от той, в которой формула будет находиться (B4), обязательно перед созданием имени активируйте ячейку B4 и не используйте абсолютные ссылки.
Подробнее об абсолютных и относительных ссылках можете прочитать в статье Абсолютные и относительные ссылки в Excel .
Присвоение имени диапазону.
В присвоении имени диапазону нет ничего сложного, просто в окне Создание имени в поле Диапазон пропишите ссылку на него. Интереснее дела обстоят с использованием пересечения диапазонов (это те ячейки, которые являются общими для двух диапазонов). Предположим, есть таблица с объемом продаж трех менеджеров за 4 квартала:
Этот пример показывает, как получить объединение и пересечение двух диапазонов в Excel. Границы на рисунке ниже представлены только в качестве иллюстрации.
-
Используйте запятую, чтобы получить объединение двух диапазонов.Объяснение: Функция SUM (СУММ) сводится к
= SUM(C4:D8) + SUM(D7:E11)
= СУММ(C4:D8) + СУММ(D7:E11), т.е. 20.
= SUM(D7:D8)
= СУММ(D7:D8), т.е. 2.
Подпишитесь к нам в дзен-канал, для получения свежих новостей it мира:
Одна из типичных задач для пользователя Microsoft Excel. Имеем два диапазона дат вида «начало-конец». Задача состоит в том, чтобы определить пересекаются ли эти диапазоны и, если да, то на сколько дней.
Пересекаются или нет?
Начнем с решения вопроса о том, есть ли пересечение интервалов в принципе? Предположим, что у нас есть таблица рабочих смен сотрудников вот такого вида:
Хорошо видно, что рабочие смены Ярослава и Елены пересекаются, но как это вычислить, не прибегая к построению календарного графика и визуальному контролю? Нам поможет функция СУММПРОИЗВ (SUMPRODUCT) .
Вставим в нашу таблицу еще один столбец с формулой, которая выдает логическое значение ИСТИНА в случае пересечения дат:
На сколько дней пересечение?
Если принципиально не просто понимать - пересекаются наши интервалы или нет, а точно знать сколько именно дней попадает в пересечение, то задача усложняется. Рассуждая логически, необходимо "прокачать" аж 3 разных ситуации в одной формуле:
- интервалы не пересекаются
- один из интервалов полностью поглощает другой
- интервалы пересекаются частично
На самом деле все можно сделать красиво с помощью функции МЕДИАНА (MEDIAN) из категории Статистические.
Если условно обозначить начало первого интервала за Н1 , а конец за К1 , и начало второго за Н2 и конец за К2 , то в общем виде наша формула может быть записана как:
=МЕДИАНА( Н1 ;К1+1; К2 +1)-МЕДИАНА( Н1 ;К1+1; Н2 )
Читайте также: