souhrn
Ke konfiguraci COUNTIFS (nebo COUNTIF) s proměnným rozsahem můžete použít funkci OFFSET. V zobrazeném příkladu je vzorec v B11:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Tento vzorec počítá neprázdné buňky v rozsahu, který začíná na B5 a končí 2 řádky nad buňkou, kde vzorec žije. Stejný vzorec se zkopíruje a vloží 2 řádky pod poslední položku v datech, jak je znázorněno.
Vysvětlení
V zobrazeném příkladu je vzorec v B11:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Při práci zevnitř ven se práce s nastavením proměnného rozsahu provádí pomocí funkce OFFSET zde:
OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1) // variable range
OFFSET má pět argumentů a je konfigurován takto:
- reference = B $ 5, začněte v buňce B5, řádek uzamčen
- řádky = 0, posunutí nulových řádků od počáteční buňky
- cols = 0, posunutí nulové sloupce počáteční buňku
- height = ROW () - ROW (B $ 5) -1 = 5 řádků vysoký
- šířka = 1 sloupec široký
Chcete-li zjistit výšku rozsahu v řádcích, použijeme funkci ROW takto:
ROW()-ROW(B$5)-1 // work out height
Jelikož ROW () vrací číslo řádku „aktuální“ buňky (tj. Buňky, ve které žije vzorec), můžeme to zjednodušit takto:
=ROW()-ROW(B$5)-1 =11-5-1 =5
S výše uvedenou konfigurací funkce OFFSET vrací rozsah B5: B9 přímo do COUNTIFS:
=COUNTIFS(B5:B9,"") // returns 4
Všimněte si, že odkaz na B $ 5 ve výše uvedeném vzorci je smíšený odkaz s relativním sloupcem a řádkem uzamčeným. To umožňuje zkopírovat vzorec do jiného sloupce a stále fungovat. Například po zkopírování do C12 je vzorec:
=COUNTIFS(OFFSET(C$5,0,0,ROW()-ROW(C$5)-1,1),"")
Poznámka: OFFSET je nestálá funkce a může způsobit problémy s výkonem u velkých nebo složitých listů.
S NEPŘÍMÝM a ADRESOU
Dalším přístupem je použití vzorce založeného na funkcích NEPŘÍMÝ a ADRESA. V tomto případě sestavíme rozsah jako text a poté použijeme INDIRECT k vyhodnocení textu jako odkazu. Vzorec v B11 by byl:
=COUNTIFS(INDIRECT(ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())),"")
Funkce ADDRESS se používá ke konstrukci rozsahu, jako je tento:
ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())
V první instanci ADRESY dodáváme číslo řádku jako pevně zakódovanou hodnotu 5 a číslo sloupce poskytujeme funkcí COLUMN:
=ADDRESS(5,COLUMN()) // returns "$B$5"
V druhém případě dodáme "aktuální" číslo řádku minus 2 a aktuální sloupec s funkcí COLUMN:
=ADDRESS(ROW()-2,COLUMN()) // returns "$B$9"
Po zřetězení těchto dvou hodnot dohromady máme:
"$B$5:$B$9" // as text
Toto je textový řetězec. Chcete-li převést na platný odkaz, musíme použít INDIRECT:
=INDIRECT("$B$5:$B$9") // returns $B$5:$B$9 as valid range
Nakonec se vzorec v B11 stává:
=COUNTIFS($B$5:$B$9,"") // returns 4
Poznámka: INDIRECT je nestálá funkce a může způsobit problémy s výkonem u velkých nebo složitých listů.
