I-Excel Lookup Formula nge-Multiple Criteria

Ngokusebenzisa ifomula yamagama ku-Excel singakha ifomula yokulanda esebenzisa izindlela eziningi ukuthola ulwazi ku- database noma ithebula lemininingwane.

Ifomula yefomu ihilela ukwakha umsebenzi we- MATCH ngaphakathi komsebenzi we- INDEX .

Lesi sifundo sibandakanya isibonelo sesinyathelo nesinyathelo sokudala ifomula yokulanda esebenzisa izindlela eziningi ukuthola umhlinzeki we-Widget ye- titanium kwisitatimende sesampula.

Ukulandela izinyathelo ezihlokweni zokufundisa ngezansi kukuhamba ngokudala nokusebenzisa ifomu elibonwe esithombeni ngenhla.

01 ka 09

Ukufaka idatha yokufundisa

Isinyathelo sokuqala esifundweni ukufaka idatha kwi-Excel sheet sheet .

Ukuze ulandele izinyathelo ekufundiseni faka idatha eboniswe esithombeni ngenhla kula maseli alandelayo.

• Faka uchungechunge oluphezulu lwedatha kumaseli D1 kuya ku-F2
• Faka ibanga lesibili kumaseli D5 kuya ku-F11

Imigqa 3 no-4 ishiywe ingenalutho ukuze ihambisane nefomula yamakheli edalwe phakathi nalesi sifundo.

Ukufundisa akubandakanyi ukufometha okubonwe esithombeni, kepha lokhu ngeke kuthinte indlela ifomula yokufundwa isebenza ngayo.

Ulwazi olukhethwa kukho lokufometha olufana nalabo okubonwe ngenhla luyatholakala kule Nhlangano YokuFometha Okuyisisekelo ye-Excel.

02 ka 09

Ukuqala ukusebenza kwe-INDEX

Umsebenzi we-INDEX ungomunye wabambalwa ku-Excel enamafomu amaningi. Umsebenzi unesimo se- Array kanye neFomu Lokubhekisela .

Ifomu le-Array libuyisela idatha yangempela kusuka ku- database noma ithebula lemininingwane, kuyilapho ifomu le-Reference likunika ireferensi yeseli noma indawo yedatha etafuleni.

Kulesi sifundo sizosebenzisa ifomu le-Array ngoba sifuna ukwazi igama lomphakeli we-widget ye-titanium kunokubhekisela kweseli kulo mhlinzeki ku-database yethu.

Ifomu ngalinye linohlu oluhlukile lwezingxabano okumelwe lukhethwe ngaphambi kokuqala umsebenzi.

Izinyathelo Zokufundisa

1. Chofoza kuseli F3 ukwenza kube iseli elisebenzayo . Yilapho esizofaka khona isidleke.
2. Chofoza kuthebhu yefomula yemenyu ye- ribbon .
3. Khetha Ukubheka kanye neNkhombandlela kusuka kubhebhoni ukuvula uhlu lwehla lokusebenza.
4. Chofoza ku- INDEX ohlwini ukuze ulethe ibhokisi lebhokisi le- Arguments .
5. Khetha uhlu, irowu_num, inketho ye- col_num ebhokisini lengxoxo.
6. Chofoza u-KULUNGILE ukuvula ibhokisi lengxoxo ye-INDEX.

03 ka 09

Ukufaka ukungena kokusebenza kwe-INDEX

Ukuphikisana kokuqala okudingekayo yi-Array argument. Le mpikiswano icacisa ububanzi bamaseli ukuthi afune idatha efunayo.

Kulesi sifundo le mpikiswano kuzoba yisampula yethu yesampula.

Izinyathelo Zokufundisa

1. Ku- ibhokisi lebhokisi lengxoxo ye -INDEX, chofoza kulayini we- Array .
2. Gqamisa amaseli D6 kuya ku-F11 ekhasini lokusebenzela ukuze ufake ibanga ebhokisini lengxoxo.

04 ka 09

Ukuqala ukusebenza kwe-MATCH Function

Uma ukukhipha umsebenzi owodwa phakathi komunye akunakwenzeka ukuvula ibhokisi lebhokisi lengxoxo yomsebenzi wesibili noma lezintambo ukufaka izingxabano ezidingekayo.

Umsebenzi ocebile kumele uthayishwe njengenye yezingxabano zomsebenzi wokuqala.

Kulesi sifundo, umsebenzi we-MATCH odiyiwe kanye neziphakamiso zawo uzongena emgqeni wesibili webhokisi lebhokisi lengxoxo ye-INDEX - umugqa we- Row_num .

Kubalulekile ukuphawula ukuthi, uma ufaka imisebenzi ngesandla, izimpendulo zomsebenzi zihlukaniswa omunye nomunye nge-comma "," .

Ukufaka ukungena kwe-MATCH Function Lookup_value

Isinyathelo sokuqala ekungeneni umsebenzi we-MATCH ocebile ukuthi ufake ukungqubuzana kwe- Lookup_value .

I- Lookup_value izoba yindawo noma ireferensi yeseli yegama lokusesha esifuna ukulifanisa ku-database.

Ngokuvamile i- Lookup_value yamukela eyodwa kuphela yokucinga noma i-term. Ukuze sifune izindlela eziningi, kufanele sandise i- Lookup_value .

Lokhu kwenziwa ngokunciphisa noma ukujoyina izingqikithi ezimbili noma eziningi ndawonye ngokusebenzisa uphawu lwe-ampersand " & ".

Izinyathelo Zokufundisa

1. Ebhokisini lebhokisi lengxoxo ye-INDEX, chofoza ku- Row_num line.
2. Thayipha umdlalo wegama lomsebenzi olandelwe ibakaki evulekile " ( "
3. Chofoza kuseli D3 ukufaka lelo rekholi yeseli ebhokisini lengxoxo.
4. Thayipha i-ampersand " & " ngemuva kokubhekisela kweseli D3 ukuze wengeze ireferensi yeselula yesibili.
5. Chofoza ku-cell E3 ukungena kulolu chungechunge lwamaseli wesibili ebhokisini lengxoxo.
6. Thayipha i-comma "," ngemuva kokubhekisela kwe-cell E3 ukuqedela ukungena kwempikiswano yomsebenzi we-MATCH Lookup_value .
7. Shiya ibhokisi lebhokisi lengxoxo ye-INDEX evulekile isinyathelo esilandelayo ekufundiseni.

Esikhathini sokugcina sokufundisa i-Lookup_values ​​izofakwa kuma-cell D3 no-E3 wephepha lokusebenzela.

05 ka 09

Ukungeza i-Lookup_array yomsebenzi we-MATCH

Lesi sinyathelo sihlanganisa ukungeza ukuphikisana kwe- Lookup_array komsebenzi we-MATCH owakhiwe .

I- Lookup_array ibanga lamaseli ukuthi umsebenzi we-MATCH uzosesha ukuze uthole ukungqubuzana kwe- Lookup_value kufakwe kwisinyathelo sangaphambilini sesifundo .

Njengoba sithole amasimu amabili okusesha ku-argument ye- Lookup_array kufanele senze okufanayo ku- Lookup_array . Umsebenzi we-MATCH usesha kuphela uhlu olulodwa kwegama ngalinye elichazwe.

Ukufaka izigaba eziningi siphinde sisebenzise i-ampersand " & " ukukhipha izingxenye ndawonye.

Izinyathelo Zokufundisa

Lezi zinyathelo kufanele zifakwe ngemuva kokuba i-comma ingene esiteji sangaphambilini ku- Row_num line ebhokisini lengxoxo ye- INDEX.

1. Chofoza kumgqa we- Row_numuva ngemuva komhlahlandlela ukubeka iphuzu lokufaka ekupheleni kokungena kwamanje.
2. Qinisekisa amaseli D6 kuya ku-D11 kushidi lokufaka ukufaka ibanga. Lena yinto yokuqala yokusebenza umsebenzi wokucwaninga.
3. Thayipha i-ampersand " & " ngemuva kokubhekiselwe kweseli D6: D11 ngoba sifuna umsebenzi ukucinga izigaba ezimbili.
4. Gqamisa amaseli E6 kuya ku-E11 kushidi lokufaka ukufaka ibanga. Lona uhlobo lwesibili lo msebenzi ukucinga.
5. Thayipha i-comma "," ngemuva kokubhekisela kweseli e- E3 ukuqedela ukungena kwempikiswano yomsebenzi we-MATCH Lookup_array .
6. Shiya ibhokisi lebhokisi lengxoxo ye-INDEX evulekile isinyathelo esilandelayo ekufundiseni.

06 ka 09

Ukwengeza uhlobo lomdlalo nokuqedela ukusebenza kwe-MATCH

Iphikisana lesithathu nelokugcina lomsebenzi we-MATCH yi- argument Match_type.

Le mpikiswano iyatshela i-Excel ukuthi ifana kanjani ne-Lookup_value ngamanani ku-Lookup_array. Izinketho ziyi: 1, 0, noma -1.

Le mpikiswano iyakhethwa. Uma ingavumelekile umsebenzi usebenzise inani elizenzakalelayo le-1.

• uma i-Match_type = 1 noma ishiyiwe: MATCH ithola inani elikhulu kunazo zonke elingaphansi noma elingana ne-Lookup_value. Idatha ye-Lookup_array kufanele ihlungwe ngokunyuka kwe-oda.
• uma umdlalo_type = 0: MATCH uthola inani lokuqala elilingana ncamashi ne-Lookup_value. Idatha ye-Lookup_array ingahle ihlelwe kunoma iyiphi i-oda.
• uma i-Match_type = -1: MATCH ithola inani elincane kunazo zonke noma lilingana ne-Lookup_value. Idatha ye-Lookup_array kufanele ihlungwe ngokuhlehlisa.

Izinyathelo Zokufundisa

Lezi zinyathelo kufanele zifakwe ngemuva kokuba i-comma ingene esiteji sangaphambilini ku- Row_num line ebhokisini lengxoxo ye- INDEX.

1. Ukulandela i-comma ku- Row_num line, thayipha i-zero " 0 " ngoba sifuna umsebenzi ocebile ukubuyisela ukufana okufanayo nemigomo esifaka kumaseli D3 no-E3.
2. Thayipha ibhakha lokuvala " ) " ukuqedela umsebenzi we-MATCH.
3. Shiya ibhokisi lebhokisi lengxoxo ye-INDEX evulekile isinyathelo esilandelayo ekufundiseni.

07 ka 09

Buyela emsebenzini we-INDEX

Manje ukuthi umsebenzi we-MATCH usuqedile sizohambisa emgqeni wesithathu webhokisi lengxoxo evulekile bese ufaka ingxabano yokugcina yomsebenzi we-INDEX.

Le ngxabano yesithathu futhi yokugcina yikhompikiswano ye - Column_yodwa eyatshela i-Excel inombolo yekholomu ku- range D6 kuya ku-F11 lapho izothola ulwazi esifuna ukubuyiselwa ngomsebenzi. Kulesi simo, umphakeli we- widget ye- titanium .

Izinyathelo Zokufundisa

1. Chofoza ku- Column_numugqa ebhokisini lengxoxo.
2. Faka inombolo yesithathu " 3 " (akukho izingcaphuno) kulo mkhakha kusukela sibheka idatha kukholomu yesithathu ebangeni D6 kuya ku-F11.
3. Ungacindezele Kulungile noma uvale ibhokisi lengxoxo ye-INDEX. Kumele ihlale ivulekile isinyathelo esilandelayo ekufundiseni - ukudala ifomula yamalungu .

08 ka 09

Ukudala ifomu ye-Array

Ngaphambi kokuvala ibhokisi lengxoxo sidinga ukuguqula umsebenzi wethu osikiselweyo kwifomula yamalungu .

Ifomula yamalungu yilokho okuyivumela ukuthi ifune amagama amaningi etafuleni lwedatha. Kulesi sifundo sibheka ukufanisa amagama amabili: Umjaho ovela kukholomu 1 no-titanium kusuka kukholomu 2.

Ukudala ifomula yamagama ku-Excel kwenziwa ngokucindezela i- CTRL , SHIFT , ne- ENTER okhiye kwikhibhodi ngesikhathi esifanayo.

Umphumela wokucindezela lawa maki ndawonye ukuzungeza lo msebenzi ngama-curly braces: {} okubonisa ukuthi manje ifomula yamalungu.

Izinyathelo Zokufundisa

1. Njengoba ibhokisi lengxoxo eligcwalisiwe livulekile isinyathelo sangaphambilini salesi sifundo, cindezela bese ubamba izinkinobho ze- CTRL ne- SHIFT ekhibhodi bese ucindezela bese ukhulula ukhiye we- ENTER .
2. Uma kwenziwe kahle, ibhokisi lengxoxo izovala futhi iphutha le- # N / A lizovela esitokisini F3 - iseli lapho singene khona emsebenzini.
3. Iphutha le- # N / A livela kuseli F3 ngoba amangqamuzana D3 no-E3 angenalutho. I-D3 ne-E3 yileli maseli lapho sitshele khona umsebenzi ukuthola ama-Lookup_values ​​esiteji sesi-5 sesifundo. Uma idatha ingeziwe kulawa maseli amabili, iphutha lizoshintshwa ngolwazi oluvela ku- database .

09 ka 09

Ukungeza i-Criteria Yokusesha

Isinyathelo sokugcina esifundweni ukufaka amagama okusesha ekhasini lethu lomsebenzi.

Njengoba kushiwo kwisinyathelo sangaphambilini, sibheka ukufanisa amagama Amagajethi avela kukholomu 1 no- Titanium kusuka kukholomu 2.

Uma, futhi kuphela, ifomula yethu ithola ukufanelana kwemibandela yomabili kumakholomu afanele ku-database, ingabe izobuyisela inani kusukela kukholomu yesithathu.

Izinyathelo Zokufundisa

1. Chofoza kuseli D3.
2. Thayipha Umjaho bese ucindezela ukhiye we- Enter ekhibhodi.
3. Chofoza kuseli E3.
4. Thayipha i- Titanium bese ucindezela ukhiye we- Enter ekhibhodi.
5. Igama lomthengisi Umjaho we-Inc. Kufanele avele esitokisini F3 - indawo yomsebenzi ngoba yilabo kuphela umphakeli owehlisiwe othengisa i-Titanium Widget.
6. Uma uchofoza ku-cell F3 umsebenzi ophelele
   {= INDEX (D6: F11, MATCH (D3 & E3, D6: D11 & E6: E11, 0), 3)}
   ibonakala kubha yefomula ngaphezulu kwephepha lokusebenzela .

Qaphela: Esikhathini sethu kwakukhona umhlinzeki oyedwa kuphela wamawijethi we-titanium. Uma ngabe kunomhlinzeki ongaphezu kweyodwa, umphakeli odweliswe kuqala ku-database ubuyiselwa umsebenzi.