Excel สำหรับงานบัญชี: เทมเพลตและสูตรที่จำเป็น

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

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

การตั้งค่าเทมเพลตสมุดบัญชีแยกประเภททั่วไป (General Ledger หรือ GL)

สมุดบัญชีแยกประเภททั่วไป (General Ledger) เป็นแหล่งรวบรวมหลักของธุรกรรมทางการเงินทั้งหมดของคุณ หากคุณกำลังใช้ Excel เพื่อทำบัญชีสำหรับธุรกิจขนาดเล็ก การวางโครงสร้าง GL ให้ถูกต้องตั้งแต่วันแรกเป็นสิ่งสำคัญมาก โครงสร้าง GL ที่ไม่ดีจะทำให้ไม่สามารถสร้างรายงานแบบอัตโนมัติได้ในภายหลัง

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

วันที่	รหัสธุรกรรม	รหัสบัญชี	คำอธิบาย	เดบิต	เครดิต	ยอดคงเหลือสะสม
2023-10-01	TRX-001	1010 (เงินสด)	เงินลงทุนของเจ้าของ	$10,000		$10,000
2023-10-03	TRX-002	6010 (ค่าเช่า)	จ่ายค่าเช่าเดือนตุลาคม		$2,000	$8,000
2023-10-05	TRX-003	4010 (ยอดขาย)	ใบแจ้งหนี้ลูกค้า A	$1,500		$9,500

การสร้างยอดคงเหลือสะสมแบบไดนามิก

หากต้องการคำนวณยอดคงเหลือสะสมที่จะอัปเดตแบบไดนามิกเมื่อคุณเพิ่มแถว คุณต้องใช้สูตรที่บวกฝั่งเดบิตและลบฝั่งเครดิตออกจากยอดคงเหลือของแถวก่อนหน้า สมมติว่าแถวที่ 1 คือส่วนหัว (Header) ของคุณ และแถวที่ 2 มีธุรกรรมแรก ให้ใส่ยอดคงเหลือเริ่มต้นของคุณในช่อง G2 จากนั้นในเซลล์ G3 ให้พิมพ์ว่า:

=G2 + E3 - F3

ลากสูตรนี้ลงมา เพื่อป้องกันไม่ให้สูตรแสดงผลรวมซ้ำๆ ในแถวว่างด้านล่างข้อมูลของคุณ ให้ซ้อนสูตรด้วยฟังก์ชัน IF ที่จะตรวจสอบว่าคอลัมน์วันที่ (A) ว่างเปล่าหรือไม่:

=IF(A3="", "", G2 + E3 - F3)

เคล็ดลับระดับโปร: เพื่อให้มั่นใจถึงความถูกต้องและป้องกันการพิมพ์ผิดในคอลัมน์รหัสบัญชีของคุณ ให้ตั้งค่าผังบัญชี (Chart of Accounts) ในแท็บแยกต่างหากและใช้ การตรวจสอบความถูกต้องของข้อมูล (Data Validation) เพื่อควบคุมการป้อนข้อมูล ผ่านเมนูแบบเลื่อนลง (Drop-down) วิธีนี้จะช่วยประหยัดเวลาในการแก้ปัญหาได้หลายชั่วโมงเมื่อถึงเวลาต้องจัดทำงบการเงิน

การสร้างงบการเงินแบบไดนามิก (งบกำไรขาดทุนและงบดุล)

เมื่อโครงสร้างสมุดบัญชีแยกประเภททั่วไปของคุณสมบูรณ์แล้ว การสร้างงบกำไรขาดทุน (Income Statement) และงบดุล (Balance Sheet) ก็จะกลายเป็นการรวบรวมข้อมูลตามรหัสบัญชี ฟังก์ชันที่มีประสิทธิภาพสูงสุดสำหรับงานนี้คือ SUMIFS

SUMIFS ช่วยให้คุณสามารถหาผลรวมของค่าต่างๆ ในช่วงข้อมูลได้ก็ต่อเมื่อตรงตามเงื่อนไขหลายข้อ (เช่น ต้องตรงกับรหัสบัญชีที่ระบุ และ ต้องอยู่ในช่วงวันที่ที่กำหนด) การเรียนรู้ การหาผลรวมแบบมีเงื่อนไขด้วย SUMIF และ SUMIFS เป็นสิ่งสำคัญอย่างยิ่งสำหรับการทำรายงานทางการเงินแบบอัตโนมัติ

ทีละขั้นตอน: การสร้างงบกำไรขาดทุนแบบอัตโนมัติ

1. ในแผ่นงาน (Sheet) ใหม่ ให้ระบุชื่อบัญชีรายได้และค่าใช้จ่ายของคุณในคอลัมน์ A และรหัสบัญชีที่สอดคล้องกันในคอลัมน์ B
2. ในเซลล์ C1 และ D1 ให้ใส่วันที่ของรอบระยะเวลารายงานของคุณ (เช่น วันที่เริ่มต้น: 2023-10-01, วันที่สิ้นสุด: 2023-10-31)
3. ในคอลัมน์ E (คอลัมน์ผลรวม) ให้ใช้ฟังก์ชัน SUMIFS เพื่อดึงผลรวมของฝั่งเดบิตหรือเครดิตสำหรับรหัสบัญชีนั้นๆ ในช่วงเวลาที่กำหนด

นี่คือไวยากรณ์สำหรับหาผลรวมของคอลัมน์ เครดิต (รายได้) จากแผ่นงานที่ชื่อ "GL" สำหรับรหัสบัญชี "4010" ในเดือนตุลาคม:

=SUMIFS(GL!$F:$F, GL!$C:$C, 4010, GL!$A:$A, ">="&$C$1, GL!$A:$A, "<="&$D$1)

มาดูส่วนประกอบของสูตรนี้กัน:

• GL!$F:$F: ช่วงข้อมูลที่เราต้องการหาผลรวม (คอลัมน์เครดิต)
• GL!$C:$C: ช่วงเงื่อนไขแรก (คอลัมน์รหัสบัญชี)
• 4010: รหัสบัญชีเฉพาะที่เรากำลังค้นหา
• GL!$A:$A: ช่วงเงื่อนไขที่สอง (คอลัมน์วันที่)
• ">="&$C$1: บอกให้ Excel ทราบว่าวันที่ต้องมากกว่าหรือเท่ากับวันที่เริ่มต้นของเรา
• "<="&$D$1: บอกให้ Excel ทราบว่าวันที่ต้องน้อยกว่าหรือเท่ากับวันที่สิ้นสุดของเรา

การปรับปรุงการทำงบกระทบยอดเงินฝากธนาคารให้รวดเร็วขึ้น

การทำงบกระทบยอดเงินฝากธนาคาร (Bank Reconciliation) คือกระบวนการตรวจสอบยอดคงเหลือในบันทึกทางบัญชีของนิติบุคคลให้ตรงกับข้อมูลที่สอดคล้องกันในรายการเดินบัญชีของธนาคาร (Bank Statement) Excel มีประโยชน์อย่างมากในการค้นหาข้อผิดพลาด เช็คที่สูญหาย หรือค่าธรรมเนียมธนาคารที่ซ้ำซ้อน

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

แม้ว่านักบัญชีหลายคนจะนิยมใช้ VLOOKUP แต่การเปลี่ยนมาใช้ วิธีการค้นหาด้วย INDEX MATCH จะมอบความยืดหยุ่นที่สูงกว่ามาก โดยเฉพาะอย่างยิ่งเมื่อค่าที่คุณใช้ค้นหา (เช่น หมายเลขเช็ค) ไม่ได้อยู่ในคอลัมน์แรกของตาราง

การใช้ EXACT และการจัดรูปแบบตามเงื่อนไข

หากคุณเรียงลำดับทั้งสองรายการตามวันที่และจำนวนเงินแล้ว คุณสามารถนำจำนวนเงินตามบัญชีมาลบด้วยจำนวนเงินตามธนาคารได้เลย ผลลัพธ์ที่เป็น 0 หมายความว่ายอดตรงกัน

=Book_Amount - Bank_Amount

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

การจัดการลูกหนี้และเจ้าหนี้การค้า (รายงานอายุหนี้)

กระแสเงินสดคือเส้นเลือดใหญ่ของทุกธุรกิจ การติดตามลูกหนี้การค้า (ผู้ที่ค้างชำระคุณ) และเจ้าหนี้การค้า (ผู้ที่คุณค้างชำระ) เป็นงานประจำวัน การสร้างรายงานอายุหนี้ (Aging Report) ใน Excel ช่วยให้คุณระบุได้ว่าใบแจ้งหนี้ใดเป็นยอดปัจจุบัน ค้างชำระ หรือล่าช้าอย่างรุนแรง

ในการสร้างรายงานอายุหนี้ คุณต้องคำนวณส่วนต่างระหว่างวันที่ปัจจุบันและวันครบกำหนดชำระของใบแจ้งหนี้ จากนั้นจัดกลุ่มตัวเลขดังกล่าวออกเป็นหมวดหมู่ (เช่น 0-30 วัน, 31-60 วัน, 61-90 วัน, 90 วันขึ้นไป)

วิธีทำ: การสร้างสูตรสำหรับรายงานอายุหนี้

สมมติว่าคอลัมน์ A มีหมายเลขใบแจ้งหนี้ คอลัมน์ B มีชื่อลูกค้า คอลัมน์ C มีวันครบกำหนดชำระ และคอลัมน์ D มียอดคงค้าง (Open Balance) ในคอลัมน์ E เราต้องการคำนวณจำนวนวันที่เกินกำหนดชำระ

=TODAY() - C2

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

=IF(E2<0, "Not Due", IF(E2<=30, "1-30 Days", IF(E2<=60, "31-60 Days", IF(E2<=90, "61-90 Days", "Over 90 Days"))))

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

5 สูตร Excel ที่จำเป็นสำหรับนักบัญชีทุกคน

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

1. EOMONTH (End of Month): สิ่งจำเป็นสำหรับการคาดการณ์รายการค้างรับค้างจ่ายและการสร้างส่วนหัวของแต่ละเดือน =EOMONTH(A2, 0) จะส่งคืนวันสุดท้ายของเดือนสำหรับวันที่ในเซลล์ A2 หากเปลี่ยนจาก 0 เป็น 1 จะส่งคืนวันสุดท้ายของเดือนถัดไป
2. EDATE: เหมาะอย่างยิ่งสำหรับการคำนวณวันครบกำหนดของเงินกู้หรือการลงทุน โดยจะบวกจำนวนเดือนที่ระบุเข้ากับวันที่ =EDATE(Start_Date, 12) จะบวกเพิ่ม 12 เดือนพอดี
3. PMT (Payment): ใช้คำนวณยอดผ่อนชำระสำหรับเงินกู้โดยพิจารณาจากการชำระเงินคงที่และอัตราดอกเบี้ยคงที่ มีความสำคัญมากสำหรับการตัดจำหน่ายหนี้สิน ไวยากรณ์: =PMT(rate, nper, pv)
4. SLN (Straight-Line Depreciation): ส่งคืนค่าเสื่อมราคาแบบเส้นตรงของสินทรัพย์สำหรับหนึ่งงวด ไวยากรณ์: =SLN(cost, salvage, life)
5. XLOOKUP: ฟังก์ชันใหม่ล่าสุดที่เข้ามาแทนที่ VLOOKUP และ INDEX MATCH สามารถค้นหาข้อมูลทางซ้าย กำหนดข้อความเริ่มต้นเมื่อพบข้อผิดพลาด และค้นหาจากล่างขึ้นบนได้ ขอแนะนำอย่างยิ่งหากคุณใช้งาน Microsoft 365

จัดการงานที่ซ้ำซากให้เป็นอัตโนมัติด้วย Power Query

การคัดลอกและวางข้อมูลจากซอฟต์แวร์บัญชีลงในเทมเพลต Excel ทุกเดือนเป็นงานที่น่าเบื่อและเสี่ยงต่อความผิดพลาดของมนุษย์ หากคุณพบว่าตัวเองต้องจัดรูปแบบไฟล์ CSV ที่ส่งออกจาก QuickBooks, Xero หรือธนาคารของคุณด้วยตนเองทุกเดือน ถึงเวลาอัปเกรดขั้นตอนการทำงานของคุณแล้ว

คุณสามารถใช้ Power Query เพื่อนำเข้าและแปลงข้อมูลแบบมือโปร Power Query ช่วยให้คุณสร้างการเชื่อมต่อไปยังไฟล์ข้อมูลดิบ (เช่น การดึงข้อมูล CSV รายเดือน) คุณสามารถตั้งค่ากฎต่างๆ เพื่อลบแถวบนสุดที่ไม่จำเป็นโดยอัตโนมัติ เปลี่ยนข้อความเป็นวันที่ เติมหมายเลขบัญชีในช่องว่าง (Fill Down) และยกเลิกการทำ Pivot คอลัมน์ (Unpivot) ในเดือนถัดไป คุณเพียงแค่วางไฟล์ CSV ใหม่ลงในโฟลเดอร์ กด "รีเฟรช" ใน Excel และขั้นตอนการจัดรูปแบบทั้งหมดของคุณก็จะถูกปรับใช้ในพริบตา

เขียนสูตรบัญชีได้เร็วขึ้นด้วย AI

การจดจำสูตรที่ซับซ้อนและมีการซ้อนกันหลายชั้นอาจเป็นเรื่องที่น่าหวั่นใจ แม้แต่สำหรับผู้เชี่ยวชาญด้านการเงินที่มีประสบการณ์ หากคุณพบว่าตัวเองกำลังดิ้นรนกับการจำไวยากรณ์ที่แน่นอนสำหรับการค้นหาข้อมูลที่ซับซ้อน ฟังก์ชัน IF ของกลุ่มอายุหนี้ หรือการคำนวณค่าเสื่อมราคาที่ยุ่งยาก เครื่องมืออย่าง GPTExcel สามารถช่วยคุณได้ เพียงแค่อธิบายความต้องการของคุณเป็นภาษาพูดธรรมดา เช่น "คำนวณค่าเสื่อมราคาแบบเส้นตรงสำหรับสินทรัพย์ในระยะเวลา 5 ปีโดยไม่สนใจมูลค่าซาก" จากนั้นรับสูตรที่ถูกต้องและใช้งานได้ทันที

การจับคู่ความรู้พื้นฐานที่แข็งแกร่งเกี่ยวกับโครงสร้าง Excel เข้ากับผู้ช่วย AI สมัยใหม่ จะช่วยให้คุณสร้างเทมเพลตทางบัญชีที่เชื่อถือได้และปราศจากข้อผิดพลาดในเวลาเพียงเสี้ยววินาที

คำถามที่พบบ่อย (Frequently Asked Questions)

ฉันจะป้องกันไม่ให้สูตรบัญชีของฉันถูกลบโดยไม่ตั้งใจได้อย่างไร?

คุณสามารถปกป้องเทมเพลตของคุณได้โดยใช้ฟีเจอร์ "ป้องกันแผ่นงาน" (Protect Sheet) ของ Excel ก่อนอื่น ให้คลุมดำเซลล์ที่อนุญาตให้ป้อนข้อมูล (เช่น รายละเอียดธุรกรรม) คลิกขวา เลือก "จัดรูปแบบเซลล์" (Format Cells) ไปที่แท็บ "การป้องกัน" (Protection) และนำเครื่องหมายถูกออกจาก "ล็อก" (Locked) จากนั้น ไปที่แท็บ "รีวิว" (Review) บน Ribbon แล้วคลิก "ป้องกันแผ่นงาน" (Protect Sheet) สูตรของคุณจะถูกล็อกไว้ แต่ผู้ใช้ยังคงสามารถป้อนข้อมูลได้

Excel สามารถทดแทนซอฟต์แวร์บัญชีเฉพาะทางอย่าง QuickBooks ได้ทั้งหมดหรือไม่?

แม้ว่าธุรกิจขนาดเล็กมากหรือธุรกิจใหม่จะสามารถใช้ Excel ในการติดตามรายรับและรายจ่ายพื้นฐานได้ แต่ก็ไม่แนะนำให้ใช้ทดแทนซอฟต์แวร์บัญชีเฉพาะทางอย่างถาวร ซอฟต์แวร์เฉพาะทางรับประกันว่ามีการปฏิบัติตามกฎการบัญชีคู่ (Double-entry) อย่างเคร่งครัด รักษาร่องรอยการตรวจสอบบัญชี (Audit Trail) ที่เข้มงวด และจัดการกับการทำรายงานภาษีที่ซับซ้อนได้ในตัว Excel เหมาะที่จะใช้เป็นส่วนเสริมในการวิเคราะห์และจัดทำรายงานสำหรับระบบบัญชีหลักของคุณมากกว่า

วิธีที่ดีที่สุดในการสรุปข้อมูลสมุดบัญชีแยกประเภททั่วไปจำนวนมากคืออะไร?

PivotTable เป็นวิธีที่มีประสิทธิภาพมากที่สุดในการสรุปข้อมูลบัญชีแยกประเภทนับพันแถว โดยการแทรก PivotTable คุณสามารถลาก "ชื่อบัญชี" (Account Name) ไปที่เขตข้อมูลแถว (Rows) ลาก "วันที่" (จัดกลุ่มตามเดือน) ไปที่เขตข้อมูลคอลัมน์ (Columns) และลาก "จำนวนเงิน" (Amount) ไปที่เขตข้อมูลค่า (Values) เพื่อสร้างสรุปข้อมูลทางการเงินแบบตารางไขว้ (Cross-tabulation) ได้ในทันทีโดยไม่ต้องเขียนสูตรเลยแม้แต่สูตรเดียว

ฉันจะหาการบันทึกรายการธุรกรรมที่ซ้ำซ้อนในสมุดบัญชีแยกประเภทได้อย่างไร?

วิธีที่เร็วที่สุดคือการใช้การจัดรูปแบบตามเงื่อนไข (Conditional Formatting) คลุมดำคอลัมน์ที่มีข้อมูลอ้างอิงธุรกรรมของคุณ (เช่น หมายเลขเช็คหรือรหัสใบแจ้งหนี้) ไปที่แท็บ "หน้าแรก" (Home) คลิก "การจัดรูปแบบตามเงื่อนไข" (Conditional Formatting) เลือก "กฎการเน้นเซลล์" (Highlight Cells Rules) และเลือก "ค่าที่ซ้ำกัน" (Duplicate Values) Excel จะทำการเน้นสีธุรกรรมใดๆ ที่ถูกป้อนข้อมูลมากกว่าหนึ่งครั้งในทันที