"Excel" paieškos formulė su keliais kriterijais

Naudodami masyvo formulę "Excel" mes galime sukurti paieškos formą, kuri naudoja keletą kriterijų, kad surastų informaciją duomenų bazėje arba duomenų lentelėje.

Masyvo formulė apima funkcijos MATCH priskyrimą indekso funkcijoje.

Šioje pamokoje pateikiamas žingsnis po žingsnio pavyzdžio sukurti paieškos formą, kuri naudoja keletą kriterijų, norint rasti titano "Widgets" tiekėją pavyzdinėje duomenų bazėje.

Toliau pateiktose pamokų temose pateikiami veiksmai padės jums kurti ir naudoti formules, pateiktas aukščiau esančiame paveikslėlyje.

01 iš 09

Įvadas į mokymo duomenis

Pirmas žingsnis pamoka yra įvesti duomenis į "Excel" darbalapį .

Norėdami sekti instrukcijų veiksmus, įveskite duomenis, rodomus paveikslėlyje aukščiau, į šias langelius .

• Įveskite aukščiausią duomenų diapazoną į ląsteles nuo D1 iki F2
• Įveskite antrąjį diapazoną į ląsteles nuo D5 iki F11

3 ir 4 eilutės yra tuščios, kad būtų galima pritaikyti šioje instrukcijoje sukurtą masyvo formulę .

Mokomoji medžiaga neapima formato, matomo paveikslėlyje, tačiau tai neturės įtakos, kaip veikia paieškos formulė.

Informacija apie formatavimo parinktis, panašias į anksčiau pateiktas, pateikiama šiame "Excel" formato vedlyje.

02 iš 09

Pradedant indekso funkciją

INDEKSO funkcija yra viena iš nedaugelio "Excel", kuri turi keletą formų. Funkcija turi masyvo formą ir nuorodą formą .

Masyvo forma grąžina faktinius duomenis iš duomenų bazės ar duomenų lentelės, o informacinėje formoje pateikiama lentelės duomenų bazės ar lentelės vietos informacija.

Šioje pamokoje mes naudosime "Array Form", nes norime sužinoti pavadinimą tiekėjo, kuris naudoja titano elementus, o ne ląstelių nuorodą į šį tiekėją mūsų duomenų bazėje.

Kiekvienoje formoje yra skirtingas argumentų sąrašas, kurį reikia pasirinkti prieš pradedant funkciją.

Tutorial žingsniai

1. Spustelėkite langelį F3, kad jis taptų aktyvia ląstele . Čia mes pateiksime įdėtą funkciją.
2. Spustelėkite skirtuką Formulės juostelės meniu.
3. Pasirinkite iešką ir nuorodą iš juostos, kad atidarytumėte išskleidžiamąjį sąrašą.
4. Spustelėkite INDEX sąraše, kad pamatytumėte dialogo langą " Pasirinkti argumentus" .
5. Dialogo lange pasirinkite masyvą, row_num, col_num parinktį.
6. Spustelėkite OK, kad atidarytumėte INDEX funkcijos dialogo langą.

03 iš 09

Įvesdami indekso funkcijos masyvo argumentą

Pirmas reikalingas argumentas yra masyvo argumentas. Šis argumentas nurodo ieškomų ląstelių diapazoną norimų duomenų.

Šiame vadove šis argumentas bus mūsų duomenų bazės pavyzdys.

Tutorial žingsniai

1. Dialogo lange " INDEX" funkcija spustelėkite eilutės masyvą .
2. Išskleiskite langelius D6-F11 darbalapyje , kad dialogo lange įveskite diapazoną.

04 iš 09

Pradėti įklijuotą MATCH funkciją

Kai vieną funkciją priskiriate kitoje, neįmanoma atidaryti antrojo arba įdėtos funkcijos dialogo lango, kad galėtumėte įvesti reikiamus argumentus .

Įdėta funkcija turi būti įvesta kaip vienas iš pirmosios funkcijos argumentų.

Šioje pamokoje įdedama MATCH funkcija ir jos argumentai bus įrašyti į antrą INDEX funkcijos dialogo lango eilutę - Row_num eilutę.

Svarbu pažymėti, kad, įvedant funkcijas rankiniu būdu, funkcijos argumentai yra atskirti vienas nuo kito kableliu "," .

Įeikite į MATCH funkcijos "Lookup_value" argumentą

Pirmasis įvesties į įklijuotą MATCH funkciją įvedimas yra " Lookup_value" argumentas.

" Lookup_value" bus vietovės arba ląstelės nuoroda paieškos terminui, kurį mes norime sutapti duomenų bazėje.

Paprastai " Lookup_value" priima tik vieną paieškos kriterijų arba terminą. Norint ieškoti kelių kriterijų, turime išplėsti " Lookup_value" .

Tai atliekama sujungiant arba jungiant dvi ar daugiau ląstelių nuorodų kartu naudojant simbolį " & ".

Tutorial žingsniai

1. Dialogo lange INDEX, spustelėkite eilutę Row_num .
2. Įveskite funkcijos pavadinimo atitiktį, po kurios bus rodomas atviras laikiklis " ( "
3. Spustelėkite langelį D3, kad įvedėte tą langelio nuorodą į dialogo langą.
4. Po antraštės " D3 " įrašykite ampersandą " & ", kad pridėtumėte antrą langelį.
5. Spustelėkite langelį E3, kad įvedėte šią antraeilę nuorodą į dialogo langą.
6. Įveskite kablelį "," po langelio nuorodos E3, kad užbaigtumėte MATCH funkcijos " Lookup_value" argumento įrašą.
7. Palikite INDEX funkcijos dialogo langą, atidarytą kitam žingsnio žingsniui.

Paskutiniame žingsnio žingsnyje "Lookup_values" bus įrašyti į lakštus D3 ir E3 darbalaukyje.

05 iš 09

"MATCH" funkcijos "Lookup_array" pridėjimas

Šis žingsnis apima pridedant " nesurenkamos MATCH" funkcijos " Lookup_array" argumentą .

" Lookup_array" yra ląstelių asortimentas, kurio MATCH funkcija ieškos, norėdami rasti " Lookup_value" argumentą, pridėtą ankstesniame žingsnio etape.

Kadangi mes nustatėme du paieškos laukelius " Lookup_array" argumentuose, mes turime padaryti tą patį ir " Lookup_array" . MATCH funkcija ieško tik vieno masyvo kiekvienam nurodytam terminui.

Norėdami įvesti keletą matricų, mes dar kartą naudosime " & ", kad sujungti masyvus.

Tutorial žingsniai

Šie veiksmai turi būti įvedami po kableliais, įrašytais ankstesniame žingsnyje ROW_num eilutėje INDEX funkcijos dialogo lange .

1. Paspauskite eilutės Row_num po kablelės, kad įterpimo taškas būtų įrašytas dabartinio įrašo pabaigoje.
2. Išskleiskite langelius nuo D6 iki D11 darbalapyje, kad patektumėte į diapazoną. Tai yra pirmasis masyvas, kurio funkcija yra ieškoti.
3. Įveskite "ampersand" ir " & " po ląstelių nuorodų D6: D11, nes mes norime, kad funkcija būtų ieškoma dviejų masyvų.
4. Išskleiskite langelius nuo E6 iki E11 darbalapyje, kad patektumėte į diapazoną. Tai yra antrasis masyvas, kurio funkcija yra ieškoti.
5. Įveskite kablelį "," po langelio nuorodos E3, kad užbaigtumėte MATCH funkcijos " Lookup_array" argumento įrašą .
6. Palikite INDEX funkcijos dialogo langą, atidarytą kitam žingsnio žingsniui.

06 iš 09

Pridedamas atitikties tipas ir "MATCH" funkcijos užbaigimas

Trečias ir paskutinis MATCH funkcijos argumentas yra Match_type argumentas.

Šis argumentas sako "Excel", kaip "Lookup_value" suderinti su reikšmėmis "Lookup_array". Pasirinkimai yra: 1, 0 arba -1.

Šis argumentas yra neprivalomas. Jei jis praleistas, funkcija naudoja numatytąją 1 reikšmę.

• jei Match_type = 1 arba praleistas: MATCH nustato didžiausią vertę, kuri yra mažesnė arba lygi Lookup_value. "Lookup_array" duomenys turi būti rūšiuojami didėjančia tvarka.
• jei match_type = 0: MATCH nustato pirmąją vertę, kuri yra lygi "Lookup_value" tikslui. "Lookup_array" duomenys gali būti rūšiuojami bet kokia tvarka.
• jei Match_type = -1: MATCH nustato mažiausią vertę, didesnę arba lygią Lookup_value. "Lookup_array" duomenys turi būti rūšiuojami mažėjančia tvarka.

Tutorial žingsniai

Šie veiksmai turi būti įvedami po kableliais, įrašytais ankstesniame žingsnyje ROW_num eilutėje INDEX funkcijos dialogo lange .

1. Po kableliais eilutėje Row_num įveskite nulį " 0 ", nes mes norime, kad įdėta funkcija grąžintų tikslius atitikimus įvestiems terminams D3 ir E3 ląstelėse.
2. Įveskite uždaromojo turinio skliaustą " ) ", kad užbaigtumėte MATCH funkciją.
3. Palikite INDEX funkcijos dialogo langą, atidarytą kitam žingsnio žingsniui.

07 iš 09

Atgal į INDEX funkciją

Dabar, kai MATCH funkcija yra atlikta, mes pereisime į trečią eilutę atidaryto dialogo lango ir įveskite paskutinį INDEX funkcijos argumentą .

Šis trečias ir paskutinis argumentas yra Column_num argumentas, kuris Excel nurodo stulpelio numerį diapazone nuo D6 iki F11, kuriame jis suranda informaciją, kurią mes norime grąžinti pagal funkciją. Tokiu atveju yra titano elementų tiekėjas.

Tutorial žingsniai

1. Dialogo lange spustelėkite eilutę " Column_num" .
2. Įveskite trijų skaičių " 3 " (be kabučių) šioje eilutėje, nes mes ieškome duomenų trečioje skiltyje nuo diapazono D6 iki F11.
3. Nepaspauskite OK arba uždarykite INDEX funkcijos dialogo langą. Tai turi likti atidaryta kitam žingsnio žingsniui - sukurti masyvo formulę .

08 iš 09

Kūrimo masyvo formulė

Prieš uždarant dialogo langą, mes turime pasukti įdėtą funkciją į masyvo formulę .

Masyvo formulė leidžia tai ieškoti kelių terminų duomenų lentelėje. Šioje pamokoje mes siekiame suderinti du terminus: 1 stulpelio elementus ir titaną iš stulpelio 2.

"Excel" masyvo formulės sukūrimas tuo pačiu metu paspaudžiamas CTRL , SHIFT ir ENTER klavišus.

Šių klavišų paspaudimo poveikis yra supaprastinti funkciją su garbančiais petnešomis: {} nurodant, kad dabar yra masyvo formulė.

Tutorial žingsniai

1. Kai baigtas dialogo langas vis dar atidarytas iš ankstesnio šio vadovo žingsnio, paspauskite ir laikykite klaviatūros CTRL ir SHIFT klavišus, tada paspauskite ir atleiskite ENTER mygtuką.
2. Jei atliksite teisingai, dialogo langas bus uždarytas, ir langelyje F3 - langelyje, kuriame mes įvedėme funkciją, pasirodys # N / A klaida.
3. Lauke F3 pasirodo # N / A klaida, nes ląstelės D3 ir E3 yra tuščios. D3 ir E3 yra ląstelės, kuriose mes pasakėme funkciją rasti "Lookup_values" žinių 5 žingsnyje. Kai duomenys bus įtraukti į šias dvi ląsteles, klaida bus pakeista informacija iš duomenų bazės .

09 iš 09

Paieškos kriterijų pridėjimas

Paskutinis žingsnis pamoka yra įtraukti paieškos terminus į mūsų darbalapį.

Kaip minėta ankstesniame žingsnyje, mes ieškome atitikti terminus " Valdikliai" iš 1 stulpelio ir " Titanas" iš 2 stulpelio.

Jei ir tik tada, kai mūsų formulė randa abiejų terminų atitiktį atitinkamose duomenų bazės stulpeliuose, ji grąžins vertę iš trečio stulpelio.

Tutorial žingsniai

1. Spustelėkite langelį D3.
2. Įveskite widgets ir paspauskite klaviatūros klavišą Enter .
3. Spustelėkite langelį E3.
4. Įveskite Titanium ir paspauskite Enter klavišą.
5. Tiekėjo pavadinimas " Widgets Inc." turėtų būti rodomas lange F3 - funkcijos vieta, nes ji yra vienintelis tiekėjas, kuris parduoda Titanium Widgets.
6. Kai paspausite langelį F3, atlikite visą funkciją
   {= INDEKSAS (D6: F11, MATCH (D3 ir E3, D6: D11 ir E6: E11, 0), 3)}
   pasirodo formulės juostoje virš darbalapio .

Pastaba: mūsų pavyzdyje buvo tik vienas titano elementų tiekėjas. Jei buvo daugiau nei vienas tiekėjas, pirmiausia duomenų bazėje įrašytas tiekėjas grąžina šią funkciją.