Dynaamisen Excel-kaavion luonti

Edellisessä Kohdealueen kuvaaminen Excelissä –jutussa kerroin miten voit määrittää alueen sijainnin ja koon dynaamisesti OFFSET (SIIRTYMÄ) -funktiolla. Entä jos laskemisen lisäksi kaavion lähdealueen pitäisi muuttua?

Kerron tässä jutussa miten saat aikaan kaavion, joka näyttää halutun tuotteen myyntikäyrän halutulta aikajaksolta. Tämä pitää tehdä kiertoteitse aluenimen avulla, sillä Excelissä ei voi käyttää kaavaa kaavion lähdealueena.

Kokeile ensin alla olevassa esimerkkitaulukossa tuotenimen ja kuukausien lukumäärän muuttamista:

Kaavion ja dynaamisen aluenimen luonti

Muodosta ensin mallikaavio esimerkiksi ensimmäisestä tuoterivistä (kuvan kaavio on luotu alueesta A13:G14).

kaavion luonti

Jos olet muodostanut kaavan, joka laskee halutun alueen luvut yhteen, kopioi kaavasta talteen OFFSET-funktio. Tulet tarvitsemaan funktiota hetken kuluttua.

kopioi offset-funktio

Luo aluenimi, joka muodostuu kyseisen OFFSET-funktion avulla. Määrität aluenimen komennolla Formulas > Defined Names > Define Name > Define Name (Kaavat > Määritetyt nimet > Määritä nimi > Määritä nimi). Syötä alueelle nimi, jossa ei saa olla välilyöntejä ja kirjoita Refers to (Viittaus) –kenttään yhtäsuuruusmerkki ja liitä kopioimasi OFFSET-funktio kenttään. Muuta funktion kaikki soluviittaukset absoluuttisiksi, kuten $A$13. Kopioi aluenimi leikepöydälle, sillä tulet tarvitsemaan sitä hetken kuluttua. Hyväksy OK:lla.

määritä nimi

(Tässä on hyvä paikka sellaiselle pikkuvinkille, että punaisella nuolisymbolilla varustetuissa Excel-kentissä nuolinäppäimillä siirtyminenhän aiheuttaa ikävästi soluosoitteiden ilmestymistä kenttään. Jos siis aiot siirtyä nuolinäppäimillä Refers to –kentän sisällä, paina ensin F2, jottei kentän sisältö mene sekaisin.)

Nimen kytkeminen kaavion arvosarjaan

Valitse arvosarjan eli viivan päältä pikavalikosta Select Data (Valitse tiedot).

select data

Valitse vielä Edit (Muokkaa).muokkaa arvosarjaa

 

Määritä, että sarjan nimi tulee solusta, johon käyttäjä syöttää tuotteen ja että sarjan arvot määräytyvät juuri luomasi aluenimen (eli OFFSET-funktion) perusteella. Älä poista kummastakaan kentästä taulukon nimeä ja huutomerkkiä.

määritä aluenimi kaavion taustalle

Kaavio toimii, jos funktio on kunnossa absoluuttisine viittauksineen. Jos syötät taulukkoon kuukausien lukumääräksi 0, saat virheilmoituksen, sillä alue ei voi olla 0 saraketta leveä. Vaikka syöttäisit uuden sallitun luvun, kaavio ei heti päivity, vaan sinun pitää painaa F9, joka suorittaa kaavojen uudelleenlaskennan. Jutun alussa olevaan esimerkkitaulukkoon on luotu kelpoisuustarkistus (validation), ja soluun voi syöttää vain arvoja välillä 1 – 6.

Vastaa

Täytä tietosi alle tai klikkaa kuvaketta kirjautuaksesi sisään:

WordPress.com-logo

Olet kommentoimassa WordPress.com -tilin nimissä. Log Out / Muuta )

Twitter-kuva

Olet kommentoimassa Twitter -tilin nimissä. Log Out / Muuta )

Facebook-kuva

Olet kommentoimassa Facebook -tilin nimissä. Log Out / Muuta )

Google+ photo

Olet kommentoimassa Google+ -tilin nimissä. Log Out / Muuta )

Muodostetaan yhteyttä palveluun %s