Абсолютные и относительные ссылки в Excel

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

Хорошая новость: логика ссылок очень простая, если объяснить её на нормальных данных, а не на абстрактных A1–B2. Ниже разберёшь три вида ссылок — относительные, абсолютные и смешанные — на примерах из учебного файла excel_links_demo.xlsx .

• список продуктов с ценами и НДС;
• матрица расходов: категории × месяцы;
• таблица коэффициентов;
• матрица коэффициентов 1–1,4.

Относительные ссылки: формула «шагает» вместе со строкой

Относительная ссылка — это стандартный режим Excel. Например: B2, C5, D10.

Excel запоминает не только адрес ячейки, но и смещение относительно формулы. Формула копируется вниз — ссылки сдвигаются вместе с ней.

Вот простой пример — лист «Базовые_Данные»:

В ячейке D2 формула:

=B2*C2

При копировании вниз Excel автоматически пересчитывает:

• D3 → =B3*C3
• D4 → =B4*C4
• D5 → =B5*C5

Это и есть относительное поведение: формула сдвигается на строку — ссылки следуют за ней.

Такие ссылки удобны, когда:

• нужно посчитать сумму по каждой строке (Цена × Количество);
• добавляется наценка или скидка для каждого товара;
• одинаковый расчёт повторяется для десятков строк.

Абсолютные ссылки: фиксируем значение «раз и навсегда»

Если ссылка не должна меняться при копировании, помогает знак доллара $. Абсолютная ссылка выглядит так:

$A$1

Здесь фиксированы:

• столбец A ($A),
• строка 1 ($1).

Пример — НДС для всех продуктов

На листе «Коэффициенты» есть небольшая таблица:

На листе «Базовые_Данные» есть столбец «Сумма_с_НДС». В ячейке E2 можно использовать формулу:

=D2 * Коэффициенты!$B$2

Смысл такой:

• D2 — сумма по строке (Цена × Количество);
• Коэффициенты!$B$2 — ставка НДС, одна и та же для всех строк в этой таблице.

Формула копируется вниз, ссылка на D2 становится D3, D4 и т.д., а ссылка на $B$2 не меняется. Это классический сценарий для абсолютных ссылок: фиксированный параметр используется во множестве строк (такие параметры часто называют «константой»).

Смешанные ссылки: формула работает и по строкам, и по столбцам

Смешанная ссылка — это когда фиксируется только часть адреса:

• $A1 — зафиксирован столбец A, строка может меняться;
• A$1 — зафиксирована строка 1, столбец может меняться.

Такой подход особенно полезен, когда таблица похожа на матрицу: категории по строкам, месяцы по столбцам.

Пример — лист «Категории_Месяцы»

Блок 1 — фактические расходы

Здесь — исходная матрица расходов: строки — категории (Еда, Транспорт, Связь…), столбцы — месяцы от января до декабря. На основе этой таблицы удобно считать доли и структуру расходов.

Блок 2 — «Доля месяца в годовом расходе категории»

Вопрос, на который отвечает этот блок:

Как распределились годовые расходы категории по месяцам?

Для строки «Еда» в ячейке B3 (январь) формула может выглядеть так:

=B3 / СУММ($B3:$M3)

Что здесь происходит:

• в числителе — расход Еды за январь;
• в знаменателе — сумма по строке от января до декабря для Еды;
• диапазон $B3:$M3 фиксирует столбцы B и M, но не фиксирует строку.

При копировании формулы:

• вправо — числитель меняется (C3, D3…), диапазон остаётся в столбцах B–M;
• вниз — и числитель, и диапазон переходят на следующую строку (Еда → Транспорт → Связь и т.д.).

Блок 3 — «Доля категории в расходах месяца»

Здесь другой вопрос:

Как в каждом месяце распределились расходы между категориями?

Для строки «Еда» в ячейке B3 (январь) формула:

=B3 / СУММ(B$3:B$7)

Разбор:

• в числителе — расход Еды за январь;
• в знаменателе — сумма по столбцу B (январь) по всем категориям;
• диапазон B$3:B$7 фиксирует строки 3 и 7, но не фиксирует столбец.

При копировании формулы:

• вниз — числитель меняется (Транспорт, Связь…), диапазон остаётся B$3:B$7;
• вправо — диапазон автоматически переключается на C$3:C$7, D$3:D$7 и далее.

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

Как быстро переключать тип ссылки: F4

Всё ещё проще: просто нажми F4.

Последовательность такая:

1. выделить ссылку в формуле (например, B2);
2. нажать F4 несколько раз;
3. Excel по кругу переключит варианты: B2 → $B$2 → B$2 → $B2 → B2.

Так легко получить нужный тип ссылки — относительную, абсолютную или смешанную — без ручной расстановки знаков $.

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

«Закрепить всё сразу»

Есть соблазн «подстраховаться» и добавить $ ко всем ссылкам подряд. В итоге формула перестаёт подстраиваться под строки и столбцы при копировании, и в каждой строке получается один и тот же результат.

«Ссылка уехала на не ту ячейку»

Часто это означает, что должна была использоваться абсолютная или смешанная ссылка, а осталась относительная. Перед копированием полезно задать себе простой вопрос: «Эта ссылка должна меняться или нет?»

«Меняется структура таблицы — меняются формулы»

Даже абсолютные ссылки ($A$1) смещаются при удалении и вставке строк и столбцов. Так Excel старается сохранить смысл формулы. Это ожидаемое поведение, а не ошибка.

Куда двигаться дальше с формулами

Когда логика ссылок становится понятной, намного проще собирать формулы с условиями — по статусу, по менеджеру, по датам. Например:

• посчитать сумму по условию в Excel (СУММЕСЛИ);
• выбрать между СУММ, СУММЕСЛИ и СУММЕСЛИМН в реальных задачах;
• посчитать сумму по датам в Excel — за день, месяц или весь период.

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

Что стоит запомнить

Итог

Ссылки в Excel — это не «волшебство доллара», а понятный способ управлять тем, что должно меняться, а что — оставаться на месте.

• Относительные ссылки (A1) двигаются вместе с формулой.
• Абсолютные ($A$1) цепляются за конкретную ячейку.
• Смешанные ($A1, A$1) помогают строить аккуратные матрицы и отчёты без лишних формул.

На нескольких живых примерах — товаров с НДС и таблицы «Категории × Месяцы» — тема перестаёт быть страшной. Формулы становятся предсказуемыми, а копирование — уже не лотереей, а управляемым инструментом.

Что дальше

Хочешь продолжить и собрать из таких примеров полноценный бюджет или отчёт, где формулы не ломаются при копировании? Посмотри бесплатный курс: