Suuri arvosanataulukko – Taulukon siistiminen ja muotoilu

Tämä tarina alkaa siitä, kun löysin edestäni läjän kurssien arvosanoja ja päätin hieman tarkastella niitä. Data oli allaolevassa muodossa, joten sen saaminen analysoitavaan muotoon vaati useita pieniä kikkoja, joita ajattelin tässä valaista hieman lukijalle.

Eri tavoin muotoiltuja taulukoita saattaa tulla vastaan oikeastaan missä vain, joten suosittelenkin lukijaa kiinnittämään huomionsa erityisesti taulukoinnin lopputulokseen, eli siihen minkälaisessa muodossa taulukon tietojen kuulusi olla. Kaikki toimenpiteet sitä ennen ovat vain askelia sitä kohti, eli älä lue tätä ohjetta kuin piru raamattua, vaan mieti miten juuri sinun datassa pääsisit oikeaan lopputulokseen.

Aluksi data oli tässä muodossa, useampaan eri välilehteen jaettuna.

Screen Shot 09-25-14 at 09.43 AM

Lopulta taulukko näytti tältä, kaikki yhdessä välilehdessä.

Screen Shot 09-25-14 at 01.17 PM

Eron näkee toki jo kaukaa, mutta kannattaa huomata erityisesti muutamat seikat alkuperäisestä:

  • Arvosanat ovat pystyssä samassa solussa siten, että vasemmalla on annettu arvosana, keskellä opiskelijoiden lukumäärä ja lopuksi prosenttiosuus. Nämä ovat erotettu välilyönnillä toisistaan.
  • Kurssien koodit, nimet ja päivämäärät ovat taulukon sivussa epämääräisessä järjestyksessä, kuitenkin siten, että vain yksi niistä on rivillä ja sarakkeessa kerrallaan.

Lopputuloksesta näet, kuinka yhdellä rivillä on vain ja ainoastaan yksi havainto arvosanojen määrästä kerrallaan siten, että esimerkiksi:

”4. huhtikuuta 2013 kurssillla HALL3020 annettiin arvosanaa ’4’ 12 kappaletta”

Aloitetaanpas!

Vaihe 1: Yhdistä välilehdet samaan taulukkoon

Tämä vaihe saattaa kuulostaa turhalta, mutta jos kaikki taulukot ovat samassa mudossa useammalla välilehdellä, voit yhdistää ne kerralla samaan taulukkoon.

Vaihe 2: Lisätään kurssien tiedot kaikille riveille

Koska kurssien tiedot olivat kaikki omissa sarakkeissaan, niin tämä on todella yksinkertainen vaihe. Ajatuksena on, että jos solu on tyhjä, sen arvo on sama kuin sen yläpuolella olevassa.

Excelille selität tämän kirjoittamalla:

=IF(solu kurssisarakkeessa=””;ylempi solu;solu kurssisarakkeessa)

eli esimerkiksi

=IF(B23=””;E22;B23)

Kopioi tämä kaava kaikkiin soluihin painamalla oikean alakulman +-näppäintä. Toista sama muille sarakkeille.

Screen Shot 09-25-14 at 11.59 AM

Vaihe 3: Kopioi kurssien tiedot arvosanojen viereen

Muotoilin vaiheen otsikon tarkoituksella hankalasti sen vuoksi, että huomio kiinnittyisi oikeaan asiaan. Tarkoituksena ei siis ole silmät ummessa kopioida jotain arvoja, vaan päämääränä on saada jokaisen arvosanan kohdalle tarvittavat tiedot kurssista.

Väliin jää siis väistämättä kokonaisia rivejä, joissa on virheitä ja epämääräisiä arvoja. Älä huolehdi niistä, sillä voit poistaa ne vielä myöhemmin.

Kun kaikki arvot vaiheessa 2 on kopioitu, siirretään kurssien tiedot taulukkon vasemmalle puolelle. Tämä helpottaa uusien arvojen lisäämistä myöhemmin, sillä solut eivät ole enää tiellä. Siirtäminen vaatii pari liikettä:

  • ensin kopioidaan ja liitetään aiemmin lasketut solut siten, että vain niiden arvot jäävät taulukkoon. Tämä onnistuu painamalla Ctrl + Alt + V ja valitsemalla ”Paste Values”, tai kopioimalla ensin ja valitsemalla Paste Values jälkikäteen.

Screen Shot 09-25-14 at 09.56 AM

  • Tämän jälkeen valitaan juuri liitetyt arvot ja siirretään ne taulukon vasempaan reunaan. Tämä onnistuu valitsemalla sarakkeet, painamalla Ctrl + X , klikkaamalla ensimmäisen sarakkeen kohdalla oikella ja Insert Cut Cells.

Screen Shot 09-25-14 at 09.56 AM 001

Lopuksi voit poistaa alkuperäiset solut, joissa on vielä kaavat.

Vaihe 4: Jaa teksti sarakkeisiin

Koska arvosanat, lukumäärät ja osuudet olivat samassa solussa, välilyönneillä eroteltuina, voit jakaa niiden arvot sarakkeisiin Text to Colums-työkalulla. Käytä erottimena välilyöntiä ja paina Finish. Arvot ovat omissa soluissaan. Jos numerot ovat muotoiltu siten, että niiden sisällä on välilyönti (esimerkiksi 2 100), niin kiroa taulukon laatija ja korjaa virheet käsin.

Screen Shot 09-25-14 at 09.52 AM

Vaihe 5: Korjaa prosentit prosenttiarvoiksi

Prosentit korjataan prosenttiarvoiksi luomalla uuteen sarakkeeseen niiden arvo/100 ja  kopioimalla tämän uuden sarakkeen arvot vanhan päälle.

Screen Shot 09-25-14 at 10.00 AM

Vaihe 6: Poista turhat ja epämääräiset arvot taulukosta

Tässä vaiheessa kaikki taulukon arvot ovat periaatteessa kunnossa, mutta niiden joukossa on paljon sekalaisia arvoja. Voit poistaa ne siten, että valitset turhat rivit ja painat Deleteä.

Tätä vaihetta nopeuttaa huomattavasti, jos epämääräiset arvot ovat kaikki samassa paikassa. Kikkailemalla Sort-työkalun kanssa saat järjestettyä ne samaan kohtaan. Esimerkissä esimerkiksi sarake Lukumäärä voidaan järjestää laskevien arvojen mukaan, jolloin taulukon pohjalle jää ne rivit, joissa arvo on tyhjä. Voit myös poistaa hypoteesien vastaiset havainnot tässä vaiheessa (vitsi vitsi).

Screen Shot 09-25-14 at 10.01 AM

Screen Shot 09-25-14 at 10.03 AM

Lukumäärämän mukaan järjestäminen jätti taulukkoon vain todellisia arvoja.

Vaihe 7: Poista duplikaatit

Kriittisesti tietoon suhtautuvat huomasivat heti ylemmästä taulukosta, että Excelistä löytyy sellainen näppärä työkalu, kuin Remove Duplicates. Tämän avulla taulukossa mahdolliset kaksi kertaa ilmenevät rivit voidaan poistaa. Voit valita sarakkeet, joiden mukaan duplikaatin tunnistaminen tapahtuu, jolloin esimerkiksi työkalu jättää huomioimatta mahdolliset erot ajassa.

Ai niin, muistithan antaa sarakkeille nimet, sillä ei tässä mitään luolamiehiä enää olla!

Screen Shot 09-25-14 at 10.14 AM

Vaihe 8: Lisätään taulukkoon oppiaineiden koodit

Koska kaikki kurssikoodit ovat samanmuotoisia, tämä on helppo tehtävä. Ensimmäiset neljä merkkiä saat LEFT-kaavalla (suomeksi VASEN):

=LEFT(solun sijainti;4)

Muista taas kopioida ja liittää vain arvot, niin suuren taulukon käsittely on nopeampaa!

Screen Shot 09-25-14 at 10.17 AM

Vaihe 9: Taulukko on valmis!

Screen Shot 09-25-14 at 12.58 PM

Näin helposti se kävi! Tällä kertaa taulukon muotoiluun tarvittiin näitä työkaluja, ensi kerralla ehkä muita. Esimerkiksi VLOOKUP tulee tarpeeseen, mikäli taulukkoon täytyy hakea arvoja toisesta taulukosta. Joskus taulukko ei näytä aluksi ollenkaan järkevältä, mutta usein saat sen käsiteltävään muotoon pienellä kikkailulla!

Tämän jälkeen voit muodostaa taulukon perusteella esimerkiksi Pivot-taulukolla, jota tullaan käsittelemään seuraavassa postauksessa.

Avoimet kurssit:

Vastaa

Sähköpostiosoitettasi ei julkaista.