Tag Archives: laske.a

Dynaamiset nimet 2

Leave a reply

Dynaamisille asioille riittää paljon eri soveltuvuus aloja tässä yksi esimerkki lisää.

Valitaan listaruudusta osasto, tuote tai jokin muu ehto ja sen perusteella tuodaan lähdetiedoista tietoja.

Listaruudun tekoon käytän tässä esimerkissä toimintoa Tietojen kelpoisuuden tarkistaminen joka on Datatyökaluryhmässä Tiedot valintanauhalla. (Data,Data tools, Data Validation).

Esimerkki:

Esimerkki nimen käytöstä

Esimerkki nimen käytöstä

Esimerkissä olen laittanut kaikki valittavissa olevat maat omaan tauluun ja nimennyt sen parametrit.

Tämän jälkeen olen määrittänyt nimen Maat jonka viittaus on:
=SIIRTYMÄ(Parametrit!$A$1;1;0;LASKE.A(Parametrit!$A:$A)-1)

SIIRTYMÄ (OFFSET), LAKE.A (COUNTA)

Nimi määritellään kohdasta Kaavat, Määritetyt nimet, Määritä nimi (Formulas, Defined Names, Define Name).

Annetaan Nimi -ruutuun kuvaava nimi, Laajuus on työkirja ja viittaukseen yllä mainittu kaava.
Olen laittanut kaavaan -1 koska nimisarakkeessa on otsikko ja sitä en halua mukaan listaan.
Tämän jälkeen olen laittanut soluun B3 kelpoisuustarkistuksen joka näyttää luettelon.
Lähteeksi olen laittanut nimen jonka loin aiemmin.

Huomaa että lähteen luettelo alkaa = -merkillä.

Kelpoisuustarkistus

Kelpoisuustarkistus

Tämän jälkeen rakennetaan kaavat jotka hakee tai poimii tietoja valitun arvon mukaan.
Esimerkkiin olen laittanut esimerkinomaisesti seuraavat kaavat:
=LASKE.JOS(C11:C2165;B3) (COUNTIF)
=SUMMA.JOS(C11:C2165;B3;L11:L2165) (SUMIF)

Jos haluat poimia monta tietoa samasta lähteestä, samalta riviltä harkitse VASTINE (MATCH) ja INDEKSI (INDEX) funktioiden käyttöä PHAKU (VLOOKUP) funktion sijaan. Tämä on monesti tehokkaampaa.

Dynaaminen kaavio

2 Replies

Taas kerran jouduin tappelemaan dynaamisen kaavion kanssa ja laitan esimerkin nyt myös tähän.

Syy, miksi käytän dynaamista kaaviota, on esimerkiksi kun haluan kaaviokuvan jatkuvan automaattisesti, kun datasarjaan tulee uusia elementtejä.

Voihan sen myös tehdä kopioi-liitä menetelmällä, mutta kun on kiire ja tapahtuma on toistuva, haluaa mielellään jotakin “helpompaa”.

Dynaaminen kaaviokuva rakentuu datasarjoista, joille on annettu nimet. Nimet taas saadaan dynaamisiksi käyttäen funktioita SIIRTYMÄ (OFFSET) ja LASKE.A (COUNTA).

SIIRTYMÄ-funktion syntaksi on:
SIIRTYMÄ(viittaus; rivit; sarakkeet; [korkeus]; [leveys])

Kun olen määritellyt nimet, luon kaaviokuvan normaalilla tavalla.
Tämän jälkeen lähden muokkaamaan datasarjoja ja korvaan siinä olevat viittaukset luomillani nimillä.

=SARJA(Esimerkki!$A$2;kd_kaavio.xlsx!Kuukaudet;kd_kaavio.xlsx!Tuote_1;1)

Huomaa, että sinun on annettava datasarjassa koko työkirjan nimi, pelkkä datasarjan nimi ei riitä, vaikka nimen laajuudeksi on määritelty työkirja.

Alla oleva kuvassa on esitelty kaavio, työkirjassa olevat nimet ja kaaviokuvan sarja.

Dynaaminen kaavio määrityksineen

Dynaaminen kaavio

Nimen määrittely

Valitse Kaavat/Määritetyt nimet, Määritä nimi (Formulas/Defined name, Define name).
Anna nimi valintaikkunassa. Viittauskohtaan kirjoitat kaavan.

 

Määritä nimi

Määritä nimi

Kuvassa on kuukaudet-nimen määrittely.
=SIIRTYMÄ(Esimerkki!$A$1;0;1;;LASKE.A(Esimerkki!$1:$1))
SIIRTYMÄ(viittaus; rivit; sarakkeet; [korkeus]; [leveys])
viittaus = taulun nimi ja soluviittaus josta aloitetaan
rivit = 0 koska ollaan samalla rivillä kuin viittaus
sarakkeet = 1 koska kuukaudet alkaa viittauksesta nähden seuraavasta sarakkeesta
korkeus = ;; ei tarvita koska ollaan samalla rivillä
leveys = LASKE.A(Esimerkki!$1:$1), lasketaan rivin 1 täytetyt solut

Tuotenimet on määritelty vastaavasti:

=SIIRTYMÄ(Esimerkki!$A$2;0;1;;LASKE.A(Esimerkki!$2:$2)-1)
Huomaa nimen muoto: Tuote_1 koska nimissä ei saa olla välilyöntejä.
-1 kaavassa tarvitaan, koska tieto alkaa toisesta sarakkeesta, ensimmäisessä kun on riviotsikko.

Kun nimet on määritelty, valitsen kaaviokuvan datasarjan ja korvaan siinä olevat viittaukset luomillani nimillä.
Tässä työskentelen kaavarivillä, koska se on mielestäni helpointa näin.

=SARJA(Esimerkki!$A$2;kd_kaavio.xlsx!Kuukaudet;kd_kaavio.xlsx!Tuote_1;1)
Huomaa tosiaan työkirjan nimi: kd_kaavio.xlsx!

Kun työskentelet valintaikkunoissa, kuten tuo nimenmäärittelyikkuna, ja haluat siirtyä kirjoittamassasi kaavassa nuolinäppäimellä, paina ensin funktionäppäintä F2, muussa tapauksessa se onnistu.