Note: The other languages of the website are Google-translated. Back to English
English English

Як підсумувати лише відфільтровані або видимі комірки в Excel?

Можливо, вам легко підсумувати стовпець цифр в Excel, але іноді вам потрібно відфільтрувати або приховати деякі дані, щоб відповідати вашим критеріям. Після приховування або фільтрування, і тепер ви просто хочете скласти лише відфільтровані або видимі значення. Якщо ви застосуєте функцію підсумовування в Excel, усі значення, включаючи приховані дані, будуть додані, як у цьому випадку, як ви можете підсумувати лише відфільтровані або видимі значення комірок у Excel?


Сумуйте лише відфільтровані або видимі значення комірок за формулою

З цим SUBTOTAL Функція, яка ігнорує рядки, виключені фільтром, дозволяє легко додати лише видимі клітинки. Ви можете зробити так:

Припустимо, у вас є ряд даних, і вони були відфільтровані, як вам потрібно, див. Знімок екрана:

1. Наприклад, у порожню комірку, C13, введіть таку формулу: = Проміжна сума (109, C2: C12) (109 вказує, коли підсумовуєте цифри, приховані значення ігноруватимуться; C2: C12 - діапазон, який ви підсумуєте, ігноруючи відфільтровані рядки.), і натисніть клавішу вводити ключ

Примітка: Ця формула також може допомогти вам підсумувати лише видимі клітинки, якщо на вашому аркуші є приховані рядки. Однак ця формула не може підсумовувати з ігноруванням комірок у прихованих стовпцях.

Сума / кількість / середнє значення видимих ​​комірок лише у визначеному діапазоні з ігноруванням прихованих або відфільтрованих комірок / рядків / стовпців

Зазвичай функція SUM / Count / Average підраховує всі клітини у вказаному діапазоні на клітинах речовини, приховані / відфільтровані чи ні. У той час як функція Проміжні підсумки може підсумовувати / рахувати / усереднювати лише ігноруючи приховані рядки. Однак Kutools для Excel ПІДКРИВЕ / ВИДИМО / СЕРЕДОВИЙ функції легко обчислюють вказаний діапазон, ігноруючи будь-які приховані комірки, рядки або стовпці. Повнофункціональна безкоштовна пробна версія 30 днів!
кількість підсумків оголошень лише середнє значення видимих ​​комірок

Сумуйте лише відфільтровані або видимі значення комірок за допомогою функції, визначеної користувачем

Якщо вас цікавить наступний код, він також може допомогти вам підсумувати лише видимі клітинки.

1. Утримуйте клавішу ALT + F11 і відкриває Microsoft Visual Basic для додатків вікна.

2. Клацання Insert > Модуліта вставте наступний код у вікно Модуль.

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. Потім натисніть вводити і ви отримаєте бажаний результат.


Підсумок / підрахунок / усереднення лише відфільтрованих або видимих ​​комірок за допомогою Kutools для Excel

Якщо у вас встановлений Kutools для Excel, ви можете швидко розрахувати суму / кількість / середнє значення лише видимих ​​або відфільтрованих клітинок у Excel.

Kutools для Excel - Включає більше 300 зручних інструментів для Excel. Повна функціональна безкоштовна пробна версія 30 днів, кредитна картка не потрібна! Get It Now

Наприклад, ви хочете підсумувати лише видимі клітинки, виберіть клітинку, в яку ви помістите результат підсумовування, введіть формулу = ПІДВИДИМО (C3: C12) (C3: C13 - діапазон, де ви підсумуєте лише видимі клітинки), і натисніть на вводити ключ

А потім підсумовується результат підсумовування з ігноруванням усіх прихованих комірок. Дивіться знімок екрана:

Для підрахунку лише видимих ​​комірок застосуйте цю формулу = ВИДИМО (C3: C12); Для усереднення лише видимих ​​комірок, будь ласка, застосуйте цю формулу = СЕРЕДОВИЙ (C3: C12).

Примітка: Якщо ви не можете точно запам'ятати формули, ви можете виконати наступні кроки, щоб легко підсумувати / підрахувати / обчислити лише видимі клітинки:

1. Виділіть клітинку, куди ви помістите результат підсумовування, та натисніть Кутулс > Функції > Статистика та математика > ПІДКРИВЕ (Або СЕРЕДНІЙ, ВИДИМО як вам потрібно). Дивіться знімок екрана:

2. У діалоговому вікні Аргументи функцій, що відкривається, вкажіть діапазон, в якому буде підсумовано ігнорування прихованих комірок, і натисніть OK кнопку. Дивіться знімок екрана:

Kutools для Excel - Включає більше 300 зручних інструментів для Excel. Повна функціональна безкоштовна пробна версія 30 днів, кредитна картка не потрібна! Get It Now

А потім підсумовується результат підсумовування з ігноруванням усіх прихованих комірок.


Демо: Сума / Кількість / Середнє лише відфільтровані або видимі клітинки


Kutools для Excel включає більше 300 зручних інструментів для Excel, які можна спробувати без обмежень протягом 30 днів. Завантажте та безкоштовно пробну версію зараз!

Легко підсумовуйте / підраховуйте лише відфільтровані / видимі клітинки, видаливши приховані рядки в Excel

Підсумовуючи / підраховуючи відфільтровані комірки в Excel, функція SUM або функція підрахунку не ігноруватиме приховані комірки. Якщо приховані / відфільтровані рядки видаляються, ми можемо лише легко підсумовувати або підраховувати видимі клітинки. Ви можете спробувати Kutools для Excel Видалити приховані (видимі) рядки та стовпці утиліта для її вирішення. Повнофункціональна безкоштовна пробна версія 30 днів!
оголошення видалити приховані рядки стовпці 3

Статті по темі:


Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%

  • Повторне використання: Швидко вставте складні формули, діаграми і все, що ви використовували раніше; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
  • Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
  • Об’єднати клітинки / рядки / стовпці без втрати даних; Вміст розділених комірок; Об'єднати повторювані рядки / стовпці... Запобігання дублюючим клітинам; Порівняйте діапазони...
  • Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
  • Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
  • Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
  • Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
  • Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
  • Понад 300 потужних функцій. Підтримує Office / Excel 2007-2019 та 365. Підтримує всі мови. Простота розгортання на вашому підприємстві чи в організації. Повна функція 30-денної безкоштовної пробної версії. 60-денна гарантія повернення грошей.
вкладка kte 201905

Вкладка Office забезпечує інтерфейс з вкладками для Office і значно спрощує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці
Коментарі (24)
Поки немає оцінок. Оцініть першим!
Цей коментар був мінімізований модератором на сайті
Привіт усім, я хотів би додати два значення в різні стовпці, але лише з видимими клітинками, коли я використовую фільтр. наприклад B3=B2+A3 Я намагався використати функцію SUBTOTAL, але це не працює. Порадьте будь ласка, наперед дякую! з повагою,
Цей коментар був мінімізований модератором на сайті
Створіть рядок у верхній частині рядка, до якого ви виберете застосувати фільтр. У стовпець, для якого потрібно підсумок, напишіть формулу =проміжний підсумок(9,{початковий номер стовпця},{кінцевий номер стовпця}). Наприклад, якщо для стовпця А мені потрібні проміжні підсумки на основі значення в стовпці b, то якщо рядок заголовка оскільки дані знаходяться в рядку 2, а рядок 1 є порожнім, то введіть формулу в стовпець A1 як =проміжний підсумок(9,A2:a999) Коли ви змінюєте умову фільтра в стовпці B, ви побачите загальну зміну.
Цей коментар був мінімізований модератором на сайті
Як додати критерії до 2-го варіанту. Наприклад, якщо у мене є діапазон значень, я хочу, щоб він вибирав значення, які мають критерії «X» у стовпці.
Цей коментар був мінімізований модератором на сайті
Коли ви показуєте рядки, "=SumVisible()" відображає помилку. Чи можна змінити «Функція», щоб відображати «Нуль» або «Без прихованих рядків»?
Цей коментар був мінімізований модератором на сайті
Дякую, Мілінда. Я вставив модуль у «Книгу 1» і «Personal.xlsb», але коли я відкриваю новий файл, я не можу отримати доступ до функції. Будь ласка, що я зробив не так?
Цей коментар був мінімізований модератором на сайті
привіт, коли я подаю в суд на цю функцію та фільтр, підсумки не відображаються. чому
Цей коментар був мінімізований модератором на сайті
У мене була схожа проблема, коли я хотів обчислити різницю з двох клітинок у різних рядках, де я використовую фільтр даних, щоб розрізняти, які рядки відображаються. Я вирішив проблему таким методом: 1. Створіть новий стовпець [або рядок залежно від того, що приховано] і позначте його як видимий. 2. Створіть новий стовпець, який використовуватиметься для збереження значення з останнього «видимого» рядка, який ви хочете зменшити. 3. У стовпці «Видимий» скористайтеся формулою: =+SUBTOTAL(102,cell_in_row_or_column) Це матиме 1, як тільки ви введете її, але буде 0, якщо клітинка невидима. 4. У стовпці, створеному для кроку 2, потрібна проста формула: +IF(A,B,C); де A=комірка, яка повертає статус видимого, B значення, з яким потрібно працювати з того самого рядка [просто копія], C значення з тієї ж клітинки в попередньому рядку, оскільки клітинка не видима. Таким чином, останній «видимий» елемент поширюється за лаштунками до рядка над видимим рядком(ами). Це дозволяє створювати прості формули, виконуючи операції з попереднього рядка. Це працює для багатьох ситуацій, коли вам потрібно мати справу з окремими точками даних, на відміну від діапазонів, які кращі з проміжним підсумком.
Цей коментар був мінімізований модератором на сайті
Дуже дякую!! Це було дуже корисно :)
Цей коментар був мінімізований модератором на сайті
Рішення спрацювало для мене. :D
Цей коментар був мінімізований модератором на сайті
Я можу змусити ваш VBA для =SUMVISIBLE вище працювати добре. Однак, якщо мої дані виконуються вертикально, і я приховую рядки. Чи є спосіб запрограмувати його, щоб ваші дані працювали горизонтально, і вони все ще працювали, коли ви приховуєте стовпці? СПАСИБІ!
Цей коментар був мінімізований модератором на сайті
Вибачте, що я переплутав код безпеки публікацій внизу, і здається, що моє запитання, можливо, було видалено, оскільки воно просили мене спробувати інше. Я можу отримати код VBA для SUMVISIBLE, щоб він працював добре, якщо мої дані розташовані вертикально і я приховую рядки. Однак це не так, якщо мої дані розташовані горизонтально, і я хочу приховати стовпці. Чи є спосіб запрограмувати це? Спасибі!
Цей коментар був мінімізований модератором на сайті
Це круто! Дуже дякую.
Цей коментар був мінімізований модератором на сайті
Я намагаюся використовувати функцію AVERAGEVISIBLE для усереднення 12 найбільших значень у стовпці, це працює у звичайній функції AVERAGE як =AVERAGE(LARGE(E971:E1540,{1,2,3,4,5,6,7,8,9,10,11,12) ,XNUMX})) однак, коли я намагаюся використати середню видиму функцію, вона повертає #VALUE!, є ідеї?
Цей коментар був мінімізований модератором на сайті
як я можу пропустити приховані клітинки з формул Excel у відфільтрованому аркуші
Цей коментар був мінімізований модератором на сайті
як пропустити приховані клітинки з формул Excel у відфільтрованому аркуші....

клітини жовтого кольору включають приховані клітини через фільтр....
Цей коментар був мінімізований модератором на сайті
привіт,
Які розрахунки потрібно зробити? Kutools для Excel підтримує три функції підрахунку/сумування/середнього значення, ігноруючи всі приховані клітинки.
Цей коментар був мінімізований модератором на сайті
Коли я застосовую, скажімо, формулу G3-K2 у відфільтрованому аркуші Excel і перетягую формулу, вона включає приховані клітинки

наприклад



Термін дії Кількість підйому
27054.59 123.41
26931.18 330.98
26600.20 493.66
26106.54 476.38
25630.16 480.64
25149.52 577.06


тепер значення в дійсності настає після того, як отримати віднімається підняту кількість (наприклад, 2-е значення 26931.18=27054.59-123.41)

3-е значення в дійсності=2-ге значення в дійсності-2-ге значення в піднятій кількості. і так далі.....

тепер цей аркуш відфільтровано, і коли я перетягую формули в стовпець дійсності, він включає приховані клітинки через фільтр.

що дає мені неправильний результат
Цей коментар був мінімізований модератором на сайті
привіт,
Звичайний =G3-K2 не буде ігнорувати приховані клітинки/рядки/стовпці шляхом перетягування для копіювання. На жаль, я не можу знайти правильну формулу для вашої. Усі формули чи методи, про які йдеться в цій статті, стосуються лише суми/підрахунку/середнього ігнорування прихованих клітинок.
Цей коментар був мінімізований модератором на сайті
Термін дії Кількість підйому
27054.59 123.41
26931.18 330.98
26600.20 (26600.2=26931.18-330.98) 493.66
26106.54 476.38
25630.16 480.64
25149.52 577.06
Цей коментар був мінімізований модератором на сайті
Коли я застосовую, скажімо, формулу G3-K2 у відфільтрованому аркуші Excel і перетягую формулу, вона включає приховані клітинки

наприклад



Термін дії Кількість підйому
27054.59 123.41
26931.18 330.98
26600.20 493.66
26106.54 476.38
25630.16 480.64
25149.52 577.06


тепер значення в дійсності настає після того, як отримати віднімається підняту кількість (наприклад, 2-е значення 26931.18=27054.59-123.41)

3-е значення в дійсності=2-ге значення в дійсності-2-ге значення в піднятій кількості. і так далі.....

тепер цей аркуш відфільтровано, і коли я перетягую формули в стовпець дійсності, він включає приховані клітинки через фільтр.

що дає мені неправильний результат
Цей коментар був мінімізований модератором на сайті
або ось так



колонка колонка d

100 10

90 10

80 10



де 90=100-10, 80=90-10 і так далі.........

перетягування формули включає приховані клітинки у формулу
Цей коментар був мінімізований модератором на сайті
Привіт

Quiero sumar dentro de un rango de fila que al ocultar columnas sume sólo las visibles.
No he logrado hacerlo, alguna fórmula? o tendría que hacerse con una marcro?

Велике спасибі!
Цей коментар був мінімізований модератором на сайті
Привіт

No me resulta sumar el rango de una fila considerando sólo las columnas visibles.

Формула алгуни чи макрос алгуни?

Спасибо!
Цей коментар був мінімізований модератором на сайті
Привіт, Крістобаль,
Вибачте, я не можу чітко зрозуміти вашу проблему. Чи могли б ви пояснити свою проблему англійською?
Або ви можете вставити знімок екрана або файл, щоб описати свою проблему.
Дякую!
There are no comments posted here yet
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0   Персонажі
Рекомендовані місця