Это фрагмент из книги "Магия таблиц"
Ссылки на ячейки в формулах
Ссылаться на ячейку в формуле можно следующими способами:
- Щелкнуть мышкой на эту ячейку (в том числе предварительно перейдя на другой лист, щелкнув на его ярлык, если нужное значение хранится не на одном листе с формулой).
- Ввести ее адрес с клавиатуры (не забывайте, что номера столбцов — это латинские буквы).
- Выделить соседнюю с формулой ячейку с помощью клавиш со стрелками (это удобно, если вам нужно сослаться на ячейку, которая находится недалеко от ячейки с формулой). Когда вы вводите формулу, есть два режима редактирования: в одном нажатие стрелок на клавиатуре перемещает курсор в самой формуле, во втором — выделяет ячейки. Переключаются эти режимы клавишей 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.
Плюс в том, что все строки будут учитываться в формуле, минус в том, что попадут лишние — то есть мы априори ссылаемся на миллион с лишним строк.
Чтобы сослаться на строки/столбцы целиком при вводе формулы, можно либо ввести их "вручную", либо щелкнуть мышкой на заголовок столбца/строки (номер).
Именованные диапазоны
В Excel и Google Таблицах диапазонам можно присваивать имена. После того, как диапазону присвоено имя, на него можно ссылаться в формулах — и такие формулы читаются легче (=Налог*Выручка, а не =B2*$E$1). Кроме того, имена помогают в некоторых особых ситуациях.
В Excel можно задать имя следующими способами.
Выделить ячейку/диапазон и ввести имя в поле, которое так и называется — "Имя", находится оно слева от строки формул:
Если в диапазоне есть заголовки и вы хотите их использовать в качестве имен, используйте команду "Создать из выделенного" на вкладке "Формулы" (Formulas — Create from Selection). Можно также использовать сочетание клавиш Ctrl + Shift + F3
Еще можно пользоваться диспетчером имен — это окно вызывается сочетанием Ctrl + F3 — там можно и создавать имена, и просматривать/изменять/удалять существующие.
Есть некоторые ограничения:
- Имя может быть длиной до 255 символов
- Имена могут начинаться с буквы, нижнего подчеркивания, обратной косой черты.
- Пробелы в именах (как и разные символы, математические операторы) не допускаются, но можно использовать вместо них нижнее подчеркивание.
После того, как мы задали имя, мы можем использовать его в формулах.
Имена отображаются в подсказке с функциями — так, если у нас есть имя "Ставка_роялти", то оно появится в списке при вводе первых букв. Достаточно будет щелкнуть на него или выделить и нажать Tab:
Если мы присвоили имя одной ячейке, можно ссылаться на нее — это будет словно абсолютная ссылка — не переживая, что ссылка "поедет" (как это было бы с относительной ссылкой):
Если же имя присвоено целому диапазону, то мы можем:
- ссылаться на значение из этого диапазона в той же строке/столбце (своего рода относительная ссылка) в версиях Excel до 2019 включительно или ссылаться сразу на весь диапазон в Excel 2021 / Microsoft 365:
- ссылаться на весь диапазон — например, вычислить сумму всех значений из именованного диапазона.