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

Як порахувати відфільтровані дані / список із критеріями в Excel?

Ви можете помітити, що незалежно від того, відфільтрували ви таблицю чи ні, функція COUNTIF ігноруватиме фільтрацію і повертатиме фіксоване значення. У деяких випадках вам потрібно підрахувати відфільтровані дані за певними критеріями. Тож як це зробити? У цій статті я познайомлюсь із кількома способами швидкого підрахунку даних / списку, відфільтрованих у Excel.


Дані, відфільтровані з урахуванням критеріїв, додають стовпець довідки в Excel

У цій статті я взяв наступну таблицю як приклад. Тут я відфільтрував Джулі та Ніколь у колонці "Продавець".

Вихідні дані:

Відфільтровані дані:

Цей метод допоможе вам додати додатковий стовпець довідки, а потім ви зможете застосувати функцію COUNTIFS для підрахунку відфільтрованих даних у Excel. (примітки: Цей метод вимагає, щоб ви відфільтрували оригінальну таблицю перед виконанням наступних кроків.)

1. Знайдіть порожню клітинку крім оригінальної відфільтрованої таблиці, говорить Cell G2, введіть = IF (B2 = "Груша", 1, ""), а потім перетягніть маркер заповнення до потрібного діапазону. (Примітка: У формулі = IF (B2 = "Груша", 1, ""), B2 - це клітина, яку ви будете рахувати, а "Груша" - критерії, за якими ви будете рахувати.)

Тепер поряд із оригінальною відфільтрованою таблицею додано стовпець довідки. "1" вказує, що це груша в стовпці B, тоді як пусті натякає, що це не груша в стовпці B.

2. Знайдіть порожню комірку і введіть формулу =COUNTIFS(B2:B18,"Pear",G2:G18,"1"), і натисніть кнопку вводити ключ. (Примітка: У формулі =COUNTIFS(B2:B18,"Pear",G2:G18,"1"), B2: B18 та G2: G18 - це діапазони, які ви будете рахувати, а "Груша" та "1" - критерії, за якими ви будете рахувати.)

Тепер ви одразу отримаєте число підрахунку. Зверніть увагу, що кількість підрахунків не зміниться, якщо ви вимкнете фільтрування або зміните фільтрування.

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

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

Дані, відфільтровані з урахуванням критеріїв, виконуються функціями Excel

Якщо ви хочете, щоб кількість лічильників змінювалася у міру зміни фільтра, ви можете застосувати функції SUMPRODUCT в Excel наступним чином:
У порожню комірку введіть формулу =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B18,ROW(B2:B18)-MIN(ROW(B2:B18)),,1)),ISNUMBER(SEARCH("Pear",B2:B18))+0), і натисніть кнопку вводити ключ

стрічка записки Формула занадто складна, щоб запам'ятати? Збережіть формулу як автоматичний запис тексту для повторного використання лише одним клацанням у майбутньому!
Детальніше ...     Безкоштовне випробування

Примітки:
(1) У наведеній вище формулі B2: B18 - діапазон, який ви будете рахувати, а "Груша" - критерії, за якими ви будете рахувати.
(2) Повертається значення зміниться, коли ви вимкнете фільтрування або зміни фільтрації.

Легко розділити діапазон на кілька аркушів на основі критеріїв у стовпці в Excel

Порівняно зі складними формулами масиву, можливо, набагато простіше зберегти всі відфільтровані записи на новому аркуші, а потім застосувати функцію Count для підрахунку відфільтрованого діапазону даних або списку.

Kutools для Excel Розділити дані Утиліта може допомогти користувачам Excel легко розділити діапазон на кілька аркушів на основі критеріїв в одному стовпці вихідного діапазону. Повнофункціональна безкоштовна пробна версія 30 днів!
дані про розподіл оголошень 0


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


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

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% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці
Коментарі (14)
Поки немає оцінок. Оцініть першим!
Цей коментар був мінімізований модератором на сайті
вау, потужний і простий у використанні. СПАСИБІ!
Цей коментар був мінімізований модератором на сайті
ДЕ МОЖНА ЗАВАНТАЖИТИ ПРОБНУ ВЕРСІЮ KUTOOLS ДЛЯ MAC? З повагою
Цей коментар був мінімізований модератором на сайті
ЧУДО, я використав формулу, і це було саме те, що мені потрібно. Спасибі!!
Цей коментар був мінімізований модератором на сайті
Як я можу додати інший критерій до формули відфільтрованих даних?
Цей коментар був мінімізований модератором на сайті
Привіт Кейн!
Який тип критеріїв фільтрації ви хочете додати? Більш детальна інформація допоможе нам зрозуміти та швидше вирішити вашу проблему.
Цей коментар був мінімізований модератором на сайті
Те саме з моїм запитанням. Як порахувати відфільтрований, якщо є два критерії «Груша» для фруктів і «Джулі» для продавця?

Завдяки.
Цей коментар був мінімізований модератором на сайті
привіт,
У цій ситуації, я думаю, допоміжний стовпець, який я ввів у першому методі, може бути легше порахувати.
Цей коментар був мінімізований модератором на сайті
Відмінно!!! Тепер можна фільтрувати й підраховувати на основі критеріїв.
Цей коментар був мінімізований модератором на сайті
Дуже дякую. Це дійсно чудово! Дуже дякую раз.
Цей коментар був мінімізований модератором на сайті
Як щодо того, якщо «груша» має бути числовим значенням «<0», що ви використовуєте замість (пошук?
Цей коментар був мінімізований модератором на сайті
Привіт, сиб,
Ви можете застосувати функції COUNTIFS для підрахунку елементів з двома або більше критеріями. У випадку цієї веб-сторінки ви можете використовувати формули =COUNTIFS(B2:B21,"Pear",C2:C21,"<0"), щоб підрахувати груші, кількість яких менше 0.
Однак результат підрахунку є твердим і не зміниться під час зміни фільтра.
Цей коментар був мінімізований модератором на сайті
привіт,
Мені потрібна допомога з розрахунком відсотка студентів PP (стовпець F) з SEN (стовпець E), які мають s або b (стовпець G)

Ось формула, яку я намагався використати, але вона не працює.

Буду вдячний за будь-яку допомогу/пораду.

=SUMPRODUCT(ISNUMBER(MATCH($E$2:$E$30,{"<>"},0))*ISNUMBER(MATCH($F$2:$F$30,{"<>"},0))*ISNUMBER(MATCH($T$2:$T$30,{"s","b"},0)))/SUMPRODUCT(ISNUMBER(MATCH($E$2:$E$30,{"<>"},0))*ISNUMBER(MATCH($F$2:$F$30,{"<>"},0)))

Claire
Цей коментар був мінімізований модератором на сайті
Привіт, я хочу підрахувати значення більше 1, але з відфільтрованими видимими даними, ви можете допомогти?
Цей коментар був мінімізований модератором на сайті
Мило, це працює!
There are no comments posted here yet
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0   Персонажі
Рекомендовані місця