Vihje: Kokeile käyttää uusia XHAKU- ja XMATCH-funktioita , jotka ovat parannetut versiot tässä artikkelissa kuvatuista funktioista. Nämä uudet funktiot toimivat mihin tahansa suuntaan ja palauttavat oletusarvoisesti tarkat vastineet, mikä helpottaa ja helpottaa niiden käyttöä kuin edeltäjät.
Oletetaan, että sinulla luettelo toimistosijaintinumeroista ja haluat tietää, ketkä työntekijät ovat missäkin toimistossa. Laskentataulukko on valtava, joten tehtävä saattaa vaikuttaa haastavalta. Se on todellisuudessa melko helppo haku-funktion ansiosta.
PHAKU- ja VHAKU-funktiot yhdessä INDEKSI- ja VASTINE-funktioiden kanssa kuuluvat Excelin hyödyllisimpiin funktioihin.
Huomautus: Ohjattu hakufunktion luomistoiminto ei ole enää käytettävissä Excelissä.
Tässä on esimerkki PHAKU-funktion käyttämisestä.
=PHAKU(B2,C2:E7,3,TOSI)
Tässä esimerkissä B2 on ensimmäinen argumentti eli tietoelementti, joka tarvitaan funktion toimintaa varten. PHAKU-funktiossa tämä ensimmäinen argumentti on arvo, jonka haluat löytää. Argumentti voi olla soluviittaus tai kiinteä arvo, kuten ”nieminen” tai 21 000. Toinen argumentti on solualue, C2-:E7, joka sisältää etsittävän arvon. Kolmas argumentti on sarake solualueella, joka sisältää etsittävän arvon.
Neljäs argumentti on valinnainen. Se voi olla TOSI tai EPÄTOSI. Jos kirjoitat TOSI tai jätät argumentin tyhjäksi, funktio palauttaa ensimmäisen argumentin arvon lähes tarkan vastineen. Jos kirjoitat EPÄTOSI, funktio etsii ensimmäisen argumentin arvoa vastaavan arvon. Toisin sanoen neljännen argumentin jättäminen tyhjäksi tai TOSI-arvon käyttäminen tekee hausta joustavamman.
Tässä esimerkissä näytetään, kuinka funktio toimii. Kun kirjoitat arvon soluun B2 (ensimmäinen argumentti), PHAKU hakee arvon solualueelta C2:E7 (toinen argumentti) ja palauttaa lähimmän lähes tarkan vastineen alueen kolmannesta sarakkeesta, joka on sarake E (kolmas argumentti).
Neljäs argumentti on tyhjä, joten funktio palauttaa lähes tarkan vastineen. Jos näin ei tapahdu, sinun on kirjoitettava yksi sarakkeen C tai D arvoista tuloksen saamiseksi.
Kun olet tutustunut PHAKU-funktioon, VHAKU-funktion käyttäminen on yhtä helppoa. Kirjoitat samat argumentit, mutta se etsii riveistä sarakkeiden sijaan.
INDEKSI- ja VASTINE-funktioiden käyttäminen PHAKU-funktion sijaan
PHAKU-funktion käyttämisessä on tiettyjä rajoituksia. PHAKU-funktio voi hakea arvoa vain vasemmalta oikealle. Tämä tarkoittaa sitä, että etsittävän arvon sisältävän sarakkeen pitäisi aina sijaita palautusarvon sisältävän sarakkeen vasemmalla puolella. Jos laskentataulukkoa ei ole rakennettu tällä tavalla, älä käytä PHAKU-funktiota. Käytä sen sijaan INDEKSI- ja VASTINE-funktioiden yhdistelmää.
Tässä esimerkissä näkyy pieni luettelo, jossa haettava arvo, Chicago, ei ole vasemmanpuoleisimmassa sarakkeessa. PHAKU-funktiota ei siis voi käyttää. Sen sijaan käytämme VASTINE-funktiota Chicagon etsimiseen alueelta B1:B11. Se löytyy rivistä 4. Indeksi käyttää tätä arvoa hakuargumenttina ja löytää Chicagon populaation 4. sarakkeesta (sarake D). Käytetty kaava näkyy solussa A14.
Lisää esimerkkejä INDEKSI- ja VASTINE-funktioiden käyttämisestä PHAKU-funktion sijaan on Artikkelissa , https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ Microsoft MVP Bill Jelen.
Kokeile
Ennen kuin ryhdyt käyttämään hakufunktioita omissa laskelmissasi, voit halutessasi harjoitella oheisilla mallitiedoilla.
PHAKU-funktion esimerkki toiminnassa
Kopioi seuraavat tiedot tyhjään laskentataulukkoon.
Vihje: Määritä sarakkeiden A–C sarakeleveydeksi 250 pikseliä ja valitse Rivitä teksti (Aloitus-välilehti, Tasaus-ryhmä) ennen kuin liität tietoja Exceliin.
|
Tiheys |
Viskositeetti |
Lämpötila |
|
0,457 |
3,55 |
500 |
|
0,525 |
3,25 |
400 |
|
0,606 |
2,93 |
300 |
|
0,675 |
2,75 |
250 |
|
0,746 |
2,57 |
200 |
|
0,835 |
2,38 |
150 |
|
0,946 |
2,17 |
100 |
|
1,09 |
1,95 |
50 |
|
1,29 |
1,71 |
0 |
|
Kaava |
Kuvaus |
Tulos |
|
=PHAKU(1,A2:C10,2) |
Käyttää epätarkan vastineen palauttavaa hakua ja etsii sarakkeesta A arvoa 1. Funktio palauttaa sarakkeen A suurimman arvon (0,946), joka on yhtä suuri tai pienempi kuin 1. Funktio palauttaa lisäksi sarakkeen B saman rivin arvon. |
2,17 |
|
=PHAKU(1;A2:C10;3;TOSI) |
Käyttää epätarkan vastineen palauttavaa hakua ja etsii sarakkeesta A arvoa 1. Funktio palauttaa sarakkeen A suurimman arvon (0,946), joka on yhtä suuri tai pienempi kuin 1. Funktio palauttaa lisäksi sarakkeen C saman rivin arvon. |
100 |
|
=PHAKU(0.7;A2:C10;3;EPÄTOSI) |
Käyttää tarkan vastineen palauttavaa hakua ja etsii sarakkeesta A arvoa 0,7. Koska sarakkeessa A ei ole tätä arvoa vastaavaa arvoa, funktio palauttaa virhearvon. |
#PUUTTUU! |
|
=PHAKU(0,1;A2:C10;2;TOSI) |
Käyttää epätarkan vastineen palauttavaa hakua ja etsii sarakkeesta A arvoa 0,1. Koska 0,1 on pienempi kuin sarakkeen A pienin arvo, funktio palauttaa virhearvon. |
#PUUTTUU! |
|
=PHAKU(2;A2:C10;2;TOSI) |
Käyttää epätarkan vastineen palauttavaa hakua ja etsii sarakkeesta A arvoa 2. Funktio palauttaa sarakkeen A suurimman arvon (1,29), joka on yhtä suuri tai pienempi kuin 2. Lisäksi funktio palauttaa sarakkeen B saman rivin arvon. |
1,71 |
VHAKU-funktion esimerkki
Kopioi kaikki tämän taulukon solut ja liitä ne tyhjän Excel-taulukon soluun A1.
Vihje: Määritä sarakkeiden A–C sarakeleveydeksi 250 pikseliä ja valitse Rivitä teksti (Aloitus-välilehti, Tasaus-ryhmä) ennen kuin liität tietoja Exceliin.
|
Akselit |
Laakerit |
Pultit |
|
4 |
4 |
9 |
|
5 |
7 |
10 |
|
6 |
8 |
11 |
|
Kaava |
Kuvaus |
Tulos |
|
=VHAKU("Akselit"; A1:C4; 2; TOSI) |
Etsii arvon "Akselit" riviltä 1 ja palauttaa saman sarakkeen (sarake A) rivin 2 arvon. |
4 |
|
=VHAKU("Laakerit"; A1:C4; 3; EPÄTOSI) |
Etsii arvon "Laakerit" riviltä 1 ja palauttaa saman sarakkeen (sarake B) rivin 3 arvon. |
7 |
|
=VHAKU("B"; A1:C4; 3; TOSI) |
Etsii arvon "B" riviltä 1 ja palauttaa saman sarakkeen rivin 3 arvon. Koska arvon "B" tarkkaa vastinetta ei löydy, käytetään riviltä 1 suurinta arvoa, joka on pienempi kuin "B": "Akselit," sarakkeessa A. |
5 |
|
=VHAKU("Pultit"; A1:C4; 4) |
Etsii arvon "Pultit" riviltä 1 ja palauttaa saman sarakkeen (sarake C) rivin 4 arvon. |
11 |
|
=VHAKU(3;{1;2;3\"a","b","c"\"d","e","f"};2;TOSI) |
Etsii luvun 3 kolmerivisestä matriisivakiosta ja palauttaa rivin 2 arvon samasta sarakkeesta (tässä tapauksessa kolmannesta sarakkeesta). Matriisivakiossa on kolme riviä arvoja; rivit on erotettu toisistaan puolipisteellä (;). Koska "c" esiintyy rivillä 2 ja samassa sarakkeessa kuin 3, "c" palautuu. |
c |
INDEKSI- ja VASTINE-funktioiden esimerkit
Viimeisessä esimerkissä käytetään INDEKSI- ja VASTINE-funktioita yhdessä vanhimman laskun numeron ja sitä vastaavan päivämäärän palauttamiseen kullekin viidelle kaupungille. Päivämäärä palautetaan lukuna, joten TEKSTI-funktiolla se muotoillaan päivämäärämuotoon. INDEKSI-funktio käyttää argumenttinaan VASTINE-funktion tulosta. INDEKSI- ja VASTINE-funktioiden yhdistelmää käytetään kahdesti jokaisessa kaavassa – ensin laskun numeron palauttamiseen ja sitten päivämäärän palauttamiseen.
Kopioi kaikki tämän taulukon solut ja liitä ne tyhjän Excel-taulukon soluun A1.
Vihje: Määritä sarakkeiden A–D sarakeleveydeksi 250 pikseliä ja valitse Rivitä teksti (Aloitus-välilehti, Tasaus-ryhmä) ennen kuin liität tietoja Exceliin.
|
Lasku |
Kaupunki |
Laskun päivämäärä |
Vanhin lasku kaupungin perusteella, päivämäärä |
|
3115 |
Atlanta |
7.4.2012 |
="Atlanta = "&INDEKSI($A$2:$C$33,VASTINE("Atlanta",$B$2:$B$33,0),1)& ", Laskun päivämäärä: " & TEKSTI(INDEKSI($A$2:$C$33,VASTINE("Atlanta",$B$2:$B$33,0),3),"k/p/vv") |
|
3137 |
Atlanta |
9.4.2012 |
="Austin = "&INDEKSI($A$2:$C$33,VASTINE("Austin",$B$2:$B$33,0),1)& ", Laskun päivämäärä: " & TEKSTI(INDEKSI($A$2:$C$33,VASTINE("Austin",$B$2:$B$33,0),3),"k/p/vv") |
|
3154 |
Atlanta |
11.4.2012 |
="Dallas = "&INDEKSI($A$2:$C$33,VASTINE("Dallas",$B$2:$B$33,0),1)& ", Laskun päivämäärä: " & TEKSTI(INDEKSI($A$2:$C$33,VASTINE("Dallas",$B$2:$B$33,0),3),"k/p/vv") |
|
3191 |
Atlanta |
21.4.2012 |
="New Orleans = "&INDEKSI($A$2:$C$33,VASTINE("New Orleans",$B$2:$B$33,0),1)& ", Laskun päivämäärä: " & TEKSTI(INDEKSI($A$2:$C$33,VASTINE("New Orleans",$B$2:$B$33,0),3),"k/p/vv") |
|
3293 |
Atlanta |
25.4.2012 |
="Tampa = "&INDEKSI($A$2:$C$33,VASTINE("Tampa",$B$2:$B$33,0),1)& ", Laskun päivämäärä: " & TEKSTI(INDEKSI($A$2:$C$33,VASTINE("Tampa",$B$2:$B$33,0),3),"k/p/vv") |
|
3331 |
Atlanta |
27.4.2012 |
|
|
3350 |
Atlanta |
28.4.2012 |
|
|
3390 |
Atlanta |
1.5.2012 |
|
|
3441 |
Atlanta |
2.5.2012 |
|
|
3517 |
Atlanta |
8.5.2012 |
|
|
3124 |
Austin |
9.4.2012 |
|
|
3155 |
Austin |
11.4.2012 |
|
|
3177 |
Austin |
19.4.2012 |
|
|
3357 |
Austin |
28.4.2012 |
|
|
3492 |
Austin |
6.5.2012 |
|
|
3316 |
Dallas |
25.4.2012 |
|
|
3346 |
Dallas |
28.4.2012 |
|
|
3372 |
Dallas |
1.5.2012 |
|
|
3414 |
Dallas |
1.5.2012 |
|
|
3451 |
Dallas |
2.5.2012 |
|
|
3467 |
Dallas |
2.5.2012 |
|
|
3474 |
Dallas |
4.5.2012 |
|
|
3490 |
Dallas |
5.5.2012 |
|
|
3503 |
Dallas |
8.5.2012 |
|
|
3151 |
New Orleans |
9.4.2012 |
|
|
3438 |
New Orleans |
2.5.2012 |
|
|
3471 |
New Orleans |
4.5.2012 |
|
|
3160 |
Tampa |
18.4.2012 |
|
|
3328 |
Tampa |
26.4.2012 |
|
|
3368 |
Tampa |
29.4.2012 |
|
|
3420 |
Tampa |
1.5.2012 |
|
|
3501 |
Tampa |
6.5.2012 |
