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

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

Назовем в 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 и получить результат. Для этого:

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

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

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