I-Excel Lookup Formula eneeNkcazo ezininzi

Ngokusebenzisa ifom elandelelweyo kwi-Excel singenza ifom ye-lookup esebenzisa iindlela ezininzi zokufumana ulwazi kwi- database okanye itafile yedatha.

Umgaqo-nkqubo uquka ukuhlawula umsebenzi weMATCH ngaphakathi kwe- INDEX umsebenzi.

Olu qeqesho lubandakanya isinyathelo kunye nomzekelo wesisombululo sokudala ifom ye-lookup esebenzisa iindlela ezininzi zokufumana umthengisi we- titanium Widgets kwisiseko seenkcukacha.

Ukulandela amanyathelo kwimixholo yokufundisa engezantsi kukuhamba ngokudala nokusebenzisa ifom ebonwe kumfanekiso ongentla.

01 ka 09

Ukufaka i-Tutorial Data

Isinyathelo sokuqala kwisifundo kukufaka idatha kwiphepha lokusebenzela lase Excel.

Ukuze ulandele amanyathelo ekufundiseni faka idatha eboniswe kumfanekiso ongentla apha kwiiseli ezilandelayo.

• Faka uluhlu oluphezulu lweedatha kwiiseli D1 ukuya kwiF2
• Faka udidi lwesibini kwiiseli D5 ukuya kwi-F11

Imigangatho 3 ne-4 ishiywe ingenanto ukuze kulungelelaniswe iifom zoluhlu ngexesha le tutorial.

I-tutorial ayifaki ukufomatha ebonwe kumfanekiso, kodwa oku akukuchaphazela indlela ifom ye-lookup isebenza ngayo.

Ulwazi malunga nokufometha okukhethiweyo kufana nalabo abonwe ngentla ziyafumaneka kule Tetorial Basic Formatting Formation.

02 we-09

Ukuqala ukusebenza kwe-INDEX

Umsebenzi we-INDEX ungomnye wabambalwa kwi-Excel eneefom ezininzi. Umsebenzi unesimo sohlobo kunye neFomu yokuBhekisela .

Ifomu ye-Array ibuyisela idatha yangempela kwisiseko sedatha okanye itafile yedatha, ngelixa iFomu yokuFakelwa ikunika iselfowuni okanye indawo yedata etafileni.

Kule tutorial siza kusebenzisa ifom ye-Array njengoko sifuna ukwazi igama lomthengisi wee-widget ze-titanium kunokubhekisela kweseli kulo mthengisi kwiziko lethu.

Ifom nganye ifom uluhlu oluhlukeneyo lweengxabano okumelwe lukhethwe ngaphambi kokuqala umsebenzi.

Iimfundiso zeTutorial

1. Cofa kwifowuni F3 ukwenza iteksi esebenzayo . Yilapho esiya kufaka khona umsebenzi odibeneyo.
2. Cofa kwiTrafom ithebhu yemenyu yebhondi .
3. Khetha uLookup kunye neNkcukacha kwi-Ribbon ukuvula uluhlu lokuhla kwehla.
4. Cofa kwi- INDEX kuloluhlu ukukhupha ibhokisi yencoko yeeArguments .
5. Khetha uluhlu, umugqa_num, ukhetho lwe- col_num kwibhokisi yencoko.
6. Cofa Kulungile ukuvula ibhokisi yencoko yencoko ye-INDEX.

03 ka 09

Ukufaka i-INDEX yoHlelo lweMisebenzi yokuHlanganisa

Ingxabano yokuqala efunekayo yi-Array argument. Le ngxabano ichaza uluhlu lweeseli ukukhangela idatha efunwayo.

Kule khokelo le ngxabano iya kuba sisiseko sethu sedatha .

Iimfundiso zeTutorial

1. Kwibhokisi yencoko yencoko ye -INDEX, nqakraza kumgca we- Array .
2. Gqamisa iiseli ze-D6 ukuya kwi-F11 kwiphepha lokusebenzela ukuze ufake ibhola kwibhokisi yencoko.

04 we-09

Ukuqala ukusebenza kweMATCH Function

Xa ukuhlalisa omnye umsebenzi ngaphakathi kwelinye akunakwenzeka ukuvula ibhokisi yencoko yencoko yomsebenzi okanye yesibini ukufaka iingxoxo eziyimfuneko.

Umsebenzi odibeneyo kufuneka ufakwe njengenye yeengxabano zomsebenzi wokuqala.

Kule tutorial, umsebenzi weMATCH odibeneyo kunye neziphakamiso zayo ziya kufakwa kumgca wesibini webhokisi yencoko yebhokisi ye- INDEX - umgca weRow_num .

Kubalulekile ukuba uqaphele ukuba, xa ufaka imisebenzi ngokwayo, iziphakamiso zomsebenzi ziyahlukana omnye komnye nge-comma "," .

Ukungena kwi-MATCH Function Lookup_value Argument

Isinyathelo sokuqala ekungeneni umsebenzi we-MATCH odibeneyo kukufaka ingxabano yeLookup_value .

I- Lookup_value iya kuba yindawo okanye iselfowuni yegama leseshishini lokukhangela esifuna ukulifanisa kwisiseko sedatha.

Ngokuqhelekileyo iLookup_value iyakwamukela kuphela iikhrayitheriya enye okanye ikota. Ukuze sifune iindlela ezininzi, kufuneka sikwandise i- Lookup_value .

Oku kwenziwa ngokucwangcisa okanye ukujoyina iinkcukacha ezibini okanye ngaphezulu kunye kunye nokusebenzisa uphawu lwe-ampersand " & ".

Iimfundiso zeTutorial

1. Kwibhokisi yencoko yencoko ye-INDEX, nqakraza kwi- Row_num line.
2. Thayipha umdlalo wegama lomsebenzi olandelwe ibakaki evulekile " ( "
3. Cofa kwiseli D3 ukungena kuloo ntetho yeselenki kwibhokisi yencoko.
4. Thayipha i-ampersand " & " emva kwesalathisi se- D3 ukwenzela ukongeza isalathisi yesibini yesibini.
5. Cofa kwifowuni E3 ukungena kweli festile yesibini kwibhokisi yencoko.
6. Thayipha i-comma "," emva kokubhekisele kweseli E3 ukugqiba ukungena kwempikiswano yomsebenzi we- MATCH Lookup_value .
7. Shiya ibhokisi yencoko yencoko ye-INDEX evuliwe kwisinyathelo esilandelayo kwisifundo.

Kwinqanaba lokugqibela lokufundisa i-Lookup_values ​​iya kufakwa kwiiseli D3 no-E3 kwiphepha lokusebenzela.

05 ka 09

Ukongeza i-Lookup_array ye-Function Function

Eli nyathelo liquka ukufaka ingxabano yeLookup_array yomsebenzi we-MATCH odibeneyo.

I- Lookup_array uluhlu lwamaseli apho umsebenzi weMATCH uzakufuna ukufumana ingxabano yeLookup_value yongezwe kwisinyathelo esedlule sesifundo .

Ekubeni siye safumanisa amasimi amabini ophando kwi- Lookup_array ingxabano kufuneka senze okufanayo kwiLookup_array . Umsebenzi we-MATCH uhlola kuphela uluhlu olulodwa kwikota nganye echaziweyo.

Ukufaka iifayile ezininzi sisebenzisa kwakhona i-ampersand " & " ukuze iqokelele ndawonye i-arrays.

Iimfundiso zeTutorial

Ezi nyathelo kufuneka zingeniswe emva kokuba i-comma ingene kwisinyathelo sangaphambili kwi- Row_num line kwibhokisi yebhokisi yencoko ye -INDEX.

1. Cofa kwi- Row_num line emva komxube ukuba ufake indawo yokufaka ekupheleni kokungena kwangoku.
2. Gqamisa iiseli ze-D6 ukuya kwi-D11 kwiphepha lokusebenzela ukufaka uluhlu. Olu luhlu lokuqala lo msebenzi ukukhangela.
3. Thayipha i-ampersand " & " emva kweeferensi zeeseli D6: D11 kuba sifuna umsebenzi ukukhangela iipragram ezimbini.
4. Phakamisa iiseli E6 ukuya ku-E11 kwiphepha lokusebenzela ukuze ufake ububanzi. Olu luhlu lwesibini umsebenzi ukukhangela.
5. Thayipha i-comma "," emva kokubhekisele kweseli ye- E3 ukugqiba ukungena kwempikiswano yomsebenzi we- MATCH Lookup_array .
6. Shiya ibhokisi yencoko yencoko ye-INDEX evuliwe kwisinyathelo esilandelayo kwisifundo.

06 ka 09

Ukongeza uhlobo lomdlalo kunye nokugqiba umsebenzi we-MATCH

Ingxabano yesithathu kunye yokugqibela yomsebenzi we-MATCH yi- Match_type argument.

Le ngxabano ixelela i-Excel indlela yokufanisa i-Lookup_value kunye namaxabiso kwiLookup_array. Iinqununu zezi: 1, 0, okanye -1.

Le ngxabano ikhethiweyo. Ukuba akushiywanga umsebenzi usebenzisa ixabiso elingagqibekanga le-1.

• ukuba i-Match_type = 1 okanye ingashiywanga: UMATCH ufumana ixabiso elikhulu kunawo wonke okanye lilingana neLookup_value. Idatha yeLookup_array kufuneka ihlelwe ngokunyuka komyalelo.
• ukuba umdlalo_type = 0: UMATCH ufumana ixabiso lokuqala elilingana neLookup_value. Idatha yeLookup_array ingahlulwa kunoma yimuphi umyalelo.
• ukuba iMatch_type = -1: IMATCH ifumana ixabiso elincinane kunaleyo okanye lilingana neLookup_value. Idata yeLookup_array kufuneka ihlelwe ngokuhlehla.

Iimfundiso zeTutorial

Ezi nyathelo kufuneka zingeniswe emva kokuba i-comma ingene kwisinyathelo sangaphambili kwi- Row_num line kwibhokisi yebhokisi yencoko ye -INDEX.

1. Ukulandela i-comma kwi- Row_num line, thayipha i-" 0 " kuba sifuna umsebenzi odibeneyo ukubuyisela umdlalo ohambelana nemimiselo esifakayo kwiiseli D3 no-E3.
2. Thayipha ibhondi yokuvala ejikelezayo " ) " ukugqiba umsebenzi weMATCH.
3. Shiya ibhokisi yencoko yencoko ye-INDEX evuliwe kwisinyathelo esilandelayo kwisifundo.

07 ka 09

Buyela kumsebenzi we-INDEX

Ngoku ukuba umsebenzi weMATCH usenziwa siya kuthutha kumgca wesithathu kwibhokisi yencoko yokuvula uze ufake ingxabano yokugqibela yomsebenzi we-INDEX.

Le ngxabano yesithathu kunye yokugqibela yi- Column_num argument exelela i-Excel inombolo yekholomu kwi-D6 ukuya kwi-F11 apho iya kufumana ulwazi olufunayo olubuyiselwe ngumsebenzi. Kule meko, umthengisi wee- widget ze- titanium .

Iimfundiso zeTutorial

1. Cofa kwiKholam_nomgca kwibhokisi yencoko.
2. Faka inombolo yesithathu " 3 " (akukho zicaphulo) kulo mgca kubeni sifuna idatha kwikholamu yesithathu kwinqanaba D6 ukuya kwi-F11.
3. Musa ukuchofoza OK okanye uvale ibhokisi yencoko yencoko ye-INDEX. Kufuneka ihlale ivulekile isinyathelo esilandelayo ekufundiseni - ukudala ifomula yomhlathi .

08 ka 09

Ukudala iFomula yeFom

Ngaphambi kokuvala ibhokisi yencoko yebhokisi yencoko kufuneka siphendule umsebenzi wethu odibeneyo kwifom yefom .

Ifomula yomhlathi yinto evumela ukuba ifune amagama amaninzi kwitheyibhile yedatha. Kule tutorial sibheke ukufana nemigangatho emibini: Iifayile ezivela kwikholam 1 kunye ne-titanium ukusuka kwikholam yesi-2.

Ukudala ifom elandelelweyo kwi-Excel yenziwe ngokucindezela i- CTRL , SHIFT , kwaye ENTER izitshixo kwikhibhodi ngexesha elinye.

Impembelelo yokucindezela ezi zitshixo kunye kukuzungezela umsebenzi kunye neengqungquthela zengqungquthela: {} ebonisa ukuba ngoku i-formula formula.

Iimfundiso zeTutorial

1. Ngebhokisi yencoko yebhokisi yebhokisi yencoko ivulekile ukusuka kwinqanaba langaphambili lale tutorial, cindezela kwaye ubambe iifayile ze CTRL ne SHIFT kwikhibhodi uze ucinezele kwaye ukhulule i- ENTER iqhosha.
2. Ukuba kwenziwe ngokuchanekileyo, ibhokisi yencoko yencoko iya kuvala kwaye impazamo ye-# N / A iza kubonakala kwiseli F3 - iseli apho singene khona emsebenzini.
3. Iphutha le-# N / A livela kwiseli yeF3 kuba iiseli D3 ne-E3 zingenanto. I-D3 ne-E3 yiiseli apho sitshele khona umsebenzi ukuba sifumane i-Lookup_values ​​kwisigaba 5 se-tutorial. Xa idatha yongezwa kula maseli amabini, iiposa iya kutshintshwa ngolwazi oluvela kwisiseko sedatha .

09 we 09

Ukongeza iCriteria Criteria

Inyathelo lokugqibela kwinqanaba lokufundisa lukwongeza amagama okukhangela kwiphepha lethu lomsebenzi.

Njengoko kuboniswe kwisinyathelo esandulelayo, sijonge ukufana nemigqaliselo Iifayile ezivela kwikholamu 1 kunye neTitanium ukusuka kwikholamu yesi-2.

Ukuba, kunye kuphela ukuba, ifomula yethu ifumana umdlalo weemimiselo zombini kwiikholomu ezifanelekileyo kwiziko ledatha, iya kubuyisa inani ukusuka kwikholamu yesithathu.

Iimfundiso zeTutorial

1. Cofa kwiseli D3.
2. Uhlobo lweeWowmark kwaye cindezela ukhiye wokungena kwibhodibhodi.
3. Cofa kwi cell E3.
4. Thayipha iTitanium uze ucine iqhosha lokungena kwikhibhodi.
5. Igama lomthengisi I- Widgets Inc. kufuneka ibonakale kwiseli F3 - indawo yomsebenzi ekubeni ngumthengisi kuphela odwelisweyo othengisa i-Titanium Widgets.
6. Xa ucofa kwiseli F3 umsebenzi opheleleyo
   {= INDEX (D6: F11, MATCH (D3 & E3, D6: D11 & E6: E11, 0), 3)}
   ibonakala kwifom yefomula ngaphezu kwephepha lokusebenzela .

Qaphela: Kwimizekelo yethu kwakukho umthengisi omnye kuphela wee-widget ze-titanium. Ukuba kukho umnikezeli ongaphezu kweyodwa, umthengisi odweliswe kuqala kwi-database ubuyiselwa ngumsebenzi.