EXCEL FORMULA: DINAMIČNO IMENOVANO OBMOČJE Z OFFSET

Splošna formula | _+_ | Povzetek

Eden od načinov za ustvarjanje dinamike imenovani obseg s formulo je uporabiti funkcijo 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 nestanovitna funkcija, kar pomeni, da se pri vsaki spremembi delovnega lista preračuna. S sodobnim strojem in manjšim naborom podatkov to ne bi smelo povzročiti težav, vendar boste pri velikih naborih podatkov morda opazili počasnejše delovanje. V tem primeru razmislite o izgradnji dinamičnega poimenovanega obsega s funkcijo INDEX.

V prikazanem primeru je formula za dinamični razpon:

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

Opomba: ta formula je namenjena opredelitvi a imenovani obseg ki se lahko uporabijo v drugih formulah. Pojasnilo

Ta formula uporablja funkcijo OFFSET za ustvarjanje obsega, ki se razširi in skrči s prilagajanjem višine in širine glede na število praznih celic.

Prvi argument v OFFSET predstavlja prvo celico v podatkih (izvor), ki je v tem primeru celica B5. Naslednja dva argumenta sta odmika za vrstice in stolpce in sta podana kot nič.

kako izračunati stopnjo rasti na

Zadnja dva argumenta predstavljata višino in širino. Višina in širina se ustvarjata z uporabo COUNTA, zaradi česar je nastala referenčna dinamika.

Za višino uporabljamo funkcijo COUNTA za štetje praznih vrednosti v območju B5: B100. To ne predvideva praznih vrednosti v podatkih in nobenih vrednosti nad B100. COUNTA vrne 6.

Za širino uporabljamo funkcijo COUNTA za štetje praznih vrednosti v območju B5: Z5. To ne predvideva nobenih celic glave in nobenih glav nad Z5. COUNTA vrne 6.

Na tej točki formula izgleda tako:

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

S temi informacijami OFFSET vrne sklic na B5: G10, kar ustreza obsegu višine 6 vrstic s 6 stolpci čez.

Opomba: Obsege, ki se uporabljajo za višino in širino, je treba prilagoditi glede na postavitev delovnega lista.

Različica s polnimi referencami stolpcev/vrstic

Za višino in širino lahko uporabite tudi celotne reference stolpcev in vrstic:

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

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 razponov znotraj COUNTA. Pomanjkljivost izhaja iz ogromnih polnih stolpcev in vrstic - paziti je treba, da se napačne vrednosti preprečijo izven obsega, saj lahko zlahka zavržejo štetje.