สูตร Excel สำคัญสำหรับงานบัญชี 10 สูตรที่ใช้บ่อย พร้อมตัวอย่าง
· อ่านประมาณ 8 นาที
งานบัญชีใช้ Excel ทุกวัน ตั้งแต่สรุปค่าใช้จ่าย คำนวณภาษี ไปจนถึงกระทบยอดกับรายการธนาคาร ถ้ารู้ สูตร Excel งานบัญชี ที่ถูกจังหวะ จะลดเวลาทำงานจากหลายชั่วโมงเหลือไม่กี่นาที และลดความผิดพลาดจากการพิมพ์ตัวเลขเอง บทความนี้รวม 10 สูตรสำคัญพร้อมตัวอย่างและภาพหน้าจอ เป็นเนื้อหาหนึ่งที่เรามักใช้ในอบรม Excelและสอน Excelให้ฝ่ายบัญชีและการเงิน
ถ้ายังไม่แน่ใจพื้นฐาน อ่านสูตร Excel พื้นฐานที่ต้องรู้ก่อน
1. SUMIFS รวมยอดตามเงื่อนไขหลายข้อ
สูตรหลักของงานสรุปค่าใช้จ่าย รวมยอดตามรหัสบัญชี เดือน หรือแผนกพร้อมกันได้
=SUMIFS($D$2:$D$7,$A$2:$A$7,$G2,$C$2:$C$7,H$1)

ล็อกช่วงข้อมูลด้วย $ เพื่อให้ลากสูตรไปทั้งตารางได้ อ่านเพิ่มที่SUMIFS และ COUNTIFS
2. คำนวณ VAT 7% ทั้งแบบรวมและแยก
- ราคายังไม่รวม VAT: VAT =
=B2*7%, ราคารวม ==B2*1.07 - ราคารวม VAT แล้ว ต้องการแยก: ก่อน VAT =
=ROUND(B2*100/107,2), VAT ==ROUND(B2*7/107,2)

อัตรา VAT และเงื่อนไขอาจเปลี่ยนตามประกาศ ควรตรวจกับกรมสรรพากรและนักบัญชีของบริษัท
3. ROUND, ROUNDUP, ROUNDDOWN ปัดเศษให้ตรงกฎ
ตัวเลขทางบัญชีต้องปัดให้ตรงกับที่แสดงในเอกสาร ไม่ใช่แค่ซ่อนทศนิยมด้วยการจัดรูปแบบ เพราะค่าจริงในเซลล์ยังเป็นทศนิยมเต็มและอาจทำให้ยอดรวมต่างกันหลักสตางค์
=ROUND(A2,2)ปัดเป็น 2 ตำแหน่งตามหลักปกติ=ROUNDUP(A2,0)ปัดขึ้นเป็นจำนวนเต็ม=ROUNDDOWN(A2,2)ตัดทิ้งโดยไม่ปัดขึ้น
4. คำนวณภาษีหัก ณ ที่จ่ายด้วย IF และ VLOOKUP
ทำตารางอัตราไว้ในชีตหนึ่ง แล้วใช้ VLOOKUP ดึงอัตราตามประเภทรายได้ เช่น ค่าบริการ 3% ค่าเช่า 5% ค่าโฆษณา 2% ค่าขนส่ง 1%
=ROUND(B2*VLOOKUP(C2,อัตราภาษี!$A$2:$B$6,2,FALSE),2)
ตัวอย่างอัตราข้างต้นเป็นอัตราที่ใช้กันทั่วไป แต่เงื่อนไขขึ้นกับประเภทเงินได้และผู้จ่าย ต้องตรวจประกาศปัจจุบันเสมอ อ่านวิธีใช้ VLOOKUP ที่VLOOKUP ข้ามชีต
5. VLOOKUP / XLOOKUP ดึงข้อมูลชื่อบัญชีและผู้ขาย
ใช้ดึงชื่อบัญชีจากรหัส หรือเลขประจำตัวผู้เสียภาษีจากชื่อผู้ขาย ถ้าใช้ Excel 365 ให้ลอง XLOOKUP ที่ไม่ต้องนับคอลัมน์และมองซ้ายได้
=XLOOKUP(A2,ผังบัญชี!A:A,ผังบัญชี!B:B,"ไม่พบรหัส")
6. IFERROR ซ่อนข้อผิดพลาดให้รายงานเรียบร้อย
ครอบสูตรที่อาจเจอ #N/A หรือ #DIV/0! ด้วย =IFERROR(สูตรเดิม,"") แต่ควรใช้อย่างมีสติ เพราะซ่อนข้อผิดพลาดจริงที่ควรแก้ได้ ควรใส่ข้อความที่บอกสาเหตุ เช่น "ไม่พบรหัส" แทนค่าว่าง
7. COUNTIF ตรวจใบแจ้งหนี้ซ้ำ
ก่อนจ่ายเงินหรือบันทึกบัญชี ใช้ =COUNTIF($A$2:$A$100,A2)>1 เพื่อเช็กว่าเลขที่เอกสารซ้ำหรือไม่ ใช้ร่วมกับ Conditional Formatting เพื่อระบายสีแถวที่ซ้ำ
8. กระทบยอดสมุดบัญชีกับ Statement ด้วย IF + VLOOKUP + IFERROR
รวมสูตรจากข้อ 5-7 เป็นสูตรเดียวที่บอกว่ารายการตรงกัน ยอดไม่ตรง ไม่พบ หรือซ้ำ
=IF(COUNTIF($A$2:$A$6,A2)>1,"ซ้ำ ตรวจรายการ",
IFERROR(IF(B2=VLOOKUP(A2,Statement!A:B,2,0),"ตรงกัน","ยอดไม่ตรง"),"ไม่พบใน Statement"))

9. EOMONTH และ DATE จัดการวันที่ครบกำหนดและปิดงวด
=EOMONTH(A2,0)วันสิ้นเดือนของเดือนนั้น ใช้ปิดงวดบัญชี=A2+30หรือ=EDATE(A2,1)คำนวณวันครบกำหนดชำระ=TODAY()-C2นับอายุหนี้ (aging)
อ่านสูตรวันที่เพิ่มที่สูตรวันที่ใน Excel
10. Pivot Table สรุปงบทดลองและรายงานเปรียบเทียบ
เมื่อข้อมูลเป็นรายการยาวหลายพันบรรทัด Pivot Table สรุปตามบัญชี เดือน และแผนกได้ในไม่กี่คลิก และ Refresh เมื่อข้อมูลเปลี่ยน ดูขั้นตอนในสอนใช้ Pivot Table ทำรายงาน
เคล็ดลับให้ไฟล์บัญชีน่าเชื่อถือ
- แยกชีตข้อมูลดิบ ชีตตารางอัตรา และชีตรายงานออกจากกัน
- อย่าพิมพ์ทับสูตรด้วยตัวเลข ถ้าจำเป็นให้ระบายสีและเขียนหมายเหตุ
- ล็อกเซลล์สูตร (Protect Sheet) ก่อนส่งให้คนอื่นกรอก
- เก็บสำเนาไฟล์ทุกงวดก่อนแก้ไข
สรุป
10 สูตรสำคัญของงานบัญชีคือ SUMIFS, VAT, ROUND, หัก ณ ที่จ่าย, VLOOKUP/XLOOKUP, IFERROR, COUNTIF, สูตรกระทบยอด, EOMONTH และ Pivot Table เมื่อใช้ร่วมกันได้ จะสร้างรายงานที่ตรวจสอบง่ายและทำซ้ำได้ทุกงวด
หมายเหตุ: ตัวเลขและชื่อในภาพเป็นข้อมูลตัวอย่างเพื่อการเรียนรู้ ไม่ใช่ข้อมูลจริงของบริษัทใด
ถ้าอยากฝึกกับข้อมูลจริงของงานบัญชี Wise Success มีอบรม Excelและสอน ExcelหลักสูตรExcel Advance ที่ครอบคลุมสูตรและ Pivot Table ทั้งแบบPublic TrainingและIn-house สำหรับองค์กร หรือติดต่อเราเพื่อปรึกษาหัวข้อให้ตรงกับทีมบัญชี
- #สูตร Excel งานบัญชี
- #สอน Excel
- #อบรม Excel
- #SUMIFS
- #VLOOKUP
- #คำนวณ VAT
- #Excel