I-Microsoft Excel inezinye izindlela ezipholile futhi isebenzisa ama-formula we-SUM kanye ne-INDIRECT amahlaya ashukumisayo nje kuphela izindlela ezimbili zokusebenzisa kalula idatha onayo.
I-SUM - INDIRECT Ukwaziswa kwefomula
Ukusebenzisa umsebenzi we- INDIRECT emafomulini we-Excel kwenza kube lula ukushintsha ububanzi bezinkomba ezisetshenziselwa ifomula ngaphandle kokuthi uhlele ifomula ngokwayo.
I-INDIRECT ingasetshenziswa ngemininingwana yemisebenzi eyamukela isithenjwa seseli njengengxabano njengemisebenzi ye- OffSET ne-SUM.
Esikhathini esilandelayo, ukusebenzisa i-INDIRECT njengokungqubuzana komsebenzi we-SUM kungakha ububanzi obunamandla besithenjwa sefoni ukuthi umsebenzi we-SUM uyanezela.
INDIRECT lokhu lokhu ngokubhekisela kwedatha kumaseli ngokuqondile ngokusebenzisa indawo ephakathi.
Isibonelo: I-SUM - I-FORIRECT ifomula isetshenziselwa ukuqoqa inani leDynamic Range of Values
Lesi sibonelo sisekelwe kudatha eboniswe esithombeni ngenhla.
Ifomula ye- SUM - INDIRECT edalwe ngokusebenzisa izinyathelo zokufundisa ngezansi ngu:
= SUM (INDIRECT ("D" & E1 & ": D" & E2))
Kule fomula, impikiswano yomsebenzi we-INDIRECT enezinyosi iqukethe izinkomba kumaseli E1 no-E2. Izinombolo kulawo maseli, 1 no-4, uma zihlangene nazo zonke ezinye izingxabano ze-INDIRECT, faka izinkomba ze-cell D1 no-D4.
Ngenxa yalokho, ububanzi bezinombolo ezifakwe umsebenzi we- SUM yidatha equkethwe uhla lwamaseli D1 kuya ku-D4 - okungama-50.
Ngokushintsha izinombolo ezikhona kumaseli E1 no-E2; Kodwa-ke, ububanzi obuzobe bukhona bungashintshwa kalula.
Lesi sibonelo sizoqala ukusebenzisa ifomula elingenhla ukuze uqoqe idatha kumaseli D1: D4 bese ushintsha ibanga elifingqwe ku-D3: D6 ngaphandle kokuhlela ifomu ku-cell F1.
01 ka-03
Ukufaka i-Formula - Izinketho
Izinketho zokufaka ifomu zibandakanya:
- ukuthayipha ifomu elingenhla ngqo esitokisini F1 bese ucindezela ukhiye wokungena kwikhibhodi
- ukungena emsebenzini we-INDIRECT njengengxabano usebenzisa ibhokisi lengxoxo lomsebenzi we-SUM
Imisebenzi eminingi ku-Excel inebhokisi lengxoxo, elikuvumela ukuba ufake izingxabano zomsebenzi ngamunye emgqeni ohlukile ngaphandle kokukhathazeka nge- syntax .
Kulesi simo, ibhokisi lengxoxo lomsebenzi we-SUM lingasetshenziswa ukwenza lula ifomula ngandlela-thile. Ngenxa yokuthi umsebenzi we-INDIRECT uyadliwa ngaphakathi kwe-SUM, Umsebenzi we-INDIRECT kanye neziphakamiso zawo kufanele kusengenwe ngesandla.
Izinyathelo ezingezansi zisebenzisa ibhokisi lebhokisi le-SUM ukuze ufake ifomula.
Ukufaka idatha yokufundisa
I-Cell Data D1 - 5 D2 - 10 D3 - 15 D4 - 20 D5 - 25 D6 - 30 E1 - 1 E2 - 4- Faka idatha elandelayo kumaseli D1 kuya ku-E2
Ukuqala ifomula ye-SUM - INDIRECT - Ukuvula ibhokisi le-Function SUM
- Chofoza kuseli F1 - yilapho imiphumela yalesi sibonelo izoboniswa khona
- Chofoza kuthebhu yefomula yemenyu ye- ribbon
- Khetha i- Math & Trig kusuka kubhoni ukuze uvule uhlu lwehla lokusebenza
- Chofoza ku- SUM ohlwini ukuze uvule ibhokisi lengxoxo lomsebenzi
02 ka-03
Ukufaka umsebenzi we-INDIRECT - Chofoza ukuze Ubuke Isithombe Esikhulu
I-formula ye-INDIRECT idinga ukungena njengengxabano yomsebenzi we-SUM.
Uma kwenzeka imisebenzi emihle, i-Excel ayivumeli ukuvula ibhokisi lengxoxo lomsebenzi wesibili ukufaka izingxabano zalo.
Umsebenzi we-INDIRECT, ngakho-ke, kufanele ungene ngesandla ohlwini lweNombolo1 yebhokisi lengxoxo ye-SUM Function.
- Ebhokisini lengxoxo, chofoza kulayini leNombolo1
- Faka umsebenzi olandelayo we-INDIRECT: INDIRECT ("D" & E1 & ": D" & E2)
- Chofoza OK ukuqedela umsebenzi bese uvala ibhokisi lengxoxo
- Inombolo engu- 50 kufanele ivele esitokisini F1 ngoba lokhu kungumumo wedatha etholakala kumaseli D1 kuya ku-D4
- Uma uchofoza ku-cell F1 ifomula ephelele = SUM (INDIRECT ("D" & E1 & ": D" & E2)) ibonakala kwibha yefomula ngaphezulu kwephepha lokusebenzela
Ukudiliza umsebenzi we-INDIRECT
Ukuze udale ibanga eliguqukayo kukholomu D usebenzisa i-INDIRECT, kumele sihlanganise incwadi D enkulumweni yomsebenzi we-INDIRECT ngezinombolo eziqukethwe kumaseli E1 no-E2.
Lokhu kufezwa okulandelayo:
- i-ampersand ( & ) isetshenziselwa ukuncintisana noma ukujoyina ndawonye idatha yombhalo (kulokhu incwadi D) nge-reference reference (E1 no-E2)
- ngokuqhubekayo, idatha yombhalo ekhonjiswa ngezithenjwa zeseli kufanele ifakwe ngamamaki okucaphuna kabili ( "" )
- Okokugcina, amaphuzu okugcina ebangeni ahlukaniswe ikholoni (:)
Ngakho-ke, iphuzu lokuqala lobubanzi lichazwe ngabalingiswa: "D" & E1 .
Isethi yesibili yabalingiswa: ": D" & E2 ihlanganisa ikholoni nephuzu lokuphela. Lokhu kwenziwa ngoba ikholoni ngumlingisi wombhalo futhi, ngakho-ke, kufanele ifakwe phakathi kwamamaki okucaphuna.
I-ampersand yesithathu maphakathi isetshenziselwa ukufaka izingxenye ezimbili izingxabano eyodwa:
"D" & E1 & ": D" & E203 ka 03
Ukushintsha ngokuphawulekayo uRange lomsebenzi we-SUM
Iphuzu eliphelele laleli fomula ukwenza kube lula ukuguqula ububanzi obuthathwe umsebenzi we-SUM ngaphandle kokuthi uhlele impikiswano yomsebenzi.
Ngokufaka umsebenzi we-INDIRECT kufomula, ukushintsha izinombolo kumaseli E1 no-E2 kuzoguqula ububanzi bamaseli afundwa umsebenzi we-SUM.
Njengoba kungabonakala esithombeni esingenhla, lokhu kuholela ekuphenduleni kwefomula esesitokisini F1 esishintshayo njengoba ihlanganisa uhla olusha lwedatha.
- Chofoza kuseli E1
- Thayipha inombolo 3
- Cindezela ukhiye wokungena kwikhibhodi
- Chofoza kumakhalekhukhwini E2
- Thayipha inombolo 6
- Cindezela ukhiye wokungena kwikhibhodi
- Impendulo ku-cell F1 kufanele ishintshe ibe ngu-90 - yiyonke inani lezinombolo eziqukethwe kumaseli D3 kuya ku-D6
- Ukuhlolwa okuqhubekayo ifomula ngokushintsha okuqukethwe kwamaseli B1 no-B2 kunoma yiziphi izinombolo eziphakathi kuka-1 no-6
INDIRECT kanye #REF! Inani lephutha
I-#REF! Inani lephutha lizovela kuseli F1 uma ingxabano yomsebenzi we-INDIRECT:
- akuyona inkomba yeselula evumelekile
- iqukethe ukubhekisela kwangaphandle kwencwadi ehlukile futhi leyo ncwadi yokusebenzela ayivulekile
- ibhekisela ebangeni leseli ngaphandle kwemingcele yomsebenzi (umugqa we-1,048,576 noma ikholomu XFD)