Как сделать сумму в таблице Excel и не потерять строку
Как сделать сумму в таблице Excel: формула СУММ, правильный диапазон и проверка чисел. Разбери пропущенные строки, текст вместо сумм и итоги после фильтра.
Д
Денис
Продакт LibraChat
8 мин чтения
В списке поездки у меня получалось 8700 рублей. Я уже собрал расходы на дорогу, ночёвку и еду, потом дописал трансфер за 600. Итог остался прежним. Формула не сломалась: последняя строка просто не входила в её диапазон. Разбираясь, как сделать сумму в таблице, я перестал начинать с кнопки и стал сначала проверять границы чисел, которые хочу сложить.
Коротко: в Excel поставь итог вне исходных сумм и укажи диапазон, например `=СУММ(B2:B6)`. В английской версии это `=SUM(B2:B6)`. После добавления расхода проверь, входит ли его ячейка в формулу. Видимое число ещё может храниться как текст.
Как сделать сумму в таблице Excel: задать начало и конец
В моём списке первая сумма находится в B2, последняя — в B6, итог — в B7. Столбец A отвечает за названия расходов, B — за суммы; первая строка занята заголовками. По этим адресам я отделяю нужные числа от номера билета или количества участников.
У полного списка итог 9300, у диапазона до B5 — 8700. Эти 600 рублей я связываю с трансфером: именно этот расход остался за границей. Значит, исправлять цены или заново переносить таблицу незачем — нужно вернуть расход в границы суммы.
Для такого листа сделай следующее:
Введи суммы в B2:B6 отдельными числами.
Выбери B7, куда будет записан результат.
Набери `=СУММ(` и выдели мышью ячейки от B2 до B6. Либо напиши их адреса вручную.
Закрой скобку, чтобы получилась запись `=СУММ(B2:B6)`.
Нажми Enter и сравни результат с контрольными 9300.
Снова выдели B7 и прочитай формулу в строке формул. Проверь обе границы, а не только первое число.
Двоеточие означает непрерывный диапазон: от B2 до B6 включительно. Это пять ячеек одного столбца. Если написать B2:B5, Excel не узнает, что ты хотел захватить ещё одну строку.
Итоговая ячейка B7 остаётся снаружи. Формула `=СУММ(B2:B7)`, введённая в саму B7, ссылается на себя. Получается циклическая ссылка, а не расширенный итог. Поэтому мысль «на всякий случай захвачу весь столбец» здесь опаснее, чем небольшой явный диапазон.
Я не зашиваю в формулу сами цены длинной цепочкой `2400+3600+…`. После изменения расхода пришлось бы искать его ещё и внутри записи. Ссылки на ячейки оставляют исходные данные на виду. Варианты самой записи подробнее разобраны в материале о формуле суммы в Excel.
Автосумма предлагает диапазон, а не читает список расходов
Можно обойтись без ручного ввода функции. В настольном Excel выбери ячейку под числами, затем «Автосумма» на вкладке «Главная» или «Формулы». Excel вставит формулу и выделит ячейки для сложения. Перед Enter просмотри выделение.
Вот здесь я раньше торопился. Появилась рамка, значит всё готово. Но между расходами могла стоять пустая строка, а ниже лежать дописанная покупка. Предложение программы надо сопоставить со своим списком. Не с внешним видом блока.
Для нашего листа нужны B2:B6. Если выделился другой участок, исправь ссылку до подтверждения. Автосумма экономит набор имени функции, но решение о том, какие расходы относятся к поездке, остаётся у тебя.
Для горизонтального ряда правило выбора другое: итог ставится справа от чисел. Например, суммы B2:D2 складываются в E2 формулой `=СУММ(B2:D2)`. Сам диапазон теперь идёт по строке. Не копируй адреса из вертикального примера, если данные расположены иначе.
Названия функций зависят от языка Excel. Здесь основная запись дана для русской версии, рядом указано английское имя SUM. Если используешь другой интерфейс, выбирай соответствующее имя из подсказки программы. Принцип с двумя границами диапазона сохраняется.
Почему полный диапазон иногда даёт только 7500
Я попробовал проследить ещё одну причину расхождения: сумма 1800 после копирования выглядела как число, но была текстом. В ссылочном диапазоне функция СУММ пропускает текстовые значения и складывает числовые. Без этой строки наш набор даёт 7500. Пропуск не обязан сопровождаться заметной ошибкой.
Поэтому я отдельно проверяю подозрительную ячейку. Если Excel показывает предупреждение «Число сохранено как текст», у него есть команда преобразования в число. Выдели нужные ячейки, открой индикатор предупреждения и выбери «Преобразовать в число».
Если в содержимом набрано «1800 рублей», это уже смешение числа с подписью. В числовой ячейке оставь 1800, а валюту показывай денежным форматом или вынеси в заголовок столбца. При этом денежный формат и приписанное к цифрам слово устроены по-разному: первый меняет отображение числового значения, второе может сделать содержимое текстом.
Не пытайся исправить всё выравниванием вправо. Оно меняет оформление, а не доказывает тип значения. После преобразования вернись к итогу и проверь, появилась ли ожидаемая разница. В нашем примере она составляет 1800.
Мне помогает короткий порядок поиска:
Сначала сравнить диапазон с первой и последней нужной строкой.
Затем проверить, что суммы действительно числовые.
После этого искать лишние включённые итоги и повторяющиеся расходы.
Так не приходится наугад менять всё сразу. Исправление границы не преобразует текст в число. А преобразование числа не добавит в диапазон строку, которой там нет.
Как сделать сумму в таблице после добавления расхода
Допустим, к исходным пяти расходам добавилась камера хранения за 450. Полный расчёт теперь должен дать 9750. Но сначала посмотри, куда именно ты добавил запись.
При вставке строки внутри уже указанного диапазона ссылка SUM корректируется вместе с изменением листа. Microsoft отдельно оговаривает границу: добавление снаружи диапазона нельзя считать тем же действием. Поэтому я не полагаюсь на одно слово «вставил».
Если новый расход оказался в B7, итог надо разместить ниже, например в B8, и проверить диапазон B2:B7. Итоговая ячейка снова находится за его пределами. Если вставка переместила строки иначе, ориентируйся на фактические адреса, а не на прежний номер ячейки из инструкции.
После такой правки я задаю себе два вопроса. Вошла ли новая сумма? Не вошёл ли вместе с ней старый итог? Второй вопрос легко забыть, когда первый наконец решён.
Крупному списку полезна ещё одна договорённость: одна строка означает один расход. Если в середине есть подытоги, общий диапазон СУММ нельзя бездумно растянуть поверх них вместе с исходными суммами. Одна и та же покупка тогда будет посчитана через свою строку и ещё раз через подытог.
Если строку скрыли, расход не исчез
Фильтр и ручное скрытие меняют видимость списка. Обычная СУММ продолжает учитывать числовые значения диапазона. Если тебе нужен общий бюджет поездки, такое поведение может быть правильным. Если нужна сумма только отобранных строк, задача уже другая.
Для вертикального списка Excel предлагает ПРОМЕЖУТОЧНЫЕ.ИТОГИ, по-английски SUBTOTAL. У суммирования используются разные номера функции:
Запись для B2:B6 · Строки, отсеянные фильтром · Строки, скрытые вручную
`=СУММ(B2:B6)` — учитываются — учитываются
`=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;B2:B6)` — не учитываются — учитываются
`=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;B2:B6)` — не учитываются — не учитываются
В английской записи пример выглядит как `=SUBTOTAL(109,B2:B6)`. Имя функции и разделитель аргументов должны соответствовать настройкам твоего Excel. Эти правила относятся к вертикальному диапазону: скрытие столбца не надо считать равноценной заменой скрытию строки.
Полный бюджет моей поездки включает и расход, скрытый фильтром. Для него я беру СУММ. Если мне нужна только отобранная часть списка, обращаюсь к ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Правильная сумма видимых расходов ещё не означает, что посчитан весь бюджет.
Для обсуждения формулы в LibraChat подготовь адреса исходных ячеек и место итога. Чат умеет объяснять задачи; ему не нужно угадывать расположение твоего листа. Попробуй такой запрос:
В Excel расходы стоят в B2:B6: 2400, 3600, 1800, 900, 600. Итог находится в B7. Объясни формулу суммы и проверь границы. Затем разбери, почему запись B2:B5 пропустит расход, а B2:B7 в самой B7 создаст ссылку на себя. Отдельно объясни, что изменится, если 1800 хранится текстом. Дай проверки, которые я выполню в своей копии листа.
Переноси предложенные изменения в копию, где можешь проверить исходные данные. Для дальнейшей работы с сервисом можно заранее посмотреть возможности тарифов.
В своей копии увеличь последний расход на 100 рублей. Итог должен вырасти ровно на 100, если эта числовая ячейка входит в нужный диапазон и расчёт обновлён. Потом верни прежнее значение. Такая проверка последней строки полезнее, чем ещё раз убедиться, что итог красиво выделен жирным.
