Taloushotelli julkaisee LinkedInissä viikoittaisen teeman mukaisia lyhyitä tietoiskuja maanantaista perjantaisin.
Viikolla 21 (2024) teema oli Excel-vinkit tekstinkäsittelyyn. Löydät alta yhdistettynä viikon Excel-vinkit. Jos kaipaat apua Excelin hyödyntämisessä organisaatiossasi tehokkaasti, ota yhteyttä Taloushotelliin.
1. TEXTSPLIT
Kun Excelissä piti datasta erottaa vaikkapa etunimet ja sukunimet, ei tehtävä ollut aina helppo. Yleisin tapa edellytti ensin LEFT ja FIND -funktioiden yhdistämistä. Tällä saatiin vasta etunimi! Sukunimi saatiin RIGHT ja FIND -funktioiden naittamisella (tai vaihtoehtoisesti MID ja FIND -funktion kautta). Vaihtoehtoisesti “Text to columns” tuottaa lopputuloksen muutamalla napin painalluksella, mutta vaatii manuaaliajon, eikä ole kestävä ratkaisu, jos dataa tarvitsee päivittää.
TEXTSPLIT on Excelin uusimpia funktioita ja korvaa yksinkertaisuudellaan nämä legacy-ratkaisut. TEXTSPLIT-funktiolla voit helposti jakaa tekstin eri osiin käyttämällä erilaisia erottimia, kuten pilkkuja, välilyöntejä tai viivoja. Tämä tekee tekstin käsittelystä paljon intuitiivisempaa ja tehokkaampaa.
Esimerkiksi, jos solussa A1 on teksti ”Etunimi Sukunimi”, voit jakaa sen etu- ja sukunimeksi käyttämällä seuraavaa kaavaa: =TEXTSPLIT(A1; ” ”)
Esimerkin etu- ja sukunimityyppinen data on myös yleensä täynnä hömppää kuten ylimääräisiä välilyöntejä. Vanhoja ratkaisuja käyttämällä moni löytää itsensä siivoamasta näitä datasta käsin tai kikkakolmosin. TEXTSPLITissä on kuitenkin sisäänrakennettu toiminnallisuus, jolla ylimääräiset erottimet saa siivottua pois: =TEXTSPLIT(A1; ” ”;;TRUE)
Ja jos tarvetta olisi esimerkiksi pelkälle etu- tai sukunimelle, niin ongelma ratkeaa TEXTAFTER ja TEXTBEFORE -funktioilla.
2. TEXTJOIN
Kun Excelissä piti yhdistää esimerkiksi osoite, postinumero ja -toimipaikka yhteen soluun, päädyttiin usein ärsyttävän muotoiluongelman äärelle. Esimerkiksi useiden solujen sisällön yhdistäminen yhdeksi tekstiksi edellytti CONCATENATE-funktion käyttöä (vaihtoehtona “&”-operaattori), mikä oli aikaa vievää ja altisti virheille: =CONCATENATE(A1; ” ”; B1; ” ”; C1)
Tämä kaava yhdistää solujen A1, B1 ja C1 sisällöt tyhjällä välillä erotettuna. Vaikka tämä toimii, se on kömpelö ja vaatii jokaisen solun yksitellen mainitsemista. Myös välimerkki pitää erikseen kirjoittaa jokaisen solun väliin. Lisäksi, CONCATENATE ei salli tyhjien solujen ohittamista, mikä voi johtaa ei-toivottuihin tuplaväleihin.
TEXTJOIN-funktio korvaa nämä vanhat ratkaisut yksinkertaisuudellaan ja joustavuudellaan. TEXTJOINin avulla voit helposti määrittää erottimen, ohittaa tyhjät solut ja yhdistää suuretkin tietomäärät vaivattomasti.
TEXTJOIN-funktiolla voit yhdistää tekstiä näin: =TEXTJOIN(” ”; TRUE; A1)
Tämä kaava yhdistää solujen A1–C3 sisällöt tyhjällä välillä erotettuina, ohittaen mahdolliset tyhjät solut. Se tekee tekstin yhdistämisestä helppoa ja nopeaa, säästäen aikaa ja vaivaa.
3. CONCAT
Excelissä on toisinaan tilanteita, joissa sinulla on taulussa vaikkapa tuotteen nimi ja tuotteen koodi, mutta haluat yhdistää tiedon muotoon “Tuote: lapaset | Tuotekoodi: A2122”.
Yksi tapa (jota olen nähnyt pelottavan usein käytettävän) on hakata tiedot yksi kerrallaan käsin. Toinen tapa on yhdistää tiedot funktiolla, johon sopii tässä tilanteessa CONCAT. =CONCAT(”Tuote: ”; A1;” | Tuotekoodi: ”; B1)
CONCAT on erityisen hyödyllinen silloin, kun on pitkiä rimpsuja, jotka pitää lisätä yhdeksi pötköksi. Esim. jos tuotekoodi on jaettu kolmeen sarakkeeseen: “A”, “21”, “22”, niin muista keinoja käyttäen päädyt esim. tällaiseen rimpsuun: =”Tuote: “&A1&” | Tuotekoodi: “&B1&C1&D1
Concatia käyttäen päästään paljon selkeämpään muotoon. =CONCAT(”Tuote: ”; A1;” | Tuotekoodi: ”; B1)
Ero on erityisen konkreettinen, kun yhdisteltäviä numerosarjoja on jopa satoja. CONCAT-funktio on parannus vanhaan CONCATENATE-funktioon, joka tulee poistumaan kokonaan lähivuosina.
4. SUBSTITUTE
CSV-tiedoston muodostaminen Excelissä voi johtaa päätäsärkeviin virheisiin, jos selitteissä tai muussa lähtöaineistoissa on puolipisteitä. Tällöin SUBSTITUTE pelastaa päivän.
Tilanne tulee vastaan monessakin käytännön tehtävässä, mutta itselleni yksi konkreettisin on, kun siirretään kirjanpitotietoja yhdestä järjestelmästä toiseen. Jos migraatio suoritetaan amatöörimäisesti eli siirretään vain saldot, niin ongelma ei ole konkreettinen, mutta siirrettäessä kaikki kirjanpitorivit ollaan tyypillisesti tilanteessa, jossa varsinkin tiliotteen selitteissä on kaikenlaisia yliheittomerkkejä ja puolipisteitä, jotka rikkovat CSV-tiedoston, mikäli ne pääsevät niin pitkälle.
Yleensä ongelma korjataan Power Queryn kautta, mutta jos kyseessä on ns. helppo siirto, jossa sarakkeita vain järjestellään uuteen järjestykseen, niin yksinkertaisella funktiolla pääsee ongelmasta eroon: =SUBSTITUTE(Siirtoaineisto[Selite];”;”;””)
Vastaavaa ratkaisua voi soveltaa, jos Exceliin tuodaan aineistoa, jossa ääkköset kääntyvät hepreaksi. Sen sijaan, että käsin näppäillään PÆÆkkœnen Pääkköseksi, voi tällä kaavalla hoitaa koko sarakkeen kerralla. Säästää aikaa ja hermoja – varsinkin jos vastaanottava järjestelmä ottaa vastaan rikkinäistä dataa ja selvittelet kynän ja kamman kanssa iltapäivän, mistä erot ovat syntyneet.
5. PROPER, UPPER & LOWER
Mitä jos sinun pitäisi siistiä tuhatniminen asiakasrekisteri, mutta lähtödata on kirjoitettu ihan miten sattuu? Siellä on MAtti mEikäläinen ja sadoittain vastaavia typoja.
Yksi tapa on puhdistaa koko roska käsin, mutta kallisarvoista aikaa voi käyttää voi toisinkin – varsinkin kesällä.
Mitä jos koko roskan saisi puhdistettua kerralla näin? Monen tunnin työ tippuu 30 sekuntiin. =SUBSTITUTE(PROPER(A1);”Æ”;”ö”)
PROPER-funktio on erityisen hyödyllinen etunimien ja sukunimien yhtenäistämisessä. Se muuntaa tekstin muotoon ”Matti Meikäläinen”, eli jokaisen sanan ensimmäinen kirjain on iso ja loput pienellä.
Entä mitäpä jos eilisen esimerkin mukaisesti lähtödata on lisäksi vielä hassua ja kaikki äovat kääntyneet muotoon Æ? MAtti mEikäläinen onkin MAtti mEikÆlÆinen.
Sitten vain funktioita yhdistelemään: =SUBSTITUTE(PROPER(A1);”Æ”;”ä”)
SUBSTITUTE-funktiolla korjataan se karu kohtalo, jonka ääkköset usein kokevat matkalla eri järjestelmästä toiseen.
Isot alkukirjaimet joka sanassa eivät ole aina haluttu lopputulos, jos pelataan yritysten nimien kanssa. Tällöin yksi ratkaisu voi olla UPPER, joka muuttaa koko nimen isoiksi kirjaimiksi. Vähemmän huutava ratkaisu on LOWER, jolla saadaan nauttia tasaisen pienistä kirjaimista.