Как суммировать значения без промежуточных итогов или исключить их в Excel?
При работе с наборами данных в Excel часто организуют и анализируют числа, вычисляя промежуточные итоги для различных групп или категорий. Однако после добавления этих промежуточных итогов может возникнуть необходимость рассчитать общий итог, исключая значения промежуточных итогов, чтобы избежать двойного учета при финальной сумме. Проблема заключается в том, что при прямом сложении всех чисел промежуточные итоги включаются в общий результат, что приводит к завышению итоговой суммы. Чтобы этого избежать, нужно найти способ сложить только исходные данные, исключив строки с промежуточными итогами. В этой статье мы представляем несколько практических методов решения этой задачи, чтобы вы могли эффективно рассчитывать точные результаты в разных сценариях.
Суммирование значений без промежуточных итогов с помощью функции СУММ в Excel
Суммирование значений без промежуточных итогов с помощью функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel
Код VBA - Суммирование значений с исключением строк промежуточных итогов
Фильтрация - Использование фильтра и функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ для суммирования только видимых (не являющихся промежуточными итогами) строк
Суммирование значений без промежуточных итогов с помощью функции СУММ в Excel
Общепринятый подход для расчета промежуточных итогей групп - это вставка функции СУММ внутри или под каждой соответствующей группой. Однако, когда вы хотите вычислить общий итог для всего списка, игнорируя эти строки с промежуточными итогами групп, требуется дополнительный шаг. Вот метод, который вы можете использовать для достижения этого:
1. Используйте функцию СУММ в каждой группе для генерации соответствующих промежуточных итогов, как показано на этом скриншоте:
2. Затем, чтобы найти общий итог без включения строк с промежуточными итогами, введите следующую формулу в пустую ячейку, где вы хотите увидеть результат:
=СУММ(B2:B21)/2
Нажмите Enter, чтобы получить правильный итог. Эта формула работает, потому что предполагает, что вы вставили промежуточные итоги с помощью функции СУММ сразу после соответствующих групп, что фактически удваивает сумму исходных данных. Деление на 2 устраняет дублированный итог. Обратите внимание, этот метод наиболее подходит, когда структура данных такова, что сумма исходных значений и промежуточных итогов вместе составляет ровно в два раза больше суммы исходных данных.
Если структура ваших данных более сложная или количество строк с промежуточными итогами различно, альтернативные методы, описанные ниже, могут предложить более точное и гибкое решение.
Суммирование значений без промежуточных итогов с помощью функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel предоставляет встроенную возможность выполнять вычисления только для отфильтрованных или видимых строк. Это особенно полезно при работе со списками, содержащими промежуточные итоги, поскольку формулы ПРОМЕЖУТОЧНЫЕ.ИТОГИ можно настроить так, чтобы они игнорировали другие результаты ПРОМЕЖУТОЧНЫХ.ИТОГОВ в диапазоне, тем самым предотвращая двойной учет значений.
Чтобы использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ в этом контексте, следуйте этим шагам:
1. Введите функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ для каждой группы следующим образом:
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;B2:B10)
Это применяет операцию СУММ (номер функции 9) к указанному диапазону и часто используется как часть встроенной функции Excel «Данные > Промежуточные итоги».
2. Для общего итога, исключающего внутренние промежуточные итоги, введите следующую формулу в выбранную ячейку:
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;B2:B21)
Нажмите Enter, чтобы автоматически суммировать значения данных, игнорируя любые вложенные функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в диапазоне. Этот подход подходит для списков, использующих функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ для сводок по группам, и устраняет риск многократного суммирования промежуточных итогов.
Имейте в виду, что функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ лучше всего работает, когда ячейки с промежуточными итогами были созданы с использованием ПРОМЕЖУТОЧНЫЕ.ИТОГИ, а не СУММ. Кроме того, если вы фильтруете или скрываете строки, ПРОМЕЖУТОЧНЫЕ.ИТОГИ можно настроить так, чтобы она суммировала только видимые (нераскрытые) данные, что полезно в ситуациях динамической отчетности.
Код VBA - Суммирование значений с исключением строк промежуточных итогов
Если структура ваших данных сложна или вы хотите автоматизированный метод для суммирования только строк с исходными данными (исключая промежуточные итоги), вы можете использовать простой макрос VBA. Этот подход позволяет вам программно определить и просуммировать только те строки, которые соответствуют вашим критериям, например, основываясь на специфическом форматировании, наличии формул или других отличительных атрибутах.
Например, если ваши строки с промежуточными итогами рассчитываются с помощью формул (например, СУММ или ПРОМЕЖУТОЧНЫЕ.ИТОГИ), а строки с исходными данными содержат только статические значения, вы можете настроить код VBA так, чтобы он суммировал только те ячейки в определенном столбце, которые не содержат формул. Вот как это сделать:
1. Нажмите Инструменты разработчика > Visual Basic чтобы открыть окно Microsoft Visual Basic for Applications.
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-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек