Z uporabo formule matrike v Excelu lahko ustvarimo formulo za iskanje, ki uporablja več kriterijev za iskanje podatkov v podatkovni zbirki ali tabeli podatkov. Formula matrike vključuje gnezdenje funkcije MATCH znotraj funkcije INDEX.
Ta vadnica vsebuje korak za korakom oblikovanje lookup formula, ki uporablja več kriterijev za iskanje dobaviteljatitanium dodatki v vzorčni bazi podatkov. Sledite skupaj, če želite izvedeti, kako enostavno je ustvariti lastne formule po meri.
Pripravite Delovni list Excel s podatki
Če želite slediti korakom v tej vadnici, boste morali začeti z vnosom vzorčnih podatkov v naslednje celice, kot je prikazano na zgornji sliki. Vrstice 3 in 4 ostanejo prazna, da se prilagodi formulo matrike, ustvarjena v tem vadnici.
- Vnesite vrsto podatkov v celice D1 do F2
- Vnesite drugi obseg v celice D5 do F11
Vadnica ne vključuje oblikovanja, ki je vidna na sliki, vendar to ne bo vplivalo na delovanje formule za iskanje. Informacije o možnostih oblikovanja, podobne tistim, ki so prikazane zgoraj, so na voljo v tem priročniku Tutorial za oblikovanje v Excelu.
02 od 08Ustvarite funkcijo INDEX v Excelu
The INDEKS funkcija je ena izmed redkih v Excelu, ki ima več oblik. Funkcija ima Oblika obrazca in a Referenčni obrazec. Obrazec Array vrne dejanske podatke iz podatkovne baze ali tabele podatkov, medtem ko vam referenčna obrazec poda referenco celice ali lokacijo podatkov v tabeli.
V tej vadnici bomo uporabili obrazec Array, ker želimo vedeti ime dobavitelja za pripomočke titana in ne referenco celice tega dobavitelja v naši podatkovni bazi. Za ustvarjanje funkcije INDEX sledite tem korakom:
- Kliknite na celico F3 da bi bila aktivna celica - tukaj bomo vstopili v ugnezdeno funkcijo.
- Kliknite na Formule jeziček v meniju traku.
- Izberite Iskanje in sklicevanje od traku, da odprete funkcijo.
- Kliknite na INDEKS na seznamu za prikaz Izberite Argumenti pogovorno okno.
- Izberite array, row_num, col_num možnost v pogovornem oknu.
- Kliknite v redu da odprete pogovorno okno funkcije INDEX; to bo odprlo Formula Builder v Excelu.
- V Ljubljani Formula Builder, kliknite na Array line.
- Označite celice D6 do F11, tako da kliknete in povlečete na delovnem listu, da vstopite v obseg v graditelj.
V starejših različicah Excela Formula Builder bo nadomestil Argumenti funkcij okno. Uporabite enake korake, kot so omenjene v tej vadnici v okno.
03 od 08Zaženite funkcijo Privzete matrike
Kadar je ena funkcija vgrajena v drugo, ni mogoče odpreti graditelja formule drugega ali ugnezdene funkcije o vnesite potrebne argumente. Vgnezdena funkcija mora biti vnesena kot ena od razvrščenih v prvo funkcijo.
Pri vnosu funkcij ročno, so argumenti funkcije ločeni drug od drugega z vejico.
Prvi korak pri vnosu ugnežene funkcije MATCH je vnos v Lookup_value prepir. Lookup_value bo referenca lokacije ali celice za iskalni izraz, ki ga želimo ujemati v podatkovni bazi.
Običajno Lookup_value sprejema samo eno iskalno merilo ali izraz. Če želite poiskati več kriterijev, moramo razširiti Lookup_value; tnjegov se opravi s povezovanjem ali povezovanjem dveh ali več celičnih referenc skupaj z ampersand simbolom - &.
- V Ljubljani Formula Builder, kliknite na Row_num line.
- Vnesite ime funkcije MATCH ki ji sledi odprt okrogel nosilec.
- Kliknite na celico D3 vnesite referenco celice v pogovorno okno.
- Vnesite ampersand po referenci celice D3 da dodate drugo referenco celice.
- Kliknite na celico E3 za vnos te druge referenčne številke.
- Za referenco celice vnesite vejico E3 za dokončanje vnosa funkcije MATCH Lookup_value prepir.
V zadnjem koraku vadnice, naša Lookup_values bodo vnesene v celice D3 in E3 delovnega lista.
04 od 08Dokončajte funkcijo Privzeto MATCH
Ta korak pokriva dodajanje Lookup_array argument za ugnezdeni MATCH funkcijo. Lookup_array je obseg celic, ki jih bo funkcija MATCH poiskala, da bi našli argument Lookup_value, dodan v prejšnjem koraku vadnice.
Ker smo v argumentu Lookup_array identificirali dve iskalni polji, moramo storiti enako za Lookup_array. Funkcija MATCH išče samo eno matriko za vsak naveden izraz, zato vnesemo več nizov, ki jih ponovno uporabimo za ampersand in jih združimo skupaj.
Te korake je treba vnesti po vejici, ki ste jo vnesli v prejšnji korak na Row_num linija v INDEKS funkcijo.
- Kliknite na Row_num črta za vejico, da se vstavi točka na koncu trenutnega vnosa.
- Označite celice D6 do D11 na delovnem listu, da vstopite v obseg - to je prva matrika, ki jo želite iskati.
- Vnesite ampersand po celicah D6: D11 ker želimo, da funkcija poišče dve nizi.
- Označite celice E6 do E11 na delovnem listu, da vstopite v obseg - to je druga vrsta, ki jo funkcija išče.
- Za referenco celice vnesite vejico E3 za dokončanje vnosa MATCH funkcije Lookup_array prepir.
- Pusti INDEKS pogovorno okno za funkcijo, odprto za naslednji korak v vadnici.
Dodajte argument za tip MATCH
Tretji in zadnji argument funkcije MATCH je Match_type prepir; pove Excelu, kako naj ustreza Lookup_value z vrednostmi v Lookup_array - razpoložljive izbire so 1, 0 ali -1.
Ta argument je neobvezen. Če je izpuščena, funkcija uporabi privzeto vrednost 1.
- Če je Match_type = 1 ali je izpuščen: MATCH najde največjo vrednost, ki je manjša ali enaka Lookup_value. Podatke Lookup_array je treba razvrščati po naraščajočem vrstnem redu.
- Če je Match_type = 0: MATCH najde prvo vrednost, ki je točno enaka Lookup_value. Podatke Lookup_array lahko razvrstite v poljubnem vrstnem redu.
- Če je Match_type = -1: MATCH najde najmanjšo vrednost, ki je večja ali enaka vrednosti Lookup_value. Podatke Lookup_array je treba razvrščati po padajočem vrstnem redu.
Te korake je treba vnesti po vejici, ki ste jo vnesli v prejšnji korak na Row_num linija v INDEKS funkcijo.
- Po vejici na Row_num vrstico, vnesite ničlo - 0 - ker želimo, da ugnezdena funkcija vrne natančno ujemanje z izrazi, ki smo jih vnesli v celice D3 in E3.
- Vpišite oklepni pokrov - ) - dokončati MATCH funkcijo.
- Pusti Formula Builder odprto za naslednji korak v vadnici.
Dokončanje funkcije INDEX
Zdaj, ko MATCH funkcijo naredimo, se bomo premaknili v tretjo vrstico Formula Builder in vnesli zadnji argument za INDEKS funkcijo. Ta tretji in zadnji argument je Column_num argument, ki Excelu pove številko stolpca v območju D6 do F11; tu je tista informacija, ki jo želimo vrniti s funkcijo. V tem primeru dobavitelj za titanovih pripomočkov.
- Kliknite na Column_num line.
- Vnesite številko tri - 3 - v tej vrstici, saj iščemo podatke v tretjem stolpcu območja D6 do F11.
- Spet zapusti Formula Builder odprto za naslednji korak v vadnici.
Ustvarjanje formule matrike
Preden zaprete Formula Builder, moramo svojo ugnezdeno funkcijo spremeniti v matrično formulo; to omogoča, da naša funkcija v tabeli podatkov omogoča iskanje več izrazov. V tem vadnici želimo ujemati dva izraza: Pripomočki iz stolpca 1 in Titan iz stolpca 2.
Ustvarjanje matrične formule v Excelu poteka s pritiskom na CTRL, SHIFT, in ENTER tipke na tipkovnici ob istem času. Ko pritisnete, bo funkcija obkrožena z zavihanimi oporniki, kar pomeni, da je funkcija sedaj matrika.
- Zapri Formula Builder s klikom na Končano gumb.
- Nato izberite celico F4 in pritisnite Vnesite ključ za ogled formule.
- Če želite formulo pretvoriti v matriko, hkrati pritisnite CTRL + SHIFT + ENTER na tipkovnici.
- Če je storjeno pravilno a # N / A napaka bo prikazana v celici F3 - celici, kjer smo vstopili v funkcijo.
- The # N / A napaka se pojavi v celici F3 ker celice D3 in E3 so prazni. D3 in E3 sta celice, kjer smo funkciji povedali, da najdejo Lookup_values. Ko se podatki v teh dveh celicah doda, se napaka nadomesti z informacijami iz baze podatkov.
Dodajanje kriterijev iskanja
Na koncu bomo dodali iskalne izraze na naš delovni list. Kot smo omenili v prejšnjem koraku, iščemo, da se ujemajo s pogoji Pripomočki iz stolpca 1 in Titan iz stolpca 2. Če in le če naša formula ugotovi ujemanje obeh izrazov v ustreznih stolpcih v bazi podatkov, bo vrnila vrednost iz tretjega stolpca.
- Kliknite na celico D3.
- Tip Pripomočki in pritisnite Vnesite tipko na tipkovnici.
- Kliknite na celico E3.
- Tip Titan in pritisnite Vnesite tipko na tipkovnici.
- Ime dobavitelja Widgets Inc. se mora pojaviti v celici F3 - lokacijo funkcije, saj je edini dobavitelj, ki je na seznamu, ki prodaja dodatke Titanium.
- Ko kliknete celico F3, se celotna funkcija prikaže v vrstici s formulo nad delovnim listom.
{= INDEX (D6: F11, MATCH (D3 in E3, D6: D11 in E6: E11, 0), 3)}
V našem primeru je obstajal le en dobavitelj za pripomočke titana. Če je imela več kot en dobavitelj, se s temi funkcijami vrne dobavitelja, ki je prvi naveden v podatkovni zbirki.
