Zhrnutie
Ak chcete nakonfigurovať COUNTIFS (alebo COUNTIF) s variabilným rozsahom, môžete použiť funkciu OFFSET. V zobrazenom príklade je vzorec v B11:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Tento vzorec počíta neprázdne bunky v rozsahu, ktorý začína na B5 a končí 2 riadky nad bunkou, kde vzorec žije. Rovnaký vzorec sa skopíruje a prilepí 2 riadky pod posledný záznam v údajoch, ako je to znázornené.
Vysvetlenie
V zobrazenom príklade je vzorec v B11:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Pri práci zvnútra von sa práca pri nastavovaní premenného rozsahu vykonáva pomocou funkcie OFFSET tu:
OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1) // variable range
OFFSET má päť argumentov a je nakonfigurovaný takto:
- referencia = B $ 5, začiatok v bunke B5, riadok uzamknutý
- riadky = 0, posunutie nulových riadkov od začiatočnej bunky
- cols = 0, posun nulových stĺpcov od počiatočnej bunky
- height = ROW () - ROW (B $ 5) -1 = 5 riadkov vysoký
- šírka = 1 stĺp široký
Na výpočet výšky rozsahu v riadkoch používame funkciu ROW takto:
ROW()-ROW(B$5)-1 // work out height
Pretože ROW () vracia číslo riadku „aktuálnej“ bunky (tj. Bunky, v ktorej žije vzorec), môžeme to zjednodušiť takto:
=ROW()-ROW(B$5)-1 =11-5-1 =5
Pri vyššie uvedenej konfigurácii funkcia OFFSET vráti rozsah B5: B9 priamo na COUNTIFS:
=COUNTIFS(B5:B9,"") // returns 4
Všimnite si, že odkaz na B $ 5 vo vyššie uvedenom vzorci je zmiešaný odkaz so zamknutým relatívnym stĺpcom a riadkom. To umožňuje vzorec skopírovať do iného stĺpca a stále fungovať. Napríklad po skopírovaní do C12 má vzorec:
=COUNTIFS(OFFSET(C$5,0,0,ROW()-ROW(C$5)-1,1),"")
Poznámka: OFFSET je volatilná funkcia a môže spôsobiť problémy s výkonom vo veľkých alebo zložitých pracovných hárkoch.
S NEPRIAMYM a ADRESOU
Ďalším prístupom je použitie vzorca založeného na funkciách NEPRIAME a ADRESA. V takom prípade zostavíme rozsah ako text a potom použijeme INDIRECT na vyhodnotenie textu ako referencie. Vzorec v B11 by bol:
=COUNTIFS(INDIRECT(ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())),"")
Funkcia ADDRESS sa používa na zostavenie rozsahu ako je tento:
ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())
V prvom prípade ADRESY dodáme číslo riadku ako pevnú hodnotu 5 a číslo stĺpca poskytneme funkciou COLUMN:
=ADDRESS(5,COLUMN()) // returns "$B$5"
V druhom prípade dodáme „aktuálne“ číslo riadku mínus 2 a aktuálny stĺpec s funkciou COLUMN:
=ADDRESS(ROW()-2,COLUMN()) // returns "$B$9"
Po zreťazení týchto dvoch hodnôt dohromady máme:
"$B$5:$B$9" // as text
Toto je textový reťazec. Ak chcete previesť na platný odkaz, musíme použiť INDIRECT:
=INDIRECT("$B$5:$B$9") // returns $B$5:$B$9 as valid range
Nakoniec sa vzorec v B11 stáva:
=COUNTIFS($B$5:$B$9,"") // returns 4
Poznámka: INDIRECT je volatilná funkcia a môže spôsobiť problémy s výkonom vo veľkých alebo zložitých pracovných hárkoch.
