Екцел има много функција у којима корисник мора да наведе један или више критеријума да би добио резултат. На пример, ако желите да бројите ћелије на основу више критеријума, можете да користите функције ЦОУНТИФ или ЦОУНТИФС у Екцелу.
Овај водич покрива различите начине коришћења једног или више критеријума у функцијама ЦОУНТИФ и ЦОУНТИФС у Екцелу.
Иако ћу се у овом водичу првенствено фокусирати на функције ЦОУНТИФ и ЦОУНТИФС, сви ови примери се могу користити и у другим Екцел функцијама које узимају више критеријума као улазе (као што су СУМИФ, СУМИФС, АВЕРАГЕИФ и АВЕРАГЕИФС).
Увод у Екцел ЦОУНТИФ и ЦОУНТИФС функције
Хајде да се прво позабавимо коришћењем функција ЦОУНТИФ и ЦОУНТИФС у Екцелу.
Екцел ЦОУНТИФ функција (узима појединачне критеријуме)
Екцел ЦОУНТИФ функција најбоље одговара ситуацијама када желите пребројати ћелије на основу једног критеријума. Ако желите да бројите на основу више критеријума, користите функцију ЦОУНТИФС.
Синтакса
= ЦОУНТИФ (опсег, критеријуми)
Улазни аргументи
- домет - опсег ћелија које желите да пребројите.
- критеријуми - критеријуми који се морају оценити у односу на распон ћелија да би се ћелија пребројила.
Екцел ЦОУНТИФС функција (узима више критеријума)
Екцел ЦОУНТИФС функција најбоље одговара ситуацијама када желите да бројите ћелије на основу више критеријума.
Синтакса
= ЦОУНТИФС (опсег_критеријума1, критеријум1, [опсег_критеријума2, критеријум2] …)
Улазни аргументи
- опсег_критеријума1 - Распон ћелија за које желите да процените према критеријумима1.
- критеријуми1 - критеријуме које желите да процените за опсег_критеријума1 да бисте утврдили које ћелије желите да бројите.
- [опсег_критеријума2] - Распон ћелија за које желите да процените према критеријумима2.
- [критеријуми2] - критеријуме које желите да процените за ранге_ранге2 да бисте утврдили које ћелије треба да се преброје.
Хајде сада да погледамо неке примере коришћења више критеријума у ЦОУНТИФ функцијама у Екцелу.
Коришћење БРОЈНИХ критеријума у Екцел ЦОУНТИФ функцијама
#1 Броји ћелије када је критеријум ЈЕДНАКА вредност
Да бисте добили број ћелија где је аргумент критеријума једнак наведеној вредности, можете или директно да унесете критеријум или да користите референцу ћелије која садржи критеријуме.
Испод је пример где рачунамо ћелије које садрже број 9 (што значи да је аргумент критеријума једнак 9). Ево формуле:
= ЦОУНТИФ ($ Б $ 2: $ Б $ 11, Д3)
У горњем примеру (на слици), критеријум је у ћелији Д3. Такође можете унети критеријуме директно у формулу. На пример, можете користити и:
= ЦОУНТИФ ($ Б $ 2: $ Б $ 11,9)
#2 Броји ћелије када је критеријум ВЕЋИ ОД вредности
Да бисмо добили број ћелија чија је вредност већа од наведене вредности, користимо оператор већи од („>“). Могли бисмо га користити директно у формули или користити референцу ћелије која има критеријуме.
Кад год користимо оператор у критеријумима у Екцелу, морамо га ставити у двоструке наводнике. На пример, ако је критеријум већи од 10, онда морамо да унесемо „> 10“ као критеријум (погледајте слику испод):
Ево формуле:
= ЦОУНТИФ ($ Б $ 2: $ Б $ 11, ”> 10 ″)
Такође можете имати критеријуме у ћелији и као критеријум користити референцу ћелије. У овом случају не морате да стављате критеријуме у двоструке наводнике:
= ЦОУНТИФ ($ Б $ 2: $ Б $ 11, Д3)
Такође се може догодити случај да желите да критеријуми буду у ћелији, али не желите да то буде са оператором. На пример, можда желите да ћелија Д3 има број 10, а не> 10.
У том случају морате креирати аргумент критеријума који је комбинација референце оператора и ћелије (погледајте слику испод):
= ЦОУНТИФ ($ Б $ 2: $ Б $ 11, ”>” & Д3)
НАПОМЕНА: Када комбинујете оператор и референцу ћелије, оператор је увек у двоструким наводницима. Оператер и референца ћелије су спојени знаком & ().
#3 Броји ћелије ако је критеријум МАЊИ ОД вредности
Да бисмо добили број ћелија чија је вредност мања од наведене вредности, користимо оператор лесс тхан („<“). Могли бисмо га користити директно у формули или користити референцу ћелије која има критеријуме.
Кад год користимо оператор у критеријумима у Екцелу, морамо га ставити у двоструке наводнике. На пример, ако је критеријум да број треба да буде мањи од 5, онда морамо да унесемо „<5“ као критеријум (види слику испод):
= ЦОУНТИФ ($ Б $ 2: $ Б $ 11, ”<5 ″)
Такође можете имати критеријуме у ћелији и као критеријум користити референцу ћелије. У овом случају не морате да стављате критеријуме у двоструке наводнике (погледајте слику испод):
= ЦОУНТИФ ($ Б $ 2: $ Б $ 11, Д3)
Такође, могао би се догодити случај да желите да критеријуми буду у ћелији, али не желите да то буде са оператором. На пример, можда желите да ћелија Д3 има број 5, а не <5.
У том случају морате креирати аргумент критеријума који је комбинација референце оператора и ћелије:
= ЦОУНТИФ ($ Б $ 2: $ Б $ 11, ”<“ & Д3)
НАПОМЕНА: Када комбинујете оператор и референцу ћелије, оператор је увек у двоструким наводницима. Оператер и референца ћелије су спојени знаком & ().
#4 Бројање ћелија са више критеријума - између две вредности
Да бисмо добили број вредности између две вредности, морамо да користимо више критеријума у функцији ЦОУНТИФ.
Ево два начина да то учините:
МЕТОДА 1: Коришћење функције ЦОУНТИФС
Функција ЦОУНТИФС може да обрађује више критеријума као аргументе и броји ћелије само ако су сви критеријуми тачни. За бројање ћелија са вредностима између две наведене вредности (рецимо 5 и 10), можемо користити следећу функцију ЦОУНТИФС:
= ЦОУНТИФС ($ Б $ 2: $ Б $ 11, ”> 5 ″, $ Б $ 2: $ Б $ 11,” <10 ″)
НАПОМЕНА: Горња формула не броји ћелије које садрже 5 или 10. Ако желите да укључите ове ћелије, користите операције веће од (> =) и мање од (<=). Ево формуле:
= ЦОУНТИФС ($ Б $ 2: $ Б $ 11, ”> = 5 ″, $ Б $ 2: $ Б $ 11,” <= 10 ″)
Можете такође имати ове критеријуме у ћелијама и користити референцу ћелије као критеријум. У овом случају не морате да стављате критеријуме у двоструке наводнике (погледајте слику испод):
Такође можете користити комбинацију референци ћелија и оператора (где је оператор унесен директно у формулу). Када комбинујете оператор и референцу ћелије, оператор је увек у двоструким наводницима. Оператер и референца ћелије спојени су знаком & ().
МЕТОДА 2: Коришћење две функције ЦОУНТИФ
Ако имате више критеријума, можете користити ЦОУНТИФС или креирати комбинацију ЦОУНТИФ функција. Формула испод такође би урадила исту ствар:
= ЦОУНТИФ ($ Б $ 2: $ Б $ 11, ”> 5 ″)-ЦОУНТИФ ($ Б $ 2: $ Б $ 11,”> 10 ″)
У горњој формули прво проналазимо број ћелија које имају вредност већу од 5 и одузимамо број ћелија са вредношћу већом од 10. То би нам дало резултат као 5 (што је број ћелија које имају вредности веће од 5 и мање од 10).
Ако желите да формула садржи и 5 и 10, уместо тога користите следећу формулу:
= ЦОУНТИФ ($ Б $ 2: $ Б $ 11, ”> = 5 ″)-ЦОУНТИФ ($ Б $ 2: $ Б $ 11,”> 10 ″)
Ако желите да формула искључи „5“ и „10“ из бројања, користите следећу формулу:
= ЦОУНТИФ ($ Б $ 2: $ Б $ 11, ”> = 5 ″)-ЦОУНТИФ ($ Б $ 2: $ Б $ 11,”> 10 ″)-ЦОУНТИФ ($ Б $ 2: $ Б $ 11,10)
Ове критеријуме можете имати у ћелијама и користити референце ћелија или можете користити комбинацију оператора и референци ћелија.
Коришћење критеријума ТЕКСТА у Екцел функцијама
#1 Броји ћелије када је критеријум ЈЕДНАК наведен текст
За пребројавање ћелија које садрже потпуно подударање наведеног текста, можемо једноставно користити тај текст као критеријум. На пример, у скупу података (приказано испод на слици), ако желим да пребројим све ћелије са именом Јое, могу да користим формулу испод:
= ЦОУНТИФ ($ Б $ 2: $ Б $ 11, "Јое")
Пошто је ово текстуални низ, морам ставити текстуалне критеријуме у двоструке наводнике.
Такође можете имати критеријуме у ћелији, а затим користити ту референцу ћелије (као што је приказано испод):
= ЦОУНТИФ ($ Б $ 2: $ Б $ 11, Е3)
НАПОМЕНА: Можете добити погрешне резултате ако постоје водећи/крајњи размаци у опсегу критеријума или критеријума. Обавезно очистите податке пре коришћења ових формула.
#2 Броји ћелије када критеријуми НИСУ ЈЕДНАКИ наведеном тексту
Слично ономе што смо видели у горњем примеру, можете пребројати и ћелије које не садрже наведени текст. Да бисмо то урадили, морамо да користимо оператор није једнак оператору ().
Претпоставимо да желите да пребројите све ћелије које не садрже назив ЈОЕ, ево формуле која ће то учинити:
= ЦОУНТИФ ($ Б $ 2: $ Б $ 11, "Јое")
Такође можете имати критеријуме у ћелији и као критеријум користити референцу ћелије. У овом случају не морате да стављате критеријуме у двоструке наводнике (погледајте слику испод):
= ЦОУНТИФ ($ Б $ 2: $ Б $ 11, Е3)
Такође се може догодити случај да желите да критеријуми буду у ћелији, али не желите да то буде са оператором. На пример, можда желите да ћелија Д3 носи име Јое, а не Јое.
У том случају морате креирати аргумент критеријума који је комбинација референце оператора и ћелије (погледајте слику испод):
= ЦОУНТИФ ($ Б $ 2: $ Б $ 11, ”” и Е3)
Када комбинујете оператор и референцу ћелије, оператор је увек у двоструким наводницима. Оператер и референца ћелије су спојени знаком & ().
Коришћење критеријума ДАТЕ у Екцел функцијама ЦОУНТИФ и ЦОУНТИФС
Екцел складишти датум и време као бројеве. Дакле, можемо га користити на исти начин на који користимо бројеве.
#1 Броји ћелије када је критеријум ЈЕДНАК наведен датум
Да бисмо добили број ћелија које садрже наведени датум, користили бисмо оператор једнако = () заједно са датумом.
Да бисте користили датум, препоручујем употребу функције ДАТЕ, јер се тиме уклања свака могућност грешке у вредности датума. Тако, на пример, ако желим да користим датум 1. септембар 2015, могу да користим функцију ДАТЕ као што је приказано испод:
= ДАТЕ (2015,9,1)
Ова формула би вратила исти датум упркос регионалним разликама. На пример, 01-09-2015 би био 1. септембар 2015. према синтакси датума у САД, а 09. јануар 2015. према синтакси датума у Великој Британији. Међутим, ова формула би увек враћала 1. септембар 2105.
Ево формуле за бројање ћелија које садрже датум 02.09.2015.
= ЦОУНТИФ ($ А $ 2: $ А $ 11, ДАТЕ (2015,9,2))
#2 Бројање ћелија ако је критеријум ПРЕ или ПОСЛЕ одређеног датума
За бројање ћелија које садрже датум пре или после наведеног датума, можемо користити операторе мање од/веће од.
На пример, ако желим да пребројим све ћелије које садрже датум који је после 02. септембра 2015, могу да користим формулу:
= ЦОУНТИФ ($ А $ 2: $ А $ 11, ”>” & ДАТЕ (2015,9,2))
Слично томе, можете пребројати и ћелије пре наведеног датума. Ако желите да укључите датум у бројање, користите оператор „једнако“ заједно са оператором „веће од/мање од“.
Такође можете да користите референцу ћелије која садржи датум. У овом случају морате комбиновати оператор (унутар двоструких наводника) са датумом помоћу знака & ().
Погледајте пример испод:
= ЦОУНТИФ ($ А $ 2: $ А $ 11, ”>” & Ф3)
#3 Бројање ћелија са више критеријума - између два датума
Да бисмо добили број вредности између две вредности, морамо да користимо више критеријума у функцији ЦОУНТИФ.
То можемо учинити помоћу две методе - једне функције ЦОУНТИФС или две функције ЦОУНТИФ.
МЕТОДА 1: Коришћење функције ЦОУНТИФС
ЦОУНТИФС функција може узети више критеријума као аргументе и броји ћелије само ако су сви критеријуми ТРУЕ. За бројање ћелија са вредностима између два наведена датума (рецимо 2. септембра и 7. септембра), можемо користити следећу функцију ЦОУНТИФС:
= ЦОУНТИФС ($ А $ 2: $ А $ 11, ”>” & ДАТЕ (2015,9,2), $ А $ 2: $ А $ 11, ”<“ & ДАТЕ (2015,9,7))
Горња формула не броји ћелије које садрже наведене датуме. Ако желите да укључите и ове датуме, користите операторе веће од једнаке (> =) и мање од једнаке (<=). Ево формуле:
= ЦОУНТИФС ($ А $ 2: $ А $ 11, ”> =” & ДАТЕ (2015,9,2), $ А $ 2: $ А $ 11, ”<=” & ДАТЕ (2015,9,7))
Можете такође имати датуме у ћелији и користити референцу ћелије као критеријум. У овом случају не можете имати оператор са датумом у ћелијама. Морате ручно додати операторе у формулу (у двоструким наводницима) и додати референцу ћелије користећи амперсанд (&). Погледајте слику испод:
= ЦОУНТИФС ($ А $ 2: $ А $ 11, ”>” & Ф3, $ А $ 2: $ А $ 11, ”<“ & Г3)
МЕТОДА 2: Коришћење функција ЦОУНТИФ
Ако имате више критеријума, можете користити једну функцију ЦОУНТИФС или креирати комбинацију две функције ЦОУНТИФС. Формула испод такође би помогла:
= ЦОУНТИФ ($ А $ 2: $ А $ 11, ”>” & ДАТЕ (2015,9,2))-ЦОУНТИФ ($ А $ 2: $ А $ 11, ”>” & ДАТЕ (2015,9,7))
У горњој формули прво проналазимо број ћелија које имају датум после 2. септембра и одузимамо број ћелија са датумима после 7. септембра. То би нам дало резултат као 7 (што је број ћелија које имају датуме после 2. септембра и пре или пре 7. септембра).
Ако не желите да се формула рачуна и за 2. и за 7. септембар, уместо тога користите следећу формулу:
= ЦОУНТИФ ($ А $ 2: $ А $ 11, ”> =” & ДАТЕ (2015,9,2))-ЦОУНТИФ ($ А $ 2: $ А $ 11, ”>” & ДАТЕ (2015,9,7))
Ако желите да искључите оба датума из бројања, користите следећу формулу:
= ЦОУНТИФ ($ А $ 2: $ А $ 11, ”>” & ДАТЕ (2015,9,2))-ЦОУНТИФ ($ А $ 2: $ А $ 11, ”>” & ДАТЕ (2015,9,7) -ЦОУНТИФ ($ А 2 УСД: 11 УСД, ДАТЕ (2015,9,7)))
Такође, можете да дате датуме критеријума у ћелијама и користите референце ћелија (заједно са операторима у двоструким наводницима спојеним знаком амперсанд).
Коришћење ВИЛДЦАРД ЗНАКОВА у критеријумима у ЦОУНТИФ & ЦОУНТИФС функцијама
У Екцелу постоје три заменска знака:
- * (звездица) - Представља било који број знакова. На пример, ек* може значити екцел, екцелс, пример, стручњак итд.
- ? (Знак питања) - Представља један једини лик. На пример, Тр? Мп би могао да значи Трамп или Трамп.
- ~ (тилда) - Користи се за идентификацију замјенског знака (~, *,?) У тексту.
Можете користити функцију ЦОУНТИФ са замјенским знаковима за бројање ћелија када друга уграђена функција пребројавања не успије. На пример, претпоставимо да имате скуп података као што је приказано испод:
Узмимо сада различите примере:
#1 Бројање ћелија које садрже текст
За бројање ћелија са текстом можемо користити заменски знак * (звездица). Пошто звездица представља било који број знакова, рачунала би све ћелије које садрже било који текст. Ево формуле:
= ЦОУНТИФС ($ Ц $ 2: $ Ц $ 11, ”*”)
Напомена: Горња формула занемарује ћелије које садрже бројеве, празне ћелије и логичке вредности, али ће рачунати да ћелије садрже апостроф (и стога изгледају празне) или ћелије које садрже празан низ (= ””) које су можда враћене као део формуле.
Ево детаљног водича о руковању случајевима у којима постоји празан низ или апостроф.
Ево детаљног водича о руковању случајевима у којима постоје празни низови или апострофе.
Испод је видео који објашњава различите сценарије пребројавања ћелија са текстом.
#2 Броји ћелије које нису празне
Ако размишљате о употреби функције ЦОУНТА, размислите поново.
Пробајте и можда вам не успе. ЦОУНТА ће такође бројати ћелију која садржи празан низ (формуле се често враћају као = ”” или када људи унесу само апостроф у ћелију). Ћелије које садрже празне низове изгледају празне, али нису и стога се броје помоћу функције ЦОУНТА.
ЦОУНТА ће такође бројати ћелију која садржи празан низ (формуле се често враћају као = ”” или када људи унесу само апостроф у ћелију). Ћелије које садрже празне низове изгледају празне, али нису и стога се броје помоћу функције ЦОУНТА.
Дакле, ако користите формулу = ЦОУНТА (А1: А11), она враћа 11, док би требало да врати 10.
Ево исправке:
= ЦОУНТИФ ($ А $ 1: $ А $ 11, ”?*”)+ЦОУНТ ($ А $ 1: $ А $ 11)+СУМПРОДУЦТ (-ИСЛОГИЦАЛ ($ А $ 1: $ А $ 11))
Схватимо ову формулу разбијајући је:
- ЦОУНТИФ ($ Н $ 8: $ Н $ 18, ”?*”) - Овај део формуле враћа 5. Ово укључује било коју ћелију која има текстуални знак у себи. А? представља један знак и * представља било који број знакова. Дакле, комбинација?* У критеријумима приморава Екцел да броји ћелије које имају најмање један текстуални знак у себи.
- ЦОУНТ ($ А $ 1: $ А $ 11) - Ово броји све ћелије које садрже бројеве. У горњем примеру враћа 3.
- СУМПРОДУЦТ (-ИСЛОГИЦАЛ ($ А $ 1: $ А $ 11)) - Ово броји све ћелије које садрже логичке вредности. У горњем примеру враћа 2.
#3 Бројање ћелија које садрже одређени текст
Рецимо да желимо да пребројимо све ћелије где назив продајног представника почиње са Ј. То се лако може постићи коришћењем заменског знака у функцији ЦОУНТИФ. Ево формуле:
= ЦОУНТИФС ($ Ц $ 2: $ Ц $ 11, "Ј*")
Критеријум Ј* наводи да текст у ћелији треба да почиње са Ј и може да садржи било који број знакова.
Ако желите да пребројите ћелије које садрже абецеду било где у тексту, обрубите је звездицом са обе стране. На пример, ако желите да бројите ћелије које у себи садрже абецеду „а“, користите * а * као критеријум.
Овај чланак је необично дугачак у поређењу са мојим другим чланцима. Надам се да сте уживали. Реците ми своја размишљања остављајући коментар.
Такође би вам могли бити корисни следећи Екцел водичи:
- Избројите број речи у Екцелу.
- Бројање ћелија на основу боје позадине у Екцелу.
- Како збројити колону у Екцелу (5 заиста лаких начина)
