ສູດ Excel SUM ແລະ OFFSET ສູດ

ໃຊ້ SUM ແລະ OFFSET ເພື່ອຊອກຫາທັງຫມົດສໍາລັບຂໍ້ມູນແບບເຄື່ອນໄຫວຂອງຂໍ້ມູນ

ຖ້າ ແຜ່ນວຽກ Excel ຂອງທ່ານປະກອບມີການຄິດໄລ່ໂດຍອີງໃສ່ ລະດັບ ການປ່ຽນແປງຂອງຈຸລັງ, ການນໍາໃຊ້ຫນ້າທີ່ SUM ແລະ OFFSET ຮ່ວມກັນໃນສູດ SUM OFFSET ເຮັດໃຫ້ວຽກງານເກັບຮັກສາການຄິດໄລ່ທັນສະໄຫມ.

ສ້າງຂອບເຂດການເຄື່ອນໄຫວດ້ວຍຟັງຊັນ SUM ແລະ OFFSET

ຖ້າທ່ານໃຊ້ການຄິດໄລ່ໄລຍະເວລາທີ່ປ່ຽນແປງຢ່າງຕໍ່ເນື່ອງ - ເຊັ່ນການຂາຍທັງຫມົດສໍາລັບເດືອນ - ຟັງຊັ່ນ OFFSET ຊ່ວຍໃຫ້ທ່ານຕັ້ງລະດັບການເຄື່ອນໄຫວທີ່ປ່ຽນແປງຍ້ອນວ່າຕົວເລກຍອດຂາຍຂອງແຕ່ລະມື້ເພີ່ມຂຶ້ນ.

ໂດຍຕົວເອງ, ຟັງຊັນ SUM ສາມາດຮອງຮັບເຊນໃຫມ່ຂອງຂໍ້ມູນທີ່ຖືກໃສ່ເຂົ້າໃນລະດັບທີ່ຖືກລວມ.

ຂໍ້ຍົກເວັ້ນຫນຶ່ງເກີດຂື້ນເມື່ອຂໍ້ມູນຖືກໃສ່ລົງໃນຫ້ອງບ່ອນທີ່ມີຫນ້າທີ່ຢູ່ໃນປະຈຸບັນ.

ໃນຮູບພາບຕົວຢ່າງທີ່ມາພ້ອມກັບບົດຄວາມນີ້, ຕົວເລກຍອດຂາຍໃຫມ່ສໍາລັບແຕ່ລະມື້ຈະຖືກເພີ່ມເຂົ້າໄປໃນລຸ່ມຂອງບັນຊີເຊິ່ງບັງຄັບໃຫ້ຈໍານວນທັງຫມົດສືບຕໍ່ປ່ຽນລົງຫນຶ່ງຫ້ອງແຕ່ລະຄັ້ງເມື່ອຂໍ້ມູນໃຫມ່ຖືກເພີ່ມ.

ຖ້າຫາກວ່າຟັງຊັນ SUM ຖືກນໍາໃຊ້ດ້ວຍຕົນເອງເພື່ອໃຫ້ຂໍ້ມູນທັງຫມົດ, ມັນຈໍາເປັນຕ້ອງປັບປຸງລະດັບຂອງຈຸລັງທີ່ໃຊ້ເປັນການ ໂຕ້ຖຽງ ຂອງການເຮັດວຽກແຕ່ລະຂໍ້ມູນໃຫມ່ທີ່ໄດ້ເພີ່ມ.

ຢ່າງໃດກໍຕາມ, ໂດຍນໍາໃຊ້ປະຕິບັດ SUM ແລະ OFFSET ຮ່ວມກັນ, ແນວໃດກໍ່ຕາມ, ລະດັບທີ່ຈະມີທັງຫມົດກາຍເປັນແບບເຄື່ອນໄຫວ. ໃນຄໍາສັບຕ່າງໆອື່ນໆ, ມັນມີການປ່ຽນແປງເພື່ອຮອງຮັບຈຸລັງຂໍ້ມູນໃຫມ່. ນອກເຫນືອຈາກຈຸລັງໃຫມ່ຂອງຂໍ້ມູນບໍ່ກໍ່ໃຫ້ເກີດບັນຫາເນື່ອງຈາກວ່າລະດັບຍັງສືບຕໍ່ປັບຕົວຍ້ອນວ່າແຕ່ລະຫ້ອງໃຫມ່ໆຖືກເພີ່ມ.

Syntax and Arguments

ອ້າງອີງເຖິງຮູບພາບທີ່ມາບົດຄວາມນີ້ເພື່ອປະຕິບັດຕາມການສອນນີ້.

ໃນສູດນີ້, ຟັງຊັນ SUM ແມ່ນໃຊ້ໃນການລວມຂໍ້ມູນທີ່ສະຫນອງໃຫ້ເປັນການໂຕ້ຖຽງຂອງມັນ. ຈຸດເລີ່ມຕົ້ນສໍາລັບຊ່ວງນີ້ແມ່ນຄົງທີ່ແລະຖືກກໍານົດວ່າເປັນຂໍ້ ອ້າງອີງຂອງເຊນ ຕໍ່ເລກທໍາອິດທີ່ຈະຖືກລວມໂດຍສູດ.

ຟັງຊັນ OFFSET ແມ່ນຢູ່ພາຍໃນຟັງຊັນ SUM ແລະຖືກນໍາໃຊ້ເພື່ອສ້າງຈຸດສິ້ນສຸດແບບເຄື່ອນໄຫວໃນລະດັບຂໍ້ມູນທີ່ລວມໄປດ້ວຍສູດ. ນີ້ແມ່ນສໍາເລັດໂດຍກໍານົດຈຸດສຸດທ້າຍຂອງລະດັບຕໍ່ຫນຶ່ງເຊນເຫນືອຕໍາແຫນ່ງຂອງສູດ.

ສູດ syntax ຂອງສູດ:

= SUM (Range Start: OFFSET (Reference, Rows, Cols))

ຊ່ວງເລີ່ມຕົ້ນ - (ຕ້ອງການ) ຈຸດເລີ່ມຕົ້ນສໍາລັບລະດັບຂອງຈຸລັງທີ່ຈະຖືກລວມໂດຍຫນ້າທີ່ SUM. ໃນຮູບພາບຕົວຢ່າງ, ນີ້ແມ່ນ cell B2.

ການອ້າງອີງ - (ຕ້ອງການ) ການອ້າງອີງຫ້ອງການທີ່ໃຊ້ສໍາລັບການຄິດໄລ່ຈຸດສຸດທ້າຍຂອງລະດັບທີ່ຕັ້ງຢູ່ແຖວເກັດທີ່ຢູ່ແລະຖັນຈໍານວນຫຼາຍ. ໃນຮູບແບບຕົວຢ່າງ, ຕົວຢ່າງການ ອ້າງອິງ ແມ່ນຕົວຫນັງສືອ້າງອິງສໍາລັບສູດຕົວຂອງມັນເອງເນື່ອງຈາກວ່າພວກເຮົາສະເຫມີຕ້ອງການລະດັບທີ່ຈະສິ້ນສຸດຫນຶ່ງເຊນຂ້າງເທິງສູດ.

ແຖວ - (ຕ້ອງການ) ຈໍານວນແຖວຂ້າງເທິງຫຼືຂ້າງລຸ່ມໂຕ ອ້າງອີງ ການນໍາໃຊ້ used ໃນການຄິດໄລ່ການຊົດເຊີຍ. ຄ່ານີ້ສາມາດເປັນບວກ, ລົບຫຼືຖືກຕັ້ງເປັນສູນ.

ຖ້າສະຖານທີ່ຂອງຊົດເຊີຍແມ່ນຢູ່ເຫນືອການໂຕ້ຖຽງ ອ້າງອີງ , ມູນຄ່ານີ້ແມ່ນເປັນຕົວເລກລົບ. ຖ້າມັນຢູ່ຂ້າງລຸ່ມ, ການໂຕ້ຖຽງ Rows ແມ່ນເປັນບວກ. ຖ້າ offset ຢູ່ໃນແຖວດຽວກັນ, ການໂຕ້ຖຽງນີ້ແມ່ນສູນ. ໃນຕົວຢ່າງນີ້, ການຊົດເຊີຍເລີ່ມຕົ້ນຫນຶ່ງແຖວເຫນືອຕົວຢ່າງການ ອ້າງອີງ , ດັ່ງນັ້ນຄ່າສໍາລັບການໂຕ້ຖຽງນີ້ແມ່ນເປັນຕົວລົບຫນຶ່ງ (-1).

Cols - (ຕ້ອງການ) ຈໍານວນຂອງ ຄໍລໍາ ຢູ່ທາງຊ້າຍຫລືຂວາຂອງໂຕ ອ້າງອິງອ້າງອີງທີ່ ໃຊ້ໃນການຄິດໄລ່ການຊົດເຊີຍ. ຄ່ານີ້ສາມາດເປັນບວກ, ລົບຫຼືຖືກຕັ້ງເປັນສູນ

ຖ້າຕໍາແຫນ່ງຂອງຊົດເຊີຍແມ່ນຢູ່ເບື້ອງຊ້າຍຂອງການໂຕ້ຖຽງ ອ້າງອີງ , ມູນຄ່ານີ້ແມ່ນເປັນຕົວເລກທີ່ມີຜົນລົບ. ຖ້າໄປທາງຂວາ, ການໂຕ້ຖຽງຂອງ Cols ແມ່ນບວກ. ໃນຕົວຢ່າງນີ້, ຂໍ້ມູນທີ່ຖືກລວມຢູ່ໃນຄໍລໍາດຽວກັນກັບສູດດັ່ງນັ້ນຄ່າສໍາລັບການໂຕ້ຖຽງນີ້ແມ່ນສູນ.

ການນໍາໃຊ້ສູດ SUM OFFSET ກັບຂໍ້ມູນການຂາຍທັງຫມົດ

ຕົວຢ່າງນີ້ໃຊ້ສູດ SUM OFFSET ເພື່ອສົ່ງກັບຄືນທັງຫມົດສໍາລັບຕົວເລກການຂາຍລາຍວັນທີ່ລະບຸໄວ້ໃນຄໍລໍາ B ຂອງແຜ່ນວຽກ.

ໃນເບື້ອງຕົ້ນ, ສູດໄດ້ຖືກເຂົ້າໄປໃນຫ້ອງ B6 ແລະລວມຂໍ້ມູນການຂາຍໃນສີ່ມື້.

ຂັ້ນຕອນຕໍ່ໄປແມ່ນການຍ້າຍສູດ SUM OFFSET ລົງໃນແຖວເພື່ອເຮັດໃຫ້ຫ້ອງສໍາລັບຍອດຂາຍຂອງມື້ທີຫ້າ.

ນີ້ແມ່ນສໍາເລັດໂດຍ ການໃສ່ແຖວ 6 ໃຫມ່, ເຊິ່ງຍ້າຍສູດລົງໄປຫາແຖວ 7.

ເນື່ອງຈາກການຍ້າຍນີ້ Excel ຍົກເລີກການໂຕ້ຖຽງອ້າງອີງໃສ່ cell B7 ໂດຍອັດຕະໂນມັດແລະເພີ່ມ B6 cell ໃຫ້ກັບຂອບເຂດທີ່ໄດ້ຖືກ summed ໂດຍສູດ.

ເຂົ້າສູ່ສູດ SUM OFFSET ສູດ

1. ໃຫ້ຄລິກໃສ່ cell B6 ຊຶ່ງເປັນສະຖານທີ່ບ່ອນທີ່ຜົນໄດ້ຮັບຂອງສູດຈະຖືກສະແດງໃນເບື້ອງຕົ້ນ.
2. ໃຫ້ຄລິກໃສ່ແຖບ ສູດ ຂອງເມນູ ribbon .
3. ເລືອກ Math & Trig ຈາກໂບເພື່ອເປີດບັນຊີລາຍການແບບເລື່ອນລົງ.
4. ໃຫ້ຄລິກໃສ່ SUM ໃນບັນຊີລາຍຊື່ເພື່ອສະແດງ ກ່ອງໂຕ້ຕອບ ຂອງຫນ້າທີ່.
5. ໃນກ່ອງໂຕ້ຕອບ, ໃຫ້ຄລິກໃສ່ເສັ້ນຫມາຍ ເລກ 1 .
6. ຄລິກໃສ່ cell B2 ເພື່ອເຂົ້າໄປໃສ່ກະສານອ້າງອີງຂອງຫ້ອງນີ້ໃນກ່ອງໂຕ້ຕອບ. ສະຖານທີ່ນີ້ແມ່ນຈຸດປາຍທາງຄົງທີ່ສໍາລັບສູດ;
7. ໃນກ່ອງໂຕ້ຕອບ, ໃຫ້ຄລິກໃສ່ເສັ້ນຫມາຍ ເລກ 2 .
8. ກະລຸນາປົດຕໍາແຫນ່ງ OFFSET ດັ່ງຕໍ່ໄປນີ້: OFFSET (B6, -1.0) ເພື່ອສ້າງຈຸດສິ້ນສຸດແບບໄດນາມິກສໍາລັບສູດ.
9. ກົດ OK ເພື່ອເຮັດສໍາເລັດຫນ້າທີ່ແລະປິດກ່ອງໂຕ້ຕອບ.

ທັງຫມົດ $ 567915 ປາກົດຢູ່ໃນຫ້ອງ B7.

ເມື່ອທ່ານກົດປຸ່ມ B3, ຟັງຊັນສົມບູນ = SUM (B2: OFFSET (B6, -1.0)) ຈະປາກົດຢູ່ໃນ ແຖບສູດ ຂ້າງເທິງແຜ່ນວຽກ.

ເພີ່ມຂໍ້ມູນການຂາຍມື້ຕໍ່ໄປ

ເພື່ອເພີ່ມຂໍ້ມູນການຂາຍມື້ຕໍ່ໄປ:

1. ໃຫ້ຄລິກຂວາເທິງ ຫົວ ແຖວສໍາລັບແຖວ 6 ເພື່ອເປີດເມນູ context.
2. ໃນເມນູ, ໃຫ້ຄລິກ ໃສ່ Insert ເພື່ອໃສ່ແຖວໃຫມ່ໃນແຜ່ນວຽກ.
3. ດັ່ງນັ້ນ, ສູດ SUM OFFSET ເລື່ອນລົງໄປຫາ cell B7 ແລະແຖວ 6 ແມ່ນຫວ່າງແລ້ວ.
4. ກົດທີ່ຫ້ອງ A6 .
5. ກະລຸນາໃສ່ຈໍານວນ 5 ເພື່ອຊີ້ບອກວ່າຈໍານວນຍອດຂາຍໃນມື້ທີຫ້າຈະຖືກນໍາເຂົ້າ.
6. ກົດໃສ່ຫ້ອງ B6.
7. ພິມຫມາຍເລກ $ 1458.25 ແລະກົດປຸ່ມ Enter ໃສ່ ແປ້ນພິມ.

ການປັບປຸງຈຸລັງ B7 ສໍາລັບການໃຫມ່ທັງຫມົດ $ 7137.40.

ເມື່ອທ່ານຄລິກໃສ່ cell B7, ສູດປັບປຸງ = SUM (B2: OFFSET (B7, -1.0)) ຈະປາກົດໃນແຖບສູດ.

ຫມາຍເຫດ : ຟັງຊັນ OFFSET ມີຕົວຢ່າງສອງຕົວຢ່າງ: ຄວາມສູງ ແລະ ຄວາມກວ້າງ, ເຊິ່ງຖືກຍົກເວັ້ນໃນຕົວຢ່າງນີ້.

ການໂຕ້ຖຽງເຫຼົ່ານີ້ສາມາດຖືກນໍາໃຊ້ເພື່ອບອກການເຮັດວຽກຂອງ OFFSET ຮູບຮ່າງຂອງຜົນຜະລິດໃນແງ່ຂອງມັນເປັນແຖວເກັດທີ່ຢູ່ສູງແລະຄໍລໍາຫຼາຍດັ່ງກ່າວ.

ໂດຍການຍົກເລີກການໂຕ້ຖຽງເຫຼົ່ານີ້, ຟັງຊັນ, ໂດຍຕົວຢ່າງ, ໃຊ້ລະດັບຄວາມສູງແລະຄວາມກວ້າງຂອງໂຕອ້າງອິງອ້າງອີງແທນ, ເຊິ່ງໃນຕົວຢ່າງນີ້ແມ່ນຫນຶ່ງແຖວສູງແລະຫນຶ່ງຄໍລໍາກວ້າງ.