Dynaamiset kaaviot, joissa käytetään Excelin OFFSET-funktiota ja alueitten nimiä, muuttavat kokoa, kun tietoja lisätään tai poistetaan. Toisin kuin taulukoiden käyttäminen dynaamisiin kaavioihin, tämä menetelmä on taaksepäin yhteensopiva Excelin varhaisempien versioiden kanssa.
Käytä Excelin OFFSET- ja COUNTA-funktioita luodaksesi dynaamisia alueiden nimiä, jotka mukautuvat automaattisesti, kun tietoja lisätään tai poistetaan.
Klikkaa ladataksesi opetusesimerkkitiedoston, jossa näytetään, kuinka dynaamiset kaaviot muuttavat kokoa OFFSET- ja alueiden nimiä käyttäen. Esimerkki sisältää sekä vaaka- että pystysuuntaista dataa.
OFFSET- ja alueen nimi-menetelmä vaatii enemmän työtä kuin taulukossa käytettävät kaksi napsautusta, mutta sen avulla voit hallita kaikkea kaavion dataa, kuten kirjassani ”Balanced Scorecards and Operational Dashboards with Microsoft Excel” osoitetaan. Tällä CriticalToSuccess.com-sivustolla on esimerkkejä muista OFFSETin käyttötavoista, esimerkiksi vierityskaavion luomisesta, kaavioiden ohjaamisesta pudotusvalikoilla ja muusta. Etsi CriticalToSuccess.com-sivustolta hakusanalla ”OFFSET” tai ”dynaaminen alue” nähdäksesi esimerkkejä.
Staattisen kaavion luominen
Aloita dynaaminen kaavio luomalla ensin staattinen kaavio seuraavasti,
1. Napsauta alueen B7:D12 sisällä, jotta Excel tietää, missä kaavion tiedot sijaitsevat.
2. Valitse Lisää-välilehdeltä Kaaviot-ryhmästä yksinkertainen kaavio, kuten pylväs-, pylväs- tai viivakaavio.
Siirrä tätä kaaviota niin, että sinulla on tilaa lisätä dataa data-alueen alapuolelle.
Dynaamisten alueiden nimien luominen OFFSET:llä
Escelin eri versioissa on erilaiset menetelmät alueiden nimikaavojen luomiseksi ja muokkaamiseksi. Seuraavat ohjeet koskevat Excel 2007, 2010 ja 2013.
1. Napsauta Kaavat-välilehden Määritelty nimi -ryhmän Määritä nimi -painiketta. Määrittele nimi -ruutu tulee näkyviin.
Luo alueen nimet Määrittele nimi -ruutuun.
2. Kirjoita Nimi-tekstiruutuun rngVDate. Nimeämiskäytäntö on,
rng Alueen nimi
V Tietojen pystysuunta B7:B16
Date Alueen tietotyyppi
3. Kirjoita kommentti-tekstikenttään kommentti, joka auttaa sinua tai muita tietämään, mitä varten tämä alueen nimi on.
4. Kirjoita Refers to -kenttään OFFSET-kaava, joka laskee Date-tietoalueen.
=OFFSET(wrksht!$B$7,0,0,0,COUNTA(wrksht!$B$7:$B$16),1)
5. Kirjoita OFFSET-kaava, joka laskee Date-tietoalueen. Napsauta Ok.
Tämän OFFSET-kaavan avulla voit syöttää uusia päivämääriä minne tahansa alueella B7:B16, kunhan uudet päivämäärät ovat vanhan päivämääräluettelon alareunassa eikä päivämäärien välissä ole tyhjiä soluja.
Toista nyt sama prosessi luodaksesi alueiden nimet Myynti- ja Yksikkötiedoille.
Lisäämäsi nimet ja kaavat ovat,
tietoalueen nimi viittaa
Myynti rngVMyynti =OFFSET(wrksht!$C$7,0,0,0,COUNTA(wrksht!$C$7:$C$16),1)
Yksiköt rngVYksiköt =OFFSET(wrksht!$D$7,0,0,COUNTA(wrksht!$D$7:$D$16),1)
Klikkaa tästä, jos haluat tietää, miten OFFSET laskee dynaamisen alueen nimen koon. Tässä artikkelissa annetaan myös vinkkejä dynaamisten alueiden nimien tarkistamiseen.
Klikkaa tästä oppiaksesi virheen, jota et koskaan halua tehdä OFFSETin kanssa. Teetkö sinä tämän virheen?
Klikkaa ladataksesi opetusnäytetiedoston, jossa käsitellään kaavioiden dynaamista koon muuttamista OFFSETin ja alueiden nimien avulla. Esimerkki sisältää sekä vaaka- että pystysuuntaista dataa.
Alueen nimen lisääminen kaavion päivämäärän kaavaan
Nyt voit tehdä kaavioistasi dynaamisia lisäämällä luomasi alueen nimet kaavioihin, joita kaavioissa käytetään dataan viittaamiseen. Älä ole huolissasi. Tämä on hyvin suoraviivaista. Itse asiassa tämän oppiminen antaa sinulle mystisiä kykyjä ja voiman taivuttaa mikä tahansa kaavio tahtosi mukaan. (Sen avulla voit myös korjata kaaviot, jotka menevät sekaisin.)
Loit edellä kolme dynaamisen alueen nimeä, jotka viittaavat kaavion käyttämiin tietoihin,
rngVDate
rngVSales
rngVUnits
Seuraavassa korvataan kaavion käyttämät soluviittaukset luomillasi dynaamisilla alueen nimillä,
1. Kaavaa on vaikea nähdä tässä valintaikkunassa, joten napsauta soluviitteen oikealla puolella olevaa valintakuvaketta. Tämä tuo näkyviin laajemman Axis Labels -ruudun, jossa voit viitata kaavaan. OLE VAROVAINEN ennen muokkaamista!
Axis Labels -ruutu näyttää vaaka-akselin viittauksen.
4. OLE VAROVAINEN! Ennen kuin yrität muokata kaavaa, napsauta kaavan sisällä ja paina Muokkaa-näppäintä, F2, jotta voit muokata kaavaa. Paina End-näppäintä siirtyäksesi kaavan loppuun. Korvaa !-merkin jälkeiset soluviittaukset alueen nimellä, kuten tässä kuvassa näkyy.
Kaavasi pitäisi nyt näyttää seuraavalta,
=’Dynamically Resize Charts to Data.xlsx’!rngVDate
Axis Labels -ruutu näyttää tältä sen jälkeen, kun olet painanut F2-näppäintä ja muokannut kaavaa niin, että se sisältää Date-alueen nimen.
VINKKI: Jos teet virheen kaavaa muokatessasi, paina näppäinyhdistelmää Ctrl+Z (Undo), palataksesi takaisin.
5. Palaa Valitse tietolähde -valintaikkunaan Valitse tietolähde -valintaikkunaan napsauttamalla Ok, jotta voit lisätä Myynnin ja Yksiköiden nimet.
Välialueen nimen lisääminen kaavion Myynnin ja Yksiköiden kaavaan
Vaihda nyt Myynti- ja Yksiköt-akselien kaavat alueen nimiin käyttämällä lähes samaa prosessia.
1. Napsauta Valitse tietolähde -valintaikkunassa Myynti ja Muokkaa-painiketta saadaksesi näkyviin Muokkaa sarjaa -valintaikkunan.
Korvaa Myyntitiedot-soluviittaus sen alueen nimellä.
2. Napsauta valintakuvaketta Myynti-sarjaan viittaavan Sarjan arvo -kaavan oikealla puolella.
3. Napsauta kaavan sisällä, paina F2 muokataksesi ja vaihda Salesdatan soluviittaukset sarjan nimeen, rngVSales.
Kaavio käyttää nyt dynaamista sarjanimeä Salesdatan viittaamiseen.