Toukokuun puolivälissä minulle tarjoutui tilaisuus pitää Excel-jatkokurssi. En tietenkään kieltäytynyt, kun tilaisuus osui kohdalle kollegan sairastumisen johdosta. Excel jatkokurssilla on poikkeuksetta kovin motivoitunutta väkeä, innokkaita harjoitusten tekijöitä ja aktiivisia kyselijöitä. Siellä yleensä itse oppii myös jotain uutta. Ei tullut poikkeusta tälläkään kertaa. Seuraava episodi tapahtui erään funktiolaskuharjoituksen kanssa.

Meillä oli harjoitus, jossa verrattiin kahta luetteloa. Ensimmäisessä oli otsikot mukaan lukien 105 riviä ja toisessa 127 riviä. Ei siis mitään. Varsin lyhyitä luetteloita kummatkin. Näihin sitten PHAKU-funktiolla (engl. VLOOKUP) tehtiin koodien perusteella selitystekstien haku toisesta luettelosta. Ei vielä tässä vaiheessa kuulosta kovin kummalliselle. Eihän?

Harjoitustehtävän kuvaus

Harjoituksen tehtävänanto kuuluu seuraavasti:

Tee näiden kahden tässä taulukossa olevien aineistojen vertailu.
Onko jommassa kummassa aineistossa koodeja, jotka puuttuvat toisesta?

Harjoitusaineisto näyttää alla olevan kuvan mukaisilta. Lähdejärjestelmä1:ssä alue on siis solut A3:B107 ja vastaavasti Aineisto2 on vastaavasti solut E3:F129.

Siispä lähdimme tekemään laskukaavan soluun C4 seuraavalla tavalla:

=PHAKU(A4;$E$4:$F$129;2;EPÄTOSI)
=VLOOKUP(A4;$E$4:$F$129;2;FALSE)

Ensimmäinen kaava solusta C4 kopioidaan sitten täyttökahvalla alas kaikille muille Lähdejärjestelmä1:n riveille.

Sen jälkeen soluun G4 tehdään vastaavalla periaatteella samankaltainen PHAKU-funktion mukainen lauseke:

=PHAKU(E4;$A$4:$B$107;2;EPÄTOSI)
=VLOOKUP(E4;$A$4:$B$107;2;FALSE)

Kaavan tulos näyttää järkevältä, jonka jälkeen kaavaa sitten kopioidaan muille riveille.

Harjoituksen tulos

Tässä ei sinänsä vielä mitään ihmeellistä. Tätähän harjoituksen toimeksiannossa juuri pyydettiinkin. Lopputulos on toivotun näköinen ja PHAKU-funktio ratkaisi asian. Tällaisia luettelovertailuita on Excelillä tehty ja tullaan tekemään jatkossa vielä moniaita kertoja uudestaan. Toimii. Ihastellaan vielä lopputulosta hetken aikaa:

Äkillinen muutos tilannekuvassa

Harjoitus saatiin siis valmiiksi. Arvostan kärsivällisyyttäsi, että olet jaksanut tänne asti lukea tätä blogikirjoitusta. Mutta missä tässä olivat ne otsikon lupaamat kansalliset erityispiirteet? Odota…

Kun kurssilaiset ovat suomalaisia, niin erityispiirre on se, että hehän keksivät heti käteviä lisäkysymyksiä. Niin tästäkin harjoituksesta. Eräs kurssilainen kysyi, että ”Voidaanko lisätä nyt H-sarakkeeseen teksti Samanlaiset, jos F-sarakkeen ja G-sarakkeen tekstit ovat samat ja teksti Erilaiset, jos F- ja G-sarakkeiden tekstit poikkeavat toisistaan?”

Minä sanoin, että ”Tietenkin. Tehdään se JOS-funktiolla.”

Niinpä sitten lisäämme soluun H4 seuraavan funktiolausekkeen:

=JOS(F4=G4;"Samanlaiset";"Erilaiset")
=IF(F4=G4;"Samanlaiset";"Erilaiset")

Lopputulos solussa H4 näyttää aivan oikealta. Sekalaiset ja Muu ovat taatusti eri arvot, joten pitääkin lukea solussa H4, että ovat Erilaiset. Tästä sitten täyttökahvalla kopioimaan alaspäin.

Nyt ei enää näytäkään hyvältä. Jokin on pielessä. Kaikki on erilaisia tai tulee virheilmoitus #PUUTTUU! (mikä sinänsä on ihan oikein ja näin sen pitääkin tässä aineistossa mennä, koska luetteloissa ei ollut kaikkia toisiaan vastaavia koodin mukaisia selitystekstejä). Mutta eikö Ylioppilastutkinko ole sama arvo soluissa F5 ja G5? Silmämääräisesti kyllä. Jokin on siis pahasti pielessä.

Ongelmanratkaisun alkeita

Onko lähtötiedoissa ongelma? Tämä tulee ensimmäisenä mieleen. Kun luet Exceliin erilaisista tietojärjestelmistä tietoja sisään, niin monesti joudun esikäsittelemään aineistoa ennen kuin voit tehdä mitään järkevää laskentaa tai analysointia tiedoille. Lähdejärjestelmä saattaa lisätä erilaisia erotinmerkkejä ja tietueen tai rivin lopetusmerkkejä. Monesti tietoja joutuu jakamaan sarakkeisiin ja siivoamaan erikoismerkkejä pois. Tutkitaanpa tätä meidän aineistoa. Ovatko esimerkiksi Lähdejärjestelmä1:ssä ja Ainesto2:ssa olevat ”Ylioppilas” koulutuskoodit yhtä pitkiä tekstejä? Se selvitetään lisäämällä PITUUS-funktio (engl. LEN) sarakkeisiin I ja J. Sarakkeessa I tutkitaan solun B5 arvoa ”Ylioppilastutkinto” ja sarakkeessa J puolestaan solun F5 tekstiä ”Ylioppilastutkinto”. Tätä kaavaa voisi sitten monistaa muille riveille.

Kaavat solussa I5 on seuraavasti:

=PITUUS(B5)

=LEN(B5)

Samalla tavalla tehdään kaava soluun J5:

=PITUUS(F5)

 

=LEN(F5)

No niin, tässä taitaa olla ongelman ydin tai ainakin kovin lähelle ollaan päästy sitä. Näin voisi ajatella. Solussa F5 on 20 merkkiä pitkä teksti ja solussa B5 kaksi merkkiä lyhyempi. Onko siis F5-solussa kaksi välilyöntimerkkiä tekstin perässä?

Ongelmanratkaisua, vaihe 2

Äskeisen esimerkin valossa tulee mieleen, että poistetaan ylimääräiset välilyönnit käyttämällä POISTA.VÄLIT-funktiota (engl. TRIM).

Katsotaan lisäämällä sarakkeisiin K ja L uudet funktiolausekkeet muotoa:

=POISTA.VÄLIT(B5)
=TRIM(B5)

Ja vastaavasti siis L-sarakkeeseen:

=POISTA.VÄLIT(F5)
=TRIM(F5)

Katsotaanpa lopputulosta. Nyt kelpaa. Molemmista sarakkeista jäi jäljelle vain tekstit ilman mitään jälkijättöisiä välilyöntejä. Seis, odota… jäikö sittenkään?

Ongelmanratkaisua, vaihe 3

Nyt voisimme tutkia vielä PITUUS-funktiolla välilyönneistä trimmattujen tekstien pituuksia sarakkeissa M ja N.

Lisätään soluun M5 funktiolauseke:

=PITUUS(K5)

=LEN(K5)

Lisätään soluun N5 vastaavasti saman periaatteen mukaisesti funktiolauseke:

=PITUUS(L5)

=LEN(L5)

Tuliko hyvä?

Ei tullut hyvä. Ei.

Ongelmanratkaisua, vaihe 4

Mikä ihme siellä solussa F5 nyt oikein panee hanttiin? Tehdäänpä solussa kaksoisnapsautus:

Jahah… siellä on jotain merkkejä, jotka näyttävät välilyönneiltä, mutta eivät ole, koska äskeinen POISTA.VÄLIT-funktio ei niille mitään tehnyt. Tekstin viimeiset kirjaimen perässä on jotain merkkejä kaksi kappaletta, koska kursori vilkkuu solun syötteen lopussa. Näkymättömiä välilyöntejä? Mitä nuo ovat?

Selvitetään solun viimeinen merkki oikeasta reunasta lukien käyttäen funktioita OIKEA ja KOODI eli englanniksi vastaavat funktiot ovat RIGHT ja CODE. Poistetaan soluista I5:N5 aikaisemman kaavat ja niiden yläpuolelta muotoillut keltaiset otsikkosolut ja laaditaan soluun I5 seuraava kaava:

=KOODI(OIKEA(F5))

=CODE(RIGHT(F5))

Tulokseksi saadaan koodi 160:

Tavanomainen välilyönti on koodi 32, mutta 160 onkin harvinaisempi – se on sitova välilyönti, jota käyttävät kokeneet tekstinkäsittelijät esimerkiksi numerokoodeissa, joissa on tuhaterottimia, kun numerosarja pitää saada pysymään samalla tekstirivillä. Tämä selittää sen, että POISTA.VÄLIT ei vaikuttanut solun merkkijonon pituuteen. Se, mistä nämä merkit olivat tulleet Aineisto2:n rivien loppuun, jäi arvoitukseksi. Voi olla, että järjestelmä on osoittanut noilla merkeillä Exceliin vietävässä tiedostossa, että kyseessä on rivin loppu tai tietueen loppu. Mene ja tiedä. Vaikea sanoa, koska kurssilla meillä ei ollut tietoa mistä järjestelmistä nuo aineistot on luettu Exceliin.

Ratkaisuvaihtoehdot

Ensimmäinen vaihtoehto on se, että käytetään Excelin toimintoa MUOKKAA – KORVAA  (engl. EDIT – REPLACE) ja hätistellään sillä pois soluista nuo kaksinkertaiset sitovat välilyönnit. Toimii. Helppo, nopea ja hygieeninen ratkaisu. Toinen vaihtoehto on se, että laaditaan H-sarakkeeseen sellainen JOS-funktio, että se jättää F-sarakkeesta vertailtavan arvon kahta merkkiä lyhyemmäksi. Jälkimmäinen on siinä mielessä parempi, että jos tietojen tuonti lähdejärjestelmistä on toistuvaa, niin funktiolausekkeen osaavat käsitellä tuodun tiedon aina samalla tavalla. Muistaako ihminen, että tietojen tuonnin jälkeen pitää käyttää MUOKKAA-KORVAA -toimintoa kahden peräkkäisen sitovan välilyönnin hävittämiseen? Ei välttämättä muista. Ja muutenkin tietojen käsittelyä kannattaa automatisoida. Kaava solussa H4 on siis muotoa:

=JOS(VASEN(F4;PITUUS(F4)-2)=G4;”Samanlaiset”;”Erilaiset”)

=IF(LEFT(F4;LEN(F4)-2)=G4;”Samanlaiset”;”Erilaiset”)

Nyt alkaa olla jo niin selvät sävelet tässä kupletissa, että kopioidaan kaava saman tien muille riveille alaspäin:

Näyttää ihan juuri siltä kuten pitikin näyttää. Ainoa on nuo joissakin soluissa olevat #PUUTTUU! -virheilmoitukset. Niin oikeaoppinen käsittely onkin sitten kokonaan oman blogikirjoituksen aihe.

Loppupäätelmät

PHAKU ja sen sisarfunktio VHAKU (engl. HLOOKUP) ovat oikein käteviä luetteloiden vertailujen funktioita. Niilläkään ei kuitenkaan ratkaista kaikkea. Kannattaa huomata myös Excelin pivot-taulukkojen ominaisuudet tällaisia laskelmia selviteltäessä. Nyt etenkin Excelin versioissa 2013 ja 2016 PHAKU-funktion käyttöä voidaan monessa yhteydessä vähentää. Kun teet Pivot-taulukon, niin voit valita, että kyseinen taulukko tai alue liitetään tietomalliin:

Tällainen tietomallin käyttäminen on nykyaikainen tapa luetteloinformaation vertailua varten. Kyseessä on sen verran laaja asiakokonaisuus, että jätämme sen tällä kertaa käsittelemättä. Tämähän on jo kokonaan oman blogikirjoituksen aihe. Nyt ei muuta kuin Excelit esille ja aletaan vertailemaan luetteloita. Kansallisia erityispiirteitä unohtamatta. Siitä nimittäin saattaa oppia jotain uutta lisää, kun lähtee tekemään ihan oman mielijohteen mukaisesti luetteloiden vertailua.