Obecný vzorec
=COUNTIF(INDIRECT("'"&sheetname&"'!"&"range"),criteria)
souhrn
Chcete-li v sešitu vyhledat hodnotu v několika listech a vrátit počet, můžete použít vzorec založený na funkcích COUNTIF a INDIRECT. S určitým předběžným nastavením můžete tento přístup použít k vyhledání konkrétní hodnoty v celém sešitu. V zobrazeném příkladu je vzorec v C5:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),$C$4)
Kontext - ukázková data
Sešit obsahuje celkem 4 listy. Každý list1 , list2 a list3 obsahují 1000 náhodných křestních jmen, která vypadají takto:
Vysvětlení
Rozsah B7: B9 obsahuje názvy listů, které chceme zahrnout do vyhledávání. Jedná se pouze o textové řetězce a musíme udělat nějakou práci, abychom je rozpoznali jako platné odkazy na listy.
Při práci zevnitř ven se tento výraz používá k vytvoření odkazu na celý list:
"'"&B7&"'!"&"1:1048576"
Jednotlivé uvozovky jsou přidány, aby umožnily názvy listů s mezerami, a vykřičník je standardní syntaxe pro rozsahy, které obsahují název listu. Text „1: 1048576“ je rozsah, který zahrnuje každý řádek v listu.
Po vyhodnocení B7 a zřetězení hodnot se vrátí výše uvedený výraz:
"'Sheet1'!1:1048576"
který jde do funkce INDIRECT jako argument 'ref_text'. INDIRECT vyhodnotí tento text a vrátí standardní odkaz na každou buňku v List1 . Tím přejde do funkce COUNTIF jako rozsah. Kritéria jsou poskytována jako absolutní odkaz na C4 (uzamčen, takže vzorec lze zkopírovat dolů do sloupce C).
COUNTIF poté vrátí počet všech buněk s hodnotou rovnou "mary", v tomto případě 25.
Poznámka: COUNTIF nerozlišuje velká a malá písmena.
Obsahuje vs. rovná se
Pokud chcete spočítat všechny buňky, které obsahují hodnotu v C4, můžete namísto všech buněk rovných C4 přidat do kritérií například zástupné znaky:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),"*"&C4&"*")
Nyní COUNTIF bude počítat buňky s podřetězcem „John“ kdekoli v buňce.
Výkon
Obecně není dobrým zvykem určit rozsah, který zahrnuje všechny buňky listu. To může způsobit problémy s výkonem, protože rozsah zahrnuje miliony a miliony buněk. V tomto příkladu je problém složený, protože vzorec používá funkci NEPŘÍMÝ, což je těkavá funkce. Těkavé funkce se přepočítávají při každé změně listu, takže dopad na výkon může být obrovský.
Pokud je to možné, omezte rozsahy na rozumnou velikost. Pokud například víte, že se data za řádkem 1000 neobjeví, můžete prohledávat pouze prvních 1000 řádků takto:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1000"),$C$4)
