Monthly Archives

maaliskuu 2019

Datan käsittelyn kulmakivet Excelissä

By | Excel

Pitkän Excel-urani aikana olen havainnut, että Excelin tehokkaan käytön perustana on vain muutama melko yksinkertainen juttu, jotka on vieläpä helppo omaksua. Tästä huolimatta, en ole törmännyt kehenkään, joka systemaattisesti olisi hyödyntänyt näitä perusasioita. Itse taas en pärjää päivääkään käyttämättä jotain näistä (useimmiten kaikkia!). Seuraavaksi esittelen näkemykseni näistä datan käsittelyn peruspilareista tärkeysjärjestyksessä.

1 Datan kerääminen oikealla tavalla

Excel on uskomattoman tehokas työkalu, mutta sen voimien valjastamisen ehdoton edellytys on, että data kerätään sellaiseen muotoon, jota Excel on luotu käsittelemään. Käytännössä poikkeuksetta tämä tarkoittaa tietokantataulujen muotoon kerättyä dataa, jossa ainakin seuraavat ehdot täyttyvät:

  • Solussa on vain yksi asia kerrallaan.
    • Jos pitää kerätä esim. toimituspuutteita, soluun ei kirjoiteta ”2 lavaa” tai ”3 kpl”, vaan tehdään kaksi saraketta, ”Yksikkö” ja ”Määrä”
  • Sarakkeilla on otsikot ja samalla sarakkeella on vain samankaltaisia asioita.
    • Jos sarakkeella on syötettäviä arvoja, ei tehdä kaavarivejä väliin.
    • Kaavasarakkeet ovat erikseen, ja jokaisella sarakkeella on aina sama kaava.

2 Muotoile taulukoksi -toiminto (Format as table)

Edellä kuvatun kaltaisen datarakenteen muodostamisen avuksi kannattaa ehdottomasti käyttää Excelin toimintoa ”Format as table” (Muotoile taulukoksi), joka mm. kopioi sarakkeelle tehdyn kaavan automaattisesti koskemaan kaikkia rivejä. Samalla taulukkoalue tulee automaattisesti nimetyksi. Lisäksi taulukon otsikoihin voi viitata suoraan nimellä, jolloin kaavoista tulee helposti ymmärrettäviä.

Tarkastellaan seuraavaksi  yhteenvetoja myyntidatasta.

SUMIFS-kaava nimeämättömällä alueella

Tavoitteena on laskea Pipot-tuoteryhmän myytyjen tuotteiden määrä asiakkaasta välittämättä. Yo. kuvan kaavasta on vaikeaa saada tolkkua. Tosin kuvattu tilanne on optimaalinen siinä mielessä, että summattavat alueet ovat kaavan vieressä, mutta yleensä dataa on tällaisessa tilanteessa paljon ja kaavat toisella välilehdellä.

Tehdään seuraavaksi sama käyttäen taulukkoa, joka on tehty käyttäen Format as table -toimintoa:

SUMIFS-kaava nimetyssä taulukossa

Huomataan, että kaavasta on tullut hyvin selkeälukuinen; taulukon ’myyntidata’ sarakkeesta ’Määrä’ lasketaan yhteen rivit, joille sarakkeen ’Tuoteryhmä’ arvo on solun A10 arvo (’Pipot’). Tämä kaava on myös helppo kirjoittaa, vaikka sen tekisi kokonaan toiselle välilehdelle, jos vain taulukon ja sarakkeen nimet on valittu järkevästi. Tällaisia kaavoja ei tarvitse myöskään kirjoittaa kokonaan käsin, vaan taulukon nimen jälkeen Excel antaa listan sarakeotsikoista, joita voi kätevästi valita sarkain-näppäimellä.

3 Pivot-taulukon käyttö

Ei liene liioiteltua sanoa, että Excelin paras toiminto on pivot-taulukko. Sillä voi aggregoida muutamassa sekunnissa satojen tuhansien rivien datasta olennaisen tiedon ulos. Esimerkiksi edellisen esimerkin tuoteryhmäkohtainen summa syntyy muutamalla klikkauksella (tarkalleen ottaen neljällä):

Pivot-ryhmittely tuotteittain

Tai jos halutaan yhteenveto asiakkaittain, tarvitaan kaksi klikkkausta lisää:

Pivot-ryhmittely asiakkaittain

Pivot-taulukon käyttö edellyttää ehdottomasti, että data on kerätty yllä mainitulla tavalla, ja data kannattaa myös olla muotoiltu taulukoksi (Format as table). Tällöin pivot taulukon luonti on erittäin helppoa; painetaan vain taulukon Design-välilehden nappia:

Pivot-taulukon luonti

4 Excelin peruskaavat SUMIFS ja INDEX-MATCH

Kaksi ehdottomasti tärkeintä kaavaa datan käsittelyssä ovat SUMIFS ja INDEX-MATCH yhdistelmä. Ensin mainittu esiintyi jo ylempänä, ja pivot-taulukko tekee juuri tämän saman työn. INDEX ja MATCH ovat kaksi erillistä funktiota, mutta yhdessä erittäin tehokkaat. Nämä kaksi funktiota korvaavat usein käytetyn VLOOKUP-funktion (jonka käytöstä kannattaa luopua heti, jos olet tähän erehtynyt).

INDEX- ja -MATCH-funktioiden käyttö on helpointa esitellä esimerkillä. Oletetaan, että edellä esitellyn myyntidatan lisäksi on seuraavanlainen asiakasrekisteri:

Excel-taulukko asiakkaat

Jos haluamme hakea asiakkaan nimen myyntidata-tauluun, käy tämä seuraavasti (taulukon nimenä ’asiakas’):

INDEX-MATCH-esimerkkitaulukko

INDEX siis ottaa syötteekseen taulukon, rivinumeron ja sarakenumeron (sarakenumero ei ole pakollinen, jos hakualue on yhden sarakkeen levyinen). MATCH taas palauttaa hakualueelta ensimmäisen löytämänsä hakuarvon vastineen rivinumeron. Käytettäessä hakuparametria 0, pitää vastineen olla täsmälleen sama.

Esimerkissä siis taulukon ’asiakas’ sarakkeesta ’Nimi’ haetaan kohdetaulukon sarakkeesta ’Asiakas’ löytyvää arvoa. Ensimmäisen rivin tapauksessa hakuarvo on ’X’, joka löytyy asiakastaulun ensimmäiseltä riviltä. MATCH-funktio palauttaa siis hakuarvon X-tapauksessa arvon 1. Tällöin INDEX-kaava on seuraavanlainen: INDEX(asiakas[Nimi];1). Siis asiakas-taulukon ’Nimi’-sarakkeesta palautetaan ensimmäisen rivin arvo, eli Urheilutukku Oy. Ja kuten aiemmin todettiin, muotoiltu taulukko kopioi kaavan automaattisesti kaikille riveille, ja lopputulos näyttää seuraavalta:

Excel-taulukon muotoilu

Vielä on kiinnitettävä huomioita @-symbolin käyttöön, joka on tärkeä osa tämän tyyppisissä taulukoissa. @-symboli viittaa aina kaavan sijaintiriviin. Tällöin siis kaavassa näkyvä MATCH([@[Asiakas]]…) tarkoittaa, että etsitään sarakkeen ’Asiakas’ kaavan kanssa samalla rivillä olevaa yksittäistä arvoa. Eli @-symboli viittaa aina sarakkeen tiettyyn riviin, ja ilman tätä symbolia viitataan koko sarakkeeseen. Lisäksi, jos tarkastellaan sarakkeeseen ’Asiakkaan nimi’ syntynyttä kaavaa, huomataan, että kaava on täsmälleen sama kaikilla riveillä. Eli vaikka hakuarvo tietysti muuttuu riveillä, @-symboli mahdollistaa sen, että itse kaava ei muutu!

Edellä esitettyjen toimintojen hyvä puoli on myös se, että ne toimivat täsmälleen samoin, vaikka datamäärä kasvaisi kuinka paljon hyvänsä!

Kiinnostuitko?

Haluatko apua laskentataulukkojesi järjestämisessä tai Excel-raportoinnissa? Ota yhteyttä ja mietitään yhdessä miten voimme auttaa juuri sinun yritystäsi!