Имена диапазонов Excel с абсолютным адресом

В основном пользователи Excel используют один тип имен диапазонов. При использовании имени в формулах, к нему обращаются как к абсолютной ссылке на диапазон ячеек. Хотя в предыдущем уроке мы присвоили имя не диапазону, а числу (значению).

Преимущества имен диапазонов перед абсолютными ссылками

Приготовьте лист, на котором расходы будут пересчитаны из одной валюты в другую.

Пересчет должен выполняться соответственно курсов валют, которые изменяются. Поэтому курсы нельзя вносить в формулы, чтобы при их изменении не пришлось редактировать каждую ячейку.

Для решения данной задачи мы можем обойтись без использования имен с помощью абсолютных ссылок. Ниже приведем пример. Но имена дают более изящное решение данной задачи. Для сравнения рассмотрим оба варианта.

Допустим, мы решаем данную задачу с помощью абсолютной ссылки на ячейку со значением текущего курса. Тогда нам нужно сделать так:

1. Запишем курсы ЕВРО и ДОЛЛАРА в отельных ячейках F2 и G2.

2. В ячейки C2 и D2 введем формулы, которые ссылаются к ценам в рублях через относительную ссылку, а к другим валютам через абсолютную ссылку.
3. Скопируем диапазон ячеек C2:D2 в C3:D5.

Данное решение вполне рабочее и его используют для временных вычислений. Преимущества абсолютных ссылок очевидны. При изменении только одной ячейки автоматически пересчитывается целый диапазон ячеек без лишних изменений.

Главным недостатком абсолютных ссылок является плохая читабельность формул. В документах для долгосрочного использования вместо абсолютных ссылок лучше использовать имена. Они обладают теми же преимуществами, но и улучшают читабельность формул. Это существенно повысит производительность пользователя при редактировании формул для внесения поправок или изменения порядка аргументов вычислений. Даже спустя несколько лет Вы откроете такой документ и быстро сориентируетесь в алгоритмах расчетов в сложных отчетах. Тем более это важно, если документ предназначен для использования широкого круга пользователей.

Автоматическое создание и добавление имен диапазонов в формулы Excel

Теперь рассмотрим использование имен как альтернативный вариант для выше описанной задачи:

1. Выделите диапазон ячеек F1:G2 и выберите инструмент: «Формулы»-«Определенные имена»-«Создать из выделенного».

2. В появившемся окне «Создание имен из выделенного диапазона», отметьте первую опцию: «в строке выше», как на рисунке. Это значит, что значения в верхних строках будут использованы для названия имен ячеек в нижних строках. Будет создано одновременно сразу 2 имени. Ячейка F2 получит имя «Евро», а ячейка G2 – «Доллар».
3. Выделите диапазон C2:D5 и выберите инструмент из выпадающего меню: «Формулы»-«Определенные имена»-«Присвоить имя»-Применить имена».

4. В появившемся окне выделите сразу 2 имени, а остальное оставьте все по умолчанию и нажмите ОК.

Это только примитивный пример преимущества использования имен вместо абсолютных ссылок. Вы без проблем можете менять курсы валют (изменяя значения ячеек F2 и G2), а цены будут автоматически пересчитаны.

Примечание. Курсы валют можно хранить не только в значениях ячеек, но и в самих именах. Просто в поле диапазон введите значение текущего курса.



Использования имен Excel в выпадающих списках при пересечении множеств

Теперь приведем более наглядный пример существенного преимущества использования имен.

Создайте отчет по продажам за первый квартал по 4-ем магазинам как на рисунке:

С помощью формулы и оператора пересечения множеств мы будем работать с этим отчетом как с базой данных. В ячейках A8 и B8 мы создаем запрос к базе, а в ячейке C8 получим результирующий ответ. Сначала создадим все имена:

1. Выделите диапазон ячеек A2:D5 и выберите инструмент: «Формулы»-«Создать из выделенного». В появившемся окне отмечаем вторую опцию сверху: «в столбце слева».

2. Выделите диапазон ячеек B1:D5 и выберите инструмент: «Формулы»-«Создать из выделенного». В появившемся окне отмечаем вторую опцию сверху: «в столбце сверху». Таким образом, у нас создались все нужные нам имена. Чтобы убедиться в этом выберите инструмент: «Диспетчер имен».

3. Перейдите в ячейку C8 и введите функцию СУММ со следующими аргументами: =СУММ(Магазин3 февраль) и нажмите Enter.

Отлично! В результате мы видим значение 500 – это прибыль магазина3 за февраль месяц. Теперь нам только осталось с помощью функции сделать обработчик запросов, который так же будет использовать имена в своем алгоритме. Для этого:

1. Модифицируем формулу в ячейке C8, а именно так: =СУММ(ДВССЫЛ(A8) ДВССЫЛ(B8)). И нажмите Enter. В результате формула выдала ошибку: #ССЫЛКА! Не переживайте по этому поводу, все под контролем, делаем обработчик запросов далее.
2. Создадим еще 2 имени. Выделите диапазон A2:A5 чтобы присвоить ему имя «магазины». Для этого выберите инструмент: «Формулы»-«Присвоить имя». Заполните диалоговое окно как на рисунке. А потом выделите диапазон B1:D1 и присвойте ему имя «месяцы».

3. Создаем выпадающий список для безошибочного запроса к нашей мини базе данных. Перейдите в ячейку А8 и выберите инструмент: «Данные»-«Работа с данными»-«Проверка данных». В появившемся окне: «Проверка вводимых значений» внесите настройки, так как показано на рисунке. И нажмите ОК.

4. Таким же образом создайте второй список с месяцами в ячейке B8.

Готово! В результате мы теперь можем уверенно работать с нашей базой данных. Указываем параметры запроса, а в ячейке теперь вместо ошибки #ССЫЛКА! отображается правильный результирующий ответ.

Примечание. Хотя списки можно и не использовать, а вводить названия магазинов и месяцев вручную. Списки нужны для удобства ввода и исключения возможных ошибок допущенных при ручном вводе значений. Результат будет тот же.

В некоторой степени решение данной задачи можно выполнить и без имен используя плохо-читаемые абсолютные адреса ссылок на диапазоны ячеек. Но вот такой обработчик запросов без использования имен сделать гораздо сложнее.