Luku 17 · 17.4
Haku kahdella ehdolla samanaikaisesti
PHAKU ja XHAKU hakevat yhden arvon perusteella (katso PHAKU antaa virheen #N/A: näin ratkaiset sen ja XHAKU: haku myös vasemmalle ja omalla virheilmoituksella). Käytännössä haet usein kahden asian perusteella samanaikaisesti: kimpun hinnan koossa L, asiakkaan hinnan tiettynä vuonna tai tuotteen varastosaldon tietyssä värissä. Siihen on kolme hyvää tapaa, jotka kaikki toimivat tavallisella Enter-näppäimellä. Neljäs tapa, jonka näet usein internetissä, ei toimi Euro-Officessa sellaisenaan.
Esimerkkinä käytämme Bloemisterij Het Zonnetjen hinnastoa. Sama tuote on siinä useaan kertaan eri koossa:
| A | B | C | |
|---|---|---|---|
| 1 | Tuote | Koko | Hinta |
| 2 | Kimppu | S | 17,50 |
| 3 | Kimppu | M | 24,50 |
| 4 | Kimppu | L | 34,50 |
| 5 | Surusidonta | M | 65,00 |
| 6 | Surusidonta | L | 89,00 |
| 7 | Morsiuskimppu | M | 79,50 |
Haettu tuote on solussa F2 (Kimppu) ja koko solussa G2 (L). Haemme arvoa 34,50.
Tapa 1: XHAKU yhdistetyillä arvoilla
=XHAKU(F2&"|"&G2;A2:A7&"|"&B2:B7;C2:C7;"ei löytynyt")
&-merkki liittää tekstit yhteen. F2&"|"&G2 tuottaa tekstin "Kimppu|L". A2:A7&"|"&B2:B7 tekee luettelon jokaisesta rivistä samanlaisen yhdistelmän ("Kimppu|S", "Kimppu|M" ja niin edelleen), ja XHAKU hakee niistä. Väliin lisätty pystyviiva | estää sen, että kaksi eri yhdistelmää tuottaisi sattumalta saman tekstin. Jos yhdistelmää ei ole, esimerkiksi surusidontaa koossa S, saat tuloksen "ei löytynyt".

Jos katsot kaavariviä kirjoittamisen jälkeen, alueiden A2:A7 ja B2:B7 edessä on @-merkki. Euro-Office lisää sen itse, ja tulos on silti oikein.
Tapa 2: SUODATA kaikille osumille
=SUODATA(C2:C7;(A2:A7=F2)*(B2:B7=G2);"ei mitään")
SUODATA ei palauta vain ensimmäistä, vaan kaikki rivit, jotka täyttävät molemmat ehdot. Jos yhdistelmä esiintyy kahdesti, näet molemmat hinnat allekkain. Varmista siis, että kaavan alla on tilaa (katso Itsestään päivittyvät luettelot funktioilla SUODATA, AINUTKERTAISET.ARVOT ja LAJITTELE). Jos haluat ehdon "tai" ehdon "ja" sijaan, käytä plusmerkkiä: (A2:A7="Kimppu")+(A2:A7="Surusidonta").
Tapa 3: SUMMA.JOS.JOUKKO, kun yhdistelmä on yksilöllinen
=SUMMA.JOS.JOUKKO(C2:C7;A2:A7;F2;B2:B7;G2)
Kuulostaa oudolta, koska et halua laskea mitään yhteen. Jos jokainen yhdistelmä esiintyy kuitenkin vain kerran, summa on yksinkertaisesti se yksi hinta. Etu: SUMMA.JOS.JOUKKO on helppo, nopea ja toimii jokaisessa Excelissä. Haitta: jos yhdistelmää ei ole, saat tuloksen 0 (0,00 €) ilmoituksen sijaan. Ja jos yhdistelmä on vahingossa luettelossa kahdesti, saat molempien hintojen summan.
Mitä löydät internetistä: INDEKSI ja VASTINE kertolaskulla
Monet Excel-sivustot neuvovat kaavan =INDEKSI(C2:C7;VASTINE(1;(A2:A7=F2)*(B2:B7=G2);0)). Euro-Officessa tämä kaava antaa tavallisella Enter-näppäimellä tuloksen #N/A, vaikka yhdistelmä olisi olemassa. Jos viimeistelet sen näppäinyhdistelmällä Ctrl+Vaihto+Enter, kaava saa aaltosulkeet {…} ja toimii. Se unohtuu helposti, joten käytä mieluummin edellä kuvattuja XHAKU- tai SUODATA-funktioita.
Haku sekä rivin että sarakkeen perusteella
Jos hinnastosi on taulukko, jossa tuotteet ovat allekkain ja koot vierekkäin (matriisi), käytä INDEKSI-funktiota kahden VASTINE-funktion kanssa. Kun tuotteet ovat alueella A11:A13, koot S, M ja L alueella B10:D10 ja hinnat alueella B11:D13:
=INDEKSI(B11:D13;VASTINE(F2;A11:A13;0);VASTINE(G2;B10:D10;0))
Ensimmäinen VASTINE hakee tuotteen rivin ja toinen koon sarakkeen. INDEKSI poimii solun niiden risteyskohdasta.
Minkä valitset?
| Tilanne | Paras valinta |
|---|---|
| Yksi vastaus ja selkeä ilmoitus, jos sitä ei ole | XHAKU yhdistetyillä arvoilla |
| Tiedoston on toimittava myös vanhassa Excelissä | SUMMA.JOS.JOUKKO |
| Osumia voi olla useita | SUODATA |
| Hintataulukko, jossa koot ovat sarakeotsikoina | INDEKSI kahden VASTINE-funktion kanssa |
Sudenkuopat
- #N/A, vaikka yhdistelmä on todella olemassa: syynä on usein välilyönti arvon perässä ("Kimppu ") tai luku, joka on tallennettu tekstinä. Katso PHAKU antaa virheen #N/A: näin ratkaiset sen.
- Eripituiset alueet: =XHAKU(F2;A2:A7;C2:C50) antaa tuloksen #VALUE!. Aloita ja lopeta kaikki alueet samalla rivillä.
- Kokonaiset sarakkeet (A:A) näissä kaavoissa hidastavat suurta tiedostoa, koska Euro-Office yhdistää silloin miljoona riviä. Käytä kiinteää aluetta, kuten A2:A500. Katso Hitaan laskentataulukon nopeuttaminen.
- Kollega, jolla on Excel 2019 tai vanhempi, näkee XHAKU- ja SUODATA-funktioiden kohdalla virheen #NAME?.
