Создание и использование именованных диапазонов

Именованный диапазон позволяет присвоить понятное имя ячейке или блоку ячеек. Вместо того чтобы запоминать, что данные о продажах находятся в B2:B13, можно просто назвать эту область Sales.

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

• =SUM(B2:B13) превращается в =SUM(Sales)
• Имена работают и в Экселе, и в Гугл Таблицах

Именованные диапазоны решают три повседневные проблемы:

• Читаемость — =Revenue-Costs сразу понятнее, чем =C2-C3.
• Надежность — имя следует за данными, поэтому вставка строк выше не направит формулу к неправильным ячейкам.
• Повторное использование — вводите одно имя во многих формулах вместо повторного ввода длинного адреса.

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

Самый быстрый способ в Экселе — использовать поле имени: небольшое поле слева от строки формул, в котором обычно отображается адрес текущей ячейки.

• Выделите ячейки, которым хотите присвоить имя, например B2:B13.
• Щелкните внутри поля имени.
• Введите имя, например Sales, и нажмите Ввод.

Вот и всё. Теперь диапазон имеет имя, и его можно использовать в любой формуле книги.

В Гугл Таблицах вместо поля имени используется меню.

• Выделите нужные ячейки, например B2:B13.
• Откройте меню Данные и выберите Именованные диапазоны.
• Введите имя на боковой панели и нажмите Готово.

На панели также перечислены все созданные вами имена, поэтому позже их можно изменять или удалять. Результат такой же, как в Экселе: теперь можно написать =SUM(Sales).

И Эксель, и Гугл Таблицы соблюдают несколько правил именования. Помните о них, чтобы избежать ошибок:

• Начинайте с буквы или знака подчеркивания, но не с цифры.
• Без пробелов — используйте Unit_Price или UnitPrice вместо Unit Price.
• Имя не должно выглядеть как адрес ячейки: поэтому Q1 отклоняется, а Quarter1 работает.
• Имена нечувствительны к регистру: sales и Sales указывают на один и тот же диапазон.

Представьте лист, в столбце B которого указаны ежемесячные продажи, а диапазону B2:B13 присвоено имя Sales.

Теперь подсчет итогов и средних значений становится простым и понятным:

• Итог продаж: =SUM(Sales)
• Средний месяц: =AVERAGE(Sales)
• Лучший месяц: =MAX(Sales)

Любой открывший файл сразу поймет эти формулы, даже не видя исходных адресов.

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

Сравните два подхода:

• Без имени: =SUM(Data!B2:B13)
• С именем: =SUM(Sales)

Имя скрывает ссылку на лист, поэтому формулы сводки остаются короткими и аккуратными.

Именованные диапазоны особенно полезны, когда формулы становятся длиннее. Предположим, что Revenue и Costs указывают на диапазоны значений. Формула маржи прибыли читается как предложение:

=(SUM(Revenue)-SUM(Costs))/SUM(Revenue)

Можно также сочетать имена с условными функциями, например подсчитывать продажи для определенного региона:

• =SUMIF(Region,"East",Sales)

Здесь Region и Sales — именованные диапазоны одинаковой длины.

Имена не являются постоянными. В Экселе откройте диспетчер имен на вкладке «Формулы», чтобы переименовать диапазон, изменить охватываемые им ячейки или полностью удалить его.

В Гугл Таблицах те же элементы управления находятся на боковой панели Именованные диапазоны в меню «Данные».

• Изменение ячеек обновляет каждую формулу, использующую это имя.
• Удаление имени превращает его формулы в ошибки #NAME?, поэтому сначала обновите их.

Если в ячейке отображается #NAME?, электронная таблица не распознает введенное вами имя. Распространенные причины:

• Опечатка — вы написали Sale, хотя диапазон называется Sales.
• Имя было удалено или никогда не создавалось.
• Появился пробел, например в неправильно записанной формуле =SUM( Sales ).

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

Несколько привычек помогут сделать именованные диапазоны полезными, а не запутывающими:

• Используйте описательные имена, например QuarterlySales, а не Range1.
• Соблюдайте единый стиль именования, например везде используйте PascalCase.
• Давайте имена часто используемым диапазонам, а не каждой отдельной ячейке.
• Документируйте имена в диспетчере имен, чтобы коллеги понимали их назначение.

Удачно выбранные имена превращают непонятную книгу в такую, которая почти сама себя объясняет.

Вы присвоили диапазону B2:B13 имя Sales. Какая формула правильно подсчитает его итог?

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

• Создавайте имена с помощью поля имени (Эксель) или через Данные > Именованные диапазоны (Гугл Таблицы).
• Соблюдайте правила: начинайте с буквы, не используйте пробелы и имена, похожие на адреса ячеек.
• Используйте имена везде, где ввели бы адрес, в том числе на других листах.
• Управляйте именами в диспетчере имен и проверяйте формулы на опечатки #NAME?.

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