Excel SUM da OFFSET Formula

Yi amfani da SUM da OFFSET don neman samfurori na jeri na bayanai

Idan aikin ɗakunan Excel ɗinku ya ƙunshi lissafi dangane da canjin canji na sel, ta yin amfani da SUM da OFFSET tare a cikin tsarin SUM OFFSET ya sauƙaƙa da aikin kiyaye lissafi har zuwa yau.

Ƙirƙirar Dynamic Range Tare da SUM da OFFSET ayyuka

© Ted Faransanci

Idan kun yi amfani da lissafi na tsawon lokacin da ke ci gaba da sauyawa - kamar yawan tallace-tallace na wata - aiki na OFFSET ya ba ka damar saita tasiri mai dadi wanda ke ci gaba da sauyawa kamar yadda aka ƙara yawan tallan tallace-tallace a kowace rana.

Ta hanyar kanta, aikin SUM zai iya saukewa sau da yawa akan sabbin jinsunan bayanan da aka saka a cikin kewayon.

Ɗaya daga cikin ɓangaren yakan faru lokacin da aka saka bayanai zuwa cikin tantanin halitta inda aikin yanzu yake.

A cikin misalin hoton da ke tare da wannan labarin, an ƙara sababbin lambobin tallace-tallace a kowace rana zuwa kasan lissafin, wanda ya tilasta yawancin da zai ci gaba da sauke tantanin halitta ɗaya a duk lokacin da aka kara sabbin bayanai.

Idan ana amfani da aikin SUM a kan kansa don tattara bayanai, zai zama wajibi ne a sake gyara sassan kwayoyin da aka yi amfani da su a matsayin ƙwaƙwalwar aiki a duk lokacin da aka ƙara sabon bayanai.

Ta amfani da SUM da OFFSET suna aiki tare, duk da haka, iyakar da ta zama ta zama tsauri. A wasu kalmomi, yana canzawa don saukar da sabon kwayoyin bayanai. Bugu da žarin sababbin kwayoyin bayanai bazai haifar da matsala ba saboda kewayon ya ci gaba da daidaitawa kamar yadda aka kara sabbin sababbin kwayoyin.

Hadin rubutu da jayayya

Duba hoto da ke bin wannan labarin don bi tare da wannan koyawa.

A cikin wannan tsari, aikin SUM yana amfani da shi don yada jimlar bayanai da aka bayar a matsayin hujja. Maganin farko ga wannan kewayawa na da ƙayyadaddun kuma an gano shi a matsayin ƙirar salula zuwa lambar farko da za a haɗa ta da tsari.

Ana amfani da aikin OFFSET a cikin aikin SUM kuma ana amfani dasu don ƙirƙirar ƙarshen bayanan bayanan da aka samo ta hanyar dabarar. An cika wannan ta hanyar saita ƙarshen iyakar zuwa ɗayan ɗaya a sama da wurin da aka yi.

Ma'anar ta dabarar:

= SUM (Range Fara: OFFSET (Magana, Rows, Cols))

Fara Range - (da ake buƙata) wurin farawa don kewayon kwayoyin da za a haɗa su ta hanyar SUM. A cikin misali misali, wannan cell B2 ne.

Magana - (da ake buƙata) tantanin halitta da aka yi amfani da ita don ƙididdige iyakar kewayon yana da yawancin layuka da ginshiƙai. A cikin misalin hoto, hujja ta Magana shine tantanin tantanin halitta saboda tsarin da kanta tun lokacin da muke son layin don ƙare ɗaya cell a sama da tsari.

Rumuka - (da ake buƙata) yawan layuka a sama ko a ƙasa da amsar da aka yi amfani da su a lissafin ƙaddamar. Wannan darajar zai iya zama tabbatacce, korau, ko saita zuwa kome.

Idan yanayin ƙayyadaddun yana sama da hujjar Magana , wannan darajar ba ta da kyau. Idan akwai ƙasa, Shawarar layi na da kyau. Idan an biya farashin a jere guda ɗaya, wannan gardama ba kome bane. A cikin wannan misali, azabar ta fara tayi daya a sama da batun Magana , don haka darajar wannan hujjar ta kasance mummunan (-1).

Maɓuɓɓuka - (da ake buƙata) yawan ginshiƙai zuwa hagu ko dama na maganganun da aka yi amfani da shi wajen yin la'akari da biya. Wannan darajar zai iya zama tabbatacce, korau, ko saita zuwa kome

Idan yanayin biya ya kasance a hagu na Tambaya, wannan darajar ba ta da kyau. Idan har dama, Magana Cols tabbatacciya ce. A cikin wannan misali, bayanan da aka tattara a cikin wannan shafi kamar yadda aka yi don haka darajar wannan gardama ba kome ce ba.

Amfani da SUM OFFETET Formula zuwa Kayan Duka Kasuwanci

Wannan misali yana amfani da SUM OFFSET dabara don dawo da jimlar don tallan tallace-tallace yau da kullum da aka jera a shafi na B na takaddun aiki.

Da farko, an shigar da wannan tsari a cikin salula B6 kuma ya tattara bayanan tallace-tallace na kwanaki hudu.

Mataki na gaba shine don motsa SUM OFFSET da aka tsara a jerin jere don samun damar ajiyar kwanan rana na biyar.

Ana kammala wannan ta hanyar sa sabon jigon 6, wanda ke motsa wannan tsari zuwa jere 7.

A sakamakon wannan tafiyewa, Excel ta atomatik sabunta maganin da aka nuna zuwa cell B7 kuma yana ƙara sallar B6 a cikin kewayon ta hanyar dabara.

Shigar da SUM OFFETET Formula

  1. Danna kan b6 B6, wanda shine wurin da za a nuna sakamakon wannan tsari.
  2. Danna maɓallin Formulas na shafin rubutun .
  3. Zabi Math & Trig daga ribbon don buɗe jerin abubuwan da aka sauke.
  4. Danna SUM a cikin jerin don kawo akwatin maganganun aikin.
  5. A cikin akwatin maganganu, danna kan lambar Number1 .
  6. Danna sel B2 don shigar da wannan tantanin halitta a cikin akwatin maganganu. Wannan wuri shi ne mahimmanci na mahimmanci ga wannan tsari;
  7. A cikin akwatin maganganu, danna kan lambar Number2 .
  8. Shigar da aikin OFFSET ɗin nan: OFFSET (B6, -1.0) don samar da mahimmancin ra'ayi don wannan tsari.
  9. Danna Ya yi don kammala aikin kuma rufe akwatin maganganu.

Jimlar $ 5679.15 ya bayyana a tantanin halitta B7.

Lokacin da ka danna kan tantanin halitta B3, cikakken aikin = SUM (B2: OFFSET (B6, -1.0)) ya bayyana a cikin maɓallin tsari a sama da takardun aiki.

Ƙara Bayanan Tallace-tallace na Kashegari

Don ƙara bayanan tallace-tallace na gaba mai zuwa:

  1. Danna-dama a kan jigo na jere don jere 6 don buɗe menu mahallin.
  2. A cikin menu, danna kan Sa don saka sabon jere a cikin takarda.
  3. A sakamakon haka, SUM OFFSET dabarun da ke motsawa zuwa tantanin halitta B7 da jere 6 yanzu ya zama komai.
  4. Danna kan salula A6 .
  5. Shigar da lamba 5 don nuna cewa an shigar da tallace-tallace don rana ta biyar.
  6. Danna kan b6 B6.
  7. Rubuta lambar $ 1458.25 sannan danna maɓallin Shigar da ke keyboard.

Salon B7 na sabuwar sabuwa na $ 7137.40.

Lokacin da ka danna kan tantanin B7, hanyar da aka sabunta = SUM (B2: OFFSET (B7, -1.0) ya bayyana a cikin tsari.

Lura : Ayyukan OFFSET yana da mahimman ra'ayoyi guda biyu: Height da Width, waɗanda aka cire a wannan misali.

Wadannan muhawara za a iya amfani da su don gaya wa aikin OFFSET siffar kayan aiki ta hanyar kasancewa da yawa layuka da yawa da kuma ginshiƙai masu yawa.

Ta hanyar watsar da waɗannan muhawarar, aikin, ta hanyar tsoho, yana amfani da tsawo da nisa na taƙaitaccen Magana a maimakon, wanda, a cikin wannan misali ɗaya ne mai tsawo da ɗaya shafi.