สูตรคำนวณเงินคงเหลือ Excel แบบยกยอดต่อเนื่อง

Harry P
18 ก.ย. 26
15:50
24 views

สูตรคำนวณเงินคงเหลือ Excel สามารถทำได้ด้วยหลักง่ายๆ คือนำเงินคงเหลือจากรายการก่อนหน้า บวกกับรายรับ และหักรายจ่ายของรายการปัจจุบัน จากนั้นจึงยกยอดที่ได้ ไปคำนวณต่อในแถวถัดไป ทำให้เห็นยอดเงินที่เหลือหลังแต่ละรายการได้ต่อเนื่องรายละเอียด

สูตรการคำนวณเงินคงเหลือใน Excel ใช้แบบไหน?

ใช้สูตร ยอดคงเหลือเดิม + รายรับ – รายจ่าย โดยอ้างอิงเซลล์ของแต่ละรายการ เช่น หากคอลัมน์ C เป็นรายรับ คอลัมน์ D เป็นรายจ่าย และคอลัมน์ E เป็นเงินคงเหลือ สูตรสำหรับรายการถัดจากยอดตั้งต้น อาจเขียนได้ว่า =E2+C3-D3

ในตารางตัวอย่าง สูตรที่ช่องเงินคงเหลือจะนำ 10,000 + 20,000 – 0 จึงเหลือ 30,000 บาท ส่วนแถวค่าอาหารก็ใช้ยอด 30,000 บาทจากแถวก่อนหน้า แล้วหักออกอีก 500 บาท Excel รองรับการนำ Cell Reference มาใช้ภายในสูตรโดยตรง เมื่อค่าของเซลล์ต้นทางเปลี่ยน ผลลัพธ์ของสูตรก็สามารถคำนวณตามข้อมูลใหม่ได้

ยอดตั้งต้นควรวางตรงไหนก่อนเริ่มคำนวณ?

ควรวางยอดตั้งต้นไว้ก่อนรายการรายรับ และรายจ่ายแรก เพื่อให้สูตรมีจำนวนเงินเริ่มต้นสำหรับนำไปยกยอดต่อ ตัวอย่างเช่น หากวันที่เริ่มทำตารางมีเงินอยู่ 10,000 บาท ก็สามารถบันทึกจำนวนนี้ไว้ในช่องเงินคงเหลือของแถวแรกได้เลย

ยอดตั้งต้นไม่จำเป็นต้องถือเป็นรายรับใหม่ เพราะเป็นเงินที่มีอยู่ก่อนเริ่มบันทึกรายการ วิธีแยกยอดตั้งต้นออกจากรายรับจะช่วยให้ตารางอ่านง่ายขึ้น และไม่ทำให้ยอดเงินเข้าของช่วงเวลานั้นสูงเกินความจริง

ทำอย่างไรให้เงินคงเหลือยกยอดต่อแถวอัตโนมัติ?

ให้สร้างสูตรในแถวแรกที่ต้องคำนวณก่อน แล้ว Copy หรือลากสูตรลงมายังรายการด้านล่าง Excel จะปรับ Cell Reference ตามตำแหน่งของแต่ละแถวให้อัตโนมัติเมื่อใช้ Relative Reference

ตัวอย่างเช่น สูตรในแถวแรกคือ =E2+C3-D3 เมื่อลากลงหนึ่งแถว สูตรจะเปลี่ยนเป็น =E3+C4-D4 และแถวต่อไปจะอ้างอิงยอดที่คำนวณได้ จากรายการก่อนหน้าไปเรื่อยๆ หลักนี้เกิดจาก Relative Reference ซึ่ง Excel จะปรับเลขแถว หรือคอลัมน์ตามตำแหน่งของสูตรเมื่อ Copy หรือ Fill ไปยังเซลล์อื่น

มีสูตรเงินคงเหลือแบบไม่ต้องอ้างอิงยอดแถวก่อนหน้าหรือไม่?

มี โดยสามารถคำนวณจาก ยอดตั้งต้น + รายรับสะสม – รายจ่ายสะสม แทนการยกเงินคงเหลือจากแถวก่อนหน้า วิธีนี้เหมาะกับตารางที่ต้องการตรวจย้อนกลับว่าตัวเลขเกิดจากรายรับ และรายจ่ายสะสมเท่าไร สมมติยอดตั้งต้นอยู่ที่ G1 รายรับเริ่มจากคอลัมน์ C แถว 2 และรายจ่ายอยู่คอลัมน์ D

สามารถใช้สูตรลักษณะนี้ได้ =$G$1+SUM($C$2:C2)-SUM($D$2:D2) เมื่อลากสูตรลง ช่วงปลายของ SUM จะขยายไปตามรายการ เช่น จาก C2 เป็น C2:C3 และ C2:C4 ขณะที่ $G$1 จะยังอ้างอิงยอดตั้งต้นเซลล์เดิม เพราะเป็น Absolute Reference ซึ่งจะไม่ปรับตำแหน่งตามเมื่อ Copy หรือ Fill สูตรไปยังแถว หรือคอลัมน์อื่น

เพิ่มแถวใหม่แล้วทำไมเงินคงเหลือไม่คำนวณต่อ?

สาเหตุหลักอาจเป็น

แถวใหม่ไม่มีสูตร
สูตรไม่ได้ถูก Copy ลงมา
Cell Reference อ้างอิงผิดแถว
แทรกข้อมูลอยู่นอกช่วงตารางเดิม

ลบแถวแล้วทำไมยอดเงินคงเหลือถึงเปลี่ยน?

เพราะรายการที่ถูกลบอาจเป็นส่วนหนึ่งของยอดสะสม เมื่อรายรับ หรือรายจ่ายรายการนั้นหาย ยอดเงินคงเหลือหลังจากจุดดังกล่าว จึงสามารถเปลี่ยนตามได้ ซึ่งไม่ได้หมายความว่าสูตรผิดเสมอไป สิ่งที่ควรระวังคือการลบเฉพาะบางเซลล์ หรือเผลอลบสูตรในคอลัมน์เงินคงเหลือ

สูตรถูกแต่ยอดเงินคงเหลือไม่ตรง ควรเช็กอะไร?

ควรเริ่มจากตรวจข้อมูลต้นทางก่อนแก้สูตร เพราะปัญหาอาจไม่ได้อยู่ที่เครื่องหมายบวก หรือลบเพียงอย่างเดียว โดยสามารถไล่ตรวจได้ดังนี้

ยอดตั้งต้นตรงกับเงินจริง ณ วันที่เริ่มบันทึกหรือไม่
มีรายรับ หรือรายจ่ายรายการใดตกหล่น หรือบันทึกซ้ำหรือไม่
สูตรเงินคงเหลือ อ้างอิงแถวก่อนหน้าถูกต้องหรือไม่
มีแถวใดที่สูตรถูกลบ หรือถูกแทนที่ด้วยตัวเลขหรือไม่
รายรับ และรายจ่าย ถูกเก็บเป็นตัวเลขจริง หรือเป็นข้อความ
หลังเพิ่ม หรือลบแถว ช่วงที่สูตรอ้างอิงยังครอบคลุมข้อมูลหรือไม่

โดยเฉพาะข้อมูลที่ถูกเก็บเป็น Text แม้จะมีหน้าตาเหมือนตัวเลข ก็สามารถทำให้การคำนวณบางรูปแบบ ให้ผลลัพธ์ที่ไม่เป็นไปตามที่คาดได้ จึงแนะนำให้แปลงค่าที่ควรนำไปคำนวณกลับมาเป็น Number เมื่อพบว่าตัวเลขถูกเก็บเป็นข้อความ (2026) [1]

ควรใช้สูตรยกยอดหรือ SUM สะสมดีกว่า?

ขึ้นอยู่กับรูปแบบตาราง หากต้องการตารางรายรับรายจ่ายทั่วไปที่อ่านสูตรง่าย วิธีนำยอดคงเหลือจากแถวก่อนหน้ามาบวก และลบต่อ เหมาะกับการเริ่มต้น เพราะมองเห็นลำดับของเงินหลังแต่ละรายการได้ชัดเจน

แต่ถ้าต้องตรวจยอดย้อนหลังบ่อย สามารถใช้ SUM เพื่อรวมรายรับ และรายจ่ายสะสมจากยอดตั้งต้นได้ เพราะเป็นการรวมตัวเลขทุกแถว โดยไม่ต้องกำหนดเงื่อนไขเหมือน SUMIF ทำให้ยอดแต่ละแถว คำนวณจากข้อมูลต้นทางสะสมโดยตรง และตรวจที่มาของยอดได้ง่ายขึ้น

ตารางเงินคงเหลือใน Excel รองรับข้อมูลได้มากแค่ไหน?

รองรับข้อมูลได้ค่อนข้างมาก โดย Excel หนึ่ง Worksheet มีได้สูงสุด 1,048,576 แถว และ 16,384 คอลัมน์ แต่เมื่อตารางมีสูตรจำนวนมาก ก็ควรจัดช่วงอ้างอิงให้เหมาะสม และตรวจสูตรยกยอดเป็นระยะ เพื่อให้แก้ไข และตรวจหาความผิดพลาดได้ง่ายขึ้น (2024-08-28) [2]

สรุป การคำนวณเงินคงเหลือใน Excel แบบยกยอด

สุดท้ายแล้ว หลักของสูตรคำนวณเงินคงเหลือใน Excel คือยอดคงเหลือเดิม + รายรับ – รายจ่าย = ยอดคงเหลือใหม่ แล้วนำผลลัพธ์ไปใช้ต่อกับรายการถัดไป หากเป็นตารางที่มีการเพิ่ม หรือลบข้อมูลบ่อย ควรตรวจ Cell Reference และช่วงสูตรให้ต่อเนื่องอยู่เสมอ