Excel - Datan käsittely

Tietojen tarkistaminen

Kun tehdään erilaisia tiedonsiirtolomakkeita, on hyvä tarkistaa tietoja, jotta syötetyt tiedot menevät kerralla oikein. Tietojen tarkistamisen tarkoituksena on siis, että viedään vain oikeanlaista tietoa.

Tietojen kelpoisuuden tarkastaminen löytyy Tiedot -välilehdeltä kohdasta "Datatyökalut".

Esimerkki

Halutaan listata kaikki alle 2 vuotiaat hiehot, eli jotka ovat syntyneet 30.3.2018 jälkeen. Tietojen tarkistaminen -työkaluun voimme kirjata kaikki syntymäpäivät, ja ohjelma näyttää onko kyseinen hieho alle 2 vuotias.

Ensiksi täytetään asetukset -välilehti haluamillamme ehdoilla, eli syötetty päivämäärä saa olla sama tai suurempi kuin 30.3.2018.

Seuraavaksi sanoma -välilehdelle mitä haluamme info -taulun meille kertovan halutusta päivämäärästä.

Viimeiseksi vielä mitä haluamme virhesanoman olevan, kun laitamme sellasen päivämäärän, jota emme halua.

Nyt saimme valitsemamme solun viereen info -taulun, joka kertoo mitä päivämääriä solussa haetaan. Taulua voi siirtää haluamaansa paikkaan hiiren vasemmalla näppäimellä.

Jos kirjoitamme soluun päivämäärän, joka on ehtojemme ulkopuolelta, saamme virhesanoman.

Päivämäärän lisäksi Tietojen tarkistaminen -työkalulla voi tarkistaa kokonaislukuja, desimaalilukuja, luetteloita, aikaa ja tekstin pituutta. Siihen voi asettaa myös oman ehdon.

Välisumma- funktio

Välisumma- funktiota voidaan käyttää pystysuunnassa sekä tietosarakkeissa. Välisumma-funktiota käytetään silloin, kun ei voi käyttää summa- funktiota.

Välisumma- funktiolla on omat arvonsa, jota voi laskea. Funktionumero kertoo, laskeeko funktio piilotetut arvot vai ei. Funktionumerot 1-11 laskee piilotetut arvot, kun taas 101-111 ohittaa piilotetut arvot.  

Funktiota voidaan käyttää suodatus toimintona, kun esimerkiksi halutaan tietää suuresta tietokannasta jokin tietty osuus.

Esimerkki suodatuksesta

Suodatuksella voidaan valita jokin tietty alue jota funktio ei ota huomioon. Suodatuksen saa käyttöön tiedot- välilehdeltä ja sieltä valinta 'suodata'. Tämän jälkeen voi valita haluaman alueen, minkä haluaa suodattaa. Tässä esimerkissä otin rastin pois 1 ja 25 kohdalta. Jolloin funktio kertoi muiden tuotteiden yhteismäärän

Välivaihe kuva funktiosta. 

Tässä kuvassa on käytetty suodatinta joka poisti käytöstä 1 ja 45. Jolloin saadaan muiden tuotteiden määrä yhteensä. 

Pivot -taulukoiden perusteet

Pivot -taulukkoa voidaan käyttää tietojen yhteenvetoon, analysointiin, tutkimiseen ja esittämiseen. Pivot -taulukon avulla voi tehdä nopeasti ja helposti yhteenvedon suuristakin tietomääristä ja listoista. Pivot -taulukon saa luotua "Lisää" -välilehdeltä kohdasta "Pivot-taulukko".

Esimerkki

Meillä on luettelo kaikista Jyväskyläläisen ratsastuskoulun hevosista, johon on kirjattu hevosten rodut, nimet, sukupuolet, värit ja painot. Haluamme tehdä taulukoita eri ominaisuuksien mukaan. Käytämme tähän Pivot-taulukkoa.

Ensiksi haluamme eri rotuisten hevosten painojen keskiarvot. Menemme Lisää -välilehdelle ja painamme kohtaa "Pivot-taulukko". Tarkistetaan, että taulukon alue on koko listamme ja painetaan OK.

Alla on näkymä jonka saamme. Oikeassa reunassa näkyy listamme otsikot, joita laitamme oikeaan alareunaan kohtiin "Riviotsikot" ja "Arvot" haluamiemme tietojen mukaan.

Koska halutaan tietää painot rotujen mukaan, laitetaan "Rotu" -otsikko kohtaan Riviotsikot ja "Paino" -otikko kohtaan arvot.

Nyt Pivot-taulukko laski kyseisten rotujen painot yhteen. Lisätäänpä "Paino" -otsikko toisen kerran kohtaan Arvot, ja painetaan siitä "Arvokentän asetukset", josta saamme muutettua Arvokentän laskentaperusteen keskiarvoksi.

Painetaan sitten OK, ja Pivot antaa meille valmiin taulukon.

Seuraavaksi haluamme painot värien mukaan, eli teemme samat toimenpiteet, mutta vaihdamme riviotsikoihin ”rotu” otsikon tilalle ”väri”.

Pivot -taulukko siis helpottaa ja vähentää työmäärää, sekä nopeuttaa taulukoiden tekemistä.

Suodatus ja erikoissuodatus 

Suodatus  - toiminnan avulla tietoja voi poistaa näkyvistä. Se ei siis varsinaisesti poista mitään tietoja vaan piilottaa ne. Tietojen suodatuksessa suodatettavan alueen tulee olla eheä, eli ilman kokonaan tyhjiä sarakkeita tai rivejä. Jos tällaisia on, suodatus päättyy tyhjään sarakkeeseen tai riviin, eli ei silloin toimi oikein. Kuitenkaan ei haittaa, jos jokunen solu on tyhjä. Suodatus – toiminta löytyy excelissä tiedot – välilehdeltä. Otsikkoriveiltä voi suodattaa esimerkiksi taulukkoa miten haluaa.

Tässä, kuvassa 1 eri henkilöitä ja heidän ammattejaan.

Jos nyt taulukosta haluttaisi valita vain agrologit, tulee valita ’’Ammatti’’ otsikko. Siellä on erilaisia toimintoja, kuten järjestely aakkosten tai värin mukaan. Siellä näkyy myös kaikki ammatit. Jos haluaa esimerkiksi vain agrologit esiin, raksitetaan ammateista vain ’’agrologi’’, jolloin kaikki muut ammatit suodattuu pois. Silloin tulee kuvan 2 näkymä.

Erikoissuodatus

Nämä aiemmin suodatetut tiedot saadaan myös kopioitua, jolloin ne suodatetut rivit voidaan poimia erikseen. Harjoitusvideolla käydään läpi miten tämä tapahtuu, siinä myös suodatus tapahtuu agrologi-ammatin mukaan. 

Huomioita!

- Muista oikeinkirjoitus

- Kun erikoissuodatus valitaan lisäasetuksista, laita kohdistin ensin tietoalueelle, josta tietoja haetaan ja suodattaa, jotta erikoissuodatus löytää tämän alueen.

- Tarkista vielä, onko solualue varmasti oikea

- Ehtoalueelle maalataan vain ne rivit, joissa on ehtoja eli ei tyhjiä  

- Kopio – kohtaan napsautus siihen soluun, jonne tiedot liitetään

- Nyt koska nämä tiedot on kopioitu, ei ne häviä tietoalueelta vaan näkyvät edelleen sielläkin

- Tätä kopiointia ei voi suoraan kopioida eri tauluun, se tehdään yhdessä makron kanssa

Excel - Makrot

Makrot

Makroja käytettään, kun tehdään toimintoja useaan kertaan tiettyä työvaihetta taulukkolaskelmassa. Makroja käyttämällä tämä toimenpide voidaan automatisoida. Makrot on etukäteen päätetty komentojono, joka automatisoi toistuvan tehtävän.

Makrot löytyvät Excelissä valintanauhasta ’kehitystyökalut’ välilehdeltä. Jos tämä ’kehitystyökalut’ välilehti ei näy valintanauhallasi voit lisätä sen klikkaamalla hiiren oikealla napilla valintanauhan päällä, jolloin tulee valikko. Valitse ’mukauta valintanauhaa’. Voit lisätä rastilla ’kehitystyökalut’ oikealta puolelta valikosta.

Makroja tehdessä tulee työkirja tallentaa makroja hyväksyvään muotoon. Tallennusmuotona käytetään ’Excel-työkirja (makrot käytössä) (*.xlsm)’.

Yksinkertaisen makron tekeminen

Vie kohdistin työkirjassa kohtaan johonkin kohtaan. Mene ’kehitystyökalut’ välilehdelle ja valitse ’tallenna makro’. Valitse nimi makrolle sekä tee kuvaus, missä kerrot mitä makro tekee. 

Makron tiedot

Tämän jälkeen, kun olet painanut ok, tulee olla huolellinen, koska makro tallentaa kaikki toimintasi ok panikilleen jälkeen. Makro ei kuitenkaan tallenna aikaa. Tässä esimerkissä valitsin ok painikkeen jälkeen C4 solun ja kirjoitin siihen testi, tämän jälkeen valitsin valintanauhasta ’lopeta tallennus’. 

Kaikki tehdyt makrot löytyvät kehitystyökalu välilehdeltä ja valintanauhasta löytyy makrot- kohta. 

Valitse makrojen lähde kohtaan: Tämä työkirja. Makroa voidaan muokata/tarkastella kun valitaan yllä olevasta valinnasta haluttu makro sekä oikealta valitaan 'muokkaa' vaihtoehto. 

Tässä kuvassa on makron koodi.

Tarvittaessa koodia voidaan kopioida. Esimerkiksi jos on useampi makro niin toisesta makrosta voi siirtää kopiomalla, jonkin toiminnon toiseen makroon. 

Lomakeohjausobjektit

Lomakeohjausobjektit löytyvät kehitystyökalu välilehdeltä valintanauhakohdasta ’lisää’. 

Esimerkki täydennys- ja tyhjennys painikkeesta.

Valitse kehitystyökalut välilehti.

Valitse ’lisää’ kohdasta painike objekti. ja luo painike työkirjaan.

Vie kohdistin johonkin soluun ja valitse valintanauhasta ’tallenna makro’. Tämän jälkeen kirjoita makron nimi ja aloita makron tallennus ok painiketta painamalla. Kirjoita tekstiä valitsemiisi soluihin ja paina lopuksi lopeta tallennus valinta nauhasta.

Sen jälkeen luo toinen painike sekä luo toinen makro, joka tyhjentää aikaisemman makron. Tämän painikkeen nimeksi voi laittaa esimerkiksi ’tyhjennä’. Kun viet hiiren painikkeen päälle ja klikkaat hiiren oikealla napilla saat lisättyä haluamat makrot painikkeisiin. 

Tyhjennä painike poistaa numerot ja painike palauttaa ne. 

Esimerkki yhdistelmäruudun käytöstä

Tässä esimerkissä on laitettu paikkakunnittain eri tapahtumia. Yhdistelmäruutua käytetään tässä löytämään millä paikkakunnalla järjestetään mikäkin tapahtuma.

Luo yhdistelmäruutu. Sen jälkeen klikkaa hiiren oikealla napilla ruutua, jolloin tulee valikko, josta valitaan ’muotoile objektia’. Syöttöalueeseen maalataan halutut tiedot. Niin kuin alla olevassa kuvassa. Solulinkkiin valitaan solu, tässä valitsin G4. 

Tämän jälkeen yhdistelmäruutu näyttää taulukon id numeroita. Joten muokataan indeksillä, että       yhdistelmäruutu näyttää missä paikkakunnalla tapahtuma on. Kirjoitin yhteen soluun paikkakunta ja sen perään tein indeksi kaavan, johon saa näkyviin halutun tapahtuman paikkakunnalle. Alla kuva kaavasta.  

Tavoitteen haku

Tavoitteen haku -toimintoa voidaan käyttää, jos tiedetään minkä tuloksen haluaa kaavasta, mutta ei ole varma, miten sen saa aikaiseksi.

Esimerkkitehtävä

Olet ostamassa hevosta, jonka haluat maksaa osamaksulla. Tiedät paljon hevonen maksaa, kuinka pitkään haluat sitä maksaa ja kuinka suurina summina pystyt sen maksamaan. Tavoitteen haku -toiminnolla pystyt selvittämään, millaisen koron tarvitset osamaksullesi, jotta ehdot täyttyvät.

Kirjoita ensiksi tarvitsemasi otsikot, ja tiedot jotka jo tiedät.

Seuraavaksi lisää kaava, jolle sinulla on tavoite. Tässä tapauksessa käytämme "Maksu" -funktiota.

Koska kohdassa B3 ei ole arvoa, Excel olettee korkoprosentiksi 0%. Korkoprosentti halutaan selvittää Tavoitteen haku -toiminnolla, joten soluun B4 tullut luku jätetään vielä tässä vaiheessa huomiotta.

Seuraavaksi käytetään Tavoitteen haku -toimintoa. Se löytyy välilehdeltä "Tiedot" kohdasta "Entä-jos-analyysi" ja sieltä alasvetovalikosta "Tavoitteen haku". "Määritä soluun" -kohtaan annamme solun, joka sisältää kaavan, jonka haluamme ratkaista. Tässä tapauksessa B4. "Tavoitearvo" -kohtaan kirjoitamme sen summan, mitä haluamme aina kerralla maksaa. Tässä esimerkissä se on 2000€. Kirjoitetaan kohtaan siis -2000. Luvun täytyy olla negatiivinen, sillä se edustaa maksua. "Muuttamalla solua" -kohtaan annamme sen solun, jonka haluamme muuttaa. Tässä esimerkissä se on B3.

Painetaan OK, ja muutetaan vielä tulos %-muotoon

Suojaus

Makroihin liittyy makrosuojaus. Makrosuojaus löytyy Excelistä "Kehitystyökalut" välilehdeltä kohdasta "Makrosuojaus". Oletuksena asetuksissa on valittuna "Poista käytöstä kaikki makrot ja ilmoita". Makroissa on hyvä olla makrosuojaus, sillä niillä voi saada myös paljon haittaa aikaan, muunmuassa makroviruksia on ollut liikkeellä. Makrojen käytön helpottamiseksi ja jos avaat vain tiedostoja joiden alkuperästä olet varma, voit valita kohdan "Ota käyttöön kaikki makrot". Jos olet avaamassa tiedostoa, jonka alkuperästä et ole aivan täysin varma, ota makrot pois käytöstä.

Excel - Datan käsittely

Datan kerääminen Formsin avulla 

Office 365 valikosta löytyy Forms-ohjelma. Sen avulla voi luoda ja analysoida erilaisia kyselyjä tai tutkimuksia, esimerkkinä tapahtumaan ilmoittautumiskysely tai gallup. Sillä voi myös tehdä tietovisoja, jossa on pistetytysominaisuus. Formsin lomake – toiminto on monipuolinen, sinne voi tehdä valmiita pohjia sekä kerätä monipuolisesti erilaista tietoa, ja sitä voi käyttää myös kännykän avulla. Tehdyn kyselyn tms. voi jakaa jakolinkin avulla. Linkki on usein melko pitkä, joten sitä voi lyhentää esimerkiksi bitly.com tai urly.fi – sovelluksen kautta, jolloin esimerkiksi urly.fi-sovellusta käyttämällä jakolinkin osoite kopioidaan ja liitetään sivuston osoite-kenttään, osoite lyhennetään ja lyhennetyllä osoitteella pääsee samaan paikkaan. Sivusto tekee samalla myös QR-koodin eli ruutukoodin linkille. Forms kyselyn tulokset tallentuvat Excel-tiedostoon, jota voi tarkastella. Jos lomakkeessa on tarkasteltavissa numerotietoja, ne ovat usein alun perin tekstitietona ja ne täytyy muuntaa luvuksi, jotta niitä voi esimerkiski laskea.

Kuvassa näkymä, josta pääsee aloittamaan uuden lomakkeen tai tietovisan teon. 

Seuraavassa kuvassa luodaan kyselyä, jossa tiedustellaan osallistumista seminaariin. Kyselyä voi muokata monin eri tavoin, esimerkiksi luomalla aloitus- ja lopetuspäivämäärän, lisäämällä vaihtoehtoja, tuottamalla vapaata tekstiä, valitsemalla ketkä kyselyyn voivat vastata ja niin edelleen. 

Kun vastauksia on tullut, se näkyy seuraavassa kuvassa nähtävin tavoin. Kuvassa näkyvä ''avaa Excelissä'' näyttää tulokset Excel-taulukossa, missä niitä voi tarkastella monipuolisesti. Tätä käsitellään seuraavaksi. 

Joukkofunktioiden käyttö

Excelissä esimerkiksi Formsin avulla kerättyjä tietoja voi käsitellä erilaisten funktioiden avulla. Laske-funktio laskee solujen lukumäärän, ei solujen arvoja. Laske a – funktio laskee ne solut, jossa on tietoa, eli esimerkiksi numero tai kirjain. Laske tyhjät – funktio laskee ne solut, joissa tietoja ei ole. Käsiteltävät solut kannattaa valita painamalla kyseistä saraketta, jota haluaa tutkia à Ctrl + shift pohjaan ja nuolinäppäimiä käyttämällä.

Laske.jos.joukko – funktio laskee tietyt solut, jotka on määritelty tiettyjen ehtojen avulla eli se laskee kuinka monesti ehdot täyttyvät. Ensin määritetään alue, jota tarkastellaan (esimerkiksi taulukon kaikkien lehmien rodut) ja sen jälkeen ehto- alueelle määritetään ehdot, eli esimerkiksi halutaan laskea kaikkien lehmien roduista Ayrshire-rotuisten lehmien määrä.

Funktioiden avulla voidaan myös laskea ehdollisia summia, eli summa.jos.joukko. Tämä funktio toimii muuten samoin kuin laske.jos.joukko, mutta summa.jos.joukko laskee summia, joilla on useita ehtoja. Esimerkkinä voidaan tarkastella laajasta lypsytilataulukosta juuri niitä lypsytiloja, jotka sijaitsevat Etelä-Suomessa ja joiden lehmämäärät ylittävät yli 30 lehmää.

Harjoitustehtävä

Yllä olevan seminaariin osallistumiskyselyn pohjalta tarkastellaan Excelissä saatuja vastauksia. Laske vastauksista laske.jos.joukko - funktion avulla, kuinka moni aikoo osallistua seminaariin. 

Harjoituksen purku: Kiinteä merkkijono - eli tässä tapauksessa vastaus kyllä - on kätevämpi kaavaan kirjoittamisen sijaan ilmoittaa siten, että sille tehdään syöttökenttä.

Kuvassa Kyllä-valinnalle on tehty syöttörivi solussa D12.

Aloitetaan funktio: = ja LASKE.JOS.FUNKTIO, joka ilmestyy kenttään sitä kirjoitettaessa. Valitaan tarkasteltava alue eli Kyllä ja Ei vastaukset maalaamalla alue. Sen jälkeen ; -merkki, jonka jälkeen valitaan solu D12 eli ''Kyllä''. Sulku kiinni ja enter. 

Vastaukseksi tulee 4. 

Nyt jos halutaan tarkastella sitä, kuinka moni ei osallistu seminaariin, vaihdetaan vain solun D12 eli ''Kyllä'' paikalle ''Ei''. Vastaus tulee näkyviin. 

Ehdollinen muotoilu

Taulukkoja voi myös visualisoida/korostaa. Valitaan aloitusvälilehdeltä kohta à ehdollinen muotoilu, josta voi valita esimerkiksi pienempi kuin jokin tietty luku, jolloin valitun alueen pienempi kuin jokin – luvut korostuvat valitulla värillä. Alueille voi myös tehdä tätä kautta muitakin korostuksia, kuten kuvakkeita. Nämä toiminnot selkeyttää ja korostaa arvoja, jotka ovat esimerkiksi erityisesti tarkastelussa.

Aineiston käsittelyssä huomioitavaa

On tärkeää huomioida, että jos Exceliin on tuotu muusta järjestelmästä tietoa, voi näistä aineistoista löytyä muotoiluvirheitä, josta syystä päällepäin näyttävät luvut eivär välttämättä Excelissä lukeudu luvuiksi. Tämä voi johtua siitä, että aineistossa on ollut jokin virheellinen syöte, jonka takia kaikkia numeroita ei voi tulkita luvuksi. Tämän takia, jos numeroita ruvetaan vaikkapa laskemaan yhteen, tulos on väärä, koska kaikkia numeroita ei tulkita luvuksi ja niitä ei silloin lasketa. Tämän voi tarkastaa edellisten funktioiden avulla, esimerkiksi numerotiedoston voi funktioiden avulla tarkastaa, onko tietoja yhtä paljon kuin lukuja. Jos tietoja näyttää olevan esimerkiksi 10, mutta lukuja vain 8, on kaksi numeroarvoa merkitty jotenkin virheellisesti ja näitä Excel ei laske luvuiksi. Tämän voi myös testata ''on luku'' – funktiolla, jolloin se kertoo onko jokin kyseinen arvo oikea luku. Tämä funktio antaa vastaukseksi joko tosi (eli arvo on oikea luku) tai epätosi. Se, että jotakin numeroa ei tulkita luvuksi, voidaan tarkistaa kopioimalla kyseinen numero tai vaikka kaikki numerot, ja liittämällä wordiin. Sieltä voidaan nähdä esimerkiksi merkit, joita Excel ei tunnista, ja siksi ei tulkitse numeroa luvuksi. Nämä merkit voidaan wordissa nähdä ja poistaa, jolloin kun numero viedään takaisin Exceliin, tulisi kaikkien näkyä lukuina.

Excel - taulukko ja Excel - hakufunktiot

Excel - taulukko

Excel taulukko - on toiminto, jossa Exceliin tehdään erillinen taulukko, toisin sanoen myös nimetty tai jäsennetty taulukko.

Taulukko toimii siten, että voidaan ruveta rakentamaan esimerkiksi henkilölistaa. Excel taulukoksi sen saa muutettua painamalla lisää - välilehdeltä lisää taulukko – painiketta. Taulukon luomisen yhteydessä laitetaan raksi kohtaan ’’taulukossa on otsikot’’ jos näin on.

Tämän jälkeen taulukosta tulee muotoiltu ja nimetty Excel- taulukko, kuten alla olevassa kuvassa näkyy. Tämä Excel- taulukko mahdollistaa sen, että tämän taulukon alueeseen voidaan viitata muutenkin kuin solualueilla, esimerkiksi selkokielisillä nimillä. Taulukoita voi nimetä. Excel taulukkoon voi lisätä sarakkeita painamalla oikean puolen sarakkeen kohdalta hiiren oikeaa näppäintä, ja valitsemalla lisää -> taulukon sarake oikealle. Sarakkeelle voi antaa haluamansa nimen. Huom! Kun sarakkeita lisätään, se niin sanotusti sisältää edellisen sarakkeen solumuodon, eli jos edellinen sarake on esimerkiksi prosentti- muodossa, on lisättykin sarake siinä muodossa.

Kun sarakkeessa olevia lukuja ruvetaan laskemaan yhteen, tulee kaavaan näkyviin hakasulut, tätä kutsutaan jäsennetyksi viittaukseksi. Kun kaava hyväksytään, se tekee laskutoimituksen automaattisesti koko taulukkoon.

  

Kun taulukosta painaa hiiren oikealla näppäimellä, menee Taulukko kohtaan ja valitsee Summarivin, taulukon alariville tulee Summarivi, joka laskee yhteen taulukon kaikki palkat. Sen avulla voi myös tarkastella muitakin arvoja, esimerkiksi pienimmän ja suurimman arvon ja keskiarvon.  Esimerkki taulukossa voidaan esimerkiksi tarkastella lisien keskiarvoa kyseisen toiminnan avulla, joka näyttää olevan 290,82€. Mikäli taulukkoon pitää lisätä uusia henkilöitä ja arvoja tai vaihtoehtoisesti poistaa, muuttuu lopputulos automaattisesti sen mukaan.

Alueen nimeäminen

Jos esimerkiksi lisiä halutaan laskea useasti yhteen ja sitä tarvitaan monessa paikkaa, voidaan alue nimetä. Alue valitaan (jos alueita on useissa paikoissa, painetaan Ctrl pohjaan ja valitaan kaikki). Kaavat – välilehdeltä löytyy ’’määritä nimi’’ ja valitaan selvä nimi, jotta tiedetään mitkä luvut on kyseessä (tässä tapauksessa esimerkiksi ’’Lisät’’). Laajuus – kohtaan kannattaa laittaa työkirja, sitten valitaan OK. Nyt kun kaavaa rupeaa kirjoittamaan, tulee nimetty alue pudotusvalikkoon, jolloin aluetta ei tarvitse enää erikseen valita. Nimetyt alueet tulee näkyviin painamalla (Fn ja) F5.

Phaku - funktio 

Phaku-funktio on niin sanottu pysty haku- funktio. Phaku- funktiossa taulukon pitää olla nousevassa järjestyksessä (esim. 1à4, EI 4à1).

Phaku funktiolla on neljä argumenttia (tietoelementtiä), joista hakuarvo, taulukkomatriisi ja sarakeindeksinumero on pakollisia. Niiden lisäksi on aluehaku. P-funktion hakuarvo on arvo, jota etsitään. Taulukkomatriisi tarkoittaa sitä oletettua paikkaa, mistä etsittävissä oleva arvo löytyy (jokin tietoalue, ei otsikkotietoja mukaan). Phaku lähtee valitun alueen vasemmasta reunasta etsimään hakuarvoa. Sarakeindeksinumero etsii valitulta alueelta sen, monennessa sarakkeessa haluttu tieto on. Phaku-funktiossa on myös aluehaku, joka on joko tosi tai epätosi. Kun aluehakuun kirjoitetaan tosi, hakufunktio palauttaa aina jonkin, lähimmäksi osuvan arvon, joka ei aina ole oikea arvo. Kun kirjoitetaan epätosi, hakufunktio palauttaa oikean arvon, ja jos ei arvoa löydy, tulee virheilmoitus (#puuttuu). Yksinkertainen esimerkki:  haetaan numeroa 4 ja aluehakuun kirjoitetaan tosi. Jos numeroa 4 ei löydy, aluehaku palauttaa lähimmäksi osuvan arvon eli esimerkiksi 3. Jos taas aluehakuun kirjoitetaan epätosi, ei se anna mitään arvoa vaan virheilmoituksen. Sen takia epätosi- vaihtoehto on usein hyvä vaihtoehto.

Kyseisen toiminnan voi tehdä ohjattuna toimintona, jolloin kaavat välilehdeltä löytyy ’’lisää funktio’’. Valitaan ’’kaikki’’ ja etsitään PHAKU, ja kirjoitetaan sarakkeisiin tarvittavat tiedot. Kaavan voi myös kirjoittaa käsin.

Opetusvideo

Listassa on lehmien tietoja. Opetusvideolla etsitään tietyn lehmän nimi; lehmän, jonka korvanumero on 354.

Kirjoitetaan kaava tässä tehtävässä suoraan soluun, eli ei käytetä ohjattua toimintoa.

Vhaku-funktio

Vhaku- funktio toimintoa käytetään samaan tapaan kuin P-hakua, mutta V-haussa käytetään rivi-indeksiä, kun halutut kohteet ovat vaakasuunnassa. Vhaku- funktiossa hakuarvojen tulee olla vasemmalta oikealle nousevassa järjestyksessä. Tärkeä huomio on, että funktio ei hae lähintä numeraalista arvoa vaan lähimpänä prosenttiarvoa olevaa hakuarvoa.

Vhaku- funktiossa kaava on hyvin samanlainen, kuin Phaku- funktiossa. Kaavojen erona on esimerkiksi "rivi_indeksi" ja lopussa käytettävä "Tosi"- vaihtoehto (lähes tarkka vastine). Jos kaavaan laittaa "Epätosi" niin vastaukseksi tulisi soluun "#Puuttuu". Kaavaa kirjoittaessa kannattaa lukita f4- napilla "taulukko_matriisi"- kohta, jotta kaavan saa kopioitua täyttökahvalla. Huomioi myös, että "taulukko_matriisi"- kohtaa maalatessa  ei tule otsikoita maalatulle alueelle.

Kuva kaavasta

                                                                                Kuva taulukosta 

Vastine-funktio

Vastine-funktio etsii määritettyä kohdetta määritettyjen solujen alueesta ja antaa vastaukseksi kyseisen kohteen suhteellisen sijainnin alueessa. Vastine-funktiota voidaan käyttää siis Haku-funktion sijasta silloin kun halutaan tietää kohteen sijainti eikä itse kohdetta.

Esimerkki Vastine-funktion käytöstä:

Sinulla on lista kaikista Tarvaalan Biotalousinstituutin 150 opiskelijan nimistä ja puhelinnumeroista. Sinun täytyy soittaa tietylle opiskelijalle tärkeästä kiireellisestä asiasta, mutta nimet eivät ole aakkosjärjestyksessä ja listan läpikäyminen on aikaa vievää. Vastine-funktion avulla saat tietää kohteen sijainnin, jolloin sen löytäminen on nopeampaa.

Ensiksi valitse solu listan ulkopuolelta johon haluat vastauksesi tulevan. Sitten mene "kaavat" välilehdelle, sieltä "haku" ja "vastine". Sinulle aukeaa "funktion argumentit" -ikkuna. "Hakuarvo" kohtaan tulee kohde jota etsit (tässä tapauksessa opiskelijan nimi jota etsit) ja "Haku-matriisi" kohtaan alue jolta kohdetta etsit.

Jo tässä kohtaa näemme, että kaavan tulos on 14, joka tarkoittaa sitä, että nimi Valtteri jota etsimme, on rivillä 14. Vielä painamalla "OK"-näppäintä, saamme numeron 14 näkyviin siihen soluun, johon kirjoitimme funktiota.

Indeksi-funktio

Indeksi-funktiota käytetään kuten haku-funktiota, mutta toisin kuin haku-funktiossa, jossa kohteen on oltava pysty- tai vaakarivillä, indeksi-funktio etsii kohteen taulukosta tai alueelta.

Esimerkki Indeksi-funktion käytöstä:

Sinulla on taulukko rehuohran tonnihinnoista nyt, sekä vuoden 2020 termiinihinnoista kaupungeittain. Haluat tietää mikä on rehuohran tonnihinta Loimaalla kesä-heinäkuussa.

Ensiksi valitaan solu johon vastaus halutaan. Kirjoitetaan =indeksi( , jolloin excel ehdottaa kahta eri tapaa käyttää indeksi-funktiota.

Koska haluamme tietää tietyn solun yhdeltä tietyltä alueelta, käytämme "viittaus" tapaa. Eli seuraavaksi funktioon kirjoitetaan taulukkomme alue: C5:F10.

Sitten kirjoitamme rivinumeron, jolla haluamamme tieto taulukossa sijaitsee. Vaikka "Loimaa" lukee Excelin rivillä 7, on se taulukkomme rivillä 3.

Sitten kirjoitamme sarakenumeron, jolla haluamamme tieto taulukossa sijaitsee. "Nyt €/tn" on taulukkomme ensimmäinen sarake, "Huhti-Touko" toinen, jne. Eli "Kesä-Heinä" sijaitsee sarakkeessa 3.

Painetaan "Enter" jolloin saamme kirjoittamaamme soluun vastauksemme.

Hinnat otettu Hankkija.fi Viljojen päivän hinnoista.

Excel - Loogiset funktiot

Käsiteltävänä aiheena on sisäkkäiset Jos-lauseet, Ja-, Tai-funktiot.

Jos- funktio 

Jos- funktiolla voidaan tehdä Excelissä vertailua arvon ja odotetun arvon välillä. Sen avulla voidaan vertailla myös tekstiä. Jos- funktion tulos voi olla esimerkiksi tosi tai epätosi. Tilanteesta riippuen tuloksen voi ilmaista muullakin tavalla, esimerkiksi ’’tulos on viitearvoissa’’ ja ’’tulos on alle viitearvon’’.

Seuraavan esimerkin avulla nähdään yksi tapaus, jossa voidaan hyödyntää Jos-funktiota. 

Seuraavassa esimerkissä on koottu Exceliin opiskelijoiden tenttien arvosanoja. Tentti on hyväksytty, jos pistemäärä on 40 pistettä tai yli. Jos pistemäärä on alle 40, tentti on hylätty.

Jos- funktion saa esille kaavat- välilehdeltä, tai kirjoittamalla ruutuun = merkin ja kirjoittamalla sen jälkeen jos, jolloin Excel ehdottaa funktiota.

Seuraavaksi tehdään vertailu, jossa siis vertaillaan opiskelijoiden saatuja pistemääriä hyväksyttyyn pistemäärään. Ensin testataan, onko C5 eli Matin saama pistemäärä pienempi kuin D16 eli tentin hyväksytty pistemäärä. Jos vertailu on totta, tentti on hylätty, jos se ei ole totta, tentti on hyväksytty.

Kun funktioon lisätään tentin hyväksytty pistemäärä, solu D16, se tulee lukita näppäimellä F4, jotta kun kaavaa kopioidaan seuraaville oppilaille, tämä soluosoite ei muutu.

Puolipiste toimii erotinmerkkinä. Lainausmerkit laitetaan aina tekstin alkuun ja loppuun.

Saadaan Matin tulos, joka on hyväksytty. Kaava voidaan kopioida kaikille opiskelijoille ja näin nähdään jos-funktion avulla, kuka on saanut hyväksytyn tai hylätyn.

Sisäkkäiset Jos -funktiot 

Jos halutaan erotella ja testata samanaikaisesti useita eri kriteerejä, tulee käyttää sisäkkäistä jos -funktiota. Sillä saadaan esimerkiksi selville, onko tulos pienempi, suurempi, vai yhtä suuri.

Seuraavassa esimerkissä tarkastellaan poikimiskertoja. Tilalla on asetettu tavoiteltavaksi poikimiskerraksi 3 kertaa/ lehmä, ja tuloksia vertaillaan tässä taulukossa. Tarkastellaan, onko lehmä päässyt tavoitemäärään, onko se jäänyt alle tavoitteen tai onko se mennyt yli tavoitteen.

Jälleen tavoiteltu poikimakerta (3) lukitaan, koska sitä solua käytetään kaikkien lehmien kohdalla. Funktion alku on samankaltainen kuin yllä olevassa Jos- funktiossa. Tässä tapauksessa kuitenkin erotellaan sen jälkeen toisella jos-funktion käytöllä, onko tulos yhtä suuri kuin tavoitearvo,  vai onko tulos suurempi kuin tavoitearvo.
















Tuloksesta nähdään, että osa lehmistä on päässyt tavoitteeseen, eli tulos on yhtä suuri kuin tavoitemäärä. Osa lehmistä on jäänyt alle tavoitteen ja muutama on päässyt yli tavoitteen.

JA-funktio

JA- funktiolla tutkitaan, toteutuuko jotkin tietyt ehdot kaikki kerralla, kuten tässä tapauksessa tutkitaan, että onko kaikki vaiheet kerralla alle 4. JA-funktiolla voidaan esimerkiksi tutkia, että taulukossa olevien tuotteiden valmistusaika on alle < 4min. JA-funktio antaa tuloksen TOSI (true) tai EPÄTOSI (false). 

 JA-funktio saadaan tehtyä solussa = merkin jälkeen, kun kirjoittaa soluun = merkin jälkeen JA, ohjelma todennäköisesti tarjoaa JA-funktiota itse. Sen jälkeen valitaan haluttu solu jota verrataan tässä tapauksessa numeroon 4. Solu jossa on verrattava kohde (kuten tässä 4) kannattaa lukita f4 näppäintä käyttäen, jolloin ei tarvitse tehdä kaavaa jokaiseen kohtaan erikseen.

Täyttökahvalla saadaan maalattua samalla kaavalla myös muihin haluttuihin kohtiin tulos. 

Lopuksi nähdään kaavan tulokset. Läpimenoaikaan on tullut joko TOSI tai EPÄTOSI. Taulukosta voi nähdä, että osat 1,4, 6 ja 7 ovat TOSI, kun taas loput ovat EPÄTOSI.

TAI-funktio

Tai -funktiolla tutkitaan, toteutuuko joku annetuista ehdoista.

Seuraavassa esimerkissä on koottu Exceliin opiskelijoiden tenttien ja kurssitehtävien pistemääriä. Opiskelijan on saatava joko tentistä vähintään 30 pistettä tai kurssitehtävistä vähintään 50 pistettä päästäkseen kurssista läpi.

Tai -funktion saa esille kaavat -välilehdeltä, tai kirjoittamalla ruutuun = merkin ja kirjoittamalla sen jälkeen tai, jolloin Excel ehdottaa funktiota. Saadaksemme esimerkkimme toimimaan, on tai -funktiota käytettävä jos -funktion kanssa. Se saadaan kirjoittamalla ruutuun = merkin jälkeen jos, jonka jälkeen laitetaan sulku auki, ja tämän jälkeen kirjoitetaan tai. Seuraavaksi valitaan solu, josta halutaan tietää täyttääkö se jomman kumman ehdoista.

Jos ehto on tosi, tulee tulokseksi TOSI ja jos ei, niin EPÄTOSI.