
rezumat
Pentru a configura COUNTIFS (sau COUNTIF) cu un interval variabil, puteți utiliza funcția OFFSET. În exemplul prezentat, formula din B11 este:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Această formulă numără celulele care nu sunt goale într-un interval care începe la B5 și se termină cu 2 rânduri deasupra celulei în care locuiește formula. Aceeași formulă este copiată și lipită 2 rânduri sub ultima intrare din date așa cum se arată.
Explicaţie
În exemplul prezentat, formula din B11 este:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Lucrând din interior spre exterior, activitatea de configurare a unui interval variabil se face prin funcția OFFSET aici:
OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1) // variable range
OFFSET are cinci argumente și este configurat astfel:
- referință = B $ 5, începeți de la celula B5, rând blocat
- rânduri = 0, compensează zero rânduri de la celula de pornire
- cols = 0, compensează zero coloane pornind celula
- înălțime = ROW () - ROW (B $ 5) -1 = 5 rânduri înălțime
- lățime = 1 coloană lată
Pentru a calcula înălțimea intervalului în rânduri, folosim funcția ROW astfel:
ROW()-ROW(B$5)-1 // work out height
Deoarece ROW () returnează numărul rândului celulei „curente” (adică celula în care trăiește formula), putem simplifica astfel:
=ROW()-ROW(B$5)-1 =11-5-1 =5
Cu configurația de mai sus, OFFSET returnează intervalul B5: B9 direct la COUNTIFS:
=COUNTIFS(B5:B9,"") // returns 4
Observați că referința la 5 USD în formula de mai sus este o referință mixtă, cu coloana relativă și rândul blocat. Acest lucru permite copierea formulei într-o altă coloană și funcționarea în continuare. De exemplu, odată copiat în C12, formula este:
=COUNTIFS(OFFSET(C$5,0,0,ROW()-ROW(C$5)-1,1),"")
Notă: OFFSET este o funcție volatilă și poate provoca probleme de performanță în foile de lucru mari sau complexe.
Cu INDIRECT și ADRESĂ
O altă abordare este utilizarea unei formule bazate pe funcțiile INDIRECTE și ADRESĂ. În acest caz, asamblăm un interval ca text, apoi folosim INDIRECT pentru a evalua textul ca referință. Formula din B11 ar fi:
=COUNTIFS(INDIRECT(ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())),"")
Funcția ADRESĂ este utilizată pentru a construi un interval ca acesta:
ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())
În prima instanță a ADDRESS, furnizăm numărul rândului ca valoare codificată 5 și furnizăm numărul coloanei cu funcția COLUMN:
=ADDRESS(5,COLUMN()) // returns "$B$5"
În a doua instanță, furnizăm numărul de rând „curent” minus 2 și coloana curentă cu funcția COLUMN:
=ADDRESS(ROW()-2,COLUMN()) // returns "$B$9"
După concatenarea acestor două valori împreună, avem:
"$B$5:$B$9" // as text
Rețineți că acesta este un șir de text. Pentru a converti la o referință validă, trebuie să folosim INDIRECT:
=INDIRECT("$B$5:$B$9") // returns $B$5:$B$9 as valid range
În cele din urmă, formula din B11 devine:
=COUNTIFS($B$5:$B$9,"") // returns 4
Notă: INDIRECT este o funcție volatilă și poate provoca probleme de performanță în foile de lucru mari sau complexe.