Ang Microsoft Excel ay may ilang mga cool na trick at gamit ang SUM at INDIRECT dynamic na hanay ng mga formula ay dalawang paraan lamang upang madaling manipulahin ang data na mayroon ka.
SUM - HINDI DIREKTA Pangkalahatang-ideya ng Formula
Ang paggamit ng INDIRECT function sa Excel formula ay ginagawang madali upang baguhin ang hanay ng mga sanggunian ng cell na ginamit sa formula nang hindi kinakailangang i-edit ang formula mismo.
Ang INDERECT ay maaaring gamitin sa isang bilang ng mga pag-andar na tumatanggap ng cell reference bilang isang argument tulad ng mga OFFSET at SUM function.
Sa huli kaso, gamit ang INDIRECT bilang argumento para sa SUM function ay maaaring lumikha ng isang dynamic na hanay ng mga sanggunian ng cell na ang SUM function pagkatapos ay nagdadagdag up.
HINDI DIREKTA ito sa pamamagitan ng pagtukoy sa data sa mga cell na hindi direkta sa pamamagitan ng isang intermediate na lokasyon.
Halimbawa: SUM - HINDI DIREKTA Formula na ginagamit sa Kabuuang isang Dynamic na Saklaw ng Mga Halaga
Ang halimbawang ito ay batay sa data na ipinapakita sa imahe sa itaas.
Ang SUM - INDERECT formula na nilikha sa pamamagitan ng paggamit ng mga hakbang sa tutorial sa ibaba ay:
= SUM (HINDI DIREKTA ("D" & E1 & ": D" & E2))
Sa pormulang ito, ang argumento ng nested INDIRECT function ay naglalaman ng mga sanggunian sa mga cell E1 at E2. Ang mga numero sa mga selula na iyon, 1 at 4, kapag pinagsama sa natitirang argumento ng INDIRECT, ay bumubuo ng mga sanggunian ng cell D1 at D4.
Bilang isang resulta, ang hanay ng mga numero na kabuuan ng SUM function ay ang data na nakapaloob sa hanay ng mga cell D1 hanggang D4 - na kung saan ay 50.
Sa pagbabago ng mga numero na matatagpuan sa mga cell E1 at E2; gayunpaman, ang saklaw na maaaring mabuo ay madaling mabago.
Ang halimbawang ito ay unang gagamitin ang pormula sa itaas upang buuin ang data sa mga cell D1: D4 at pagkatapos ay palitan ang summed range sa D3: D6 na walang pag-edit ng formula sa cell F1.
01 ng 03
Pagpasok sa Formula - Mga Pagpipilian
Ang mga opsyon para sa pagpasok ng formula ay kasama ang:
- i-type ang formula sa itaas nang direkta sa cell F1 at pagpindot sa Enter key sa keyboard
- pagpasok ng INDIRECT function bilang isang argument gamit ang dialog ng SUM function
Karamihan sa mga function sa Excel ay may isang dialog box, na nagbibigay-daan sa iyo upang ipasok ang bawat isa sa mga argumento ng function sa isang hiwalay na linya nang hindi kinakailangang mag-alala tungkol sa syntax .
Sa kasong ito, ang dialog box ng SUM function ay maaaring gamitin upang gawing simple ang formula sa isang tiyak na lawak. Sapagkat ang INDIRECT function ay nested sa loob ng SUM, ang INDIRECT function at ang mga argumento nito ay dapat pa manipasok nang manu-mano.
Ang mga hakbang sa ibaba ay gumagamit ng SUM dialog box upang ipasok ang formula.
Pagpasok sa Data ng Tutorial
Cell Data D1 - 5 D2 - 10 D3 - 15 D4 - 20 D5 - 25 D6 - 30 E1 - 1 E2 - 4- Ipasok ang sumusunod na data sa mga cell D1 hanggang E2
Pagsisimula ng SUM - HINDI DIREKTA Formula - Pagbubukas ng SUM Function Box sa dialog
- Mag-click sa cell F1 - kung saan ang mga resulta ng halimbawang ito ay ipapakita
- Mag-click sa tab na Formula ng laso menu
- Piliin ang Math & Trig mula sa laso upang buksan ang drop down na listahan ng function
- Mag-click sa SUM sa listahan upang buksan ang dialog box ng function
02 ng 03
Pagpasok sa INDIRECT Function - Mag-click sa Tingnan ang Mas malaking Larawan
Ang INDERECT formula ay kailangang ipasok bilang argument para sa SUM function.
Sa kaso ng mga nested function, hindi pinapahintulutan ng Excel na buksan ang dialog box ng pangalawang function upang ipasok ang mga argumento nito.
Samakatuwid, ang INDIRECT function ay kailangang maipasok nang manu-mano sa linya ng Number1 ng dialog box ng SUM Function.
- Sa dialog box, mag-click sa linya ng Number1
- Ipasok ang sumusunod na INDIRECT function: INDERECT ("D" & E1 & ": D" & E2)
- I-click ang OK upang makumpleto ang pag-andar at isara ang dialog box
- Ang numero 50 ay dapat na lumitaw sa cell F1 dahil ito ay ang kabuuang para sa data na matatagpuan sa mga cell D1 hanggang D4
- Kapag nag-click ka sa cell F1 ang kumpletong formula = SUM (INDIRECT ("D" & E1 & ": D" & E2)) ay lilitaw sa formula bar sa itaas ng worksheet
Pag-alis ng Hindi Gagaling na Function
Upang lumikha ng isang dynamic na saklaw sa haligi D gamit ang INDIRECT, dapat naming pagsamahin ang titik D sa argumento ng INDIRECT function na may mga numero na nasa mga cell E1 at E2.
Ito ay nagagawa ng mga sumusunod:
- Ang ampersand ( & ) ay ginagamit upang pagsamahin o pagsama-sama ang data ng teksto (sa kasong ito ang letra D) na may reference sa cell (E1 at E2)
- Dagdag pa, ang data ng teksto na nauugnay sa mga reference sa cell ay dapat na napapalibutan ng double quotation marks ( "" )
- Panghuli, ang mga dulo ng hanay ng hanay ay pinaghihiwalay ng colon (:)
Samakatuwid, ang panimulang punto ng hanay ay tinukoy ng mga character: "D" & E1 .
Ang pangalawang hanay ng mga character: ": D" & E2 ay pinagsasama ang colon na may dulo point. Ginagawa ito dahil ang colon ay isang tekstong karakter at, samakatuwid, ay dapat kasama sa loob ng mga panipi.
Ang ikatlong ampersand sa gitna ay ginagamit upang pagsamahin ang dalawang bahagi sa isang argumento :
"D" & E1 & ": D" & E203 ng 03
Dynamic na Pagbabago sa Saklaw ng SUM Function
Ang buong punto ng pormula na ito ay upang gawing madali upang baguhin ang saklaw ng totaled ng SUM function nang hindi kinakailangang i-edit ang argumento ng function.
Sa pamamagitan ng pagsasama ng INDIRECT function sa formula, ang pagpapalit ng mga numero sa mga cell E1 at E2 ay magbabago sa hanay ng mga cell na nabasa ng SUM function.
Tulad ng makikita sa larawan sa itaas, ito rin ay nagreresulta sa sagot ng formula na matatagpuan sa pagbabago ng cell F1 habang sumasama ang bagong hanay ng data.
- Mag-click sa cell E1
- I-type ang numero 3
- Pindutin ang Enter key sa keyboard
- Mag-click sa cell E2
- I-type ang numero 6
- Pindutin ang Enter key sa keyboard
- Ang sagot sa cell F1 ay dapat magbago sa 90 - kung saan ay ang kabuuan ng mga numero na nasa mga cell D3 hanggang D6
- Karagdagang pagsubok sa formula sa pamamagitan ng pagbabago ng mga nilalaman ng mga cell B1 at B2 sa anumang mga numero sa pagitan ng 1 at 6
HINDI DIREKTA at #REF! Halaga ng Error
Ang #REF! ang halaga ng error ay lilitaw sa cell F1 kung ang argumento ng INDIRECT function:
- ay hindi isang wastong reference ng cell
- ay naglalaman ng isang panlabas na reference sa isang iba't ibang mga workbook at ang workbook ay hindi bukas
- ay tumutukoy sa isang hanay ng cell sa labas ng mga limitasyon ng worksheet (hilera 1,048,576 o haligi XFD)