Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.
Всем известно, что формула =СУММ(А2:А10) суммирует диапазон из девяти ячеек с A2 по A10, а =СУММ(А1;А3;А5;А7;А9) – суммирует значение в пяти, указанных ячейках. Тем не менее, очень немногие понимают, что пробел внутри функции СУММ – это, на самом деле, оператор пересечения. Рассмотрим типичную коммерческую таблицу (рис. 1). Для начала присвоим имена строкам и столбцам:
- Выделите диапазон ячеек A1:F
- Пройдите по меню ФОРМУЛЫ –> Создать из выделенного.
- В окне Создание имени из выделенного диапазона выберите в строке выше и в столбце слева. Нажмите Оk.
Рис. 1. Создание имен диапазонов на основе таблицы с заголовками строк и столбцов
Скачать заметку в формате Word или pdf, примеры в формате Excel
Excel создаст имена для 12 городов. Например, имя Атланта будет присвоено диапазону ячеек В2:F2. Excel также создаст пять имен диапазонов для заголовков в первой строке. Например, имя Себестоимость будет присвоено диапазону ячеек С2:С13 (рис. 2).
Рис. 2. Excel мгновенно создал 17 имен диапазонов
Теперь вы можете использовать формулу =СУММ(Продажи), чтобы найти общий объем продаж. У вас также появилась возможность использовать операцию пересечения – ˽ (пробел), что позволит найти значение на пересечении двух именованных диапазонов. Например, формула =СУММ(Бостон Себестоимость) позволяет найти значение себестоимости для продаж по городу Бостон (рис. 3).
Рис. 3. Оператор пересечения используется для получения значения, находящегося на пересечении двух именованных диапазонов
Примечания:
- Используйте подсказки при наборе формул (рис. 4). Как только вы наберете букву С, появится меню с доступными именами и функциями. Для перемещения по меню используйте стрелки ↑ (вверх) и ↓ (вниз), а для выбора значения из меню подсказок – клавишу Табуляция (интуитивное использование Enter в этом случае вам не поможет, так как Enter используется для окончания ввода формулы).
- С этой же задачей справится формула =СУММ(3:3 C:C), которая также вернет значение на пересечении третьей строки и столбца С. Но согласитесь, использование именованных диапазонов выглядит более профессионально и облегчает понимание формул.
- Вы также можете найти себестоимость продаж по Бостону и Далласу одновременно при помощи формулы =СУММ((Бостон;Даллас) Себестоимость). Здесь Бостон и Даллас взяты в скобки, чтобы изменить порядок выполнения операторов. Оператор пересечения — ˽ (пробел) имеет более высокий приоритет, чем оператор объединения — ; (точка с запятой). Подробнее см. Операторы и их приоритет.
Рис. 4. Подсказки при наборе формул