Imisebenzi engama-30 ye-Excel ezinsukwini ezingama-30: INDIRECT

Siyakuhalalisela! Ufinyelele osukwini lokugcina lwemarathon 30 Excel isebenza ngezinsuku ezingama-30. Kube wuhambo olude noluhehayo lapho ofunde khona izinto eziningi eziwusizo mayelana nemisebenzi ye-Excel.

Ngosuku lwe-30 lwemarathon, sizonikela ngesifundo somsebenzi INDALO (INDIRECT), ebuyisela isixhumanisi esishiwo yiyunithi yezinhlamvu yombhalo. Ngalo msebenzi, ungakha uhlu lokudonsela phansi oluncikile. Isibonelo, lapho ukhetha izwe ohlwini lokudonsela phansi kunquma ukuthi yiziphi izinketho ezizovela ohlwini lokudonsela phansi kwedolobha.

Ngakho-ke, ake sibhekisise ingxenye yethiyori yomsebenzi INDALO (INDIRECT) futhi uhlole izibonelo ezingokoqobo zokusetshenziswa kwayo. Uma unolwazi olwengeziwe noma izibonelo, sicela wabelane ngazo kumazwana.

Umsebenzi 30: I-INDIRECT

Umsebenzi INDALO (INDIRECT) ibuyisela isixhumanisi esishiwo yiyunithi yezinhlamvu yombhalo.

Ungawusebenzisa kanjani umsebenzi we-INDIRECT?

Kusukela umsebenzi INDALO (INDIRECT) ibuyisela isixhumanisi esinikezwe iyunithi yezinhlamvu yombhalo, ongayisebenzisa ukuze:

  • Dala isixhumanisi sokuqala esingaguquki.
  • Dala ireferensi kububanzi obuqanjwe igama elimile.
  • Dala isixhumanisi usebenzisa ishidi, umugqa, nolwazi lwekholomu.
  • Dala izinombolo eziningi ezingaguquki.

I-syntax INDIRECT (INDIRECT)

Umsebenzi INDALO (INDIRECT) ine-syntax elandelayo:

INDIRECT(ref_text,a1)

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

  • umbhalo_wokuphinda (link_to_cell) umbhalo wesixhumanisi.
  • a1 - uma ilingana neQINISO (TRUE) noma ingacacisiwe, khona-ke isitayela sesixhumanisi sizosetshenziswa A1; futhi uma AMANGA (AMAMANGA), kusho isitayela I-R1C1.

Izicupho INDIRECT (INDIRECT)

  • Umsebenzi INDALO (INDIRECT) ibalwa kabusha noma nini lapho amanani eshidi lokusebenzela le-Excel eshintsha. Lokhu kunganciphisa kakhulu ibhuku lakho lokusebenza uma umsebenzi usetshenziswa kumafomula amaningi.
  • Uma umsebenzi INDALO (INDIRECT) yenza isixhumanisi kwenye incwadi yokusebenzela ye-Excel, leyo ncwadi yokusebenzela kufanele ivuleke noma ifomula izobika iphutha #REF! (#XHUMANA!).
  • Uma umsebenzi INDALO (INDIRECT) ibhekisela kububanzi obudlula umkhawulo werowu nekholomu, ifomula izobika iphutha #REF! (#XHUMANA!).
  • Umsebenzi INDALO (INDIRECT) ayikwazi ukubhekisela kububanzi obuqanjwe ngokuguqukayo.

Isibonelo sokuqala: Dala isixhumanisi sokuqala esingaguquki

Esibonelweni sokuqala, amakholomu C no-E aqukethe izinombolo ezifanayo, izibalo zazo zibalwa kusetshenziswa umsebenzi ISUM (SUM) nazo ziyafana. Nokho, amafomula ahluke kancane. Kuseli C8, ifomula ithi:

=SUM(C2:C7)

=СУММ(C2:C7)

Kuseli E8, umsebenzi INDALO (INDIRECT) idala isixhumanisi seseli E2 yokuqala:

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

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

Uma ufaka umugqa phezulu eshidini futhi wengeza inani likaJanuwari (Jan), inani elikukholomu C ngeke lishintshe. Ifomula izoshintsha, isabela ekufakweni komugqa:

=SUM(C3:C8)

=СУММ(C3:C8)

Nokho, umsebenzi INDALO (INDIRECT) ilungisa u-E2 njengeseli lokuqala, ngakho u-January ufakwa ngokuzenzakalelayo ekubalweni kwesamba sekholomu E. Iseli lokugcina lishintshile, kodwa iseli lokuqala alizange lithinteke.

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

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

Isibonelo sesi-2: Xhuma kububanzi obunegama elingashintshi

Umsebenzi INDALO (INDIRECT) ingakha ireferensi yobubanzi obuqanjiwe. Kulesi sibonelo, amaseli aluhlaza okwesibhakabhaka akha ububanzi NumList. Ngaphezu kwalokho, ububanzi obuguqukayo bubuye budalwe kusuka kumanani akukholomu B I-NumListDyn, kuye ngenani lezinombolo kule kholomu.

Isamba sazo zombili ububanzi singabalwa ngokumane sinikeze igama laso njengempikiswano yomsebenzi ISUM (SUM), njengoba ungabona kumaseli E3 no-E4.

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

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

Esikhundleni sokuthayipha igama lobubanzi kumsebenzi ISUM (SUM), Ungabhekisela egameni elibhalwe kwelinye lamaseli eshidi lomsebenzi. Isibonelo, uma igama NumList ibhalwe kuseli D7, bese ifomula kuseli E7 izoba kanje:

=SUM(INDIRECT(D7))

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

Ngeshwa umsebenzi INDALO (INDIRECT) ayikwazi ukudala ireferensi yobubanzi obuguqukayo, ngakho-ke uma ukopisha le fomula phansi kuseli E8, uzothola iphutha. #REF! (#XHUMANA!).

Isibonelo sesi-3: Dala isixhumanisi usebenzisa ishidi, umugqa, nolwazi lwekholomu

Ungakwazi kalula ukudala isixhumanisi ngokusekelwe kumugqa nezinombolo zekholomu, kanye nokusebenzisa inani elithi FALSE (FALSE) ku-agumenti yesibili yomsebenzi. INDALO (INDIRECT). Le yindlela isixhumanisi sesitayela esakhiwe ngayo I-R1C1. Kulesi sibonelo, sengeze igama leshidi kusixhumanisi – 'MyLinks'!R2C2

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

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

Isibonelo sesi-4: Dala izinombolo eziningi ezingaguquki

Kwesinye isikhathi udinga ukusebenzisa izinombolo eziningi kumafomula e-Excel. Esibonelweni esilandelayo, sifuna ukulinganisa izinombolo ezinkulu ezi-3 kukholamu B. Izinombolo zingafakwa efomini, njengoba kwenziwa kuseli D4:

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

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

Uma udinga amalungu afanayo amakhulu, cishe ngeke ufune ukufaka zonke izinombolo kufomula. Inketho yesibili ukusebenzisa umsebenzi UMUGQA (UROW), njengoba kwenziwa kufomula yamalungu afanayo efakwe kuseli D5:

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

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

Inketho yesithathu ukusebenzisa umsebenzi UMUGQA (STRING) kanye INDALO (INDIRECT), njengoba kwenziwa ngefomula yamalungu afanayo kuseli D6:

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

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

Umphumela wawo wonke amafomula ama-3 uzofana:

Nokho, uma imigqa ifakwe phezulu eshidini, ifomula yesibili izobuyisela umphumela ongalungile ngenxa yokuthi izinkomba kufomula zizoshintsha kanye nokushintshwa komugqa. Manje, esikhundleni sesilinganiso sezinombolo ezintathu ezinkulu, ifomula ibuyisela isilinganiso sezinombolo ezinkulu zesi-3, sesi-4, nesi-5.

Ukusebenzisa imisebenzi INDALO (INDIRECT), ifomula yesithathu igcina izithenjwa zomugqa ezifanele futhi iyaqhubeka nokubonisa umphumela olungile.

shiya impendulo