Как суммировать значения в Excel, не включая промежуточные итоги?
При работе с наборами данных в Excel часто возникает необходимость структурировать и анализировать числовую информацию, вычисляя промежуточные итоги по разным группам или категориям. Однако после их добавления может понадобиться рассчитать общий итог, исключающий эти промежуточные значения, чтобы избежать двойного учёта в финальной сумме. Проблема в том, что при обычном суммировании всех чисел промежуточные итоги тоже попадают в расчёт, завышая итоговый результат. Чтобы этого не допустить, нужно уметь суммировать только исходные данные, игнорируя строки с промежуточными итогами. В этой статье представлены несколько практических методов решения этой задачи, позволяющих точно и эффективно получать корректные результаты в различных ситуациях.
Суммирование значений без промежуточных итогов с помощью функции СУММ в Excel
Суммирование значений без промежуточных итогов с помощью функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel
Код VBA — суммирование значений с исключением строк промежуточных итогов
Фильтрация — использование фильтра и функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ для суммирования только видимых (не промежуточных) строк
Суммирование значений без промежуточных итогов с помощью функции СУММ в Excel
Распространённый способ подсчёта промежуточных итогов по группам — размещение функции СУММ внутри или сразу под каждой группой. Однако если нужно получить общий итог по всему списку, игнорируя строки с промежуточными итогами, обычно требуется дополнительное действие. Ниже описан метод, позволяющий достичь этой цели:
1. Используйте функцию СУММ внутри каждой группы, чтобы получить соответствующие промежуточные итоги, как показано на этом снимке экрана:
2. Затем, чтобы получить общий итог без учёта строк промежуточных итогов, введите в пустую ячейку (где должен отобразиться результат) следующую формулу:
=SUM(B2:B21)/2
Нажмите Enter, чтобы получить корректный итог. Эта формула работает, потому что промежуточные итоги, созданные с помощью функции СУММ, предполагаются расположенными сразу после соответствующих групп — фактически удваивая сумму исходных данных. Деление на 2 устраняет это дублирование. Обратите внимание: такой метод лучше всего подходит, когда структура набора данных устроена так, что совокупная сумма исходных значений и промежуточных итогов составляет ровно удвоенную сумму исходных данных.
Если структура ваших данных более сложная или количество строк с промежуточными итогами варьируется, альтернативные методы, описанные ниже, обеспечат более точное и гибкое решение.
Суммирование значений без промежуточных итогов с помощью функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel — это встроенное решение для выполнения расчётов исключительно по отфильтрованным или видимым строкам. Она особенно полезна при работе со списками, содержащими промежуточные итоги: формулы ПРОМЕЖУТОЧНЫЕ.ИТОГИ можно настроить так, чтобы они игнорировали другие результаты ПРОМЕЖУТОЧНЫЕ.ИТОГИ в диапазоне, предотвращая тем самым двойной учёт значений.
Чтобы использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ в данном контексте, выполните следующие действия:
1. Введите функцию =SUBTOTAL(9,B2:B10) для каждой группы следующим образом:
Это применяет операцию СУММ (номер функции 9) к Ограниченный диапазон и часто используется как часть встроенной функции Excel «Данные > Промежуточные итоги».
2. Чтобы рассчитать общий итог, игнорируя внутренние промежуточные итоги, введите в нужную ячейку следующую формулу:
=SUBTOTAL(9,B2:B21)
Нажмите Enter, чтобы автоматически просуммировать значения данных, игнорируя любые вложенные функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в диапазоне. Этот подход идеально подходит для списков, где для сводки по группам используется функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ, и полностью исключает риск многократного суммирования промежуточных итогов.
Имейте в виду, что функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ даёт наилучший результат, если ячейки промежуточных итогов были созданы с помощью ПРОМЕЖУТОЧНЫЕ.ИТОГИ, а не СУММ. Кроме того, если вы применяете фильтр или скрываете строки, ПРОМЕЖУТОЧНЫЕ.ИТОГИ можно настроить так, чтобы суммировать только видимые (непрятые) данные — это полезно при динамическом составлении отчётов.
Код VBA — суммирование значений с исключением строк промежуточных итогов
Если структура ваших данных сложна или вы хотите автоматизировать суммирование только исходных строк — исключая промежуточные итоги, — воспользуйтесь простым макросом VBA. Этот подход позволяет программно находить и суммировать лишь те строки, которые соответствуют заданным критериям: например, определённому форматированию, наличию формул или другим отличительным признакам.
Например, если строки промежуточных итогов рассчитываются с помощью формул (например, СУММ или ПРОМЕЖУТОЧНЫЕ.ИТОГИ), а строки исходных данных содержат только статические значения, можно настроить код VBA так, чтобы он суммировал только те ячейки в Укажите столбец, которые не содержат формул. Ниже приведено, как это сделать:
1. Щёлкните Средства разработчика > Visual Basic, чтобы открыть окно Microsoft Visual Basic для приложений.
2. В новом окне нажмите Вставка > Модуль и вставьте следующий код в модуль:
Sub SumNonSubtotalRows()
Dim WorkRng As Range
Dim SumResult As Double
Dim cell As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select the range to sum (e.g., B2:B21)", xTitleId, WorkRng.Address, Type:=8)
SumResult = 0
For Each cell In WorkRng
If Not cell.HasFormula Then
SumResult = SumResult + cell.Value
End If
Next
MsgBox "The sum of non-subtotal rows is: " & SumResult, vbInformation, xTitleId
End Sub3. Нажмите кнопку 
Выполнить, чтобы запустить код. Появится запрос на выбор диапазона с вашими данными. Макрос просуммирует только те ячейки в выделенном диапазоне, которые не содержат формул, автоматически пропуская типичные строки промежуточных итогов.
Фильтрация — использование фильтра и функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ для суммирования только видимых (не промежуточных) строк
Если в вашем наборе данных можно отфильтровать строки промежуточных итогов (например, по метке, ключевому слову или шаблону, позволяющему их идентифицировать), можно использовать встроенную функцию фильтрации Excel совместно с функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ для суммирования только видимых (не отфильтрованных) строк данных. Этот метод удобен в случаях, когда промежуточные итоги имеют единый индикатор или метку.
1. Щёлкните в любом месте набора данных, перейдите на вкладку Данные и нажмите Фильтр, чтобы включить фильтрацию по столбцам.
2. Щёлкните раскрывающийся список во вспомогательном столбце, снимите флажок «Промежуточный итог», чтобы скрыть соответствующие строки и отобразить только исходные данные.
3. В отдельной ячейке введите следующую формулу, чтобы просуммировать только видимые (отфильтрованные) строки.
=SUBTOTAL(9,B2:B21)Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ (с номером функции 9 для СУММ) игнорирует скрытые строки и возвращает сумму только тех строк, которые в данный момент видимы (то есть исходных строк данных, оставшихся после фильтрации). Это решение быстро и динамично: при повторном применении фильтров или расширении набора данных формула автоматически обновится.
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек