Pulogalamu Yoyang'ana Pakati pa Zowonjezera Zambiri

Pogwiritsira ntchito ndondomeko yosiyanasiyana mu Excel tikhoza kupanga fomu yamakono yomwe imagwiritsa ntchito njira zambiri kuti tipeze zambiri mu deta kapena tebulo la deta.

Njira yowonjezeramo ikuphatikizapo kumangirira ntchito YAMAKONSO mkati mwa INDEX ntchito.

Chitsanzo ichi chimaphatikizapo chitsanzo choyendetsedwerapo cha kupanga pulogalamu yochezera yomwe imagwiritsira ntchito njira zambiri zopezera wogula ajambuliki a titaniyamu muzitsanzo zachinsinsi.

Kutsatira ndondomeko m'mitu yophunzitsira pansipa ikukuyenderani kupyolera mukulenga ndikugwiritsa ntchito ndondomeko yomwe ili mu chithunzi pamwambapa.

01 ya 09

Kulowa Datorial Data

Gawo loyamba mu phunziroli ndilowetsa deta mu Excel sheet sheet .

Kuti mutenge tsatanetsatane mu phunzirolo lowetsani deta yosonyezedwa mu chithunzi pamwamba pa maselo otsatirawa.

• Lowetsani deta yapamwamba mu maselo D1 mpaka F2
• Lowani chigawo chachiwiri mu maselo D5 mpaka F11

Mizere 3 ndi 4 yasiyidwa yopanda kanthu kuti akwaniritse ndondomeko yowonjezera yomwe ilipo panthawiyi.

Maphunzirowa sakuphatikizapo maonekedwe omwe awonedwa mu fano, koma izi sizidzakhudza momwe fomu yamakono imagwirira ntchito.

Zambiri pazomwe mungapangire zofanana ndi zomwe tawonera pamwambazi zilipo mu phunziroli la Basic Excel Formatting.

02 a 09

Kuyambira ntchito INDEX

Ntchito INDEX ndi imodzi mwa ochepa mu Excel omwe ali ndi mitundu yambiri. Ntchitoyi ili ndi Fomu Yowonjezera ndi Fomu Yoyenera .

Fomu ya Array imabweretsanso deta lenileni kuchokera ku deta kapena tebulo la deta, pomwe Fomu ya Fomu imakupatsani selo kapena malo a deta.

Mu phunziroli tidzagwiritsa ntchito Fomu ya Mzere popeza tikufuna kudziwa dzina la wogulitsa kwa ma widget otani m'malo mofotokozera selo kwa wogulitsa izi m'dongosolo lathu.

Fomu lirilonse liri ndi mndandanda wosiyana wa zifukwa zomwe ziyenera kusankhidwa musanayambe ntchitoyo.

Maphunziro Otsogolera

1. Dinani pa selo F3 kuti likhale selo yogwira ntchito . Apa ndi pamene tidzalowa ntchito yachisa.
2. Dinani pa Fomu tab ya menyu yowonjezera.
3. Sankhani Kutsegula ndi Kutchulidwa kuchokera ku riboni kuti mutsegule ntchito yolemba pansi.
4. Dinani ku INDEX mu mndandanda kuti mubweretse kukambirana kwasankho .
5. Sankhani mndandanda, mndandanda, mndandanda, ndondomeko yotsatila mubox .
6. Dinani OK kuti mutsegule ntchito INDEX ntchito dialog box.

03 a 09

Kulowa mu COMPEY Function Array argument

Chotsutsana choyamba chofunika ndi kutsutsana kwadongosolo. Mtsutso uwu umatanthawuza ma selo osiyanasiyana kuti afufuzidwe pa data yofunidwa.

Kwa phunziro ili, mfundoyi idzakhala yosungiramo zitsanzo zathu.

Maphunziro Otsogolera

1. Mu INDEX ntchito bokosi la bokosi , dinani pa mndandanda.
2. Onetsetsani maselo D6 kuti F11 mu ofesi yolembera kuti mulowetse mndandanda mu bokosi.

04 a 09

Kuyambira Mgwirizano wa NTHAWI Yogwidwa

Pamene kumanga thupi kumagwira mkati mwa wina sikutheka kutsegula bokosi la ntchito lachiwiri kapena lachisa polemba zifukwa zoyenera.

Ntchito yodyetsedwa iyenera kuyimilidwa ngati imodzi mwa zifukwa za ntchito yoyamba.

Mu phunziro ili, chithunzi cha MATCH chokhala ndi chisa chake ndi zifukwa zake zidzalowa mu mzere wachiwiri wa INDEX ntchito yolemba bokosi - Row_num line.

Ndikofunika kuzindikira kuti, polowera ntchito pamanja, mfundo zokhudzana ndi ntchito zimasiyanirana ndi chiwerengero "," .

Kulowetsa MATCH's Lookup_value Kukangana

Gawo loyamba lolowa mu chithunzi cha MATCH chodyetsedwa ndilowetsa mkangano wa Lookup_value .

Lookup_value idzakhala malo kapena selo loyang'ana pa nthawi yomwe mukufuna kufufuza.

Kawirikawiri Lookup_value amavomereza kamodzi kokha kafukufuku kapena nthawi. Kuti tifufuze mayankho angapo, tiyenera kuwonjezera lookup_value .

Izi zimachitika polemba kapena kulumikiza mazokambirana awiri kapena ambiri pamodzi pogwiritsa ntchito chizindikiro cha ampersand " & ".

Maphunziro Otsogolera

1. Mu INDEX ntchito dialog box, dinani pa Row_num line.
2. Lembani mndandanda wa dzina la ntchito motsatizana ndi bolodi lozungulira lotseguka " ( "
3. Dinani pa selo D3 kuti mulowetse selololo mu bokosi la dialog.
4. Lembani ampersand " & " pambuyo pa selo ya D3 yowonjezera kuti muwonjezere selo lachiwiri.
5. Dinani pa selo E3 kuti mulowetse selo lachiwirilo mulowemo.
6. Lembani comma "," kutanthauzira selo la E3 kukwaniritsa malowedwe a MATCH's Lookup_value argument.
7. Chokani INDEX ntchito yolemba bokosi lotseguka pa sitepe yotsatira mu phunziro.

Mu sitepe yotsiriza ya phunziro la Lookup_values ​​lidzalowa mu maselo D3 ndi E3 a worksheet.

05 ya 09

Kuwonjezera pa Lookup_array kwa Ntchito MATCH

Khwerero iyi ikuphatikizapo kuwonjezera mkangano wa Lookup_array wa ntchito yomanga MATCH.

Lookup_array ndi maselo osiyanasiyana omwe MATCH ntchito ikuyesa kupeza fufuzani Lookup_value yowonjezeredwa mu sitepe yapitayi ya phunzirolo.

Popeza tapeza malo awiri ofufuzira mu mkangano wa Lookup_array tiyenera kuchita chimodzimodzi kwa Lookup_array . Ntchito MATCH imangosaka imodzi yokha ya mawu omwe atchulidwa.

Kuti tilowetsenso maulendo ambiri timagwiritsanso ntchito ampersand " & " kuti tigwirizane pamodzi.

Maphunziro Otsogolera

Zochitika izi ziyenera kulowetsedwa pambuyo pa komma yomwe inalowa mu sitepe yapitayi pa Row_num line mu INDEX ntchitobox dialog box .

1. Dinani pa Row_num mzere pambuyo pa comma kuti muikepo tsamba lolowetsa kumapeto kwa zolowera.
2. Onetsetsani maselo D6 mpaka D11 mu tsamba lokuthandizani kuti mulowe muyeso. Izi ndizoyamba ntchitoyi ndi kufufuza.
3. Lembani ampersand " & " pambuyo mafotokozedwe a selo D6: D11 chifukwa tikufuna ntchitoyi kufufuza zigawo ziwiri.
4. Onetsetsani maselo E6 mpaka E11 mu ofesi yolembera. Ichi ndichiwiri ntchitoyi ndi kufufuza.
5. Lembani comma "," kutanthauzira selo ya E3 kukwaniritsa malowedwe a MATCH a Lookup_array .
6. Chokani INDEX ntchito yolemba bokosi lotseguka pa sitepe yotsatira mu phunziro.

06 ya 09

Kuwonjezera Mtundu wa Match ndi Kumaliza Ntchito YAMATCH

Kukambirana kwachitatu ndi kotsiriza kwa ntchito MATCH ndi Match_type mtsutso.

Mtsutso uwu umauza Excel momwe mungagwirizanitse ndi Lookup_value ndi mfundo mu Lookup_array. Zosankha ndi: 1, 0, kapena -1.

Mtsutso uwu ndi wosankha. Ngati iyo yasiya ntchitoyi imagwiritsa ntchito mtengo wosasintha wa 1.

• ngati Match_type = 1 kapena yasiya: MATCH imapeza mtengo waukulu kwambiri womwe uli wochepa kapena wofanana ndi Lookup_value. Deta ya Lookup_array iyenera kusankhidwa kukwera.
• ngati match_type = 0: MATCH amapeza mtengo woyamba womwe uli wofanana ndi Lookup_value. Deta ya Lookup_array ikhoza kusankhidwa mu dongosolo lililonse.
• ngati Match_type = -1: MATCH imapeza mtengo wochepa kwambiri kuposa kapena wofanana ndi Lookup_value. Deta ya Lookup_array iyenera kusankhidwa potsika.

Maphunziro Otsogolera

Zochitika izi ziyenera kulowetsedwa pambuyo pa komma yomwe inalowa mu sitepe yapitayi pa Row_num line mu INDEX ntchitobox dialog box .

1. Potsatira chiwerengero pa Row_num line, yesani zero " 0 " chifukwa tikufuna ntchito yodzala kubwezeretsa zofanana ndi zomwe timalowa m'maselo D3 ndi E3.
2. Lembani mzere wozungulira " ) " kukwaniritsa ntchito MATCH.
3. Chokani INDEX ntchito yolemba bokosi lotseguka pa sitepe yotsatira mu phunziro.

07 cha 09

Bwererani ku ntchito INDEX

Tsopano kuti ntchito ya MATCH yatsimikizika tidzasunthira ku gawo lachitatu lotseguka la bokosilo ndikulowa mkangano womaliza wa INDEX ntchito.

Nthano yachitatu ndi yomaliza ndiyo ndondomeko_makangano omwe amauza Excel chiwerengero cha mndandanda m'mabuku a D6 mpaka F11 komwe angapeze zomwe tikufuna kuti zibwezeretsedwe. Pankhaniyi, katundu wotsatsa maofesi a titaniyamu .

Maphunziro Otsogolera

1. Dinani pa Column_mu mzere mu bokosi la bokosi.
2. Lowani chiwerengero chachitatu " 3 " (palibe ndemanga) pamzerewu kuyambira pamene tikuyang'ana deta m'mbali yachitatu ya D6 mpaka F11.
3. Musati Dinani OK kapena mutseke INDEX ntchito yolemba bokosi. Iyenera kukhala yotseguka pa sitepe yotsatirayi - popanga ndondomeko yambiri .

08 ya 09

Kupanga Mpangidwe Wowonjezera

Tisanatseke bukhuli tiyenera kugwiritsa ntchito ndondomeko yathu yowonjezera .

Njira yowonjezera ndiyo yomwe imaloleza kufufuza mau angapo mu tebulo la deta. Mu phunziro ili tikuyang'ana kuti tigwirizane ndi mawu awiri: Mayijayi kuchokera ku ndime 1 ndi titaniyamu kuchokera ku ndime 2.

Kupanga ndondomeko yowonjezera mu Excel yachitidwa mwa kukakamiza CTRL , SHIFT , ndi ENTER makiyi pa makiyi nthawi yomweyo.

Zotsatira za kukakamiza makiyi awa pamodzi ndikuzungulira ntchitoyo ndi makongoletsedwe: {} posonyeza kuti tsopano ndi ndondomeko yambiri.

Maphunziro Otsogolera

1. Ndi bokosi la bokosi lomaliza likutsegulidwa kuchokera ku gawo lapitalo la phunziro ili, pindani ndi kugwira CTRL ndi SHIFT makiyi pa ikhibhodi ndiye pezani ndi kumasula ENTER .
2. Ngati mwachita molondola, bokosi la funso lidzatsekedwa ndipo nthenda ya # N / A idzawonekera mu selo F3 - selo limene tinalowa ntchito.
3. Vuto la # N / A likuwoneka mu selo F3 chifukwa maselo D3 ndi E3 alibe kanthu. D3 ndi E3 ndi maselo kumene tinauza ntchito kuti tipeze ma Lookup_values ​​mu gawo la 5 la phunzirolo. Deta ikangowonjezedwa ku maselo awiriwa, vutoli lidzaloledwa ndi chidziwitso kuchokera ku deta .

09 ya 09

Kuwonjezera Zofuna Zosaka

Gawo lomaliza mu phunziro ndi kuwonjezera mawu ofufuzira ku tsamba lathu la ntchito.

Monga tanenera mu sitepe yapitayi, tikuyang'ana kuti tifanane ndi mafotokozedwe Ophatikiza pa ndime 1 ndi titaniyamu kuchokera pa ndime 2.

Ngati, ndipo kokha ngati, mayendedwe athu amapeza machesi awiriwo m'mazenera oyenera m'kabokosilo, adzabwezera mtengo kuchokera ku gawo lachitatu.

Maphunziro Otsogolera

1. Dinani pa selo D3.
2. Lembani Zigawolo ndi pindikizani Mphindi ya Kulowa pa makiyi.
3. Dinani pa selo E3.
4. Lembani Titani ndikusindikiza fungulo lolowani pa kibokosi.
5. Wopatsa katunduyo dzina lakuti Widgets Inc. ayenera kuwonedwa mu selo F3 - malo omwe ntchitoyo ilipo chifukwa ndi omwe akugulitsidwa omwe akugulitsa Mayi Widget.
6. Mukasindikiza pa selo F3 ntchito yonse
   {= INDEX (D6: F11, MATCH (D3 & E3, D6: D11 & E6: E11, 0), 3)}
   imapezeka mu bar lamuzula pamwamba pa tsamba .

Zindikirani: Mu chitsanzo chathu panali pulogalamu imodzi yokha ya ma widget a titaniyamu. Ngati pangakhale zogulitsa zingapo, wogulitsa omwe adatchulidwa koyambirirayi akubwezeredwa ndi ntchitoyo.