Excel создание имен формул

В Excel в Интернете вы можете использовать именованные диапазоны, которые вы определили в Excel для Windows или Mac. Выберите имя в поле «Имя» для перехода к расположению диапазона или использования именованного диапазона в формуле.

На данный момент создание нового именованного диапазона в 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

.

Обилие числовых констант, безымянных диапазонов и неочевидных математических формул делает документ Excel трудным для понимания. К счастью, вы можете присвоить любой константе, формуле или диапазону ячеек удобное символическое имя, пригодное для дальнейшего использования.

Проделайте следующие шаги для наименования объекта:

1. Выберите вкладку Формулы ленты инструментов, далее пункт Присвоить имя. Вы увидите диалоговое окно присвоения имени — см. рис. 1.5.
2. В поле Имя введите желаемое имя для использования.
3. В поле Диапазон введите необходимую константу, формулу или имя диапазона. Обратите внимание, что по умолчанию поле содержит имя текущего выделенного диапазона.
4. Нажмите на кнопку ОК.

Теперь вы можете использовать созданное имя объекта вместо непосредственного его ввода. Например, если вам необходимо вычислить объем сферы, вы будете использовать следующую формулу: V = 4/π*r3/3, (где r — радиус сферы). Далее, если присвоить некоторой ячейке имя Радиус, вы можете создать формулу с именем ОбъемСферы. В поле Диапазон диалогового окна присвоения имени следует ввести формулу вычисления объема: =(4*ПИ()*Радиус^3)/3.

Функция ПИ() в Excel возвращает значение р.

Работа с именами в формулах: вставка имен в формулы

Одним из способов вставки имени в формулу является просто ввод его в строку ввода формулы. Но что если вы не можете вспомнить необходимое имя? Или что если имя слишком длинное, и вы можете ошибиться при его вводе? Для таких ситуаций в Excel предусмотрены различные вспомогательные средства. Итак, начните вводить формулу, и когда вы дойдете до момента ввода имени, воспользуйтесь одной из следующих методик.

1. Перейдите на вкладку Формулы ленты инструментов, далее нажмите на кнопку Использовать в формуле, и затем из раскрывающегося списка вы сможете выбрать необходимое имя.
2. Перейдите на вкладку Формулы ленты инструментов, далее нажмите на кнопку Использовать в формуле и выберите последний пункт Вставить имена… Вы увидите диалоговое окно вставки имен, показанное на рис. 1.7. Также вы можете использовать клавишу F3 для вызова данного окна.
3. При вводе первых букв имени Excel автоматически предложит вам использовать подходящие созданные имена. При этом переместитесь на необходимое имя и нажмите на клавишу Tab клавиатуры.

Применение имен к формулам

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

1. Выберите несколько ячеек, если вы хотите применить имена к ним, или одну ячейку, если вы хотите применить созданные имена ко всему листу.
2. Перейдите на вкладку Формулы ленты инструментов, далее раскрывающееся меню справа от кнопки Присвоить имя и далее из меню пункт Применить имена… Вы увидите диалоговое окно Применение имен (см. рис. 1.8).
3. Выберите имя или имена для применения.
4. Поставьте галочку Игнорировать тип ссылки для игнорирования относительной и абсолютной связи при присвоении имен.
5. Поставьте галочку Использовать имена строк и столбцов для указания Excel использовать символические имена строк и столбцов (если это возможно) для указания отдельных ячеек.
6. Нажмите ОК для присвоения имен.

Игнорирование типа ссылки

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

Например, если формула содержит выражение =СУММ(А1:А10), а имя Продажи присвоено диапазону $A$1:$А$10. В случае выключения флажка Игнорировать тип ссылки, Excel не применит имя к диапазону в ячейке; имя Продажи указывает на абсолютный диапазон, в то время как формула в ячейке содержит относительное значение.

Перейти к содержанию

В предыдущей статье были перечислены виды имен, которые можно использовать в редакторе Excel и правила, которыми при их создании следует руководствоваться. Теперь рассмотрим самое интересное и полезное – варианты и инструменты создания имен, а также, как их применять.

Присвоение имени диапазону ячеек

Допустим, есть столбец таблицы с информацией о доходах организации за полугодие. Используем это и диапазону, который он занимает: D5:D60 присвоим имя Доходзаполугодие. Вот несколько вариантов создания имени для заданного диапазона:

1. При помощи команды Создать из выделенного фрагмента:

• Выделяем в столбце таблицы диапазон ячеек D5:D60 в блок;
• В области Определенные имена на вкладке Формулы ленты меню нажимаем кнопку Создать из выделенного (или нажимается комбинация клавиш Ctrl+Shift+F3);
• Если имя берется из заголовка столбца, то в выпадающем диалоговом окне ставим галочку напротив пункта В строке выше;
  нажимаем кнопку ОК.

Правильно ли Вы создали имя и какому диапазону оно присвоено, можно проверить через окно Диспетчер имен, которое вызывается одноименной кнопкой из этой же области.

2. Создание имени через поле Имя:

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

• Выделяем диапазон ячеек D5:D60;
• В поле Имя вводим имя Доходзаполугодие, заменив отображаемый там адрес первой ячейки диапазона;
• Подтверждаем ввод нажатием клавиши Enter. Все, имя создано!

3. Создание имени через команду Присвоить имя:

Для создания имени через кнопку Присвоить имя необходимо:

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

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

Присваивать имена константам или формулам имеет смысл, если они часто употребляются. Например, есть коэффициент трения сталь-сталь, имеющий значение 0,15. Использовать просто цифру не практично, можно скоро забыть, что это за 0,15 там встречается. Поэтому будем использовать более информационное имя Коэф_тр_стали.

Как создать имя для константы?

Делаем следующие шаги:

• В области Определенные имена на вкладке Формулы ленты меню нажимаем кнопку Присвоить имя;
• В открывшемся окне Создание имени заполняем поля, как приведено на рисунке, введя значение константы в поле Диапазон;
• С нажатием на кнопку ОК получаем имя, которое можно подставлять в формулы.

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

Как удобнее подставлять имя в формулу Excel?

Допустим, имя Коэф_тр_стали нужно вставить в некое выражение =Коэф_тр_стали*5.

Тут порядок действий следующий:

• Ставим курсор в нужную ячейку;
• Вводим знак равенства «=»;
• В области Определенные имена на вкладке Формулы ленты меню нажимаем кнопку Присвоить имя;
• В области Определенные имена на вкладке Формулы ленты меню нажимаем кнопку выпадающего списка Использовать в формуле;
• Из списка имен выбираем нужное, после чего оно появляется в формуле;
• Завершаем построение выражения введя символы “*5”.

Присваивание имен таблицам

Отдельно стоит поговорить об именах таблиц. Начиная с версии MS Office 2007 таблицы правильно создавать через команду Таблица, расположенную в области Таблицы вкладки Вставка. При этом Excel автоматически присваивает таблице имя: Таблица1 или Таблица2 и т.д., но его можно изменить через Конструктор таблиц, чтобы сделать более выразительными.

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

Немного в стороне от таблицы введем формулу =СУММ(Товар[Стоимость]). По мере ввода формулы редактор Excel даже предложит выбрать среди других имен формул и имя таблицы, и имя столбца. В итоге мы получим сумму по столбцу Стоимость.

Ссылки вида Имя_Таблицы[Имя_столбца] называются Структурированными ссылками.

Что очень важно, именуются таблицы не только для использования в формулах. Присваивая имя таблице, мы получаем то самое имя, по которому к этой таблице, а значит и к ее данным, можно обратиться извне. Точно так же, как можно обратиться к базе данным! Это позволяет строить запросы на выборку информации из этой таблицы, анализировать ее и обрабатывать, создавая новые таблицы с новой информацией. Это довольно большая тема, поэтому она будет рассмотрена в дальнейших статьях.

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

• имена
  могут содержать не более 255 символов;

• имена
  должны начинаться с буквы и могут
  содержать любой символ, кроме пробела;

• имена
  не должны быть похожи на ссылки, такие,
  как ВЗ, С4;

• имена
  не должны использовать функции Excel,
  такие, как СУММ,
  ЕСЛИ и
  т. п.

В
меню Вставка,
Имя существуют
две различные команды создания именованных
областей: Создать
и Присвоить.

Команда Создать позволяет
задать (ввести) требуемое имя ( только
одно ),
командаПрисвоить использует
метки, размещенные на рабочем листе, в
качестве имен областей (разрешается
создавать сразу
несколько имен )

Упражнение

Создание
имени

1.
Выделите ячейку В1 (табл. 1 предыдущей
страницы).

2.
Выберите в меню Вставка,
Имя команду Присвоить .

3.
Введите имя Часовая
ставка и
нажмите ОК .

4.
Выделите ячейку В1 и убедитесь, что в
поле имени указано Часовая
ставка .

Упражнение

Создание
нескольких имен

1.
Выделите ячейки ВЗ:С5 (табл. 2 предыдущей
страницы).

2.
Выберите в меню Вставка,
Имя команду Создать ,
появится диалоговое окноСоздать
имена (рис.
1).

3.
Убедитесь, что переключатель в столбце
слева помечен и нажмите ОК .

4.
Выделите ячейки ВЗ:СЗ и убедитесь, что
в поле имени указано Иванов.

Рис.
1. Диалоговое окно Создать имена

Можно
в формулу вставить имя вместо абсолютной
ссылки.

1.
В строке формул установите курсор в то
место, где будет добавлено имя.

2.
Выберите в меню Вставка,
Имя команду Вставить, появится
диалоговое окноВставить
имена.

3.
Выберите нужное имя из списка и нажмите ОК.

Ошибки в формулах Excel

Если
при вводе формул или данных допущена
ошибка, то в результирующей ячейке
появляется сообщение об ошибке. Первым
символом всех значений ошибок является
символ #.
Значения ошибок зависят от вида допущенной
ошибки.

Excel
может распознать далеко не все ошибки,
но те, которые обнаружены, надо уметь
исправить.

Ошибка #### появляется,
когда вводимое число не умещается в
ячейке. В этом случае следует увеличить
ширину столбца.

Ошибка #ДЕЛ/0! появляется,
когда в формуле делается попытка деления
на нуль. Чаще всего это случается, когда
в качестве делителя используется ссылка
на ячейку, содержащую нулевое или пустое
значение.

Ошибка #Н/Д! является
сокращением термина «неопределенные
данные». Эта ошибка указывает на
использование в формуле ссылки на пустую
ячейку.

Ошибка #ИМЯ? появляется,
когда имя, используемое в формуле, было
удалено или не было ранее определено.
Для исправления определите или исправьте
имя области данных, имя функции и др.

Ошибка #ПУСТО! появляется,
когда задано пересечение двух областей,
которые в действительности не имеют
общих ячеек. Чаще всего ошибка указывает,
что допущена ошибка при вводе ссылок
на диапазоны ячеек.

Ошибка #ЧИСЛО! появляется,
когда в функции с числовым аргументом
используется неверный формат или
значение аргумента.

Ошибка #ССЫЛКА! появляется,
когда в формуле используется недопустимая
ссылка на ячейку. Например, если ячейки
были удалены или в эти ячейки было
помещено содержимое других ячеек. 

Ошибка
#ЗНАЧ! появляется,
когда в формуле используется недопустимый
тип аргумента или операнда. Например,
вместо числового или логического
значения для оператора или функции
введен текст.

Кроме
перечисленных ошибок, при вводе формул
может появиться циклическая ссылка.

Циклическая
ссылка возникает
тогда, когда формула прямо или косвенно
включает ссылки на свою собственную
ячейку. Циклическая ссылка может вызывать
искажения в вычислениях на рабочем
листе и поэтому рассматривается как
ошибка в большинстве приложений. При
вводе циклической ссылки появляется
предупредительное сообщение (рис. 1).

Рис.
1. Циклическая ссылка

Индикатор
циклической ссылки в строке состояния
показывает «ссылку» на последнюю
зависимую формулу.

Когда
формула, содержащая циклическую ссылку,
находится на рабочем листе, появится
сообщение, указанное на рис. 1.

Для
исправления ошибки удалите ячейку,
которая вызвала циклическую ссылку,
отредактируйте или введите заново
формулу.

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

• #
• #
• #
• #
• #
• #
• #
• #
• #
• #
• #