Допустим, у нас есть лист, который генерирует счет и рассчитывает налог на добавленную стоимость — НДС. Обычно в этом случае значение налоговой ставки вводится в ячейку, а затем формулы используют ссылку на эту ячейку. Чтобы упростить процесс, вы можете дать этой ячейке имя, например НДС. Или вы можете обойтись без ячейки, сохранив значение налоговой ставки в именованной константе.
Рис. 1. Определите имя, которое относится к константе
Действуйте следующим образом (рис. 1):
Вы создали именованную формулу, в которой не используются ссылки на ячейки. Попробуем вставить в любую ячейку следующую формулу: = НДС. Эта простая формула возвращает 0,18. Поскольку эта именованная формула всегда возвращает одну и ту же сумму, ее можно рассматривать как именованную константу. Эту константу можно использовать в более сложной формуле, например = A1 * НДС.
Именованная константа также может состоять из текста. Например, название компании можно указать как константу. Например, в диалоговом окне «Создать имя» можно ввести следующую формулу с именем MSFT: = «Microsoft Corporation».
После этого вы можете использовать формулу ячейки: = «Годовой отчет:» & MSFT. Эта формула возвращает текст Годовой отчет: Microsoft Corporation).
Имена, не относящиеся к диапазонам, не отображаются в диалоговых окнах «Имя» или «Перейти» (окно «Перейти» открывается при нажатии клавиши F5). Это разумно, потому что эти константы нигде не доступны в интерфейсе. Однако они появляются в диалоговом окне «Вставить имя» (открывается при нажатии F3), а также в раскрывающемся списке, используемом при создании формулы (рис. 2; при вводе формулы введите букву H, и Excel спросит вас). Это также имеет смысл, поскольку именованные константы необходимы специально для использования в формулах.
Рис. 2. Именованная константа доступна для использования в формулах
Как вы уже догадались, постоянное значение можно изменить в любое время, открыв диалоговое окно Диспетчера имен (Формулы -> Определенные имена -> Диспетчер имен). Нажмите кнопку «Изменить» внутри, чтобы открыть окно «Изменить имя». Затем введите новое значение в поле «Диапазон». Когда вы закроете это окно, Excel будет использовать новое значение и пересчитать формулы, использующие это имя.
Адаптировано из книги Джона Уокенбаха. Excel 2013. Советы и рекомендации. — СПб .: Пьетро, 2014 — С. 112, 113.
Это простой, но интересный метод, который позволяет заменять данные из небольших таблиц вообще без использования ячеек. Его суть в том, что вы можете «вшить» серию заменяющих значений прямо в формулу. Рассмотрим несколько способов сделать это.
Если вам нужно заменить данные из одномерного массива на число, вы можете использовать функцию ИНДЕКС или ее более простую и подходящую функцию, в данном случае аналог — функцию ВЫБОР. Отображает элемент массива по порядковому номеру. Так, например, если нам нужно вывести название дня недели с его номером, мы можем использовать следующую конструкцию
Это простой пример для начала, чтобы понять идею о том, что справочная таблица может быть встроена непосредственно в формулу. Теперь давайте посмотрим на более сложный, но более симпатичный пример.
Предположим, у нас есть список городов, в которых с помощью функции ВПР подставляем значения коэффициентов заработной платы во второй столбец желтой таблицы справа:
Уловка заключается в том, что вы можете заменить ссылку на диапазон таблицей $ E $ 3: $ F $ 5 массивом констант в формуле, и правая таблица больше не понадобится. Чтобы не вводить данные вручную, можно проделать небольшую хитрость.
Выберите пустую ячейку. Введите с клавиатуры знак «равно» и выберите диапазон с таблицей — его адрес должен появиться в строке формул:
Выделите мышкой ссылку E3: F5 в строке формул и нажмите клавишу F9 — ссылка превратится в массив констант:
Осталось скопировать получившийся массив и вставить его в нашу формулу с помощью ВПР, а саму таблицу удалить за ненадобностью:
Развивая идею предыдущего метода, вы можете попробовать другой вариант — создать именованный массив констант в оперативной памяти, который затем можно будет использовать в формуле. Для этого на вкладке «Формулы» нажмите кнопку NameManager. Затем нажмите кнопку New, подумайте и введите имя (пусть будет, например, City) и в поле Reference вставьте массив констант, скопированный в предыдущем методе:
Нажмите ОК и закройте Диспетчер имен. Теперь добавленное имя можно безопасно использовать на любом листе книги в любой формуле, например в нашей функции ВПР:
Компактный, красивый и в какой-то мере защищает от озорных ручек непрофессионалов
