Kaj morate vedeti
- Najprej ustvarite funkcijo INDEX, nato zaženite ugnezdeno funkcijo MATCH z vnosom argumenta Lookup_value.
- Nato dodajte argument Lookup_array, ki mu sledi argument Match_type, nato določite obseg stolpcev.
- Nato spremenite ugnezdeno funkcijo v matrično formulo s pritiskom Ctrl+ Shift+ Enter. Na koncu dodajte iskalne izraze na delovni list.
Ta članek pojasnjuje, kako ustvariti formulo za iskanje, ki uporablja več kriterijev v Excelu za iskanje informacij v zbirki podatkov ali tabeli s podatki z uporabo matrične formule. Matrična formula vključuje ugnezdenje funkcije MATCH znotraj funkcije INDEX. Informacije zajemajo Excel za Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 in Excel za Mac.
Sledite vadnici
Če želite slediti korakom v tej vadnici, vnesite vzorčne podatke v naslednje celice, kot je prikazano na spodnji sliki. Vrstici 3 in 4 ostaneta prazni, da se prilagodita matrični formuli, ustvarjeni med to vadnico. (Upoštevajte, da ta vadnica ne vključuje oblikovanja, ki ga vidite na sliki.)
- Vnesite zgornji obseg podatkov v celice D1 do F2.
- Vnesite drugi obseg v celice D5 do F11.
Ustvarite funkcijo INDEX v Excelu
Funkcija INDEX je ena redkih funkcij v Excelu, ki ima več oblik. Funkcija ima matrični obrazec in referenčni obrazec. Array Form vrne podatke iz zbirke podatkov ali tabele podatkov. Referenčni obrazec daje sklic na celico ali lokacijo podatkov v tabeli.
V tej vadnici se obrazec Array Form uporablja za iskanje imena dobavitelja za pripomočke iz titana, namesto sklicevanja celice na tega dobavitelja v bazi podatkov.
Sledite tem korakom, da ustvarite funkcijo INDEX:
- Izberite celico F3, da postane aktivna celica. V to celico bo vnesena ugnezdena funkcija.
-
Pojdi na Formule.
Image - Izberite Iskanje in referenca, da odprete spustni seznam funkcij.
- Izberite INDEX, da odprete pogovorno okno Izberite argumente.
- Izberite niz, številka_vrstice, številka_stolpca.
- Izberite OK, da odprete pogovorno okno Function Arguments. V Excelu za Mac se odpre graditelj formul.
- Postavite kazalec v besedilno polje Array.
-
Označite celice od D6 do F11 na delovnem listu, da vnesete obseg v pogovorno okno.
Pustite pogovorno okno Argumenti funkcije odprto. Formula ni končana. Formulo boste dokončali v spodnjih navodilih.
Image
Zaženite ugnezdeno funkcijo MATCH
Pri ugnezdenju ene funkcije v drugo ni mogoče odpreti graditelja formule druge ali ugnezdene funkcije za vnos potrebnih argumentov. Ugnezdeno funkcijo je treba vnesti kot enega od argumentov prve funkcije.
Pri ročnem vnosu funkcij so argumenti funkcije med seboj ločeni z vejico.
Prvi korak za vnos ugnezdene funkcije MATCH je vnos argumenta Lookup_value. Iskalna_vrednost je lokacija ali sklic na celico za iskalni izraz, ki se ujema v bazi podatkov.
Iskalna_vrednost sprejme samo eno iskalno merilo ali izraz. Če želite iskati po več kriterijih, razširite Lookup_value tako, da združite ali združite dva ali več referenc celic z uporabo znaka &.
- V pogovornem oknu Argumenti funkcije postavite kazalec v besedilno polje Row_num.
- Vnesite MATCH(.
-
Izberite celico D3, da vnesete referenco celice v pogovorno okno.
- Vnesite & (ampersand) za sklicem na celico D3, da dodate drugi sklic na celico.
- Izberite celico E3, da vnesete referenco druge celice.
-
Vnesite , (vejico) za sklicem na celico E3, da dokončate vnos argumenta Lookup_value funkcije MATCH.
Image V zadnjem koraku vadnice bodo iskalne_vrednosti vnesene v celici D3 in E3 delovnega lista.
Dokončajte ugnezdeno funkcijo MATCH
Ta korak zajema dodajanje argumenta Lookup_array za ugnezdeno funkcijo MATCH. Lookup_array je obseg celic, ki jih funkcija MATCH išče, da bi našla argument Lookup_value, dodan v prejšnjem koraku vadnice.
Ker sta bili v argumentu Lookup_array identificirani dve iskalni polji, je treba enako narediti za Lookup_array. Funkcija MATCH išče le eno matriko za vsak podani izraz. Če želite vnesti več nizov, uporabite ampersand za združevanje nizov.
- Postavite kazalec na konec podatkov v besedilnem polju Row_num. Kazalec se pojavi za vejico na koncu trenutnega vnosa.
- Označite celice od D6 do D11 na delovnem listu, da vnesete obseg. Ta obseg je prvi niz, ki ga funkcija išče.
- Vnesite & (znak &) za sklici na celico D6:D11. Ta simbol povzroči, da funkcija išče dve matriki.
- Označite celice od E6 do E11 na delovnem listu, da vnesete obseg. Ta obseg je drugi niz, ki ga funkcija išče.
-
Vnesite , (vejico) za sklicem na celico E3, da dokončate vnos argumenta Lookup_array funkcije MATCH.
Image - Pustite pogovorno okno odprto za naslednji korak v vadnici.
Dodajte argument tipa MATCH
Tretji in zadnji argument funkcije MATCH je argument Match_type. Ta argument pove Excelu, kako naj ujema Lookup_value z vrednostmi v Lookup_array. Na voljo so 1, 0 ali -1.
Ta argument ni obvezen. Če je izpuščen, funkcija uporablja privzeto vrednost 1.
- Če je Match_type=1 ali je izpuščen, MATCH poišče največjo vrednost, ki je manjša ali enaka Lookup_value. Podatki Lookup_array morajo biti razvrščeni v naraščajočem vrstnem redu.
- Če je Match_type=0, MATCH najde prvo vrednost, ki je enaka Lookup_value. Podatke Lookup_array je mogoče razvrstiti v poljubnem vrstnem redu.
- Če je Match_type=-1, MATCH najde najmanjšo vrednost, ki je večja ali enaka Lookup_value. Podatki Lookup_array morajo biti razvrščeni v padajočem vrstnem redu.
Vnesite te korake za vejico, vneseno v prejšnjem koraku, v vrstico Row_num v funkciji INDEX:
- V besedilno polje Row_num za vejico vnesite 0 (ničlo). Ta številka povzroči, da ugnezdena funkcija vrne natančna ujemanja z izrazi, vnesenimi v celicah D3 in E3.
-
Vnesite ) (oklepaj) za dokončanje funkcije MATCH.
Image - Pustite pogovorno okno odprto za naslednji korak v vadnici.
Dokončajte funkcijo INDEX
Funkcija MATCH je končana. Čas je, da se premaknete v besedilno polje Column_num v pogovornem oknu in vnesete zadnji argument za funkcijo INDEX. Ta argument pove Excelu, da je številka stolpca v območju od D6 do F11. V tem obsegu najde informacije, ki jih vrne funkcija. V tem primeru dobavitelj pripomočkov iz titana.
- Postavite kazalec v besedilno polje Column_num.
-
Vnesite 3 (številka tri). Ta številka pove formuli, naj išče podatke v tretjem stolpcu obsega od D6 do F11.
Image - Pustite pogovorno okno odprto za naslednji korak v vadnici.
Ustvari matrično formulo
Preden zaprete pogovorno okno, spremenite ugnezdeno funkcijo v matrično formulo. Ta matrika omogoča funkciji iskanje več izrazov v podatkovni tabeli. V tej vadnici se ujemata dva izraza: Widgets iz stolpca 1 in Titanium iz stolpca 2.
Če želite ustvariti matrično formulo v Excelu, pritisnite CTRL, SHIFT in ENTERtipk hkrati. Ko jo pritisnete, je funkcija obdana z zavitimi oklepaji, kar pomeni, da je funkcija zdaj niz.
- Izberite OK, da zaprete pogovorno okno. V Excelu za Mac izberite Done.
- Izberite celico F3 za ogled formule, nato postavite kazalec na konec formule v vrstici s formulami.
- Če želite formulo pretvoriti v matriko, pritisnite CTRL+ SHIFT+ ENTER.
- V celici F3 se prikaže napaka A N/A. To je celica, v katero je bila vnesena funkcija.
-
Napaka N/A se prikaže v celici F3, ker sta celici D3 in E3 prazni. D3 in E3 sta celici, v katerih funkcija išče Lookup_value. Ko so podatki dodani v ti dve celici, se napaka nadomesti z informacijami iz zbirke podatkov.
Image
Dodaj iskalna merila
Zadnji korak je dodajanje iskalnih izrazov na delovni list. Ta korak se ujema z izrazoma Widgets iz stolpca 1 in Titanium iz stolpca 2.
Če formula najde ujemanje za oba izraza v ustreznih stolpcih v bazi podatkov, vrne vrednost iz tretjega stolpca.
- Izberi celico D3.
- Vnesite Widgets.
- Izberi celico E3.
- Vnesite Titanium in pritisnite Enter.
- Ime dobavitelja, Widgets Inc., se pojavi v celici F3. To je edini navedeni dobavitelj, ki prodaja pripomočke Titanium.
-
Izberi celico F3. Funkcija se pojavi v vrstici s formulami nad delovnim listom.
{=INDEX(D6:F11, MATCH(D3&E3, D6:D11&E6:E11, 0), 3)}
V tem primeru je samo en dobavitelj pripomočkov iz titana. Če je bilo dobaviteljev več, funkcija vrne dobavitelja, ki je prvi v bazi podatkov.
Image
