Bạn làm công việc quản lý nhân sự nhưng chưa biết cách thống kê tình trạng làm việc của từng người trên Excel? Hãy cùng Học Excel Online tìm hiểu cách ứng dụng hàm SEARCH và hàm COUNTIFS để thống kê tình trạng làm việc nhé. Chi tiết như sau:

Giả sử chúng ta có 1 bảng dữ liệu thống kê về các công việc, người thực hiện và tình trạng thực hiện của từng người như sau:

Yêu cầu đặt ra là chúng ta cần thống kê để lên báo cáo tổng hợp về từng nhân viên theo các tình trạng công việc, tổng số công việc của từng người.

Để giải quyết được yêu cầu này, chúng ta có thể thực hiện theo các bước như sau

Phân tích đặc điểm dữ liệu

Trong bất kỳ yêu cầu làm việc nào trên Excel thì việc phân tích để hiểu đặc điểm dữ liệu là hết sức quan trọng. Khi nắm được đặc điểm dữ liệu rồi thì chúng ta mới có thể có phương án phù hợp giúp giải quyết các yêu cầu đó. Cụ thể trong trường hợp này chúng ta có:

Cột Người thực hiện: Nhiều người có thể thực hiện chung 1 công việc, trong đó tên các nhân viên được cách nhau bởi dấu chấm phẩy. Các nhân viên này đều thuộc Danh sách nhân viên trong bảng báo cáo. Với việc trình bày như vậy thì không thể dùng hàm tính trực tiếp tới cột này được. => Trong trường hợp này, chúng ta có thể dùng hàm để tìm kiếm xem tên nhân viên cần báo cáo có xuất hiện trong từng công việc không => Sử dụng hàm SEARCH để tìm kiếm theo chuỗi ký tự.

Cột Tình trạng: Bao gồm 2 tình trạng là Hoàn thành và Chưa hoàn thành. Phù hợp với yêu cầu báo cáo theo điều kiện là tình trạng.

Như vậy báo cáo trong trường hợp này gồm 2 điều kiện là Nhân viên và Tình trạng. Do đó có thể dùng hàm COUNTIFS để thực hiện báo cáo dạng đếm theo nhiều điều kiện.

Tìm nhân viên có tên trong cột Người thực hiện bằng hàm SEARCH

Do trong cột Người thực hiện có thể có nhiều hơn 1 nhân viên trong 1 ô, mà báo cáo yêu cầu lập riêng theo từng người. Như vậy chúng ta phải sử dụng cột phụ để tách từng nhân viên ra. Tìm kiếm xem trong mỗi công việc có xuất hiện nhân viên đó không.

Bước 1: Tạo mỗi nhân viên 1 cột trong bảng dữ liệu

Để thực hiện thao tác này, chúng ta copy vùng danh sách nhân viên. Sau đó tại cột tiếp theo trong bảng dữ liệu, thực hiện Paste Special.

Trong cửa sổ Paste Special chọn mục Transpose

Bước 2: Dùng hàm SEARCH tìm vị trí xuất hiện của mỗi nhân viên

Tại ô I3 (bắt đầu cho nhân viên thứ 1) dùng hàm như sau:

=SEARCH(I$2,$G3)

Hàm này có ý nghĩa: Tìm vị trí của đoạn text ô I2 trong đoạn text ô G3. Cố định dòng 2 tại tham chiếu I2 để khi sao chép công thức cho những người khác sẽ có thể thay đổi cột I, nhưng không thay đổi dòng 2 khi sao chép xuống dưới. Tương tự như vậy sẽ cố định cột G để không thay đổi tọa độ cột này khi sao chép công thức sang phải.

Kết quả trả về là:

• Nếu có xuất hiện: trả về số thứ tự tại vị trí bắt đầu tìm thấy trong toàn bộ dãy ký tự
• Nếu không xuất hiện: trả về lỗi #Value!

Khi filldown công thức cho cả cột này ta sẽ thấy các kết quả là:

Để tránh lỗi #VALUE! thì chúng ta sử dụng hàm bẫy lỗi là hàm IFERROR. Những giá trị nào ra lỗi #VALUE! sẽ thay bằng 0

Công thức đầy đủ trong ô I3 sẽ là:

=IFERROR(SEARCH(I$2,$G3),0)

Kết quả sau khi bẫy lỗi và áp dụng sang những nhân viên khác như sau:

Bước 3: Dùng hàm COUNTIF để đếm tổng số công việc thực hiện của mỗi nhân viên

Tại dòng Tổng cộng, chúng ta sẽ đếm tổng số công việc thực hiện của mỗi nhân viên. Điều này được hiểu là mỗi lần xuất hiện giá trị >0 tức là nhân viên đó có thực hiện công việc.

Do đó hàm COUNTIF sẽ đếm theo giá trị lớn hơn 0

Kết quả như sau:

Hoàn thiện báo cáo tổng hợp với hàm COUNTIFS và INDIRECT

Thông qua các bước ở trên, chúng ta đã có được 1 bảng dữ liệu phù hợp để làm báo cáo tổng hợp, khi mỗi nhân viên đều có 1 cột dữ liệu tương ứng để tính.

Đếm kết quả báo cáo với hàm COUNTIFS

Cách làm thông thường là chúng ta sẽ dùng ngay hàm COUNTIFS như sau:

Tại ô B3 đặt hàm =COUNTIFS($I$3:$I$19,”>0″,$H$3:$H$19,B$2)

Trong đó:

• I3:I19 là vùng kết quả tương ứng với nhân viên Lâm trong bảng dữ liệu đã thực hiện ở trên
• >0 là chỉ đếm những giá trị lớn hơn 0 trong vùng I3:I19
• H3:H19 là vùng Tình trạng của các công việc
• B2 là giá trị tại ô B2=Hoàn thành, chỉ đếm những giá trị hoàn thành trong vùng H3:H19

Kết quả thu được là 3 => Đúng

Tuy nhiên chúng ta thấy mỗi nhân viên lại ở 1 cột khác nhau. Khi đó sao chép công thức xuống những nhân viên khác thì chúng ta phải sửa tham chiếu cột I thành các cột tương ứng với nhân viên tiếp theo. Việc này khá thủ công.

Xác định tham chiếu từng nhân viên với hàm ADDRESS

Để có thể tự động xác định tọa độ tham chiếu của mỗi nhân viên, chúng ta có thể làm như sau:

Bước 1: Chèn thêm 1 cột Tọa độ sau cột Tên nhân viên

Bước 2: Sử dụng hàm Address kết hợp hàm Match cho vị trí ô B3 như sau:

B3=”Debai!”&ADDRESS(2,MATCH($A3,$A$2:$O$2,0))&”:”&ADDRESS(1+COUNTA($H$3:$H$19),MATCH($A3,$A$2:$O$2,0))

• Trong đó sử dụng hàm MATCH($A3,$A$2:$O$2,0) để tìm vị trí cột tương ứng với tên nhân viên tại vị trí đặt công thức. Ở đây chúng ta đặt công thức tại B3 nên xét cho nhân viên tại ô A3
• Hàm Address sẽ trả về tọa độ 1 ô nếu xác định được số dòng, số cột.
• Để xác định cụ thể Sheet nào, chúng ta có thể kết hợp thêm tên Sheet và dấu ! ở phía trước để giúp tham chiếu chính xác hơn. Trường hợp tham chiếu nằm cùng sheet thì có thể bỏ qua tên sheet.

Tương tự như vậy khi copy công thức cho các nhân viên tiếp theo chúng ta có tọa độ như sau:

Hoàn thiện công thức COUNTIFS bằng cách kết hợp hàm INDIRECT

Khi đã có địa chỉ tọa độ tham chiếu tương ứng theo tên nhân viên, chúng ta có thể sử dụng hàm INDIRECT để lấy nội dung trong các tham chiếu đó thay vì phải sử dụng trực tiếp địa chỉ tham chiếu.

Công thức COUNTIFS phía trên có thể sửa lại là:

C3=COUNTIFS(INDIRECT($B3),”>0″,$I$3:$I$19,C$2)

(So với công thức ở trên thì các tham chiếu bị tăng 1 cột do chúng ta mới chèn thêm 1 cột là Tọa độ tham chiếu)

Lúc này có thể sao chép công thức từ C3 tới các nhân viên khác bên dưới, sang cột Chưa hoàn thành bên phải.

Kết quả thu được là:

Như vậy báo cáo đã hoàn thành 1 cách chính xác rồi phải không nào. Nếu bạn cần dữ liệu để làm mẫu thì có thể tải file mẫu tại địa chỉ:

http://bit.ly/2O0ByMn

Các hàm trong Excel thật đa dạng và thú vị đúng không nào. Việc kết hợp linh hoạt các hàm trong Excel sẽ giúp công việc của chúng ta đơn giản hơn, tự động hơn và tiết kiệm thời gian, giúp tăng hiệu quả công việc một cách đáng kể. Muốn hiểu rõ về các hàm trong Excel, sử dụng tốt các công cụ của Excel, bạn có thể tham gia khóa học EX101 – Excel từ cơ bản tới chuyên gia dành cho người đi làm của hệ thống Học Excel Online. Hiện nay hệ thống đang có nhiều ưu đãi rất hấp dẫn cho bạn khi đăng ký khóa học này. Chi tiết xem tại: