สูตร Excel สำคัญสำหรับงาน HR ฝ่ายบุคคล 9 สูตรที่ใช้บ่อย พร้อมตัวอย่าง
· อ่านประมาณ 8 นาที
งาน HR ต้องจัดการข้อมูลพนักงานจำนวนมาก ทั้งกำลังคน อายุงาน วันลา เงินเดือน และเอกสารต่าง ๆ ถ้าใช้ สูตร Excel สำหรับงาน HR ให้เป็น จะสรุปรายงานได้เร็วขึ้นมาก และลดความผิดพลาดจากการนับหรือคำนวณด้วยมือ บทความนี้รวม 9 สูตรสำคัญพร้อมตัวอย่างและภาพหน้าจอ ซึ่งเป็นเนื้อหาที่เรามักสอนในอบรม Excelและสอน Excelสำหรับฝ่ายบุคคล
ถ้ายังไม่แน่ใจพื้นฐาน อ่านสูตร Excel พื้นฐานที่ต้องรู้ก่อน
1. COUNTIFS นับจำนวนพนักงานตามเงื่อนไข
ใช้นับพนักงานตามแผนก สถานะ หรือประเภทการจ้าง พร้อมกันหลายเงื่อนไขได้
=COUNTIFS(แผนก,$A2,สถานะ,"ทำงานอยู่")

2. SUMIFS และ AVERAGEIFS สรุปเงินเดือนและค่าใช้จ่ายบุคลากร
=SUMIFS(เงินเดือน,แผนก,$A2)เงินเดือนรวมต่อแผนก=AVERAGEIFS(เงินเดือน,แผนก,$A2)เงินเดือนเฉลี่ยต่อแผนก
อ่านวิธีใช้ละเอียดที่SUMIFS และ COUNTIFS
3. DATEDIF คำนวณอายุงาน
นับอายุงานเป็นปีและเดือนจากวันเริ่มงานถึงวันที่อ้างอิง
=DATEDIF(B2,TODAY(),"Y")&" ปี "&DATEDIF(B2,TODAY(),"YM")&" เดือน"

DATEDIF เป็นฟังก์ชันที่ไม่แสดงในรายการช่วยเหลือของ Excel แต่ใช้งานได้ ดูตัวอย่างเพิ่มที่คำนวณอายุงานด้วย DATEDIF ถ้าแสดงปีเป็น พ.ศ. ตามค่าภูมิภาคของเครื่อง สูตรยังคำนวณได้ถูกต้อง
4. คำนวณวันลาคงเหลือและแจ้งสถานะด้วย IF
คงเหลือ = สิทธิ์ − ใช้ไป แล้วใช้ IF แจ้งเตือนคนที่ใกล้ใช้สิทธิ์หมด
=IF(D2=0,"ใช้หมดแล้ว",IF(D2<=2,"ใกล้หมด","ปกติ"))

ใช้ Conditional Formatting ระบายสีสถานะเพื่อให้เห็นภาพรวมในทันที ดูหลักการเพิ่มเติมที่IF และ IFS
5. NETWORKDAYS นับวันทำงานจริง
=NETWORKDAYS(วันเริ่มลา,วันสิ้นสุดลา,วันหยุด) นับวันทำงานโดยไม่รวมเสาร์-อาทิตย์ และวันหยุดที่ระบุไว้ในช่วงเซลล์ ใช้คำนวณจำนวนวันลาที่หักจริง หรือวันที่ผ่านงานในรอบประเมิน ถ้าบริษัทมีวันหยุดต่างจากเสาร์-อาทิตย์ใช้ NETWORKDAYS.INTL
6. คำนวณประกันสังคมด้วย MIN
ใช้ MIN จำกัดฐานค่าจ้างไม่ให้เกินเพดาน แล้วคูณอัตรา
=MIN(D2,$H$1)*$H$2
ให้เก็บเพดานค่าจ้างและอัตราไว้ในเซลล์พารามิเตอร์ ตัวอย่างข้างบนใช้เพดาน 15,000 บาท อัตรา 5% เพื่อประกอบการอธิบายเท่านั้น เพดานและอัตราจริงเปลี่ยนตามประกาศ ต้องตรวจกับสำนักงานประกันสังคมก่อนใช้จริง การแยกเป็นเซลล์พารามิเตอร์ทำให้แก้ค่าเดียวแล้วทั้งไฟล์ปรับตาม
7. คำนวณ OT และวันที่
ค่าล่วงเวลาและการคำนวณวันที่เป็นงานที่ HR ต้องทำทุกเดือน สูตรรายละเอียดอยู่ในสูตรหา OT ใน Excel และสูตรวันที่ใน Excel โดยอัตราค่าล่วงเวลาต้องเป็นไปตามกฎหมายแรงงานและข้อบังคับของบริษัท
8. ตรวจข้อมูลพนักงานซ้ำด้วย COUNTIF
ก่อนนำเข้าข้อมูลหรือจ่ายเงินเดือน ใช้ =COUNTIF($A$2:$A$500,A2)>1 เช็กรหัสพนักงานหรือเลขบัตรประชาชนที่ซ้ำ แล้วระบายสีแถวที่ซ้ำด้วย Conditional Formatting เพื่อแก้ก่อนส่งต่อ
9. VLOOKUP / XLOOKUP ดึงข้อมูลพนักงานจากรหัส
ใช้ดึงชื่อ แผนก หรือเงินเดือนจากรหัสพนักงาน เมื่อต้องทำรายงานจากหลายชีต ดูVLOOKUP ข้ามชีต และถ้าขึ้น #N/A อ่านVLOOKUP ขึ้น #N/A แก้อย่างไร
เคล็ดลับสำหรับไฟล์ HR
- ข้อมูลส่วนบุคคลต้องปกป้อง ใส่รหัสผ่านไฟล์ (File → Info → Protect Workbook) จำกัดคนเข้าถึง และอย่าส่งไฟล์เงินเดือนทางแชตทั่วไป ตามหลักการคุ้มครองข้อมูลส่วนบุคคล (PDPA)
- ใช้ Dropdown List ในช่องสถานะและแผนก เพื่อไม่ให้พิมพ์สะกดต่างกัน ดูทำ Dropdown ด้วย Data Validation
- แยกชีตข้อมูลดิบ ตารางพารามิเตอร์ และรายงานออกจากกัน
- เก็บสำเนาไฟล์ทุกงวดก่อนแก้ไข
สรุป
9 สูตรสำคัญของงาน HR คือ COUNTIFS, SUMIFS/AVERAGEIFS, DATEDIF, IF, NETWORKDAYS, MIN สำหรับเพดานประกันสังคม, สูตร OT/วันที่, COUNTIF ตรวจซ้ำ และ VLOOKUP/XLOOKUP ใช้ร่วมกันแล้วจะได้รายงานบุคลากรที่ตรวจสอบง่ายและอัปเดตเองได้
หมายเหตุ: ตัวเลขและชื่อในภาพเป็นข้อมูลตัวอย่างเพื่อการเรียนรู้ ไม่ใช่ข้อมูลจริงของบริษัทใด
ถ้าอยากให้ทีมใช้ Excel ได้คล่องกับข้อมูลจริงของงานเอง Wise Success มีอบรม Excelและสอน Excelสำหรับฝ่ายบุคคล หลักสูตรExcel for HR ทั้งแบบPublic TrainingและIn-house สำหรับองค์กร หรือติดต่อเราเพื่อปรึกษาหัวข้อให้ตรงกับทีม
- #สูตร Excel HR
- #สูตร excel
- #สอน Excel
- #อบรม Excel
- #DATEDIF
- #COUNTIFS
- #Excel HR