Generička formula
=COUNTIF(INDIRECT("'"&sheetname&"'!"&"range"),criteria)
Sažetak
Za traženje vrijednosti u više radnih listova u radnoj knjizi i vraćanje brojača možete upotrijebiti formulu koja se temelji na funkcijama COUNTIF i INDIRECT. Uz neko preliminarno postavljanje, možete koristiti ovaj pristup za traženje određene vrijednosti u cijeloj radnoj knjizi. U prikazanom primjeru formula u C5 je:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),$C$4)
Kontekst - uzorci podataka
Radna knjiga sadrži ukupno 4 radna lista. Sheet1 , Sheet2 i Sheet3 svaka sadrži 1000 nasumične prva imena koja izgleda ovako:
Obrazloženje
Raspon B7: B9 sadrži nazive listova koje želimo uključiti u pretragu. To su samo tekstualni nizovi, a mi moramo napraviti neki posao kako bismo ih prepoznali kao važeće reference lista.
Radeći iznutra prema van, ovaj se izraz koristi za izgradnju reference cjelokupnog lista:
"'"&B7&"'!"&"1:1048576"
Pojedinačni navodnici dodaju se kako bi se omogućila imena listova s razmacima, a uskličnik je standardna sintaksa za raspone koji uključuju naziv lista. Tekst "1: 1048576" raspon je koji uključuje svaki redak na radnom listu.
Nakon procjene B7 i spajanja vrijednosti, gornji izraz vraća:
"'Sheet1'!1:1048576"
koji ulazi u funkciju INDIRECT kao argument 'ref_text'. INDIRECT procjenjuje ovaj tekst i vraća standardnu referencu na svaku ćeliju u Sheet1 . To ulazi u funkciju COUNTIF kao raspon. Kriteriji su dani kao apsolutna referenca na C4 (zaključani kako bi se formula mogla kopirati u stupac C).
Tada COUNTIF vraća broj svih ćelija s vrijednošću koja je jednaka "mary", 25 u ovom slučaju.
Napomena: COUNTIF ne razlikuje velika i mala slova.
Sadrži u odnosu na jednako
Ako želite brojati sve stanice koje sadrže vrijednost u C4, umjesto svih ćelija jednakih C4, možete dodati zamjenske znakove sljedećim kriterijima:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),"*"&C4&"*")
Sada će COUNTIF brojati stanice s podnizom "John" bilo gdje u ćeliji.
Izvođenje
Općenito, nije dobra praksa određivati raspon koji uključuje sve ćelije radnog lista. To može uzrokovati probleme s performansama, jer raspon uključuje milijune i milijune stanica. U ovom primjeru problem je složen, jer formula koristi INDIRECT funkciju, koja je hlapljiva funkcija. Hlapljive se funkcije preračunavaju pri svakoj promjeni radnog lista, tako da utjecaj na izvedbu može biti velik.
Kad je moguće, ograničite domete na razumnu veličinu. Na primjer, ako znate da se podaci neće pojaviti nakon 1000 redaka, možete pretraživati samo prvih 1000 redaka ovako:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1000"),$C$4)
