Dynaamisen kaavioalueen luominen Excelissä

Kun luot kaavion Excelissä ja lähdetiedot muuttuvat, sinun on päivitettävä kaavion tietolähde, jotta se vastaa uusia tietoja.

Jos työskentelet usein päivitettävien kaavioiden kanssa, kannattaa luoda dynaaminen kaavioalue.

Mikä on dynaaminen kaavioalue?

Dynaaminen kaavioalue on data-alue, joka päivittyy automaattisesti, kun tietolähdettä muutetaan.

Tätä dynaamista aluetta käytetään sitten kaavion lähdetietona. Kun tiedot muuttuvat, dynaaminen alue päivittyy välittömästi, mikä johtaa kaavion päivitykseen.

Alhaalla on esimerkki kaaviosta, jossa käytetään dynaamista kaavioaluetta.

Huomaa, että kaavio päivittyy uusilla touko- ja kesäkuun datapisteillä heti, kun tiedot on syötetty.

Miten luodaan dynaaminen kaavioalue Excelissä?

On kaksi tapaa luoda dynaaminen kaavioalue Excelissä:

• Käyttämällä Excel-taulukkoa
• Käyttämällä kaavoja

Useimmissa tapauksissa Excel-taulukon käyttäminen on paras tapa luoda dynaamisia alueita Excelissä.

Katsotaanpa, miten kumpikin näistä menetelmistä toimii.

Klikkaamalla tästä pääset latailemaan esimerkkitiedoston.

Excel-taulukon käyttäminen

Excel-taulukon käyttäminen on paras tapa luoda dynaamisia alueita, koska se päivittyy automaattisesti, kun siihen lisätään uusi datapiste.

Excel-taulukko-ominaisuus otettiin käyttöön Excelin 2007-versiossa Windowsissa, ja jos käytössäsi on sitä aikaisemmat versiot, et pysty käyttämään tätä ominaisuutta (ks. seuraava osio dynaamisten kaavioalueiden luomisesta kaavojen avulla).

Alla olevassa esimerkissä näet, että heti kun lisään uusia tietoja, Excel-taulukko laajenee sisällyttämään nämä tiedot osaksi taulukkoa (huomaa, että rajaus ja muotoilu laajenevat sisällyttämään ne taulukkoon).

Nyt tätä Excel-taulukkoa on käytettävä luotaessamme taulukoita.

Tässä ovat tarkat vaiheet dynaamisen viivakaavion luomiseksi Excel-taulukon avulla:

• Valitse koko Excel-taulukko.
• Mene Lisää-välilehdelle.
• Valitse Kaaviot-ryhmässä ’Viiva, jossa on merkkejä’ -kaavio.

Se on siinä!

Yllä olevat vaiheet lisäisivät viivakaavion, joka päivittyisi automaattisesti, kun lisäät uusia tietoja Excel-taulukkoon.

Huomaa, että vaikka uusien tietojen lisääminen päivittää kaavion automaattisesti, tietojen poistaminen ei poista datapisteitä kokonaan. Jos esimerkiksi poistat 2 datapistettä, kaaviossa näkyy oikealla hieman tyhjää tilaa. Voit korjata tämän vetämällä Excel-taulukon oikeassa alareunassa olevaa sinistä merkkiä poistaaksesi poistetut datapisteet taulukosta (kuten alla näkyy).

Olen ottanut esimerkkinä viivakaavion, mutta voit luoda myös muita kaaviotyyppejä, kuten pylväs- tai pylväskaavioita, käyttämällä tätä tekniikkaa.

Excelin kaavojen käyttäminen

Kuten mainitsin, Excel-taulukkoa käyttäminen on paras keino dynaamisten kaaviokuvioalueiden luomiseen.

Jos et kuitenkaan jostain syystä voi käyttää Excel-taulukkoa (mahdollisesti jos käytät Excel 2003:a), on olemassa toinen (hieman monimutkainen) tapa luoda dynaamisia kaavioalueita käyttämällä Excel-kaavoja ja nimettyjä alueita.

Esitellään, että sinulla on alla olevan kaltainen tietokokonaisuus:

Luoaksemme dynaamisen kaavioalueen näistä tiedoista, meidän on:

1. Luoaksemme kaksi dynaamista nimettyä aluetta OFFSET-kaavalla (yksi kummallekin sarakkeelle ’Arvot’ ja ’Kuukaudet’). Tietopisteen lisääminen/poistaminen päivittäisi nämä nimetyt alueet automaattisesti.
2. Sisällytä kaavio, joka käyttää nimettyjä alueita tietolähteenä.

Selitän nyt jokaisen vaiheen yksityiskohtaisesti.

Vaihe 1 – Dynaamisten nimettyjen alueiden luominen

Alhaalla on vaiheet dynaamisten nimettyjen alueiden luomiseksi:

• Mene välilehdelle ’Kaavat’.
• Klikkaa ’Nimenhallinta’.
• Nimenhallinta-valintaikkunassa määritä nimeksi ChartValues ja kirjoita seuraava kaava kohtaan Viittaa osaan: =OFFSET(Kaava!$B$2,,,COUNTIF(Formula!$B$2:$B$100,”<>”))
• Klikkaa OK.
• Klikkaa Name Manager -valintaikkunassa New.
• Määritä Name Manager -valintaikkunassa nimeksi ChartMonths ja kirjoita Refers to -osaan seuraava kaava: =OFFSET(Formula!$A$2,,,COUNTIF(Formula!$A$2:$A$100,”<>”))
• Klikkaa Ok.
• Klikkaa Sulje.

Yllä olevilla vaiheilla on luotu työkirjaan kaksi nimettyä aluetta – ChartValue ja ChartMonth (nämä viittaavat vastaavasti tietokannan arvojen ja kuukausien alueeseen).

Jos menet päivittämään arvosaraketta lisäämällä yhden datapisteen lisää, ChartValue-niminen alue päivittyy nyt automaattisesti näyttämään ylimääräisen datapisteen siinä.

Taidon tekee tässä OFFSET-funktio.

’ChartValue’-nimisen alueen kaavassa olemme määritelleet B2:n vertailupisteeksi. OFFSET-kaava alkaa sieltä ja ulottuu kattamaan kaikki sarakkeen täytetyt solut.

Sama logiikka toimii myös ChartMonth-nimisen alueen kaavassa.

Vaihe 2 – Luo kaavio käyttämällä näitä nimettyjä alueita

Nyt sinun tarvitsee vain lisätä kaavio, joka käyttää nimettyjä alueita tietolähteenä.

Tässä ovat vaiheet kaavion lisäämiseksi ja dynaamisten kaavioalueiden käyttämiseksi:

• Mene Lisää-välilehdelle.
• Klikkaa ’Lisää viiva- tai aluekaavio’ -painiketta ja lisää ’Viiva, jossa on merkkejä’ -kaavio. Tämä lisää kaavion laskentataulukkoon.
• Kun kaavio on valittuna, siirry Suunnittelu-välilehdelle.
• Klikkaa Valitse tiedot.
• Klikkaa ’Valitse tietolähde’ -valintaikkunassa Lisää-painiketta kohdassa ’Legenda-merkinnät (sarjat)’.
• Kirjoita Sarjan arvokenttä-kenttään merkinnäksi: =Muoto!ChartValues (huomaa, että sinun on määritettävä työarkin nimi ennen nimettyä aluetta, jotta tämä toimisi).
• Klikkaa OK.
• Klikkaa Muokkaa-painiketta ’Horisontaalisten (luokka) akselien merkinnät’ -kentässä.
• Kirjoita ’Axis Labels’ -valintaikkunaan =Formula!ChartMonths
• Klikkaa Ok.

Se on siinä! Nyt kaaviosi käyttää dynaamista aluetta ja päivittyy, kun lisäät/poistat datapisteitä kaaviossa.

Muutama tärkeä asia, joka on hyvä tietää, kun käytät nimettyjä alueita kaavioissa:

• Kaavion datassa ei saisi olla tyhjiä soluja. Jos tyhjiä on, nimetty alue ei viittaisi oikeaan datasettiin (koska kokonaislukumäärä johtaisi siihen, että se viittaisi pienempään määrään soluja).
• Tulee noudattaa nimeämiskonventiota, kun käytät arkin nimeä kaavion lähteessä. Jos esimerkiksi arkin nimi on yksi sana, kuten Formula, voit käyttää =Formula!ChartValue. Mutta jos sanoja on enemmän kuin yksi, kuten Formula Chart, sinun on käytettävä =’Formula Chart’!ChartValue.

Saatat pitää myös seuraavista Excel-oppaista:

• Lämpömittarikaavion luominen Excelissä.
• Kellokäyrän tekeminen Excelissä.
• Vaihekaavion luominen Excelissä.
• Pareto-kaavion luominen Excelissä.
• Histogrammin tekeminen Excelissä
• Virhepalkkien lisääminen Excelissä (vaaka-/pysty-/sovitettu)