§ 3.2. Организация вычислений в электронных таблицах

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

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

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

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

Относительная ссылка хранит не имя клетки, а её положение по отношению к клетке с формулой. Пусть в клетке C3 записано =A3*B2. Табличный процессор понимает это так: возьми число из клетки той же строки, что стоит на два столбца левее, и умножь его на число из клетки, которая на один столбец левее и на одну строку выше (рис. 1). Имена A3 и B2 — лишь удобная для человека запись этих смещений.

Поэтому при копировании относительная ссылка меняется сама и сохраняет смещение. Сдвинули формулу на один столбец вправо — каждая буква столбца в ссылке сменилась на следующую по алфавиту. Сдвинули на одну строку вниз — каждый номер строки вырос на единицу. Влево и вверх всё работает так же, только буквы идут по алфавиту назад, а номера уменьшаются. Если формулу из C3 скопировать в E5, она уедет на два столбца вправо и на две строки вниз, и в E5 окажется =C5*D4: на рисунке 1 стрелки у обеих формул одной и той же формы. Сдвигаются и границы диапазонов: формула =СУММ(B2:D2), скопированная на строку ниже, превращается в =СУММ(B3:D3).

Дано: в клетке B2 записана формула =A2+B1. Её скопировали в клетку D5. Требуется узнать, какой вид примет формула в D5.

Решение. Сначала считаем сдвиг. От столбца B до столбца D два шага вправо, от строки 2 до строки 5 три шага вниз. Каждую относительную ссылку сдвигаем на столько же: A2 превращается в C5, а B1 — в D4.

Ответ: =C5+D4.

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

Дано: в начале года в копилке лежало 150 рублей, и каждый месяц в неё добавляют 45 рублей. В клетке B2 записано число 150, в клетке B3 — формула =B2+45, и эту формулу скопировали в клетки B4:B8. Требуется узнать, что покажет клетка B8.

Решение. После копирования в B4 стоит =B3+45, в B5 — =B4+45, и так далее вплоть до B8, где записано =B7+45. Каждая клетка берёт число из клетки над собой и прибавляет к нему 45. Формул шесть, с B3 по B8, значит, к 150 прибавили 45 шесть раз: 150 + 6 · 45 = 420.

Ответ: 420.

Ради такого копирования относительные ссылки и придуманы: правило одно на всю таблицу, а данные у каждой строки свои, и программа сама подставляет нужные адреса.

Копируют формулу обычными командами «копировать» и «вставить», но чаще — заполнением. У активной клетки в правом нижнем углу есть маленький квадратик, маркер автозаполнения. Если потянуть за него мышью вниз или вправо, формула разойдётся по всем клеткам, через которые прошёл указатель, и в каждой копии ссылки сдвинутся ровно на столько, на сколько эта клетка отстоит от исходной. Для формулы заполнение и копирование дают один и тот же результат.

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

Посмотрим, что бывает, если про знак доллара забыть.

Дано: занятие в кружке робототехники стоит 120 рублей, и это число записано в клетке C1. В клетке C2 стоит заголовок столбца «Оплата», а в клетках B3:B6 — сколько занятий посетили четыре ученика: 4, 6, 5 и 8. Требуется так записать формулу в C3, чтобы её копии в C4:C6 правильно посчитали плату, и узнать, сколько заплатит третий ученик.

Решение. Первая мысль — написать =B3*C1. В самой клетке C3 это верно, но при копировании вниз обе ссылки сдвинутся, и в C4 окажется =B4*C2: число занятий умножится на заголовок «Оплата», и вместо суммы клетка покажет сообщение об ошибке. Ссылка на цену должна стоять на месте, поэтому пишем =B3*$C$1. После копирования в C5 будет =B5*$C$1, то есть 5 · 120 = 600.