Ứng dụng Index+match trong quản lý hợp đồng lao động

Xin chào mừng các bạn đến với Học Excel Online

Trên trang blog.hocexcel.online có 1 bài viết rất hay về việc sử dụng hàm index kết hợp với hàm match tối ưu hơn là việc sử dụng hàm vlookup. Nhiều bạn muốn biết là ứng dụng của index và match vào công việc như thế nào.

Vậy nên hôm nay mình sẽ hướng dẫn các bạn cách sử dụng hàm index + match trong việc theo dõi hợp đồng lao động.

Cụ thể như sau:

Như các bạn thấy ở hình trên chúng ta có 2 phần:

• Phần phía bên trái là nội dung của 1 bản hợp đồng lao động (các dòng màu đỏ sẽ thay đổi để tra cứu theo số Hợp đồng được chọn (ô M11)
• Phần phía bên phải, chúng ta có 1 bảng kê thông tin nhân viên, bao gồm nhiều thông tin và có kèm theo số HĐ LĐ. Tuy nhiên cấu trúc bảng này có 1 chút đặc biệt là cột số HĐ LĐ lại không phải là cột đầu tiên trong bảng.

=> Với cấu trúc bảng như vậy, chúng ta chỉ có thể sử dụng hàm vlookup để tra cứu được duy nhất 1 thông tin là Ngày ký (Do yêu cầu của hàm vlookup là cột chứa điều kiện (criteria) phải là cột đầu tiên bên trái bảng dữ liệu). Còn lại các thông tin khác thì vlookup không sử dụng được.

Đừng bỏ lỡ: khóa học Excel kế toán online

Trong trường hợp như thế này thì sử dụng index và match là giải pháp tối ưu. Cách sử dụng index và match như sau:

Cấu trúc hàm Index(array, row_num, [column_num])

Array là vùng chứa kết quả dữ liệu cần tìm, bắt đầu từ dòng 1 (nếu không quy định giá trị column_num thì sẽ hiểu Array chỉ có 1 cột)

Row_num là dòng chứa giá trị cần tìm trong vùng Array (dạng số)

Column_num là cột chứa giá trị cần tìm (dạng số)

Như vậy trong hàm Index, ta luôn biết Array nằm ở đâu (cần cho ra kết quả ở cột nào). Còn xác định Row_num thì cần sử dụng hàm Match

Cấu trúc hàm Match(lookup_value, lookup_array, [match_type])

lookup_value là giá trị cần tìm (ở đây là số hợp đồng)

lookup_array là vùng chứa giá trị cần tìm (cột Số HĐ LĐ)

match_type là dạng tìm kiếm, sử dụng giá trị 0 để tìm kiếm chính xác.

• Ngày ký hợp đồng (ô I4): =INDEX($T$1:$T$9,MATCH($M$11,$S$1:$S$9,0))

Dòng này sẽ cho ra kết quả ngày dạng dd/mm/yyyy

Để hiển thị định dạng “Ngày 03 tháng 03 năm 2017” tại ô I4 thì ta chọn Format Cells ô I4 và làm như sau:

Chọn Custom / mục Type: bạn nhập nội dung “Ngày “dd” tháng “mm” năm “yyyy

• Số Hợp đồng: = “Số “&M11
• Họ tên người lao động: =INDEX($L$1:$L$9,MATCH($M$11,$S$1:$S$9,0))
• Ngày sinh: =TEXT(INDEX($M$1:$M$9,MATCH($M$11,$S$1:$S$9,0)),”dd/mm/yyyy”)

Thay vì vào format cells để định dạng, các bạn có thể kết hợp hàm Text để định dạng trực tiếp cho dữ liệu trong cell

• Phần thông tin chứng minh thư, gồm 3 chỉ tiêu: Số, ngày cấp, nơi cấp. Chúng ta có thể tách ra 3 hàm, sau đó ghép các hàm lại với dấu & (khi nối các chỉ tiêu thì ta có thể kết hợp thêm vài dấu cách để ngăn cách các chỉ tiêu trên 1 dòng)

=”Số CMND / Hộ chiếu “&INDEX($N$1:$N$9,MATCH($M$11,$S$1:$S$9,0))&”   “&”Cấp ngày “&TEXT(INDEX($O$1:$O$9,MATCH($M$11,$S$1:$S$9,0)),”dd/mm/yyyy”)&”   “&”Tại “&INDEX($P$1:$P$9,MATCH($M$11,$S$1:$S$9,0))

• Nơi đăng ký hộ khẩu: =INDEX($Q$1:$Q$9,MATCH($M$11,$S$1:$S$9,0))
• Nơi ở hiện nay: =INDEX($R$1:$R$9,MATCH($M$11,$S$1:$S$9,0))

Như vậy khi áp dụng Index và match, điều cần lưu ý chỉ là đối tượng Array (vùng chứa kết quả cần tìm) của hàm Index. Còn các nội dung của hàm Match không đổi khi làm tương tự với nhiều nội dung trên cùng 1 bảng dữ liệu.

Các bạn có thể kiểm tra lại kết quả của hàm bằng cách thay đổi số hợp đồng ở ô M11.

Như vậy chúng ta đã hoàn thành xong 1 ví dụ về việc sử dụng hàm index match vào công việc thực tế, cụ thể hơn là quản lý hợp đồng lao động, một công việc mà hầu như ai làm về kế toán tiền lương hay bộ phận nhân sự đều gặp phải.

Cảm ơn các bạn đã chú ý theo dõi.

Để có thể nâng cao kỹ năng Excel trong công việc kế toán tiền lương, nhân sự, các bạn có thể tham gia khóa học

TL01 – ứng dụng EXCEL VBA trong TIỀN LƯƠNG – NHÂN SỰ

khóa học sẽ cung cấp cho bạn những kiến thức về công việc kế toán tiền lương, hành chính, nhân sự trong thực tế và các kỹ năng excel kết hợp với VBA để giải quyết công việc, tối ưu hiệu suất của bạn.