Vastine- ja Indeksi-funktiot
Tällä videolla kerron Phaku-funktioon liittyvistä rajoitteista sekä näytän miten Vastine- ja Indeksi-funktiota voidaan hyödyntää tuotetietojen hakemisessa ja yhdistämisessä.
Taulukkosivulla on oleellisilta osin samat tiedot kuin edellisessä tehtävässä. Tehtävänä on jälleen täydentää tuotetiedot sopivaa hakufunktiota käyttäen.
Tuotteiden lisätiedot -taulukkosivu poikkeaa edellisestä tehtävästä kuitenkin siten, että nyt tuotekoodit onkin kerrottu G-sarakkeessa.
Tämä tarkoittaa sitä, että en voi hyödyntää Phaku-funktiota. Phaku-funktio nimittäin olettaa, että hakuarvo eli tässä tapauksessa tuotekoodi on aina ilmaistu tietoalueen ensimmäisessä sarakkeessa.
Yksi vaihtoehto olisi siirtää Tuotekoodi-sarake ensimmäiseksi. Kaikissa tilanteissa ei ole kuitenkaan toivottavaa lähteä muuttamaan taulukon asettelua, joten ratkaisemme ongelman käyttämällä indeksi- ja vastine-funktiota.
Aloitan näyttämällä esimerkin indeksi-funktion käytöstä. Indeksi-funktio hakeaa taulukkoalueelta tietyllä rivillä ja tietyssä sarakkeessa olevan solun arvon. Pääset hyödyntämään sitä kohta varsinaisessa tehtävässä eli tuotetietojen täydentämisessä, mutta tee ensin perässäni seuraava esimerkki.
Valitsen solun J5. Haluan hakea tähän soluun oheisesta taulukosta rivillä 1 ja sarakkeessa A olevan tiedon.
Valitsen Kaavat-välilehdeltä Hakufunktioiden alta Indeksi-funktion. Tästä funktiosta on kaksi eri versiota. Valitaan vaihtoehdoista ensimmäisen. Matriisi argumentti on solualue, josta haen tietoja. En ota mukaan otsikkoriviä, koska en ole kiinnostunut hakemaan sen tietoja.
Siirryn tabulaattorilla seuraavaan kohtaan. Rivi-nro ja sarake-nro -argumenteiksi valitsen kaavan yläpuolella olevat solut.
Huomaa, että rivinumero 1 viittaa valitsemani solualueen ensimmäiseen riviin eikä taulukkosivun ensimmäiseen riviin. Samoin sarakenumero 1 viittaa valitsemani solualueen ensimmäiseen sarakkeeseen, vaikka se sattuukin tässä esimerkissä olemaan myös taulukkosivun ensimmäisen sarake.
Lopputulos näyttää hyvältä. Eli soluun haetaan tällä hetkellä solun A2 arvo. Jos vaihdan rivi-nro ja sarake-nro argumenttien arvoja, haettava arvo päivittyy vastaamaan uusia arvoja.
Siirryn seuraavaksi Vastine_ja_indeksi -taulukkosivulle. Katsotaan ensin yhdessä esimerkki Vastine-funktion käytöstä. Riville 1 on nyt syötetty suoraan lukuarvoina tiettyä otsikkoa vastaava järjestysnumero Tuotteiden lisätiedot -taulukkosivulla.
Poistan nämä arvot koska jos esimerkiksi sarakkeiden järjestys Tuotteiden lisätiedot -taulukkosivulla muuttuisi, hakufunktio, jonka tulen myöhemmin lisäämään, ei enää toimisi oikein. Sen sijaan haluan että arvot haetaan Vastine-funktion avulla.
Lisään soluun Hakufunktioiden alta löytyvän Vastine-funktion. Funktio hakee tietyn hakuarvon sijainnin tietyn solualuuen sisältä. Haluan hakea tähän soluun paino-otsikon sijainnin Tuotteiden lisätiedot -taulukkosivulla olevalla tietoalueella.
Siten hakuarvo on ilmaistu solussa E2. Haku_matriisi on Tuotteiden lisätiedot -taulukkosivulla oleva, otsikot sisältävä tietoalue. Koska tiedän, että haluan kohta kopioida kaavaa, lisään dollarimerkit ilmaisemaan absoluuttista viittausta. Käytän tässä apuna näppäinkomentoa shift + F4.
Vastine_laji -argumentiksi tulee täsmällinen vastine eli 0.
Lopputulos näyttää hyvältä. Kopioin koko rivin käyttämään soluun syöttämääni funktiota.
Seuraavaksi muutan lähdealueen sarakkeiden järjestystä valitsemalla kokonaisia sarakkeita ja sen jälkeen pitämällä shift-painiketta pohjassa ja raahaamalla sarakkeen uuteen sijaintiin.
Koska käytämme vastine-funktiota otsikkojen sijainnin selvittämiseen, päivittyvät sarake numerot vastaamaan sarakkeiden uutta järjestystä.
Tehtävä: Vastine-funktion harjoittelu
Hyödynnä seuraavaksi vastine-funktiota tiettyä tuotekoodia vastaavan rivinumeron hakemiseksi. Aloita kirjoittamalla funktio soluun A3. Kirjoita funktio niin, että voit kopioida sen riville 12 asti.
Pysäytä nyt video hetkeksi ja yritä ratkaista tehtävä. Katso sitten malliratkaisu ja siihen liittyvät huomiot.
Malliratkaisu
Näytän seuraavaksi tehtävän malliratkaisun.
Valitsen solun A3 ja lähden syöttämään soluun vastine-funktiota kuten äskenkin. Hakuarvo on tuotekoodi ja haku_matriisi on tuotekoodisarake Tuotteiden lisätiedot-välilehdeltä. Lisäksi haluan käyttää absoluuttisia soluviittauksia.
Haluamme jälleen täsmällisen vastineen eli vastinelajiksi tulee nolla.
Kopioidaan kaava lopuksi hakemaan kaikki taulukossa olevia tuotekoodeja vastaavat rivien sijainnit.
Tehtävä: Indeksi-funktion harjoittelu
Seuraava tehtäväsi on hyödyntää indeksi-funktiota tuotetietojen täydentämiseen Tuotteiden lisätiedot -taulukkosivulta löytyvillä tiedoilla. Hyödynnä indeksifunktiossa vastine-funktion avulla laskettuja rivi- ja sarakenumeroja.
Pysäytä nyt video hetkeksi ja yritä ratkaista tehtävä. Katso sitten malliratkaisu ja siihen liittyvät huomiot.
Malliratkaisu
Näytän seuraavaksi tehtävän malliratkaisun.
Valitsen solun E3 ja lähden syöttämään indeksi-funktiota. Taulukkomatriisi on täydennättävien tietojen lähde. Lisään soluviittaukseen dollarimerkit sekä sarake- että rivitunnusten eteen käyttämällä näppäinkomentoa shift + F4.
Rivinumero on kerrottu solussa A3. Koska rivinumero otetaan tälle tuotteelle aina sarakkeesta A, tulee A-sarakkeen eteen laittaa dollarimerkki.
Sarakenumero on kerrottu solussa E1. Koska sarakenumero otetaan tälle tuotteelle aina riviltä 1, tulee kaavassa rivinumeron eteen asettaa dollarimerkki.
Kaava on valmis. Kopioin kaavaa täydennettävälle alueelle. Voin vielä verrata tietoja ja todeta, että tietojen täydennys on tapahtunut oikein.