Hľadajte hodnotu vo viacerých pracovných listoch

Ak chcete v zošite vyhľadať hodnotu a vrátiť počet, môžete použiť vzorec založený na COUNTIF a NEPRIAMY funkcie. 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 uvedenom príklade vzorec v C5 je:

= COUNTIF ( INDIRECT ('''&sheetname&''!'&'range'),criteria)

Kontext - ukážkové údaje

Pracovný zošit obsahuje celkom 4 pracovné listy. List 1 , List 2 a List 3 každý obsahuje 1 000 náhodných krstných mien, ktoré vyzerajú takto:

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ť kus práce, aby boli uznané ako platné odkazy na hárky.

ako môžete zobraziť zoznam názvov rozsahov, aby ste uľahčili ich úpravu alebo odstránenie?

Tento výraz, ktorý funguje zvnútra von, sa používa na zostavenie odkazu na celý list:

 
= COUNTIF ( INDIRECT ('''&B7&''!'&'1:1048576'),$C)

Jednoduché úvodzovky sú pridané tak, aby umožňovali názvy hárkov s medzerami a výkričník je štandardnou syntaxou pre rozsahy, ktoré obsahujú názov hárka. Text „1: 1048576“ je rozsah, ktorý zahŕňa každý riadok v pracovnom hárku.

Po vyhodnotení B7 a zreťazení hodnôt vráti vyššie uvedený výraz:

ako používať rozptyl v programe Excel

 
'''&B7&''!'&'1:1048576'

ktorý ide do NEPRIAMA funkcia ako argument „ref_text“. INDIRECT vyhodnotí tento text a vráti štandardný odkaz na každú bunku v List 1 . Toto vstupuje do funkcie COUNTIF ako rozsah. Kritériá sú uvedené ako absolútna referencia do C4 (uzamknuté, aby bolo možné vzorec skopírovať do stĺpca C).

COUNTIF potom vráti počet všetkých buniek s hodnotou rovnou „mary“, v tomto prípade 25.

ako vytvoriť časový rozvrh v programe Excel

Poznámka: V COUNTIF sa nerozlišujú malé a veľké písmená.

Obsahuje vs. rovná sa

Ak chcete spočítať všetky bunky, ktoré obsahovať hodnota v C4 namiesto všetkých buniek rovnocenný do C4, môžete pridať zástupné znaky podľa týchto kritérií:

 
''Sheet1'!1:1048576'

Teraz COUNTIF bude počítať bunky s podreťazcom „John“ kdekoľvek v bunke.

Výkon

Vo všeobecnosti nie je vhodné špecifikovať rozsah, ktorý obsahuje všetky bunky pracovného hárka. Mohlo by to spôsobiť problémy s výkonom, pretože rozsah zahŕňa milióny a milióny buniek. V tomto prípade je problém znásobený, pretože vzorec používa funkciu NEPRIAMY, ktorou je a prchavá funkcia . Prchavé funkcie sa prepočítavajú pri každej zmene pracovného hárka, takže vplyv na výkon môže byť obrovský.

Ak je to možné, obmedzte rozsahy na rozumnú veľkosť. Ak napríklad viete, že sa údaje nezobrazia za riadkom 1 000, môžete vyhľadať iba prvých 1 000 riadkov takto:

 
= COUNTIF ( INDIRECT ('''&B7&''!'&'1:1048576'),'*'&C4&'*')