Kaip sukurti „Excel“paieškos formulę su keliais kriterijais

2024 Autorius: Abigail Brown | [email protected]. Paskutinį kartą keistas: 2024-01-07 19:05

Ką žinoti

• Pirmiausia sukurkite funkciją INDEX, tada paleiskite įdėtą funkciją MATCH, įvesdami argumentą Lookup_value.
• Toliau pridėkite argumentą Lookup_array, po kurio eina argumentas Match_type, tada nurodykite stulpelių diapazoną.
• Tada įdėtąją funkciją paverskite masyvo formule paspausdami Ctrl+ Shift+ Enter. Galiausiai į darbalapį pridėkite paieškos terminus.

Šiame straipsnyje paaiškinama, kaip sukurti paieškos formulę, kuri naudoja kelis kriterijus programoje „Excel“, kad būtų galima rasti informaciją duomenų bazėje arba duomenų lentelėje naudojant masyvo formulę. Masyvo formulė apima funkcijos MATCH įdėjimą į funkciją INDEX. Informacija apima „Excel“, skirtą „Microsoft 365“, „Excel 2019“, „Excel 2016“, „Excel 2013“, „Excel 2010“ir „Excel“, skirtą „Mac“.

Sekite mokymo programą

Jei norite atlikti šioje mokymo programoje nurodytus veiksmus, įveskite pavyzdinius duomenis į šiuos langelius, kaip parodyta paveikslėlyje toliau. 3 ir 4 eilutės paliekamos tuščios, kad tilptų šios pamokos metu sukurta masyvo formulė. (Atminkite, kad šioje mokymo programoje nėra paveikslėlyje matomo formatavimo.)

• Įveskite didžiausią duomenų diapazoną į langelius D1–F2.
• Įveskite antrąjį diapazoną į langelius nuo D5 iki F11.

Sukurkite INDEKSO funkciją programoje Excel

Funkcija INDEX yra viena iš nedaugelio „Excel“funkcijų, turinčių kelias formas. Funkcija turi masyvo formą ir nuorodos formą. Masyvo forma pateikia duomenis iš duomenų bazės arba duomenų lentelės. Nuorodos formoje pateikiama langelio nuoroda arba duomenų vieta lentelėje.

Šioje mokymo programoje masyvo forma naudojama titano valdiklių tiekėjo pavadinimui rasti, o ne langelio nuorodai į šį tiekėją duomenų bazėje.

Jei norite sukurti funkciją INDEX, atlikite šiuos veiksmus:

1. Pasirinkite langelį F3, kad jis būtų aktyvus. Šiame langelyje bus įvesta įdėta funkcija.

2. Eiti į Formulės.

   

3. Pasirinkite Lookup & Reference, kad atidarytumėte funkcijų išskleidžiamąjį sąrašą.
4. Pasirinkite INDEX, kad atidarytumėte dialogo langą Select Arguments.
5. Pasirinkite masyvas, eilutės_nm., stulpelio_nr..
6. Pasirinkite OK, kad atidarytumėte dialogo langą Funkcijos argumentai. Programoje „Excel“, skirta „Mac“, atidaroma formulių kūrimo priemonė.
7. Įdėkite žymeklį į Array teksto laukelį.

8. Paryškinkite langelius nuo D6 iki F11 darbalapyje, kad įvestumėte diapazoną į dialogo langą.

   Palikite atidarytą dialogo langą Funkcijos argumentai. Formulė nebaigta. Užpildysite formulę toliau pateiktose instrukcijose.

   

Paleiskite įdėtą MATCH funkciją

Įdėjus vieną funkciją kitoje, neįmanoma atidaryti antrosios arba įdėtos funkcijos formulių kūrimo priemonės, kad būtų įvesti reikiami argumentai. Įdėta funkcija turi būti įvesta kaip vienas iš pirmosios funkcijos argumentų.

Įvedant funkcijas rankiniu būdu, funkcijos argumentai vienas nuo kito atskiriami kableliu.

Pirmasis veiksmas norint įvesti įdėtąją MATCH funkciją yra įvesti argumentą Lookup_value. Lookup_value yra paieškos termino, kurį reikia atitikti duomenų bazėje, vieta arba langelio nuoroda.

Lookup_reikšmė priima tik vieną paieškos kriterijų arba terminą. Norėdami ieškoti kelių kriterijų, išplėskite Lookup_value sujungdami arba sujungdami dvi ar daugiau langelių nuorodas naudodami ampersando simbolį (&).

1. Dialogo lange Funkcijos argumentai įveskite žymeklį į Row_num teksto laukelį.
2. Įveskite MATCH(.
3. Pasirinkite langelį D3, kad įvestumėte langelio nuorodą į dialogo langą.
4. Įveskite & (ampersandą) po langelio nuorodos D3, kad pridėtumėte antrą langelio nuorodą.
5. Pasirinkite langelį E3, kad įvestumėte antrojo langelio nuorodą.

6. Įveskite , (kablelis) po langelio nuorodos E3, kad užbaigtumėte funkcijos MATCH argumento Lookup_value įvedimą.

   

   Paskutiniame mokymo programos žingsnyje Lookup_values bus įvestos į darbalapio langelius D3 ir E3.

Užbaikite įdėtą MATCH funkciją

Šis veiksmas apima įdėtos funkcijos MATCH argumento Lookup_array pridėjimą. „Lookup_array“yra langelių diapazonas, kurio funkcija MATCH ieško, kad surastų argumentą „Lookup_value“, pridėtą ankstesniame mokymo programos etape.

Kadangi argumente Lookup_array buvo nustatyti du paieškos laukai, tą patį reikia padaryti ir su Lookup_masyvo. Funkcija MATCH ieško tik vieno masyvo kiekvienam nurodytam terminui. Norėdami įvesti kelis masyvus, naudokite ampersand, kad sujungtumėte masyvus.

1. Padėkite žymeklį teksto laukelio Row_num duomenų pabaigoje. Žymeklis rodomas po kablelio dabartinio įrašo pabaigoje.
2. Paryškinkite langelius nuo D6 iki D11 darbalapyje, kad įvestumėte diapazoną. Šis diapazonas yra pirmasis masyvas, kurio ieško funkcija.
3. Įveskite & (& ) po langelių nuorodų D6:D11. Šis simbolis leidžia funkcijai ieškoti dviejuose masyvuose.
4. Paryškinkite langelius nuo E6 iki E11 darbalapyje, kad įvestumėte diapazoną. Šis diapazonas yra antrasis masyvas, kurio ieško funkcija.

5. Įveskite , (kablelis) po langelio nuorodos E3, kad užbaigtumėte funkcijos MATCH argumento Lookup_array įvedimą.

   

6. Palikite dialogo langą atidarytą kitam mokymo programos veiksmui.

Pridėkite MATCH tipo argumentą

Trečias ir paskutinis funkcijos MATCH argumentas yra Match_type argumentas. Šis argumentas nurodo Excel, kaip suderinti Lookup_value su reikšmėmis Lookup_masyvo. Galimi pasirinkimai: 1, 0 arba -1.

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

• Jei Match_type=1 arba yra praleista, MATCH suranda didžiausią reikšmę, kuri yra mažesnė arba lygi Lookup_value. „Lookup_masyvo“duomenys turi būti rūšiuojami didėjančia tvarka.
• Jei Match_type=0, MATCH suranda pirmąją reikšmę, kuri yra lygi Lookup_value. „Lookup_masyvo“duomenis galima rūšiuoti bet kokia tvarka.
• Jei Match_type=-1, MATCH suranda mažiausią reikšmę, kuri yra didesnė arba lygi Lookup_value. „Lookup_masyvo“duomenys turi būti rūšiuojami mažėjimo tvarka.

Įveskite šiuos veiksmus po kablelio, įvesto ankstesniame veiksme funkcijos INDEX eilutės_numeris eilutėje:

1. Įveskite 0 (nulis) po kablelio Row_num teksto laukelyje. Dėl šio skaičiaus įdėta funkcija grąžina tikslias D3 ir E3 langeliuose įvestų terminų atitiktis.

2. Įveskite ) (užbaigiamasis skliaustas), kad užbaigtumėte MATCH funkciją.

   

3. Palikite dialogo langą atidarytą kitam mokymo programos veiksmui.

Užbaikite INDEKSO funkciją

Funkcija MATCH atlikta. Atėjo laikas pereiti į dialogo lango teksto laukelį Column_num ir įvesti paskutinį funkcijos INDEX argumentą. Šis argumentas nurodo Excel, kad stulpelio numeris yra nuo D6 iki F11. Šiame diapazone ji randa funkcijos grąžintą informaciją. Šiuo atveju titano valdiklių tiekėjas.

1. Įdėkite žymeklį į Column_num teksto laukelį.

2. Įveskite 3 (skaičius trys). Šis skaičius nurodo formulei ieškoti duomenų trečiajame diapazono D6–F11 stulpelyje.

   

3. Palikite dialogo langą atidarytą kitam mokymo programos veiksmui.

Sukurkite masyvo formulę

Prieš uždarydami dialogo langą, įdėtą funkciją paverskite masyvo formule. Šis masyvas leidžia funkcijai ieškoti kelių terminų duomenų lentelėje. Šioje mokymo programoje atitinka du terminai: valdikliai iš 1 stulpelio ir titanas iš 2 stulpelio.

Jei norite sukurti masyvo formulę programoje Excel, paspauskite CTRL, SHIFT ir ENTERklavišų vienu metu. Paspaudus funkciją, ji yra apsupta riestiniais skliaustais, nurodant, kad funkcija dabar yra masyvas.

1. Pasirinkite OK, kad uždarytumėte dialogo langą. „Excel“, skirtoje „Mac“, pasirinkite Atlikta.
2. Pasirinkite langelį F3, kad peržiūrėtumėte formulę, tada užveskite žymeklį formulės juostoje formulės gale.
3. Norėdami konvertuoti formulę į masyvą, paspauskite CTRL+ SHIFT+ ENTER.
4. A N/A klaida pasirodo F3 langelyje. Tai langelis, kuriame buvo įvesta funkcija.

5. Klaida N/A pasirodo F3 langelyje, nes langeliai D3 ir E3 yra tušti. D3 ir E3 yra langeliai, kuriuose funkcija ieško, kad surastų Lookup_value. Pridėjus duomenis prie šių dviejų langelių, klaida pakeičiama informacija iš duomenų bazės.

   

Pridėti paieškos kriterijus

Paskutinis veiksmas yra įtraukti paieškos terminus į darbalapį. Šis veiksmas atitinka terminus Valdikliai iš 1 stulpelio ir „Titanium“iš 2 stulpelio.

Jei formulė randa abiejų terminų atitiktį atitinkamuose duomenų bazės stulpeliuose, ji grąžina reikšmę iš trečiojo stulpelio.

1. Pasirinkite langelį D3.
2. Įveskite Widgets.
3. Pasirinkite langelį E3.
4. Įveskite Titanium ir paspauskite Enter.
5. Tiekėjo pavadinimas Widgets Inc. rodomas langelyje F3. Tai vienintelis išvardytas tiekėjas, parduodantis titano valdiklius.

6. Pasirinkite langelį F3. Funkcija rodoma formulės juostoje virš darbalapio.

   {=INDEX(D6:F11, MATCH(D3&E3, D6:D11&E6:E11, 0), 3)}
   Šiame pavyzdyje yra tik vienas titano valdiklių tiekėjas. Jei būtų buvę daugiau nei vienas tiekėjas, funkcija grąžina pirmą duomenų bazėje nurodytą tiekėją.