Область видимости имени диапазона ячеек на листе Excel

Excel позволяет создавать имена с областью видимости в приделах 1-го листа или целой книги. По умолчанию имя доступно в приделах целой книги. Это в большинстве случаем удобно, когда одно значение имени можно использовать на разных листах. Но иногда требуется ограничить область действия имени. Для этого в Excel присутствует возможность создавать имена доступ, к которым можно получить только из одного листа.

Пример необходимости ограничить область видимости имени диапазона

Главная необходимость ограничить область видимости имени диапазонов – это инкапсуляция формул в Excel. Например, если копируем лист формулы с именами не должны нарушать логику алгоритмов вычисления в таблицах Excel. А также нельзя допускать ошибок при копировании формул с именами.

Допустим имеется отчет о доходах, расхода и прибыли за каждый квартал 2023-го года. Для суммирования итоговых значений формулы используют ссылки на именные диапазоны в своих аргументах. При этом области видимости всех именных диапазонов как по умолчанию – вся «Рабочая книга»:

Мы копируем лист для заполнения данных за 2024 чтобы не создавать заново всю структуру отчета со всеми его формулами. Мы скопировали лист, переименовали его в «2024» и заполнили новыми данными всю структуру отчета:

В результате все итоговые значения не изменились и выдают неверный итоговый результат.

Определим имя, так чтобы была возможность сконструировать структуру книги соответственно следующим требованиям:

Прибыль, как известно – это доход минус расход. Ежегодно мы формируем отчеты, в которых подытоживаем обще-годовую прибыль. Для этого мы вычитаем расходы из доходов отдельно по каждому кварталу, а потом суммируем результат.



Пока мы анализируем лист с данными за конкретный год, нас не интересуют другие года. Например, формула: =СУММ(расходы) для нас ассоциируются с расходами текущего года данного листа.

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

Если-бы действия имени «расходы» распространялось на все листы, то его нельзя было-бы использовать на других листах не допустив ошибки. А если создавать для каждого нового листа свое новое имя, тогда после копирования листа нужно редактировать каждую формулу для удаления старых и вставки новых имен в аргументы.

Лучшее решение в данной ситуации – это ограничить распространения действия имени в пределах одного листа.

Сначала создадим пример отчета, в котором уже готовые все формулы, но данные еще незаполненные. Пример послужит нам шаблоном. Для удобства область заполнения данных выделим цветным фоном. И так:

1. На листе: «Пример отчета» выделите диапазон B2:B5 и выберите инструмент: «Формулы»-«Определенные имена»-«Присвоить имя». В появившемся окне создаем имя «Доход» ограниченным в пределах одного листа заполнив поля, так как на рисунке:

2. Аналогичным способом создаем еще 2 имени: «Расход» и «Прибыль» для диапазонов C2:C5 и D2:D5 соответственно. Эти 2 имени так же должны иметь распространение действия в пределах 1-го листа. А в поле «Диапазон» окна «Создание имени», так же должны быть абсолютные ссылки на их диапазоны.
3. Не забудьте заполнить диапазон D2:D5 формулами. Так же введите формулы в ячейки B6, C6 и D6 для получения итоговых значений.

4. Скопируйте лист «Пример отчета» и присвойте название для копии «2008г.». О том, как скопировать лист вы уже знаете из урока: Создание, копирование и перемещение листов в книгах Excel
5. Новый лист «2008г» заполните данными и выберите инструмент: «Формулы»-«Определенные имена»-«Диспетчер имен».

Как видно в диспетчере имен Excel автоматически создал новые имена с распространением действия в пределах одного листа.

Полезный совет. Лист «Пример отчета» в данной книге хорошо использовать в качестве шаблона для ежегодных отчетов. Поэтому лучше защитить его формулы от изменения. Оставить доступными для редактирования только ячейки диапазонов ввода данных B2:C5. О ток как защитить формулы от случайного изменения читайте в уроке: Защита листа и ячеек в Excel.

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