• решетка (#): это ссылка на массив, который возвращается формулой из указанной ячейки (такой тип ссылок работает только в Excel 2021 / Microsoft 365); например, A1# — это ссылка на массив значений, который возвращает формула, находящаяся в ячейке A1 (мы подробно обсудим в главе «Динамические массивы», зачем нужны и как работают такие ссылки);
• знаки сравнения: «равно» (=) (знак «равно» в начале формулы присутствует всегда, а еще один может использоваться, если мы хотим сравнить два значения), «больше» (>), «меньше» (<), «больше либо равно» (>=), «меньше либо равно» (<=), «не равно» (<>);
• функции рабочего листа Excel (например, СУММ / SUM, СРЗНАЧ / AVERAGE, ВПР / VLOOKUP и другие, всего функций в Excel более 500 — количество разнится в зависимости от версии); ниже мы поговорим о том, как вставлять функции в формулы.
Ссылки на ячейки в формулах
Ссылаться на ячейку в формуле можно следующими способами.
• Щелкнуть мышкой на эту ячейку (в том числе предварительно перейдя на другой лист, щелкнув на его ярлык, если нужное значение хранится не на одном листе с формулой).
• Ввести ее адрес с клавиатуры (не забывайте, что номера столбцов — это латинские буквы).
• Выделить соседнюю с формулой ячейку с помощью клавиш со стрелками (это удобно, если вам нужно сослаться на ячейку, которая находится недалеко от ячейки с формулой). Когда вы вводите формулу, есть два режима редактирования: в одном нажатие стрелок на клавиатуре перемещает курсор в самой формуле, во втором — выделяет ячейки. Переключаются эти режимы клавишей 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
В 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).
Итак, в чем польза имен:
• формулы легче воспринимать;
• имена можно выбирать из выпадающего списка при вводе формул;