Ознакомительная версия. Доступно 12 страниц из 57
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, а далее идут названия листов.
Если имя действует на уровне листа, значит, диапазонам на других листах тоже можно присвоить такое имя. И значит, ссылаться в формулах на него придется с указанием названия листа.
Поле «Имя» можно использовать для быстрой навигации в книге: можно выбрать имя из списка. После выбора соответствующий диапазон будет выделен.
Ознакомительная версия. Доступно 12 страниц из 57
