Luodaanko Excel-hakukaava useilla ehdoilla?

Mitä tietää

• Luo ensin INDEX-funktio ja käynnistä sitten sisäkkäinen MATCH-funktio antamalla Lookup_value-argumentti.
• Lisää sitten Lookup_array-argumentti ja sen jälkeen Match_type argumentti ja määritä sitten sarakealue.
• Muuta sitten sisäkkäinen funktio taulukkokaavaksi painamalla . työntää Ctrl+Siirtää+Tulla sisään. 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 koskevat Excel for Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 ja Excel for Mac.

Seuraa opetusohjelmaa

Noudata tämän opetusohjelman vaiheita kirjoittamalla näytetiedot seuraaviin soluihin alla olevan kuvan mukaisesti. Rivit 3 ja 4 on jätetty tyhjiksi tämän opetusohjelman aikana luodun taulukkokaavan mukauttamiseksi. (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ä

INDEX-funktio on yksi harvoista Excelin funktioista, jolla on useita muotoja. Funktiolla on Array Form ja Reference Form. Matriisilomake palauttaa tiedot tietokannasta tai tietotaulukosta. Viitelomake tarjoaa soluviittauksen tai tietojen sijainnin taulukossa. Tässä opetusohjelmassa käytetään taulukkolomaketta etsimään titaaniwidgetien toimittajan nimi tietokannan soluviittauksen sijaan. Luo INDEX-funktio seuraavasti:

1. Valitse solu F3 tehdä siitä aktiivinen solu. Sisäkkäinen funktio syötetään tähän soluun.

2. Mene kaavat.

   

3. Valita Katso ja katso avataksesi avattavan toimintoluettelon.

4. Valitse SISÄLLYSLUETTELO avaamaan Valitse argumentit valintaikkuna.

5. Valita taulukko, rivin_numero, sarakkeen_numero.

6. Valitse Okei avaamaan Funktioargumentit valintaikkuna. Excel for Macissa Kaavan rakennustyökalu avautuu.

7. Aseta kohdistin kohtaan matriisi tekstilaatikko.

8. Korosta soluja D6 avulla F11 laskentataulukossa syöttääksesi alueen valintaikkunaan. Jätä Function Arguments -valintaikkuna auki. Kaava ei ole valmis. Täytät kaavan alla olevissa ohjeissa.

   

Käynnistä sisäkkäinen MATCH-funktio

Kun yksi funktio on sisäkkäin toisen sisällä, ei ole mahdollista avata toisen tai sisäkkäisen funktion kaavanmuodostajaa tarvittavien argumenttien syöttämiseksi. Sisäkkäinen funktio on syötettävä yhdeksi ensimmäisen funktion argumenteista. Kun syötät funktioita manuaalisesti, funktion argumentit erotetaan pilkulla. Ensimmäinen vaihe sisäkkäisen MATCH-funktion syöttämiseksi on syöttää Lookup_value-argumentti. Lookup_value on tietokannasta löydettävän hakutermin sijainti- tai soluviittaus. Lookup_value hyväksyy vain yhden hakuehdon tai -termin. Jos haluat etsiä useita ehtoja, laajenna Lookup_value -arvoa ketjuttamalla tai ketjuttamalla kaksi tai useampi soluviittaus et-merkillä (&).

1. Vuonna Funktioargumentit -valintaikkunassa, aseta kohdistin kohtaan Rivi_nm tekstilaatikko.

2. Tulla sisään KILPAILU(.

3. Valitse solu D3 syöttääksesi soluviittauksen valintaikkunaan.

4. Tulla sisään & (et-merkki) soluviittauksen jälkeen D3 lisätäksesi toisen soluviittauksen.

5. Valitse solu E3 syöttääksesi toisen soluviittauksen.

6. Tulla sisään , (pilkku) soluviittauksen E3 jälkeen viimeistelläksesi MATCH-funktion Lookup_value-argumentin syöttämisen.

   

   Opetusohjelman viimeisessä vaiheessa Lookup_values’et syötetään laskentataulukon soluihin D3 ja E3.

Suorita sisäkkäinen MATCH-funktio

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 on tunnistettu kaksi hakukenttää, sama on tehtävä Lookup_array-argumentille. MATCH-funktio löytää vain yhden taulukon kullekin määritetylle termille. Jos haluat syöttää useita taulukoita, ketjuta taulukot et-merkillä.

1. Aseta kohdistin tietojen loppuun Rivi_nm tekstilaatikko. Kohdistin näkyy pilkun jälkeen nykyisen merkinnän lopussa.

2. Korosta soluja D6 avulla D11 laskentataulukossa syöttääksesi alueen. Tämä alue on ensimmäinen matriisi, jota funktio etsii.

3. Tulla sisään & (et-merkki) soluviittausten jälkeen D6:D11. Tämä symboli saa funktion etsimään kahta taulukkoa.

4. Korosta soluja E6 avulla E11 laskentataulukossa syöttääksesi alueen. Tämä alue on toinen taulukko, jota funktio etsii.

5. Tulla sisään , (pilkku) soluviittauksen jälkeen E3 suorittaaksesi MATCH-funktion Lookup_array-argumentin syöttämisen loppuun.

   

6. Jätä valintaikkuna auki opetusohjelman seuraavaa vaihetta varten.

Lisää MATCH-tyypin argumentti

Kolmas ja viimeinen argumentti MATCH-funktio on Match_type Perustelu. Tämä argumentti kertoo Excelille, kuinka Lookup_value vastaa Lookup_arrayssa olevia arvoja. Käytettävissä olevat vaihtoehdot ovat 1, 0 tai -1. Tämä argumentti on valinnainen. Jos jätetään pois, funktio käyttää oletusarvoa 1.

• Jos Match_type = 1 tai 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 INDEX-funktion rivin_numero riville:

1. Tulla sisään 0 (nolla) desimaalipilkun jälkeen Rivi_nm tekstilaatikko. Tämä numero saa sisäkkäisen funktion palauttamaan tarkat vastaavuudet soluihin D3 ja E3 syötetyille termeille.

2. Tulla sisään ) (sulkeen) suorittaaksesi MATCH-toiminnon loppuun.

   

3. Jätä valintaikkuna auki opetusohjelman seuraavaa vaihetta varten.

Suorita INDEX-toiminto loppuun

MATCH-toiminto on valmis. 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ästä alueesta se löytää funktion palauttamat tiedot. Tässä tapauksessa titaanisten widgetien toimittaja.

1. Aseta kohdistin kohtaan sarakkeen_numero tekstilaatikko.

2. Tulla sisään 3 (numero kolme). Tämä numero käskee kaavan etsimään tietoja välin D6–F11 kolmannesta sarakkeesta.

   

3. 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 kaksi termiä vastaavat: widgetit sarakkeesta 1 ja titaani sarakkeesta 2. Voit luoda taulukkokaavan Excelissä painamalla CTRL, SIIRTÄÄja TULLA SISÄÄN avaimet samanaikaisesti. Kun toimintoa painetaan, sitä ympäröivät kiharat aaltosulut, mikä osoittaa, että funktio on nyt matriisi.

1. Valitse Okei sulkeaksesi valintaikkunan. Valitse Excel for Macissa Tehty.

2. Valitse solu F3 tarkastellaksesi kaavaa asettamalla kohdistin kaavan loppuun kaavapalkissa.

3. Jos haluat muuntaa kaavan taulukoksi, paina CTRL+SIIRTÄÄ+TULLA SISÄÄN.

4. #N/A virhe ilmestyy soluun F3. Tämä on solu, johon funktio syötettiin.

5. #N/A-virhe näkyy solussa F3, koska solut D3 ja E3 ovat tyhjiä. D3 ja E3 ovat soluja, joista funktio etsii Lookup_value. Kun tiedot on lisätty näihin kahteen soluun, virhe korvataan tietokannan tiedoilla.

   

Lisää hakukriteerit

Viimeinen vaihe on lisätä hakutermit laskentataulukkoon. Tämä vaihe vastaa termejä Widgetit sarakkeesta 1 ja Titaani sarakkeesta 2. Jos kaava löytää osuman molemmille termeille tietokannan oikeista sarakkeista, se palauttaa arvon kolmannesta sarakkeesta.

1. Valitse solu D3.

2. Tulla sisään Widgetit.

3. Valitse solu E3.

4. Tyyppi Titaanija paina Tulla sisään.

5. Toimittajan nimi, Widgets Inc., näkyy solussa F3. Tämä on ainoa listattu toimittaja, joka myy titaaniwidgetejä.

6. 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 oli useampi kuin yksi, funktio palauttaa tietokannassa ensimmäisenä mainitun toimittajan.