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ää vaiheittaisen esimerkin etsintäkaavion luomisesta, joka käyttää useita kriteerejätitaani-widgetit näytetietokannassa. Seuraa pitkin oppia, kuinka helppoa on luoda omat hakutaulukosi.
Valmista Excel-työkirjan tiedot
Jotta voit seurata tässä opetusohjelmassa annettuja ohjeita, sinun on aloitettava syöttämällä näytetiedot seuraaviin soluihin yllä olevan kuvan mukaisesti. Rivit 3 ja 4 jätetään tyhjäksi tämän opetusohjelman aikana luodun taulukkokaavan mukauttamiseksi.
- Syötä datan huipputiedot soluihin D1 että F2
- Anna toinen alue soluihin D5 että F11
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/08Luo INDEX-funktio Excelissä
INDEKSI toiminto on yksi harvoista Excelissä, jossa on useita muotoja. Toiminnolla on Array-lomake ja a Viitelomake. 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-widgeteistä sen sijaan, että se olisi soluviittaus tähän toimittajiin tietokantaamme. Luo INDEX-toiminto seuraavasti:
- Napsauta solua F3 jotta se olisi aktiivinen solu - tähän päästään sisäänsyötetty toiminto.
- Klikkaa kaavat nauhavalikon välilehti.
- Valita Haku ja viite nauhan palkista avataksesi toiminnon pudota alas.
- Klikkaa INDEKSI luettelossa tuoda esiin Valitse Argumentit valintaikkuna.
- Valitse array, row_num, col_num valintaikkunassa.
- Klikkaus kunnossa avaa INDEX-toiminnon valintaikkuna; tämä avaa Formula Builderin Excelissä.
- Vuonna Formula Builder, Klikkaa ryhmä linja.
- Korosta solut D6 että F11, napsauttamalla ja vetämällä, kirjoittamalla laskentataulukon rakennusosaan.
Excelin vanhemmissa versioissa Formula Builder korvataan Function Arguments ikkuna. Käytä tässä opetusohjelmassa mainittuja ohjeita ikkunaan.
03/08Aloita Nested MATCH -toiminto
Kun peset toisen toiminnon sisällä toista, ei ole mahdollista avata toista tai sisäkkäistä funktiota kaavanmuodostaja o syöttä tarvittavat argumentit. Sisäkkäinen toiminto on kirjoitettava yhtenä ensimmäisen toiminnon järjestyksessä.
Kun syötät toimintoja manuaalisesti, funktiokeskustelut erotetaan toisistaan pilkulla.
Ensimmäinen askel sisäkkäisen MATCH-toiminnon syöttämisessä on syöttää hakuarvo Perustelu. 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; Thänet tehdään ketjuttamalla tai yhdistämällä kaksi tai useampia soluviitteitä yhdessä käyttäen ampersand-symbolia - &.
- Vuonna Formula Builder, Klikkaa ROW_NUM linja.
- Kirjoita toiminnon nimi OTTELU jota seuraa avoin pyöreä kiinnike.
- Napsauta solua D3 syöttää soluviittaus valintaikkunaan.
- Kirjoita ampersand ja soluviitteen jälkeen D3 toisen soluviitteen lisäämiseksi.
- Napsauta solua E3 syöttää tämä toinen soluviite.
- Kirjoita pilkku soluviitteen jälkeen E3 täyttää MATCH-toiminnon merkinnät hakuarvo Perustelu.
Tutorial viimeisessä vaiheessa meidän Lookup_values syötetään laskentataulukoihin D3 ja E3.
04/08Suorita Nested MATCH -toiminto
Tämä vaihe käsittää lisäyksen etsintäjono argumentti sisäkkäiselle OTTELU toimia. 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 kullekin määritellylle termille, joten syöttääksesi useita taulukoita, käytämme jälleen kerran ampersand ja ketjutetaan ryhmät yhteen.
Nämä vaiheet tulee syöttää sen jälkeen, kun pilkku on syötetty edelliseen vaiheeseen ROW_NUM linja INDEKSI toimia.
- Klikkaa ROW_NUM rivin jälkeen pisteen asettamiseksi pisteen kohdalle nykyisen merkinnän lopussa.
- Korosta solut D6 että D11 taulukon sisältämälle alueelle - tämä on ensimmäinen taulukko, jonka tehtävänä on etsiä.
- Kirjoita ampersand ja soluviitteiden jälkeen D6: D11 koska haluamme, että funktio etsii kahta ryhmää.
- Korosta solut E6 että E11 kirjoitetaan laskentataulukkoon - tämä on toinen taulukko, jonka tehtävänä on etsiä.
- Kirjoita pilkku soluviitteen jälkeen E3 loppuun OTTELU funktion etsintäjono Perustelu.
- Jätä INDEKSI toiminto -valintaikkuna avautuu opetusohjelman seuraavaan vaiheeseen.
Lisää MATCH-tyypin argumentti
MATCH - toiminnon kolmas ja viimeinen argumentti on match_type Perustelu; se kertoo Excelille, miten se vastaa hakuarvo arvoja Lookup_array - käytettävissä olevat vaihtoehdot 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ää pienemmän arvon, joka on suurempi tai yhtä suuri kuin Lookup_value. Lookup_array-tiedot on lajiteltava laskevassa järjestyksessä.
Nämä vaiheet tulee syöttää sen jälkeen, kun pilkku on syötetty edelliseen vaiheeseen ROW_NUM linja INDEKSI toimia.
- Seuraavana pilkulla ROW_NUM rivi, kirjoita nolla - 0 - koska haluamme, että sisäkkäinen toiminto palauttaa täsmälliset soluihin syötettävät ehdot D3 ja E3.
- Kirjoita sulkukierroksen kiinnike - ) - loppuun OTTELU toimia.
- Jätä Formula Builder avaa opetusohjelman seuraavaan vaiheeseen.
INDEX-toiminnon viimeistely
Nyt kun OTTELU toiminto tehdään, siirrymme kaavan rakennuttajan kolmanteen riviin ja syötä viimeinen argumentti INDEKSI toimia. Tämä kolmas ja viimeinen väite on sarakenro argumentti, joka kertoo Excel-sarjan sarjanumeron D6 että F11; tässä se löytää tiedot, jotka haluamme palauttaa funktio. Tässä tapauksessa tavarantoimittaja titaani widgetit.
- Klikkaa sarakenro linja.
- Anna numero kolme - 3 - tällä rivillä, koska etsimme tietoja alueen kolmannessa sarakkeessa D6 että F11.
- Jälleen, jätä Formula Builder avaa opetusohjelman seuraavaan vaiheeseen.
Array-kaavan luominen
Ennen sulkemista Formula Builder, meidän täytyy kääntää sisäkkäinen toimintamme taulukkokaavioksi; tämä on se, jonka avulla toimintamme etsii useita termejä datatietokuvassa. Tässä opetusohjelmassa etsimme vastaamaan kahta termiä: widgetit sarakkeista 1 ja 2 Titaani sarakkeesta 2.
Excel-kaavion luominen tapahtuu painamalla CTRL, SIIRTÄÄ, ja TULLA SISÄÄN näppäimiä samanaikaisesti. Kun painetaan, toimintoa ympäröidään kiharaiset ripustimet, mikä osoittaa, että funktio on nyt taulukko.
- Sulje Formula Builder napsauttamalla Tehty painiketta.
- Valitse sitten solu F4 ja paina Tulla sisään avain kaavan tarkasteluun.
- Voit muuntaa kaavan taulukkoon painamalla samanaikaisesti CTRL + SHIFT + ENTER näppäimistöllä.
- Jos se on tehty oikein a # N / A virhe ilmestyy soluun F3 - solu, johon syötimme toiminnon.
- # N / A solussa näkyy virhe F3 koska solut D3 ja E3 ovat tyhjiä. D3 ja E3 ovat soluja, joissa kerroimme toiminnon löytävän Lookup_values. Kun nämä kaksi solua lisätään tietoihin, virhe korvataan tietokannan tiedoilla.
Hakuehtojen lisääminen
Lopuksi lisäämme hakutermit laskentatauluun. Kuten edellisessä vaiheessa mainittiin, etsimme vastaavia ehtoja widgetit sarakkeista 1 ja 2 Titaani sarakkeesta 2. Jos ja vain, jos kaava löytää vastaavuuden molemmille termeille tietokannassa olevissa sopivissa sarakkeissa, palauttaa arvon kolmannesta sarakkeesta.
- Napsauta solua D3.
- Tyyppi widgetit ja paina Tulla sisään näppäintä.
- Napsauta solua E3.
- Tyyppi Titaani ja paina Tulla sisään näppäintä.
- Toimittajan nimi Widgets Inc. pitäisi näkyä solussa F3 - toiminnon sijainnista, koska se on ainoa toimittaja, joka myy Titanium-widgettejä.
- Kun napsautat solua F3, koko toiminto näkyy laskentataulukon yläpuolella olevassa kaavassa.
{= INDEKSI (D6: F11, MATCH (D3 & E3, D6: D11 & E6: E11, 0), 3)}
Esimerkissämme oli vain yksi toimittaja titaaniyhdistelmille. Jos palveluntarjoaja oli useampi kuin yksi, toimittaja ilmoittautuu ensin tietokantaan.
