Tag Archives: match

Verrataan tämän viikon lukuja edellisvuoden vastaaviin

Leave a reply

Excelin funktioiden avulla se voidaan tehdä alla olevan esimerkin mukaan.
Esimerkissäni lähden siitä, että minulla on kaikki tieto allekkain taulukkomuodossa.
Lähtötieto määritellään taulukoksi (Lisää/Taulukko (Insert/Table)).
Taululle annoin nimen Results. Taulun sarakkeiden nimiksi tulee Day ja Value. Näitä käytän myöhemmin kaavoissani.

Ohessa esimerkkitaulu:

Sitten rakensin kaavat.
Soluun B1 kirjoitan päivämäärän josta lähden liikkeelle. Siinä voisi olla myös funktio TÄMÄ.PÄIVÄ (TODAY) jos haluan lähtökohdaksi aina tämän päivän.
Soluun D7 laitan kaavan =B1
Soluun D8 tulee kaava =D7-1. Tämä vähentää aina kyseisestä päivästä yhden päivän.
Soluun D9 kaava =D8-1 jne.
E7-E13 soluihin haen arvon kaavalla:
=JOSVIRHE(INDEKSI(Results[Value];VASTINE(D7;Results[Day];0));0)
=IFERROR(INDEX(Results[Value];MATCH(D7;Results[Day];0));0)
Kaavassa käytän kolmea funktiota: VASTINE (MATCH) joka hakee päivämäärän taulukosta ja palauttaa sen rivinumeron jossa se sijaitsee, INDEKSI (INDEX) palauttaa arvon joka sijaitsee arvosarakkeessa sillä rivillä jonka funktio vastine määrittelee ja lopuksi JOSVIRHE (IFERROR) funktio joka antaa kaavan arvoksi nolla (0) mikäli päivämäärää ei löydy.
Kaavoissa viitataan määriteltyyn taulukkoon ja sen sarakkeisiin esimerkiksi seuraavasti: Results[Day], Results on taulun nimi ja [Day] taulun sarake.
Sarakkeessa F lasketaan päivämäärä seuraavasti:
=ARVO(PÄIVÄ(D7)&”.”&KUUKAUSI(D7)&”.”&VUOSI(D7)-1)
=VALUE(DAY(D7)&”.”&MONTH(D7)&”.”&YEAR(D7)-1)
Funktiot PÄIVÄ (Day) poimii päivän, KUUKAUSI (MONTH) kuukauden ja VUOSI (YEAR) vuoden josta vähennetään yksi (1). Nämä yhdistetään merkkijonoksi &-merkillä jossa piste on erottimena. Koska piste on tekstiä on se laitettava lainausmerkkeihin (“). ARVO (VALUE) funktio muuntaa merkkijonon arvoksi jotta voin sitä käyttää G-sarakkeen kaavoissa (A-sarake josta päivämäärä haetaan on päivämäärämuoto).
Toinen helpompi tapa on käyttää funktiota PÄIVÄ.KUUKAUSI (EDATE) ja silloin kaava olisi:
=PÄIVÄ.KUUKAUSI(D7;-12)
=EDATE(D7;-12)
G-sarakkeen kaavat ovat rakenteeltaan samanlaisia kuin E-sarakkeessa:
=JOSVIRHE(INDEKSI(Results[Value];VASTINE(F7;Results[Day];0));0)
=IFERROR(INDEX(Results[Value];MATCH(F7;Results[Day];0));0)

SIIRTYMÄ – VASTINE (OFFSET – MATCH)

Leave a reply

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

Tehosta Excel kaavoja

Leave a reply

Järkeä PHAKU (VLOOKUP) –funktion käyttöön.

Kun halutaan välttää PHAKU-funktion palauttama virhe, niissä tapauksissa kun hakuarvoa ei löydetä, käytetään usein rakenteeltaan seuraavan laista kaavaa:

=JOS(ONVIRHE(PHAKU(A1;Product!C1:AF1691;3;EPÄTOSI));””;PHAKU(A1;Product!C1:AF1691;3;EPÄTOSI))

Eli JOS (IF) ja ONVIRHE (ISERROR) funktiolla testataan palauttaako PHAKU virheen. Oheisessa esimerkkikaavassa PHAKU suoritetaan kaksi kertaa, mikäli arvo löydetään. Ensin tarkistetaan palauttaako virheen ja mikäli ei palauta virhettä suoritetaan haku uudestaan arvon palauttamiseksi.

Kaava voidaan optimoida käyttämällä JOSVIRHE (IFERROR) funktiota seuraavasti:
=JOSVIRHE(PHAKU(A1;Product!C1:AF1691;3;EPÄTOSI);””)
Nyt suoritetaan PHAKU vain kerran jolloin laskenta nopeutuu.

Kannattaa myös lajitella se sarake josta PHAKU-funktiolla haetaan.

Usein etsitään tietoja myös samasta taulusta samalla hakuarvolla useista sarakkeista PHAKU –funktiolla.
Edullisempi tapa saattaa usein olla PHAKU –funktion korvaaminen VASTINEN (MATCH) ja INDEKSI (INDEX) –funktiolla.
VASTINE palauttaa rivinumeron jolla hakuarvo sijaitsee ja INDEKSI –funktiolla poimitaan tieto halutusta sarakkeesta. Täten tehdään haku vain kerran.

Esimerkki:

Hakufunktiot