Splošna formula
=COUNTIF(INDIRECT("'"&sheetname&"'!"&"range"),criteria)
Povzetek
Če želite po več delovnih listih v delovnem zvezku poiskati vrednost in vrniti štetje, lahko uporabite formulo, ki temelji na funkcijah COUNTIF in INDIRECT. Z nekaj predhodne nastavitve lahko s tem pristopom iščete določeno vrednost v celotnem delovnem zvezku. V prikazanem primeru je formula v C5:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),$C$4)
Kontekst - vzorčni podatki
Delovni zvezek vsebuje skupaj 4 delovne liste. Sheet1 , Sheet2 in Sheet3 vsebujejo po 1000 naključnih imen, ki so videti takole:
Pojasnilo
Obseg B7: B9 vsebuje imena listov, ki jih želimo vključiti v iskanje. To so samo besedilni nizi in nekaj dela moramo opraviti, da jih bomo prepoznali kot veljavne sklice na liste.
Ta izraz se uporablja od znotraj navzven, da se ustvari referenca celotnega lista:
"'"&B7&"'!"&"1:1048576"
Enotni narekovaji so dodani, da dovoljujejo imena listov s presledki, klicaj pa je standardna sintaksa za obsege, ki vključujejo ime lista. Besedilo "1: 1048576" je obseg, ki vključuje vse vrstice na delovnem listu.
Po oceni B7 in združevanju vrednosti se zgornji izraz vrne:
"'Sheet1'!1:1048576"
ki gre v funkcijo INDIRECT kot argument 'ref_text'. INDIRECT ovrednoti to besedilo in vrne standardni sklic na vsako celico v Sheet1 . To preide v funkcijo COUNTIF kot obseg. Merila so navedena kot absolutna referenca na C4 (zaklenjena, da lahko formulo prepišemo v stolpec C).
Nato COUNTIF vrne štetje vseh celic z vrednostjo, enako "mary", v tem primeru 25.
Opomba: COUNTIF ne razlikuje med velikimi in malimi črkami.
Vsebuje v primerjavi z enakimi
Če želite prešteti vse celice, ki vsebujejo vrednost v C4, lahko namesto vseh celic, enakih C4, dodate nadomestne znake takim merilom:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),"*"&C4&"*")
Zdaj bo COUNTIF prešteval celice s podnizom "John" kjer koli v celici.
Izvedba
Na splošno ni dobra praksa, če določite obseg, ki vključuje vse celice delovnega lista. To lahko povzroči težave z zmogljivostjo, saj obseg vključuje milijone in milijone celic. V tem primeru je težava zapletena, saj formula uporablja funkcijo INDIRECT, ki je spremenljiva funkcija. Hlapne funkcije se preračunajo pri vsaki spremembi delovnega lista, zato je učinek na uspešnost lahko velik.
Če je mogoče, omejite območja na smiselno velikost. Če na primer veste, da se podatki ne bodo pojavili mimo vrstice 1000, lahko poiščete samo prvih 1000 vrstic, kot je ta:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1000"),$C$4)
