Si të krijoni një formulë kërkimi në Excel me kritere të shumta

2024 Autor: Abigail Brown | [email protected]. E modifikuara e fundit: 2024-01-07 19:06

Çfarë duhet të dini

• Së pari, krijoni një funksion INDEX, më pas filloni funksionin e ndërlidhur MATCH duke futur argumentin Lookup_value.
• Tjetra, shtoni argumentin Lookup_array i ndjekur nga argumenti Match_type, më pas specifikoni gamën e kolonës.
• Pastaj, kthejeni funksionin e ndërthurur në një formulë grupi duke shtypur Ctrl+ Shift+ Enter. Së fundi, shtoni termat e kërkimit në fletën e punës.

Ky artikull shpjegon se si të krijoni një formulë kërkimi që përdor kritere të shumta në Excel për të gjetur informacion në një bazë të dhënash ose tabelë të dhënash duke përdorur një formulë grupi. Formula e grupit përfshin futjen e funksionit MATCH brenda funksionit INDEX. Informacioni mbulon Excel për Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 dhe Excel për Mac.

Ndiqni së bashku me tutorialin

Për të ndjekur hapat në këtë tutorial, futni të dhënat e mostrës në qelizat e mëposhtme, siç tregohet në imazhin më poshtë. Rreshtat 3 dhe 4 janë lënë bosh për të akomoduar formulën e grupit të krijuar gjatë këtij tutoriali. (Vini re se ky tutorial nuk përfshin formatimin e parë në imazh.)

• Fut gamën kryesore të të dhënave në qelizat D1 deri në F2.
• Fut gamën e dytë në qelizat D5 deri në F11.

Krijoni një funksion INDEX në Excel

Funksioni INDEX është një nga funksionet e pakta në Excel që ka forma të shumta. Funksioni ka një Formë Array dhe një Formë Reference. Forma Array kthen të dhënat nga një bazë të dhënash ose tabela e të dhënave. Formulari i referencës jep referencën e qelizës ose vendndodhjen e të dhënave në tabelë.

Në këtë tutorial, Formulari i Array përdoret për të gjetur emrin e furnizuesit për miniaplikacionet e titanit, në vend të referencës së qelizës për këtë furnizues në bazën e të dhënave.

Ndiq këto hapa për të krijuar funksionin INDEX:

1. Zgjidhni qelizën F3 për ta bërë atë qelizën aktive. Kjo qelizë është vendi ku do të futet funksioni i ndërlidhur.

2. Shko te Formulat.

   

3. Zgjidhni Lookup & Reference për të hapur listën rënëse të funksioneve.
4. Zgjidh INDEX për të hapur kutinë e dialogut Zgjidh Argumentet.
5. Zgjidh array, rresht_num, kolonë_num.
6. Zgjidhni OK për të hapur kutinë e dialogut Function Arguments. Në Excel për Mac, hapet Formula Builder.
7. Vendosni kursorin në kutinë e tekstit Array.

8. Theksoni qelizat D6 deri në F11 në fletën e punës për të futur diapazonin në kutinë e dialogut.

   Lëreni të hapur kutinë e dialogut Argumentet e Funksionit. Formula nuk ka përfunduar. Ju do të plotësoni formulën në udhëzimet e mëposhtme.

   

Fillimi i Funksionit Nested MATCH

Kur futni një funksion brenda një tjetri, nuk është e mundur të hapni ndërtuesin e formulave të funksionit të dytë ose të ndërthurur për të futur argumentet e nevojshme. Funksioni i mbivendosur duhet të futet si një nga argumentet e funksionit të parë.

Kur futni funksionet me dorë, argumentet e funksionit ndahen nga njëri-tjetri me presje.

Hapi i parë për të futur funksionin e ndërlidhur MATCH është futja e argumentit Lookup_value. Lookup_value është vendndodhja ose referenca e qelizës për termin e kërkimit që duhet të përputhet në bazën e të dhënave.

Vlera e kërkimit pranon vetëm një kriter ose term kërkimi. Për të kërkuar për kritere të shumta, zgjeroni vlerën e kërkimit duke bashkuar ose bashkuar dy ose më shumë referenca qelizash duke përdorur simbolin ampersand (&).

1. Në kutinë e dialogut Function Arguments, vendoseni kursorin në kutinë e tekstit Rew_num.

2. Fut MATCH(.
3. Zgjidh qelizën D3 për të futur atë referencë qelize në kutinë e dialogut.
4. Fut & (ampersand) pas referencës së qelizës D3 për të shtuar një referencë të dytë të qelizës.
5. Zgjidh qelizën E3 për të futur referencën e qelizës së dytë.

6. Enter , (një presje) pas referencës së qelizës E3 për të përfunduar hyrjen e argumentit Lookup_value të funksionit MATCH.

   

   Në hapin e fundit të tutorialit, vlerat e kërkimit do të futen në qelizat D3 dhe E3 të fletës së punës.

Plotëso funksionin e Ndeshjes së Nested

Ky hap përfshin shtimin e argumentit Lookup_array për funksionin e ndërlidhur MATCH. Lookup_array është diapazoni i qelizave që kërkon funksioni MATCH për të gjetur argumentin Lookup_value të shtuar në hapin e mëparshëm të tutorialit.

Për shkak se dy fusha kërkimi u identifikuan në argumentin Lookup_array, e njëjta gjë duhet bërë edhe për grupin Lookup_. Funksioni MATCH kërkon vetëm një grup për çdo term të specifikuar. Për të futur vargje të shumta, përdorni ampersand-in për të lidhur vargjet së bashku.

1. Vendosni kursorin në fund të të dhënave në kutinë e tekstit Rresht_num. Kursori shfaqet pas presjes në fund të hyrjes aktuale.
2. Theksoni qelizat D6 deri në D11 në fletën e punës për të hyrë në intervalin. Ky varg është grupi i parë që kërkon funksioni.
3. Fut & (një ampersand) pas referencave të qelizës D6:D11. Ky simbol bën që funksioni të kërkojë dy vargje.
4. Theksoni qelizat E6 deri në E11 në fletën e punës për të hyrë në intervalin. Ky varg është grupi i dytë që kërkon funksioni.

5. Fut , (një presje) pas referencës së qelizës E3 për të përfunduar hyrjen e argumentit Lookup_array të funksionit MATCH.

   

6. Lëre të hapur kutinë e dialogut për hapin tjetër në tutorial.

Shto argumentin e tipit MATCH

Argumenti i tretë dhe i fundit i funksionit MATCH është argumenti Match_type. Ky argument i tregon Excel se si të përputhet Lookup_value me vlerat në Lookup_array. Zgjedhjet e disponueshme janë 1, 0 ose -1.

Ky argument është fakultativ. Nëse hiqet, funksioni përdor vlerën e paracaktuar prej 1.

• Nëse Match_type=1 ose hiqet, MATCH gjen vlerën më të madhe që është më e vogël ose e barabartë me vlerën e kërkimit. Të dhënat Lookup_array duhet të renditen në rend rritës.
• Nëse Match_type=0, MATCH gjen vlerën e parë që është e barabartë me Lookup_value. Të dhënat Lookup_array mund të renditen sipas çdo rendi.
• Nëse Match_type=-1, MATCH gjen vlerën më të vogël që është më e madhe ose e barabartë me vlerën e kërkimit. Të dhënat Lookup_array duhet të renditen në rend zbritës.

Fut këta hapa pasi presja e futur në hapin e mëparshëm në rreshtin Row_num në funksionin INDEX:

1. Fut 0 (një zero) pas presjes në kutinë e tekstit Rresht_num. Ky numër bën që funksioni i ndërlidhur të kthejë përputhje të saktë me termat e futur në qelizat D3 dhe E3.

2. Fut ) (një kllapë mbyllëse) për të përfunduar funksionin MATCH.

   

3. Lëre të hapur kutinë e dialogut për hapin tjetër në tutorial.

Mbaro funksionin INDEX

Funksioni MATCH është kryer. Është koha për të kaluar në kutinë e tekstit Column_num të kutisë së dialogut dhe për të futur argumentin e fundit për funksionin INDEX. Ky argument i tregon Excel se numri i kolonës është në intervalin D6 deri në F11. Ky interval është vendi ku gjen informacionin e kthyer nga funksioni. Në këtë rast, një furnizues për pajisje titani.

1. Vendosni kursorin në kutinë e tekstit Column_num.

2. Fut 3 (numrin tre). Ky numër tregon formulën që të kërkojë të dhëna në kolonën e tretë të diapazonit D6 deri në F11.

   

3. Lëre të hapur kutinë e dialogut për hapin tjetër në tutorial.

Krijoni formulën e grupit

Para se të mbyllni kutinë e dialogut, kthejeni funksionin e ndërlidhur në një formulë grupi. Ky grup i lejon funksionit të kërkojë terma të shumtë në tabelën e të dhënave. Në këtë tutorial, përputhen dy terma: Widgets nga kolona 1 dhe Titanium nga kolona 2.

Për të krijuar një formulë grupi në Excel, shtypni CTRL, SHIFT dhe ENTERçelësa njëkohësisht. Pasi të shtypet, funksioni rrethohet nga mbajtëse kaçurrelë, që tregon se funksioni tani është një grup.

1. Zgjidhni OK për të mbyllur kutinë e dialogut. Në Excel për Mac, zgjidhni Done.
2. Zgjidhni qelizën F3 për të parë formulën, më pas vendoseni kursorin në fund të formulës në shiritin e formulës.
3. Për ta kthyer formulën në një grup, shtypni CTRL+ SHIFT+ ENTER.
4. Një gabim N/A shfaqet në qelizën F3. Kjo është qeliza ku është futur funksioni.

5. Gabimi N/A shfaqet në qelizën F3 sepse qelizat D3 dhe E3 janë bosh. D3 dhe E3 janë qelizat ku funksioni kërkon të gjejë vlerën e kërkimit. Pasi të dhënat shtohen në këto dy qeliza, gabimi zëvendësohet me informacion nga baza e të dhënave.

   

Shto kriteret e kërkimit

Hapi i fundit është të shtoni termat e kërkimit në fletën e punës. Ky hap përputhet me termat Widgets nga kolona 1 dhe Titanium nga kolona 2.

Nëse formula gjen një përputhje për të dy termat në kolonat e duhura në bazën e të dhënave, ajo kthen vlerën nga kolona e tretë.

1. Zgjidh qelizën D3.
2. Fut Vidgets.
3. Zgjidh qelizën E3.
4. Shkruani Titanium dhe shtypni Enter.
5. Emri i furnizuesit, Widgets Inc., shfaqet në qelizën F3. Ky është i vetmi furnizues i listuar që shet pajisje të tipit Titanium.

6. Zgjidh qelizën F3. Funksioni shfaqet në shiritin e formulës mbi fletën e punës.

   {=INDEX(D6:F11, MATCH(D3&E3, D6:D11&E6:E11, 0), 3)}
   Në këtë shembull, ka vetëm një furnizues për miniaplikacionet e titanit. Nëse ka pasur më shumë se një furnizues, furnizuesi i renditur i pari në bazën e të dhënave kthehet nga funksioni.