Tag Archives: offset

SIIRTYMÄ – VASTINE (OFFSET – MATCH)

Esimerkki Siirtymä (Offset) ja Vastine (Match) -funktioiden käytöstä dynaamiseen summaamiseen.

Halutaan laskea listasta aina annetusta päivämäärästä seitsemän seuraava lukua yhteen.

Aloituspäivä kirjoitetaan soluun ja kaavassa käytetään Vastine-funktiota hakemaan päivän sijainti.

Koska vastinefunktioon on annettu koko A-sarake ja ensimmäisellä rivillä on otsikko, palautettavasta rivistä miinustetaan 1.
Kaava on muotoa:
=VASTINE(D2;A:A;0)-1, =MATCH(D2;A:A;0)-1

Esimerkissä se palauttaa päivämäärän positioksi 6.
Positiosta 6 halutaan laskea seuraavat 7 lukua yhteen B-sarakkeesta.
Käytetään Summa-funktiota, johon yhdistetään Siirtymä-funktio.
Siirtymä-funktiolla kerrotaan, mistä lähdetään liikkeelle (A1), monenneltako riviltä laskenta (Vastineen palauttama arvo) aloitetaan ja kuinka monta riviä lasketaan (E2).
=SUMMA(SIIRTYMÄ(A1;D4;1;E2)), =SUM(OFFSET(A1;D4;1;E2))
Kaikki voidaan laittaa yhteen ja samaan kaavaan, tällöin kaavan muoto on:
=SUMMA(SIIRTYMÄ(A1;VASTINE(D2;A:A;0)-1;1;E2))
=SUM(OFFSET(A1;MATCH(D2;A:A;0)-1;1;E2))

Esimerkki Summa, Siirtymä ja Vastine funktiosta

Dynaaminen kaavio

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.