Представьте прайс-лист на сто позиций. В каждой строке нужно умножить количество на цену — то есть написать сто почти одинаковых формул. Вручную это долго и очень легко ошибиться. Но если разобраться, как устроены ссылки, достаточно написать формулу один раз и растянуть её вниз: Таблицы сами подставят в каждую строку её собственные числа.

А теперь усложним: у всех товаров одна и та же ставка НДС, и она лежит в отдельной ячейке. Если просто скопировать формулу вниз, ссылка на ставку тоже «съедет» и расчёт сломается. Именно эту разницу — какие ссылки должны меняться при копировании, а какие обязаны остаться на месте — мы и разберём. После урока вы сможете писать формулы, которые правильно копируются на любое количество строк, давать диапазонам понятные имена и брать данные с других листов документа.

Что уже умеем

В первом модуле вы познакомились с интерфейсом Google Таблиц, научились вводить и форматировать данные, посчитали первые итоги функциями SUM, AVERAGE, MIN, MAX, COUNT и настроили совместный доступ. То есть формулы вам уже знакомы: вы знаете, что любая начинается со знака равенства, а в скобках указывается ячейка или диапазон.

Сейчас мы посмотрим на формулы с другой стороны — со стороны адресов. Разберёмся, что происходит со ссылками при копировании, как зафиксировать нужную ячейку знаком доллара, как заменить сухие адреса осмысленными именами и как обратиться к данным на соседнем листе.

Что вы узнаете в рамках статьи

В этом уроке вы:

• научитесь уверенно читать ссылки на ячейки и записывать диапазоны;
• поймёте, почему относительная ссылка меняется при копировании формулы;
• освоите абсолютные и смешанные ссылки со знаком $;
• дадите диапазону собственное имя и используете его в формуле;
• напишете ссылку на ячейку с другого листа документа.

Ключевые термины

• Ссылка — указание на ячейку или диапазон внутри формулы, например C2 или B2:B6.
• Относительная ссылка — ссылка вида C2, которая сдвигается вместе с формулой при копировании.
• Абсолютная ссылка — ссылка вида $G$1 со знаками доллара; при копировании остаётся на прежней ячейке.
• Смешанная ссылка — ссылка, в которой зафиксирована только строка (C$2) или только столбец ($C2).
• Именованный диапазон — диапазон, которому вы дали собственное имя, например Суммы, чтобы использовать его в формулах вместо адреса.
• Межлистовая ссылка — ссылка на ячейку другого листа, записывается через восклицательный знак: Лист2!A1.

Ссылка и диапазон: адресная система таблицы

Ссылка — это способ сказать формуле, откуда брать данные. Вместо самого числа вы указываете адрес ячейки, в которой это число лежит. Выгода огромная: если значение в ячейке поменяется, все формулы, которые на неё ссылаются, пересчитаются сами.

Диапазон — это несколько соседних ячеек, записанных через двоеточие. Читается он как «от и до»: C2:C6 — пять ячеек столбца C, A1:D1 — первая строка от столбца A до столбца D, B2:D6 — прямоугольный блок из пятнадцати ячеек.

Возьмём небольшой список канцелярии для закупки. Заполните лист так:

Здесь B2 — это 12, а C4 — 220. Адреса не обязательно набирать руками: начните вводить формулу и щёлкните по нужной ячейке — ссылка подставится сама.

Относительные ссылки: формула, которая едет вместе с вами

Введите в D2 формулу:

=B2*C2

В D2 появится 1800. Теперь скопируйте эту ячейку и вставьте её в D3:D6 (или потяните вниз за маленький квадратик в правом нижнем углу выделения). Загляните в D3 — там уже =B3*C3, а в D6 — =B6*C6.

Это и есть относительные ссылки: Таблицы запомнили не конкретные адреса, а смысл «возьми две ячейки левее в моей же строке». Формула сдвинулась на строку вниз — вместе с ней сдвинулись и ссылки. Результат:

Одна формула вместо пяти — а на таблице из тысячи строк экономия становится решающей.

Абсолютные и смешанные ссылки: фиксируем ячейку знаком доллара

Добавим ставку НДС. В F1 напишите подпись «Ставка НДС», а в G1 введите 0,2. Теперь посчитаем налог в столбце E. Если написать в E2 формулу =D2*G1 и скопировать вниз, то в E3 окажется =D3*G2 — а G2 пустая, и вместо налога вы получите нули. Ссылка на ставку тоже поехала вниз, хотя должна была остаться на месте.

Чтобы закрепить ячейку, поставьте знак доллара перед буквой столбца и перед номером строки. Введите в E2:

=D2*$G$1

Скопируйте вниз — D2 превратится в D3, D4, D5, D6, а $G$1 останется неизменной. В столбце E появятся значения 360, 280, 352, 450 и 180. Быстрый способ расставить доллары: поставьте курсор на ссылку и нажимайте F4.

Закрепить можно и только половину адреса — такие ссылки называют смешанными:

Смешанные ссылки выручают в таблицах, которые заполняются в двух направлениях, — например, когда одну формулу нужно растянуть и вправо, и вниз.

Именованные диапазоны: понятные имена вместо адресов

Через месяц формула =SUM(D2:D6) уже не расскажет, что именно она складывает. Поэтому диапазонам можно давать имена. Выделите D2:D6, откройте меню «Данные», затем «Настроить именованные диапазоны», введите имя Суммы и нажмите «Готово». Теперь в любой ячейке работает формула:

=SUM(Суммы)

Результат — 8110, то есть итог по всей закупке. Читается такая формула почти как обычная фраза. Есть и практический плюс: если вы перенесёте диапазон в другое место листа, имя переедет вместе с ним и формула не сломается.

Имена не должны содержать пробелов и не могут совпадать с адресами ячеек: Цены_2025 подойдёт, A1 — нет.

[Скриншот: боковая панель «Именованные диапазоны» с введённым именем «Суммы» и диапазоном D2:D6]

Ссылки между листами

Данные редко живут на одном листе: сырые цифры удобно держать отдельно от итогового отчёта. Чтобы обратиться к ячейке другого листа, укажите его имя, восклицательный знак и адрес:

=Лист2!A1

Если в названии листа есть пробел, имя берётся в одинарные кавычки:

='Закупка май'!D2

Работает это и с диапазонами: на новом листе «Итоги» общую сумму закупки даст формула =SUM('Закупка май'!D2:D6). Проще всего не набирать имя листа вручную: начните формулу, перейдите на нужный лист и выделите ячейки — ссылка соберётся сама.

Частые ошибки

• Забыть доллары в ссылке на общий коэффициент. Формула в первой строке считает верно, а ниже появляются нули или странные числа. Прежде чем копировать формулу, спросите себя: какая ссылка должна остаться неподвижной, — и закрепите её.
• Ставить доллары везде «на всякий случай». Формула =$B$2*$C$2 при копировании вниз даст один и тот же результат во всех строках. Фиксировать нужно только то, что действительно не должно меняться.
• Ошибка #REF! после удаления строк. Она означает, что формула ссылается на ячейку, которой больше нет. Проверьте, что удаляли, и восстановите диапазон через историю версий.
• Ошибка #NAME? в формуле с именем диапазона. Обычно это опечатка в имени или диапазон вообще не создан. Сверьтесь со списком в меню «Данные», затем «Настроить именованные диапазоны».

Частые вопросы

• Можно ли ссылаться на весь столбец целиком?
  Да, запись C2:C означает «от C2 и до конца столбца». Это удобно, когда данные будут дописываться: новые строки попадут в расчёт автоматически.
• Что будет с формулами, если переименовать лист?
  Google Таблицы сами обновят все межлистовые ссылки на новое имя. А вот если вы вписывали имя листа внутрь текстовой строки, его придётся править руками.

Итоги

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

Дальше начинается самое интересное. Пока таблица только считает то, что вы ей велели, — а в следующем уроке она начнёт принимать решения. Мы разберём условные функции: научим таблицу проверять условия и выдавать разные результаты в зависимости от того, что она нашла в ячейке.