Excel vzorec: Dynamický pojmenovaný rozsah s OFFSET -

Obecný vzorec

=OFFSET(origin,0,0,COUNTA(range),COUNTA(range))

souhrn

Jedním ze způsobů, jak vytvořit dynamický pojmenovaný rozsah pomocí vzorce, je použití funkce OFFSET společně s funkcí COUNTA. Dynamické rozsahy jsou také známé jako rozšiřující se rozsahy - automaticky se rozšiřují a smršťují, aby vyhovovaly novým nebo odstraněným datům.

Poznámka: OFFSET je volatilní funkce, což znamená, že se přepočítává s každou změnou v listu. S moderním strojem a menší datovou sadou by to nemělo způsobit problém, ale u velkých datových sad můžete vidět pomalejší výkon. V takovém případě zvažte vytvoření dynamického pojmenovaného rozsahu pomocí funkce INDEX.

V zobrazeném příkladu je vzorec použitý pro dynamický rozsah:

=OFFSET(B5,0,0,COUNTA($B$5:$B$100),COUNTA($B$4:$Z$4))

Vysvětlení

Tento vzorec používá funkci OFFSET ke generování rozsahu, který se rozšiřuje a smršťuje úpravou výšky a šířky na základě počtu neprázdných buněk.

První argument v OFSETU představuje první buňku v datech (počátek), což je v tomto případě buňka B5. Další dva argumenty jsou posuny řádků a sloupců a jsou dodávány jako nula.

Poslední dva argumenty představují výšku a šířku. Výška a šířka jsou generovány za běhu pomocí COUNTA, díky čemuž je výsledná reference dynamická.

Pro výšku používáme funkci COUNTA k počítání neprázdných hodnot v rozsahu B5: B100. To nepředpokládá žádné prázdné hodnoty v datech a žádné hodnoty nad B100. COUNTA vrátí 6.

Pro šířku používáme funkci COUNTA k počítání neprázdných hodnot v rozsahu B5: Z5. To nepředpokládá žádné buňky záhlaví a žádné záhlaví nad Z5. COUNTA vrátí 6.

V tomto okamžiku vypadá vzorec takto:

=OFFSET(B5,0,0,6,6)

S touto informací OFFSET vrací odkaz na B5: G10, což odpovídá rozsahu výšky 6 řádků o 6 sloupců napříč.

Poznámka: Rozsahy použité pro výšku a šířku by měly být upraveny tak, aby odpovídaly rozložení listu.

Varianta s úplnými odkazy na sloupce / řádky

Můžete také použít úplné odkazy na sloupce a řádky pro výšku a šířku takto:

=OFFSET($B$5,0,0,COUNTA($B:$B)-2,COUNTA($4:$4))

Všimněte si, že výška se upravuje pomocí -2, aby se zohlednily hodnoty záhlaví a názvu v buňkách B4 a B2. Výhodou tohoto přístupu je jednoduchost rozsahů uvnitř COUNTA. Nevýhoda pochází z obrovských velikostí plných sloupců a řádků - je třeba dbát na to, aby se zabránilo chybným hodnotám mimo rozsah, protože mohou snadno vyhodit počet.