Как сделать формулу на весь столбец без пропущенных строк
Как сделать формулу на весь столбец в Excel: заполни нужные строки, настрой вычисляемый столбец и отличи расчёт каждой позиции от одной суммы по колонке.
Ольга
Основатель малого бизнеса, партнёр
8 мин чтения
Я добавила в смету последнюю работу, заполнила часы и ставку и посмотрела на итог: 3900 рублей. Пустую D4 заметила позже. В ней должен был появиться расчёт ещё на 800 рублей, а полный итог составлял 4700. После этого вопрос, как сделать формулу на весь столбец, для меня начинается с границ списка. Первая правильная ячейка ещё не означает, что рассчитаны все записи.
В моём примере три работы по подготовке выездной ярмарки. Разберу пропуск на этом коротком списке, затем добавлю четвёртую работу. Так будет видно, что заполняется разово, а что должно расширяться вместе с данными.
Почему итог не показал пропущенную формулу
Я собрала исходные данные в A1:D4. В первой строке заголовки «Работа», «Часы», «Ставка», «Сумма». Ставка указана в рублях за час, сумма получается в рублях. Вот значения, которые должны получиться после полного расчёта:
Работа · Часы · Ставка · Сумма
Сборка стойки — 2 — 900 — 1800
Развеска плакатов — 3 — 700 — 2100
Упаковка оборудования — 1 — 800 — 800
В черновике было две суммы. Третьей ещё не было. Поэтому сложились только 1800 и 2100. Полученное 3900 выглядело правдоподобно. Но формула суммирования не создаёт пропущенный расчёт работы, даже если диапазон включает пустую ячейку.
Если в A4 есть название работы, а в B4 и C4 записаны исходные числа, пустая D4 означает, что последняя запись ещё не рассчитана, и я сначала проверяю её, прежде чем обсуждать итог или искать ошибку в самой ставке.
Пример ниже рассчитан на русскоязычный настольный Excel Microsoft 365 для Windows. В открытой справке Microsoft заполнение вниз и СУММ также описаны для Excel 2024, 2021, 2019 и 2016. Здесь используются обычные числа, без времени в формате часов и минут.
Как сделать формулу на весь столбец в нужном диапазоне
Сначала я отделяю заголовок от данных. В D1 находится слово «Сумма», первую формулу надо вводить в D2. Для нашей сметы это `=B2*C2`: часы первой работы умножаются на её ставку.
Дальше можно заполнить сразу все три расчётные ячейки:
Введи `=B2*C2` в D2 и подтверди ввод.
Выдели D2:D4, включая верхнюю ячейку с формулой.
Нажми Ctrl+D: Excel заполнит выделение вниз по образцу сверху.
Выбери D4 и проверь, что там `=B4*C4`, а результат равен 800.
Альтернатива для того же диапазона — протянуть маркер заполнения от D2 до D4. В обоих случаях относительные ссылки меняются для каждой строки. Копии используют собственные часы и ставку, а не продолжают считать сборку стойки.
Я проверила нижний расчёт отдельно: один час по 800 рублей даёт 800. Это важнее, чем убедиться только в правильности верхних 1800. Именно последняя работа выпала из моей сметы.
«Весь столбец» здесь означает все строки с работами. Не нужно выделять пустые ячейки до конца листа ради буквальности запроса. И не стоит выделять D1 вместе с диапазоном: при заполнении сверху исходной ячейкой станет заголовок.
Найди последнюю строку с работой. Её номер подставь вместо 4. После заполнения всё равно открой нижнюю формулу. Наличие числа рядом не доказывает, что оно получено нужными ссылками.
Что я изменила для растущего списка
Заполнение D2:D4 решило текущий пропуск. Но завтра может появиться работа в строке 5. Разовая команда не означает, что любая будущая запись где угодно на листе автоматически получит расчёт. Мне понадобился другой способ организации списка.
Для растущей сметы подходит объект «Таблица Excel» с вычисляемым столбцом. Это специальный объект, а не только нарисованные границы вокруг ячеек. В него можно ввести одну формулу, которую Excel распространит по расчётному столбцу.
Чтобы воспроизвести этот способ, начни с чистой копии исходных столбцов A:C: заголовки в строке 1, три работы в строках 2–4. Справа оставь свободное место.
Выдели A1:C4 и выбери «Главная» → «Форматировать как таблицу».
Выбери стиль, проверь диапазон и отметь, что у данных уже есть заголовки.
На вкладке «Конструктор таблиц» задай имя таблицы «Работы» в поле «Имя таблицы».
В D1 введи «Сумма»: новый столбец должен войти в таблицу.
Первой работе в D2 нужна формула `=[@Часы]*[@Ставка]`. Клавиша Enter подтвердит её ввод.
Знак @ указывает на текущую строку. Формула берёт часы и ставку одной работы. У вычисляемого столбца она распространяется на остальные строки таблицы. Здесь не требуется вручную менять B2 на B3 или повторять вставку по ячейкам.
Я оставляю проверку D4 и в этом варианте: ожидается 800. Если расчёт не появился, сначала проверь, входит ли D в объект таблицы и сформирован ли вычисляемый столбец. Подставлять число вручную вместо выяснения причины я бы не стала: это скроет незавершённую настройку.
Почему один итог не заполняет расчётные строки
Отдельный вопрос — как получить сумму по колонке. В обычном диапазоне можно поместить в F2 формулу `=СУММ(D2:D4)`. Она вернёт одно значение, 4700, когда все три работы рассчитаны. Формулы внутри D2:D4 от этой команды не появятся.
Если нужны все числовые значения физического столбца D, запись будет `=СУММ(D:D)`. Размещай такой итог вне D. Иначе формула включит собственную ячейку и создаст циклическую ссылку.
У D:D нет знания о границах именно этой сметы. В сумму могут попасть и другие числа ниже, включая вручную добавленный промежуточный итог. Поэтому для нашего списка понятнее назвать диапазон работ или воспользоваться столбцом таблицы.
Для таблицы с именем «Работы» внешний итог в F2 можно записать так: `=СУММ(Работы[Сумма])`. Эта ссылка охватывает данные столбца «Сумма» без его заголовка и строки итогов таблицы. Название таблицы и название столбца должны совпадать с твоими, а не просто быть похожими.
Результата теперь два. В D рассчитана каждая работа. В F2 находится общий итог. Это разные задачи.
Как сделать формулу на весь столбец и проверить новую запись
Я добавляю четвёртую работу непосредственно под таблицей, в A5:C5: «Перенос подставок», 2 часа, ставка 650. При таком вводе таблица расширяется. Вычисляемый столбец должен продолжить расчёт для включённой строки.
Я проверила числа новой записи: `2*650` даёт 1300. После её включения общий итог должен составить 6000. Но сначала смотрю на границу таблицы и D5, а уже потом на F2.
Пустая строка-разделитель между таблицей и новой работой меняет ситуацию. Не рассчитывай, что запись в произвольном месте листа будет автоматически включена. Проверь, вошла ли она в тот объект, к которому относится вычисляемый столбец.
Моя проверка после добавления теперь короткая:
строка с новой работой находится внутри таблицы «Работы»;
в D5 стоит расчёт, а не вручную переписанное число;
часы 2 и ставка 650 дают 1300;
внешний итог ссылается на нужный столбец таблицы;
после новой записи получается 6000, а не прежние 4700.
У обычного диапазона проверяй отдельно и нижнюю формулу, и охват итога. Запись D2:D4 явно называет строки 2–4: при перестройке списка надо посмотреть, какие адреса оказались в формуле после изменения. Полагаться только на прежнюю сумму я больше не хочу.
Охват строк и правильность самого выражения приходится проверять отдельно. Если нужно разобрать адреса в своей формуле, можно продолжить с материалом о нейросети для формул. В нашей смете даже верное умножение в D2 не устраняет пропуск D4. Поэтому в следующем запросе я называю не только формулу, но и последнюю заполненную строку.
Как описать свой диапазон в запросе к LibraChat
Если устройство листа отличается, можно попросить объяснение по его схеме. Я предлагаю передать координаты и заранее вычисленный результат одной позиции. Вот запрос под нашу смету:
В Excel заголовки A1:D1: Работа, Часы, Ставка, Сумма. Данные в строках 2–4. В D2 формула =B2*C2. B4=1, C4=800, ожидаю D4=800. Объясни, как заполнить D2:D4, сохранив D1. Затем предложи способ для списка, куда добавляются работы. Отдельно покажи, где разместить одну сумму по расчётному столбцу. Опирайся на эту схему: доступа к моей открытой книге у тебя нет.
Вставь в чат описание своего листа и разбери границы расчёта в LibraChat. Сопоставь предложение с фактической последней строкой. Если там осталось пусто, общий итог ещё не подтверждает полноту заполнения.
Для регулярной работы с такими реестрами можно заранее изучить тарифы LibraChat.
К исходным трём работам относятся 4700 рублей, после четвёртой ожидаются 6000. Начальные 3900 были верным сложением неполного списка. Поэтому перед отправкой сметы я теперь открываю последнюю расчётную ячейку: есть ли формула именно у последней работы?
