Bendroji formulė
=COUNTIF(INDIRECT("'"&sheetname&"'!"&"range"),criteria)
Santrauka
Norėdami ieškoti vertės keliose darbaknygės vertės lentelėse ir grąžinti skaičių, galite naudoti formulę, pagrįstą funkcijomis COUNTIF ir NETIESIOGINIS. Kai atliksite išankstinę sąranką, galite naudoti šį metodą, norėdami ieškoti konkrečios vertės visoje darbaknygėje. Parodytame pavyzdyje formulė C5 yra:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),$C$4)
Kontekstas - duomenų pavyzdys
Darbaknygėje yra iš viso 4 darbalapiai. Kiekviename „Sheet1“ , „ Sheet2“ ir „ Sheet3“ yra 1000 atsitiktinių vardų, kurie atrodo taip:
Paaiškinimas
Diapazone B7: B9 yra lapų pavadinimai, kuriuos norime įtraukti į paiešką. Tai tik teksto eilutės, ir mes turime atlikti tam tikrą darbą, kad jos būtų pripažintos galiojančiomis lapų nuorodomis.
Dirbant iš vidaus, ši išraiška naudojama kuriant viso lapo nuorodą:
"'"&B7&"'!"&"1:1048576"
Norint leisti lapų pavadinimus su tarpais, pridedamos vienos kabutės, o šauktukas yra standartinė diapazonų, į kuriuos įeina lapo pavadinimas, sintaksė. Tekstas „1: 1048576“ yra diapazonas, apimantis visas darbalapio eilutes.
Įvertinus B7 ir sujungus vertes, aukščiau pateikta išraiška grąžinama:
"'Sheet1'!1:1048576"
kuris eina į NETIESIOGIN function funkciją kaip „ref_text“ argumentą. Netiesioginis įvertina šį tekstą ir grąžina standartinę nuorodą į kiekvieną „ Sheet1“ langelį . Tai patenka į funkciją COUNTIF kaip diapazoną. Kriterijai pateikiami kaip absoliuti nuoroda į C4 (užrakinta, kad formulę būtų galima nukopijuoti žemiau C stulpelio).
Tada COUNTIF pateikia visų langelių skaičių, kurio vertė lygi „mary“, šiuo atveju 25.
Pastaba: „COUNTIF“ neskiria didžiųjų ir mažųjų raidžių.
Sudėtyje yra lygūs
Jei norite suskaičiuoti visas langelius, kurių vertė yra C4, vietoj visų langelių, lygių C4, prie tokių kriterijų galite pridėti pakaitos simbolius:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),"*"&C4&"*")
Dabar COUNTIF suskaičiuos langelius su posakiu „John“ bet kurioje langelio vietoje.
Spektaklis
Apskritai nėra gera praktika nurodyti diapazoną, kuris apima visas darbalapio ląsteles. Tai padarius, gali kilti našumo problemų, nes diapazone yra milijonai ir milijonai ląstelių. Šiame pavyzdyje problema yra sudėtinga, nes formulėje naudojama funkcija NETIESIOGINĖ, kuri yra nepastovi funkcija. Nepastovios funkcijos perskaičiuojamos kiekvieną pakeitimą darbalapyje, todėl poveikis našumui gali būti didžiulis.
Kai įmanoma, apribokite intervalus iki protingo dydžio. Pavyzdžiui, jei žinote, kad duomenys nebus rodomi po 1000 eilutės, galite ieškoti tik pirmose 1000 eilučių taip:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1000"),$C$4)
