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

B2 для всех строк).

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

 + T) после того, как вы щелкнули на ячейку в формуле (или после того, как установили курсор на ссылку в формуле). Появятся доллары у номеров строки и столбца — это означает, что ссылка стала абсолютной, а не относительной. То есть ссылкой на ячейку с адресом D1, а не на ячейку справа и сверху от формулы.

Эту формулу следует читать так (с точки зрения ссылок): произведение ячейки слева от формулы (B3) и ячейки D1 ($D$1).

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

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

Ссылка вида A$1 — это ссылка на первую строку, а вот ссылка на столбец остается относительной, то есть будет меняться при копировании формулы с такой ссылкой. Аналогично $A1 — это ссылка на столбец A, а номер строки будет меняться при перемещении формулы.

В следующем примере нам нужно перемножить возможные варианты объема производства (из строки 2) и оптовых цен (из столбца B) — чтобы на пересечении получить сценарии по выручке для каждой пары параметров.

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

Абсолютные ссылки тоже не подойдут: с ними мы в любых ячейках будем умножать одни и те же ячейки C2 и B3 друг на друга. Это пример ситуации, когда нужны смешанные ссылки, ведь нам нужно перемножать разные (относительная часть ссылки) числа из столбца B (абсолютная часть ссылки) с разными (относительная) числами из строки 2 (абсолютная).

ССЫЛКИ НА СТОЛБЦЫ И СТРОКИ ЦЕЛИКОМ

В Excel (и в Google Таблицах) можно ссылаться не только на отдельные ячейки и диапазоны, но и на столбцы и строки целиком.

Вот как выглядят такие ссылки:

2:2 — относительная ссылка на всю вторую строку;

$3:$3 — абсолютная ссылка на всю третью строку;

A: E — относительная ссылка на пять столбцов, от A до E, целиком;

$B:$B — абсолютная ссылка на весь столбец B.

Плюс в том, что все строки будут учитываться в формуле. Минус в том, что попадут лишние, то есть мы априори ссылаемся на миллион с лишним строк.

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

ИМЕНОВАННЫЕ ДИАПАЗОНЫ

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

В 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, а далее идут названия листов.

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

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