Перейти до основного матеріалу

Як порахувати відфільтровані дані / список із критеріями в 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 AI Aide: Революціонізуйте аналіз даних на основі: Інтелектуальне виконання   |  Згенерувати код  |  Створення спеціальних формул  |  Аналізуйте дані та створюйте діаграми  |  Викликати функції Kutools...
Популярні функції: Знайдіть, виділіть або визначте дублікати   |  Видалити порожні рядки   |  Об’єднайте стовпці або клітинки без втрати даних   |   Раунд без Формули ...
Супер пошук: VLookup за кількома критеріями    Багатозначний VLookup  |   VLookup на кількох аркушах   |   Нечіткий пошук ....
Розширений розкривний список: Швидке створення випадаючого списку   |  Залежний спадний список   |  Виберіть розкривний список, що вибирається ....
Менеджер колонок: Додайте конкретну кількість стовпців  |  Перемістити стовпці  |  Перемкнути статус видимості прихованих стовпців  |  Порівняйте діапазони та стовпці ...
Особливості: Фокус сітки   |  Перегляд дизайну   |   Велика панель формул    Диспетчер робочих книг і аркушів   |  Бібліотека ресурсів (автотекст)   |  Вибір дати   |  Об’єднайте робочі аркуші   |  Шифрування/розшифрування клітинок    Надсилайте листи за списком   |  Супер фільтр   |   Спеціальний фільтр (фільтр жирний/курсив/закреслений...) ...
Топ-15 наборів інструментів12 текст Tools (додати текст, Видалити символи, ...)   |   50 + Графік типи (діаграма Ганта, ...)   |   40+ Практичний Формули (Розрахуйте вік на основі дня народження, ...)   |   19 вставка Tools (Вставте QR-код, Вставити зображення зі шляху, ...)   |   12 Перетворення Tools (Числа до слів, Валютна конверсія, ...)   |   7 Злиття та розділення Tools (Розширені комбіновані ряди, Розділені клітини, ...)   |   ... і більше

Покращуйте свої навички Excel за допомогою Kutools для Excel і відчуйте ефективність, як ніколи раніше. Kutools для Excel пропонує понад 300 додаткових функцій для підвищення продуктивності та економії часу.  Натисніть тут, щоб отримати функцію, яка вам найбільше потрібна...

Опис


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

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
Comments (17)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
When i change the filter, the count will change automatically ?
This comment was minimized by the moderator on the site
Is there a way to modify the formula for filtered data if I'm wanting to gather the information but for both pears and oranges?
This comment was minimized by the moderator on the site
Hi there,

With the first method, you can enter the following formula in the helper column: =IF(B2="Pear",1,IF(B2="Orange",1,"")
And then use the following formula to get the total count: =COUNTIFS(G2:G18,1)

Amanda
This comment was minimized by the moderator on the site
Sweet, this works!
This comment was minimized by the moderator on the site
hey i want to count value greater than 1 but with filtered visible data, can you help?
This comment was minimized by the moderator on the site
Hi,
I need help calculating the percentage of PP students (column F) with SEN (column E) who have s or b (column G)

Here's the formula I've been trying to use but it's not working.

Any help/advice appreciated.

=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
This comment was minimized by the moderator on the site
How about if “pear” needs to be a number value “<0” what do you use instead of (search?
This comment was minimized by the moderator on the site
Hi Sib,
You can apply the COUNTIFS functions to count items with two or more criteria. In the case of this webpage, you can use the formulas =COUNTIFS(B2:B21,"Pear",C2:C21,"<0") to count the pears whose amount is less than 0.
However, the count result is solid and won’t change when you change the filter.
This comment was minimized by the moderator on the site
Thanks a lot. It's really excellent! Thanks once a lot.
This comment was minimized by the moderator on the site
Excellent!!! Now able to filter and countif based on creiteria.
This comment was minimized by the moderator on the site
How would I add another criteria to the filtered data formula?
This comment was minimized by the moderator on the site
Hi Kane,
Which kind of filter criteria do you want to add? More detailed information can help we understand and solve your problem quicker.
This comment was minimized by the moderator on the site
Same with my question. How to count filtered if there are two criteria "Pear" for fruit and "Julie" for salesman?

Thanks.
This comment was minimized by the moderator on the site
Hi,
In this situation, I guess a helper column I introduced in the first method may be easier to count.
This comment was minimized by the moderator on the site
AWESOME, I used the formula, and it was exactly what I need. thanks!!
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations