Генеричка формула
(=SUM(--(FREQUENCY(IF(criteria,MATCH(vals,vals,0)),ROW(vals)-ROW(vals.first)+1)>0)))
Резиме
Да бисте бројали јединствене вредности текста у опсегу са критеријумима, можете користити формулу низа засновану на функцијама ФРЕКУЕНЦИ и МАТЦХ. У приказаном примеру, формула у Г6 је:
(=SUM(--(FREQUENCY(IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0)),ROW(B5:B11)-ROW(B5)+1)>0)))
који враћа 3, пошто су три различита човека радила на пројекту Омега.
Напомена: ово је формула низа и мора се унети са цонтрол + схифт + ентер.
Објашњење
Ово је сложена формула која користи ФРЕКУЕНЦИ за бројање нумеричких вредности изведених помоћу функције МАТЦХ. Функционишући изнутра према унутра, функција МАТЦХ се користи за добијање положаја сваке вредности која се појављује у подацима:
MATCH(B5:B11,B5:B11,0)
Резултат из МАТЦХ-а је низ попут овог:
(1;1;3;1;1;6;7)
Будући да МАТЦХ увек враћа позицију првог подударања, вредности које се појављују више пута у подацима враћају исту позицију. На пример, зато што се „Јим“ појављује 4 пута на листи, он се у овом низу приказује 4 пута као број 1.
Изван функције МАТЦХ, функција ИФ користи се за примену критеријума, што у овом случају укључује испитивање да ли је пројекат „омега“ (из ћелије Г5):
IF(C5:C11=G5 // filter on "omega"
ИФ функција делује као филтер, дозвољавајући да вредности из МАТЦХ прођу само ако су повезане са „омега“. Резултат је низ попут овог:
(FALSE;FALSE;FALSE;1;1;6;7) // after filtering
Филтрирани низ испоручује се директно функцији ФРЕКУЕНЦИ као аргумент дата_арраи . Даље, функција РЕД се користи за изградњу секвенцијалне листе бројева за сваку вредност у подацима:
ROW(B3:B12)-ROW(B3)+1
Ово ствара низ попут овог:
(1;2;3;4;5;6;7;8;9;10)
који постаје аргумент бинс_арраи у ФИЛТЕР. У овом тренутку имамо:
FREQUENCY((FALSE;FALSE;FALSE;1;1;6;7),(1;2;3;4;5;6;7))
ФРЕКУЕНЦИ враћа низ бројева који указују на бројање сваке вредности у низу података, које организује бин. Када је број већ избројан, ФРЕКУЕНЦИ ће вратити нулу. Резултат из ФРЕКУЕНЦИ је низ попут овог:
(2;0;0;0;0;1;1;0) // result from FREQUENCY
Напомена: ФРЕКУЕНЦИ увек враћа низ са још једном ставком од поља бинс_арраи .
У овом тренутку формулу можемо преписати овако:
=SUM(--((2;0;0;0;0;1;1;0)>0))
Проверавамо да ли постоје вредности веће од нуле, што бројеве претвара у ТРУЕ или ФАЛСЕ:
=SUM(--((TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE)))
Затим користимо двоструки негатив да присилимо логичке вредности на 1с и 0с:
=SUM((1;0;0;0;0;1;1;0))
Коначно, функција СУМ враћа 3 као коначни резултат.
Напомена: ово је формула низа и мора се унети помоћу Цонтрол + Схифт + Ентер.
Руковање празним ћелијама у опсегу
Ако су неке ћелије у опсегу празне, мораћете да прилагодите формулу како бисте спречили преношење празних ћелија у функцију МАТЦХ, што ће довести до грешке. То можете учинити додавањем друге угнежђене функције ИФ да бисте проверили да ли има празних ћелија:
(=SUM(--(FREQUENCY(IF(B5:B11"",IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0))),ROW(B5:B11)-ROW(B5)+1)>0)))
Са два критеријума
Ако имате два критеријума, логику формуле можете проширити додавањем још једног угнежденог ИФ:
(=SUM(--(FREQUENCY(IF(c1,IF(c2,MATCH(vals,vals,0))),ROW(vals)-ROW(vals.1st)+1)>0)))
Где су ц1 = критеријуми1, ц2 = критеријуми2 и валс = распон вредности.
Са логичком логиком
Помоћу логичке логике можете смањити угнежђене ИФ-ове:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),MATCH(vals,vals,0)),ROW(vals)-ROW(vals.1st)+1)>0)))
Ово олакшава додавање и управљање додатним критеријумима.
