Формулы и ссылки: относительные и абсолютные

Формула — это арифметика по адресам

Формула — вычисление, которое живёт в ячейке. Она всегда начинается со знака =. Дальше идут числа, знаки действий и ссылки — адреса ячеек, откуда брать данные.

Добавим в учебную таблицу столбец F «Сумма»: количество, умноженное на цену. В F2:

=D2*E2

1250

Можно было бы написать =5*250 — результат тот же. Но такая формула посчитает один раз и забудет. А формула со ссылками держит связь с данными: исправьте количество в D2 на 6, и в F2 сразу появится 1500. В этом и есть смысл таблиц: считаем по ссылкам, а не по числам.

Знаки действий привычные: +, -, * — умножить, / — разделить, ^ — степень. Порядок как в школе: сначала умножение и деление, потом сложение и вычитание, а скобки меняют порядок.

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

Формула нужна в каждой строке. Писать её десять раз не нужно: выделите F2 и потяните вниз за маркер заполнения — маленький квадрат в правом нижнем углу ячейки. В Excel можно просто дважды щёлкнуть по маркеру: формула сама заполнит столбец до конца данных.

При копировании ссылки сдвигаются вместе с формулой:

• в F2 — =D2*E2;
• в F3 — =D3*E3;
• в F11 — =D11*E11.

Такие ссылки называют относительными: они запоминают не адрес, а положение — «две ячейки левее, в той же строке». Именно это и нужно в списке, где каждая строка считается по своим данным.

Абсолютные ссылки: знак $

Иногда ссылка двигаться не должна. Магазин даёт скидку 10% на все заказы. Ставку удобно хранить в одной ячейке, чтобы менять в одном месте: в L1 напишем «Скидка», в M1 — 10%.

Столбец G «Со скидкой» — сумма, умноженная на 1 минус скидка. Пишем в G2 =F2*(1-M1) и копируем вниз. И вот что выходит:

Во второй строке скидка пропала. Ссылка на M1 сдвинулась вместе с формулой на M2, M3 — а там пусто, и пустая ячейка считается нулём. Никакой ошибки таблица не показала — просто посчитала неверно. Это самый коварный вид ошибок, поэтому после копирования всегда проверяйте пару ячеек в строке формул.

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

=F2*(1-$M$1)

1125

Теперь при копировании F2 превращается в F3, F4, а $M$1 остаётся на месте.

• $M$1 — закреплены и столбец, и строка;
• M$1 — закреплена только строка: при копировании вправо столбец сдвинется;
• $M1 — закреплён только столбец.

Знаки можно не печатать: поставьте курсор на ссылку в формуле и нажмите F4 — каждое нажатие меняет вариант закрепления. Работает и в Excel, и в Google Таблицах. На ноутбуке иногда нужно нажать Fn + F4.

Коды ошибок

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

Так коды выглядят в Excel с русским интерфейсом. В Google Таблицах те же ошибки могут показываться английскими кодами: #DIV/0!, #VALUE!, #REF!, #NAME?.

=D2*E2

1250

=F2*(1-$M$1)

1125