Как суммировать только отфильтрованные или видимые ячейки в Excel?
Возможно, вам легко суммировать столбец чисел в Excel, но иногда вам нужно отфильтровать или скрыть некоторые данные, чтобы они соответствовали вашим критериям. После скрытия или фильтрации, и теперь вы просто хотите добавить только отфильтрованные или видимые значения. Если вы примените функцию Sum в Excel, все значения, включая скрытые данные, будут добавлены, в этом случае, как вы могли суммировать только отфильтрованные или видимые значения ячеек в Excel?
- Суммировать только отфильтрованные или видимые значения ячеек с помощью формулы
- Суммируйте только отфильтрованные или видимые значения ячеек с помощью функции, определяемой пользователем
- Сумма / подсчет / среднее только отфильтрованные или видимые ячейки с помощью Kutools for Excel
Суммировать только отфильтрованные или видимые значения ячеек с помощью формулы
С этой SUBTOTAL функция, которая игнорирует строки, которые были исключены фильтром, вы можете легко добавить только видимые ячейки. Вы можете сделать так:
Предположим, у вас есть диапазон данных, и они были отфильтрованы по мере необходимости, см. Снимок экрана:
1. В пустой ячейке, например, C13, введите следующую формулу: = Итого (109; C2: C12) (109 указывает на то, что при суммировании чисел скрытые значения игнорируются; C2: C12 - диапазон, который вы суммируете, игнорируя отфильтрованные строки.), и нажмите Enter .
Примечание: Эта формула также может помочь вам суммировать только видимые ячейки, если на вашем листе есть скрытые строки. Однако эта формула не может суммироваться с игнорированием ячеек в скрытых столбцах.
Сумма / количество / среднее значение видимых ячеек только в указанном диапазоне с игнорированием скрытых или отфильтрованных ячеек / строк / столбцов
Обычно функция SUM / Count / Average будет подсчитывать все ячейки в указанном диапазоне, если ячейки материи скрыты / отфильтрованы или нет. В то время как функция Subtotal может только суммировать / подсчитывать / усреднять, игнорируя скрытые строки. Однако Kutools для Excel СУЩЕСТВЕННЫЙ / СОВМЕСТНЫЙ / СРЕДНЯЯВИДИМАЯ функции легко вычисляют указанный диапазон, игнорируя любые скрытые ячейки, строки или столбцы.
Суммируйте только отфильтрованные или видимые значения ячеек с помощью функции, определяемой пользователем
Если вас интересует следующий код, он также может помочь вам суммировать только видимые ячейки.
1. Удерживайте ALT + F11 ключи, и он открывает Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модулии вставьте следующий код в окно модуля.
Function SumVisible(WorkRng As Range) As Double
'Update 20130907
Dim rng As Range
Dim total As Double
For Each rng In WorkRng
If rng.Rows.Hidden = False And rng.Columns.Hidden = False Then
total = total + rng.Value
End If
Next
SumVisible = total
End Function
3. Сохраните этот код и введите формулу = SumVisible (C2: C12) в пустую ячейку. Смотрите скриншот:
4, Затем нажмите Enter key и вы получите желаемый результат.
Сумма / подсчет / среднее только отфильтрованные или видимые ячейки с помощью Kutools for Excel
Если у вас установлен Kutools for Excel, вы можете быстро вычислить сумму / количество / среднее значение только видимых или отфильтрованных ячеек в Excel.
Kutools for Excel - Дополните Excel более чем 300 основными инструментами. Наслаждайтесь полнофункциональной 30-дневной БЕСПЛАТНОЙ пробной версией без необходимости использования кредитной карты! Get It Now
Например, вы хотите суммировать только видимые ячейки, выберите ячейку, в которую вы поместите результат суммирования, введите формулу = СУММВИДИМ (C3: C12) (C3: C13 - это диапазон, в котором вы суммируете только видимые ячейки) в него и нажмите Enter .
И тогда результат суммирования вычисляется без учета всех скрытых ячеек. Смотрите скриншот:
Для подсчета только видимых ячеек примените эту формулу = СОВМЕСТНЫЙ (C3: C12); Для усреднения только видимых ячеек примените эту формулу = СРЕДНЕВИДИМОЕ (C3: C12).
Примечание: Если вы не можете точно запомнить формулы, вы можете выполнить следующие шаги, чтобы легко суммировать / подсчитывать / усреднять только видимые ячейки:
1. Выберите ячейку, в которую вы поместите результат суммирования, и нажмите Кутулс > функции > Статистические и математические > СУЩЕСТВЕННЫЙ (или СРЕДНИЙ ВИДИМОЙ, СОВМЕСТНЫЙ как вам нужно). Смотрите скриншот:
2. В открывшемся диалоговом окне «Аргументы функций» укажите диапазон, в котором вы будете суммировать, игнорируя скрытые ячейки, и нажмите кнопку OK кнопка. Смотрите скриншот:
Kutools for Excel - Дополните Excel более чем 300 основными инструментами. Наслаждайтесь полнофункциональной 30-дневной БЕСПЛАТНОЙ пробной версией без необходимости использования кредитной карты! Get It Now
И тогда результат суммирования вычисляется без учета всех скрытых ячеек.
Демонстрация: сумма / подсчет / среднее только отфильтрованные или видимые ячейки
Легко суммировать / подсчитывать только отфильтрованные / видимые ячейки, удаляя скрытые строки в Excel
При суммировании / подсчете отфильтрованных ячеек в Excel функция СУММ или функция подсчета не будет игнорировать скрытые ячейки. Если скрытые / отфильтрованные строки удалены, мы можем легко суммировать или подсчитывать только видимые ячейки. Вы можете попробовать Kutools for Excel's Удалить скрытые (видимые) строки и столбцы утилита для ее решения.
Статьи по теме:
Как суммировать абсолютные значения в Excel?
Как быстро суммировать каждую вторую или n-ю строку / столбец в Excel?
Лучшие инструменты для офисной работы
Улучшите свои навыки работы с Excel с помощью Kutools for Excel и почувствуйте эффективность, как никогда раньше. Kutools for Excel предлагает более 300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы получить функцию, которая вам нужна больше всего...
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!