10 cú pháp hàm và chức năng Excel dành cho nghề hành chính nhân sự

Microsoft Excel không thể thay thế cho hệ thống quản lý nhân sự hoặc công cụ phân tích nguồn nhân lực nâng cao, nhưng với tư cách là một phần mềm hỗ trợ tính năng theo dõi hàng ngày và báo cáo đột xuất dành cho nhân sự, Excel sẽ giúp bạn trở thành chuyên gia nhân sự dựa trên dữ liệu. 

Với ý nghĩ đó, mình sẽ giới thiệu một số công thức hàm, công cụ dành cho hành chính nhân sự phổ biến nhất trong Excel. Cùng mình khám phá nhé!

Hàm Excel về ngày tháng cho hành chính nhân sự

Excel lưu trữ ngày tháng dưới dạng số seri tuần tự bắt đầu bằng 1 và bắt đầu từ ngày 1 tháng 1 năm 1900, lúc 00:00 sáng. Vì vậy, ngày 03 tháng 7 năm 2021 là 44380 vì đó là ngày thứ 44,380 kể từ đầu thời gian Excel.

 

Excel có nhiều cách để chúng ta lưu trữ, so sánh và tính toán ngày và giờ. Trong đó, có 16 định dạng tiêu chuẩn cho ngày tháng và các quy tắc định dạng tùy chỉnh cung cấp cho bạn nhiều quyền kiểm soát hơn đối.

Để chọn một định dạng, hãy nhấp chuột phải vào một ô hoặc danh sách ngày tháng và chọn Format Cells.

 

Sau đó bạn chọn một trong các định dạng ngày tháng có sẵn của Excel phù hợp với công việc của bạn.

 

Xem thêm: Muốn làm tốt công việc hành chính nhân sự không thể thiếu Excel

Sử dụng hàm TODAY trong Excel để xác định ngày hôm nay

Trước khi bắt đầu tính toán ngày, hãy xem cách chúng ta có thể nhập ngày hôm nay (ngay cả khi chúng ta không biết hôm nay là ngày bao nhiêu)

Hàm TODAY là một công cụ giúp tiết kiệm thời gian bởi vì nó truy xuất ngày và giờ hôm nay từ máy tính của bạn, vì vậy nó sẽ tự động cập nhật. Trang tính của bạn tự động cập nhật khi bạn mở hoặc thay đổi dữ liệu. 

Chức năng này rất tiện lợi để tính toán những thứ như tuổi, thời gian phục vụ hoặc bất kỳ trường hợp nào mà khoảng thời gian bạn đang sử dụng không có kết thúc cụ thể.

Ví dụ: Nếu bạn muốn tính tuổi của một nhân viên, bạn có thể trừ ngày sinh của nhân viên đó cho ngày hôm nay.

- Bước đầu tiên, mình sẽ bắt đầu bằng cách định dạng cột để hiển thị năm thay vì số seri hay ngày tháng. 

- Bấm chuột phải vào tiêu đề cột "Tuổi", chọn Format Cells sau đó bảng Format Cells sẽ mở ra.

- Tại đây, bạn chọn Custom, trong ô Type, bạn nhập "yy" 

 

- Cuối cùng, chọn OK để lưu thay đổi.

Như vậy bây giờ bạn có thể lấy Cột năm sinh trừ cho năm tương ứng với ngày hôm nay để tính tuổi của nhân viên.

Một số điều HR thường làm là đo lường sự khác biệt giữa hai ngày để tính toán những vấn đề cơ bản như tuổi của nhân viên, thời gian làm việc, điều kiện hưởng trợ cấp, lương hưu và thâm niên.

Xem thêm: Hướng dẫn cách sử dụng hàm TODAY trong Excel nhanh và đơn giản nhất

Tìm sự khác biệt giữa hai ngày bằng hàm DATEDIF trong Excel  

Hàm DATEDIF tính chênh lệch giữa hai ngày trong Excel

Cú pháp hàm:

= DATEDIF (start_date, end_date, unit)

Trong đó:

- start_date: Ngày đầu tiên muốn tính toán ngày có thể nhập ở dạng chuỗi văn bản hoặc dưới dạng số seri.

- end_date: Ngày cuối cùng muốn tính toán

- unit: Loại thông tin muốn trả về, gồm các giá trị sau:

+ unit = “Y” -> Trả về số năm trong khoảng thời gian.

+ unit = “M” -> Trả về số tháng trong khoảng thời gian.

+ unit = “D” -> Trả về số ngày trong khoảng thời gian.

+ unit = “MD” -> Trả về sự khác biệt giữa các ngày trong start_date và end_date, không phụ thuộc tháng và năm.

+ unit = “YM” -> Trả về sự khác biệt giữa các tháng trong start_date và end_date, không phụ thuộc ngày và năm.

+ unit = “YD” -> Trả về sự khác biệt giữa các ngày trong start_date và end_date, không phụ thuộc năm.

Ví dụ:

 

Kết quả:

Xem thêm: Cách dùng hàm DATEDIF hệ số lương thưởng, thâm niên làm việc cực chuẩn

Hàm DAYS360 trả về số ngày giữa 2 mốc thời gian

Lịch Gregorian mặc định gây khó khăn cho kế toán hay việc tính toán trong Excel, vì vậy họ đã phát minh ra lịch 360 ngày trong năm với 30 ngày trong mỗi tháng để tạo ra một khuôn khổ đúng.

Cú pháp hàm:

=DAYS360(end_date,start_date)

Trong đó:

- end_date: Là giá trị ngày kết thúc trong khoảng thời gian cần tính ngày. Là giá trị bắt buộc.

- start_date: Là giá trị ngày bắt đầu trong khoảng thời gian cần tính ngày. Là giá trị bắt buộc.

Lưu ý:

- Ngày phải được lấy từ một hàm khác hoặc được nhập bằng định dạng DATE: DATE (yyyy, m, d), ví dụ DATE (2021,3,11) là ngày 11 tháng 3 năm 2021.

- Nếu start_date sau ngày end_date, công thức DATEDIF trả về một số âm.

Xem thêm: Hàm DAYS360 là hàm gì? Cách sử dụng hàm DAYS360 trong Excel

Hàm YEARFRAC để tính số năm trong Excel

Nếu bạn muốn tính toán sự khác biệt giữa hai ngày dưới dạng năm, hàm YEARFRAC sẽ làm điều đó cho bạn. 

Cú pháp hàm:

= YEARFRAC(start_date, end_date,[basis])

Trong đó: 

- start_date: Ngày bắt đầu

- end_date: Ngày kết thúc

- basis: Cơ sở xác định cách đếm ngày theo bảng sau:

0	Đếm ngày theo US(NASD) 30/360
1	Đếm ngày theo ngày thực tế / năm thực tế
2	Đếm ngày theo số ngày thực tế / 360
3	Đếm ngày theo số ngày thực tế / 365
4	Đếm ngày theo EUROPEAN 30/360

Ví dụ:

 

 

Kết quả:

Xem thêm: Cách dùng hàm YEAHFRAC xác định % thời gian trong một năm

Hàm NETWORKDAYS tính số lượng ngày làm việc trong Excel

Hàm NETWORKDAYS là một công thức tiện dụng để quản lý các dự án, lịch trình hoặc bất kỳ lúc nào bạn cần tính toán khoảng thời gian của các ngày làm việc bao gồm cả ngày nghỉ.

Cú pháp hàm:

=NETWORKDAYS(start_date,end_date,holidays)
 

Mẹo: Bạn nên thiết lập một bảng tính có tên là Holidays chứa các ngày nghỉ lễ để dễ dàng theo tác hơn.

Ví dụ: Tính số ngày làm việc trong trường hợp sau đây

Bước 1: Chọn tất cả các ô có chứa ngày nghỉ lễ 

Bước 2: Bấm chuột phải và chọn Define Name để đặt tên cho vùng dữ liệu

 

Bước 3: Nhập tên cho vùng dữ liệu. Ở đây mình nhập "Holidays"

Mình nhận được kết quả như sau: 

 

Định dạng bảng làm việc trong Excel

Trước khi bắt đầu làm việc với các công thức tra cứu, hãy định dạng bảng nhân viên để các hàm và thủ tục của bạn hoạt động một cách nhất quán.

Mỗi bảng phải có một Primary Key (cột khóa chính - cột tiêu chuẩn) cột luôn luôn phải có đầy đủ dữ liệu mà bạn không được phép bỏ trống bất kỳ một ô tính nào.

Nếu bạn làm việc với thông tin nhân viên, Primary Key hầu như luôn là ID nhân viên. Vì vậy, bạn nên đặt nó ở cột đầu tiên (ngoài cùng bên trái) trong mỗi trang tính. Bây giờ, khi liệt kê từng nhân viên làm cho các hàm VLOOKUP, INDEX, MATCH và INDEX-MATCH sẽ dễ quản lý hơn nhiều. 

Xem thêm: Cách tạo bảng và định dạng bảng biểu Excel

Lọc dữ liệu trong Excel

Bộ lọc là công cụ tiết kiệm thời gian đáng kinh ngạc khi bạn muốn chia nhỏ dữ liệu. Chúng cũng có thể hữu ích khi bạn muốn xác thực dữ liệu trong bảng. Nếu bạn đang làm việc trong một trang tính Excel, bạn có thể nhận thấy rằng các bảng của bạn tự động có bộ lọc. 

Giả sử bạn có một báo cáo liệt kê tất cả nhân viên và bạn cần theo dõi các đánh giá hiệu suất chưa hoàn thành. Bạn có thể sắp xếp trang tính theo trạng thái lọc các thành phần theo nhu cầu sau đó sao chép và dán những dữ liệu bạn cần theo dõi.

Trong ví dụ này, mình muốn tìm tất cả nhân viên thuộc phòng kinh doanh.

 

Bước 1: Trên thanh công cụ, bạn chọn tab Data và chọn thẻ Filter trong nhóm Sort & Filter.

 

Hoặc tại tab Home bạn chọn mũi tên xổ xuống tại thẻ Sort & Filter và chọn Filter để mở bộ lọc.

Bước 2: Tại mỗi cột Excel, bạn sẽ thấy có mũi tên hướng xuống tại ô tiêu đề. Bạn nhấp vào mũi tên hướng xuống ở cột mà bạn muốn lọc.

 

Bước 3: Bỏ chọn hộp Select All để bỏ chọn tất cả dữ liệu một cách nhanh chóng.

 

Bước 4: Chọn các hộp dữ liệu mà bạn muốn hiển thị, cụ thể trong ví dụ của mình là "Kinh doanh"

 

Kết quả

 

Bên cạnh đó, bạn cũng có thể sử dụng nhiều bộ lọc cho kiểu dữ liệu trong cột. 

Giả sử mình lọc những nhân viên bắt đầu làm việc từ khoảng thời gian 01/01/2021 đến 30/06/2021, bạn làm như sau:

Bước 1: Nhấp vào bộ lọc cột "Ngày bắt đầu".

Bước 2: Chọn tùy chọn Date Filters. Bây giờ bạn nhận được một danh sách các tùy chọn lọc và nhấp chọn "Between"

 

Bước 3: Tại cửa sổ hiện lên, bạn điền vào các thông số ngày theo nhu cầu công việc.

Xem thêm: Hướng dẫn cách lọc dữ liệu còn gọi là Filter trong Excel có bài tập đính kèm

Sử dụng Pivot Tables trong Excel

Trong số tất cả các tính năng và công cụ của Excel, Pivot Tables là một trong những tính năng hữu ích nhất. Chúng là các báo cáo tóm tắt được tạo từ cơ sở dữ liệu, bảng trong trang tính, tệp dữ liệu nguồn bên ngoài hoặc cơ sở dữ liệu nguồn bên ngoài.

Các chức năng của Pivot Tables cho phép bạn chuyển đổi bất kỳ nguồn dữ liệu nào và thực hiện tóm tắt dữ liệu mạnh mẽ trong một vài cú nhấp chuột. Sau đó, bạn có thể chỉnh sửa, kết hợp và thao tác dữ liệu theo bất kỳ cách nào bạn muốn.

Mình sẽ hướng dẫn bạn cách tạo báo cáo "Lý do chấm dứt hợp đồng theo bộ phận"

Dưới đây, mình sẽ hướng dẫn các bạn cụ thể cách tạo báo cáo từ bất kỳ trang tính nào:

1. Đầu tiên, chúng ta nhấp vào ô trên cùng bên trái (A1) trong bảng dữ liệu nhân viên ở Excel. Một mẫu dữ liệu tốt sẽ có ID nhân viên (Employee Number), là một giá trị duy nhất cho mỗi nhân viên, trong cột A.

2. Tiếp theo, chúng ta chọn tab Insert trên thanh công cụ.

3. Sau đó, bạn nhấp vào biểu tượng PivotTable . Hộp thoại Create PivotTable sẽ mở ra.

4. Tại đây, Excel sẽ tự động chọn toàn bộ bảng dữ liệu của bạn.

5. Bạn nhấp chọn New Worksheet để cho Excel biết vị trí đặt bảng là một sheet mới.

6. Sau đó, nhấp vào OK .

7. Một tab mới sẽ mở ra với bảng PivotTable trống ở bên trái và biểu mẫu các trường PivotTable ở bên phải.

8. Trong biểu mẫu trường PivotTable, hãy kéo Date of Termination vào phần Values, tại đây nó sẽ cung cấp cho bạn giá trị số cho bảng để đếm.

 

9. Chúng ta tiếp tục kéo Department (Bộ phận), Employee Name (Tên nhân viên), Position (Vị trí) , và Date of Term vào phần Rows

 

Lúc này, bạn có thể bắt đầu hình dung được bảng tóm tắt, nhưng chúng sẽ là một mớ hỗn độn, khó hiểu. Chúng ta sẽ khắc phục điều đó ngay bây giờ:

10. Nhấp vào bất kỳ đâu trong bảng, sau đó nhấp vào Design. Cửa sổ Design xuất hiện. 

11. Chọn Report Layout. 

12. Tiếp theo bạn chọn Show in Tabular Form. 

 

13. Tuy nhiên, chúng ta không cần tổng phụ trong cột "Employee Name". Vì vậy, bạn nhấp chuột phải vào cột "Employee Name" và nhấp vào Subtotal “Employee Name” để bỏ chọn. Chúng ta làm tương tự với cột "Position".

 

Bây giờ chúng ta đã có một bảng gọn gàng hiển thị số lượng nhân viên và lý do chấm dứt hợp đồng cho mỗi bộ phận.

Xem thêm: Cách lập báo cáo bằng Pivot Table trong Excel khi không có cột tháng

Một số điều HR nên biết về Pivot Tables

Mình sẽ chỉ ra lý do tại sao công cụ này được gọi là PivotTable - bảng tổng hợp.

Trong biểu mẫu trường PivotTable, mình lấy trường Department (Bộ phận) và kéo trường đó vào phần Filters (Bộ lọc) để pivot trường từ trường hàng sang một bộ lọc nơi chúng ta có thể thay đổi báo cáo để hiển thị một bộ phận hoặc bất kỳ sự kết hợp các bộ phận nào.

Bây giờ, chúng ta sẽ thử một pivot khác. Chúng ta nhấp chuột phải vào trường Reason (Lý do), di chuột vào Move, sau đó nhấp vào Move “Reason” to Beginning.

 

Bây giờ chúng ta đã có một báo cáo về lý do chấm dứt hợp đồng.

Xem thêm: Hướng dẫn cách xóa nhanh một trường Pivot Table trong Excel

Các cột trong Pivot Tables

Nếu chúng ta muốn xem lý do chấm dứt hợp đồng được tóm tắt cho từng bộ phận, chúng ta có thể sử dụng các cột để hiển thị chi tiết các bộ phận.

Kéo Department (Bộ phận) vào phần Columns (Cột). Bây giờ chúng tôi có một báo cáo tóm tắt, nhưng nó vẫn chưa hữu ích cho lắm.

Hãy tối ưu hóa nó như sau:

- Nhấp chuột phải vào ô Grand Total và chọn Show Values ​​As, sau đó chọn % của Grand Total.

Sau khi chúng ta sử dụng ô Grand Total để định dạng các số dưới dạng phần trăm không có số thập phân, chúng ta có một báo cáo cho chúng ta biết điều chúng ta nên thay đổi để giữ chân nhân sự.

Các công thức và chỉ số nguồn nhân lực phổ biến

Mặc dù có hàng trăm công thức và chức năng có thể giúp HR hoàn thành công việc, nhưng có một số công thức nổi bật vì chúng hỗ trợ HR thúc đẩy năng suất làm việc mạnh mẽ như:

Chi phí phúc lợi dành cho nhân viên

Tính toán lợi ích (bao gồm tất cả các phúc lợi dành cho nhân viên) là một cách tính toán chi phí lợi ích và tính tổng chi phí dành cho nhân viên để sử dụng trong các số liệu nội bộ khác.

Tính toán chi phí phúc lợi cho mỗi nhân viên

Tính toán chi phí phúc ích chính là tỷ lệ chi phí lợi ích trên một số lượng nhân viên trung bình.

Chi phí phúc lợi = Tổng chi phí phúc lợi ÷ Số nhân viên trung bình

Bạn có thể chia nhỏ chi phí này theo bất kỳ khoảng thời gian nào để so sánh. Nhiều công ty tính toán các tỷ lệ riêng biệt cho người lao động theo giờ, người làm công ăn lương và giám đốc điều hành.

Tính lợi ích và phúc lợi theo phần trăm lương

Tính toán phúc lợi phải trả theo tỷ lệ phần trăm lương như sau:

Tỷ lệ phúc lợi = Chi phí lợi ích ÷ Tổng chi trả

Tổng chi phí chi trả cho nhân viên

Khi chúng ta có tỷ lệ lợi ích, chúng ta có thể sử dụng nó để tính tổng chi phí chi trả cho nhân viên.

Tổng chi phí nhân viên = (1 + Tỷ lệ phúc lợi) × Tổng chi trả

Xem thêm: Hướng dẫn cách xác định ngày hết hạn hợp đồng trên Excel

Trong lĩnh vực nhân sự, đôi khi chúng ta quá tập trung vào chi phí mà quên mất việc thể hiện kết quả kinh doanh của mình. Mình khuyên bạn nên hợp tác với các nhà quản lý và lãnh đạo trong toàn doanh nghiệp để trả lời các câu hỏi quan trọng nhất khi thực hiện thay đổi kinh doanh, đó là: Cái gì đã thay đổi? Bằng bao nhiêu?

Doanh thu trên mỗi nhân viên

Một câu trả lời nhanh cho những câu hỏi đó là trong doanh thu trên mỗi nhân viên. Chúng ta lấy doanh thu dòng đầu từ các báo cáo tài chính và chia nó cho số lượng nhân viên.

Doanh thu trên mỗi nhân viên = Số lượng nhân viên trung bình ÷ Doanh thu

Các chỉ số thu hút nhân tài

Chi phí tuyển dụng là một thước đo kinh doanh quan trọng. Bạn phải biết cần bao nhiêu thời gian và chi phí để tuyển dụng nhân tài. Sau đó, bạn có thể so sánh những chi phí đó với những gì bạn chi cho sự tham gia, giữ chân và phát triển của nhân viên.

Thời gian cần cho một vị trí tuyển dụng

Thời gian cần thiết để tìm nguồn nhân lực, thu hút, lựa chọn và tuyển dụng ứng viên là một yếu tố quan trọng trong việc tính toán chi phí tuyển dụng.

Một hệ thống không hiệu quả có thể khiến chi phí phát triển nguồn nhân lực ngoài tầm kiểm soát và tác động lớn hơn nhiều so với chi phí vận hành việc tuyển dụng. 

Ví dụ: Nhân viên còn lại của bạn phải làm thay việc cho những vị trí còn trống, chưa tuyển dụng được và doanh nghiệp có thể phải chịu chi phí làm thêm giờ. Bên cạnh đó, nhân viên của bạn sẽ có thêm căng thẳng, mệt mỏi dẫn đến công việc không hiệu quả. 

 

Chi phí mỗi lần tuyển dụng

Chi phí cho mỗi lần tuyển dụng bao gồm chi phí cho vị trí tuyển dụng, chi phí trực tiếp cho việc quảng cáo và tuyển dụng, cũng như chi phí cho người quản lý và người tuyển dụng trong quá trình này. Dưới đây là bảng phân tích chi phí thu hút nhân tài với các công thức nhân sự tương ứng:

	Công thức
Chi phí trả cho vị trí được tuyển dụng	Lương x ngày công + giwof làm thêm
Thời gian của nhà tuyển dụng để tìm nguồn, sàng lọc và phỏng vấn các ứng viên	(Lương của nhà tuyển dụng theo giờ + tỷ lệ phúc lợi) x giờ
Người quản lý dành thời gian để phỏng vấn, đánh giá và lựa chọn ứng viên	(Lương của người quản lý + tỷ lệ phúc lợi) x giờ

Tỉ lệ lợi nhuận

Để hiểu hiệu quả và năng suất của công việc thu nhận nguồn nhân lực, bạn sẽ cần phải đo lường mỗi bước trong quy trình hoạt động. Chỉ khi bạn làm điều đó, bạn mới có thể đưa ra quyết định sáng suốt về những công việc đang hoạt động hoặc không hoạt động.

Tỷ lệ lợi nhuận là tỷ lệ phần trăm của những người nộp đơn tuyển dụng thực hiện các bước tiếp theo của quy trình.

Tỷ lệ lợi nhuận = người nộp đơn còn lại ở quy trình tiếp theo ÷ người nộp đơn ở bước trước

Tốc độ tuyển dụng

Thời gian tuyển dụng và tốc độ tuyển dụng không giống nhau.

Thời gian để tuyển dụng chỉ cung cấp cho bạn độ dài của toàn bộ quá trình. Nhiều công ty hiện đang bắt đầu đo lường tốc độ tuyển dụng của quy trình ở mỗi bước. Bất kỳ sự chậm trễ nào vào thời điểm quan trọng đều có thể dẫn đến những kết quả không như ý trong công việc tuyển dụng. 

Bạn có thể theo dõi tốc độ cùng với tỷ lệ lợi nhuận cũng như các công thức tính toán của mình theo bảng dưới đây:

Công thức tính toán như sau:

Kết luận

Trong bài viết trên, Gitiho đã cùng bạn khám phá 10 cú pháp Excel cũng như các công thức, quy tắc tuyển dụng áp dụng thường xuyên trong ngành hành chính nhân sự dành cho HR. Hy vọng bạn sẽ áp dụng thành công vào công việc của mình và đừng quên theo dõi chúng mình để xem thêm các bài viết bổ ích khác nhé!

Nằm lòng kĩ năng chuyên ngành hành chính nhân sự trong tài liệu đính kèm bài viết nhé!