Phaku-funktio
Tällä videolla käyn läpi hakufunktioiden käyttöä ja niiden hyödyntämistä tuotetietojen yhdistämisessä.
Heti alkuun on syytä mainita, että Microsoft on hiljattain julkaissut uuden Xlookup eli Xhaku-funktion. Se ei kuitenkaan ole vielä saatavilla kuin ainoastaan Office insiders -tilaajille sekä aivan uusimmissa Office 365 -versioissa.
Voit testata onko xhaku-käytettävissäsi klikkaamalla solua ja aloittamalla funktion nimen kirjoittamisen. Jos funktio ilmestyy näkyviin, uusi funktio on käytettävissäsi.
Koska xhaku ei ole vielä välttämättä saatavillasi, käyn videolla läpi vanhemman hakufunktion, Vlookup eli Phaku-funktion käytön.
Taulukossa on tuotteiden hintatietoja. Haluan, kirjoittaessani soluun D2 tuotteen nimen, sitä vastaava hintatieto haetaan soluun E2.
Valitsen solun E2 ja siirryn Kaavat-välilehdelle. Haku-painikkeen alta löytyy Phaku-funktio.
Olen hakemassa tuotteen hintaa tuotteen nimen perusteella, joten hakuarvo kohtaan tulee solu, jossa on haettavan tuotteen nimi.
Taulukko_matriisi -argumentti on tietoalue, jossa tuotetiedot ovat. Käytän valitsemiseen näppäinkometoja ctrl + shift + nuoli alaspäin JA ctrl + shift + nuoli oikealle.
Sarakkeen indeksinumero kertoo sen sarakkeen järjestysnumeron, jossa haettava tieto on. Hinta-tieto on hintataulukon ensimmäisestä sarakkeesta lukien järjestyksessään toinen, joten sarakkeen indeksinumeroksi tulee kakkonen.
Alue_haku argumentilla on kaksi mahdollista arvoa. Joko TOSI tai EPÄTOSI. Jos arvo on epätosi, on haku täsmällinen eli jotta funktio palauttaisi etsittävän tuotteen hinnan, tulee hintataulukosta löytyä täsmälleen haettavalla nimellä oleva tuote.
Jos alue_haku kohtaan syöttää arvon TOSI palauttaa funktio siinä tilanteessa, että haettavaa tuotetta ei juuri sillä nimellä löydy, haettavan tuotenimen lähimmän vastineen. Tämä on harvoin se mitä halutaan. Useimmiten PHAUN kanssa tulee Alue_haku argumentiksi syöttää EPÄTOSI tai sen lyhenne eli nolla.
Hyväksyn funktion argumentit OK:lla. Ja aivan kuten pitikin funktio hakee tuotteen Roamer 4 M hintatiedon. Jos kirjoitan soluun D2 toisen tuotteen nimen, hakee funktion vastaavasti tämän tuotteen hinnan.
Jos taas kirjoitan hakuarvoksi tuotenimen, jota taulukosta ei löydy, palauttaa funktio PUUTTUU -virheen.
Kuumoan viimeisimmän muutoksen näppäinkomennolla CTRL + z.
Tehtävä: Phaku-funktion harjoittelu
Siirryn seuraavaksi Tuotetiedot 2 -taulukkosivulle. Taulukkosivulla pääset harjoittelemaan phaku-funktio käyttöä.
Syötä soluun F2 funktio, joka hakee solussa E3 olevaa tuotekoodia vastaavan tuotteen nimen. Tämän jälkeen syötä vastaavasti soluun G2 funktio, joka hakee tuotekoodia vastaavan hinnan.
Pysäytä video hetkeksi ja yritä tehdä tehtävät itsenäisesti. Katso sitten video loppuun, niin näet tehtävien ratkaisut ja niihin liittyvät huomiot.
Malliratkaisu
Kun syötän hinta-tiedon antavaa funktiota, kopioin pohjaksi äsken syöttämäni funktion. Tätä ennen muokkaan funktiota kuitenkin siten, että käytän absoluuttisia soluviittauksia viitatessani hakuarvoon ja hakutaulukkoon.
Tehtävä: Lisää Phaun harjoittelua
Siirryn seuraavaksi Kaikki tuotetiedot -taulukkosivulle. Tehtävänäsi on lisätä puuttuvat tiedot phaku-funktion avulla. Puuttuvat tiedot löytyvät Tuotteiden lisätiedot taulukkosivulta. Tuotekoodi yhdistää näiden kahden taulukkosivun tiedot.
Monimutkaisten tehtävien kohdalla auttaa tehtävän pilkkominen osakokonaisuuksiksi.
Kirjoita siis aluksi funktio, joka hakee soluun D3 tuotekoodia GI5 vastaavan tuotteen painon Tuotteiden lisätiedot -taulukkosivulta. Käytä apuna riville 1 syötettyjä lukuja phakufunktion sarakeindeksinumero argumenttia syöttäesssäsi.
Kun saat tämän ensimmäisen vaiheen toimimaan, muokkaa funktiota siten, että voit kopioida funktion muihinkin alueen soluihin. Tämän edellytyksenä on, että funktion soluviittauksissa on huomioitu suhteellinen ja absoluuttinen tapa viitata solujen rivi- ja sarakeosoitteisiin.
Pysäytä nyt video hetkeksi ja aloita tehtävän tekeminen. Katso sitten video loppuun, niin näet tehtävän ratkaisun ja siihen liittyvät huomiot.
Malliratkaisu
Näytän seuraavaksi tehtävän malliratkaisun. Valitsen solun D3. Haku-funktiot -painikkeen alta löydän phaku-funktion.
Koska olen hakemassa tuotetietoja tuotekoodin perusteella tuotteelle GI5, tulee hakuarvoksi tuotekoodin ilmaiseva solu eli solu A3. Siirryn tabulattorilla syöttämään seuraavaa argumenttia.
Taulukko_matriisi on tietoalue, jolta puuttuvat tiedot löytyvät. Siirryn Tuotteiden lisätiedot -taulukkosivulle ja valitsen tuotetiedot. Hyödynnän jälleen valitsemisen näppäinkomentoja ctrl+shift+ nuoli alaspäin ja ctrl + shift + nuoli oikealle. Siirryn tabulaattorilla syöttämään seuraavaa argumenttia.
Sarakkeen indeksinumeroksi tulee haettavan tiedon järjestysnumero tietoalueella eli äskeisen kohdan taulukko_matriisissa. Hyödynnän tässä riviä yksi, jolla sarakkeen indeksinumero on kerrottu. Siirryn tabulaattorilla jälleen syöttämään seuraavaa argumenttia.
Aluehaun tulee olla täsmällinen eli epätosi eli nolla. Kaavan tuloksen esikatselu näyttää hyvältä. Hyväksyn funktion argumentit painamalla enteriä.
Tiedän kuitenkin, että kirjoittamani funktio antaa oikean vastauksen vain solussa D3. Jos lähden kopioimaan funktiota alaspäin tai oikealle, funktio ei palauta haluamiani tuloksia.
Valitsen solun D3 muokatakseni funktion soluviittauksia. Koska soluviittauksen tulee viitata samaan soluun kun kopioin funktioita oikealle eli vaakasuunnassa, tulee dollarimerkki asettaa sarakeosoitteen eli vaakasuuntaa ilmaisevan osoitteen osan eli A-kirjaimen eteen.
Siirryn tabulaattorilla seuraavaan kohtaan. Taulukko_matriisiin tulee viitata aina samaan alueeseen, joten asetan dollarimerkit sekä taulukkoalueen alunkohtaa ja loppukohtaa ilmaiseviin soluosoitteisiin. Koska dollarimerkit tulevat sekä rivi- että sarakeosoitteen eteen, käytän näppäinkomentoa Shift + F4.
Siirryn tabulaattorilla seuraavaan kohtaan. Koska soluviittaus ei saa muuttua kopioidessani kaavaa alaspäin eli pystysuunnassa, tulee dollarimerkki asettaa pystysuuntaa ilmaisevan osoitteen osan eteen eli ykkösen eteen.
Funktio on valmis. Hyväksyn muutokset enterillä ja kopioin funktiota alaspäin täyttökahvan avulla. Ja sitten oikealle. Tehtävä on valmis!