Excel vzorec: Dynamický pomenovaný rozsah s OFSETOM -

Všeobecný vzorec

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

Zhrnutie

Jedným zo spôsobov, ako vytvoriť dynamický pomenovaný rozsah pomocou vzorca, je použitie funkcie OFFSET spolu s funkciou COUNTA. Dynamické rozsahy sú tiež známe ako rozširujúce sa rozsahy - automaticky sa rozširujú a zmršťujú tak, aby vyhovovali novým alebo odstráneným údajom.

Poznámka: OFFSET je volatilná funkcia, čo znamená, že sa prepočítava pri každej zmene v pracovnom hárku. S moderným strojom a menšou množinou údajov by to nemalo spôsobiť problém, ale pri veľkých množinách údajov môžete zaznamenať pomalší výkon. V takom prípade zvážte vytvorenie dynamického pomenovaného rozsahu pomocou funkcie INDEX.

V zobrazenom príklade je vzorec použitý pre dynamický rozsah:

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

Vysvetlenie

Tento vzorec využíva funkciu OFFSET na generovanie rozsahu, ktorý sa rozširuje a zmršťuje úpravou výšky a šírky na základe počtu neprázdnych buniek.

Prvý argument v OFFSET predstavuje prvú bunku v dátach (pôvod), ktorou je v tomto prípade bunka B5. Nasledujúce dva argumenty sú kompenzáciami pre riadky a stĺpce a sú dodávané ako nula.

Posledné dva argumenty predstavujú výšku a šírku. Výška a šírka sa generujú za behu pomocou programu COUNTA, vďaka čomu je výsledná referencia dynamická.

Pre výšku používame funkciu COUNTA na počítanie neprázdnych hodnôt v rozsahu B5: B100. To nepredpokladá žiadne prázdne hodnoty v dátach a žiadne hodnoty nad B100. COUNTA vracia 6.

Pre šírku použijeme funkciu COUNTA na spočítanie neprázdnych hodnôt v rozsahu B5: Z5. To nepredpokladá žiadne bunky hlavičky a žiadne hlavičky nad rámec Z5. COUNTA vracia 6.

V tomto okamihu vyzerá vzorec takto:

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

S touto informáciou funkcia OFFSET vráti odkaz na B5: G10, čo zodpovedá rozsahu 6 riadkov a 6 stĺpcov.

Poznámka: Rozsahy použité pre výšku a šírku by sa mali upraviť tak, aby zodpovedali rozloženiu pracovného hárka.

Variácia s úplnými odkazmi na stĺpce / riadky

Môžete tiež použiť úplné odkazy na stĺpce a riadky pre výšku a šírku, napríklad takto:

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

Upozorňujeme, že výška sa upravuje pomocou -2, aby sa zohľadnili hodnoty hlavičiek a nadpisov v bunkách B4 a B2. Výhodou tohto prístupu je jednoduchosť rozsahov vo vnútri COUNTA. Nevýhoda pochádza z obrovských rozmerov plných stĺpcov a riadkov - je potrebné dbať na to, aby sa zabránilo chybným hodnotám mimo rozsahu, pretože môžu ľahko vyhodiť počet.

Určenie posledného riadku

Existuje niekoľko spôsobov, ako určiť posledný riadok (posledná relatívna poloha) v množine údajov, v závislosti od štruktúry a obsahu údajov v hárku: