VZOREC PROGRAMU EXCEL: DYNAMICKÝ POMENOVANÝ ROZSAH S OFFSET

Generický vzorec | _+_ | Zhrnutie

Jeden zo spôsobov, ako vytvoriť dynamiku pomenovaný rozsah pomocou vzorca je použiť funkciu OFFSET spolu s funkciou COUNTA. Dynamické rozsahy sú známe aj ako rozširujúce sa rozsahy - automaticky sa rozširujú a sťahujú 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 pracovného hárka. S moderným počítačom a menšou množinou údajov by to nemalo spôsobovať problém, ale pri veľkých množinách údajov môžete vidieť nižší výkon. V takom prípade zvážte vytvorenie dynamického pomenovaného rozsahu pomocou funkcie INDEX.

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

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

Poznámka: tento vzorec má definovať a pomenovaný rozsah ktoré môžu byť použité v iných vzorcoch. Vysvetlenie

Tento vzorec používa funkciu OFFSET na generovanie rozsahu, ktorý sa rozširuje a sťahuje ú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ú posuny pre riadky a stĺpce a sú zadané ako nula.

ako vypočítať rýchlosť rastu v programe Excel

Posledné dva argumenty predstavujú výšku a šírku. Výška a šírka sa generujú za behu pomocou programu COUNTA, čo robí výslednú referenciu dynamickou.

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 údajoch a žiadne hodnoty nad B100. COUNTA vracia 6.

Pokiaľ ide o šírku, na počítanie neprázdnych hodnôt v rozsahu B5: Z5 používame funkciu COUNTA. To nepredpokladá žiadne bunky hlavičky a žiadne hlavičky mimo Z5. COUNTA vracia 6.

V tomto prípade vzorec vyzerá takto:

 = OFFSET (B5,0,0, COUNTA ($B:$B0), COUNTA ($B:$Z))

S týmito informáciami funkcia OFFSET vráti odkaz na B5: G10, ktorý zodpovedá rozsahu 6 riadkov a 6 stĺpcom naprieč.

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

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

Na výšku a šírku môžete použiť aj úplné odkazy na stĺpce a riadky takto:

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

Všimnite si toho, že výška sa upravuje pomocou -2, aby sa zohľadnili hodnoty hlavičky a názvu v bunkách B4 a B2. Výhodou tohto prístupu je jednoduchosť rozsahov vo vnútri COUNTA. Nevýhoda spočíva v obrovských veľkostiach stĺpcov a riadkov - je potrebné dbať na to, aby sa zabránilo chybným hodnotám mimo rozsah, pretože môžu ľahko vyhodiť počet.

Určenie posledného radu

V závislosti od štruktúry a obsahu údajov v pracovnom hárku existuje niekoľko spôsobov, ako určiť posledný riadok (poslednú relatívnu polohu) v súbore údajov: