Splošna formula
=OFFSET(origin,0,0,COUNTA(range),COUNTA(range))
Povzetek
Eden od načinov za ustvarjanje dinamičnega imenovanega obsega s formulo je uporaba funkcije OFFSET skupaj s funkcijo COUNTA. Dinamični obsegi so znani tudi kot razširljivi obsegi - samodejno se razširijo in skrčijo, da sprejmejo nove ali izbrisane podatke.
Opomba: OFFSET je volatilna funkcija, kar pomeni, da se preračuna z vsako spremembo na delovnem listu. S sodobnim računalnikom in manjšim naborom podatkov to ne bi smelo povzročati težav, vendar boste morda opazili počasnejše delovanje velikih naborov podatkov. V tem primeru razmislite o gradnji dinamičnega imenovanega obsega s funkcijo INDEX.
V prikazanem primeru je formula, uporabljena za dinamični razpon:
=OFFSET(B5,0,0,COUNTA($B$5:$B$100),COUNTA($B$4:$Z$4))
Pojasnilo
Ta formula uporablja funkcijo OFFSET za ustvarjanje obsega, ki se širi in krči s prilagajanjem višine in širine na podlagi števila nepraznih celic.
Prvi argument v OFFSET predstavlja prvo celico v podatkih (izvor), ki je v tem primeru celica B5. Naslednja dva argumenta sta odmik za vrstice in stolpce in sta na voljo kot nič.
Zadnja dva argumenta predstavljata višino in širino. Višina in širina se sproti generirata z uporabo COUNTA, zaradi česar nastala referenčna dinamika postane dinamična.
Za višino uporabljamo funkcijo COUNTA za štetje praznih vrednosti v območju B5: B100. To ne predvideva praznih vrednosti v podatkih in vrednosti nad B100. COUNTA se vrne 6.
Za širino uporabljamo funkcijo COUNTA za štetje praznih vrednosti v območju B5: Z5. To ne predvideva nobene celice glave in nobene glave razen Z5. COUNTA se vrne 6.
V tem trenutku je formula videti takole:
=OFFSET(B5,0,0,6,6)
S temi informacijami OFFSET vrne sklic na B5: G10, kar ustreza obsegu 6 vrstic in 6 stolpcev.
Opomba: Območja, uporabljena za višino in širino, je treba prilagoditi postavitvi delovnega lista.
Različica s polnimi referencami stolpcev / vrstic
Za višino in širino lahko uporabite tudi polne reference stolpcev in vrstic, takole:
=OFFSET($B$5,0,0,COUNTA($B:$B)-2,COUNTA($4:$4))
Upoštevajte, da se višina prilagaja z -2, da se upoštevajo vrednosti glave in naslova v celicah B4 in B2. Prednost tega pristopa je preprostost obsegov znotraj COUNTA. Pomanjkljivost izhaja iz ogromnih polnih stolpcev in vrstic - paziti je treba, da preprečimo napačne vrednosti zunaj obsega, saj lahko zlahka zavrnejo štetje.
Določitev zadnje vrstice
Odvisno od strukture in vsebine podatkov na delovnem listu obstaja več načinov za določitev zadnje vrstice (zadnjega relativnega položaja) v nizu podatkov:
- Zadnja vrstica v mešanih podatkih s praznimi mesti
- Zadnja vrstica v mešanih podatkih brez praznih prostorov
- Zadnja vrstica v besedilnih podatkih
- Zadnja vrstica v številskih podatkih
