I-Excel SUM kunye ne-INDIRECT yeDynamic Range Formula

I-Microsoft Excel inezinye iindlela ezipholileyo kunye nokusebenzisa i-SUM ne-INDIRECT iifomula zamanqanaba ezinamandla ziindlela ezimbini zokusebenzisa kalula idatha onayo.

I-SUM - INDIRECT Uqwalaselo lwefomula

Ukusebenzisa umsebenzi we- INDIRECT kwiifomula ze-Excel kwenza kube lula ukutshintsha uluhlu lweeferensi zesetyenzisiweyo ezisetyenzisiweyo ngaphandle kokuba uhlele ifom ngokwayo.

I-INDIRECT ingasetyenziselwa nemisebenzi emininzi eyamkela iselferensi yeseli njengengxabano efana ne- OFFSET kunye ne-SUM imisebenzi.

Kwimeko yokugqibela, ukusebenzisa i-INDIRECT njengengxabano yomsebenzi we-SUM kunokudala uluhlu olunezakhono zeeferensi zee- cell ezenziwa umsebenzi we-SUM.

I-INDIRECT ingaba ngoku ngokubhekisela kwiseli kwiiseli ngokungqalileyo kwindawo ephakathi.

Umzekelo: I-SUM-INDIRECT Formula esetyenziselwa ukuQinisekisa iNani leMida yeMilinganiselo

Lo mzekelo usekelwe kwidatha eboniswe kumfanekiso ongentla.

Ifomula ye- SUM - INDIRECT eyenziwe ngokusebenzisa amanyathelo okufundisa apha ngezantsi:

= I-SUM (INDIRECT ("D" & E1 & ": D" & E2))

Kule fomyula, ingxabano yomsebenzi we-INDIRECT inamathengi kwiiseli E1 no-E2. Amanani kulawo maseli, 1 no-4, xa ehlanganiswe nayo yonke ingxabano ye-INDIRECT, yenza ifom yeeferensi ze-D1 kunye ne-D4.

Ngenxa yoko, uluhlu lweenombolo ezizaliswe ngumsebenzi we- SUM lwedatha equlethwe kwiiseli D1 ukuya kwi-D4 - eyi-50.

Ngokutshintsha amanani akwiiseli E1 ne-E2; nangona kunjalo, udidi olufunekayo luya kuguqulwa lula.

Lo mzekelo uza kuqala ukusebenzisa ifom yelapha ngentla ukugqiba idatha kwiiseli D1: D4 uze utshintshe uluhlu oludibeneyo kwi-D3: D6 ngaphandle kokuhlela ifomu kwifom yeF1.

01 ka 03

Ukufaka i-Formula - Khetha

Izinketho zokungena kule fom ziquka:

• ukuthayipha ifom apha ngasentla ngqo kwiseli F1 kwaye ucinezela ukungena kwekhibhodi
• ukungena umsebenzi we-INDIRECT njengengxabano usebenzisa ibhokisi yencoko yomsebenzi we-SUM

Imisebenzi eminingi kwi-Excel ibhokisi yencoko yababini, evumela ukuba ungene kwiingxoxo zomsebenzi ngamnye kumgca ohlukile ngaphandle kokukhathazeka nge syntax .

Kule meko, ibhokisi yencoko yebhokisi ye-SUM ingasetyenziselwa ukwenza lula i-formula ngendlela ethile. Ngenxa yokuba umsebenzi we-INDIRECT udibene ngaphakathi kwe-SUM, Umsebenzi we-INDIRECT kunye neengxabano zawo kufuneka kusenwe ngesandla.

Amanyathelo angezantsi isebenzisa ibhokisi yencoko ye-SUM ukungenisa ifom.

Ukufaka i-Tutorial Data

Idatha yedatha D1 - 5 D2 - 10 D3 - 15 D4 - 20 D5 - 25 D6 - 30 E1 - 1 E2 - 4

1. Faka idatha elandelayo kwiiseli D1 ukuya ku-E2

Ukuqala i-Formula ye-SUM - INDIRECT - Ukuvula ibhokisi yeNtsebenzo yeSUM ye-SUM

1. Cofa kwifowuni F1 - yilapho iziphumo zalo mzekelo ziza kuboniswa
2. Cofa kwiTrafom ithebhu yemenyu yebhondi
3. Khetha i- Math & Trig kwi-ribbon ukuvula uluhlu lwehla lokuhla
4. Cofa kwi- SUM kuloluhlu ukuvula ibhokisi yencoko yencoko yomsebenzi

02 ka 03

Ukufaka umsebenzi we-INDIRECT - Cofa ukuze Ubonise umfanekiso okhulu

Ifomula ye-INDIRECT idinga ukungeniswa njengengxabano yomsebenzi we-SUM.

Kwimeko yeempahla ezinamanzi, i-Excel ayivumeli ukuvula ibhokisi yencoko yencoko yomsebenzi ukuba ufake iingxoxo zayo.

Umsebenzi we-INDIRECT, ngoko, kufuneka ungenwe ngesandla kumgca weNombolo1 yebhokisi yencoko yencoko ye-SUM.

1. Kwibhokisi yencoko, nqakraza kumgca weNombolo1
2. Faka lo msebenzi olandelayo: INDIRECT ("D" & E1 & ": D" & E2)
3. Cofa u-OK ukugqiba umsebenzi kwaye uvale ibhokisi yencoko
4. Inani le- 50 kufuneka livele kwiseli F1 kuba eli lilonke le data ekhoyo kwiiseli D1 ukuya kwi-D4
5. Xa ucofa kwifowuni F1 ifom epheleleyo = SUM (INDIRECT ("D" & E1 & ": D" & E2)) ibonakala kwifom yefom ngasentla kwiphepha lokusebenzela

Ukuphulwa komsebenzi we-INDIRECT

Ukuze udale uluhlu olushukumisayo kwikholam ye D usebenzisa INDIRECT, kufuneka sidibanise ileta D kwimbambano yomsebenzi we-INDIRECT kunye nenani eliqulethwe kwiiseli E1 no-E2.

Oku kufezwe ngolu hlobo lulandelayo:

• i-ampersand ( & ) isetyenziselwa ukuchankanya okanye ukudibanisa kunye nedatha yombhalo (kulo mzekelo ileta D) kunye nenombolo yeself (E1 no-E2)
• ngokuqhubekayo, idatha yombhalo ekhonjiswe ngokubhekiselwe kwiiseli kufuneka ijikelezwe ngamanqaku okucaphulwa kabini ( "" )
• ekugqibeleni, iingongoma zokugqibela zoluhlu luhlukaniswe yikholoni (:)

Ngoko ke, iqalo lokuqala loluhlu luchazwe ngabalinganiswa: "D" & E1 .

Iseti yesibini yabalinganiswa: ": D" & E2 idibanisa ikholoni kunye neyokugqibela. Oku kwenziwa ngenxa yokuba ikholoni ngumlingisi wombhalo kwaye ngoko ke kufuneka kufakwe ngaphakathi kwamanqaku okucaphuna.

I-ampersand yesithathu phakathi iyasetyenziswa ukufaka iingqungquthela ezi zimbini zibe yingxabano enye:

"D" & E1 & ": D" & E2

03 ka 03

Utshintsho olunzulu kwiNqanaba loMsebenzi we-SUM

Ingongoma yeli fomyula ukwenzela ukuba kube lula ukutshintsha uhla olupheleleyo ngumsebenzi we-SUM ngaphandle kokuba uhlele ingxabano yomsebenzi.

Ngokubandakanya umsebenzi we-INDIRECT kwifomu, ukutshintsha amanani kumaseli e-E1 no-E2 kuya kutshintsha uluhlu lweeseli ezifundwa ngu-SUM umsebenzi.

Njengoko kunokubonwa kumfanekiso ongentla, oku kuya kubangela impendulo yefomyula ekhoyo kwifayile yeF1 ishintsha njengoko ithatsha uluhlu olutsha lweedatha.

1. Cofa kwiseli E1
2. Thayipha inombolo 3
3. Cinezela ukhiye wokungena kwikhibhodi
4. Cofa kwi cell E2
5. Thayipha inombolo 6
6. Cinezela ukhiye wokungena kwikhibhodi
7. Impendulo kwiseli yeF1 kufuneka ishintshe ukuya kwi-90 - yiyonke loo manani equlethwe kwiiseli D3 ukuya kwi-D6
8. Uvavanyo oluqhubekayo le fomu ngokutshintsha iziqulatho zeeseli B1 kunye neB2 kuye nawaphi na amanani phakathi kwe-1 no-6

INDIRECT kunye #REF! Iphutha lexabiso

#REF! Ixabiso lephutha liya kubonakala kwiseli F1 ukuba ingxabano yomsebenzi we-INDIRECT:

• ayikho isalathisi eselungileyo yesel
• iqulethe inkcazo yangaphandle kwincwadi yokusebenzela eyahlukileyo kwaye leyo ncwadi yokusebenzela ayivulekanga
• ibhekise kwibala leseli ngaphandle kwemida yomsebenzi (umgca 1,048,576 okanye ikholomu XFD)