Formula generică
=COUNTIF(INDIRECT("'"&sheetname&"'!"&"range"),criteria)
rezumat
Pentru a căuta mai multe foi de lucru într-un registru de lucru pentru o valoare și a returna un număr, puteți utiliza o formulă bazată pe funcțiile COUNTIF și INDIRECT. Cu unele setări preliminare, puteți utiliza această abordare pentru a căuta într-un întreg registru de lucru pentru o anumită valoare. În exemplul prezentat, formula din C5 este:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),$C$4)
Context - eșantion de date
Cartea de lucru conține 4 foi de lucru în total. Sheet1 , Sheet2 și Sheet3 conțin fiecare 1000 de nume aleatorii care arată astfel:
Explicaţie
Gama B7: B9 conține numele foilor pe care dorim să le includem în căutare. Acestea sunt doar șiruri de text și trebuie să facem unele lucrări pentru a le face să fie recunoscute ca referințe valide de foi.
Lucrând din interior spre exterior, această expresie este utilizată pentru a construi o referință de foaie completă:
"'"&B7&"'!"&"1:1048576"
Citatele unice sunt adăugate pentru a permite numele foilor cu spații, iar semnul exclamării este o sintaxă standard pentru intervalele care includ un nume de foaie. Textul „1: 1048576” este un interval care include fiecare rând din foaia de lucru.
După ce B7 este evaluat și valorile sunt concatenate, expresia de mai sus returnează:
"'Sheet1'!1:1048576"
care intră în funcția INDIRECT ca argument „ref_text”. INDIRECT evaluează acest text și returnează o referință standard la fiecare celulă din Sheet1 . Aceasta intră în funcția COUNTIF ca interval. Criteriul este furnizat ca referință absolută la C4 (blocat astfel încât formula să poată fi copiată în coloana C).
COUNTIF returnează apoi un număr al tuturor celulelor cu o valoare egală cu „mary”, 25 în acest caz.
Notă: COUNTIF nu este sensibil la majuscule și minuscule.
Conține vs. Egal
Dacă doriți să numărați toate celulele care conțin valoarea în C4, în loc de toate celulele egale cu C4, puteți adăuga metacaractere la criteriile de acest fel:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),"*"&C4&"*")
Acum COUNTIF va număra celulele cu șirul „John” oriunde în celulă.
Performanţă
În general, nu este o practică bună să specificați un interval care să includă toate celulele foii de lucru. Acest lucru poate provoca probleme de performanță, deoarece gama include milioane și milioane de celule. În acest exemplu, problema este agravată, deoarece formula utilizează funcția INDIRECT, care este o funcție volatilă. Funcțiile volatile se recalculează la fiecare modificare a foii de lucru, astfel încât impactul asupra performanței poate fi imens.
Când este posibil, limitați intervalele la o dimensiune sensibilă. De exemplu, dacă știți că datele nu vor apărea după rândul 1000, puteți căuta doar primele 1000 de rânduri astfel:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1000"),$C$4)
