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

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

Благодаря этому важные константы можно хранить в одном месте, а сложным вычислениям присваивать короткие понятные имена.

• TaxRate может быть равно числу 0.18.
• GrandTotal может содержать формулу SUM, которую вы повторно используете повсюду.

Чтобы сохранить значение под именем в Экселе, откройте диспетчер имен на вкладке «Формулы» и нажмите Создать.

• Введите имя, например TaxRate.
• В поле Ссылается на введите =0.18.
• Нажмите OK.

Обратите внимание: значение вводится со знаком равенства в начале. Теперь TaxRate ведет себя как ячейка со значением 0.18, но хранится в определениях книги, а не в видимой ячейке.

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

• Введите 0.18 в свободную ячейку, например Z1.
• Выделите Z1 и откройте Данные > Именованные диапазоны.
• Присвойте ей имя TaxRate.

Результат тот же: формула =Price*TaxRate работает везде, а ставку можно изменить, отредактировав одну ячейку.

После определения TaxRate формулы становятся понятнее. Чтобы добавить к цене в A2 налог 18%, используйте:

=A2*(1+TaxRate)

Главное преимущество проявляется при изменении ставки. Измените TaxRate в одном месте, и каждая формула книги мгновенно пересчитается.

• Не нужно искать жестко заданные значения 0.18.
• Нет риска пропустить одно из них и получить несогласованные числа.

Жесткая запись значения вроде 0.18 непосредственно в десятках формул создает проблемы при обслуживании.

• Если ставка изменится на 20%, придется найти и отредактировать каждую формулу.
• Если пропустить хотя бы одну, итоги незаметно разойдутся.
• Читатели не смогут понять, означает ли 0.18 налоговую ставку, скидку или опечатку.

Именованная константа, например TaxRate, решает все три проблемы: это единый четко обозначенный источник истины.

Имя также может хранить целую формулу. В диспетчере имен Экселя создайте имя вроде SalesTotal и укажите в поле Ссылается на:

=SUM(Sheet1!$B$2:$B$13)

Теперь ввод =SalesTotal в любую ячейку возвращает эту сумму. Вы заключили вычисление в одно понятное имя.

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

Предположим, вы часто вычисляете маржу прибыли. Определите две именованные формулы:

• TotalRevenue → =SUM(Revenue)
• TotalCost → =SUM(Costs)

Тогда формула маржи выглядит так:

=(TotalRevenue-TotalCost)/TotalRevenue

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

Продвинутый прием: если оставить ссылки относительными в именованной формуле, имя будет вести себя по-разному в зависимости от места использования.

Например, имя PrevRow, определенное при активной ячейке A2 как =A1, всегда будет указывать на ячейку непосредственно выше той, в которой вы введете =PrevRow.

• Это удобно для повторяющихся шаблонов в столбце.
• Но отладка сложнее — используйте этот прием только после того, как поймете правило активной ячейки.

По мере роста книги список именованных констант превращается в аккуратную панель настроек. Часто имеет смысл дать имена следующим значениям:

• TaxRate, DiscountRate, ShippingFee
• CompanyName как текстовой константе вроде ="Acme Inc"
• StartDate, содержащей фиксированную дату

Если сгруппировать их в диспетчере имен, вы получите единую панель управления всеми допущениями модели.

Чтобы изменить именованную константу в Экселе, откройте диспетчер имен, выберите имя и отредактируйте поле Ссылается на. Нажмите Ввод, и каждая зависимая формула пересчитается.

В Гугл Таблицах просто отредактируйте исходную ячейку, которой вы присвоили имя.

• Одно изменение распространяется на всю книгу.
• В этом и заключается главное преимущество: единый источник истины.

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

Важно различать эти два понятия:

• Именованная константа указывает на фиксированное значение, например =0.18 или ="Acme Inc". Она не изменяется, пока вы не отредактируете определение.
• Именованная формула задает вычисление, например =SUM(Revenue). Она пересчитывается при изменении входных данных.

Оба варианта создаются одинаково в диспетчере имен — различается только содержимое поля Ссылается на.

Вы определили имя TaxRate, которое указывает на =0.18. В чем главное преимущество по сравнению с вводом 0.18 в каждую формулу?

Вы узнали, что имена могут хранить не только диапазоны:

• Именованная константа, например TaxRate=0.18, хранит допущение в одном месте.
• Именованная формула, например SalesTotal=SUM($B$2:$B$13), заключает вычисление в понятное имя.
• Измените определение один раз, и все зависимые формулы обновятся.
• Используйте абсолютные ссылки в именованных формулах, чтобы они оставались зафиксированными.

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