Рецепты вычислений с проверкой
Формулы для электронных таблиц с понятными примерами
У формулы есть задача, исходные ячейки и ожидаемый результат. На небольших примерах разберём суммы, условия, проценты и ссылки, чтобы вычисления было легче повторить и проверить в своём редакторе.
Внешний сервис · Работа с документами
Формулы для таблиц
Сумма, среднее и проверка условия
Формула начинается со знака равенства и обращается к значениям ячеек. Если A2, A3 и A4 содержат 1200, 1800 и 2400, выражение =SUM(A2:A4) даст 5400. Двоеточие задаёт непрерывный диапазон от первой до последней ячейки, включая обе границы, поэтому каждое значение входит один раз.
Для того же набора =AVERAGE(A2:A4) даёт 1800. Перед использованием среднего определите, что означает одна строка и нет ли в диапазоне пропусков либо текста вместо чисел. Средний чек, среднее количество и средняя цена отвечают на разные вопросы, даже когда все результаты внешне похожи.
Условная формула выбирает один из двух результатов после проверки. Например, сравните фактический расход с плановым и верните понятную подпись «Превышение» или «В пределах плана». До записи выражения сформулируйте условие словами и решите, к какому варианту относится точное равенство значений.
У функции IF, локализованное имя которой может отображаться как ЕСЛИ, три смысловые части: проверка, результат при её выполнении и результат в другом случае. Проверьте все три ситуации: меньше, равно и больше порога. Это быстрее обнаруживает неверный знак сравнения, чем просмотр длинного готового списка.
| Ячейка или формула | Значение |
|---|---|
| A2 | 1200 |
| A3 | 1800 |
| A4 | 2400 |
| =SUM(A2:A4) | 5400 |
| =AVERAGE(A2:A4) | 1800 |
Формулы для таблиц
Критерии и проценты: как выбрать основание расчёта
Для подсчёта строк по условию и суммирования их значений нужны разные операции. Например, число оплаченных заказов отличается от суммы оплаченных заказов. Сначала определите измеряемый результат, затем выбирайте COUNTIF или SUMIF; при нескольких условиях понадобятся соответствующие функции для набора критериев.
Диапазон условия должен соответствовать диапазону суммирования по строкам. Если статусы начинаются со второй строки, а суммы случайно с третьей, результат потеряет связь с исходными записями. Для проверки оставьте маленький набор, вручную выделите подходящие строки и только затем расширяйте формулу на полный рабочий диапазон.
Процент от суммы и процент изменения — разные вычисления. Чтобы найти четверть от 200, умножают 200 на 25%. Чтобы определить рост с 200 до 250, сначала находят разницу 50, затем делят её на исходные 200. Полученная доля 0,25 отображается как 25%.
Всегда подписывайте базу сравнения. Снижение с 250 до 200 составляет 20%, а не 25%, потому что знаменатель изменился. При исходном значении ноль относительное изменение по этой формуле не определено. Подмена такого результата нулём скроет проблему; лучше показать отсутствие базы для процентного сравнения.
Формулы для таблиц
Ссылки при копировании и поиск ошибок
Относительная ссылка перемещается вместе с формулой, а абсолютная сохраняет выбранную строку и столбец. В выражении =B2*$F$1 значение B2 относится к текущей строке, а F1 может содержать общий коэффициент. При копировании вниз ожидается =B3*$F$1: коэффициент остался на месте, исходная строка изменилась.
Знак доллара перед буквой фиксирует столбец, перед номером — строку. Смешанные варианты полезны в двумерных таблицах, но применять фиксацию ко всем ссылкам подряд не нужно. Скопируйте формулу сначала на одну соседнюю строку и просмотрите адреса: неверная фиксация часто даёт правдоподобный, но повторяющийся результат.
Проверяйте причину по порядку: знак равенства, имя функции, разделители аргументов, существование ссылок и типы исходных значений. В разных настройках редактора запятая может относиться к дробной части, а аргументы разделяться точкой с запятой. Поэтому выражение из инструкции иногда требует адаптации синтаксиса.
Не скрывайте все ошибки через IFERROR до выяснения источника. Отсутствующая цена, деление на ноль и ссылка на удалённый лист означают разные проблемы. Оставьте формулу короткой, проверьте её части отдельно и сравните с ожидаемым числом. После исправления испытайте обычный случай и граничные значения заново.
ВОПРОСЫ И ОТВЕТЫ
Разберём детали.
Почему формула начинается со знака равенства?
Он сообщает редактору, что ячейка содержит вычисление. Без него ввод может восприниматься как текст.
Зачем нужен знак доллара в ссылке?
Он фиксирует столбец или строку при копировании формулы. Выбирайте фиксацию по тому, что должно оставаться постоянным.
Почему скопированная формула не работает в другом редакторе?
Могут различаться имена функций, разделители, форматы чисел и доступность самой функции. Проверяйте среду и версию.
Нужно ли скрывать все ошибки через IFERROR?
Нет, сначала выясните причину. Иначе можно скрыть отсутствие исходных данных или неверную структуру диапазона.
Как убедиться, что процент посчитан правильно?
Подпишите, что является базой сравнения, и проверьте вычисление на двух простых числах. Процент от суммы и процент её изменения — разные задачи.