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

สูตร Excel สำคัญสำหรับงาน HR ฝ่ายบุคคล 9 สูตรที่ใช้บ่อย พร้อมตัวอย่าง

· อ่านประมาณ 8 นาที

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

ถ้ายังไม่แน่ใจพื้นฐาน อ่านสูตร Excel พื้นฐานที่ต้องรู้ก่อน

1. COUNTIFS นับจำนวนพนักงานตามเงื่อนไข

ใช้นับพนักงานตามแผนก สถานะ หรือประเภทการจ้าง พร้อมกันหลายเงื่อนไขได้

=COUNTIFS(แผนก,$A2,สถานะ,"ทำงานอยู่")
ตัวอย่างตาราง Excel สรุปจำนวนพนักงาน เงินเดือนรวม และเงินเดือนเฉลี่ยรายแผนก ด้วย COUNTIFS SUMIFS และ AVERAGEIFS
สรุปกำลังคนและเงินเดือนรายแผนก (ตัวอย่างข้อมูล)

2. SUMIFS และ AVERAGEIFS สรุปเงินเดือนและค่าใช้จ่ายบุคลากร

  • =SUMIFS(เงินเดือน,แผนก,$A2) เงินเดือนรวมต่อแผนก
  • =AVERAGEIFS(เงินเดือน,แผนก,$A2) เงินเดือนเฉลี่ยต่อแผนก

อ่านวิธีใช้ละเอียดที่SUMIFS และ COUNTIFS

3. DATEDIF คำนวณอายุงาน

นับอายุงานเป็นปีและเดือนจากวันเริ่มงานถึงวันที่อ้างอิง

=DATEDIF(B2,TODAY(),"Y")&" ปี "&DATEDIF(B2,TODAY(),"YM")&" เดือน"
ตัวอย่างตาราง Excel คำนวณอายุงานของพนักงานด้วย DATEDIF และคำนวณเงินสมทบประกันสังคม
คำนวณอายุงานด้วย DATEDIF และประกันสังคม (ตัวอย่างข้อมูล)

DATEDIF เป็นฟังก์ชันที่ไม่แสดงในรายการช่วยเหลือของ Excel แต่ใช้งานได้ ดูตัวอย่างเพิ่มที่คำนวณอายุงานด้วย DATEDIF ถ้าแสดงปีเป็น พ.ศ. ตามค่าภูมิภาคของเครื่อง สูตรยังคำนวณได้ถูกต้อง

4. คำนวณวันลาคงเหลือและแจ้งสถานะด้วย IF

คงเหลือ = สิทธิ์ − ใช้ไป แล้วใช้ IF แจ้งเตือนคนที่ใกล้ใช้สิทธิ์หมด

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

ใช้ 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 ·

สูตร Excel สำหรับแผนกจัดซื้อ 8 สูตร เปรียบเทียบราคา ใบสั่งซื้อ และจุดสั่งซื้อ

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

อ่านต่อ →

เทคนิค Excel ·

สิ่งที่ต้องรู้สำหรับการใช้ MS Excel สำหรับผู้เริ่มเข้าทำงาน

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

อ่านต่อ →

เทคนิค Excel ·

สูตร Excel สำคัญสำหรับพนักงานฝ่ายการตลาด 8 สูตร วัดผลแคมเปญ CTR CPL ROAS

รวม 8 สูตร Excel ที่นักการตลาดใช้วัดผลแคมเปญ คำนวณ CTR CPC CPL ROAS เปรียบเทียบเติบโตรายเดือน และวิเคราะห์ funnel ลูกค้า พร้อมตัวอย่างและภาพหน้าจอ

อ่านต่อ →

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

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

☎ โทรเลย LINE @wisesuccess

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