Сегодня мы с вами подробно поговорим про области именованных диапазонов. Область - это местоположение и общая доступность имени. Имя в Excel может иметь область в масштабе рабочей книги или рабочего листа:
- Рабочая книга: Имя доступно во всех листах рабочей книги. Имя уникально для всех листов и на него можно легко ссылаться используя только, собственно, само имя.
- Рабочий лист: Имя уникально и легко доступно только в пределах данного конкретного рабочего листа.
По умолчанию область созданного именованного диапазона - это масштаб рабочей книги. Все имена, которые до этого мы создавали в предыдущих примерах имели такую область.
Вы, конечно, можете ссылаться на имена с областью в масштабе рабочего листа из других листов. Для этого перед именем следует поставить ссылку на лист, например, Лист1!Скидка. Однако, это не самая лучшая практика и желательно, чтобы имя имело область в масштабе рабочей книги.
Давайте посмотрим, как можно создать именованный диапазон с областью в рабочий лист.
У нас есть таблица с данными по продажам и расчётом скидки в зависимости от достижения порога заказа с помощью функции ЕСЛИ.
Если значение в столбце В превышает значение в ячейке F2, то применяется скидка 2%.
=ЕСЛИ(B2>=$F$2;B2*(1-$F$4);B2)
Мы создадим имя для ячейки F3 с областью охвата в масштабе рабочего листа.
1. Выделим ячейку F3.
2. Нажмём Формулы ➤ Задать имя.
3. В окне Создание имени измените Имя на "rngПорог".
4. Измените Область с помощью выпадающего списка с Книга на имя текущего рабочего листа ("Скидки" в нашем случае). Нажмите ОК.
Теперь имя rngПорог можно использовать вместо ссылки на ячейку $F$2.
=ЕСЛИ(B2>=rngПорог;B2*(1-$F$4);B2)
Однако, если вы попытаетесь сослаться на rngПорог из другого листа, то это имя не будет распознано.
Также вы не можете редактировать область уже созданного имени. Если вы зайдёте в Изменение имени, то увидите, что выпадающий список Область не активен.
Теперь давайте поговорим про поведение имён при копировании листов. Часто при этом возникает некоторая путаница в именах, поэтому важно знать и понимать, что происходит при копировании листов.
Вот как ведут себя имен при разных сценариях копирования:
- Когда рабочий лист с именованным диапазоном на уровне рабочей книги копируется в той же рабочей книге, то создается второе имя с тем же именем, но с областью действия на уровне рабочего листа.
Это можно проверить в окне Диспетчер имен. При копировании листа [Скидки] появилось новое имя с тем же названием, но с областью в масштабе листа [Скидки(2)].
- Когда рабочий лист с именованным диапазоном копируется в другую рабочую книгу Excel, то имя копируется вместе с листом. Это относится как к именованным диапазонам в масштабе рабочей книги, так и рабочего листа.
Часто это будет именно то, что вам и нужно, так как в новой книге скорее всего вам также понадобится именованный диапазон из скопированного листа. Но все же важно помнить о таком поведении Excel, чтобы потом для вас не стало сюрпризом внезапное появление в ваших файлах лишних имён.
- И, наконец, если вы копируете рабочий лист, когда в нем содержится именованная формула с областью действия на уровне рабочей книги, а рабочий лист копируется в другую рабочую книгу, именованная формула также копируется в другую рабочую книгу. Однако именованная формула ссылается обратно на рабочую книгу, из которой она была скопирована.
При создании динамического диапазона мы создали с помощью функции ИНДЕКС имя [lstТовары]. Затем рабочий лист был скопирован в другую рабочую книгу, и эта именованная формула была скопирована вместе с ней, но со ссылкой на рабочую книгу [Динамический диапазон.xlsx].
Это очень плохое поведение. Будьте осторожнее с внешними ссылками при копировании листов в рабочую книгу. Такое происходит только с именами с областью на уровне рабочей книги.
В следующий раз мы поговорим про присвоение имён константам.
Не переключайтесь;)