Miksi Excelin SIIVOA-funktio ei aina toimi?

Julkaistu Julkaistu: Blogi

Varmasti jokainen on löytänyt laskentataulukostaan numeroita, jotka eivät toimi kuten niiltä odottaisi. Tilanne voisi tällöin näyttää samalta kuin kuvassa vasemmalla. Jos Hinta 0 % -sarakkeen luvuista tehtäisiin valinta, Excelin tilarivillä alhaalla lukisi todennäköisesti Laske: 5 sen sijaan, että siellä näkyisi myös valitun alueen keskiarvo ja summa. Jo pelkästään numeroiden tasaus solun vasempaan laitaan tietää huonoa, koska Excelin oletus on, että teksti tasataan vasempaan, luvut oikeaan. On tietysti mahdollista, että taulukon laatija tasaa tarkoituksella luvut vasempaan, mutta tämä on sen verran outo käytäntö, että pidän vasenta tasausta melko varmana merkkinä väärästä tietotyypistä.

Ensimmäinen oletus yleensä on, että numeromerkit ovat syystä tai toisesta tekstimuodossa ja tällöin apua haetaan Muunna luvuksi -komennosta, jonka löydät mm. vihreällä kulmalla merkityn solun viereen ilmaantuvaa kuvaketta klikkaamalla. Jos onni on myötä, asiat lutviutuvat. Aina onni ei ole myötä. Kuvan tapauksessa vihreä merkki on sitä paitsi näkyvissä vain kahdella ylimmäisellä rivillä, joten siitä ei ole apua sarakkeen lopun kanssa.

Eräs omituisuus Excelissä muuten on, että vaikka luvut olisivat tekstimuodossa, ne saattavat kelvata osaksi kaavaa. Tässäkin tapauksessa tekstimuotoinen tieto toimii sarakkeen L kertolaskussa.

Excelin datan putsauksen perusfunktioihin kuuluu SIIVOA(CLEAN), jota kannattaa yleensä kokeilla. Se poistaa ns. tulostumattomat merkit. Niitä et näe silmämääräisesti, mutta ne estävät Exceliä ymmärtämästä numeroista muodostuvaa merkkijonoa. SIIVOA-funktion rajoitus kuitenkin on, että se tunnistaa merkit vain, jos ne sattuvat kuulumaan ASCII-taulukon 32 ensimmäisen merkin joukkoon. Soluun voi päätyä muitakin merkkejä, kuten kuvan tapauksessa on tapahtunut. Kuvan tilanne muuten perustuu tositapaukseen, mutta tekstiä ja lukuja on muunneltu siten, että taulukon alkuperä ei käy ilmi.

Esimerkkitapauksessa taulukko saatiin toimimaan seuraavasti:

Selvitin PITUUS-funktiolla (LEN) sarakkeessa K olevien solujen merkkimäärän (siivousurakan eri vaiheille on kuvassa omat sarakkeet, jotka olen nimennyt käyttämäni funktion mukaan). Koska esimerkiksi solussa K4 on merkit 6 ja 5, mutta PITUUS antaa vastaukseksi 3, on selvää, että solussa on ylimääräinen merkki. Se on poistettava mikäli sisällön haluaa muuntaa luvuksi. Poistaminen onnistuu vaikka niin, että aktivoi kaavarivin ja nitistää kiusankappalemerkin käsin – tämäkin merkki näytti kaavarivillä tyhjältä väliltä ja oli mahdollista poistaa. Jos taulukossa kuitenkin sattuisi olemaan esimerkiksi reilut 10 000 riviä, tällainen lähestymistapa ei ole mielekäs. Kun käsillä on iso aineisto, on helpompi käsitellä mahdollisimman suuri alue kerralla.

KOODI-funktio (CODE) kertoo sille osoitetun merkin ASCII-koodin, mutta vain yksi merkki kerrallaan, joten ensimmäiseksi on erotettava tutkittava merkki muusta solun sisällöstä. Koska kuvassa luvut ovat solun vasemmassa laidassa kiinni, uskalsin olettaa, että numeron edessä ei ole mitään ja virhemerkki sijaitsee merkkijonon oikeassa reunassa. Avuksi sopii funktio nimeltä OIKEA (RIGHT), joka pakottaa tutkimaan solun sisältöä oikeasta laidasta alkaen. Kaava =KOODI(OIKEA(K2;1)) pyytää Exceliä lukemaan solusta K2 oikealta lukien ensimmäisen merkin ja kirjoittamaan sen koodinumeron kaavan olinpaikkaan eli sarakkeen O soluun. (Koska virhemerkit eivät aina ole solun oikeassa laidassa, pistä muistiin myös funktiot VASEN (LEFT), POIMI (MID), ETSI(FIND) sekä tässä jo mainittu PITUUS (LEN). Näistä saa erilaisia yhdistelmiä, joilla pääsee käsittelemään solusta löytyvää tietoa.)

Kahdella ensimmäisellä rivillä olevat koodit tarkoittavat numeroita 5 ja 0, mutta 160 tarkoittaa sitovaa välilyöntiä (non breaking space), joka voi kulkeutua mukana kopioitaessa tietoja esimerkiksi verkkosivulta/intrasta. Merkki poistetaan korvaamalla se tyhjällä eli tässä tapauksessa kaavalla =VAIHDA(K4;MERKKI(160);””). Kaava käy läpi solun K4 sisällön ja löydettyään solusta koodia vastaavan merkin, se sijoittaa tilalle tyhjän (kaksi lainausmerkkiä, joiden välissä ei ole mitään = tyhjä). Tiesithän, että solun oikeassa alanurkassa olevan pienen ruksin kaksoisklikkaus kopioi kaavan saman tien kaikkiin sarakkeen soluihin, joten sinun ei tarvitse “maalata” hiirellä tuhansia rivejä.

Lopuksi kopioin siistityn sarakkeen sisällön leikepöydälle ja liitin sen virheellisen sarakkeen paikalle valinnalla Liitä määräten/Arvot. Solun vasemmassa laidassa näkyy vihreä merkki, mutta siitä selvitään liittämispainikkeen valikkokomennolla: sarakehan jää valituksi liittämisen jälkeen, joten Muunna luvuksi korjaa kaikki sarakkeen luvut.

Jos pidit artikkelia hyödyllisenä, saatat kiinnostua myös tästä: Näin saat kyselyn tulokset esityskelpoiseen muotoon pivot-taulukon avulla.

Jaa Facebookissa!
Jaa Twitterissä!
Jaa Google+ :ssa!
Jaa Linkedinissä!
Jaa Pinterestissä!
Seuraa Youtube-kanavaa!
Lähetä linkki kaverille!
ageismi ajanhallinta esiintyminen esitysgrafiikka esitysgrafiikkakoulutus etätyö Evernote Excel Excel-koulutus Instagram iOS itsensä johtaminen kaavio kirjat koulutus kuva Microsoft Office Microsoft Office -koulutus monipaikkatyö muistikirjaohjelma office 365 OneNote OneNote-koulutus oppiminen peliajattelu Pivot-taulukko PowerPoint PowerPoint-koulutus presentaatio Prezi Prezi-koulutus sosiaalinen intra sosiaalinen media sparkline sparkline-kaavio Sway sähköposti tietotyö tuottavuus Twitter työssäoppiminen uusi työ visualisointi webinaari yrittäjyys

Vastaa