ข้ามไปเนื้อหาหลัก
Wise Success (Thailand)
เทคนิค Excel

สูตร 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)
ตัวอย่างตาราง Excel ใช้ SUMIFS รวมยอดค่าใช้จ่ายตามรหัสบัญชีและเดือน
SUMIFS รวมยอดตามรหัสบัญชีและเดือน แล้วลากสูตรทั้งตาราง

ล็อกช่วงข้อมูลด้วย $ เพื่อให้ลากสูตรไปทั้งตารางได้ อ่านเพิ่มที่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)
ตัวอย่างตาราง Excel แยกราคารวม VAT เป็นมูลค่าก่อน VAT และ VAT 7 เปอร์เซ็นต์ด้วยสูตร ROUND
แยกราคารวม VAT เป็นมูลค่าก่อน VAT และภาษี

อัตรา 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"))
ตัวอย่างตารางกระทบยอดใน Excel ใช้ IF COUNTIF VLOOKUP และ IFERROR บอกว่ารายการตรงกัน ยอดไม่ตรง หรือซ้ำ
ผลกระทบยอด: เขียว = ตรงกัน, ส้ม = ยอดไม่ตรง/ไม่พบ, เหลือง = ซ้ำ

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 สำคัญสำหรับพนักงานขาย 8 สูตร สรุปยอด เป้า และคอมมิชชัน

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

อ่านต่อ →

เทคนิค Excel ·

สอนใช้ Pivot Table ทำรายงานยอดขายใน Excel ทีละขั้น พร้อมภาพหน้าจอ

สอนใช้ Pivot Table ทำรายงานยอดขายครึ่งปีแบบลงมือทำ สร้าง Pivot Table ลากฟิลด์ลง Rows Columns Values จัดกลุ่มวันที่เป็นเดือน Show Values As เปอร์เซ็นต์ ใส่ Slicer และ Pivot Chart พร้อมภาพหน้าจอ

อ่านต่อ →

เทคนิค Excel ·

สอนสร้างกราฟใน Excel: เลือกชนิดกราฟและกำหนดลักษณะกราฟให้ดูเป็นมืออาชีพ

สอน Excel ทำกราฟตั้งแต่เตรียมข้อมูล เลือกชนิดกราฟ สร้างกราฟ ไปจนถึงปรับแต่งชื่อกราฟ สี Data Labels แกน เส้นตาราง และกราฟผสมสองแกน พร้อมภาพหน้าจอและตารางแก้ปัญหาที่พบบ่อย

อ่านต่อ →

ต้องการจัดอบรมให้ทีมในองค์กร?

ปรับเนื้อหาให้ตรงกับงานจริงของทีมคุณ สอนที่บริษัทหรือ Online ได้ ส่งรายละเอียดมา เราตอบกลับพร้อมใบเสนอราคา

☎ โทรเลย LINE @wisesuccess

เลือกประเภทคุกกี้ที่คุณอนุญาต ปรับเปลี่ยนได้ทุกเมื่อที่ลิงก์ "ตั้งค่าคุกกี้" ท้ายเว็บไซต์