В excel есть возможность присвоения имен ячейкам, таблицам и диапазонам. После присвоения, эти имена можно использовать в формулах, при этом формулы становятся намного понятнее пользователю.
Присвоение имени константе
Имена можно присваивать и константам, которых даже нет в вашем файле. Например, если в своем прогнозе вы используете рост рынка в размере 5% в следующем году, то этому росту можно присвоить имя. Для этого перейдите на ленте во вкладку Формулы -> Определенные имена -> Задать имя:
Откроется диалоговое окно "Создание имени", в котором необходимо ввести Имя, Область действия этого имени (лист, книга), Примечание (чтобы не забыть, что это за константа), в поле Диапазон необходимо ввести число и нажать ОК:
Теперь эту константу можно использовать во всей книге при расчете формулы, например так:
Обратите внимание, теперь значительно проще понять как был сформирован объем продаж в 2021 году.
Важно: константой может быть не только число, но и текст. Таким образом вы можете упростить ввод часто повторяющихся слов.
Присвоение имени ячейке и формуле
Порядок присвоения имени ячейке и формуле не сильно отличается от предыдущего примера. Перейдите на ленте в блок Задать имя и в диалоговом окне "Создание имени" на последнем шаге вместо ввода числа введите либо ссылку на ячейку, либо формулу:
Если вы хотите, чтобы ваша именованная формула при расчете использовала значение из ячейки, находящейся левее (например, A4) от той, в которой формула будет находиться (B4), обязательно перед созданием имени активируйте ячейку B4 и не используйте абсолютные ссылки.
Подробнее об абсолютных и относительных ссылках можете прочитать в статье Абсолютные и относительные ссылки в Excel.
Присвоение имени диапазону
В присвоении имени диапазону нет ничего сложного, просто в окне Создание имени в поле Диапазон пропишите ссылку на него. Интереснее дела обстоят с использованием пересечения диапазонов (это те ячейки, которые являются общими для двух диапазонов). Предположим, есть таблица с объемом продаж трех менеджеров за 4 квартала:
Выделите эту таблицу, перейдите на ленте во вкладку Формулы -> Определенные имена -> Создать из выделенного:
В открывшемся диалоговом окне можно оставить галки там, где они стоят и нажмите ОК:
В итоге мы получим 7 автоматически созданных диапазонов.
Про ограничениях при создании именованных диапазонов - тут
Excel: тайные правила именованных диапазонов. Что нельзя использовать в названии диапазона
Не трудно догадаться, что это столбцы с объемом продаж по кварталам и строки с менеджерами (все созданные имена можно посмотреть на ленте во вкладке Формулы -> Определенные имена -> Диспетчер имен):
Теперь если вы введете в ячейку формулу: =Иванов _2_квартал , то она выведет нужное значение на пересечении этой фамилии и столбца со вторым кварталом:
Важно: если вы сначала рассчитали часть значений, а потом создали имена - автоматически эти имена в формулах не появятся. Однако, это можно сделать, выполнив команду Формулы -> Определенные имена -> Задать имя -> Применить имена.
Подписывайтесь, чтобы первыми получать самые интересные публикации! Ещё больше полезных советов и лайфхаков Excel – в моем Telegram-канале!
Для удобства - статья навигатор по каналу: Путеводитель по каналу