Resumé
For at konfigurere COUNTIFS (eller COUNTIF) med et variabelt interval kan du bruge OFFSET-funktionen. I det viste eksempel er formlen i B11:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Denne formel tæller celler, der ikke er tomme, i et interval, der begynder ved B5 og slutter 2 rækker over den celle, hvor formlen bor. Den samme formel kopieres og indsættes 2 rækker under den sidste post i dataene som vist.
Forklaring
I det viste eksempel er formlen i B11:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Arbejdet indefra og ud, arbejdet med at oprette et variabelt interval udføres af OFFSET-funktionen her:
OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1) // variable range
OFFSET har fem argumenter og er konfigureret således:
- reference = B $ 5, start ved celle B5, række låst
- rækker = 0, forskudt nul rækker fra startcelle
- cols = 0, forskudt nul kolonner startcelle
- højde = ROW () - ROW (B $ 5) -1 = 5 rækker høj
- bredde = 1 kolonne bred
For at beregne højden af rækkevidden i rækker bruger vi ROW-funktionen på denne måde:
ROW()-ROW(B$5)-1 // work out height
Da ROW () returnerer række nummeret for den "aktuelle" celle (dvs. cellen, formlen lever i), kan vi forenkle sådan:
=ROW()-ROW(B$5)-1 =11-5-1 =5
Med ovenstående konfiguration returnerer OFFSET området B5: B9 direkte til COUNTIFS:
=COUNTIFS(B5:B9,"") // returns 4
Bemærk, at henvisningen til B $ 5 i ovenstående formel er en blandet reference, hvor kolonnen er relativ og rækken låst. Dette gør det muligt at kopiere formlen til en anden kolonne og stadig fungere. Når den f.eks. Er kopieret til C12, er formlen:
=COUNTIFS(OFFSET(C$5,0,0,ROW()-ROW(C$5)-1,1),"")
Bemærk: OFFSET er en flygtig funktion og kan forårsage ydeevneproblemer i store eller komplekse regneark.
Med INDIRECT og ADRESSE
En anden tilgang er at bruge en formel baseret på INDIRECT og ADDRESS-funktionerne. I dette tilfælde samler vi et interval som tekst og bruger derefter INDIRECT til at evaluere teksten som reference. Formlen i B11 ville være:
=COUNTIFS(INDIRECT(ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())),"")
ADRESSE-funktionen bruges til at konstruere et interval som dette:
ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())
I første omgang af ADRESSE leverer vi række nummer som den hårdkodede værdi 5 og giver kolonne nummeret med COLUMN funktionen:
=ADDRESS(5,COLUMN()) // returns "$B$5"
I andet tilfælde leverer vi det "aktuelle" række nummer minus 2 og den aktuelle kolonne med COLUMN-funktionen:
=ADDRESS(ROW()-2,COLUMN()) // returns "$B$9"
Efter sammenkædning af disse to værdier har vi:
"$B$5:$B$9" // as text
Bemærk dette er en tekststreng. For at konvertere til en gyldig reference skal vi bruge INDIRECT:
=INDIRECT("$B$5:$B$9") // returns $B$5:$B$9 as valid range
Endelig bliver formlen i B11:
=COUNTIFS($B$5:$B$9,"") // returns 4
Bemærk: INDIRECT er en flygtig funktion og kan forårsage ydeevneproblemer i store eller komplekse regneark.
