SUMIFS ใช้ยังไง? รวมรายจ่ายตามหมวดและเดือน

Wynn
25 ก.ย. 26
16:52
15 views

sumifs ใช้รวมตัวเลขที่ต้องผ่านหลายเงื่อนไขพร้อมกัน เช่น รวมเฉพาะรายจ่ายหมวดอาหารภายในเดือนที่เลือก โดยกำหนดช่วงยอดเงิน ช่วงหมวด และช่วงวันที่ไว้ในสูตรเดียว จึงไม่ต้องกรองรายการแล้วบวกยอดใหม่ทุกเดือน

SUMIFS ช่วยสรุปรายจ่ายอย่างไร?

SUMIFS เหมาะกับตารางที่มีหลายรายการและต้องการคำตอบเฉพาะกลุ่ม เช่น เดือนนี้จ่ายค่าเดินทางเท่าไร หรือหมวดใดใช้เงินเกินแผน ผลรวมที่แยกตามหมวดสามารถนำไปตรวจรายจ่ายและจัดทำ งบประมาณฐานศูนย์ สำหรับรอบถัดไปได้

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

ขั้นตอนที่ 1 เตรียมตารางรายจ่ายให้คอลัมน์ชัดเจน

จัดข้อมูลเป็นหนึ่งรายการต่อหนึ่งแถว โดยให้คอลัมน์ A เป็นวันที่ คอลัมน์ B เป็นหมวด คอลัมน์ C เป็นรายละเอียด และคอลัมน์ D เป็นจำนวนเงิน ไม่ควรรวมเซลล์หรือใส่ยอดรวมย่อยคั่นกลางตาราง เพราะอาจทำให้เลือกช่วงสูตรผิด

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

ขั้นตอนที่ 2 ทำความเข้าใจโครงสร้างสูตร SUMIFS

โครงสร้างพื้นฐานคือ =SUMIFS(sum_range, criteria_range1, criterion1, criteria_range2, criterion2, …) โดย sum_range คือช่วงตัวเลขที่ต้องการรวม ส่วน criteria_range และ criterion คือช่วงตรวจสอบกับเงื่อนไขที่ต้องผ่าน

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

ขั้นตอนที่ 3 รวมรายจ่ายตามหมวดเดียว

ถ้าจำนวนเงินอยู่ใน D2 หมวดอยู่ใน B2 และเซลล์ G2 ระบุหมวดที่ต้องการ ใช้สูตร =SUMIFS($D$2:$D$100,$B$2:$B$100,$G2) เครื่องหมาย $ ช่วยตรึงช่วงข้อมูลเมื่อคัดลอกสูตรไปยังแถวอื่น

การอ้างอิง G2 แทนการพิมพ์ชื่อหมวดในสูตรช่วยให้เปลี่ยนหมวดได้ง่าย และลดความผิดพลาดจากการแก้ข้อความหลายจุด หากต้องการสรุปหลายหมวด ให้เรียงชื่อหมวดลงในคอลัมน์ G แล้วลากสูตรลง

ขั้นตอนที่ 4 กำหนดช่วงวันที่ของเดือน

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

สูตรคือ =SUMIFS($D$2:$D$100,$B$2:$B$100,$G2,$A$2:$A$100,”>=”&F$2,$A$2:$A$100,”<“&EDATE(F$2,1)) โดย EDATE เลื่อนไปยังวันเดียวกันของเดือนถัดไป ซึ่งในกรณีนี้คือวันแรกของเดือนใหม่

ขั้นตอนที่ 5 อ่านสูตรทีละเงื่อนไข

สูตรตัวอย่างเริ่มจากรวมจำนวนเงินใน D2 จากนั้นตรวจว่าหมวดใน B2 ตรงกับ G2 วันที่ใน A2 ไม่น้อยกว่า F2 และต้องน้อยกว่าวันแรกของเดือนถัดไป รายการจึงต้องผ่านเงื่อนไขทั้งหมดจึงจะถูกรวม

เครื่องหมายเปรียบเทียบต้องอยู่ในอัญประกาศและเชื่อมกับเซลล์ด้วย & เช่น “>=”&F2 หากพิมพ์ “>=F2” โปรแกรมจะอ่าน F2 เป็นข้อความ ไม่ได้นำวันที่ในเซลล์มาเปรียบเทียบ

ขั้นตอนที่ 6 คัดลอกสูตรสร้างตารางสรุป

วางชื่อหมวดตามแนวตั้งและวันแรกของแต่ละเดือนตามแนวนอน จากนั้นใช้การอ้างอิงแบบผสม เช่น $G2 สำหรับหมวด และ F$2 สำหรับเดือน เมื่อคัดลอกสูตรไปทางขวาหรือลงด้านล่าง ตำแหน่งที่จำเป็นจะเปลี่ยนอัตโนมัติ

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

ขั้นตอนที่ 7 ตรวจสูตรเมื่อยอดไม่ตรง

หากผลลัพธ์เป็นศูนย์ทั้งที่มีรายการ ให้ตรวจชนิดข้อมูลวันที่ ช่องว่างในชื่อหมวด ตำแหน่งเครื่องหมายอัญประกาศ และความยาวของทุกช่วง หากได้ #VALUE! ให้ตรวจว่า sum_range กับ criteria_range ครอบคลุมจำนวนแถวและคอลัมน์สอดคล้องกัน

Microsoft ระบุว่า SUMIF, SUMIFS, COUNTIF, COUNTIFS และ COUNTBLANK อาจแสดง #VALUE! เมื่อสูตรคำนวณโดยอ้างอิงเซลล์ในสมุดงานภายนอกที่ปิดอยู่ เอกสารครอบคลุม Excel หลายรุ่น ได้แก่ Microsoft 365, 2024, 2021 และ 2016 โดยแนะนำให้เปิดไฟล์ต้นทางหรือปรับวิธีคำนวณ (2026-03-31) [1]

ตัวอย่างสมมติการใช้ SUMIFS

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

ตัวอย่างที่ 1 รวมรายจ่ายหมวดเดียว

สมมติ G2 ระบุ “อาหาร” และรายการใน D2 มีค่าอาหาร 120, 80 และ 200 บาท สูตร =SUMIFS(D2,B2,G2) จะได้ผลรวม 400 บาท เพราะรวมเฉพาะแถวที่หมวดตรงกับ G2 รายการค่าเดินทางหรือของใช้ในช่วงเดียวกันจะไม่ถูกนำมารวม แม้จำนวนเงินจะอยู่ใน D2 เหมือนกัน เพราะไม่ผ่านเงื่อนไขหมวด

ตัวอย่างที่ 2 รวมตามหมวดและเดือน

สมมติมีค่าเดินทางเดือนสิงหาคม 150, 300 และ 250 บาท ส่วนรายการเดือนกันยายน 400 บาท สูตรที่กำหนดหมวด “ค่าเดินทาง” และช่วงเดือนสิงหาคมจะรวมได้ 700 บาท โดยไม่รวมรายการเดือนกันยายน

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

ตัวอย่างที่ 3 เพิ่มเงื่อนไขช่องทางชำระเงิน

สมมติต้องการรวมค่าอาหารเดือนสิงหาคมที่จ่ายด้วยบัตรเครดิตเท่านั้น โดยคอลัมน์ E เก็บช่องทางชำระเงิน สามารถเพิ่มคู่เงื่อนไข $E$2:$E$100,”บัตรเครดิต” ต่อท้ายสูตรเดิม

หากค่าอาหารเดือนนั้นรวม 1,500 บาท แต่ยอดที่ชำระด้วยบัตรเครดิตมี 900 บาท สูตรจะคืนค่า 900 บาท เพราะอีก 600 บาทไม่ผ่านเงื่อนไขช่องทางชำระเงิน

SUMIF กับ SUMIFS ต่างกันอย่างไร?

SUMIF เหมาะกับการรวมข้อมูลด้วยเงื่อนไขเดียว ส่วน SUMIFS รองรับหลายเงื่อนไขพร้อมกัน จุดที่มักสับสนคือลำดับอาร์กิวเมนต์ โดย SUMIFS วางช่วงผลรวมไว้ลำดับแรก ขณะที่ SUMIF วางช่วงผลรวมไว้หลังช่วงตรวจสอบและเงื่อนไข

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

ควรกำหนดช่วงข้อมูลใน SUMIFS อย่างไร?

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

Google Sheets รองรับไฟล์ได้สูงสุด 10 ล้านเซลล์ เพิ่มจากเดิม 5 ล้านเซลล์ และใช้ขีดจำกัดนี้กับไฟล์ใหม่ ไฟล์เดิม และไฟล์ที่นำเข้า อย่างไรก็ตาม ขีดจำกัดของไฟล์ไม่ได้หมายความว่าสูตรควรอ้างอิงข้อมูลทั้งหมด ควรแยกหน้าข้อมูลดิบออกจากหน้าสรุปและขยายช่วงเมื่อมีรายการเพิ่ม (2022-03-14) [2]

สรุปวิธีใช้ SUMIFS รวมรายจ่าย

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