Посмотреть видео - Использование оператора пересечения в Excel
Оператор пересечения в Excel может использоваться для поиска пересекающихся значений двух списков / диапазонов. Это необычный оператор, так как он представлен пробелом (да, верно).
Если вы используете пробел между двумя диапазонами, он становится оператором пересечения в Excel.
Оператор пересечения в Excel
Вы можете использовать оператор пересечения в Excel, чтобы найти:
- Пересечение одной строки и столбца.
- Пересечение нескольких строк и столбцов.
- Пересечение именованных диапазонов.
Пересечение одной строки и столбца
Предположим, есть набор данных, как показано ниже:
Теперь, если вы используете = C2: C13 B5: D5 [Обратите внимание, что между диапазонами есть один пробел, который также является нашим оператором пересечения в Excel.], он вернет 523 (значение в ячейке C5), что является пересечением этих двух диапазонов.
Пересечение нескольких строк и столбцов
Вы также можете использовать тот же метод, чтобы найти пересечение диапазонов, охватывающих более одной строки или столбца. Например, с тем же набором данных, который показан выше, вы можете получить пересечение продукта 1 и продукта 2 в апреле.
Вот формула, которая может это сделать: = B2: C13 B5: D5
Обратите внимание, что результат этой формулы будет отображать ошибку значения, однако, когда вы выберете формулу и нажмете F9, она отобразит результат как {649,523}. Эта формула возвращает массив значений пересечения. Вы можете использовать это в формулах, таких как SUM (для получения суммы значений пересечений) или MAX (для получения максимальных значений пересечений).
Пересечение именованных диапазонов
Вы также можете использовать именованные диапазоны, чтобы найти пересечение с помощью оператора пересечения в Excel.
Вот пример, в котором я назвал значения продукта 1 как Prdt1, значения продукта 2 как Prdt2 и значения за апрель как Apr.
Теперь вы можете использовать формулу = Prdt1 Apr, чтобы получить пересечение этих двух диапазонов. Точно так же вы можете использовать = Prdt1: Prdt2 Apr, чтобы получить пересечение Product 1, Product 2 и April.
Практический пример использования оператора пересечения в Excel
Вот ситуация, в которой этот трюк может пригодиться. У меня есть набор данных о торговых представителях и их продажах за каждый месяц в 2012 году.
Я также создал раскрывающийся список с Торговый представитель Имя в одной ячейке и Месяц имя в другом, и я хочу извлечь продажи, которые сделал представитель в этом месяце.
Что-то вроде того, что показано ниже:
Как создать это:
- Выделите весь набор данных (B3: N13) и нажмите Control + Shift + F3, чтобы создать именованные диапазоны (это также можно сделать через Формула -> Определенные имена -> Создать из выделенного). Откроется диалоговое окно «Создать имена из выделенного».
- Выберите параметры «Верхний ряд» и «Левый столбец» и нажмите «ОК».
- Это создаст именованные диапазоны для всех торговых представителей и всего месяца.
- Теперь перейдите в ячейку B16 и создайте раскрывающийся список для всех торговых представителей.
- Аналогичным образом перейдите в ячейку C15 и создайте раскрывающийся список для всех месяцев.
- Теперь в ячейке C16 используйте следующую формулу = INDIRECT (B16) INDIRECT (C15)
Как это работает?
Обратите внимание на пробел между двумя НЕПРЯМЫМИ формулами.
Функция ДВССЫЛ возвращает диапазон для именованных диапазонов - Торговый представитель и Месяц, а пространство между ними работает как оператор пересечения и возвращает пересекающееся значение.
Примечание. Этот невидимый оператор пересечения имеет приоритет над другими операторами. Поэтому, если в этом случае вы используете = INDIRECT (B16) INDIRECT (C15)> 5000, он вернет TRUE или FALSE в зависимости от пересекающегося значения.
