Sammanfattning
För att konfigurera COUNTIFS (eller COUNTIF) med ett variabelt intervall kan du använda OFFSET-funktionen. I exemplet som visas är formeln i B11:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Denna formel räknar celler som inte är tomma i ett intervall som börjar vid B5 och slutar 2 rader ovanför cellen där formeln finns. Samma formel kopieras och klistras in två rader under den sista posten i data som visas.
Förklaring
I exemplet som visas är formeln i B11:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Arbetar inifrån och ut, arbetet med att ställa in ett variabelt intervall görs av OFFSET-funktionen här:
OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1) // variable range
OFFSET har fem argument och konfigureras så här:
- referens = B $ 5, börja vid cell B5, rad låst
- rader = 0, förskjutning noll rader från startcellen
- cols = 0, offset noll kolumner startcell
- höjd = RAD () - RAD (B $ 5) -1 = 5 rader hög
- bredd = 1 kolumn bred
För att räkna ut intervallets höjd i rader använder vi ROW-funktionen så här:
ROW()-ROW(B$5)-1 // work out height
Eftersom ROW () returnerar radnumret för den "nuvarande" cellen (dvs. cellen formeln lever i) kan vi förenkla så här:
=ROW()-ROW(B$5)-1 =11-5-1 =5
Med ovanstående konfiguration returnerar OFFSET intervallet B5: B9 direkt till COUNTIFS:
=COUNTIFS(B5:B9,"") // returns 4
Observera att hänvisningen till B $ 5 i ovanstående formel är en blandad referens, med kolumnen relativ och raden låst. Detta gör att formeln kan kopieras till en annan kolumn och fortfarande fungera. När den till exempel har kopierats till C12 är formeln:
=COUNTIFS(OFFSET(C$5,0,0,ROW()-ROW(C$5)-1,1),"")
Obs: OFFSET är en flyktig funktion och kan orsaka prestandaproblem i stora eller komplexa kalkylblad.
Med INDIRECT och ADRESS
Ett annat tillvägagångssätt är att använda en formel baserad på INDIRECT- och ADRESS-funktionerna. I det här fallet sammanställer vi ett intervall som text och använder sedan INDIRECT för att utvärdera texten som referens. Formeln i B11 skulle vara:
=COUNTIFS(INDIRECT(ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())),"")
ADRESS-funktionen används för att konstruera ett intervall så här:
ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())
I den första instansen av ADRESS anger vi radnummer som det hårdkodade värdet 5 och ger kolumnnumret funktionen KOLONN:
=ADDRESS(5,COLUMN()) // returns "$B$5"
I det andra fallet levererar vi det "aktuella" radnumret minus 2 och den aktuella kolumnen med COLUMN-funktionen:
=ADDRESS(ROW()-2,COLUMN()) // returns "$B$9"
Efter att ha sammanfogat dessa två värden har vi:
"$B$5:$B$9" // as text
Observera att detta är en textsträng. För att konvertera till en giltig referens måste vi använda INDIRECT:
=INDIRECT("$B$5:$B$9") // returns $B$5:$B$9 as valid range
Slutligen blir formeln i B11:
=COUNTIFS($B$5:$B$9,"") // returns 4
Obs: INDIRECT är en flyktig funktion och kan orsaka prestandaproblem i stora eller komplexa kalkylblad.
