Mida teada
- Esm alt looge funktsioon INDEX, seejärel käivitage pesastatud funktsioon MATCH, sisestades argumendi Lookup_value.
- Järgmisena lisage argument Lookup_array, millele järgneb argument Match_type, seejärel määrake veeruvahemik.
- Seejärel muutke pesastatud funktsioon massiivivalemiks, vajutades Ctrl+ Shift+ Enter. Lõpuks lisage töölehele otsingusõnad.
See artikkel selgitab, kuidas luua otsinguvalemit, mis kasutab Excelis mitut kriteeriumi, et massiivivalemi abil andmebaasist või andmetabelist teavet leida. Massiivivalem hõlmab funktsiooni MATCH pesastamist funktsiooni INDEX sees. Teave hõlmab rakendusi Excel for Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 ja Excel for Mac.
Jälgige õpetust
Selle õpetuse juhiste järgimiseks sisestage näidisandmed järgmistesse lahtritesse, nagu on näidatud alloleval pildil. 3. ja 4. read jäetakse tühjaks, et mahutada selle õpetuse käigus loodud massiivivalemit. (Pange tähele, et see õpetus ei sisalda pildil nähtavat vormingut.)
- Sisestage ülemine andmevahemik lahtritesse D1 kuni F2.
- Sisestage teine vahemik lahtritesse D5 kuni F11.
Looge Excelis INDEX-funktsioon
Funktsioon INDEX on üks väheseid Exceli funktsioone, millel on mitu vormi. Funktsioonil on massiivivorm ja viitevorm. Massiivivorm tagastab andmed andmebaasist või andmetabelist. Viitevorm annab lahtri viite või andmete asukoha tabelis.
Selles õpetuses kasutatakse titaanvidinate tarnija nime leidmiseks massiivivormi, mitte selle tarnija lahtriviidet andmebaasis.
Järgige neid samme funktsiooni INDEX loomiseks:
- Valige lahter F3, et muuta see aktiivseks lahtriks. Sellesse lahtrisse sisestatakse pesastatud funktsioon.
-
Avage Valemid.
Image - Funktsioonide ripploendi avamiseks valige Otsing ja viide.
- Valige INDEX, et avada dialoogiboks Argumentide valimine.
- Valige massiiv, rea_number, veeru_arv.
- Valige OK, et avada dialoogiboks Funktsiooni argumendid. Programmis Excel for Mac avaneb valemi koostaja.
- Paigutage kursor tekstikasti Array.
-
Tõstke esile lahtrid D6 kuni F11, et sisestada vahemik dialoogiboksi.
Jäta funktsioonide argumendid dialoogiboks avatuks. Valem pole valmis. Täidate allolevate juhiste valemi.
Image
Käivitage pesastatud MATCH-funktsioon
Ühe funktsiooni pesastamisel teise sisse ei ole võimalik avada teise ehk pesastatud funktsiooni valemikoostajat vajalike argumentide sisestamiseks. Pesastatud funktsioon tuleb sisestada esimese funktsiooni ühe argumendina.
Funktsioonide käsitsi sisestamisel eraldatakse funktsiooni argumendid üksteisest komaga.
Pesastatud funktsiooni MATCH sisestamise esimene samm on argumendi Lookup_value sisestamine. Otsingu_väärtus on andmebaasis vastendatava otsingutermini asukoht või lahtriviide.
Otsingu_väärtus aktsepteerib ainult ühte otsingukriteeriumi või -terminit. Mitme kriteeriumi otsimiseks laiendage väärtust Lookup_value, ühendades või ühendades kaks või enam lahtriviidet, kasutades ampersandi sümbolit (&).
- Viige dialoogiboksis Funktsiooniargumendid kursor tekstikasti Row_num.
- Sisestage MATCH(.
- Valige lahter D3, et sisestada selle lahtri viide dialoogiboksi.
- Sisestage & (ampersand) pärast lahtriviidet D3, et lisada teine lahtriviide.
-
Teise lahtri viite sisestamiseks valige lahter E3.
-
Sisestage , (koma) pärast lahtriviidet E3, et lõpetada funktsiooni MATCH argumendi Lookup_value sisestamine.
Image Õpetuse viimases etapis sisestatakse otsingu_väärtused töölehe lahtritesse D3 ja E3.
Täitke pesastatud MATCH-funktsioon
See samm hõlmab argumendi Lookup_array lisamist pesastatud funktsioonile MATCH. Otsingumassiiv on lahtrite vahemik, mida funktsioon MATCH otsib, et leida õpetuse eelmises etapis lisatud argumendi Otsingu_väärtus.
Kuna argumendis Otsingu_massiiv tuvastati kaks otsinguvälja, tuleb sama teha ka Otsingu massiivi puhul. Funktsioon MATCH otsib iga määratud termini jaoks ainult ühte massiivi. Mitme massiivi sisestamiseks kasutage massiivide ühendamiseks ampersandi.
- Asetage kursor tekstikasti Row_num andmete lõppu. Kursor ilmub praeguse kirje lõppu koma järele.
-
Tõstke vahemiku sisestamiseks esile töölehel lahtrid D6 kuni D11. See vahemik on esimene massiiv, mida funktsioon otsib.
- Sisestage & (ampersand) pärast lahtriviiteid D6:D11. See sümbol paneb funktsiooni otsima kahest massiivist.
- Tõstke vahemiku sisestamiseks esile töölehel lahtrid E6 kuni E11. See vahemik on teine massiiv, mida funktsioon otsib.
-
Sisestage , (koma) pärast lahtriviidet E3, et lõpetada funktsiooni MATCH argumendi Lookup_array sisestamine.
Image - Jäta dialoogiboks õpetuse järgmise sammu jaoks avatuks.
Lisage MATCH-tüübi argument
Funktsiooni MATCH kolmas ja viimane argument on Match_type argument. See argument annab Excelile teada, kuidas otsingu_väärtus vastendada Otsingu_massiivi väärtustega. Saadaolevad valikud on 1, 0 või -1.
See argument on valikuline. Kui see jäetakse välja, kasutab funktsioon vaikeväärtust 1.
- Kui Match_type=1 või jäetakse välja, leiab MATCH suurima väärtuse, mis on Otsingu_väärtusest väiksem või sellega võrdne. Otsingu_massiivi andmed tuleb sortida kasvavas järjekorras.
- Kui Match_type=0, leiab MATCH esimese väärtuse, mis on võrdne otsingu_väärtusega. Otsingu_massiivi andmeid saab sortida mis tahes järjekorras.
- Kui Match_type=-1, leiab MATCH väikseima väärtuse, mis on Otsingu_väärtusest suurem või sellega võrdne. Otsingu_massiivi andmed tuleb sortida kahanevas järjekorras.
Sisestage järgmised sammud pärast eelmises etapis sisestatud koma funktsiooni INDEX reale Rea_number:
- Sisestage 0 (null) tekstikasti Row_num pärast koma. See arv paneb pesastatud funktsiooni tagastama täpsed vasted lahtritesse D3 ja E3 sisestatud terminitele.
-
Sisestage ) (sulguv ümar sulg), et lõpetada MATCH-funktsioon.
Image - Jäta dialoogiboks õpetuse järgmise sammu jaoks avatuks.
Lõpetage INDEX-funktsioon
Funktsioon MATCH on tehtud. On aeg liikuda dialoogiboksi tekstikasti Column_num ja sisestada funktsiooni INDEX viimane argument. See argument ütleb Excelile, et veeru number on vahemikus D6 kuni F11. See vahemik on koht, kus ta leiab funktsiooni tagastatud teabe. Sel juhul titaanvidinate tarnija.
- Asetage kursor tekstikasti Column_num.
-
Sisestage 3 (number kolm). See number käsib valemil otsida andmeid vahemiku D6 kuni F11 kolmandast veerust.
Image - Jäta dialoogiboks õpetuse järgmise sammu jaoks avatuks.
Massiivivalemi loomine
Enne dialoogiboksi sulgemist muutke pesastatud funktsioon massiivivalemiks. See massiiv võimaldab funktsioonil otsida andmetabelist mitut terminit. Selles õpetuses on vastendatud kaks terminit: vidinad veerust 1 ja Titanium veerust 2.
Excelis massiivivalemi loomiseks vajutage CTRL, SHIFT ja ENTERklahvi korraga. Kui funktsioon on vajutatud, on see ümbritsetud lokkis sulgudega, mis näitab, et funktsioon on nüüd massiiv.
- Valige dialoogiboksi sulgemiseks OK. Excel for Maci jaoks valige Valmis.
- Valemi vaatamiseks valige lahter F3, seejärel asetage kursor valemiriba valemi lõppu.
- Valemi teisendamiseks massiiviks vajutage CTRL+ SHIFT+ ENTER.
- A N/A viga ilmub lahtrisse F3. See on lahter, kuhu funktsioon sisestati.
-
Lahtris F3 ilmub viga N/A, kuna lahtrid D3 ja E3 on tühjad. D3 ja E3 on lahtrid, kust funktsioon otsib otsinguväärtuse leidmist. Pärast andmete lisamist nendesse kahte lahtrisse asendatakse viga andmebaasi teabega.
Image
Lisa otsingukriteeriumid
Viimane samm on otsingusõnade lisamine töölehel. See samm vastab terminitele Vidinad veerus 1 ja Titanium veerus 2.
Kui valem leiab andmebaasi vastavates veergudes mõlema termini jaoks vaste, tagastab see väärtuse kolmandast veerust.
- Valige lahter D3.
- Sisestage Vidinad.
- Valige lahter E3.
- Sisestage Titanium ja vajutage Sisestus.
- Tarnija nimi Widgets Inc. kuvatakse lahtris F3. See on ainus loetletud tarnija, kes müüb titaanvidinaid.
-
Valige lahter F3. Funktsioon kuvatakse töölehe kohal valemiribal.
{=INDEKS(D6:F11, MATCH(D3&E3, D6:D11&E6:E11, 0), 3)}
Selles näites on titaanvidinate jaoks ainult üks tarnija. Kui tarnijaid oleks olnud rohkem kui üks, tagastab funktsioon andmebaasis esimesena loetletud tarnija.
Image
