Vispārēja formula
=COUNTIF(INDIRECT("'"&sheetname&"'!"&"range"),criteria)
Kopsavilkums
Lai meklētu vērtību vairākās darbgrāmatās darbgrāmatā un atgrieztu skaitu, varat izmantot formulu, kas balstīta uz funkcijām COUNTIF un INDIRECT. Izmantojot iepriekšēju iestatīšanu, varat izmantot šo pieeju, lai meklētu noteiktu darbgrāmatu visā darbgrāmatā. Parādītajā piemērā formula C5 ir:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),$C$4)
Konteksts - datu paraugs
Darbgrāmatā kopā ir 4 darblapas. Sheet1 , Sheet2 un Sheet3 satur 1000 nejaušus vārdus, kas izskatās šādi:
Paskaidrojums
Diapazons B7: B9 satur lapu nosaukumus, kurus vēlamies iekļaut meklēšanā. Tās ir tikai teksta virknes, un mums ir jādara zināms darbs, lai tās tiktu atzītas par derīgām lapu atsaucēm.
Strādājot no iekšpuses, šī izteiksme tiek izmantota, lai izveidotu pilnas lapas atsauci:
"'"&B7&"'!"&"1:1048576"
Atsevišķas pēdiņas tiek pievienotas, lai atļautu lapu nosaukumus ar atstarpēm, un izsaukuma zīme ir standarta sintakse diapazoniem, kas ietver lapas nosaukumu. Teksts "1: 1048576" ir diapazons, kas ietver katru darblapas rindu.
Pēc tam, kad B7 ir novērtēts un vērtības ir savienotas, iepriekš minētā izteiksme atgriežas:
"'Sheet1'!1:1048576"
kas nonāk netiešajā funkcijā kā “ref_text” arguments. INDIRECT novērtē šo tekstu un atgriež standarta atsauci uz katru Sheet1 šūnu . Tas tiek izmantots diapazonā kā funkcija COUNTIF. Kritēriji ir norādīti kā absolūta atsauce uz C4 (bloķēta, lai formulu varētu nokopēt C slejā).
Pēc tam COUNTIF atgriež visu šūnu skaitu ar vērtību, kas vienāda ar "mary", šajā gadījumā 25.
Piezīme: COUNTIF nav reģistrjutīgs.
Satur pret vienādiem
Ja vēlaties saskaitīt visas šūnas, kas satur vērtību C4, nevis visas šūnas, kas vienādas ar C4, šādiem kritērijiem varat pievienot aizstājējzīmes:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),"*"&C4&"*")
Tagad COUNTIF uzskaitīs šūnas ar apakšvirsrakstu "John" jebkur šūnā.
Izrāde
Kopumā nav laba prakse norādīt diapazonu, kas ietver visas darblapas šūnas. Šādi rīkojoties, var rasties veiktspējas problēmas, jo diapazonā ietilpst miljoni un miljoni šūnu. Šajā piemērā problēma tiek papildināta, jo formula izmanto funkciju INDIRECT, kas ir nepastāvīga funkcija. Nepastāvīgās funkcijas pārrēķina par katru darblapas izmaiņu, tāpēc ietekme uz veiktspēju var būt milzīga.
Ja iespējams, ierobežojiet diapazonus līdz saprātīgam lielumam. Piemēram, ja jūs zināt, ka dati netiks rādīti aiz 1000. rindas, varat meklēt tikai pirmajās 1000 rindās šādi:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1000"),$C$4)
