Exceli otsingu valem mitmete kriteeriumidega

Kasutades massiivi valemit Excelis, saame luua otsingu valemi, mis kasutab andmebaasi või andmete tabelilt teavet mitmete kriteeriumide leidmiseks.

Massiivi valem hõlmab MATCH- funktsiooni pesa INDEX- funktsioonis.

See õpetus sisaldab samm-sammult näidet, kuidas luua otsingutulemust, mis kasutab mitut kriteeriumit, et leida titaani vidinaid tarnija näidisandmebaasis.

Allpool toodud juhendamisteemade sammud järgivad teid ülaltoodud pildi abil loodud valemi loomise ja kasutamise kaudu.

01 of 09

Treeningu andmete sisestamine

Exceli otsimisfunktsioon mitme kriteeriumiga. © Ted French

Juhendaja esimene samm on andmete sisestamine Exceli töölehele .

Tutoriali etappide järgimiseks sisestage ülaltoodud pildi andmed järgmistesse lahtritesse .

Rida 3 ja 4 jäetakse tühjaks, et mahutada selle juhendaja käigus loodud massiivi valemit .

Tutorial ei sisalda pildil nähtavat vormingut, kuid see ei mõjuta otsinguvaliku toimimist.

Selles Excelis põhitähtede vormindamise juhendis on teavet vorminguvõimaluste kohta, mis on sarnased ülaltoodud näidetega.

02 09

INDEKSI FUNKTSIOONI alustamine

Exceli INDEXi funktsiooni kasutamine otsinguvormil. © Ted French

INDEX-funktsioon on üks vähestest Excelist koosnevatest vormidest. Funktsioonil on array vorm ja viitenumber .

Array vorm tagastab tegelikud andmed andmebaasist või andmete tabelist, samal ajal kui võrdlusvorm annab teile tabeli andmete raku viite või asukoha.

Selles juhendis kasutame array-vormi, kuna me tahame titaanivideod tarnija nime, mitte selle tarnija raame viidet meie andmebaasis.

Igal vormil on erinevad argumentide loendid, mida tuleb enne funktsiooni alustamist valida.

Juhendaja sammud

  1. Vajuta raku F3, et muuta see aktiivseks . See on koht, kuhu me sisestame pesastatud funktsiooni.
  2. Klõpsake lindi menüü vahekaardil Vormid .
  3. Funktsiooni rippmenüü avamiseks valige rippmenüüst käsku Otsi ja viide .
  4. Klõpsake loendis INDEX , et avada dialoogiboks " Vali argumendid " .
  5. Vali dialoogiboksis array, row_num, col_num valik.
  6. INDEX-funktsiooni dialoogiboksi avamiseks klõpsake OK.

03 alates 09

INDEXi funktsioonide array argumendi sisestamine

Klõpsa pildil täissuuruses kuvamiseks. © Ted French

Esimene nõutav argument on array argument. See argument täpsustab soovitud andmete otsimiseks vajalike lahtrite ulatust .

Selle juhendi puhul on see argument meie proovide andmebaasiks .

Juhendaja sammud

  1. Klõpsake dialoogiboksis INDEX funktsioonis Array liinil.
  2. Dialoogiboksi vahemikku sisenemiseks tõstke esile töölaual esiletõstetud lahtrid D6 kuni F11.

04 09

Nested MATCH-funktsiooni käivitamine

Klõpsa pildil täissuuruses kuvamiseks. © Ted French

Kui ühe funktsiooni sisestamine teise sees ei ole võimalik teise või pesaga funktsiooni dialoogi avada vajalike argumentide sisestamiseks.

Lenditud funktsioon tuleb sisestada üheks esimese funktsiooni argumendina.

Selles juhendis sisestatakse pesastatud MATCH-funktsioon ja selle argumendid INDEX-i funktsiooni dialoogiboksi teise rida - Row_num- rida.

On oluline märkida, et funktsioonide käsitsi sisestamisel eraldatakse funktsiooni argumendid üksteisest komaga "," .

MATCH-funktsiooni lookup_value argumendi sisestamine

Pesastatud MATCH-funktsiooni sisestamise esimene samm on sisestada otsinguparameetri argument.

Lookup_value on andmebaasi jaoks soovitud otsinguterminite asukoht või raamistik.

Tavaliselt võtab otsingupäring vastu ainult üks otsingukriteerium või -termin . Mitme kriteeriumi otsimiseks peame laiendama otsingu_väärtust .

Seda tehakse, ühendades või ühendades kaks või enam rakuvälja viiteid koos, kasutades täisnurkse sümbolit " & ".

Juhendaja sammud

  1. Dialoogiboksis INDEX funktsiooni klõpsake rida Row_num .
  2. Sisestage funktsiooninime vaste, millele järgneb avatud ümarlaud " ( "
  3. Klõpsake raku D3, et sisestada selle lahtri viide dialoogiaknasse.
  4. Tüüpiks ampersandi " & " pärast lahtri viide D3 , et lisada teine ​​lahtri viide.
  5. Klõpsake lahtris E3, et sisestada see teise lahtri viide dialoogiaknasse.
  6. Sisestage koma "," pärast raku viidet E3 MATCH-i funktsiooni Lookup_value argumendi sisestamiseks .
  7. Jätke õppematerjali järgmises etapis lahti INDEX-i funktsiooni dialoogiboks.

Juhendaja viimases etapis sisestatakse töölehe rakkude D3 ja E3 väärtused Lookup_values.

05 09

MATCH-funktsiooni "Lookup_array" lisamine

Klõpsa pildil täissuuruses kuvamiseks. © Ted French

See samm hõlmab ligikaudse MATCH-funktsiooni lookup_array argumendi lisamist.

Lookup_array on lahtrite hulk, mida MATCH-funktsioon otsib, et leida otsingupakkumise_väärtuse argument, mis on lisatud õpetuse eelmises etapis.

Kuna oleme otsinguparameetri argumendis tuvastanud kaks otsinguvälja, peame tegema seda ka otsingupärandi jaoks . MATCH-funktsioon otsib ainult iga määratud tähtaja jaoks ühte massiivi.

Mitu massiivi sisestamiseks kasutame uuesti massiivide ühendamiseks koos " & ".

Juhendaja sammud

Need sammud tuleb sisestada pärast komaga, mis on sisestatud eelmise sammuna ROW_num- reale INDEX-i funktsiooni dialoogiboksis .

  1. Klõpsake Row_num- reale pärast koma, et asetada sisestamispunkt käesoleva kirje lõpus.
  2. Esiletõstetud töölaual esiletõstetud lahtrid D6 kuni D11. See on esimene massiiv, mille ülesandeks on otsida.
  3. Sisestage ampersand " & " pärast raku viiteid D6: D11, sest me tahame, et funktsioon otsiks kahte massiivi.
  4. Vahemikus sisenemiseks tõstke töölehel esile töörühmad E6 kuni E11. See on teine ​​massiiv, mille ülesandeks on otsida.
  5. Sisestage koma "," pärast lahtri E3 täita MATCH-i funktsiooni Lookup_array argumendi sisestust .
  6. Jätke õppematerjali järgmises etapis lahti INDEX-i funktsiooni dialoogiboks.

06 alates 09

Matši tüübi lisamine ja MATCH-funktsiooni täitmine

Klõpsa pildil täissuuruses kuvamiseks. © Ted French

MATCH-funktsiooni kolmas ja viimane argument on Match_type argument.

See argument räägib Excelile, kuidas Matchup_value sobitada väärtustega Lookup_array'is. Valikud on: 1, 0 või -1.

See argument on vabatahtlik. Kui see on välja jäetud, kasutab funktsioon vaikeväärtust 1.

Juhendaja sammud

Need sammud tuleb sisestada pärast komaga, mis on sisestatud eelmise sammuna ROW_num- reale INDEX-i funktsiooni dialoogiboksis .

  1. Row_num- reale järgides sisestage null " 0 ", sest me tahame, et lohistatud funktsioon tagastaks täpseid vasteid lahtritesse D3 ja E3 sisestatud terminitele.
  2. MATCH-funktsiooni täitmiseks sisestage sulgemiskraam " ) ".
  3. Jätke õppematerjali järgmises etapis lahti INDEX-i funktsiooni dialoogiboks.

07 09

Tagasi INDEXi funktsioonile

Klõpsa pildil täissuuruses kuvamiseks. © Ted French

Nüüd, kui MATCH funktsioon on tehtud, läheme avatud dialoogi kolmandasse rida ja sisestame INDEXi funktsiooni viimase argumendi .

See kolmas ja viimane argument on Column_num argument, mis ütleb Excelile veeru numbri vahemikus D6 kuni F11, kus see leiab teavet, mida me tahame funktsiooni tagastada. Sel juhul tarnija titaan vidinad .

Juhendaja sammud

  1. Klõpsake dialoogiboksis rea Column_num .
  2. Selles reas sisestage selle numbri kolm numbrit " 3 " (ilma jutumärkideta), kuna otsime andmeid vahemikus D6 kuni F11 kolmandasse veergu.
  3. Ärge klõpsake OK või sulgege INDEXi funktsioonide dialoogiboks. See peab jääma avatuks juhendaja järgmises etapis - massiivi valemi loomine .

08, 09

Array valemi loomine

Exceli otsingu array valem. © Ted French

Enne dialoogiboksi sulgemist peame oma lohistatud funktsiooni muutma massiivi valemiks .

Massiivi valem on see, mis võimaldab tal mitme loendi andmete otsimisel. Selles õpetuses otsime sobivaid kahte terminit: veerud 1 ja veerus 2 titaanist pärit vidinad.

Exceli massiivi valemi loomine toimub klaviatuuril CTRL , SHIFT ja ENTER klahvide vajutamisega samal ajal.

Nende klahvide vajutamise mõjud on funktsiooni ümbritsemine lokkidega: {}, mis näitab, et see on nüüd massiivi valem.

Juhendaja sammud

  1. Kui lõpetatud dialoogiboks on endiselt käesoleva juhendaja eelmises etapis, vajutage ja hoidke all klaviatuuri CTRL ja SHIFT klahve, seejärel vajutage ja vabastage ENTER klahv.
  2. Kui see on korralikult tehtud, sulgeb dialoogiboks ja lahtris F3 - lahtrisse, kuhu me funktsiooni sisestasime, ilmub # n / V tõrge.
  3. Lahtris F3 ilmub # N / A tõrge, kuna lahtrid D3 ja E3 on tühjad. D3 ja E3 on rakud, kus me rääkisime juhendaja 5. sammuga otsingu väärtuste leidmiseks. Kui andmed lisatakse nendele kahele lahtrisse, asendatakse viga andmebaasis oleva teabega.

09 09

Otsingukriteeriumide lisamine

Andmete otsimine Exceli otsingu array valemiga. © Ted French

Juhendaja viimane samm on lisada otsingusõnad meie töölehele.

Nagu eelmises etapis mainitud, otsime me veergude 1 ja 2. veeru titaani mõisted Widgets .

Kui ja ainult siis, kui meie valem leiab vastavuse mõlema terminiga vastavas veerus andmebaasis, kas see tagastab väärtuse kolmandast veerust.

Juhendaja sammud

  1. Klõpsake raku D3 juures.
  2. Tippige Widgets ja vajutage klaviatuuril Enter klahvi.
  3. Klõpsake elemendil E3.
  4. Sisestage titaan ja vajutage klaviatuuril Enter klahvi.
  5. Tarnija nimi Widgets Inc. peaks ilmuma funktsiooni asukohta F3, kuna see on ainus tarnija, kes müüb Titanium Widgets.
  6. Kui klõpsate elemendil F3 täielik funktsioon
    {= INDEX (D6: F11, MATCH (D3 & E3, D6: D11 & E6: E11, 0), 3)}
    ilmub töölehe kohal valemriba .

Märkus: meie näites oli titaani vidinaid ainult üks tarnija. Kui seal oli rohkem kui üks tarnija, tagastatakse andmebaasist esimesena nimetatud andmebaasi tarnija.