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

Изучите лист, показанный на рис. 91.1. Этот лист содержит список продаж за месяц, через май.

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

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

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

1. Выберите Формулы ► Определенные имена ► Присвоить имя, чтобы открыть диалоговое окно Создание имени.
2. Введите SalesData в поле Имя.
3. Введите следующую формулу в поле Диапазон (рис. 91.2): =СМЕЩ(Лист1!$В$1;0:0;СЧЁТЗ(Лист1!$В:$В);1).
4. Нажмите ОК, чтобы закрыть окно Создание имени.

Рис. 91.2. Создание динамически именованной формулы

В предыдущих шагах создается именованная формула, использующая функции СМЕЩ и СЧЁТЗ, чтобы вернуть диапазон, который изменяется, основываясь на количестве непустых ячеек в столбце В. Чтобы проверить ее, введите следующую формулу в любую ячейку не из столбца В: =CУMM(SalesData).

Эта формула возвращает сумму значений в столбце В. Чтобы убедиться, что это название относится только к непустым ячейкам, попробуйте такую формулу: =ЧCTP0K(SalesData). Формула возвращает 5 — количество строк в диапазоне. Добавьте новые данные или удалите их в конце столбца, и вы увидите, что формула под именем SalesData пересчитается соответствующим образом. Заметьте, что SalesData не отображается в поле Имя и не появляется в диалоговом окне Переход. Однако вы можете открыть это окно и набрать SalesData для выбора диапазона.

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

На данный момент вы можете задуматься о значении этого задания. В конце концов, простая формула наподобие следующей выполняет ту же самую работу без необходимости задавать для нее имя: =СУММ(В:В). Один из наиболее распространенных видов применения динамически именованной формулы состоит в настройке данных, которые будут использоваться в диаграмме. Вы можете применять этот метод для создания диаграммы с наборами данных, которые регулируются автоматически при вводе новых данных: =Лист1!$Е$1:$К$490.

Начиная с Excel 2007 данные диаграммы регулируются автоматически, если идут в виде таблицы (созданной с помощью команды Вставка ► Таблицы ► Таблица), но создание динамических имен полезно, если вы хотите, чтобы диаграмма постоянно изменяла данные, не находящиеся в таблице. Более подробную информацию о диаграммах вы найдете в соответствующем разделе нашего сайта.