Mitä tietää
- Luo ensin INDEX-funktio ja aloita sitten sisäkkäinen MATCH-funktio syöttämällä Lookup_value-argumentti.
- Lisää seuraavaksi Lookup_array-argumentti ja sen jälkeen Match_type-argumentti ja määritä sitten sarakealue.
- Muuta sitten sisäkkäinen funktio taulukkokaavaksi painamalla Ctrl+ Shift+ Enter. Lisää lopuksi hakutermit laskentataulukkoon.
Tässä artikkelissa kerrotaan, kuinka luodaan hakukaava, joka käyttää useita ehtoja Excelissä tietojen etsimiseen tietokannasta tai tietotaulukosta taulukkokaavan avulla. Taulukkokaava sisältää MATCH-funktion sisäkkäisyyden INDEX-funktion sisällä. Tiedot kattavat Excel for Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 ja Excel for Mac.
Seuraa opetusohjelmaa
Noudata tämän opetusohjelman ohjeita kirjoittamalla näytetiedot seuraaviin soluihin alla olevan kuvan mukaisesti. Rivit 3 ja 4 jätetään tyhjiksi tämän opetusohjelman aikana luodun taulukon kaavan mukaan. (Huomaa, että tämä opetusohjelma ei sisällä kuvassa näkyvää muotoilua.)
- Syötä ylin tietoalue soluihin D1–F2.
- Syötä toinen alue soluihin D5–F11.
Luo INDEX-funktio Excelissä
INDEKSI-funktio on yksi harvoista Excelin funktioista, jolla on useita muotoja. Funktiolla on Array Form ja Reference Form. Array Form palauttaa tiedot tietokannasta tai tietotaulukosta. Viitelomake antaa soluviitteen tai tietojen sijainnin taulukossa.
Tässä opetusohjelmassa taulukkolomaketta käytetään titaaniwidgetien toimittajan nimen etsimiseen tietokannan soluviittauksen sijaan tähän toimittajaan.
Luo INDEX-funktio seuraavasti:
- Valitse solu F3 tehdäksesi siitä aktiivisen solun. Tähän soluun sisäkkäinen funktio syötetään.
-
Siirry Formulasiin.
Image - Valitse Haku & Viittaus avataksesi toimintoluettelon.
- Valitse INDEX avataksesi Select Arguments -valintaikkunan.
- Valitse taulukko, rivin_numero, sarakkeen_numero.
- Valitse OK avataksesi Function Arguments-valintaikkunan. Excel for Macissa Formula Builder avautuu.
- Aseta kohdistin Array-tekstiruutuun.
-
Korosta solut D6 – F11 laskentataulukossa syöttääksesi alueen valintaikkunaan.
Jätä Function Arguments -valintaikkuna auki. Kaava ei ole valmis. Täytät alla olevien ohjeiden kaavan.
Image
Käynnistä sisäkkäinen MATCH-toiminto
Kun funktio upotetaan toisen sisään, ei ole mahdollista avata toisen eli sisäkkäisen funktion kaavanrakennustyökalua tarvittavien argumenttien syöttämiseksi. Sisäkkäinen funktio on syötettävä yhdeksi ensimmäisen funktion argumenteista.
Kun syötetään funktioita manuaalisesti, funktion argumentit erotetaan toisistaan pilkulla.
Ensimmäinen vaihe sisäkkäisen MATCH-funktion syöttämiseksi on Lookup_value-argumentin syöttäminen. Lookup_value on hakutermin sijainti- tai soluviittaus tietokannassa.
Hakuarvo hyväksyy vain yhden hakuehdon tai termin. Jos haluat etsiä useita ehtoja, laajenna Lookup_value -arvoa ketjuttamalla tai yhdistämällä kaksi tai useampia soluviittauksia et-merkillä (&).
- Aseta osoitin Function Arguments-valintaikkunassa Rivin_numero-tekstiruutuun.
- Syötä MATCH(.
- Valitse solu D3 syöttääksesi soluviittauksen valintaikkunaan.
- Syötä & (et-merkki) soluviittauksen D3 jälkeen lisätäksesi toisen soluviittauksen.
- Syötä toinen soluviittaus valitsemalla solu E3.
-
Syötä , (pilkku) soluviittauksen E3 jälkeen viimeistelläksesi MATCH-funktion Lookup_value-argumentin.
Image Opetusohjelman viimeisessä vaiheessa Lookup_values syötetään laskentataulukon soluihin D3 ja E3.
Suorita sisäkkäinen MATCH-toiminto loppuun
Tämä vaihe kattaa Lookup_array-argumentin lisäämisen sisäkkäiseen MATCH-funktioon. Lookup_array on solualue, jota MATCH-funktio etsii löytääkseen opetusohjelman edellisessä vaiheessa lisätyn Lookup_value-argumentin.
Koska Lookup_array-argumentissa tunnistettiin kaksi hakukenttää, sama on tehtävä Lookup_array-argumentille. MATCH-funktio etsii vain yhtä taulukkoa kullekin määritetylle termille. Jos haluat syöttää useita taulukoita, käytä et-merkkiä ketjuttaaksesi taulukot yhteen.
- Aseta kohdistin Rivin_numero-tekstiruudun tietojen loppuun. Kohdistin näkyy pilkun jälkeen nykyisen merkinnän lopussa.
-
Syötä alue korostamalla solut D6 – D11 laskentataulukossa. Tämä alue on ensimmäinen matriisi, jonka funktio etsii.
- Syötä & (et-merkki) soluviittausten D6:D11 jälkeen. Tämä symboli saa funktion etsimään kahta taulukkoa.
- Syötä alue korostamalla solut E6 – E11 laskentataulukossa. Tämä alue on toinen matriisi, jonka funktio etsii.
-
Syötä , (pilkku) soluviittauksen E3 jälkeen viimeistelläksesi MATCH-funktion Lookup_array-argumentin.
Image - Jätä valintaikkuna auki opetusohjelman seuraavaa vaihetta varten.
Lisää MATCH Type -argumentti
MATCH-funktion kolmas ja viimeinen argumentti on Match_type-argumentti. Tämä argumentti kertoo Excelille, kuinka Lookup_value sovitetaan hakutaulukon arvoihin. Käytettävissä olevat vaihtoehdot ovat 1, 0 tai -1.
Tämä argumentti on valinnainen. Jos se jätetään pois, funktio käyttää oletusarvoa 1.
- Jos Match_type=1 tai se jätetään pois, MATCH löytää suurimman arvon, joka on pienempi tai yhtä suuri kuin hakuarvo. Lookup_array-tiedot on lajiteltava nousevaan järjestykseen.
- Jos Match_type=0, MATCH löytää ensimmäisen arvon, joka on yhtä suuri kuin Lookup_value. Lookup_array-tiedot voidaan lajitella mihin tahansa järjestykseen.
- Jos Match_type=-1, MATCH löytää pienimmän arvon, joka on suurempi tai yhtä suuri kuin Lookup_value. Lookup_array-tiedot on lajiteltava laskevaan järjestykseen.
Syötä nämä vaiheet edellisessä vaiheessa kirjoitetun pilkun jälkeen riville INDEX-funktion rivin_numero:
- Syötä 0 (nolla) pilkun jälkeen tekstikenttään Rivin_numero. Tämä numero saa sisäkkäisen funktion palauttamaan tarkat vastaavuudet soluihin D3 ja E3 syötetyille termeille.
-
Syötä ) (päättävä hakasulku) suorittaaksesi MATCH-toiminnon loppuun.
Image - Jätä valintaikkuna auki opetusohjelman seuraavaa vaihetta varten.
Lopeta INDEX-toiminto
MATCH-toiminto on tehty. On aika siirtyä valintaikkunan Column_num-tekstiruutuun ja kirjoittaa INDEX-funktion viimeinen argumentti. Tämä argumentti kertoo Excelille, että sarakkeen numero on välillä D6–F11. Tältä alueelta se löytää funktion palauttamat tiedot. Tässä tapauksessa titaaniwidgetien toimittaja.
- Aseta kohdistin Column_num-tekstikenttään.
-
Syötä 3 (numero kolme). Tämä numero käskee kaavan etsimään tietoja välin D6 - F11 kolmannesta sarakkeesta.
Image - Jätä valintaikkuna auki opetusohjelman seuraavaa vaihetta varten.
Luo taulukkokaava
Muuta sisäkkäinen funktio taulukkokaavaksi ennen valintaikkunan sulkemista. Tämän taulukon avulla funktio voi etsiä useita termejä tietotaulukosta. Tässä opetusohjelmassa yhdistetään kaksi termiä: widgetit sarakkeesta 1 ja titaani sarakkeesta 2.
Luo taulukkokaava Excelissä painamalla CTRL, SHIFT ja ENTERnäppäintä samanaikaisesti. Kun toimintoa painetaan, sitä ympäröivät kiharat a altosulut, mikä osoittaa, että funktio on nyt matriisi.
- Sulje valintaikkuna valitsemalla OK. Valitse Excel for Macissa Valmis.
- Valitse solu F3 nähdäksesi kaavan ja aseta sitten kohdistin kaavan loppuun kaavapalkissa.
- Jos haluat muuntaa kaavan taulukoksi, paina CTRL+ SHIFT+ ENTER.
- A N/A -virhe ilmestyy soluun F3. Tämä on solu, johon funktio syötettiin.
-
N/A-virhe ilmestyy soluun F3, koska solut D3 ja E3 ovat tyhjiä. D3 ja E3 ovat soluja, joista funktio etsii hakuarvon. Kun tiedot on lisätty näihin kahteen soluun, virhe korvataan tietokannan tiedoilla.
Image
Lisää hakukriteerit
Viimeinen vaihe on lisätä hakutermit laskentataulukkoon. Tämä vaihe vastaa termejä Widgets sarakkeesta 1 ja Titanium sarakkeesta 2.
Jos kaava löytää osuman molemmille termeille tietokannan asianmukaisissa sarakkeissa, se palauttaa arvon kolmannesta sarakkeesta.
- Valitse solu D3.
- Syötä Widgets.
- Valitse solu E3.
- Kirjoita Titanium ja paina Enter.
- Toimittajan nimi, Widgets Inc., näkyy solussa F3. Tämä on ainoa listattu toimittaja, joka myy titaaniwidgetejä.
-
Valitse solu F3. Funktio näkyy laskentataulukon yläpuolella olevassa kaavapalkissa.
{=INDEKSI(D6:F11, MATCH(D3&E3, D6:D11&E6:E11, 0), 3)}
Tässä esimerkissä on vain yksi titaaniwidgetien toimittaja. Jos toimittajia olisi ollut useampi kuin yksi, funktio palauttaa tietokannassa ensimmäisenä mainitun toimittajan.
Image
