Как да преброим клетки в таблица на Excel, които отговарят на множество критерии - трикове и техники
трикове • трикове • решения
Как да преброите клетки в таблица на Excel, които отговарят на множество критерии
Тази статия предоставя примери за формули, които използват функциятаCOUNTIF. Те са полезни за преброяване на клетки, които отговарят на същите критерии. Примерите за формули са полезни, когато трябва да преброите клетки само ако отговарят на два или повече критерия. Тези критерии могат да се основават на клетките, които се преброяват, или на набор от съвпадащи клетки.
Някои от тези формули използват функциятаCOUNTIFS, която беше въведена в Excel 2007. За целите на съвместимостта предоставих и алтернативна формула, която работи с предишни версии на Excel.
Използване на критерия И
КритерииИ преброяват клетките, ако са изпълнени всички посочени условия. Типичен пример е формула, която отчита броя на стойностите в числов диапазон. Например искате да преброите колко клетки съдържат стойност, по-голяма от 0 и по-малка или равна на 12. Всяка клетка, която има положителна стойност, по-малка или равна на 12, ще бъде включена в броя.
Следната формула, която работи само в Excel 2007 и по-нови версии, брои клетки, които имат стойност между 0 и 12 в диапазон с име Данни: =COUNTIFS(Данни;">=0";Данни;" .
Аргументите на функциятаCOUNTIFS винаги идват по двойки. Тази формула има два набора сдвоени аргументи: първият аргумент във всяка двойка е диапазонът, който ви интересува, а вторият аргумент е критерият. Стойностите се вземат предвид при преброяването, ако отговарят на всички критерии, посочени за всяка двойка аргументи.
За съвместимост с предишни версии на Excel използвайте тази формула:=COUNTIF(Данни,">0")-COUNTI(Данни,">12") .
Тази формула използва говорим английски и брои броя на стойностите, по-големи от 0, и след това изважда броя на стойностите, по-големи от 12. Резултатът е броят клетки със стойности, по-големи от 0 и по-малки или равни на 12. Създаването на този вид формула може да бъде объркващо, тъй като формулата се отнася до условието ">12", дори ако целта е да се преброят стойности, по-малки или равни на 12. Алтернативен метод е да се използва масив формула като следната (създаването на формули като този тип може да ви се стори по-лесно): =SUM((Data>0)*(Data .
Когато въвеждате формула за масив, натиснетеCtrl+Shift+Enter вместо само Enter.
Ориз. 116.1. Таблицата показва различни методи за преброяване, използвайки няколко критерия.
Следната формула (само за Excel 2007 и по-нова версия) използва три набора от сдвоени аргументи, за да върне количество: =COUNTIFS(Month:"January",Agent:"Seleznev";0volume;">1000") . За съвместимост с предишни версии на Excel използвайте следната формула, за да преброите броя елементи, които отговарят и на трите критерия: =SUMPRODUCT((Month="January")*(Agent="Seleznev")*(Volume>1000)) .
Ориз. 116.2. Филтрирането се прилага за преброяване на редове, които отговарят на множество критерии
Използване на критерия ИЛИ
За да преброите клетки с помощта на критериитеИЛИ, понякога можете да използвате няколко функцииCOUNTIF. Следната формула, например, брои броя на случаите на 1,3 и 5 в диапазона с име Данни: =COUNTIF(Данни;1)+COUNTIF(Данни;3)+COUNTIF(Данни;5) .
Можете също да използвате функциятаCOUNTIF във формула за масив. И така, следната формуламасив връща същия резултат като предишната формула: =SUM(COUNTIF(Data;)) .
Въведете формула за масив и натиснетеCtrl+Shift+Enter.
Ако базирате вашите критерииИЛИ на клетки, различни от тези, които се преброяват, тогава функциятаCOUNTIF не работи (вижте Фигура 116.1). Да предположим, че искате да преброите броя на продажбите, които отговарят на един от следните критерии:
Следната формула за масив връща правилната сума: =SUM(IF((Month="January")+(Agent="Seleznev")+(Volume>1000);1)) .