Как в экселе сделать формулу суммы: итог, клиент и фильтр
Как в экселе сделать формулу суммы для всего столбца, одного клиента и видимых строк после фильтра, и в какие клетки эти формулы лучше не ставить.
Ольга
Основатель малого бизнеса, партнёр
6 мин чтения
Первый мой вопрос о том, как в экселе сделать формулу суммы, получился неудачным, и виновата в этом я. Таблицу оплат я описала одной фразой и попросила итог. Ответ дал верную формулу =СУММ(B:B) и верный совет: поставить итог в пустую клетку под таблицей. По отдельности. Вместе они давали ошибку.
Первый ответ: формула верная, место для неё нет
Запрос был короткий, каким его пишут, когда некогда:
Как в экселе сделать формулу суммы? У меня таблица оплат клиентов за месяц, нужно посчитать итог.
Ответ LibraChat начался правильно: функция СУММ и диапазон, например =СУММ(B2:B50), то есть сложить клетки со второй по пятидесятую. Диапазон можно не набирать, а выделить мышкой после открывающей скобки. Это я бы оставила как есть.
Место для формулы ответ назвал такое:
Поставь курсор в пустую ячейку под таблицей (или в отдельном столбце) и введи:
А дальше предложил сложить столбец целиком, формулой =СУММ(B:B). Каждый совет верный. Но если поставить формулу на весь столбец B в пустую клетку под таблицей, то есть в тот же столбец B, формула начнёт считать саму себя. Excel предупредит о циклической ссылке, и вместо итога в клетке будет ноль. Оговорка «или в отдельном столбце» в ответе есть, но к этой формуле она не привязана, и о ловушке ответ молчит. Ответ тут ошибся: он обещал, что Excel сразу покажет сумму.
Правило простое. Формулу на весь столбец ставят в другом столбце. Под таблицей ставят формулу с диапазоном до последней строки данных.
И ещё одного способа в ответе не было, хотя он самый короткий. Встаньте в клетку под столбцом с суммами и нажмите Alt вместе со знаком равенства. Это та же кнопка «Автосумма» со знаком Σ на вкладке «Главная». Excel сам вставит СУММ и обведёт рамкой диапазон, который счёл нужным. Проверьте рамку глазами и нажмите Enter. Глазами потому, что рамка останавливается на первой пустой клетке: если среди оплат есть пропуск, Excel обведёт только нижнюю часть столбца, и итог выйдет меньше настоящего.
А если складывать нужно клетки вразброс, перечислите их через точку с запятой: =СУММ(B2;B7;B15). Или после открывающей скобки щёлкайте по ним мышкой с зажатым Ctrl.
Для одного итога этого хватает. Но в таблице оплат итог нужен редко сам по себе. Сколько заплатил конкретный клиент? Сколько пришло за октябрь?
Второй запрос: три суммы вместо одной
Во втором запросе я описала таблицу так, как её увидел бы человек за моим компьютером: какие столбцы, сколько строк, что именно посчитать. Таблицу я на этот раз взяла за квартал: фильтр по месяцу имеет смысл, только когда месяцев несколько.
В Excel таблица оплат за квартал: в столбце A название клиента, в столбце B месяц, в столбце C сумма оплаты, строки со 2 по 200. Нужны три формулы: общая сумма, сумма только по клиенту «Альфа» и сумма только видимых строк, когда я включаю фильтр по месяцу. Напиши формулы для русского Excel и объясни, в какую ячейку их ставить.
Замените буквы столбцов, номера строк и имя клиента на свои. Слова «для русского Excel» оставьте: в русской версии функции называются по-русски, и формула с английским названием выдаст ошибку.
Ответ дал три формулы. Общая сумма: =СУММ(C2:C200). Сумма по клиенту: =СУММЕСЛИ(A2:A200;"Альфа";C2:C200), то есть где искать, что искать и что складывать. И сумма видимых строк: =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;C2:C200). Число 109 значит «сложить, пропуская скрытые строки». Про последнюю формулу ответ добавил:
Последняя формула автоматически пересчитывается при фильтрации — она суммирует только отображаемые строки.
Это верно, и это главное открытие для тех, кто считает выручку по месяцам. Обычная СУММ фильтра не замечает и складывает всё, включая спрятанные строки. Включили фильтр по октябрю, а итог остался квартальным. Такая цифра уходит в сверку с клиентом или в отчёт партнёру, и объяснять расхождение потом придётся вам.
Клетка, которую спрячет фильтр
Место для формул ответ назвал точно: ячейки E1, E2 и E3. И добавил:
Формулы можно разместить в любом свободном столбце справа от таблицы.
Столбец свободный, тут спорить не с чем. Но строки у него общие с таблицей. Включите фильтр по месяцу, и Excel спрячет строки, где месяц другой. Если во второй или третьей строке была оплата за ноябрь, а фильтр стоит на октябре, то вместе с ней исчезнут и формулы в E2 и E3. Сумма видимых строк сама станет невидимой.
Я бы поставила формулы над таблицей. Вставьте три пустые строки над шапкой: в первых двух будут итоги и условия, а третью оставьте пустой, чтобы при включении фильтра Excel не принял итоги за часть шапки. Фильтр работает только с теми строками, что ниже шапки, и итог над таблицей останется на виду. Заодно он будет первым, что видно при открытии файла.
После вставки шапка уедет в четвёртую строку, а данные начнутся с пятой. Формулы, которые уже стояли в столбце E, Excel поправит сам: их можно вырезать и перенести наверх, ссылки при вырезании не ломаются. Набираете формулы заново — берите диапазон с запасом, например C5:C1000. Пустые клетки сумму не меняют, а новые оплаты попадут в итог без правки формулы.
С суммой по клиенту я бы сделала ещё одну правку. Имя клиента в кавычках внутри формулы неудобно: чтобы посмотреть другого клиента, придётся править формулу. Впишите имя в отдельную клетку над шапкой, например G1, и сошлитесь на неё: =СУММЕСЛИ(A5:A1000;G1;C5:C1000). Теперь клиент меняется в одной клетке.
А если нужен и клиент, и месяц сразу, одной СУММЕСЛИ мало. Для двух условий есть СУММЕСЛИМН, и порядок у неё другой: сначала столбец сумм, потом пары «где искать, что искать». Например, =СУММЕСЛИМН(C5:C1000;A5:A1000;G1;B5:B1000;H1), где в H1 записан месяц. Обе клетки с условиями стоят над шапкой, и фильтр их не спрячет.
И проверьте, как имя записано в самой таблице. «Альфа» и «ООО Альфа» для формулы разные клиенты, а лишний пробел в конце названия тоже делает его другим. Если сумма по клиенту вышла меньше, чем вы ждали, ищите сначала здесь.
Что нейросеть не видела
Нейросеть не видела таблицу, только её описание. Если формула в вашей таблице всё равно считает странно, например пропускает часть чисел, загляните в разбор пяти ошибок формул Excel. А проверить итог чужой таблицы помогут приёмы из статьи, где нейросеть решает таблицу с пустыми клетками.
Одна строка над шапкой
Что бы я спросила, если бы начинала заново? Не «как сделать формулу суммы», а «куда её поставить, чтобы её не спрятал фильтр и не сломали новые строки».
Если таблица у вас устроена сложнее, опишите её LibraChat так же подробно, как во втором запросе: буквы столбцов, номера строк, что посчитать. Таблицу можно отправить и файлом Excel, тогда описывать её словами не придётся. В приложении на телефоне таблицы в выборе документов могут не показаться, поэтому файл удобнее отправлять из браузера.
Вставьте над шапкой своей таблицы две пустые строки, в верхнюю поставьте сумму видимых строк, а нижнюю оставьте пустой. Изменится ли итог, когда вы включите фильтр по месяцу?
как в экселе сделать формулу суммыбизнес и документыexcelформулы
