Excel-haun kaava useilla kriteereillä

Käyttämällä taulukkomallia Excelissä voimme luoda etsintäkaavan, joka käyttää useita kriteerejä tietojen löytämiseksi tietokannassa tai tietolomakkeessa.

Taulukon kaava sisältää MATCH- toiminnon pesemisen INDEX- toiminnon sisällä.

Tämä opetusohjelma sisältää vaihe vaiheelta esimerkkinä sellaisen lookup-kaavan luomisesta, joka käyttää useita kriteerejä löytää titaaniprofiilien toimittaja näytetietokannasta.

Ohjausaiheiden alla olevien ohjeiden mukaisesti kävelee läpi luomalla ja käyttämällä yllä olevassa kuvassa näkyvää kaavaa.

01/09

Ohjaustietojen syöttäminen

Oppaassa ensimmäinen askel on syöttää tiedot Excel- laskentataulukkoon .

Jotta tutoroidaksesi vaiheet, tutustu yllä kuvattuun tietoon seuraaviin soluihin .

• Syötä datan ylätaso soluihin D1 - F2
• Syötä toinen alue soluihin D5 - F11

Rivit 3 ja 4 jätetään tyhjäksi tämän opetusohjelman aikana luodun taulukkokaavan mukauttamiseksi.

Oppitunti ei sisällä kuvassa näkyvää muotoilua, mutta tämä ei vaikuta hakutaulukon toimintaan.

Tiedot edellä kuvatuista formaattivaihtoehdoista ovat tässä Basic Excel Formatting Tutorialissa.

02/09

INDEX-toiminnon käynnistäminen

INDEX-toiminto on yksi harvoista Excelissä, jossa on useita muotoja. Funktiolla on Array-lomake ja viitenumero .

Array-lomake palauttaa varsinaisen datan tietokannasta tai tietotaulukosta, kun taas viitemuodossa annetaan taulukon tietojen solun viite tai sijainti.

Tässä opetusohjelmassa käytämme Array-lomaketta, koska haluamme tietää tavarantoimittajan nimen titaani-widgetteistä sen sijaan, että se olisi soluviittaus tähän toimittajiin tietokantaamme.

Jokaisella lomakkeella on erilainen luettelo argumentteista, jotka on valittava ennen funktion aloittamista.

Opetusvaiheet

1. Napsauta solua F3 saadaksesi sen aktiiviseksi soluksi . Tässä me annamme sisäkkäisen toiminnon.
2. Napsauta nauha- valikon Lomakkeet -välilehteä.
3. Avaa toiminto avattava luettelo valitsemalla Etsi ja vertailu nauhalta.
4. Napsauta luettelossa olevaa INDEX-painiketta , jolloin näyttöön tulee Select Arguments -valintaikkuna .
5. Valitse valintaikkunassa array, row_num, col_num .
6. Avaa INDEX-toiminnon valintaikkuna valitsemalla OK.

03/09

Syöttäminen INDEX-funktio Array-argumenttiin

Ensimmäinen vaadittu argumentti on Array-argumentti. Tämä argumentti määrittää halutun datan etsittävien solujen määrän.

Tätä opetusohjelmaa varten tämä argumentti on meidän näyte tietokanta .

Opetusvaiheet

1. Napsauta INDEX-toiminnon valintaikkunassa Array- riviä.
2. Korosta taulukot D6 - F11 taulukkoon, jotta alue otetaan valintaikkunaan.

04/09

Nested MATCH -toiminnon aloitus

Kun peset toisen toiminnon toisessa, ei ole mahdollista avata toisen tai sisäkkäisen toiminnon valintaikkunaa syöttää tarvittavat argumentit .

Sisäkkäinen toiminto on kirjoitettava yhtenä ensimmäisen argumentin argumentaationa.

Tässä opetusohjelmassa sisäkkäinen MATCH-funktio ja sen argumentit syötetään INDEX-toiminnon valintaikkunan toiseen riviin - Row_num- riviin.

On tärkeää huomata, että kun syötetään tehtäviä manuaalisesti, funktiokeskustelut erotetaan toisistaan ​​pilkulla "," .

MATCH-toiminnon haku-arvo-argumentin syöttäminen

Ensimmäinen askel sisäkkäisen MATCH-toiminnon syöttämisessä on syöttää Lookup_value- argumentti.

Lookup_value on hakutermin sijainti tai soluviite , jonka haluamme sovittaa tietokantaan.

Tavallisesti Lookup_value hyväksyy vain yhden hakukriteerin tai termin. Jotta etsitään useita kriteerejä, meidän on laajennettava Lookup_value-arvoa .

Tämä tehdään yhdistämällä tai yhdistämällä kaksi tai useampia soluviitteitä yhdessä käyttämällä merkkiä " & ".

Opetusvaiheet

1. Napsauta INDEX-toiminnon valintaikkunassa Row_num- riviä.
2. Kirjoita toiminnon nimi, jota seuraa avoin pyöristys " ( "
3. Napsauta solua D3 ja syötä soluviittaus valintaikkunaan.
4. Kirjoita amperia ja " & " soluviitteen D3 jälkeen toisen soluviitteen lisäämiseksi.
5. Napsauta solua E3 ja anna tämä toinen soluviite valintaikkunaan.
6. Kirjoita pilkku "," kun soluviite E3 täyttää MATCH-toiminnon Lookup_value- argumentin merkinnän .
7. Jätä INDEX-toiminnon valintaikkuna auki opetusohjelman seuraavaan vaiheeseen.

Tutorial viimeisessä vaiheessa Lookup_values ​​syötetään laskentataulukoihin D3 ja E3.

05/09

MATCH-toiminnon Lookup_array lisääminen

Tämä vaihe kattaa Lookup_array- argumentin lisäämisen sisäkkäiselle MATCH-toiminnolle.

Lookup_array on solujen alue, jonka MATCH-funktio etsii löytääkseen tutoriaalin edellisessä vaiheessa lisätyn Lookup_value- argumentin.

Koska olemme löytäneet kaksi hakukenttää Lookup_array- argumentissa, meidän on tehtävä sama Lookup_array-nimiselle . MATCH-funktio hakee vain yhden taulukon jokaiselle määritellylle termille.

Jos syötät useita ryhmiä, käytämme uudelleen ampersand ja " & " ketjuttamalla ryhmät yhteen.

Opetusvaiheet

Nämä vaiheet on syötettävä INDEX-toiminnon valintaikkunan Row_num -rivin edelliseen vaiheeseen syötetyn pilkun jälkeen.

1. Napsauta Row_num- rivillä pilkulla, kun haluat sijoittaa pisteen pisteen nykyisen merkinnän lopussa.
2. Korosta kentät D6-D11 taulukkoon, jotta voit syöttää alueen. Tämä on ensimmäinen taulukko, jonka tehtävänä on etsiä.
3. Kirjoita etumerkit ja " & " soluviitteiden D6: D11 jälkeen, koska haluamme, että funktio hakee kahta ryhmää.
4. Korosta taulukot E6-E11 taulukon sisälle alueelle. Tämä on toinen taulukko, jonka tehtävänä on etsiä.
5. Kirjoita pilkku "," kun soluviite E3 täyttää MATCH-toiminnon Lookup_array- argumentin merkinnän .
6. Jätä INDEX-toiminnon valintaikkuna auki opetusohjelman seuraavaan vaiheeseen.

06/09

Ottotyypin lisääminen ja MATCH-toiminnon täydentäminen

MATCH-toiminnon kolmas ja viimeinen argumentti on Match_type-argumentti.

Tämä argumentti kertoo Excelille, kuinka Hae haku_arvo saadaan arvoilla Lookup_array. Valinnat ovat: 1, 0 tai -1.

Tämä argumentti on valinnainen. Jos se jätetään pois, toiminto 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 Lookup_value. Lookup_array-tiedot on lajitettava nousevaan järjestykseen.
• jos match_type = 0: MATCH löytää ensimmäisen arvon, joka on täsmälleen sama kuin Lookup_value. Lookup_array-tiedot voidaan lajitella missä tahansa järjestyksessä.
• jos Match_type = -1: MATCH löytää pienimmän arvon, joka on suurempi tai yhtä suuri kuin Lookup_value. Lookup_array-tiedot on lajiteltava laskevassa järjestyksessä.

Opetusvaiheet

Nämä vaiheet on syötettävä INDEX-toiminnon valintaikkunan Row_num -rivin edelliseen vaiheeseen syötetyn pilkun jälkeen.

1. Kirjoita Row_num- rivin pilkun jälkeen nolla " 0 ", koska haluamme, että sisäkkäinen toiminto palauttaa täsmälliset rivit soluihin D3 ja E3.
2. Kirjoita sulkemisen pyöreä kiinnike " ) " MATCH-toiminnon täydentämiseksi.
3. Jätä INDEX-toiminnon valintaikkuna auki opetusohjelman seuraavaan vaiheeseen.

07/09

Palaa INDEX-toimintoon

Nyt kun MATCH-toiminto on tehty, siirrymme avoimen valintaikkunan kolmanteen riviin ja anna INDEX-toiminnon viimeinen argumentti .

Tämä kolmas ja viimeinen argumentti on Column_num- argumentti, joka kertoo Excel-sarakkeen numeron D6: stä F11: een, missä se löytää toiminnon palauttamat tiedot. Tässä tapauksessa toimittaja titaaniyhdistelmille .

Opetusvaiheet

1. Napsauta valintaikkunan Column_num- rivillä.
2. Kirjoita tällä rivillä numero kolme " 3 " (ei lainausmerkkejä), koska etsimme tietoja D6: n ja F11: n kolmannessa sarakkeessa.
3. Älä napsauta OK tai sulje INDEX-toiminnon valintaikkuna. Sen on pysyttävä avoimena opetusohjelman seuraavassa vaiheessa - luomalla taulukkokaava .

08/09

Array-kaavan luominen

Ennen sulkemisen valintaikkunaa meidän täytyy kääntää sisäkkäinen toimintamme taulukkokaavaksi .

Taulukon kaava on se, jonka avulla se voi etsiä useita termejä datatietokuvassa. Tässä opetusohjelmassa etsimme vastaamaan kahta termiä: Widgetit sarakkeesta 1 ja titaani sarakkeesta 2.

Excel-kaavion luominen tapahtuu painamalla CTRL- , SHIFT- ja ENTER- näppäimiä samanaikaisesti.

Näiden näppäinten puristamisen vaikutus on ympäröimällä funktio kiharaisilla palkeilla: {}, mikä osoittaa, että se on nyt taulukkokaava.

Opetusvaiheet

1. Kun tämä opetusohjelman edellisestä vaiheesta on vielä avoin valintaikkuna, paina näppäimistön CTRL- ja SHIFT- näppäimiä ja pidä sitten painettuna ja paina ENTER- näppäintä ja vapauta se.
2. Jos se on tehty oikein, valintaikkuna sulkeutuu ja # N / A- virhe ilmestyy soluun F3 - soluun, johon olemme syöttäneet toiminnon.
3. # N / A- virhe esiintyy solussa F3, koska solut D3 ja E3 ovat tyhjiä. D3 ja E3 ovat soluja, joissa kerroimme, että funktio löytää Lookup_values-ohjeen vaiheessa 5. Kun nämä kaksi solua lisätään tietoihin, virhe korvataan tietokannan tiedoilla.

09/09

Hakuehtojen lisääminen

Tutorialin viimeinen vaihe on lisätä hakutermit laskentatauluun.

Kuten edellisessä vaiheessa mainittiin, etsimme vastaavia termejä Widgetit sarakkeesta 1 ja Titanium sarakkeesta 2.

Jos ja vain, jos kaava löytää vastaavuuden molemmille termeille tietokannassa olevissa sopivissa sarakkeissa, palauttaako se kolmannelta sarakkeelta.

Opetusvaiheet

1. Napsauta solua D3.
2. Kirjoita widgetit ja paina näppäimistön Enter- näppäintä.
3. Napsauta solua E3.
4. Kirjoita Titanium ja paina näppäimistön Enter- näppäintä.
5. Toimittajan nimi Widgets Inc. pitäisi näkyä solussa F3 - toiminnon sijainnista, koska se on ainoa toimittaja, joka myy Titanium-widgetit.
6. Kun napsautat solua F3, koko toiminto
   {= INDEKSI (D6: F11, MATCH (D3 & E3, D6: D11 & E6: E11, 0), 3)}
   näkyy laskentataulukon yläpuolella olevassa kaavassa .

Huomaa: Esimerkissämme oli vain yksi toimittaja titaani-widgetteistä. Jos palveluntarjoaja oli useampi kuin yksi, toimittaja ilmoittautuu ensin tietokantaan.