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

• Ввести ее адрес с клавиатуры (не забывайте, что номера столбцов — это латинские буквы).

• Выделить соседнюю с формулой ячейку с помощью клавиш со стрелками (это удобно, если вам нужно сослаться на ячейку, которая находится недалеко от ячейки с формулой). Когда вы вводите формулу, есть два режима редактирования: в одном нажатие стрелок на клавиатуре перемещает курсор в самой формуле, во втором — выделяет ячейки. Переключаются эти режимы клавишей F2 (^ + U).

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

То же самое происходит не только при первом вводе формулы, но и если войти в режим редактирования (нажать F2 или щелкнуть двойным щелчком по ячейке с формулой или щелкнуть на строку формул).

АБСОЛЮТНЫЕ И ОТНОСИТЕЛЬНЫЕ ССЫЛКИ

Обычная ссылка на ячейку, которая формируется по умолчанию и выглядит как A1, — это относительная ссылка.

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

Если мы ссылаемся из ячейки C2 на A2 — это ссылка на ячейку на два столбца левее от формулы, а не на ячейку с адресом A2.

Потому что стоит скопировать и вставить или протянуть (мышкой за правый нижний угол ячейки) такую формулу вниз в следующую строку — и ссылка на A2 превратится в A3. А если скопировать формулу вправо — то в B2. Это по-прежнему будет ссылка на ячейку на два столбца левее от формулы, потому она и называется относительной.

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

Достаточно сослаться на соседнюю ячейку B2 из столбца C в той же второй строке и затем протянуть вниз — в каждой строке ссылка будет меняться, соответственно, в формуле будет использоваться значение из той же строки, в которой находится формула (а не 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