Excel SUM uye INDIRECT Dynamic Range Formula

Microsoft Excel ine dzimwe nzira dzakanaka uye kushandisa SUM uye INDIRECT mafomu emhando dzakasiyana ndiyo nzira mbiri dzekushandisa nyore nyore data yaunayo.

SUM - INDIRECT Formula Overview

Kushandisa INDIRECT basa muExpcel mazano kunoita kuti zvive nyore kuchinja mararamiro esero rinoshandiswa mumutsetse kunze kwekushandura iyo fomu pachayo.

INDIRECT inogona kushandiswa nehuwandu hwemabasa anogamuchira sero rekuita semakakatanwa akadai se OFFSET uye SUM mabasa.

Muchikamu chekupedzisira, kushandisa INDIRECT semhaka yebasa reSUM kunogona kusimbisa huwandu hwemasero emaferensi ayo basa reSUM rinowedzera.

INDIRECT inoita izvi kuburikidza nemashoko ari mumasero asinganzwisisiki kuburikidza nenzvimbo yapakati.

Muenzaniso: SUM - INDIRECT Romufaira rinoshandiswa kuZvose Dynamic Range of Values

Uyu muenzaniso unobva pane data inoratidzwa mumufananidzo pamusoro apa.

I-SUM - INDIRECT yakasikwa yakagadzirwa nekushandisa zviito zvidzidzo pasi apa ndeyi:

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

Muchirevo ichi, nestedest INDIRECT's argument work contains references to cells E1 and E2. Nhamba dziri mumasero aya, 1 ne4, kana akabatanidzwa nezvose zvinokonzerwa neIndIRECT, shandisai zvinyorwa D1 uye D4.

Somugumisiro, huwandu hwenhamba hwakawanda nehuwandu hweSUM ndiyo data iri mumakamuri emasero D1 kusvika D4 - iyo 50.

Nokuchinja nhamba dziri mumasero E1 neE2; zvisinei, mararamiro aunogona kuenzaniswa anogona kuchinja nyore nyore.

Uyu muenzaniso unotanga kushandisa shanduro iri pamusoro apa kuunganidza data mumasero D1: D4 uye ochinja mararamiro akawedzerwa kusvika kuD3: D6 pasina kugadzirisa fomu muchitokisi F1.

01 of 03

Kupinda Mufomu - Zvimwe

Zvisarudzo zvekupinda murasi yacho zvinosanganisira:

• kunyora urongwa huri pamusoro apa zvakananga muchitokisi F1 uye kuvhara muIno yokupinda pane keyboard
• kupinda mu INDIRECT basa senharo uchishandisa SUM function's dialog box

Zvose zvinoshanda muEexcel zvine bhokisi rekubhokisi, rinokubvumira kupinda maitiro ese emabasa pane rimwe divi risina kurega kushushikana pamusoro pemashiripiti .

Muchiitiko ichi, sUM function's dialog box inogona kushandiswa kugadzirisa fomu kune imwe nzvimbo. Nemhaka yokuti INDIRECT basa iri kuva nenyuchi mukati meSUM, IYI INDIRECT basa uye nharo dzaro dzinofanira kunge dzichipinda mumunhu.

Nhanho dziri pasi dzinoshandisa SUM dialog box kuti uise maitiro.

Kupinda Tutorial Data

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

1. Isa data inotevera inhengo D1 kusvika E2

Kutanga SUM - INDIRECT Formula - Kuzarura Bhokisi reDhiyabhorosi reSection Dialog

1. Dzvanya pane cell F1 - iyi ndiyo iyo migumisiro yemuenzaniso uyu icharatidzwa
2. Dzvanya paMafomu yebhu yebhoni yebhoni
3. Sarudza Math & Trig kubva kubhoni kuti uzarure basa rakaderera pasi
4. Dzvanya paSUM murugwaro kuti uzarure bhokisi rebasa rebasa

02 of 03

Kupinda Basa re INDIRECT - Dzvanya kuti Uone Mifananidzo Yakakura

IYI INDIRECT inoshandiswa inofanira kuiswa sepikicha yebasa reSUM.

Panyaya yebasa rakasvibiswa, Excel haibvumiri kutsemura bhokisi rebato rebasa rekuita kuti riise mazano aro.

Iyo INDIRECT basa, saka, inofanira kuiswa manyore muNhamba1 yeropa reSUM Function's box box.

1. Mubhokisi rebhokisi, tora paNhamba1
2. Pinda zvinotevera INDIRECT basa: INDIRECT ("D" & E1 & ": D" & E2)
3. Dzvanya OK kuti upedze basa uye uvhare bhokisi rebhokisi
4. Nhamba 50 inofanira kuonekwa muchitokisi F1 sezvo ichi ndicho chizere che data iri mumasero D1 kusvika kuD4
5. Paunenge uchinyora pane cell F1 yakakwana formula = SUM (INDIRECT ("D" & E1 & ": D" & E2)) inowanikwa mubhairi yepamusoro pane pepa rekushanda

Kuputsa pasi INDIRECT Basa

Kuti tive nehuwandu hwemasimba muchitsauko D uchishandisa INDIRECT, tinofanira kusanganisira tsamba D mune INDIRECT nharo yebasa pamwe nenhamba dziri mumasero E1 neE2.

Izvi zvinoitwa neizvi:

• the ampersand ( & ) inoshandiswa kuratidzira kana kubatanidza pamwe chete mashoko ekunyora (munyaya ino tsamba D) ine cell reference (E1 neE2)
• uyezve, deta dhesi iyo iri kubvumiranwa nesero zvinyorwa zvinofanira kunge yakakombwa nemazita maviri ezvinyorwa ( "" )
• pakupedzisira, magumo ekupedzisira emutsetse akaparadzaniswa nekoloni (:)

Nokudaro, nheyo yokutanga yemutsetse inotsanangurwa nemunhu: "D" & E1 .

Yechipiri yezvinyorwa: ": D" & E2 inobatanidza chikoni nechokuguma. Izvi zvinoitwa nokuti colon ishoko runyoro uye, saka, rinofanira kuiswa mukati memashoko ekudzokorora.

Yechitatu ampersand pakati inoshandiswa kuratidzira zvikamu zviviri mubvunzo mumwe:

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

03 of 03

Kugadzirisa zvakasimba Rum Function Range

Izvo zvose zvinoreva chirevo ichi ndechokuti zvive nyore kuchinja mararamiro akaenzana nebasa reSUM pasinei nekugadzirisa mhizha yebasa.

Nokusanganisira iyo INDIRECT basa mumutsetse, kuchinja nhamba mumasere E1 neE2 kuchachinja huwandu hwemasero akaverengwa neSUM basa.

Sezvo zvinogona kuonekwa mumufananidzo wepamusoro, izvi zvinoguma nemhinduro yemhinduro iri muchitokisi F1 ichichinja sezvo iyo yose inotarisa huwandu hwemashoko.

1. Dzvanya pane cell E1
2. Tora nhamba 3
3. Dhinda pikicha inopinda mubhokisi
4. Dzvanya pane cell E2
5. Tora nhamba 6
6. Dhinda pikicha inopinda mubhokisi
7. Mhinduro yacho muchitokisi F1 inofanira kuchinja kusvika kune 90 - iyo ndiyo nhamba yemhando dziri mumasero D3 kusvika D6
8. Chimwe chiedzo chemuumbiro wekushandura zviri mukati memasero B1 neB2 kune nhamba ipi neipi pakati pe1 ne6

INDIRECT uye #REF! Error Value

The #REF! kukanganisa kwenhamba kuchaonekwa muchitokisi F1 kana INDIRECT inoshanda yenyaya:

• haisi yakakodzera sefeter reference
• rine chinyorwa chekunze kune rimwe bhuku rebhuku uye kuti bhuku rebhuku harisi rakazaruka
• rinoreva sero iri kunze kwemasheti ekushanda (rugwaro 1.048,576 kana korome XFD)