Excel-kaava: Dynaaminen nimetty alue, jossa OFFSET -

Yleinen kaava

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

Yhteenveto

Yksi tapa luoda dynaaminen nimetty alue kaavalla on käyttää OFFSET-toimintoa yhdessä COUNTA-toiminnon kanssa. Dynaamisia alueita kutsutaan myös laajeneviksi alueiksi - ne laajenevat ja supistuvat automaattisesti uusien tai poistettujen tietojen mukauttamiseksi.

Huomaa: OFFSET on haihtuva toiminto, mikä tarkoittaa, että se laskee uudelleen jokaisen laskentataulukon muutoksen kanssa. Nykyaikaisella koneella ja pienemmällä tietojoukolla tämän ei pitäisi aiheuttaa ongelmia, mutta saatat nähdä hitaampaa suorituskykyä suurissa tietojoukoissa. Harkitse siinä tapauksessa dynaamisen nimellisen alueen rakentamista INDEX-funktiolla.

Esitetyssä esimerkissä dynaamiselle alueelle käytetty kaava on:

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

Selitys

Tämä kaava käyttää OFFSET-funktiota alueen laajentamiseen, joka laajenee ja supistuu säätämällä korkeutta ja leveyttä ei-tyhjien solujen määrän perusteella.

OFFSETin ensimmäinen argumentti edustaa datan ensimmäistä solua (alkuperä), joka tässä tapauksessa on solu B5. Seuraavat kaksi argumenttia ovat rivien ja sarakkeiden siirtymät, ja ne toimitetaan nollana.

Kaksi viimeistä argumenttia edustavat korkeutta ja leveyttä. Korkeus ja leveys luodaan lennossa COUNTA-toiminnolla, mikä tekee tuloksena olevan vertailun dynaamisen.

Korkeuden osalta käytämme COUNTA-funktiota laskemaan ei-tyhjät arvot alueella B5: B100. Tällöin datassa ei ole tyhjiä arvoja eikä arvoja B100 ylittäviä arvoja. COUNTA palauttaa arvon 6.

Leveyden osalta käytämme COUNTA-funktiota laskemaan ei-tyhjät arvot alueella B5: Z5. Tämä ei oleta otsikkosoluja eikä otsikoita Z5: n jälkeen. COUNTA palauttaa arvon 6.

Tässä vaiheessa kaava näyttää tältä:

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

Näiden tietojen avulla OFFSET palauttaa viitteen kohtaan B5: G10, joka vastaa 6 rivin korkeutta ja 6 saraketta.

Huomaa: Korkeuden ja leveyden alueet tulisi säätää vastaamaan laskentataulukon asettelua.

Muunnos täydellisillä sarake- / riviviitteillä

Voit käyttää myös kokonaisia sarake- ja riviviittauksia korkeuden ja leveyden suhteen:

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

Huomaa, että korkeutta säädetään arvolla -2, jotta otetaan huomioon otsikko- ja otsikkoarvot soluissa B4 ja B2. Tämän lähestymistavan etuna on alueiden yksinkertaisuus COUNTAn sisällä. Haittana on valtavat kokonaiset sarakkeet ja rivit - on huolehdittava siitä, että väärät arvot eivät kuulu alueen ulkopuolelle, koska ne voivat helposti laskea pois laskennan.