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

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

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


Countif відфільтрував дані за критеріями, додавши допоміжний стовпець у Excel

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

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

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

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

1. Знайдіть порожню клітинку, крім вихідної відфільтрованої таблиці, скажімо, клітинку 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 ПІДКРИВЕ/ВИДИМО/СЕРЕДОВИЙ функції легко обчислюють вказаний діапазон, ігноруючи будь-які приховані комірки, рядки або стовпці.


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

Дані, відфільтровані з урахуванням критеріїв, виконуються функціями 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 легко розділити діапазон на кілька аркушів на основі критеріїв в одному стовпці вихідного діапазону.


дані про розподіл оголошень 0


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


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

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

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

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

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці
Коментарі (16)
Поки немає оцінок. Оцініть першим!
Цей коментар був мінімізований модератором на сайті
вау, потужний і простий у використанні. СПАСИБІ!
Цей коментар був мінімізований модератором на сайті
ДЕ МОЖНА ЗАВАНТАЖИТИ ПРОБНУ ВЕРСІЮ 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, але з відфільтрованими видимими даними, ви можете допомогти?
Цей коментар був мінімізований модератором на сайті
Мило, це працює!
Цей коментар був мінімізований модератором на сайті
Чи є спосіб змінити формулу для відфільтрованих даних, якщо я хочу зібрати інформацію, але не для груш і апельсинів?
Цей коментар був мінімізований модератором на сайті
Привіт там,

За допомогою першого методу ви можете ввести таку формулу в допоміжний стовпець: =IF(B2="Груша",1;IF(B2="Апельсин",1,"")
А потім скористайтеся такою формулою, щоб отримати загальну кількість: =COUNTIFS(G2:G18,1;XNUMX)

Аманда
There are no comments posted here yet

Слідуй за нами

Copyright © 2009 - WWW.extendoffice.com. | Всі права захищені. На основі ExtendOffice. | Карта сайту
Microsoft та логотип Office є товарними знаками або зареєстрованими товарними знаками Microsoft Corporation у США та / або інших країнах.
Захищений Sectigo SSL