Ang Microsoft Excel adunay pipila ka mga cool tricks ug ang paggamit sa SUM ug INDIRECT dinamikong hanay nga mga formula mao ang duha lang ka mga pamaagi aron dali nga maimpluwensyahan ang datos nga anaa kanimo.
SUM - TINUOD NGA TANANG HINUNGDANON sa Formula
Pinaagi sa paggamit sa INDIRECT function sa Excel nga mga pormula, dali nga mausab ang lainlaing mga reference sa cell nga gigamit sa pormula nga dili kinahanglan nga usbon ang pormula mismo.
INDIRECT mahimong gamiton uban ang daghang mga gimbuhaton nga modawat sa usa ka cell reference ingon nga usa ka argumento sama sa mga OFFSET ug SUM functions.
Sa ulahing kaso, ang paggamit sa INDIRECT isip argumento sa SUM function makahimo sa usa ka dinamikong lainlaing mga reference sa cell nga ang SUM function dayon nagdugang.
KINAHANGLAN ang pagbuhat niini pinaagi sa pagtumong sa mga datos sa mga selula dili diretso pinaagi sa usa ka tunga nga dapit.
Pananglitan: SUM - dili direkta nga Formula nga gigamit sa Kabilugan nga Dynamic Range of Values
Kini nga panig-ingnan gibase sa datos nga gipakita sa hulagway sa ibabaw.
Ang SUM - dili direkta nga pormula nga gimugna pinaagi sa paggamit sa mga lakang sa pagtudlo sa ubos mao ang:
= SUM (dili tinuod ("D" & E1 & ": D" & E2))
Niini nga pormula, ang argumento sa nested INDIRECT naglangkob sa mga paghisgot sa mga cell E1 ug E2. Ang mga numero sa maong mga selula, 1 ug 4, kung gikombinar uban ang nahabilin nga argumento sa INDIRECT, paghimo sa mga reference sa cell nga D1 ug D4.
Tungod niini, ang gidaghanon sa mga numero nga nalambigit sa SUM function mao ang datos nga naa sa hanay sa mga cell D1 to D4 - nga 50.
Pinaagi sa pag-usab sa mga numero nga nahimutang sa mga selula E1 ug E2; Bisan pa niana, ang gidak-on nga mahimong hingpit mahimong dali nga mausab.
Kini nga pananglitan una nga mogamit sa pormula sa ibabaw sa total nga mga datos sa mga cell D1: D4 ug dayon i-usab ang summed range sa D3: D6 nga dili mausab ang pormula sa cell F1.
01 sa 03
Pagsulod sa Pormula - Mga Pagpili
Ang mga kapilian sa pagsulod sa pormula naglakip sa:
- pag-type sa pormula sa ibabaw direkta ngadto sa cell F1 ug pagpa-press sa Enter key sa keyboard
- pagsulod sa INDIRECT function isip usa ka argumento gamit ang kahon sa dialog sa SUM function
Kadaghanan sa mga function sa Excel adunay usa ka dialog box, nga nagtugot kanimo nga mosulod sa matag usa sa mga argumento sa function sa usa ka linain nga linya nga dili mabalaka mahitungod sa syntax .
Sa kini nga kaso, ang dialog box sa SUM nga gigamit aron mapayano ang pormula sa usa ka sukod. Tungod kay ang INDIRECT nga kalihokan gisudlan sa sulod sa SUM, Ang INDIRECT function ug ang mga argumento niini kinahanglan pa nga ipasulod sa us aka paagi.
Ang mga lakang sa ubos mogamit sa SUM dialog box aron makasulod sa pormula.
Pagsulod sa Giya sa Giya
Cell Data D1 - 5 D2 - 10 D3 - 15 D4 - 20 D5 - 25 D6 - 30 E1 - 1 E2 - 4- Pagsulod sa mosunod nga data ngadto sa mga selula D1 ngadto sa E2
Pagsugod sa SUM - INDERECT Formula - Pagbukas sa SUM Function Box Dialog
- Pag-klik sa cell F1 - kini diin ang mga resulta niini nga pananglitan ipasundayag
- I-klik ang tabula sa Formula sa ribbon menu
- Pilia ang Math & Trig gikan sa laso aron maablihan ang lista sa function nga drop down
- I-klik ang SUM sa lista aron maablihan ang kahon sa dialogo sa function
02 sa 03
Pagsulod sa INDIRECT Function - I-klik sa View Larger Image
Ang INDIRECT nga pormula kinahanglang isulod isip argumento sa SUM function.
Sa kaso sa nested nga mga gimbuhaton, ang Excel dili nagtugot sa pag-abli sa kahon sa dialogo sa ikaduha nga gamit aron makasulod sa mga argumento niini.
Busa, ang INDIRECT function kinahanglang ilakip sa linya sa Number1 sa kahon sa dialogo sa SUM Function.
- Sa dialog box, i-klik ang linya sa Number1
- Pagsulod sa mosunod nga function nga INDIRECT: INDIRECT ("D" & E1 & ": D" & E2)
- I-klik ang OK aron kompletuhon ang function ug isira ang dialog box
- Ang numero 50 kinahanglan nga makita sa cell F1 tungod kay kini ang kinatibuk-an alang sa datos nga nahimutang sa mga cell D1 ngadto sa D4
- Sa imong pag-klik sa cell F1 ang kompleto nga pormula = SUM (INDIRECT ("D" & E1 & ": D" & E2)) makita sa pormula nga bar sa ibabaw sa worksheet
Pagbungkag sa TINUOD nga Katungdanan
Aron makahimo sa usa ka dynamic range sa kolum D gamit ang INDIRECT, kinahanglang ipuno ang letra D sa argumento sa INDIRECT function sa mga numero nga anaa sa mga cell E1 ug E2.
Gihimo kini sa mosunod:
- Ang ampersand ( & ) gigamit aron pagdugtong o pagduyog sa dungan nga mga datos sa teksto (sa kini nga kaso ang letra D) nga may reference sa cell (E1 ug E2)
- Dugang pa, ang mga datos sa teksto nga gihiusa uban sa mga reference sa cell kinahanglan nga gilibutan sa mga double quotation marks ( "" )
- Sa kataposan, ang katapusan nga mga punto sa gidak-on gibulag sa colon (:)
Busa, ang gisugdan nga punto sa gidak-ong gihulagway sa mga karakter: "D" & E1 .
Ang ikaduha nga mga hugpong sa mga karakter: ": D" & E2 nagkombinar sa colon sa katapusan nga punto. Gihimo kini tungod kay ang colon usa ka teksto nga karakter ug, busa, kinahanglang ilakip sa sulod sa marka sa mga kinutlo.
Ang ikatulong ampersand sa tunga-tunga gigamit sa pagdugtong sa duha ka mga bahin ngadto sa usa ka argumento :
"D" & E1 & ": D" & E203 sa 03
Dinamikong Pagbag-o sa Sakop sa SUM Function
Ang kinatibuk-ang punto sa kini nga pormula mao ang paghimo nga sayon nga usbon ang gidak-on sa kinatibuk-ang gidumala sa SUM nga walay pag-usab sa argumento sa pag-obra.
Pinaagi sa paglakip sa INDIRECT function sa pormula, ang pagbag-o sa mga numero sa mga selula E1 ug E2 mag-usab sa lain-laing mga selula nga mabasa sa SUM function.
Ingon sa makita sa hulagway sa ibabaw, kini usab moresulta sa tubag sa pormula nga nahimutang sa cell F1 nga nagbag-o tungod kay kini naglangkob sa bag-ong mga datos.
- I-klik ang cell E1
- Isulat ang numero 3
- Mopadayon sa pagsulod sa key sa keyboard
- I-klik ang cell E2
- Isulat ang numero 6
- Mopadayon sa pagsulod sa key sa keyboard
- Ang tubag sa cell F1 kinahanglan nga mag-usab ngadto sa 90 - nga mao ang kinatibuk-an sa mga numero nga anaa sa mga cell D3 ngadto sa D6
- Dugang pagsulay ang pormula pinaagi sa pag-usab sa mga sulod sa mga cell B1 ug B2 sa bisan unsang mga numero tali sa 1 ug 6
INDIRECT ug ang #REF! Sayop nga Bili
Ang #REF! ang sayop nga bili makita sa cell F1 kung ang argumento sa INDIRECT function:
- dili balido nga reference sa cell
- naglangkob sa usa ka eksternal nga paghisgot sa usa ka lainlain nga workbook ug nga ang workbook dili bukas
- nagtumong sa usa ka selula sa gawas sa mga limitasyon sa worksheet (laray 1,048,576 o kolum XFD)