Область видимости имени на листе Excel

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

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

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

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

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

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

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

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

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

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

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

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

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