Dynaamisen kaavioalueen luominen Excelissä

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

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

Mikä on dynaaminen kaavioalue?

Dynaaminen kaavioalue on tietoalue, joka päivittyy automaattisesti, kun muutat tietolähdettä.

Tätä dynaamista aluetta käytetään sitten kaavion lähdetiedona. Tietojen muuttuessa dynaaminen alue päivittyy välittömästi, mikä johtaa kaavion päivitykseen.

Alla on esimerkki kaaviosta, joka käyttää dynaamista kaavioaluetta.

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

Kuinka luoda dynaaminen kaavioalue Excelissä?

Dynaamisen kaavion voi luoda Excelissä kahdella tavalla:

• Excel -taulukon käyttäminen
• Kaavojen käyttäminen

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

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

Lataa esimerkkitiedosto napsauttamalla tätä.

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 Table -ominaisuus otettiin käyttöön Windowsin Excel 2007 -versiossa, ja jos sinulla on sitä edeltävät versiot, et voi käyttää sitä (katso seuraava osio dynaamisen kaavioalueen luomisesta kaavojen avulla).

Pro -vinkki: Jos haluat muuntaa solualueen Excel -taulukkoksi, valitse solut ja käytä pikanäppäintä - Control + T (pidä Control -näppäintä painettuna ja paina T -näppäintä).

Alla olevasta esimerkistä näet, että heti kun olen lisännyt uusia tietoja, Excel -taulukko laajenee sisällyttämään nämä tiedot osaksi taulukkoa (huomaa, että reunus ja muotoilu laajenevat sisällyttämään ne taulukkoon).

Nyt meidän on käytettävä tätä Excel -taulukkoa kaavioita luotaessa.

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

• Valitse koko Excel -taulukko.
• Siirry Lisää -välilehdelle.
• Valitse Kaaviot -ryhmässä "Viiva ja merkit" -kaavio.

Se siitä!

Yllä olevat vaiheet lisäisivät viivakaavion, joka päivittyy automaattisesti, kun lisäät 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 tyhjää tilaa oikealla. Voit korjata tämän poistamalla poistetut datapisteet taulukosta vetämällä Excel -taulukon oikeassa alakulmassa olevaa sinistä merkkiä (kuten alla).

Vaikka olen ottanut esimerkin viivakaaviosta, voit myös luoda muita kaaviotyyppejä, kuten sarake-/pylväskaavioita tällä tekniikalla.

Excel -kaavojen käyttäminen

Kuten mainitsin, Excel -taulukon käyttäminen on paras tapa luoda dynaamisia kaavioalueita.

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

Oletetaan, että sinulla on alla esitetyt tiedot:

Jotta voimme luoda dynaamisen kaavioalueen näistä tiedoista, meidän on:

1. Luo kaksi dynaamista nimettyä aluetta OFFSET -kaavan avulla (yksi arvoille ja kuukausille). Datapisteen lisääminen/poistaminen päivittää automaattisesti nimetyt alueet.
2. Lisää kaavio, joka käyttää nimettyjä alueita tietolähteenä.

Selitän nyt jokaisen vaiheen yksityiskohtaisesti.

Vaihe 1 - Dynaamisten nimettyjen alueiden luominen

Alla on vaiheet dynaamisten nimettyjen alueiden luomiseksi:

• Siirry Kaavat -välilehdelle.
• Napsauta "Nimenhallinta".
• Määritä Nimihallinta -valintaikkunassa nimi muodossa ChartValues ja kirjoita seuraava kaava kohtaan Viittaa osaan: = OFFSET (Kaava! $ B $ 2 ,,, COUNTIF (Kaava! $ B $ 2: $ B $ 100, ””))
• Napsauta OK.
• Napsauta Nimenhallinta -valintaikkunassa Uusi.
• Määritä Nimihallinta -valintaikkunassa nimi muodossa KaavioKuukausia ja kirjoita seuraava kaava kohtaan Viittaa osaan: = OFFSET (Kaava! $ A $ 2 ,,, COUNTIF (Kaava! $ A $ 2: $ A $ 100, ””))
• Napsauta OK.
• Napsauta Sulje.

Yllä olevat vaiheet ovat luoneet työkirjaan kaksi nimettyä aluetta - ChartValue ja ChartMonth (nämä viittaavat tietojoukon arvoihin ja kuukausialueeseen).

Jos päivität arvosarakkeen lisäämällä yhden datapisteen lisää, ChartValue -niminen alue päivittyy nyt automaattisesti näyttämään siinä olevat lisäpisteet.

Taikuuden tekee OFFSET -toiminto täällä.

ChartValue -nimisessä aluekaavassa olemme määrittäneet vertailupisteeksi B2. 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ä.

Voit lisätä kaavion ja käyttää dynaamisia kaavioalueita seuraavasti:

• Siirry Lisää -välilehdelle.
• Napsauta "Lisää viiva- tai aluekaavio" ja lisää "Viiva ja merkit" -kaavio. Tämä lisää kaavion laskentataulukkoon.
• Kun kaavio on valittu, siirry Suunnittelu -välilehdelle.
• Napsauta Valitse tiedot.
• Napsauta Valitse tietolähde -valintaikkunassa Lisää -painiketta kohdassa Legend Entries (Series).
• Kirjoita Sarja -arvo -kenttään = Kaava! ChartValues ​​(huomaa, että sinun on määritettävä laskentataulukon nimi ennen nimettyä aluetta, jotta tämä toimii).
• Napsauta OK.
• Napsauta Muokkaa -painiketta "Vaaka (luokka) -akselin tarroissa".
• Kirjoita Axis Labels -valintaikkunaan = Formula! ChartMonths
• Napsauta OK.

Se siitä! Kaaviosi käyttää nyt dynaamista aluetta ja päivittyy, kun lisäät/poistat datapisteitä kaavioon.

Muutama tärkeä tieto, kun käytät nimettyjä alueita kaavioiden kanssa:

• Kaavion tiedoissa ei saa olla tyhjiä soluja. Jos on tyhjä, nimetty alue ei viittaisi oikeaan tietojoukkoon (koska kokonaismäärä johtaisi siihen, että se viittaisi vähemmän soluihin).
• Sinun on noudatettava nimeämiskäytäntöä, kun käytät taulukon nimeä kaavion lähteessä. Jos esimerkiksi taulukon nimi on yksittäinen sana, kuten Kaava, voit käyttää = Kaava! ChartValue. Mutta jos on olemassa useampi kuin yksi sana, kuten kaavataulukko, sinun on käytettävä = ’kaavataulukko’! ChartValue.