Перегляд відео - Використання оператора перетину в Excel
Оператор перетину в Excel можна використовувати для знаходження перетинаються значень двох списків/діапазонів. Це незвичайний оператор, оскільки він представлений символом пробілу (так, це правильно).
Якщо ви використовуєте пробіл між двома діапазонами, він стає оператором Intersect в Excel.
Оператор перетину в Excel
Ви можете скористатися Оператором перетину в Excel, щоб знайти:
- Перетин одного рядка і стовпця.
- Перетин кількох рядків і стовпців.
- Перетин названих діапазонів.
Перетин одного ряду і стовпця
Припустимо, що є набір даних, як показано нижче:
Тепер, якщо ви використовуєте = C2: C13 B5: D5 [Зауважте, що між діапазонами є єдиний пробіл, який також є нашим оператором перетину в Excel], він поверне 523 (значення в комірці C5), що є перетином цих 2 діапазонів.
Перетин декількох рядків і стовпців
Ви також можете використовувати той самий прийом, щоб знайти перетин діапазонів, що охоплює більше одного рядка або стовпця. Наприклад, з таким самим набором даних, як показано вище, ви можете отримати перетин Продукту 1 та Продукту 2 у квітні.
Ось формула, яка може це зробити: = B2: C13 B5: D5
Зауважте, що результат цієї формули відображатиме помилку значення, однак, коли ви виберете формулу та натиснете F9, результат відобразиться як {649,523}. Ця формула повертає масив значень перетину. Ви можете використовувати це у формулах, таких як SUM (щоб отримати загальну суму перетинів) або MAX (щоб отримати максимум значень перетину).
Перетин названих діапазонів
Ви також можете використовувати іменовані діапазони, щоб знайти перетин за допомогою оператора перетину в Excel.
Ось приклад, коли я назвав значення Продукту 1 як Prdt1, Значення Продукту 2 як Prdt2 і Квітневі значення як Квітень.
Тепер ви можете використовувати формулу = Prdt1 Apr, щоб отримати перетин цих 2 діапазонів. Аналогічно, ви можете скористатися = Prdt1: Prdt2 Apr, щоб отримати перетин Продукту 1, Продукту 2 та квітня.
Практичний приклад використання оператора перетину в Excel
Ось ситуація, коли цей трюк може стати в нагоді. У мене є набір даних представників відділу продажів та продажів, які вони здійснювали щомісяця у 2012 році.
Я також створив розкривний список з Торговий представник Назвіть в одній клітинці і Місяць ім'я в іншому, і я хочу отримати дані про те, що представник зробив за цей місяць.
Щось, як показано нижче:
Як створити це:
- Виберіть весь набір даних (B3: N13) і натисніть Control + Shift + F3, щоб створити іменовані діапазони (це також можна зробити за допомогою Формули -> Визначені імена -> Створити з виділення). Відкриється діалогове вікно "Створити імена з виділення".
- Виберіть опції «Верхній рядок» і «Ліва колонка» і натисніть кнопку ОК.
- Це створить іменовані діапазони для всіх торгових представників та всього місяця.
- Тепер перейдіть до комірки В16 і створіть випадаючий список для всіх представників відділу продажів.
- Аналогічно перейдіть до комірки C15 і створіть випадаючий список для всіх місяців.
- Тепер у клітинці C16 використовуйте таку формулу = ВПРАВО (B16) ВПРАВО (C15)
Як це працює?
Зверніть увагу, що між двома непрямими формулами є пробіл.
Функція INDIRECT повертає діапазон для іменованих діапазонів - Sales rep та Month, а пробіл між ними працює як оператор перетину та повертає значення, що перетинається.
Примітка: Цей невидимий оператор перетину має пріоритет над іншими операторами. Тож якщо в цьому випадку, якщо ви використовуєте = INDIRECT (B16) INDIRECT (C15)> 5000, воно поверне TRUE або FALSE на основі значення, що перетинається.
