Именованная формула в MS 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 столбцов (B15:H20). В Строке формул введем формулу массива =Календарь+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.

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 4 (9 оценок)