Excel SUM ndi INDIRECT Mphamvu ya Mpangidwe wa Mphamvu

Microsoft Excel ili ndi zizoloŵezi zozizira ndikugwiritsa ntchito SUM ndi INDIRECT mitundu yosiyanasiyana ya machitidwe ndi njira ziwiri zokha kuti muzigwiritsa ntchito mosavuta deta yomwe muli nayo.

SUM - INDIRECT Makhalidwe mwachidule

Kugwiritsa ntchito INDIRECT ntchito mu Excel mafomu kumapangitsa kukhala kosavuta kusinthanitsa mafotokozedwe a selo ogwiritsidwa ntchito mu njirayi popanda kusintha ndondomeko yokha.

KUKHALA kungagwiritsidwe ntchito ndi ntchito zingapo zomwe zimalandila selolo monga magulu monga OFFSET ndi SUM ntchito.

Pachifukwa chotsatirachi, kugwiritsa ntchito INDIRECT monga kukangana kwa ntchito ya SUM kungapangitse mafotokozedwe amphamvu a maselo omwe ntchito ya SUM imawonjezera.

ZOKHUDZA izi zimatanthawuza deta m'maselo mwachindunji kupyolera mu malo apakati.

Chitsanzo: SUM - INDIRECT Mpangidwe umagwiritsidwa ntchito ku Total Dynamic Range of Values

Chitsanzo ichi chazikidwa pazithunzi zomwe zawonetsedwa pa chithunzi pamwambapa.

SUM - INDIRECT njira yomwe imagwiritsidwa ntchito pogwiritsa ntchito ndondomeko zotsatirazi ndi:

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

Mu chidule ichi, mtsutso wa INDIRECT wothandizira uli ndi maumboni ku maselo E1 ndi E2. Nambala mu maselo amenewa, 1 ndi 4, pamodzi ndi zina zonse za INDIRECT, pangani mafotokozedwe a D1 ndi D4.

Chotsatira chake, chiwerengero cha mawerengedwe omwe ali ndi ntchito ya SUM ndi deta yomwe ili m'maselo ambiri D1 mpaka D4 - omwe ali 50.

Mwa kusintha manambala omwe ali m'maselo E1 ndi E2; Komabe, mautanidwe omwe angakhale nawo angasinthe mosavuta.

Chitsanzo ichi choyamba chigwiritse ntchito ndondomeko yomwe ili pamwambapa kuti iwononge ma data mu maselo D1: D4 ndikusintha zomwe zafotokozedwa ku D3: D6 popanda kusintha njirayi mu selo F1.

01 a 03

Kulowa Mndandanda - Zosankha

Zosankha zobwera muyesoli ndi izi:

• kulemba ndondomekoyi pamwambapa mu selo F1 ndikukankhira pakani pa Enter
• kulowa mu INDIRECT ntchito ngati mkangano pogwiritsa ntchito bokosi la ntchito ya SUM

Ntchito zambiri mu Excel zili ndi bokosi la zokambirana, zomwe zimakulowetsani kuti mulowetse mndandanda uliwonse wa ntchito pa mzere wosiyana popanda kudandaula za syntax .

Pachifukwa ichi, bokosi la ntchito la SUM lingagwiritsidwe ntchito kupanga mosavuta njirayi. Chifukwa INDIRECT ntchito ikukhala mkati mwa SUM, Ntchito INDIRECT ndi zifukwa zake ziyenera kukhala zolembedwera.

Masitepe otsatirawa agwiritse ntchito bokosi la bokosi la SUM kuti alowemo.

Kulowa Datorial Data

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

1. Lowetsani deta zotsatirazi mu maselo D1 mpaka E2

Kuyambira SUM - INDIRECT Makhalidwe - Kutsegula Bokosi la Kugwiritsa Ntchito la SUM

1. Dinani pa selo F1 - izi ndi zomwe zotsatira za chitsanzo ichi zidzawonetsedwa
2. Dinani pa Fomu tab ya menyu yowonjezera
3. Sankhani Math & Trig kuchokera ku riboni kuti mutsegule ntchito yolemba pansi
4. Dinani pa SUM m'ndandanda kuti mutsegule dialog box

02 a 03

Kulowa Ntchito YOPHUNZITSIRA - Dinani Kuti Muwone Zithunzi Zazikulu

Cholinga cha INDIRECT chiyenera kulembedwa ngati kukangana kwa ntchito ya SUM.

Pankhani ya ntchito zachisawawa, Excel sitingatsegule bokosi lachiwiri la ntchitoyi kuti tilowetse zifukwa zake.

Cholinga cha INDIRECT, chotero, chiyenera kulowetsedwa mu Number1 mzere wa bokosi la SUM Function dialog.

1. Mu bokosi la bokosi, dinani pa Number1 mzere
2. Lowani ntchito yotsatira INDIRECT: INDIRECT ("D" & E1 & ": D" & E2)
3. Dinani OK kuti mutsirize ntchitoyi ndi kutseka bokosi la bokosi
4. Chiwerengero cha 50 chiyenera kuoneka mu selo F1 chifukwa ichi ndi chiwerengero cha deta yomwe ili mu maselo D1 mpaka D4
5. Mukasindikiza pa selo F1 ndondomeko yonse = SUM (INDIRECT ("D" & E1 & ": D" & E2)) ikupezeka pa bar barolomu pamwamba pa tsamba

Kuphwanya Ntchito INDIRECT

Kuti tipeze kukula kwa chigawo D pogwiritsa ntchito INDIRECT, tiyenera kuphatikiza kalata D mu ndondomeko ya ntchito ya INDIRECT ndi chiwerengero chomwe chili mu maselo E1 ndi E2.

Izi zikukwaniritsidwa ndi zotsatirazi:

• ampersand ( & ) amagwiritsidwa ntchito kubwereza kapena kujowina pamodzi ndondomeko yolemba (pakalata iyi D) ndi selolo (E1 ndi E2)
• Zoonjezerapo, deta yomwe ikuwerengedwera ndi mafotokozedwe a maselo ayenera kuzunguliridwa ndi zizindikiro zamagwero awiri ( "" )
• Potsirizira pake, mapeto a mapetowa akulekanitsidwa ndi colon (:)

Choncho, chiyambi choyambiracho chikufotokozedwa ndi olemba: "D" & E1 .

Mzere wachiwiri wa malemba: ": D" & E2 akuphatikiza colon ndi mapeto. Izi zachitika chifukwa colon ndi munthu wolemba malemba ndipo, chifukwa chake, ayenera kuphatikizidwa mkati mwa zizindikiro za ndemanga.

Wachitatu ampersand pakati amagwiritsanso ntchito zigawo ziwiri kukhala kutsutsana kumodzi:

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

03 a 03

Kusintha Kwambiri Mtunda wa Ntchito ya SUM

Mfundo yonse ya chidule ichi ndikuti zikhale zosavuta kusinthira zofanana ndi ntchito ya SUM popanda kusintha ndondomeko ya ntchitoyo.

Pogwiritsa ntchito INDIRECT ntchitoyi, kusintha manambala m'maselo E1 ndi E2 kudzasintha maselo osiyanasiyana omwe amawerengedwa ndi ntchito SUM.

Monga momwe tingawonere pa chithunzi pamwambapa, izi zimathandizanso yankho la funsolo lomwe lili mu selo F1 likusintha pamene likuwerengera deta yatsopano.

1. Dinani pa selo E1
2. Lembani nambala 3
3. Dinani ku key lolowamo pa khibhodi
4. Dinani pa selo E2
5. Lembani nambala 6
6. Dinani ku key lolowamo pa khibhodi
7. Yankho lake mu selo F1 liyenera kusintha mpaka 90 - ndi chiwerengero cha chiwerengero chomwe chili mu maselo D3 mpaka D6
8. Yesetsani kuyesa njirayi mwa kusintha zomwe zili m'maselo B1 ndi B2 ku nambala iliyonse pakati pa 1 ndi 6

INDIRECT ndi #REF! Kulakwitsa kwa Mtengo

The #REF! Phindu lachinyengo lidzawonekera mu selo F1 ngati mkangano wa ntchito INDIRECT:

• silololedwa molondola
• lili ndi kutanthauzira kwina kwa bukhu losiyana ndi bukhu la ntchito silo lotseguka
• limatanthawuza selo lili kunja kwa malire a worksheet (mzere wa 1,048,576 kapena chigawo XFD)