Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах)

В Excel и Google Таблицах диапазонам можно присваивать имена. После того как диапазону присвоено имя, на него можно ссылаться в формулах, и такие формулы читаются легче (=Налог*Выручка, а не =B2*$E$1). Кроме того, имена помогают в некоторых особых ситуациях.

В Excel можно задать имя следующими способами.

Выделить ячейку/диапазон и ввести имя в поле, которое так и называется — «Имя», находится оно слева от строки формул.

Если в диапазоне есть заголовки и вы хотите их использовать в качестве имен, используйте команду «Создать из выделенного» на вкладке «Формулы» (Formulas — Create from Selection). Можно также использовать сочетание клавиш Ctrl + Shift + F3.

Еще можно пользоваться диспетчером имен — это окно вызывается сочетанием Ctrl + F3, там можно и создавать имена, и просматривать/изменять/удалять существующие.

В Google Таблицах имена настраиваются в меню «Данные» — «Настроить именованные диапазоны» (Data — Named ranges). Можно задавать и в поле «Имя» слева от строки формул, как в Excel.

Есть некоторые ограничения:

• имя может быть длиной до 255 символов;

• имена могут начинаться с буквы, нижнего подчеркивания, обратной косой черты;

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

После того как мы задали имя, мы можем использовать его в формулах.

Имена отображаются в подсказке с функциями. Так, если у нас есть имя "Ставка_роялти", то оно появится в списке при вводе первых букв. Достаточно будет щелкнуть на него или выделить и нажать Tab.

Если мы присвоили имя одной ячейке, можно ссылаться на нее (это будет словно абсолютная ссылка), не переживая, что ссылка «поедет» (как это было бы с относительной ссылкой).

Если же имя присвоено целому диапазону, то мы можем:

• ссылаться на значение из этого диапазона в той же строке/столбце (своего рода относительная ссылка) в версиях Excel до 2019 включительно или ссылаться сразу на весь диапазон в Excel 2021 / Microsoft 365;

• ссылаться на весь диапазон — например, вычислить сумму всех значений из именованного диапазона.

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

Область действия задается в одноименном поле в диалоговом окне «Вставка имени». Книга — это вся книга Excel, а далее идут названия листов.

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

Поле «Имя» можно использовать для быстрой навигации в книге: можно выбрать имя из списка. После выбора соответствующий диапазон будет выделен.

А еще можно просто ввести в этом поле адрес любой ячейки и нажать Enter, чтобы к ней перейти. Кстати, это пригодится, чтобы посмотреть, какие данные есть в скрытой ячейке (и даже отредактировать их).

Также перейти к любой ячейке можно с помощью окна «Переход» (вызывается клавишей F5).

Итак, в чем польза имен:

• формулы легче воспринимать;

• имена можно выбирать из выпадающего списка при вводе формул;

• быстрый переход к именованному диапазону через поле «Имя»;

• абсолютная ссылка на ячейку/диапазон без необходимости использовать знаки доллара.

Кроме того, они будут отображаться при уменьшении масштаба до того уровня, когда смогут поместиться поверх соответствующих диапазонов.

ССЫЛКА НА ПЕРЕСЕЧЕНИЕ ДИАПАЗОНОВ

Файл с примером: Пересечение диапазонов.xlsx

Это не то, с чем вы, скорее всего, столкнетесь на практике. Но для понимания, что любой символ в формулах (пока он не взят в кавычки — тогда он становится текстом, а не частью формулы; об этом мы подробно поговорим в разделе про текст) что-то может значить, стоит обсудить пробел. Это символ пересечения диапазонов. Если у вас в формуле есть два диапазона, между которыми стоит пробел, то это будет ссылка на общие ячейки этих диапазонов.

Например, если присвоить всем диапазонам в этой таблице имена (это можно сделать с помощью команды «Создать из выделенного» на вкладке ленты «Формулы»)…

…то можно будет ссылаться на показатель и квартал как на пересечение двух диапазонов:

=Материалы март

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