Haku-funktiot 30.1.2018

PHAKU-funktio

PHAKU=pystyhaku. Sillä voidaan hakea tietoa pystysuunnassa rivi riviltä ylhäältä alaspäin. Tällä funktiolla voidaan hakea tiettyä tietoa tietystä asiasta. Funktio ikään kuin hakee ylhäältä alaspäin tietoa niin kauan, kuin oikea tieto tulee vastaan ja siirtyy sitten vaaka suunnassa vasemmalta oikealle oikeaan sarakkeeseen, siihen sarakkeeseen, mistä tieto löytyy.

Funktioon liittyvät funktion argumentit, eli tiedot, mitä funktioon tarvitaan (funktion tekijät):

Hakuarvo: On se tieto, mitä lähdetään etsimään. Voi olla esim. henkilönumero, tuotenumero, tuotteen tai asiakkaan nimi. Joku yksilöivä tieto.

Taulukko_matriisi: On se alue, mistä oletamme tiedon löytyvän. Vain varsinainen tietoalue maalataan, ei otsikkoja. Hakutaulukon (se mistä tieto haetaan) tulee olla nousevassa järjestyksessä: esim. nimet aakkosjärjestyksessä tai numerot suuruusjärjestyksessä pienimmästä suurimpaan). Muista lukita hakualue, mikäli tarkoituksena on kopioda kaavaa esim. listassa alaspäin. Jos lukitusta ei tehdä, hakualue vaihtuu, kun kaavaa kopioidaan, eikä homma onnistu. Lukitus tapahtuu f4-painikkeella, "dollarin"-merkeillä. Varmista että dollarin merkit tulevat hakualueen alkusolun ja loppusolun ympärille.

Sar_indeksi_nro: Sarake indeksi numero. Sarake indeksi nro on se tieto, mikä halutaan viedä siihen soluun, johon ollaan kaavaa kirjoittamassa. Hakualue lasketaan vasemmasta oikealle sarakkeittain. Eli jos alueella on kolme saraketta ja tieto olisi vasemman puoleisessa sarakkeessa, tulisi tähän kohtaan luku 1. Jos tieto olisi keskimmäisessä sarakkeessa, tulisi kohtaan luku 2 ja jos tieto olisi oikean puolimmaisessa sarakkeessa, tulisi kohtaa luku 3.

Alue_haku: Vaihtoehdot ovat TOSI tai EPÄTOSI. (ei liity JOS-funktioihin!). Kun valitaan EPÄTOSI, funktio hakee täsmälleen oikean vaihtoehdon. Jos valitaan TOSI, funktio hakee sinne päin, lähinnä olevan vaihtoehdon. Jos tämän kohdan jättää tyhjäksi, tekee funktio TOSI-haun, eli hakee vähän niin kuin sinne päin. Tämä kannattaa muistaa.

Esimerkkikaava 1: =PHAKU(D10;VALUUTAT!B6:D9;3;EPÄTOSI) ( Phaku_valuuttalaskuri-esimerkistä).

Esimerkkikaava 2: =PHAKU(B4;Myyjät!$A$2:$C$30;2;EPÄTOSI) (Phaku_Vhaku-esimerkki).




VHAKU-funktio

Vhaku = vaakahaku. Funktio hakee tietoa vaakasuunnasta vasemmalta oikealle.

Funktion tekijät:

Hakuarvo: tietoa, mitä haetaan

Taulukko_matriisi: Alue, miltä tietoa haetaan. Maalataan alue, ei otsikoita. Muista lukitus, jos tarkoitus kopioida kaava, tällöin hakualue pysyy samana. Hakualueessa tulee tiedot olla nousevassa järjestyksessä, niin kuin PHAKU-funktiotakin käytettäessä.

Rivi_indeksi_nro: Monesko rivi ylös alas-suunnassa hakualueella, jolta tieto löytyy.

Alue_haku: Vaihtoehdot ovat TOSI tai EPÄTOSI. (ei liity JOS-funktioihin!). Kun valitaan EPÄTOSI, funktio hakee täsmälleen oikean vaihtoehdon. Jos valitaan TOSI, funktio hakee sinne päin, lähinnä olevan vaihtoehdon. Jos tämän kohdan jättää tyhjäksi, tekee funktio TOSI-haun, eli hakee vähän niin kuin sinne päin. Tämä kannattaa muistaa.

Esimerkkikaava: =VHAKU(H4;Provisiotaulukko!$B$3:$G$4;2;TOSI) (phaku_vhaku-esimerkistä)

Esimerkissä on alue_haku-kohdassa käytetty TOSI. Tätä voidaan käyttää esim, kun haetaan tiettyjä raja-arvoja. TOSI ei koskaan ylitä rajaa, vaan jos arvo on rajan alapuolella, vaikkakin lähellä sitä, se valitsee yhtä alemman rajan. (esim. provisio-taulukko).


INDEKSI-funktio

INDEKSI-funktiolla on kaksi muotoa: matriisi ja viittausmuoto. Tässä keskitytään vain matriisi muotoon.

INDEKSI-funktiolla voidaan määritellä se kohta taulukosta, mihin halutaan siirtyä. Funktiolla voidaan hakea esim. taulukosta mikä tahansa arvo. Funktiota voidaan esim. käskeä, niin että "siirry 6 riviä alaspäin ja 2 saraketta oikealle ja tuo siinä solussa oleva tieto tiettyyn soluun".

INDEKSI-funktiolta voidaan esim kysyä "mitä on rivillä 6 ja sarakkeessa 2?".

Funktion tekijät:

Matriisi: Hakutaulukko, eli alue, jolta tieto haetaan

rivi_nro: monesko rivi hakualueella (6)

sarake_nro: Monesko sarake hakualueella (2)


Esimerkkikaava:  =INDEKSI(B2:C11;6;2) ( Indeksi_vastine-esimerkki, viimeinen, mikä tunnilla käytiin).


 VASTINE-funktio

Vastine-funktio pystyy palauttamaan tietyn arvon fyysisen sijainnin taulukossa. Sen avulla voidaan selvittää, mistä tietty tieto löytyy.

Hakuarvo: Hakuarvoksi annetaan se tieto, jonka sijaintia etsitään

Haku_matriisi: Se sarake, jolla tieto oletetaan löytyvän. Vaikka hakualueella on useampi sarake, tähän riittää siis vain tuo yksi.

Vastine_laji: Vaihtoehtoina: Pienempi kuin = 1, Tarkka vastine= 0, Suurempi kuin= -1

VASTINE-funktiolta voidaan esim. kysyä: "Millä rivillä on 87645?"

Esimerkkikaava: =VASTINE(87654;C2:C11;0) (Indeksi_vastine-esimerkki, viimeinen, mikä tunnilla käytiin).

Jos hakualueella on kaksi yhtäsuurta suurinta arvoa, vastine-funktio löytää vain ensimmäisen.
Tärkeää on, että ei haeta täsmällisiä tietoja sellaisesta sarakkeesta, missä voi olla samankaltaisia tietoja, esimerkiksi nimiluettelo.

Indeksi- ja vastine funktiota käytetään usein yhdessä. Myös etsittäessä pienintä tai suurinta arvoa, käytetään MAKS tai MIN-funktiota yhdistettynä näihin.

Tässä esimerkkikaavoja:

Suurin luku:=MAKS(C2:C11)

Millä rivillä on suurin luku? 

=VASTINE(MAKS(C2:C11);C2:C11;0)

Maksimi on hakufunktio. Maksimi näkee suurimman luvun. Maksimi funktion jälkeen jatketaan vastine funktio normaalisti loppuun.

Kenellä on kaikista suurin arvo?

=INDEKSI(B2:C11;VASTINE(MAKS(C2:C11);C2:C11;0);1)

Luku yksi kaavan perässä tarkoittaa sarakenumeroa.

Seuraavalla videolla käsitellään PHAKU, INDEKSI JA VASTINE-FUNKTIOT

JOS jonkinnäköisiä funktioita

JOS-funktio

JOS-funktio on niin sanottu apu-funktio, jolla voidaan tutkia ja testata erilaisia asioita, esimerkiksi, onko jokin asia tosi vai epätosi. JOS-funktiolla on siis kaksi eri vaihtoehtoa lopputulemaksi. JOS-funktiolla voidaan vertailla esimerkiksi, onko tietty luku toiseen lukuun verrattuna pienempi, suurempi, eri suuri, suurempi tai yhtäsuuri, pienempi tai yhtäsuuri vai yhtäsuuri.

Kertauksen vuoksi tässä vielä vertailuun käytettävät merkit:

< pienempi kuin
> suurempi kuin
<> erisuuri
>= suurempi tai yhtäsuuri
<= pienempi tai yhtäsuuri
= yhtäsuuri

Selkeyden vuoksi esimerkkikaavoissa käytetään lukuja. Kun kaavaa tekee oikeasti exceliin, kannattaa viitata suoraan soluun, jotta tietoja voi muuttaa muokkaamalla solua, eikä tarvitse erikseen kaavaan muuttaa tietoja.

JOS-funktion kaava kirjoitetaan esimerkiksi näin: =JOS(500>300;"Luku on suurempi";"Luku on yhtäsuuri tai pienempi")

Ensimmäisen puolipisteen jälkeen kirjoitetaan kaavaan se, mitä halutaan funktion antavan tiedoksi silloin, kun asia on tosi ja toisen puolipisteen jälkeen kirjoitetaan, mitä halutaan tiedoksi silloin, kun asia on epätosi. Teksti kirjoitetaan lainausmerkkien sisään. Jos tekstin sijaan käytetään numeroita, lainausmerkkejä ei tarvita.

MAKS-funktio JOS-funktion yhteydessä

MAKS-funktiolla voidaan löytää suurin lukuarvo tiettyjen lukujen joukosta. JOS-funktion yhteydessä  MAKS-funktiolla voidaan selvittää, että ylittyykö esim. joku tietty lukuarvo.

Esimerkkinä voisi olla, että nopeusrajoitus teillä on 80km/h. Lukuarvo olisi tällöin 80. Sitten olisi pidetty kirjaa ohi ajavien autojen nopeuksista ja listattu ne. MAKS funktion avulla voitaisiin selvittää, onko joku ajanut ylinopeutta vai ei, eli löytyykö listauksesta lukuarvo, mikä on suurempi kuin 80.

Kaava voisi mennä esimerkiksi näin : =JOS(MAKS(C3:C7)>80;"Raja ylittyy";"Raja ei ylity")


Liitosmerkki &

&-merkillä voidaan yhdistää kahden solun tiedot yhteen soluun. Esimerkiksi henkilön etunimi ja sukunimi voidaan yhdistää samaan soluun halutussa järjestyksessä viittaamalla niihin soluihin, joissa nimet ovat erikseen.

Kaava voisi mennä esimerkiksi näin: =B4&" "&A4

Lainausmerkit ja niiden välissä välilyönti tarkoittaa sitä, että kahden yhdistettävän asian väliin tulee välilyönti. Jos soluviittausten välissä on vain yksi &-merkki, tulevat solujen tiedot peräkkäin ilman välilyöntiä. Tämä olisi esim. nimien kanssa vähän hölmöä.


Sisäkkäinen JOS-lause

Sisäkkäisten JOS-lauseiden avulla voidaan verrata johtopäätöksiä kolmella eri tavalla. Vaihtoehdot päätöksille voivat olla suurempi, yhtäsuuri tai pienempi.

Esimerkkikaava sisäkkäisestä JOS-lauseesta: =JOS(C4>B4;"Kasvanut";JOS(C4=B4;"Yhtäsuuri";"Pienempi"))

Sisäkkäistä JOS-funktiota käyttäessä muista tarvittava määrä sulkuja! Suomennettuna kaava menisi jotenkin näin "Jos C4 on suurempi kuin B4, luku on kasvanut. Jos C4 on yhtäsuuri kuin B4, luku on yhtäsuuri, muussa tapauksessa se on pienempi."


Jäsennetty viittaus

Jäsennetty viittaus tarkoittaa sitä, että luku on liitetty tiettyyn alueeseen, esimerkiksi taulukontiettyyn sarakkeeseen. Excelin lisää taulukko-toiminnassa on automaattisesti jäsennetty viittaus mukana.


JA-funktio

Ja-funktiolla voidaan jälleen tutkia onko jokin "väittämä" tosi vai epätosi.  JA-funktiota voidaan käyttää silloin, kun vaihtoehtoja, joiden välillä asioita vertaillaan, on useampia. Voidaan olla esimerkiksi valitsemassa lastenvaunuja ja tehdään tuotevertailua eri merkkien välillä. Vaunuilla on esim. kolme eri vaatimusta, joista kaikkien tulee täyttyä, että tuote pääsee valintaprosessissa jatkoon. JA-funktio on tosi vain siinä tilanteessa, jos kaikki ehdot täyttyvät. Muussa tapauksessa se on epätosi.

Esimerkki JA-funktion käytöstä: =JA(C8=X;D8=X;E8=X)

JA-funktiolla siis kysytään, onko jokaisessa solussa esim. X. ja jos on, väittämä on tosi.

JOS-funktiolla voidaan antaa päätökselle selkeäsanaisempi vastaus, esimerkiksi "ehdot täyttyvät" tai "ehdot eivät täyty".

Esimerkki JOS-funktion yhdistämisestä JA-funktioon: =JOS(JA(C8=X;D8=X;E8=X);"Kaikki ehdot täyttyvät";"Ehdot eivät täyty")


TAI-funktio

TAI-funktio on tavallaan JA-funktion vastakohta. Siinä vertaillaan myös eri vaihtoehtoja, mutta asia on silloin tosi, jos yksikin ehdoista tai vaatimuksista täyttyy. Jos mikään ehdoista ei täyty, asia on epätosi.

Esimerkki TAI-funktion kaavasta: =TAI(C17=X;D17=X;E17=X)

Kaavalla siis käytännössä kysytään, onko yhdessäkään solussa X, ja jos on, asia on tosi, jos missään ei ole, asia on epätosi.

Jälleen JOS-funktion yhdistämällä saadaan vastauksesta selkokielisempi.

Esimerkki JOS-funktion yhdistämisestä: =JOS(TAI(C17=X;D17=X;E17=X);"Jokin ehdoista täyttyy";"Mikään ehdoista ei täyty")


LASKE-funktio

Laske-funktiolla voidaan tutkia ja laskea, monessako solussa valitulla alueella on numero.

Esimerkki: =LASKE(C26:E26)

Laske-funktio antaa vastaukseksi luvun.

JOS-funktion yhdistämällä voidaan taas antaa selkokielisempi vastaus asiaan, esim täyttyvätkö ehdot vai ei. Kolmesta ehdosta esim. vähintään kahden on täytyttävä.

Esimerkkikaava:  =JOS(LASKE(C26:E26)>=2;"2 ehtoa täyttyy";"Ehtoja ei täyty riittävästi")

Suomennettuna lause kuuluisi kutakuinkin näin: " Jos ko solujen alueella olevien lukujen määrä on suurempi tai yhtä suuri kuin kaksi, 2 ehtoa täyttyy, muussa tapauksessa ehtoja ei täyty riittävästi.


Videolla näytetään esimerkki JOS- ja sisäkkäisen JOS-funktion käytöstä

Yhteiskäyttö Microsoft Officessa

Tässä päivityksessä esitellään useita Microsoft Officen tiedostojen yhteiskäytön menetelmiä. Käsittelyssä ovat postitus, tietojen upottaminen ja tekstitiedoston avaaminen Excelissä.


Postitus

Wordissa voidaan luoda useita samanlaisia kirjeitä, laskuja, kutsuja tai osoitetarroja personoiduilla tiedoilla ja yksityiskohdilla. Yhteiskäytön ajatus on, että valmiiksi jossakin toisessa tiedostossa olevaa tietoa voidaan käyttää uuden dokumentin yhteydessä sen monistamiseksi useille vastaanottajille.

1. Sijoita taulukkomuotoinen (asiakas)tietorekisteri (esim. Word- tai Excel-tiedosto) kansiorakenteeseen, jossa se säilyy. Jatkossa dokumenttien linkitys toimii vain, jos yhdistetyt tiedostot löytyvät samoilla poluilla kuin linkkiä luotaessa. Tarkista rekisterin tiedot ja mahdollinen välilehti, jolla tarvitsemasi tiedot sijaitsevat. Sulje rekisteritiedosto.

2. Avaa Word ja kirjoita tarvitsemasi dokumentti, kirje tai kutsu.

3. Valitse Postitukset-välilehdeltä Aloita yhdistäminen --> Ohjattu vaiheittainen yhdistäminen. Seuraa toiminnon ohjeita.
   - Ohjattua toimintoa käyttämällä et unohda vaiheita välistä.

4. Valitse dokumentin tyyppi ja seuraava.

5. Valitse käyttämäsi tiedosto. Jos kirjepohja on jo auki, valitse "Käytä nykyistä asiakirjaa". Valitse seuraava.

6. Valitse rekisteri haluamastasi sijainnista tai kirjoita uusi luettelo. Jos käytät valmista rekisteritiedostoa, selaa se tiedostorakenteesta. Muista, ettet voi siirtää dokumentteja eri kansioihin, koska silloin yhteys ei enää toimi.

7. Valitse tietueet, joita haluat käyttää. Paina Ok. Jos olet tyytyväinen valitsemiisi vastaanottajiin, paina seuraava.

8. Siirrä kursori kohtaan, johon haluat jonkin tiedosta. Valitse "Lisää kohteita" sivuvalikosta. Lisää haluamasi kohteet (kentät). Sulje valinta ikkuna. Lisää tarvittavat välimerkit kohteiden väleihin. Lisää kaikki tarvittavat tiedot oikeisiin kohtiin samoin. Tallenna tiedosto myöhempää käyttöä varten. Valitse seuraava.

9. Esikatsele luotuja dokumentteja. Tarkasta dokumenttien oikeellisuus. Tässä vaiheessa voit vielä muokata vastaanottajia. Tallenna valmiit kirjeet. Valitse seuraava.

10. Nyt voit vielä muokata yksittäisiä kirjeitä halutessasi. Kannattaa koetulostaa yksi kirjeistä ja tarkastaa tulostusjälki sekä varmistaa, että osoitetiedot tulevat kirjekuoren osoiteikkunan kohdalle. Kun olet tyytyväinen, voit tulostaa kirjeet.

Jatkossa voit käyttää kohdassa kahdeksan tallennettua tiedostoa. Jos rekisteri tiedostoa päivitetään, uudet tiedot siirtyvät yhdistettyyn tiedostoon seuraavan avaamisen yhteydessä SQL-komennon avulla.


Osoitetarrat
Postitustoiminnolla voidaan tulostaa osoitetarrat kerralla kaikille vastaanottajille. Periaate on sama kuin joukkokirjeen lähettämisessä, mutta tietueita tulee samalle sivulle useita.

1. Avaa Wordilla uusi asiakirja.

2. Siirry Postitukset-välilehdelle

3. Valitse Aloita yhdistäminen --> ohjattu vaiheittainen yhdistäminen

4. Laita täppä Osoitetarrat -kohtaan. Valitse seuraava.

5. Laita täppä Muuta asiakirjan asettelua -kohtaan ja valitse Osoitetarra-asetukset.

6. Valitse huolellisesti oikea tulostin- ja osoitetarratyyppi tai mukauta omalle tarrallesi sopivat mitat. (Mittaa tarkasti!) Paina OK. Asiakirjaan ilmestyy ruudukko, joka kuvaa osoitetarroja. Valitse seuraava.

7. Lisää osoitetietueet yhdistämällä tietokanta asiakirjaan. Valitse Käytä nykyistä luetteloa ja Selaa-kohdasta osoitetietokanta. Valitse avautuvasta ikkunasta taulu, jota haluat käyttää ja tietueet, joille haluat tulostaa tarrat. Nyt Siirry seuraavaan vaiheeseen.

8. Asettele osoitetarrat. Lisää ensimmäisen osoitetarran kentät kohdasta Lisää kohteita.

9. Jos haluat käyttää ensimmäisen osoitetarran asettelua muissa tarroissa, valitse Päivitä kaikki osoitetarrat. Tietokentät ilmestyvät kaikkiin osoitetarroihin. Valitse seuraava.

10. Nyt voit esikatsella osoitetarroja ja tehdä muutoksia vastaanottajiin. Valitse seuraava.

11. Valitse Muokkaa yksittäisiä osoitetarroja, jolloin kaikki osoitetarrat tietoineen aukeavat uuteen tiedostoon. Tallenna tiedosto.

12. Tulosta yksi arkki tavalliselle tulostuspaperille. Vertaa tulostetta osoitetarra-arkkeihin. Jos tulosteen kohdistus on oikea, voit tulostaa osoitteet. Muokkaa osoitetarran kokoa tarvittaessa uudelleen ja tulosta uusi kokeilusivu.

13. Tulosta osoitetarrat tarra-arkeille.




Upotus
Upotuksella voidaan tuoda erilaisia objekteja muista tiedostoista esimerkiksi raportteihin.

1. Avaa tiedostopohja, johon tietoja koostetaan
2. Avaa tiedosto, josta tietoja haetaan.
3. Valitse kohde huolellisesti ja kopioi se.
4. Siirry tiedostopohjaan.
5. Valitse Liitä määräten.
6. Valitse Liitä linkki. Useimmiten Microsoft Excel -laskentataulukko Objekti toimii. Jos on hankaluuksia, kokeile eri valintoja.
7. Kun päivität alkuperäisen tiedoston, tiedot raportillakin päivittyvät. Tätä saattaa joutua avustamaan esimerkiksi painamalla F9 tai sulkemalla tiedostot. Tarkista siis että tiedot ovat päivittyneet!


Tekstitiedoston avaaminen Excelissä

1. Avaa Excel
2. Hae ulkoiset tiedot -> Tekstistä -> Selaa kansioista tarvitsemasi tekstitiedosto ->Tuo
   - CSV-tiedosto toimii samaan tapaan.
3. Valitse sopivat asettelut, mm. oikea erotin. Paina lopuksi valmis.
   - tiedot muodostavat valmiin taulukon Exceliin.

OPPIKIRJAESIMERKKI YHTEISKÄYTÖSTÄ

Potilaskortin ja potilastietokannan yhdistäminen ja potilaskortien tulostaminen.

Viikko 1 Tekijänoikeudet

Tekijänoikeuksien tarkoitus on suojata erilaisten teosten tuottajien, luojien, oikeutta omaan työhönsä ja mahdollistaa erityislaatuisilla, omalla käden jäljellä tuotetuilla asioilla elämisen. Tekijänoikeuksista säädetään lailla.

Tekijänoikeuslain alaisen materiaalin esittäminen julkisesti, esimerkiksi opetuksessa yhteisesti koko ryhmälle tai netissä blogikirjoituksen yhteydessä kysymättä oikeudenhaltijan lupaa on kielletty. Lisäksi paperimuotoisen materiaalin kopioiminen ja muuttaminen sähköiseksi luvatta on kielletty. Vapaasti käytettävää aineistoa voidaan hakea erityisillä hakukoneilla, joista alla on yksi esimerkki.

Teoksen tekijä voi kuitenkin antaa luvan käyttää aineistoa niin halutessaan. Näppärin tapa luvan antamiseksi on Creative Commons -lisenssi, josta kukin voi valita itseään miellyttävän ja teokselle sopivan suojan tason.

Creative Commons -lisenssin lisääminen julkaisuun on tehty helpoksi. Sivustolla creativecommons.fi voi helposti valita itselleen sopivan lisenssin kysymysten avulla. Sivulta löytyy myös ohjevideo. Lisäksi sieltä voi hakea tarvitsemaansa vapaasti käytettävissä olevaa materiaalia. Myös hakupalveluista voi etsiä vapaan lisenssin materiaalia, kun valitsee lisenssivaatimuksen haun asetuksissa.

Seuraavalla videolle kerrotaan, kuinka voit valita omiin tarpeisiisi sopivan lisenssin.

Valittu linsenssi

Tämä teos on lisensoitu Creative Commons Nimeä-EiKaupallinen-EiMuutoksia 4.0 Kansainvälinen -lisenssillä.

Tietokone työvälineenä -kurssin oppikirja

Tämä blogi toimii minun ja Jennin itse tekemänä oppikirjana.
Päivitämme blogiin viikottain kustakin kurssilla käsitellystä aiheesta.
Jokaiseen päivitykseen kuuluu muistiinpanot teoriaosuudesta sekä harjoitustehtävä ratkaisuineen.

Kihu tsemppaa.