11.5. Формулы

Определения и обозначения

Формула электронной таблицы — выражение, которое начинается со знака = и задаёт вычисление; его результат отображается в ячейке. Например, запись =2+3 позволяет получить число 5. Формулы нужны, чтобы выполнять расчёты прямо в таблице и автоматически обновлять их результаты при изменении исходных данных.

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

Относительная ссылка — ссылка на ячейку, которая при копировании формулы изменяется в соответствии с новым положением формулы. Например, если формулу =A2+B2 скопировать на строку ниже, ссылки станут A3 и B3. Такая ссылка удобна, когда одно и то же вычисление нужно повторить для разных строк таблицы.

Абсолютная ссылка — ссылка, адрес которой при копировании формулы не изменяется. Её записывают со знаками $ перед обозначением столбца и номером строки, например $B$3. Абсолютная ссылка нужна, когда формулы должны обращаться к одной постоянной ячейке, например к коэффициенту или общей ставке.

Как это устроено

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

В формуле могут участвовать числа, знаки арифметических операций и адреса ячеек. Когда встречается адрес, таблица берёт значение из соответствующей ячейки и использует его в вычислении. Например, формула =A1+B1 складывает содержимое A1 и B1, а не сами обозначения. Благодаря адресам одно выражение подходит для работы с меняющимися данными.

Порядок действий в формуле подчиняется правилам математических выражений: умножение и деление обычно выполняются раньше сложения и вычитания, а скобки задают действия, которые нужно выполнить первыми. Поэтому выражения =2+3*4 и =(2+3)*4 дают разные результаты. Скобки помогают точно выразить замысел и не полагаться на догадки при чтении сложной записи.

Таблица хранит не только полученный результат, но и связь формулы с ячейками, на которые она ссылается. Если значение в одной из этих ячеек меняется, программе достаточно повторно выполнить формулу. Поэтому итог обновляется автоматически и соответствует новым данным. Эта связь особенно полезна в таблицах, где исходные числа часто редактируют.

При копировании формулы таблица учитывает вид ссылок. Относительные ссылки сдвигаются, потому что обычно формулу копируют для аналогичных данных в соседних строках или столбцах. Абсолютные ссылки сохраняют адрес благодаря знакам $. В записи $B1 закреплён столбец, а в B$1 — строка: незакреплённая часть при копировании может измениться.

Как изменяются ссылки при копировании формулы

Сначала определите, какие части адреса должны оставаться неизменными при копировании. Если формула перемещается вниз по строкам, обычно проверяют номер строки; если вправо — обозначение столбца. Знаки $ ставят только перед теми частями адреса, которые нужно закрепить.

Разбор задач

Задача 1. В ячейках A1 и B1 записаны числа 12 и 5. Нужно ввести в C1 формулу для нахождения суммы. Поскольку числа уже находятся в таблице, удобнее сослаться на ячейки, а не вписывать их значения непосредственно. Так формула будет учитывать возможное изменение исходных чисел.

1. Выбираем ячейку C1 — в ней должен появиться результат.
2. Вводим знак =, чтобы таблица распознала вычисляемое выражение.
3. Добавляем адреса ячеек и знак сложения: =A1+B1.

Ответ: в C1 нужно ввести =A1+B1; при указанных данных результат равен 17. Если изменить число в A1 или B1, сумма пересчитается автоматически. Это позволяет проверить, что формула использует именно адреса, а не только однажды введённые числа.

Задача 2. В столбце A указаны количества товаров, а в столбце B — цены. Требуется вычислить стоимость для строки 2 и скопировать формулу вниз. Стоимость находят умножением количества на цену. При переходе к следующей строке нужны значения из следующей пары ячеек, поэтому ссылки должны быть относительными.

1. Выбираем ячейку для результата в строке 2, например C2.
2. Записываем умножение значений из A2 и B2: =A2*B2.
3. Копируем формулу вниз по столбцу C; ссылки изменятся на A3 и B3, затем на адреса следующих строк.

Ответ: формула для C2 — =A2*B2. При копировании вниз она автоматически использует количество и цену из каждой соответствующей строки. Если бы ссылки закрепили, вычисления могли бы повторно обращаться к одним и тем же данным.

Задача 3. В B1 записан постоянный коэффициент, а в столбце A — значения, которые нужно на него умножить. Нужно составить формулу для строки 2 и скопировать её вниз. Ссылка на значение из A должна меняться, чтобы брать данные текущей строки. Адрес коэффициента, напротив, должен оставаться неизменным.

1. Для строки 2 берём значение из A2.
2. Закрепляем адрес коэффициента, записав его как $B$1.
3. Соединяем ссылки знаком умножения: =A2*$B$1, затем копируем формулу вниз.

Ответ: в строке 2 вводим =A2*$B$1. При копировании A2 станет A3, A4 и далее, а ссылка $B$1 останется постоянной. Поэтому каждое значение из столбца A умножится на один и тот же коэффициент.

Типичные ошибки

Если не поставить знак = в начале, запись может восприниматься как обычный текст, а не как формула. Такую ошибку допускают, когда вводят выражение по привычке, не проверив первый символ. Убедитесь, что перед адресами и действиями стоит =, а в ячейке отображается результат вычисления.

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

Если не учесть порядок действий, программа может вычислить выражение не так, как задумано. Часто забывают, что умножение выполняется раньше сложения, или не ставят скобки для нужного порядка. Проверьте выражение на простых числах и добавьте скобки там, где последовательность действий должна быть явной.

При копировании формулы относительная ссылка на постоянную ячейку смещается, хотя адрес должен оставаться тем же. Это случается, когда не различают ссылки для строковых данных и ссылку на общий коэффициент. Посмотрите формулу после копирования: если адрес постоянного значения изменился, закрепите нужные части знаками $, например запишите $B$1.

Связь с другими темами

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

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

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

• Формула начинается со знака =.
• Адрес ячейки позволяет использовать её значение в вычислении.
• Изменение исходных данных вызывает пересчёт формул.
• Скобки позволяют явно задать порядок вычислений.
• Относительная ссылка меняется при копировании формулы.
• Знаки $ фиксируют столбец, строку или оба элемента адреса.