Üldine valem
=COUNTIF(INDIRECT("'"&sheetname&"'!"&"range"),criteria)
Kokkuvõte
Töövihikus mitme töölehe väärtuse otsimiseks ja arvu tagastamiseks võite kasutada funktsioone COUNTIF ja INDIRECT põhinevat valemit. Mõningase esialgse seadistusega saate seda lähenemist kasutada konkreetse väärtuse otsimiseks kogu töövihikust. Näidatud näites on valem C5-s:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),$C$4)
Kontekst - näidisandmed
Töövihik sisaldab kokku 4 töölehte. Leht1 , Leht2 ja Leht3 sisaldavad kumbki 1000 juhuslikku eesnime, mis näevad välja sellised:
Selgitus
Vahemik B7: B9 sisaldab lehtede nimesid, mida soovime otsingusse kaasata. Need on vaid tekstistringid ja me peame tegema natuke tööd, et neid tunnustataks kehtivate leheviidetena.
Seestpoolt töötades kasutatakse seda väljendit täislehe viite loomiseks:
"'"&B7&"'!"&"1:1048576"
Üksikud jutumärgid lisatakse tühikutega lehenimede lubamiseks ja hüüumärk on standardne süntaks vahemike jaoks, mis sisaldavad lehe nime. Tekst "1: 1048576" on vahemik, mis sisaldab töölehe kõiki ridu.
Pärast B7 hindamist ja väärtuste liitmist tagastatakse ülaltoodud avaldis:
"'Sheet1'!1:1048576"
mis läheb funktsiooni INDIRECT argumendina 'ref_text'. INDIRECT hindab seda teksti ja tagastab standardviite lehe1 igale lahtrile . See läheb vahemikku funktsiooni COUNTIF. Kriteeriumid esitatakse absoluutse viitena C4-le (lukustatud, nii et valemit saab veergu C kopeerida).
Seejärel tagastab COUNTIF kõigi lahtrite arvu väärtusega "mary", antud juhul 25.
Märkus. COUNTIF ei ole tõstutundlik.
Sisaldab võrdseid
Kui soovite loendada kõik lahtrid, mis sisaldavad väärtust C4-s, saate kõigi C4-ga võrdsete lahtrite asemel lisada metamärke järgmistele kriteeriumidele:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),"*"&C4&"*")
Nüüd loeb COUNTIF kõikjal lahtris alamstringiga "John" lahtrid.
Performance
Üldiselt ei ole hea tava määrata vahemik, mis sisaldab kõiki töölehe lahtreid. See võib põhjustada jõudlusprobleeme, kuna vahemik sisaldab miljoneid ja miljoneid rakke. Selles näites on probleem keerulisem, kuna valem kasutab funktsiooni INDIRECT, mis on kõikuv funktsioon. Lenduvad funktsioonid arvutavad iga töölehe muudatuse korral ümber, nii et mõju jõudlusele võib olla tohutu.
Kui võimalik, piirake vahemikke mõistliku suurusega. Näiteks kui teate, et andmeid ei kuvata 1000. rea taga, saate otsida ainult esimesi 1000 rida järgmiselt:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1000"),$C$4)
