Как написать формулу в Excel и скопировать её на весь столбец

Задача почти любой таблицы — посчитать: остаток на складе, сумму за месяц, долю расходов, отклонение от плана. Справка Microsoft описывает формулу как запись из функций, ссылок, операторов и констант, которая всегда начинается со знака равенства^[1]. Пока это правило не стало привычкой, расчёт легко превратить в текст, а таблицу — в набор чисел, которые никто не пересчитывает.

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

Ключевые факты: формулы в Excel

• Формулы в Excel начинаются со знака равенства и могут содержать функции, ссылки, операторы и константы^[1].
• Константы и операторы в простой формуле могут занимать до 8192 знаков^[2].
• Умножение и деление выполняются раньше сложения и вычитания, а скобки меняют порядок^[2].
• Ctrl+D заполняет формулой ячейки вниз, а Ctrl+R — вправо по строке^[5].
• Функция отличается от простой формулы тем, что выполняет готовый расчёт по аргументам в скобках^[4].
• Справка Microsoft советует выносить константы в отдельные ячейки и ссылаться на них^[1].

Что такое формулы в Excel: знак =, ссылки, функции и операторы

Формула — это запись в ячейке, которая начинается со знака равенства и вычисляет результат по числам, ссылкам на ячейки и функциям^[1]. Без знака равенства Excel примет запись за обычный текст и считать её не будет. В ячейке после ввода виден результат, а сама запись остаётся в строке формул. Формулой можно посчитать остаток, итог, среднее, отклонение или проверить условие — и всё это пересчитается само при смене исходных чисел.

Справка Microsoft разбирает формулу на четыре части^[1]:

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

Функция устроена одинаково: знак равенства, имя функции, скобки и аргументы внутри них^[4]. При вводе встроенной функции Excel показывает подсказку с синтаксисом, а окно вставки функции открывается сочетанием Shift+F3^[4]. Все функции собраны на вкладке «Формулы»^[4].

Справка относит эти правила к Excel 2016, Excel 2019, Excel 2021, Excel 2024 и Microsoft 365^[1]. В Google Таблицах формулы тоже начинаются со знака равенства, а все формулы листа там показывает сочетание Ctrl + ~^[8].

Разобравшись с частями формулы, переходим к первой записи — на ней видно, как Excel превращает ввод в расчёт.

Как написать первую формулу: пошагово от знака = до Enter

Первая формула пишется в четыре действия: выбрать ячейку, ввести знак равенства, указать ячейки и операторы между ними, нажать Enter^[2]. Вместо того чтобы набирать числа, справка предлагает щёлкать ячейки с нужными значениями^[2].

1. Щёлкните ячейку D2, где нужен остаток.
2. Введите знак = и щёлкните B2 — адрес появится в записи.
3. Введите - и щёлкните C2.
4. Нажмите Enter и проверьте результат: в D2 появится 45.
5. Выделите D2 и посмотрите в строку формул — там стоит =B2-C2.

Простые формулы всегда начинаются со знака равенства, а дальше идут значения и операторы^[2]. Если вместо щелчка набрать адрес вручную, его пишут только латинскими буквами: русская «С» в записи С2 выглядит так же, но Excel не примет её за адрес и вернёт ошибку. Исправить готовую формулу можно в строке формул или прямо в ячейке после двойного щелчка.

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

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

Однако одна и та же запись может дать разный результат в зависимости от того, в каком порядке Excel выполняет действия.

Операторы и порядок вычислений: чем отличается =5+2*3 от =(5+2)*3

Формула отличается от записи со скобками порядком действий: Excel сначала умножает и делит, а потом складывает и вычитает, поэтому =5+2*3 даёт 11, а =(5+2)*3 — 21^[2]. Скобки меняют этот порядок, и действие внутри них выполняется первым^[3].

По справке Microsoft Excel следует общим математическим правилам: скобки, степень, умножение и деление, сложение и вычитание^[3]. Операторы сравнения возвращают ИСТИНА или ЛОЖЬ, и такие результаты потом используют в условиях^[3]. Амперсанд склеивает текст и числа в одну строку, поэтому формула с ним возвращает не число, а надпись^[3].

Операторы ссылок тоже входят в формулу. Двоеточие образует диапазон, например B2:B4, а точка с запятой объединяет несколько диапазонов или аргументов^[3]. Пробел между двумя диапазонами означает пересечение — общую часть этих диапазонов^[3]. Знак процента в списке операторов обозначает долю: =10%*B2 для прихода 120 вернёт 12. Оператор сравнения не меняет данные, а лишь отвечает на вопрос «да или нет», поэтому сам по себе он не может заменить функцию условия — его результат передают в ЕСЛИ или в фильтр.

Скобки полезны и там, где порядок и так верный: формулу =(B2-C2)/B2 легче прочитать, чем запись без них. Если в формуле больше трёх действий, лучше расставить скобки явно, чем держать порядок в голове. Число открывающих и закрывающих скобок должно совпадать: лишняя или пропущенная скобка не даст Excel принять запись.

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

Как протянуть формулу на весь столбец: маркер, Ctrl+D и команда «Заполнить»

Протянуть формулу на весь столбец помогает маркер заполнения — квадрат в правом нижнем углу ячейки: его перетаскивают на соседние ячейки^[5]. Для точного диапазона подходят сочетания Ctrl+D вниз и Ctrl+R вправо^[5].

При заполнении Excel вставляет относительные ссылки^[5]: =B2-C2 в строке 3 станет =B3-C3, в строке 4 — =B4-C4. Именно поэтому одна формула работает для всей таблицы, и для кофе в D3 появится 60. Если какая-то ячейка не должна сдвигаться — курс, ставка, итог, — закрепить её нужно до протягивания^[5].

Если маркера не видно, его включают в «Файл» → «Параметры» → «Дополнительно» флажком «Разрешить маркеры заполнения и перетаскивание ячеек»^[5]. Если после протягивания во всех строках один и тот же результат, проверьте вычисление книги: в «Файл» → «Параметры» → «Формулы» должно стоять «Автоматически»^[5].

Протянутая формула в Excel сохраняет ту же логику в каждой строке, поэтому проверять её достаточно в двух местах: в первой строке и в последней. Маркер копирует запись только в смежные ячейки^[5], а для несмежных диапазонов формулу копируют и вставляют обычным способом. Если обе верны, а ссылки сдвинулись на одинаковое число строк, остальные строки тоже посчитаны правильно. Сочетание Ctrl+D заполняет ячейки, которые выделены вместе с исходной, поэтому длину столбца задаёт само выделение^[5].

Остаётся понять, какие функции стоит освоить первыми, чтобы закрыть типичные задачи отчёта.

Какие функции выбрать для первых задач: пример с таблицей остатков

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

Каждая функция в таблице — отдельная страница сайта с разбором аргументов и ошибок: сумма, среднее, округление, проценты, ссылки и имена диапазонов. Функцию можно вложить в другую: например, округлить среднее одной записью. Справка Microsoft предупреждает, что вложенная функция должна возвращать значение того же типа, что ждёт аргумент, иначе формула вернёт #ЗНАЧ!^[4]. Логический аргумент, например, может принять только ИСТИНА или ЛОЖЬ, а не текст.

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

Список функций не обязательно учить наизусть: имя можно найти на вкладке «Формулы», а подсказка при вводе покажет аргументы^[4]. Расчёт доли и наценки — частая первая задача после сложения: формулы со ставкой разобраны на странице о процентах, наценке и скидке. Функции тоже протягиваются маркером, а имя функции всегда пишется с открывающей скобкой сразу за ним.

Именно поэтому ошибки в формулах требуют отдельного разбора: чем больше функций в записи, тем больше мест, где что-то может пойти не так.

Когда формула не сработает: #ИМЯ?, #ДЕЛ/0! и ручной пересчёт

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

• Если в имени функции опечатка, например =СУМ(B2:B4), Excel покажет #ИМЯ?: справка называет опечатку одной из причин этой ошибки^[6].
• При делении на ноль или на пустую ячейку результатом станет #ДЕЛ/0!^[7]: проверьте делитель или задайте проверку через ЕСЛИ.
• Когда вычисление книги переключено на ручной режим, расчёт при заполнении не обновляется^[5]: верните режим «Автоматически».
• Если текст внутри расчёта не взят в кавычки, Excel тоже вернёт #ИМЯ?^[6].

Ошибка #ДЕЛ/0! описана в справке не только для настольного Excel, но и для Excel для iPad, iPhone, Android и Excel Web App^[7]. Обработка ошибок работает только тогда, когда причина понятна: иначе неверный результат просто станет незаметным. Полный разбор кодов ошибок и способов их найти — на странице об ошибках в формулах и их исправлении. Темы тренажёра по всем разделам собраны на главной странице тренажёра формул и функций Excel.