Formula Excel: COUNTIFS cu interval variabil -

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.

Articole interesante...