30 Excel imagwira ntchito m'masiku 30: INDIRECT

Zabwino zonse! Munafika pa tsiku lomaliza la mpikisano wa marathoni 30 Excel imagwira ntchito m'masiku 30. Unali ulendo wautali komanso wosangalatsa womwe mwaphunzira zinthu zambiri zothandiza pazantchito za Excel.

Pa tsiku la 30 la marathon, tidzapereka phunziro la ntchitoyi KUMALO (INDIRECT), yomwe imabweza ulalo womwe wafotokozedwa ndi zingwe zolembedwa. Ndi ntchitoyi, mutha kupanga mindandanda yotsatsira yodalira. Mwachitsanzo, posankha dziko kuchokera pamndandanda wotsitsa, ndiye kuti ndi zosankha ziti zomwe zidzawonekere pamndandanda wotsitsa wa mzinda.

Choncho, tiyeni tione mwatsatanetsatane gawo la chiphunzitso cha ntchito KUMALO (INDIRECT) ndikuwona zitsanzo zogwiritsa ntchito. Ngati muli ndi zina zowonjezera kapena zitsanzo, chonde gawanani nawo mu ndemanga.

Ntchito 30: INDIRECT

ntchito KUMALO (INDIRECT) imabweretsanso ulalo womwe wafotokozedwa ndi zingwe zolembedwa.

Kodi mungagwiritse ntchito bwanji INDIRECT?

Popeza ntchito KUMALO (INDIRECT) imabweretsanso ulalo woperekedwa ndi zingwe, mutha kugwiritsa ntchito ku:

  • Pangani ulalo woyambira wosasuntha.
  • Pangani zolozera ku gulu losasunthika.
  • Pangani ulalo pogwiritsa ntchito pepala, mizere, ndi zambiri zazagawo.
  • Pangani nambala yosasintha.

Syntax INDIRECT (INDIRECT)

ntchito KUMALO (INDIRECT) ili ndi mawu awa:

INDIRECT(ref_text,a1)

ДВССЫЛ(ссылка_на_ячейку;a1)

  • ref_text (link_to_cell) ndiye mawu apaulalo.
  • a1 - ngati yofanana ndi TRUE (CHOONA) kapena sichinatchulidwe, ndiye kuti kalembedwe ka ulalo kadzagwiritsidwa ntchito A1; ndipo ngati ZABODZA (ZABODZA), ndiye kalembedwe Mtengo wa R1C1.

Misampha INDIRECT (INDIRECT)

  • ntchito KUMALO (INDIRECT) imawerengedwanso nthawi zonse zikasintha zomwe zili patsamba la Excel. Izi zitha kuchepetsa kwambiri bukhu lanu lantchito ngati ntchitoyi ikugwiritsidwa ntchito m'njira zambiri.
  • Ngati ntchito KUMALO (INDIRECT) imapanga ulalo ku bukhu lina la Excel, bukulo liyenera kukhala lotseguka kapena chilinganizocho chidzanena zolakwika. #REF! (#LINK!).
  • Ngati ntchito KUMALO (INDIRECT) amalozera zamtundu womwe umapitilira malire a mzere ndi magawo, fomulayo iwonetsa cholakwika #REF! (#LINK!).
  • ntchito KUMALO (INDIRECT) sangatchule mtundu womwe watchulidwa.

Chitsanzo 1: Pangani ulalo woyambira wosasuntha

Muchitsanzo choyamba, zigawo C ndi E zili ndi manambala omwewo, mawerengero awo amawerengedwa pogwiritsa ntchito ntchitoyi SUM (SUM) ndi omwewo. Komabe, mafomuwa ndi osiyana pang'ono. Mu cell C8, formula ndi:

=SUM(C2:C7)

=СУММ(C2:C7)

Mu cell E8, ntchito KUMALO (INDIRECT) imapanga ulalo woyambira selo E2:

=SUM(INDIRECT("E2"):E7)

=СУММ(ДВССЫЛ("E2"):E7)

Ngati muyika mzere pamwamba pa pepala ndikuwonjezera mtengo wa Januware (Januware), ndiye kuti kuchuluka kwa gawo C sikungasinthe. Fomula idzasintha, potengera kuwonjezeredwa kwa mzere:

=SUM(C3:C8)

=СУММ(C3:C8)

Komabe, ntchito KUMALO (INDIRECT) imakonza E2 ngati selo loyambira, motero Januwale amangophatikizidwa powerengera magawo E. Selo lomaliza lasintha, koma selo loyambira silinakhudzidwe.

=SUM(INDIRECT("E2"):E8)

=СУММ(ДВССЫЛ("E2"):E8)

Chitsanzo 2: Lumikizani ku gulu losasintha

ntchito KUMALO (INDIRECT) atha kupanga zolozera kumtundu womwe watchulidwa. Mu chitsanzo ichi, maselo a buluu amapanga mndandanda NumList. Kuphatikiza apo, mitundu yosinthika imapangidwanso kuchokera kuzinthu zomwe zili mugawo la B NumListDyn, kutengera kuchuluka kwa manambala omwe ali patsamba lino.

Chiwerengero cha milingo yonse iwiriyi chikhoza kuwerengedwa pongopereka dzina lake ngati mtsutso pa ntchitoyi SUM (SUM), monga mukuwonera m'maselo E3 ndi E4.

=SUM(NumList) или =СУММ(NumList)

=SUM(NumListDyn) или =СУММ(NumListDyn)

M'malo molemba dzina losiyana kukhala ntchito SUM (SUM), Mutha kuloza ku dzina lolembedwa mu imodzi mwamaselo a patsambali. Mwachitsanzo, ngati dzina NumList zalembedwa mu selo D7, ndiye chilinganizo mu selo E7 adzakhala motere:

=SUM(INDIRECT(D7))

=СУММ(ДВССЫЛ(D7))

Mwatsoka ntchito KUMALO (INDIRECT) silingapange zolozera zamitundu yosiyanasiyana, chifukwa chake mukakopera fomulayi mu cell E8, mupeza cholakwika. #REF! (#LINK!).

Chitsanzo 3: Pangani ulalo pogwiritsa ntchito masamba, mizere, ndi ndandanda

Mutha kupanga ulalo mosavuta kutengera mzere ndi manambala amizere, komanso kugwiritsa ntchito mtengo wa FALSE (FALSE) pa mkangano wachiwiri wantchito. KUMALO (INDIRECT). Umu ndi momwe ulalo wa kalembedwe umapangidwira Mtengo wa R1C1. Muchitsanzochi, tawonjezeranso dzina lachipepala pa ulalo - 'MyLinks'!R2C2

=INDIRECT("'"&B3&"'!R"&C3&"C"&D3,FALSE)

=ДВССЫЛ("'"&B3&"'!R"&C3&"C"&D3;ЛОЖЬ)

Chitsanzo 4: Pangani manambala osasintha

Nthawi zina muyenera kugwiritsa ntchito manambala angapo mu Excel formulas. Muchitsanzo chotsatirachi, tikufuna kuwerengera manambala 3 akulu kwambiri pagawo B. Manambalawa atha kulembedwa munjira, monga momwe zimachitikira mu selo D4:

=AVERAGE(LARGE(B1:B8,{1,2,3}))

=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;{1;2;3}))

Ngati mukufuna gulu lalikulu, ndiye kuti simungafune kuyika manambala onse mu fomula. Njira yachiwiri ndiyo kugwiritsa ntchito ntchitoyi Row (ROW), monga momwe amachitira mu fomula yolowa mu cell D5:

=AVERAGE(LARGE(B1:B8,ROW(1:3)))

=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3)))

Njira yachitatu ndiyo kugwiritsa ntchito ntchitoyi Row (STRING) pamodzi ndi KUMALO (INDIRECT), monga momwe adachitira ndi fomula mu cell D6:

=AVERAGE(LARGE(B1:B8,ROW(INDIRECT("1:3"))))

=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(ДВССЫЛ("1:3"))))

Zotsatira zamitundu yonse 3 zidzakhala zofanana:

Komabe, ngati mizere itayikidwa pamwamba pa pepalalo, fomula yachiwiri ibweretsa zotsatira zolakwika chifukwa chakuti zolozerazo zisintha pamodzi ndi kusintha kwa mizere. Tsopano, m'malo mwa avareji ya ziwerengero zazikuluzikulu zitatu, chilinganizocho chimabwezera avareji ya manambala akuluakulu a 3, 4, ndi 5.

Kugwiritsa ntchito KUMALO (INDIRECT), fomula yachitatu imasunga mizere yolondola ndikupitilira kuwonetsa zotsatira zolondola.

Siyani Mumakonda