Excel Lookup Formula me kritere të shumëfishta

Duke përdorur një formulë të grupit në Excel, ne mund të krijojmë një formulë të kërkimeve që përdor kritere të shumëfishta për të gjetur informacion në një bazë të dhënash ose tabelë të të dhënave.

Formula e grupit përfshin folejen e funksionit MATCH brenda funksionit INDEX .

Ky tutorial përfshin një shembull hap pas hapi të krijimit të një formulari të kërkimeve që përdor kritere të shumëfishta për të gjetur një furnizues të Widgets titan në një bazë të të dhënave të mostrës.

Duke ndjekur hapat në temat e tutorial më poshtë ju ecën përmes krijimit dhe përdorimit të formulës së parë në imazhin e mësipërm.

01 nga 09

Hyrja e të Dhënave të Tutorial

Hapi i parë në tutorial është të futni të dhënat në një fletë pune në Excel.

Në mënyrë që të ndiqni hapat në tutorial futni të dhënat e paraqitura në imazhin e mësipërm në qelizat e mëposhtme.

• Shkruani gamën më të lartë të të dhënave në qelizat D1 deri në F2
• Futni vargun e dytë në qelizat D5 deri në F11

Rreshtat 3 dhe 4 janë lënë bosh për të akomoduar formulën e grupeve të krijuara gjatë këtij udhezimi.

Tutorial nuk përfshin formatimin e parë në imazh, por kjo nuk do të ndikojë në funksionimin e formulës së lookup.

Informacion mbi opsionet e formatimit të ngjashme me ato të mësipërme janë në dispozicion në këtë Udhëzues për Formattimin Bazë të Excel-it.

02 nga 09

Fillimi i funksionit INDEX

Funksioni INDEX është një nga të paktët në Excel që ka forma të shumëfishta. Funksioni ka një Formular Array dhe një Formë Referimi .

Formulari Array kthen të dhënat aktuale nga një bazë të dhënash ose tabelë të të dhënave, ndërsa Formati i Referencës ju jep referencën e qelizës ose vendndodhjen e të dhënave në tabelë.

Në këtë tutorial do të përdorim Formularin e Arrayit, pasi ne duam të dimë emrin e një furnizuesi për veglat e titanit sesa referenca e qelizës tek ky furnizues në bazën tonë të të dhënave.

Çdo formë ka një listë të argumenteve të ndryshme që duhet të zgjidhen përpara fillimit të funksionit.

Hapat e Tutorial

1. Klikoni në qelizën F3 për ta bërë qelinë aktive . Kjo është ajo ku ne do të hyjmë në funksionin e mbivendosur.
2. Klikoni në tab Formulat e menysë së fjongo .
3. Zgjidhni Kërkimin dhe Referencën nga shiriti për të hapur drop down listën e funksionit.
4. Klikoni në INDEX në listë për të shfaqur kutinë e dialogut Select Arguments .
5. Zgjidhni grupin, row_num, col_num opsion në kutinë e dialogut.
6. Kliko OK për të hapur kutinë e dialogut të funksionit INDEX.

03 nga 09

Hyrja e Argumentit Array Funksioni INDEX

Argumenti i parë i kërkuar është argumenti Array. Ky argument specifikon gamën e qelizave për t'u kërkuar për të dhënat e dëshiruara.

Për këtë tutorial ky argument do të jetë baza e të dhënave tona të mostrës.

Hapat e Tutorial

1. Në kutinë e dialogut të funksionit INDEX, klikoni në vijën Array .
2. Dallojini qelizat D6 në F11 në fletën e punës për të hyrë në diapazonin në kutinë e dialogut.

04 nga 09

Fillimi i Funksionit të Ndarjes së Përbashkët

Kur fole një funksion brenda një tjetër, nuk është e mundur të hapësh kutinë e dialogut të funksionit të dytë ose të mbivendosur për të futur argumentet e nevojshme.

Funksioni i mbivendosur duhet të shtypet si një nga argumentet e funksionit të parë.

Në këtë tutorial, funksioni MATCH i mbivendosur dhe argumentet e tij do të futen në vijën e dytë të kutisë së dialogut të funksionit INDEX - rreshti Row_num .

Është e rëndësishme të theksohet se kur hyjnë në funksione me dorë, argumentet e funksionit ndahen nga njëri-tjetri me presje "," .

Hyrja Argumenti i Lookup_value Funksioni MATCH

Hapi i parë për të hyrë në funksionin MATCH të mbivendosur është të hyjë në argumentin e Lookup_value .

Lookup_value do të jetë vendndodhja ose referenca e qelizës për termin e kërkimit që duam të përputhemi në bazën e të dhënave.

Normalisht Lookup_value pranon vetëm një kriter ose afat të kërkimit. Në mënyrë që të kërkojmë kritere të shumëfishta, ne duhet të zgjerojmë Lookup_value .

Kjo bëhet duke bashkuar ose bashkuar dy ose më shumë referenca qelizore së bashku duke përdorur simbolin " dhe ".

Hapat e Tutorial

1. Në kutinë e dialogut të funksionit INDEX, klikoni në rreshtin Row_num .
2. Shkruani ndeshje të emrit të funksionit të ndjekur nga një radhë e hapur e raundit " ( "
3. Klikoni në qelizën D3 për të hyrë në atë referencë të qelizës në kutinë e dialogut.
4. Lloji një ampersand dhe " & " pas referencës qelizore D3 në mënyrë që të shtoni një referencë të qelizës së dytë.
5. Klikoni në qelizën E3 për të hyrë në këtë referencë të qelizës së dytë në kutinë e dialogut.
6. Lloji një presje "," pas referencës së qelizës E3 për të përfunduar hyrjen e argumentit të Lookup_value të funksionit MATCH.
7. Lëreni kutinë e dialogut të funksionit INDEX hapur për hapin e ardhshëm në tutorial.

Në hapin e fundit të tutorialit Lookup_values ​​do të futet në qelizat D3 dhe E3 të fletës së punës.

05 nga 09

Shtimi i Lookup_array për Funksionin MATCH

Ky hap mbulon shtimin e argumentit Lookup_array për funksionin MATCH të mbivendosur.

Lookup_array është varg i qelizave që funksioni MATCH do të kërkojë për të gjetur argumentin Lookup_value shtuar në hapin e mëparshëm të tutorialit.

Pasi që ne kemi identifikuar dy fusha të kërkimit në argumentin Lookup_array ne duhet të bëjmë të njëjtën gjë për Lookup_array . Funksioni MATCH kërkon vetëm një grup për secilin term të specifikuar.

Për të futur variacione të shumta, përdorim përsëri " dhe " për të bashkuar vargjet së bashku.

Hapat e Tutorial

Këto hapa duhet të futen pas presjes së futur në hapin e mëparshëm në linjën Row_num në kutinë e dialogut të funksionit INDEX.

1. Klikoni në vijën Row_num pas presjes për të vendosur pikën e futjes në fund të hyrjes aktuale.
2. Shënoni qelizat D6 në D11 në fletën e punës për të hyrë në gamën. Ky është grupi i parë që funksioni është për të kërkuar.
3. Shkruaj një ampersand dhe " & " pas referencave të qelizave D6: D11 sepse duam që funksioni të kërkojë dy vargje.
4. Theksoni qelizat E6 në E11 në fletën e punës për të hyrë në rang. Ky është grupi i dytë që funksioni është për të kërkuar.
5. Shkruaj një presje "," pas referencës së qelizës E3 për të përfunduar hyrjen e argumentit Lookup_array të funksionit MATCH .
6. Lëreni kutinë e dialogut të funksionit INDEX hapur për hapin e ardhshëm në tutorial.

06 nga 09

Shtimi i llojit të ndeshjes dhe Plotësimi i funksionit MATCH

Argumenti i tretë dhe i fundit i funksionit MATCH është argumenti Match_type.

Ky argument tregon Excel se si të përputhen me vlerën Lookup_val me vlerat në Lookup_array. Zgjedhjet janë: 1, 0, ose -1.

Ky argument është fakultativ. Nëse mungon, funksioni përdor vlerën e parazgjedhur të 1.

• nëse Match_type = 1 ose është lënë jashtë: MATCH gjen vlerën më të madhe që është më e vogël ose e barabartë me Lookup_value. Të dhënat e Lookup_array duhet të renditen sipas rendit rritës.
• nëse match_type = 0: MATCH gjen vlerën e parë që është saktësisht e barabartë me Lookup_value. Të dhënat e Lookup_array mund të renditen në çdo mënyrë.
• nëse Match_type = -1: MATCH gjen vlerën më të vogël që është më e madhe ose e barabartë me Lookup_value. Të dhënat e Lookup_array duhet të renditen në rend zbritës.

Hapat e Tutorial

Këto hapa duhet të futen pas presjes së futur në hapin e mëparshëm në linjën Row_num në kutinë e dialogut të funksionit INDEX.

1. Pas presjes në rreshtin Row_num , shkruani një zero " 0 " pasi ne duam që funksioni i mbivendosur të kthejë ndeshjet e sakta me termat që hyjmë në qelizat D3 dhe E3.
2. Shtypni një radhë të mbylljes raundi " ) " për të përfunduar funksionin MATCH.
3. Lëreni kutinë e dialogut të funksionit INDEX hapur për hapin e ardhshëm në tutorial.

07 nga 09

Kthehu tek Funksioni INDEX

Tani që funksioni MATCH është bërë ne do të shkojmë në vijën e tretë të kutisë së dialogut të hapur dhe do të fusim argumentin e fundit për funksionin INDEX.

Ky argument i tretë dhe i fundit është argumenti Column_num i cili tregon Excel numrin e kolonës në rangun D6 në F11 ku do të gjejë informacionin që duam ta kthejë nga funksioni. Në këtë rast, një furnizues për widgets titan .

Hapat e Tutorial

1. Klikoni në vijën Column_num në kutinë e dialogut.
2. Futni numrin tre " 3 " (pa citate) në këtë rresht pasi që ne jemi duke kërkuar për të dhënat në kolonën e tretë të vargut D6 në F11.
3. Mos klikoni OK ose mbyllni kutinë e dialogut të funksionit INDEX. Duhet të mbetet e hapur për hapin tjetër në tutorial - duke krijuar formulën e grupeve .

08 nga 09

Krijimi i Formula Array

Para mbylljes së kutisë së dialogut ne duhet ta kthejmë funksionin tonë të mbivendosur në një formulë të grupit .

Një formulë e grupeve është ajo që e lejon atë të kërkojë terma të shumëfishta në tabelën e të dhënave. Në këtë tutorial ne po shohim që të përputhen dy terma: Widgets nga kolona 1 dhe titan nga kolona 2.

Krijimi i një formule të grupeve në Excel bëhet duke shtypur butonat CTRL , SHIFT dhe ENTER në tastierë në të njëjtën kohë.

Efekti i shtypjes së këtyre çelësave së bashku është që të rrethojnë funksionin me mbështjellje të kaçurrela: {} që tregon se tani është një formulë e grupit.

Hapat e Tutorial

1. Me kutinë e dialogut të përfunduar të hapur ende nga hapi i mëparshëm i këtij udhezimi, shtypni dhe mbani shtypur butonat CTRL dhe SHIFT në tastierë dhe më pas shtypni dhe lëshoni butonin ENTER .
2. Nëse bëhet si duhet, kutia e dialogut do të mbyllet dhe një gabim # N / A do të shfaqet në qelizën F3 - qelizën ku kemi hyrë në funksion.
3. Gabimi # N / A shfaqet në qelizën F3 sepse qelizat D3 dhe E3 janë bosh. D3 dhe E3 janë qelizat ku ne i thamë funksionit për të gjetur Lookup_values ​​në hapin 5 të tutorialit. Pasi të shtohen të dhënat në këto dy qeliza, gabimi do të zëvendësohet nga informacioni nga baza e të dhënave .

09 nga 09

Shtimi i kritereve të kërkimit

Hapi i fundit në tutorial është shtimi i termave të kërkimit në fletën tonë të punës.

Siç u përmend në hapin e mëparshëm, ne jemi duke kërkuar që të përputhen me termat Widgets nga kolona 1 dhe Titanium nga kolona 2.

Nëse dhe vetëm nëse formulari ynë gjen një ndeshje për të dy termat në kolonat e përshtatshme në bazën e të dhënave, a do të kthejë vlerën nga kolona e tretë.

Hapat e Tutorial

1. Klikoni në qelizën D3.
2. Tipi Widgets dhe shtypni butonin Enter në tastierë.
3. Kliko në qelizën E3.
4. Type Titanium dhe shtypni butonin Enter në tastierë.
5. Emri i furnizuesit Widgets Inc. duhet të paraqitet në qelizën F3 - vendndodhja e funksionit pasi që është furnizuesi i vetëm i listuar që shet Widgets Titanium.
6. Kur klikoni në qelizën F3 funksionin e plotë
   {= INDEKSI (D6: F11, MATCH (D3 & E3, D6: D11 & E6: E11, 0), 3)}
   shfaqet në shiritin e formulës mbi fletën e punës .

Shënim: Në shembullin tonë ka pasur vetëm një furnizues për veglat 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.