Как создать именованную константу в excel

Именованная формула в EXCEL

history 21 января 2013 г.
    Группы статей

  • Имена
  • Месяц
  • Настройка формул

Обычно формулы непосредственно вводятся в ячейки, но можно, предварительно присвоив формуле имя, использовать в ячейке ее имя. Какие преимущества дает именованная формула – читайте в этой статье.

Назовем в MS EXCEL Именованной, формулу, которой присвоено имя . Частным случаем именованной формулы является Именованный диапазон . В этой статье ограничимся вычислительными примерами (см. Файл примера ): именованная_формула.xlsx

Присвоение имен константам

При научных расчетах часто используются довольно громоздкие константы, например 3*Ln(2*ПИ)*sin(ПИ/2), 5*cos(2*ПИ), e ПИ и др. При частом использовании таких констант имеет смысл присвоить им имя .

  • на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя ;
  • в поле Имя введите, например: LnPie ;
  • в поле Область выберите Книга (если требуется, чтобы имя работало во всей книге, а не на отдельном листе);
  • в поле Диапазон введите формулу =3*LN(2*КОРЕНЬ(ПИ()))*ПИ()^EXP(1)
  • нажмите ОК.

Теперь в любой ячейке книги можно ввести формулу = LnPie , в результате в ячейке получим число 85,26701.

Конечно, можно предварительно вычислить константу, ввести число 85,26701 в ячейку, а затем просто ссылаться на нее в формулах. Именно так и делает подавляющее большинство пользователей. Недостаток этого – значение из ячейки можно случайно удалить. Из Диспетчера имен это сделать труднее.

Присвоение имен массивам констант

Массиву констант также можно присвоить осмысленное имя.

  • на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя ;
  • в поле Имя введите, например: День_недели ;
  • в поле Область выберите Книга (если требуется, чтобы имя работало во всей книге, а не на отдельном листе);
  • в поле Диапазон введите формулу = <1;2;3;4;5;6;7>(числа разделены точкой с запятой);
  • нажмите ОК.

Теперь, выделив любой горизонтальный диапазон из 7 ячеек, в Строке формул можно ввести формулу =День_недели нажать CTRL+SHIFT+ENTER . В результате, выделенные ячейки заполнятся значениями от 1 до 7 .

Пример использования именованных массивов констант приведен ниже.

Присвоение имен формулам (без аргументов)

В качестве еще одного примера именованной формулы создадим формулу для формирования календаря.

В ячейку B13 введите начальную дату. Календарь будет отображать месяц, которому принадлежит начальная дата.

Итак, приступим. В дополнение к именованному массиву констант из предыдущего примера ( День_недели ) создадим еще один массив констант (теперь вертикальный):

  • на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя ;
  • в поле Имя введите Номер_недели ;
  • в поле Область выберите Книга (если требуется, чтобы имя работало во всей книге, а не на отдельном листе);
  • в поле Диапазон введите формулу = <0:1:2:3:4:5>(числа разделены двоеточиями);
  • нажмите ОК.

Теперь создадим именованную формулу Календарь. Для этого:

  • на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя ;
  • в поле Имя введите, например: Календарь ;
  • в поле Область выберите Книга (если требуется, чтобы имя работало во всей книге, а не на отдельном листе);
  • в поле Диапазон введите формулу =Номер_недели*7+День_недели
  • нажмите ОК.

Теперь, для размещения нашего календаря на листе, выделим диапазон ячеек: 6 строк х 7 столбцов ( B 15: H 20 ). В Строке формул введем формулу массива = Календарь+B13-ДЕНЬНЕД(B13;2) и нажмем CTRL+SHIFT+ENTER.

Для придания календарю привычной формы нужно слегка изменить формулу: = ЕСЛИ(МЕСЯЦ(Календарь+B13-ДЕНЬНЕД(B13;2))=МЕСЯЦ(B13); Календарь+B13-ДЕНЬНЕД(B13;2);"")

Как видим, использование именованной формулы Календарь повышает наглядность и уменьшает длину формулы.

Присвоение имен формулам (с «аргументами»)

Предположим, что мы хотим создать именованную формулу НДС18 для вычисления НДС 18%. Именованной формуле невозможно напрямую передать аргумент, т.е. нельзя написать, что-то типа =НДС18($А$1). Для этого необходимо написать пользовательскую функцию на VBA, но здесь мы этим заниматься не будем.

Способ передачи аргументов именованной формуле другой: при создании имени для формулы, в поле Диапазон указывается относительная ссылка на ячейки, содержащие значения — аргументы (см. статью Именованный диапазон ). Следствием этого является определенное ограничение на размещение именованной формулы на листе. Поясним на примере.

Предположим, необходимо вычислить НДС 18% у значений, расположенных в диапазоне A23:A26 (см. файл примера ). Предположим, что нам не хочется запоминать формулу =А23/118*18 , а хочется написать =НДС18 и получить результат. Для этого:

  • выделите ячейку B23 , в которой будет находиться вычисленное значение НДС из ячейки A23 (при использовании относительной адресации важно четко фиксировать нахождение активной ячейки в момент создания имени );
  • на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя ;
  • в поле Имя введите: НДС18 ;
  • в поле Область выберите Книга (если требуется, чтобы имя работало во всей книге, а не на отдельном листе);
  • в поле Диапазон введите формулу =Лист1!$A23/118*18
  • нажмите ОК.

Мы использовали смешанную адресацию $A23 . Такая адресация позволяет находить НДС у значений расположенных на той же строке, в ячейке слева от формулы =НДС18 .

Если именованную формулу =НДС18 ввести, например, в ячейку D30 , то НДС будет вычислен у значения, расположенного в соседней ячейке слева, т.е. в С30 .

Создание и использование именованных констант

Описание проблемы

Как правило, мы часто пользуемся различными константами. Например, ставка НДС, налог на прибыль, различные математические константы и прочее. Как мы поступаем, чтобы их использовать? Мы берем отдельный лист, вносим туда эти константы и ссылаемся на них в лучшем случае закрепляя при этом ссылки на эти ячейки.

Минусы данного способа очевидны:

  • Сложность набора формул, так как приходится делать ссылку на ячейку в другом листе.
  • Сложность восприятия и чтения таких формул. В случае проверки приходится проходить по ссылкам прыгая с листа на лист.
  • Большое количество констант загромождает лист и он также становится трудно читаем.

Решение

Избавится от этого можно путем создания именованных констант. Например, создадим константу под именем НДС. Для этого:

  1. Перейдите на вкладку Формулы. Найдите группу Определенные имена и выберите команду Присвоить имя. Откроется диалоговое окно Создание имени.

  1. Введите имя константы в поле Имя. В нашем случае это НДС.
  2. В поле Область следует указать значение Книга. В этом случае выбранная переменная будет доступна на всех листай файла Excel. Если необходимо, чтобы константа работала только на конкретном листе, следует выбрать название нужного листа.
  3. В поле Примечание можно указать любой текст при необходимости.
  4. В поле Диапазон введите значение константы =18% не ссылаясь на ячейки и нажмите кнопку ОК.

Создание именованной константы в Excel

Теперь на любом листе формула =НДС будет выдавать одинаковый результат 0,18. Теперь давайте используем нашу переменную и посчитаем, например, стоимость товара без НДС (пусть стоимость с НДС будет 18000). Для этого введем в ячейку следующую формулу =18000/НДС. В итоге ячейка примет значение 10 000. Согласитесь, что формула стала намного понятней, нежели вместо НДС была указана ссылка на некую ячейку (да еще на другом листе).

Вставка именованной константыЧтобы вставить именованную константу в формулу не обязательно помнить ее точный синтаксис. Для удобства клавишей F3 можно вызвать список констант и выбрать ее из формы Вставка имени. Также это окно можно вызвать перейдя на вкладку Формулы -> Определенные имена -> Использовать в формуле.

В качестве именованных переменных можно задавать не только числовые значения, но также и текстовые, даты.

Значения констант можно менять по ходу работы. Для этого необходимо открыть окно диспетчера имен Формулы -> Определенные имена -> Диспетчер имен.

Диспетчер имен в Excel

Выберите переменную и нажмите кнопку Изменить. Введите новое значение переменной, закройте диспетчер имен. Excel заново пересчитает все все значения связанные с данной константой.

Ссылка на основную публикацию