Excel สำหรับ HR: ข้อมูลพนักงานและการวิเคราะห์ข้อมูล

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

การตั้งค่าหน้าชีตข้อมูลหลักพนักงาน (Employee Master Data)

ระบบ HR ใน Excel ทุกระบบเริ่มต้นด้วยชีตข้อมูลหลักของพนักงานที่สะอาดสะอ้านและมีโครงสร้างที่ดี ให้คิดว่านี่คือแหล่งข้อมูลความจริงเพียงหนึ่งเดียวของคุณ (Single source of truth) โดยแต่ละแถวจะแทนพนักงานหนึ่งคน และแต่ละคอลัมน์จะแทนคุณลักษณะหนึ่งอย่าง

คอลัมน์แนะนำสำหรับชีตข้อมูลหลักของคุณ:

• Employee ID (รหัสพนักงาน) — ตัวระบุที่ไม่ซ้ำกัน (เช่น EMP-001)
• Full Name (ชื่อ-นามสกุล)
• Department (แผนก)
• Job Title (ตำแหน่งงาน)
• Start Date (วันที่เริ่มงาน)
• Employment Type (ประเภทการจ้างงาน: เต็มเวลา, พาร์ทไทม์, สัญญาจ้าง)
• Manager Name (ชื่อผู้จัดการ)
• Annual Salary (เงินเดือนประจำปี)
• Status (สถานะ: พนักงานปัจจุบัน, ลาออก, พักงาน)

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

ตั้งชื่อตารางของคุณ (แทรก (Insert) → ตาราง (Table) แล้วตั้งชื่อเช่น tblEmployees) ตารางที่มีการตั้งชื่อจะขยายโดยอัตโนมัติเมื่อคุณเพิ่มแถว และทำให้สูตรของคุณอ่านง่ายขึ้นมาก

การคำนวณอายุงานและระยะเวลาการทำงาน

หนึ่งในการคำนวณของ HR ที่พบบ่อยที่สุดคืออายุงานของพนักงาน ฟังก์ชัน DATEDIF สามารถจัดการเรื่องนี้ได้อย่างยอดเยี่ยม:

=DATEDIF(B2, TODAY(), "Y") & " years, " & DATEDIF(B2, TODAY(), "YM") & " months"

โดยที่ B2 คือวันที่เริ่มงานของพนักงาน สูตรนี้จะส่งคืนข้อความที่อ่านง่ายอย่างเช่น 3 years, 7 months หากคุณต้องการเพียงจำนวนปีเต็มเพื่อใช้ในการแบ่งกลุ่ม:

=DATEDIF(B2, TODAY(), "Y")

จากนั้นคุณสามารถจัดประเภทพนักงานออกเป็นกลุ่มอายุงานได้โดยใช้ ฟังก์ชัน IF พร้อมการทดสอบตรรกะแบบซ้อนทับ (Nested IF):

=IF(E2<1,"New Hire",IF(E2<3,"Junior",IF(E2<7,"Mid-Level","Senior")))

โดยที่ E2 คือค่าอายุงานในหน่วยปี การแบ่งกลุ่มเหล่านี้มีประโยชน์อย่างมากสำหรับรายงานจำนวนพนักงานและการวิเคราะห์การรักษาพนักงาน

การสร้างเทมเพลตระบบบันทึกเวลาเข้างาน

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

พนักงาน	1-Jun	2-Jun	3-Jun	…	มาทำงานรวม	ขาดงานรวม	% การมาทำงาน
Jane Doe	P	P	A	…	=COUNTIF(B2:AF2,"P")	=COUNTIF(B2:AF2,"A")	=AG2/22
John Smith	P	L	P	…	=COUNTIF(B3:AF3,"P")	=COUNTIF(B3:AF3,"A")	=AG3/22

รหัสสถานะที่ใช้ทั่วไป: P = มาทำงาน (Present), A = ขาดงาน (Absent), L = ลา (Leave), WFH = ทำงานที่บ้าน (Work From Home) ฟังก์ชัน COUNTIF จะนับแต่ละรหัสแยกกัน ทำให้คุณได้ข้อมูลสรุปของพนักงานแต่ละคนครบถ้วน ให้นำจำนวนวันที่มาทำงานทั้งหมดหารด้วยจำนวนวันทำงานในเดือนนั้น (โดยทั่วไปคือ 22) เพื่อหาเปอร์เซ็นต์การมาทำงาน จากนั้นจัดรูปแบบคอลัมน์นั้นให้เป็นเปอร์เซ็นต์ที่มีทศนิยมหนึ่งตำแหน่ง

ใช้ Conditional Formatting (การจัดรูปแบบตามเงื่อนไข) เพื่อแสดงข้อมูลการเข้างานด้วยสีสันต่างๆ — สีแดงสำหรับการขาดงาน สีเขียวสำหรับการมาทำงานครบ — เพื่อให้ผู้จัดการสามารถมองเห็นแนวโน้มได้อย่างรวดเร็ว

การวิเคราะห์เงินเดือนด้วย SUMIF และ AVERAGEIF

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

=SUMIF(tblEmployees[Department], "Marketing", tblEmployees[Annual Salary])
=AVERAGEIF(tblEmployees[Department], "Marketing", tblEmployees[Annual Salary])
=COUNTIF(tblEmployees[Department], "Marketing")

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

=SUMIF(tblEmployees[Department], H2, tblEmployees[Annual Salary])

โดยที่ H2 คือรายการดรอปดาวน์ที่มีชื่อแผนกต่างๆ รูปแบบนี้ถือเป็นแกนหลักของมินิแดชบอร์ดสำหรับการวิเคราะห์ข้อมูล HR ด้วยตนเอง (Self-service)

ระบบการให้คะแนนการประเมินผลงาน

ชีตประเมินผลงานที่มีโครงสร้างชัดเจนจะเก็บรวบรวมคะแนนความสามารถในหลายๆ ด้าน และคำนวณคะแนนรวมให้อัตโนมัติ

คอลัมน์สมรรถนะ (Competency) ที่แนะนำ: การสื่อสาร, การทำงานเป็นทีม, ทักษะทางเทคนิค, ความเป็นผู้นำ, การส่งมอบงาน ให้ประเมินแต่ละด้านในระดับ 1–5 และคำนวณคะแนนรวมแบบถ่วงน้ำหนัก (Weighted score):

=SUMPRODUCT(C2:G2, $C$1:$G$1) / SUM($C$1:$G$1)

โดยที่แถว 1 เก็บค่าน้ำหนักของแต่ละสมรรถนะ (เช่น การสื่อสาร = 2, ทักษะทางเทคนิค = 3 เป็นต้น) และแถว 2 เก็บผลคะแนนของพนักงานหนึ่งคน ฟังก์ชัน SUMPRODUCT จะคูณคะแนนแต่ละตัวด้วยน้ำหนักของมัน นำผลลัพธ์มารวมกัน และหารด้วยน้ำหนักรวมทั้งหมด — ทำให้คุณได้ค่าเฉลี่ยถ่วงน้ำหนักที่แท้จริงโดยไม่ต้องเขียนสูตรซ้อนกันให้ซับซ้อน

กำหนดกลุ่มผลการประเมินอัตโนมัติ:

=IF(H2>=4.5,"Outstanding",IF(H2>=3.5,"Exceeds Expectations",IF(H2>=2.5,"Meets Expectations",IF(H2>=1.5,"Needs Improvement","Unsatisfactory"))))

โดยที่ H2 คือคะแนนถ่วงน้ำหนัก ใช้ Conditional Formatting เพื่อใส่รหัสสีให้กับคอลัมน์ผลการประเมิน — สิ่งนี้จะช่วยให้ข้อมูลสรุปการประเมินอ่านได้ง่ายขึ้นมากเมื่อนำเสนอในที่ประชุม

การค้นหาข้อมูลพนักงานด้วย INDEX MATCH

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

การดึงข้อมูลตำแหน่งงานโดยใช้รหัสพนักงาน:

=INDEX(tblEmployees[Job Title], MATCH(A2, tblEmployees[Employee ID], 0))

การดึงข้อมูลเงินเดือนด้วยชื่อ (มีประโยชน์ในหน้าค้นหาแบบด่วน):

=INDEX(tblEmployees[Annual Salary], MATCH(B5, tblEmployees[Full Name], 0))

ลองนำวิธีนี้ไปใช้ร่วมกับหน้าแผงควบคุมการค้นหาแบบง่ายๆ ในอีกชีตหนึ่ง เพื่อให้เจ้าหน้าที่ HR สามารถพิมพ์ชื่อแล้วดึงข้อมูลโปรไฟล์แบบเต็มของพนักงานคนนั้นขึ้นมาจากชีตข้อมูลหลักได้ทันที — โดยไม่ต้องเลื่อนหาหรือค้นหาเองด้วยสายตา

การสร้างแดชบอร์ด HR ด้วย PivotTable

เมื่อข้อมูลหลักของคุณสะอาดและมีความสอดคล้องกันแล้ว PivotTable คือวิธีที่เร็วที่สุดในการสรุปข้อมูล HR แทรก PivotTable จากตารางข้อมูลหลักพนักงานของคุณ แล้วสำรวจการสรุปข้อมูลที่มีประโยชน์เหล่านี้:

• จำนวนพนักงานตามแผนก: แถว (Rows) = แผนก, ค่า (Values) = COUNT ของ รหัสพนักงาน
• เงินเดือนเฉลี่ยตามตำแหน่งงาน: แถว (Rows) = ตำแหน่งงาน, ค่า (Values) = AVERAGE ของ เงินเดือนประจำปี
• จำนวนพนักงานตามประเภทการจ้างงาน: แถว (Rows) = ประเภทการจ้างงาน, ค่า (Values) = COUNT ของ รหัสพนักงาน
• การกระจายตัวของอายุงาน: แถว (Rows) = กลุ่มอายุงาน (คอลัมน์ที่มาจากสูตร IF), ค่า (Values) = COUNT ของ รหัสพนักงาน

จับคู่ PivotTable แต่ละอันเข้ากับแผนภูมิ — แผนภูมิแท่งสำหรับเปรียบเทียบจำนวนพนักงาน, แผนภูมิวงกลมสำหรับดูสัดส่วนประเภทการจ้างงาน เชื่อมต่อ PivotTable หลายๆ อันด้วย Slicer อันเดียว (แทรก (Insert) → Slicer) เพื่อให้เมื่อคุณคลิกเลือกแผนก ก็จะทำการกรองข้อมูลในแผนภูมิทั้งหมดพร้อมกัน นี่คือรากฐานในการ สร้างแดชบอร์ด HR แบบไดนามิกใน Excel ที่ใช้งานได้จริงและมีประโยชน์อย่างยิ่ง

อัตราการลาออกและตัวชี้วัดสำคัญของ HR

การติดตามอัตราการลาออกโดยสมัครใจ (Voluntary turnover) เป็นสิ่งสำคัญสำหรับการวางแผนกำลังคน สร้างสมุดบันทึกการพ้นสภาพพนักงาน (Terminations log) ง่ายๆ ที่มีคอลัมน์: รหัสพนักงาน, ชื่อ, แผนก, วันที่พ้นสภาพ, เหตุผล (ลาออกเอง / ให้ออก)

สูตรคำนวณอัตราการลาออกโดยสมัครใจรายเดือน:

=COUNTIFS(tblTerminations[Reason],"Voluntary",tblTerminations[Month],B1) / tblEmployees_Count * 100

โดยที่ B1 คือเดือนที่เลือก และ tblEmployees_Count คือช่วงที่มีการตั้งชื่อไว้สำหรับเก็บข้อมูลจำนวนพนักงานทั้งหมด การนำข้อมูลนี้มาพล็อตกราฟเส้นตลอด 12 เดือน จะช่วยให้ผู้บริหารเห็นภาพรวมของแนวโน้มการรักษาพนักงานได้อย่างชัดเจน โดยไม่ต้องพึ่งซอฟต์แวร์ HR เฉพาะทางเลย

ตัวชี้วัดอื่นๆ ที่ควรติดตามในแดชบอร์ดเดียวกัน:

• จำนวนวันเฉลี่ยในการหาคนมาทำงาน (จากบันทึกการประกาศรับสมัครงาน)
• อัตราการขาดงานแยกตามแผนก (จากระบบบันทึกเวลาเข้างาน)
• ชั่วโมงการฝึกอบรมต่อพนักงาน (จากชีตบันทึกการฝึกอบรม)
• การกระจายตัวของคะแนนประเมินผลงาน (จากข้อมูลการประเมิน)

การทำงานอัตโนมัติสำหรับรายงาน HR ที่ทำซ้ำๆ

รายงานจำนวนพนักงานรายเดือน ข้อมูลสรุปการเข้างาน และชีตคำนวณต้นทุนเงินเดือน มักจะมีโครงสร้างแบบเดิมทุกเดือน แทนที่จะต้องมาสร้างใหม่ด้วยตัวเองทุกครั้ง ให้พิจารณาทำเป็นระบบอัตโนมัติ การสร้างระบบอัตโนมัติใน Excel ด้วย Power Automate สามารถตั้งค่าให้ระบบสร้างรายงาน ส่งการแจ้งเตือนทางอีเมลเมื่อเปอร์เซ็นต์การเข้างานต่ำกว่าเกณฑ์ หรือคัดลอกชีตที่เสร็จสมบูรณ์แล้วไปยัง SharePoint ได้อัตโนมัติ — ทั้งหมดนี้ทำได้โดยไม่ต้องเขียนโค้ดเลยแม้แต่บรรทัดเดียว

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

การใช้ AI ช่วยให้เขียนสูตร HR ได้เร็วขึ้น

การเขียนสูตร HR ที่ซับซ้อน — โดยเฉพาะอย่างยิ่ง ฟังก์ชัน IF แบบซ้อนทับ (Nested IFs), โมเดลคำนวณคะแนนด้วย SUMPRODUCT หรือ COUNTIFS แบบหลายเงื่อนไข — อาจใช้เวลานานและเสี่ยงต่อการเกิดข้อผิดพลาด หากคุณรู้สึกติดขัด คุณสามารถอธิบายสิ่งที่คุณต้องการเป็นภาษาธรรมดา และรับสูตรที่พร้อมใช้งานได้ทันทีด้วย GPTExcel ตัวอย่างเช่น: "คำนวณคะแนนประเมินผลงานเฉลี่ยแบบถ่วงน้ำหนัก โดยที่ค่าน้ำหนักสมรรถนะอยู่ในแถว 1 และคะแนนอยู่ใน C2:G2" — แล้วสูตร SUMPRODUCT ที่ถูกต้องจะปรากฏขึ้นมาทันที พร้อมให้คุณคัดลอกไปวางได้เลย

คุณยังสามารถเรียนรู้เพิ่มเติมเกี่ยวกับ การวิเคราะห์ข้อมูลด้วย AI ใน Excel เพื่อต่อยอดการทำงาน — ช่วยค้นหารูปแบบ (Patterns) ในข้อมูล HR ของคุณที่อาจถูกมองข้ามหากวิเคราะห์ด้วยคนเพียงอย่างเดียว

คำถามที่พบบ่อย (FAQ)

สูตร Excel ที่ดีที่สุดในการคำนวณอายุงานพนักงานคืออะไร?

ใช้ DATEDIF(start_date, TODAY(), "Y") เพื่อหาจำนวนปีเต็มที่ทำงาน สำหรับผลลัพธ์ที่ละเอียดขึ้นโดยแสดงทั้งจำนวนปีและเดือน ให้รวม DATEDIF สองคำสั่งเข้าด้วยกัน: =DATEDIF(B2,TODAY(),"Y") & " yrs " & DATEDIF(B2,TODAY(),"YM") & " mo" สูตรนี้จะอัปเดตตัวเองอัตโนมัติทุกครั้งที่เปิดไฟล์

ฉันจะติดตามเวลาเข้างานของพนักงานหลายคนใน Excel ได้อย่างไร?

สร้างชีตรายเดือนโดยนำรายชื่อพนักงานเรียงในแถวและวันที่เรียงในคอลัมน์ ใส่รหัสสถานะ (P, A, L) ลงในแต่ละเซลล์ ใช้ฟังก์ชัน COUNTIF เพื่อรวมรหัสแต่ละสถานะของพนักงานแต่ละคน และใช้ COUNTIFS เพื่อสรุปผลตามแผนก ใช้ Conditional Formatting เพื่อไฮไลต์การขาดงานด้วยสีแดงเพื่อให้มองเห็นและตรวจสอบได้อย่างรวดเร็ว

Excel สามารถใช้แทนซอฟต์แวร์ HR โดยเฉพาะได้หรือไม่?

สำหรับทีมขนาดเล็กไปจนถึงขนาดกลาง (ที่มีพนักงานไม่เกินหลักร้อย) Excel สามารถใช้จัดการงาน HR หลักๆ ได้อย่างมีประสิทธิภาพ ไม่ว่าจะเป็น ข้อมูลประวัติพนักงาน การเข้างาน การประเมินผลงาน และการวิเคราะห์ข้อมูลเบื้องต้น แต่สำหรับองค์กรขนาดใหญ่ที่มีความต้องการด้านเงินเดือน สวัสดิการ หรือข้อกำหนดทางกฎหมายที่ซับซ้อน ซอฟต์แวร์ระบบสารสนเทศทรัพยากรบุคคล (HRIS) น่าจะเหมาะสมกว่า — อย่างไรก็ตาม Excel ก็ยังคงเป็นเครื่องมืออันล้ำค่าสำหรับการวิเคราะห์และการทำรายงานแบบเฉพาะกิจ (Ad-hoc) ควบคู่ไปกับระบบเหล่านั้นอยู่ดี

ฉันจะปกป้องข้อมูลพนักงานที่เป็นความลับในไฟล์ HR Excel ได้อย่างไร?

ใช้การปกป้องแผ่นงาน (รีวิว (Review) → ป้องกันแผ่นงาน (Protect Sheet)) เพื่อล็อกเซลล์ที่มีสูตร ในขณะที่ยังคงให้เซลล์สำหรับการป้อนข้อมูลสามารถแก้ไขได้ ใช้การป้องกันด้วยรหัสผ่านในระดับสมุดงาน (ไฟล์ (File) → ข้อมูล (Info) → ป้องกันสมุดงาน (Protect Workbook)) เพื่อจำกัดสิทธิ์ในการเปิดไฟล์ สำหรับคอลัมน์ที่เป็นข้อมูลเงินเดือน ควรพิจารณาซ่อนและป้องกันชีตเหล่านั้นแยกต่างหาก และแชร์เฉพาะหน้าสรุปข้อมูลให้กับผู้จัดการดูแทนการส่งไฟล์ข้อมูลหลักทั้งหมดไป