Копирование формул и знак $

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

Разберём, когда сдвиг нужен, а когда мешает, на таблице покупок с общей скидкой в ячейке G2.

Ссылка без $ называется относительной: при копировании она сдвигается на столько же строк и столбцов, что и формула. В D2 записано =B2*C2, а для следующей строки нужно =B3*C3: меняется номер строки, а смысл — «цена этой строки умножить на количество этой строки» — остаётся. Копия на одну строку вниз превращает B2 в B3, копия на два столбца вправо — в D2.

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

Формула набрана один раз — в D2. Выбирай D3, D4, D5: в каждой стоит её копия, и рамки показывают, что копия берёт цену и количество своей строки.

Столбец заполняют, протянув маркер заполнения — квадратик в правом нижнем углу рамки. Выдели D2 и тяни маркер вниз до D5:

Так это выглядит в окне Excel:

Другой способ: выдели D2:D5 и нажми Ctrl+D — диапазон заполнится вниз по верхней ячейке. Ctrl+R так же заполняет вправо по левой ячейке. Двойной щелчок по маркеру заполняет столбец до конца соседних данных, но на пустой строке останавливается, поэтому после заполнения проверь формулу в последней строке.

Ссылку на общую для всех строк ячейку закрепляют знаком $. Общая скидка записана в G2. Формула =D2*(1-G2) верно считает первую строку, но после копирования вниз станет =D3*(1-G3): ссылка на скидку сдвинулась, хотя скидка по-прежнему в G2.

Выбери E3: рамка стоит на пустой G3, поэтому ручка осталась без скидки. Адрес скидки нужно закрепить:

При копировании вниз D2 превращается в D3, а $G$2 остаётся $G$2: $ перед буквой закрепляет столбец, перед номером — строку. Закрепляется адрес, а не значение: поставь в G2 20% — все четыре цены пересчитаются.

У ссылки четыре вида: закреплено всё, ничего, только столбец или только строка. Незакреплённая часть адреса получает сдвиг копии, закреплённая остаётся:

Полностью закреплённая ссылка называется абсолютной, ссылка с одной закреплённой частью — смешанной.

Проверь правило сам: поменяй формулу или ячейку, куда её копируем.

Смешанная ссылка закрепляет одно направление: она нужна, когда формула берёт число из своей строки и из своего столбца. Построим таблицу умножения: в A2:A6 числа 1–5, в B1:F1 — тоже 1–5. Одна формула в B2, скопированная на весь прямоугольник, должна умножать число слева на число сверху:

$A2 берёт число слева: при движении вправо столбец A должен сохраниться, при движении вниз номер строки должен измениться. B$1 берёт число сверху: при движении вниз строка 1 должна сохраниться, при движении вправо столбец должен измениться. Поэтому оба знака $ стоят в разных местах по разным причинам. Выбрана D4: рамки стоят на A4 и D1 — число слева и число сверху. Выбирай другие ячейки таблицы.

Для каждой ссылки задают два вопроса: должен ли меняться столбец при копировании вправо и строка — при копировании вниз. Фиксируй только те части, которые по смыслу должны сохраняться.

Закреплять можно обе границы диапазона. Запись $A2:$F2 обозначает участок строки в столбцах A:F. При копировании вправо столбцы не меняются, а при копировании вниз номера строк увеличиваются. Так одна формула обрабатывает одинаковые участки разных строк.

В Excel для Windows при редактировании ссылки F4 по кругу переключает её виды: A2 → $A$2 → A$2 → $A2. Какой вид нужен, клавиша не знает — проверь запись глазами.