Általános képlet
=COUNTIF(INDIRECT("'"&sheetname&"'!"&"range"),criteria)
Összegzés
Ha egy munkafüzetben több munkalapon szeretne keresni egy értéket és visszaadni a számlálást, használhatja a COUNTIF és az INDIRECT függvényeken alapuló képletet. Néhány előzetes beállítással ezt a megközelítést használhatja egy teljes munkafüzet keresésére egy adott értékre. A bemutatott példában a C5 képlete a következő:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),$C$4)
Környezet - minta adatok
A munkafüzet összesen 4 munkalapot tartalmaz. Munkalap1 , Munkalap2 és Sheet3 egyenként 1000 véletlenszerűen első nevek néz ki:
Magyarázat
A B7: B9 tartomány tartalmazza azokat a lapneveket, amelyeket fel akarunk venni a keresésben. Ezek csak szöveges karakterláncok, és némi munkát kell végeznünk annak érdekében, hogy érvényes lap hivatkozásokként ismerjék el őket.
Belülről kifelé haladva ezt a kifejezést használják egy teljes lapos hivatkozás felépítéséhez:
"'"&B7&"'!"&"1:1048576"
Az egyes idézőjelek hozzáadódnak ahhoz, hogy a lapnevek szóközökkel legyenek engedélyezve, és a felkiáltójel a szintaxis a lapnevet tartalmazó tartományok számára. Az "1: 1048576" szöveg egy olyan tartomány, amely a munkalap minden sorát tartalmazza.
A B7 kiértékelése és az értékek összefűzése után a fenti kifejezés visszatér:
"'Sheet1'!1:1048576"
amely a „ref_text” argumentumként belemegy az INDIRECT függvénybe. Az INDIRECT kiértékeli ezt a szöveget, és normál hivatkozást ad vissza a Sheet1 minden cellájára . Ez a COUNTIF függvénybe kerül tartományként. A kritériumokat abszolút hivatkozásként adjuk meg a C4-re (lezárva, hogy a képlet lemásolható legyen a C oszlopban).
A COUNTIF ezután visszaadja az összes cella számát, amelynek értéke "mary", ebben az esetben 25.
Megjegyzés: A COUNTIF nem különbözteti meg a kis- és nagybetűket.
Tartalmaz vs. Egyenlő
Ha meg akarja számolni az összes cellát, amely tartalmazza az értéket a C4-ben, a C4-vel egyenlő összes sejt helyett, akkor helyettesítő karaktereket adhat hozzá az alábbi feltételekhez:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),"*"&C4&"*")
Most a COUNTIF megszámolja a cellákat a "John" alszöveggel bárhol a cellában.
Teljesítmény
Általában nem jó gyakorlat olyan tartomány meghatározása, amely az összes munkalap cellát tartalmazza. Ez teljesítményproblémákat okozhat, mivel a tartomány millió és millió cellát tartalmaz. Ebben a példában a probléma összetett, mivel a képlet az INDIRECT függvényt használja, amely egy volatilis függvény. Az illékony függvények minden munkalap-változás után újraszámolnak, így a teljesítményre gyakorolt hatás óriási lehet.
Ha lehetséges, korlátozza a tartományokat ésszerű méretre. Például, ha tudja, hogy az adatok nem jelennek meg az 1000. sor után, akkor csak az első 1000 sorban kereshet így:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1000"),$C$4)
