Генеричка формула
=COUNTIF(INDIRECT("'"&sheetname&"'!"&"range"),criteria)
Резиме
Да бисте у радној свесци тражили вредност у више радних листова и вратили број, можете да користите формулу засновану на функцијама ЦОУНТИФ и ИНДИРЕЦТ. Уз неко прелиминарно подешавање, можете користити овај приступ да бисте у целој радној свесци тражили одређену вредност. У приказаном примеру, формула у Ц5 је:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),$C$4)
Контекст - узорци података
Радна свеска садржи укупно 4 радна листа. Схеет1 , Схеет2 , а Схеет3 свака садржи 1000 случајне прве имена која изгледа овако:
Објашњење
Опсег Б7: Б9 садржи имена листова која желимо да уврстимо у претрагу. То су само текстуални низови и морамо учинити нешто да бисмо их препознали као важеће референце листа.
Радећи изнутра према ван, овај израз се користи за изградњу референци целог листа:
"'"&B7&"'!"&"1:1048576"
Појединачни наводници се додају како би се омогућила имена листова с размацима, а узвичник је стандардна синтакса за опсеге који укључују име листа. Текст „1: 1048576“ је опсег који укључује сваки ред на радном листу.
Након процене Б7 и спајања вредности, горњи израз враћа:
"'Sheet1'!1:1048576"
која улази у функцију ИНДИРЕЦТ као аргумент 'реф_тект'. ИНДИРЕЦТ процењује овај текст и враћа стандардну референцу на сваку ћелију у Схеет1 . Ово прелази у функцију ЦОУНТИФ као опсег. Критеријуми су дати као апсолутна референца на Ц4 (закључани тако да се формула може копирати у колону Ц).
Тада ЦОУНТИФ враћа број свих ћелија са вредношћу која је једнака „мари“, 25 у овом случају.
Напомена: ЦОУНТИФ не разликује велика и мала слова.
Садржи у односу на једнако
Ако желите да пребројите све ћелије које садрже вредност у Ц4, уместо свих ћелија једнаких Ц4, можете да додате џокер знакове као што је овај:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),"*"&C4&"*")
Сада ће ЦОУНТИФ бројати ћелије са поднизом „Јохн“ било где у ћелији.
Перформансе
Генерално, није добра пракса одређивање опсега који укључује све ћелије радног листа. То може изазвати проблеме са перформансама, јер опсег укључује милионе и милионе ћелија. У овом примеру је проблем сложен, јер формула користи ИНДИРЕЦТ функцију, која је променљива функција. Испарљиве функције се прерачунавају при свакој промени радног листа, тако да утицај на перформансе може бити огроман.
Када је могуће, ограничите домете на разумну величину. На пример, ако знате да се подаци неће појавити након 1000 реда, можете претражити само првих 1000 редова овако:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1000"),$C$4)
