Sammendrag
For å konfigurere COUNTIFS (eller COUNTIF) med et variabelt område, kan du bruke OFFSET-funksjonen. I eksemplet vist er formelen i B11:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Denne formelen teller celler som ikke er tomme i et område som begynner ved B5 og slutter to rader over cellen der formelen bor. Den samme formelen kopieres og limes inn 2 rader under den siste oppføringen i dataene som vist.
Forklaring
I eksemplet vist er formelen i B11:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Arbeidet fra innsiden og ut, arbeidet med å sette opp et variabelt område gjøres av OFFSET-funksjonen her:
OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1) // variable range
OFFSET har fem argumenter og er konfigurert slik:
- referanse = B $ 5, begynn på celle B5, rad låst
- rader = 0, forskjøvet null rader fra startcelle
- cols = 0, offset null kolonner startcelle
- høyde = RAD () - RAD (B $ 5) -1 = 5 rader høy
- bredde = 1 kolonne bred
For å beregne høyden på rekkevidden i rader, bruker vi ROW-funksjonen slik:
ROW()-ROW(B$5)-1 // work out height
Siden ROW () returnerer radnummeret til den "nåværende" cellen (dvs. cellen formelen lever i), kan vi forenkle slik:
=ROW()-ROW(B$5)-1 =11-5-1 =5
Med konfigurasjonen ovenfor returnerer OFFSET området B5: B9 direkte til COUNTIFS:
=COUNTIFS(B5:B9,"") // returns 4
Legg merke til at referansen til B $ 5 i formelen ovenfor er en blandet referanse, med kolonnen relative og raden låst. Dette gjør at formelen kan kopieres til en annen kolonne og fortsatt fungere. Når den for eksempel er kopiert til C12, er formelen:
=COUNTIFS(OFFSET(C$5,0,0,ROW()-ROW(C$5)-1,1),"")
Merk: OFFSET er en flyktig funksjon og kan forårsake ytelsesproblemer i store eller komplekse regneark.
Med INDIRECT og ADRESSE
En annen tilnærming er å bruke en formel basert på INDIRECT og ADDRESS-funksjonene. I dette tilfellet setter vi sammen et område som tekst, og bruker deretter INDIRECT til å evaluere teksten som referanse. Formelen i B11 vil være:
=COUNTIFS(INDIRECT(ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())),"")
ADRESSE-funksjonen brukes til å konstruere et område som dette:
ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())
I første omgang av ADRESSE, gir vi radnummer som den hardkodede verdien 5, og gir kolonnetallet COLUMN-funksjonen:
=ADDRESS(5,COLUMN()) // returns "$B$5"
I andre omgang leverer vi det "nåværende" radnummeret minus 2, og den nåværende kolonnen med COLUMN-funksjonen:
=ADDRESS(ROW()-2,COLUMN()) // returns "$B$9"
Etter sammenkobling av disse to verdiene har vi:
"$B$5:$B$9" // as text
Merk at dette er en tekststreng. For å konvertere til en gyldig referanse, må vi bruke INDIRECT:
=INDIRECT("$B$5:$B$9") // returns $B$5:$B$9 as valid range
Til slutt blir formelen i B11:
=COUNTIFS($B$5:$B$9,"") // returns 4
Merk: INDIRECT er en flyktig funksjon og kan forårsake ytelsesproblemer i store eller komplekse regneark.
