Všeobecný vzorec
=COUNTIF(INDIRECT("'"&sheetname&"'!"&"range"),criteria)
Zhrnutie
Ak chcete v zošite vyhľadať hodnotu a vrátiť počet, môžete použiť vzorec založený na funkciách COUNTIF a INDIRECT. S určitým predbežným nastavením môžete tento prístup použiť na vyhľadanie konkrétnej hodnoty v celom zošite. V zobrazenom príklade je vzorec v C5:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),$C$4)
Kontext - vzorové údaje
Zošit obsahuje celkom 4 pracovné listy. Každý hárok1 , hárok2 a hárok3 obsahuje 1 000 náhodných krstných mien, ktoré vyzerajú takto:
Vysvetlenie
Rozsah B7: B9 obsahuje názvy hárkov, ktoré chceme zahrnúť do vyhľadávania. Toto sú iba textové reťazce a musíme urobiť nejaké úsilie, aby boli rozpoznané ako platné odkazy na hárky.
Pri práci zvnútra von sa tento výraz používa na vytvorenie odkazu na celý hárok:
"'"&B7&"'!"&"1:1048576"
Jednoduché úvodzovky sa pridávajú, aby boli názvy listov s medzerami, a výkričník je štandardná syntax pre rozsahy, ktoré obsahujú názov hárka. Text „1: 1048576“ je rozsah, ktorý zahŕňa každý riadok v hárku.
Po vyhodnotení B7 a zreťazení hodnôt sa vráti výraz vyššie:
"'Sheet1'!1:1048576"
ktorý vstupuje do funkcie INDIRECT ako argument 'ref_text'. INDIRECT vyhodnotí tento text a vráti štandardný odkaz na každú bunku v hárku1 . Ide o funkciu COUNTIF ako o rozsah. Kritériá sú poskytované ako absolútny odkaz na C4 (uzamknuté, aby bolo možné vzorec kopírovať do stĺpca C).
COUNTIF potom vráti počet všetkých buniek s hodnotou rovnajúcou sa „mary“, v tomto prípade 25.
Poznámka: Funkcia COUNTIF nerozlišuje veľké a malé písmená.
Obsahuje vs. rovná sa
Ak chcete spočítať všetky bunky, ktoré obsahujú hodnotu v C4, môžete namiesto všetkých buniek rovných C4 pridať do kritérií napríklad zástupné znaky:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),"*"&C4&"*")
Teraz COUNTIF bude počítať bunky s podreťazcom „John“ kdekoľvek v bunke.
Výkon
Všeobecne nie je dobrým zvykom určovať rozsah, ktorý obsahuje všetky bunky pracovného hárka. Môže to spôsobiť problémy s výkonom, pretože rozsah zahŕňa milióny a milióny buniek. V tomto príklade je problém zložený, pretože vzorec používa funkciu NEPRIAME, čo je volatilná funkcia. Prchavé funkcie sa prepočítavajú pri každej zmene pracovného hárka, takže vplyv na výkon môže byť obrovský.
Pokiaľ je to možné, obmedzte rozsahy na rozumnú veľkosť. Ak napríklad viete, že sa údaje nezobrazia za riadkom 1000, môžete prehľadať iba prvých 1000 riadkov takto:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1000"),$C$4)
