Meklējiet vērtību vairākās darblapās

Lai darbgrāmatā meklētu vērtību vairākās darblapās un atgrieztu skaitu, varat izmantot formulu, kuras pamatā ir COUNTIF un NETIEŠI funkcijas. Veicot iepriekšēju iestatīšanu, varat izmantot šo pieeju, lai meklētu noteiktu darbgrāmatu visā darbgrāmatā. Parādītajā piemērā C5 formula ir šāda:

= COUNTIF ( INDIRECT ('''&sheetname&''!'&'range'),criteria)

Konteksts - datu paraugi

Kopā darbgrāmatā ir 4 darblapas. 1. lapa , 2. lapa , un 3. lapa katrā ir 1000 izlases vārdi, kas izskatās šādi:

Diapazons B7: B9 satur lapu nosaukumus, kurus vēlamies iekļaut meklēšanā. Tās ir tikai teksta virknes, un mums ir jāveic kāds darbs, lai tās atzītu par derīgām atsaucēm uz lapām.

Excel iegūst šūnas vērtību pēc adreses

Strādājot no iekšpuses uz āru, šo izteicienu izmanto, lai izveidotu pilnu atsauces lapu:

 
= COUNTIF ( INDIRECT ('''&B7&''!'&'1:1048576'),$C)

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ž:

kā izveidot vlookup formulu

 
'''&B7&''!'&'1:1048576'

kas nonāk Netieša funkcija kā “ref_text” argumentu. INDIRECT novērtē šo tekstu un atgriež standarta atsauci uz katru šūnu 1. lapa . Tas tiek iekļauts funkcijā COUNTIF kā diapazons. Kritēriji ir norādīti kā absolūta atsauce uz C4 (bloķēts, 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”, 25 šajā gadījumā.

kā uzstāties testā

Piezīme. COUNTIF nav reģistrjutīgs.

Satur pret vienādiem

Ja vēlaties saskaitīt visas šūnas saturēt vērtība C4, nevis visas šūnas vienāds uz C4, varat pievienot aizstājējzīmes uz šādiem kritērijiem:

 
''Sheet1'!1:1048576'

Tagad COUNTIF saskaitīs šūnas ar apakšvirkni “Jānis” jebkurā šūnas vietā.

Veiktspēja

Parasti nav laba prakse norādīt diapazonu, kas ietver visas darblapas šūnas. Tas var radīt veiktspējas problēmas, jo diapazonā ir miljoniem un miljoniem šūnu. Šajā piemērā problēma ir sarežģīta, jo formula izmanto funkciju INDIRECT, kas ir a gaistoša funkcija . Gaistošās funkcijas tiek pārrēķinātas pēc katras darblapas maiņas, tāpēc ietekme uz veiktspēju var būt milzīga.

Ja iespējams, ierobežojiet diapazonus līdz saprātīgam izmēram. Piemēram, ja 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:1048576'),'*'&C4&'*')