สูตรที่จำเป็นและควรรู้สำหรับงาน HR

สูตรการหาอายุของพนักงาน หรืออายุงาน

ฟังก์ชั่น DATEDIF สำหรับ Excel

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

รูปแบบฟังก์ชั่น

                        DATEDIF(start_date,end_date,type)

Start_date      วันที่เริ่มต้น(วันเกิด, วันเริ่มงาน)

End_date        วันที่สิ้นสุด (ส่วนใหญ่จะใช้วันปัจจุบัน)

Type               รูปแบบการแสดงผล มีดังนี้

o   “Y”                 นับระยะเป็นปี

o   “M”                                นับระยะเป็นเดือน

o   “D”                 นับระยะเป็นวัน

o   “YM”             นับระยะเป็นเดือนแบบปีชนปี

o   “YD”              นับระยะเป็นวันแบบปีชนปี

o   “MD”             นับระยะเป็นวันแบบเดือนชนเดือน

วิธีการหาอายุงานด้วยฟังก์ชั่น DATEDIF

1.               จากตัวอย่างในตาราง ที่ตำแหน่งวันที่ปัจจุบัน เราควรใช้ฟังก์ชัน TODAY( ) เพื่อให้โปรแกรมแสดงวันที่ปัจจุบันของ เครื่องทุกครั้งที่เปิดใช้งาน  ดังนั้นให้พิมพ์ =TODAY()  ที่เซลล์ C1

2.               ใส่วันที่เริ่มงาน ให้รูปแบบวันที่ ตรงกับรูปแบบวันที่ปัจจุบัน (C1)

3.               ในช่องหาอายุงาน  ช่องปี(D3) ให้ใส่สูตร =DATEDIF(C3,$C$1,”Y”) แล้วกด Enter

4.               ในช่องหาอายุงาน ช่องเดือน(E3) ให้ใส่สูตร =DATEDIF(C3,$C$1,”YM”) แล้วกด Enter

5.               ในช่องหาอายุงาน ช่องวัน (F3) ให้ใส่สูตร =DATEDIF(C3,$C$1,”MD”) แล้วกด Enter

6.               เลือกช่องเซลล์ D3:F10  แล้วคัดลอกสูตรลงมา

หากต้องการแสดงอายุงานเป็นคำอ่าน ก็สามารถทำได้ด้วยการเชื่อมเซลล์ด้วยเครื่องหมาย & ในสูตร ดังนี้

ที่ตำแหน่ง G3  ใส่สูตร =D3&D$2&E3&E$2&F3&F$2  กด Enter   ผลลัพธ์ที่ได้จะเป็น   3ปี8เดือน2วัน  ซึ่งดูแล้วไม่สวย  แก้ไขด้วยการ ใส่เคาะวรรคด้านหน้าและด้านหลัง “ปี”(D2)   “เดือน”(E2) และ “วัน”(F2)  ก็จะได้ผลลัพธ์ ตามรูปด้านล่าง

สูตรการคำนวณชั่วโมงเวลาทำงาน และคำนวณ OT

จากรูปตัวอย่างสิ่งที่เราต้องการหา คือ เวลาสาย, เวลา OT ,หักมาสาย , ค่า OT และสรุปเงินเดือน

รูปแบบฟังก์ชั่น HOUR  

                                HOUR(serial_number)

รูปแบบฟังก์ชั่น MINUTE

                                MINUTE(seria_number)

                Serial_number  คือ เวลาที่มีค่าชั่วโมงหรือค่านาทีที่ต้องการค้นหาโดยเวลาอาจอยู่ในรูปของข้อความในเครื่องหมายอัญประกาศ เช่น “6:45 PM” หรือเป็นตัวเลขทศนิยม เช่น 0.78125 ซึ่งแทนเวลา 6:45 นาฬิกา หรือผลลัพธ์จากสูตรหรือฟังก์ชั่นอื่น เช่น TIMEVALUE(“6:45 PM”)

·       วิธีการคำนวณหาเวลาเข้างานสาย และคำนวณเวลา OT

1.               ก่อนอื่นเราต้องกำหนดเวลาการเข้างานสายก่อน ในตัวอย่างนี้คือ  9:15 นาฬิกา

2.               คลิกตรงตำแหน่งเซลล์ที่ต้องการคำนวณ (F6)

3.               ใช้ฟังก์ชัน IF เพื่อทำการเปรียบเทียบ Time-In ว่าน้อยกว่าหรือมากกว่า เวลาเข้างานสาย หรือไม่ ถ้าน้อยกว่าจริงให้  แสดงค่า 0 (ไม่สาย)  หากไม่จริงให้ทำการคำนวณเวลาเข้างานสายโดย เอาเวลาเข้างาน ลบกับเวลาเข้าสาย 9:15  เขียนได้ดังนี้

        =IF(B6<$I$1,0,(B6-$I$1))

** หากต้องการ Copy โดยการลากสูตรลงมาเราต้องทำการตรึงค่าโดย  $I$1

4.               การคำนวณหา เวลา OT  คลิกตรงตำแหน่งเซลล์ที่ต้องการคำนวณ (G6)

5.               สามารถทำได้โดยการเอา  เวลา OT  Time-Out  ลบด้วย เวลา OT Time-In ได้เลย เพราะเมื่อใส่เครื่องหมายคั่นระหว่าง Hour  กับ Minute  ด้วย  :  โปรแกรมจะทำการคำนวณให้อัตโนมัติด้วยตัวเลขฐาน 60  

·        วิธีคำนวณหาจำนวนเงิน เข้างานสาย และคำนวณค่า OT

1.               ก่อนอื่นต้องหาค่าตอบแทนต่อชั่วโมงเสียก่อน  คำนวณยอดเงิน เข้างานสาย  คลิกเลือกตรงตำแหน่งเซลล์ที่ต้องการคำนวณ (H6)

2.               เพราะค่าตอบแทนที่ได้เป็นชั่วโมง เราจึงต้องแยกจำนวนชั่วโมงและเศษนาทีออกมา เพื่อเอาจำนวนชั่วโมงเต็มไปคูณกับค่าตอบแทนต่อชั่วโมง ส่วนเศษของนาทีก็เอามาหาสัดส่วนค่าตอบแทน (1 ชั่วโมงมี 60 นาที)โดยเอาค่าตอบแทนต่อชั่วโมงหารด้วย 60 แล้วจึงคูณนาที  แล้วจึงนำผลลัพธ์มารวมกัน

3.               เขียนสูตรได้ดังนี้    =(HOUR(F6)*$G$2)+(MINUTE(F6)*($G$2/60))

4.               ต้องทำการตรึงค่าตอบแทนรายชั่วโมงเพราะเป็นค่าคงที่  แล้วจึงลากสูตร copy ลงมาได้

5.               การคำนวณหาค่า OT ก็ทำเช่นเดียวกัน  เขียนสูตรได้ดังนี้       =(HOUR(G6)*$G$2)+(MINUTE(G6)*($G$2/60))

สูตรการหาจำนวนวันสาย, ลา และขาดงาน

ฟังก์ชั่น  COUNTIF

                เป็นฟังก์ชั่นส่งกลับจำนวนเซลทั้งหมดที่ไม่ใช่ค่าว่างที่อยู่ในช่วงเซลที่ระบุ โดยกำหนดเงื่อนไขในการนับจำนวน

รูปแบบฟังก์ชั่น

                COUNTIF(range,criteria)

range                       อ้างอิงไปยังช่วงเซลที่ต้องการนำมานับจำนวนข้อมูลที่ไม่ใช่ค่าว่าง ตามเงื่อนไขที่กำหนดใน criteria

criteria                   เงื่อนไขที่อยู่ในรูปของตัวเลข ข้อความ หรือนิพจน์ เพื่อใช้เป็นตัวกำหนดว่าเซลใดบ้างที่จะถูกนับหรือไม่ถูกนับ

วิธีการหาจำนวนวันที่มาสาย

1.               คลิกตรงตำแหน่งเซลล์ที่เราต้องการให้แสดงผลลัพธ์ จากตัวอย่าง คือ  H43

2.               พิมพ์    =COUNTIF(range,   ช่วงข้อมูลที่เราต้องการนับ คือ คอลัมน์ “หักมาสาย (บาท)”   H6:H36, 

3.               criteria คือ กำหนดเงื่อนไขในการนับจำนวน ให้เลือกนับเฉพาะเซลล์ที่มีค่า มากกว่า 0

4.               สูตรที่ได้คือ  =COUNTIF(H6:H36,(">0"))

วิธีการหาจำนวนวันที่ขาด ลาป่วย ลากิจ

1.               คลิกตรงตำแหน่งเซลล์ที่เราต้องการให้แสดงผลลัพธ์ จากตัวอย่าง คือ  ขาด H40 , ลาป่วย H41 , ลากิจ H42

2.               พิมพ์     =COUNTIF(range,    ช่วงข้อมูลที่เราต้องการนับ

3.               criteria คือ กำหนดเงื่อนไขในการนับจำนวน ให้เลือกนับเฉพาะเซลล์ที่ต้องการ

4.               สูตรที่ได้คือ           ขาด          =COUNTIF(J6:J36,"ขาดงาน")

                                        ลาป่วย    =COUNTIF(J6:J36,"ลาป่วย")

                                        ลากิจ       =COUNTIF(J6:J36,"ลากิจ")

การทำบัตรพนักงาน  และสลิปเงินเดือนโดยใช้ฟังก์ชั่น VLOOKUP

ฟังก์ชั่น VLOOKUP        

เป็นฟังก์ชั่นค้นหาค่าในคอลัมน์แรกของตารางหรืออาร์เรย์ข้อมูล แล้วส่งกลับค่าที่อยู่ในคอลัมน์เดียวกันของแถวที่ระบุไว้

รูปแบบฟังก์ชั่น

                VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Lookup_value                        ค่าที่ต้องการค้นหาจากคอลัมน์แรกหรือคอลัมน์ซ้ายสุดของตาราง ซึ่งเป็นได้ทั้งค่าตัวเลข ข้อความ หรือการอ้างอิงเซล

Table_array                           การอ้างอิงไปยังตารางที่ต้องการนำมาค้นหาข้อมูลที่ตรงกับ lookup_value ซึ่งการอ้างอิงนี้อาจใช้การอ้างอิงตำแหน่งช่วงเซลหรืออ้างอิงชื่อช่วงเซลก็ได้

Col_index_num                     หมายเลขคอลัมน์ใน table_array ที่ต้องการให้ส่งค่าที่ตรงกับ lookup_value กลับมา

·        ถ้า col_index_num เท่ากับ 1 จะส่งกลับค่าในคอลัมน์แรกของ table_array ถ้า col_index_num เท่ากับ 2 จะส่งกลับค่าในคอลัมน์ที่สองของ table_array และให้ผลทำนองเดียวกันสำหรับค่า col_index_num อื่นๆ

·        ถ้า col_index_num น้อยกว่า 1 จะแสดงข้อผิดพลาด #VALUE!

·        ถ้า col_index_num มากกว่าจำนวนคอลัมน์ทั้งหมดใน table_array จะแสดงข้อผิดพลาด #REF

Range_lookup                        ค่าตรรกะที่ระบุว่าจะให้ฟังก์ชั่น LOOKUP ค้นหาค่าที่ตรงกันอย่างแม่นยำหรือตรงกันโดยประมาณ

·        ถ้า range_lookup เป็น TRUE หรือละค่าไว้ ฟังก์ชั่น LOOKUP จะส่งกลับค่าที่ตรงกันอย่างแม่นยำ แต่ถ้าไม่พบจะส่งกลับค่าที่ตรงกันโดยประมาณ (ค่าที่ตรงกันโดยประมาณคือค่ามากที่สุดแต่น้อยกว่า Lookup_value)

·        ถ้า range_lookup เป็น FALSE ฟังก์ชั่น VLOOKUP จะส่งกลับค่าที่ตรงกันอย่างแม่นยำ ถ้าไม่พบจะแสดงข้อผิดพลาด #N/A

ฟังก์ชั่น HLOOKUP

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

รูปแบบฟังก์ชั่น

                HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

Lookup_value                        ค่าที่ต้องการค้นหาจากคอลัมน์แรกหรือคอลัมน์ซ้ายสุดของตาราง ซึ่งเป็นได้ทั้งค่าตัวเลข ข้อความ หรือการอ้างอิงเซล

Table_array                           การอ้างอิงไปยังตารางที่ต้องการนำมาค้นหาข้อมูลที่ตรงกับ lookup_value ซึ่งการอ้างอิงนี้อาจใช้การอ้างอิงตำแหน่งช่วงเซลหรืออ้างอิงชื่อช่วงเซลก็ได้

·        ถ้า lookup_value มีค่าน้อยกว่าค่าที่เล็กที่สุดในแถวแรกของ table_array ฟังก์ชั่น HLOOKUP จะแสดงข้อผิดพลาด #N/A

Row_index_num                    หมายเลขแถวใน table_array ที่ต้องการให้ส่งค่าที่ตรงกับ lookup_value กลับมา

·        ถ้า row_index_num เท่ากับ 1 ฟังก์ชั่น HLOOKUP จะส่งกลับค่าในแถวแรกของ table_array ถ้า row_index_num เท่ากับ 2 จะส่งกลับค่าในแถวที่สอง table_array และให้ผลทำนองเดียวกันสำหรับค่า row_index_num อื่นๆ

·        ถ้า row_index_num น้อยกว่า 1 จะแสดงข้อผิดพลาด #VALUE!

·        ถ้า row_index_num มากกว่าจำนวนแถวทั้งหมดใน table_array จะแสดงข้อผิดพลาด #REF

Range_lookup                        ค่าตรรกะที่ระบุว่าจะให้ฟังก์ชั่น HLOOKUP ค้นหาค่าที่ตรงกันอย่างแม่นยำหรือตรงกันโดยประมาณ

·        ถ้า range_lookup เป็น TRUE หรือละค่าไว้ ฟังก์ชั่น HLOOKUP จะส่งกลับค่าที่ตรงกันอย่างแม่นยำ แต่ถ้าไม่พบจะส่งกลับค่าที่ตรงกันโดยประมาณ (ค่าที่ตรงกันโดยประมาณคือค่ามากที่สุดแต่น้อยกว่า Lookup_value)

·        ถ้า range_lookup เป็น FALSE ฟังก์ชั่น HLOOKUP จะส่งกลับค่าที่ตรงกันอย่างแม่นยำ ถ้าไม่พบจะแสดงข้อผิดพลาด #N/A

ตัวอย่างสลิปเงินเดือน

ตัวอย่าง ฐานข้อมูลพนักงาน

จากตัวอย่าง

หากเราต้องการให้เมื่อพิมพ์รหัสพนักงานแล้ว ข้อมูล ชื่อ-นามสกลุล,  ตำแหน่ง, แผนก แสดงผล เราจำเป็นต้องมีข้อมูลพนักงานเสียก่อน  เพื่อใช้ในการอ้างอิง

1.               เลือกเซลล์ที่ต้องการให้แสดงผล  ในตัวอย่างคือ ช่อง ชื่อ-นามสกุล แล้วใส่สูตร VLOOKUP

2.               =VLOOKUP(Lookup_value,   ค่าที่ต้องการค้นหา คือ รหัสพนักงาน B8

3.               Table_array  คือ การอ้างอิงไปยังตารางที่ต้องการนำมาค้นหาข้อมูลที่ตรงกับ lookup_value ซึ่งการอ้างอิงนี้อาจใช้การอ้างอิงตำแหน่งช่วงเซลหรืออ้างอิงชื่อช่วงเซลก็ได้  จากตัวอย่างเราทำการอ้างอิงช่วงข้อมูลจาก worksheet ข้อมูลพนักงาน   A4:J22

4.               Col_index_num     หมายถึงเลขคอลัมน์ใน table_array ที่ต้องการให้ส่งค่าที่ตรงกับ lookup_value กลับมา  จากตัวอย่างในข้อมูลพนักงาน  ชื่อ  อยู่ในคอลัมน์ที่ 2

5.               เนื่องจาก ชื่อและนามสกุล ไม่ได้อยู่คอลัมน์เดียวกัน  และเราต้องการให้ชื่อและนามสกุลมาอยู่ในช่องเดียวกันในสลิปเงินเดือน โดยเคาะเว้นวรรคระหว่างชื่อนามสกุล  เราจึงต้องใช้เครื่องหมาย   & “  ”  & แล้วตามด้วยสูตร VLOOKUP  ที่ใช้ในการค้นหาค่านามสกุล

6.               เขียนสูตรได้ดังนี้ 

        =VLOOKUP(B8,ข้อมูลพนักงาน!A4:J22,2) &" "&VLOOKUP(B8,ข้อมูลพนักงาน!A4:J22,3)

7.               สามารถใช้สูตร VLOOKUP เพื่ออ้างอิงข้อมูล  ตำแหน่ง และแผนก

<Key Word สำหรับค้นหาบทความนี้>  เรียน Excel, เรียนเขียนสูตร, การใช้ Pivot Table,ฟังก์ชั่น if(), SumIF(), CountIF(), VLookUp(), สอน Excel สำหรับ HR