Резиме
Да бисте конфигурисали ЦОУНТИФС (или ЦОУНТИФ) са променљивим опсегом, можете користити функцију ОФФСЕТ. У приказаном примеру, формула у Б11 је:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Ова формула броји непразне ћелије у опсегу који почиње на Б5 и завршава се 2 реда изнад ћелије у којој живи формула. Иста формула се копира и налепи 2 реда испод последњег уноса у податке, као што је приказано.
Објашњење
У приказаном примеру, формула у Б11 је:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Радећи изнутра према ван, посао постављања променљивог опсега обавља функција ОФФСЕТ овде:
OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1) // variable range
ОФФСЕТ има пет аргумената и конфигурисан је овако:
- референца = Б $ 5, почиње у ћелији Б5, ред закључан
- редови = 0, померају се нула редова од почетне ћелије
- цолс = 0, офсет нула колона полази од ћелије
- висина = РЕД () - РЕД (Б $ 5) -1 = 5 редова високо
- ширина = 1 колона ширине
Да бисмо израчунали висину опсега у редовима, користимо функцију РОВ овако:
ROW()-ROW(B$5)-1 // work out height
Будући да РОВ () враћа број реда „тренутне“ ћелије (тј. Ћелије у којој живи формула), можемо поједноставити на следећи начин:
=ROW()-ROW(B$5)-1 =11-5-1 =5
Са горњом конфигурацијом, ОФФСЕТ враћа опсег Б5: Б9 директно у ЦОУНТИФС:
=COUNTIFS(B5:B9,"") // returns 4
Приметите да је референца на Б $ 5 у горњој формули помешана референца, са релативном колоном и закључаним редом. Ово омогућава копирање формуле у другу колону и даље рад. На пример, након копирања у Ц12, формула је:
=COUNTIFS(OFFSET(C$5,0,0,ROW()-ROW(C$5)-1,1),"")
Напомена: ОФФСЕТ је испарљива функција и може проузроковати проблеме са перформансама на великим или сложеним радним листовима.
Са ИНДИРЕКТНОМ и АДРЕСОМ
Други приступ је употреба формуле засноване на ИНДИРЕЦТ и АДДРЕСС функцијама. У овом случају, скупљамо опсег као текст, а затим користимо ИНДИРЕЦТ за процену текста као референце. Формула у Б11 би била:
=COUNTIFS(INDIRECT(ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())),"")
Функција АДДРЕСС се користи за конструкцију опсега попут овог:
ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())
У првом случају АДРЕСЕ, број реда додељујемо као кодирану вредност 5, а број колоне додељујемо функцији ЦОЛУМН:
=ADDRESS(5,COLUMN()) // returns "$B$5"
У другом случају испоручујемо „тренутни“ редни број минус 2, а тренутну колону функцијом ЦОЛУМН:
=ADDRESS(ROW()-2,COLUMN()) // returns "$B$9"
Након спајања ове две вредности заједно, имамо:
"$B$5:$B$9" // as text
Имајте на уму да је ово текстуални низ. Да бисмо претворили у важећу референцу, морамо да користимо ИНДИРЕЦТ:
=INDIRECT("$B$5:$B$9") // returns $B$5:$B$9 as valid range
Коначно, формула у Б11 постаје:
=COUNTIFS($B$5:$B$9,"") // returns 4
Напомена: ИНДИРЕЦТ је испарљива функција и може проузроковати проблеме са перформансама на великим или сложеним радним листовима.
