Khi thực hiện công việc chấm công trên Excel, chúng ta thường nghĩ tới những bảng chấm công đơn giản như thế này:

Nhưng thực tế lại không đơn giản như vậy:

• Hình thức chấm công bằng tay thường ít sử dụng, hầu hết dùng máy chấm công
• Doanh nghiệp có nhiều hình thức hoạt động, nhiều bộ phận, nên có nhiều nội dung chấm công khác như: Công sản xuất, công làm thêm, làm ban đêm, ca 3…
• Dữ liệu từ máy chấm công không sử dụng trực tiếp để tính lương được, mà cần phải xử lý lại trên Excel để tính toán, điều chỉnh

Trong bài viết này, Học Excel Online xin trích dẫn một yêu cầu từ thực tế mà rất nhiều bạn đã gặp phải nhưng chưa biết cách giải quyết, để chúng ta biết thêm về thực tế công việc và cách xử lý công việc đó trên excel. Yêu cầu đó như sau:

Đề bài

Từ máy chấm công, kế toán trích xuất dữ liệu ra Excel kết quả chấm công của nhân viên A để tính số giờ làm việc ban đêm. Dữ liệu trích xuất như sau:

biết rằng quy ước tính công như sau:

Dù đã có phần mềm, nhưng kỹ năng Excel vẫn cực kỳ quan trọng với kế toán, bạn đã vững Excel chưa? Hãy để tôi giúp bạn, đăng ký khoá học Excel:

• Ca 2: Làm việc từ 15h-23h thì được tính làm đêm 1h (từ 22-23h)
• Ca 3: Làm việc từ 23h-7h sáng hôm sau thì được tính làm đêm 8h (từ 23h-7h)

Cách làm

Bước 1: Phân tích yêu cầu đề bài

Bạn sẽ bắt đầu yêu cầu này thế nào:

• Lập tức mở Excel lên và mày mò công thức?
• Lên mạng hỏi  và tìm người trợ giúp?
• Dành nhiều thời gian xem đề bài và suy luận tính logic, sau đó mới thử làm hoặc nhờ người giúp?

Xem thêm: Tài liệu Excel cơ bản

Có lẽ trường hợp thứ 3 sẽ ít người làm theo. Nhưng bạn có biết rằng đó là cách tốt nhất để chúng ta học cách giải quyết vấn đề không? Ngay cả người có thể giải quyết yêu cầu đó cũng phải dành nhiều thời gian xem lại đề bài và suy luận tính logic, sau đó mới có thể đưa ra lời giải giúp bạn.

Việc đọc đề bài, phân tích đề bài là rất quan trọng. Khi đi học chúng ta được dạy nhiều, học nhiều nhưng khi đi làm rất nhiều bạn quên mất điều này.

Đề bài được diễn đạt lại như sau:

• Mỗi ngày làm việc có 3 ca được đánh số từ 1, 2, 3, mỗi ca có giờ vào và giờ ra riêng (theo thiết lập của máy chấm công) => Vùng dữ liệu từ cột C đến cột H
• Nhưng đó là thiết lập quy ước của máy chấm công, còn người lao động khi làm việc vẫn chấm công chủ yếu vào ca 1 (Cột C và D chứa thời gian theo ca 2, ca 3)
• Vì vậy chúng ta đều có thể coi bất cứ ca nào cũng có thể là ca 1, ca 2, ca 3 tùy theo thời gian làm việc của ca đó => Ca nào có thời gian chấm công thì đều được xét
• Ca 2 được hiểu là giờ vào trong khoảng 15 giờ (có thể coi như sau 12htới trước 17h), giờ ra trong khoảng 23 giờ (có thể coi như sau 22h và trước 24h)
• Ca 3 được hiểu là giờ vào trong khoảng 23 giờ (có thể coi như sau 22h và trước 24h), giờ ra trong khoảng 7 giờ (có thể coi như trước 8h), ở đây bao gồm điều kiện giờ vào phải >22 giờ

Logic khá phức tạp phải không nào? Bản thân vấn đề phức tạp thì việc diễn giải logic cũng phức tạp. Nhưng khi chúng ta đã có thể diễn giải được logic đúng thì việc xây dựng công thức, cách làm trên Excel sẽ trở nên dễ dàng hơn nhiều, bởi vấn đề đã trong phạm vi kiểm soát của chúng ta rồi.

Việc phân tích yêu cầu đề bài sẽ giúp chúng ta:

• Nắm được yêu cầu cuối cùng => Lựa chọn được phương pháp tiếp cận phù hợp (Dùng hàm gì để ra kết quả, bố cục kết quả thế nào)
• Hiểu rõ cấu trúc, đặc trưng của dữ liệu ban đầu => Dựa vào đó để có hướng giải quyết phù hợp (Cần làm những bước nào)
• Giải thích rõ các đặc điểm của dữ liệu => Biết được từng yếu tốtrong công thức sẽ được xây dựng như thế nào (Nội dung cụ thể từng bước)

Bước 2: Xây dựng bố cục kết quả

Việc xây dựng bố cục giúp chúng ta định hình được từng phần công việc cần làm, tổng kết được kết quả công việc.

Vì dữ liệu có 3 ca có thể có dữ liệu chấm công (vì chúng ta không thể biết trước dữ liệu máy chấm công kết xuất ra như thế nào, nên sẽ coi như cả 3 ca đều có thể có dữ liệu) => Kết quả tính sẽ tương ứng ra 3 cột theo từng ca (tương ứng cột I, J, K). Có thêm cột tổng cộng để tính tổng số giờ làm đêm theo từng ngày

Tìm hiểu về khóa học Word Excel với các chuyên gia

Bước 3: Xây dựng công thức tính

Khi xây dựng công thức tính, chúng ta cần phải luôn dựa vào logic đã phân tích ở trên để xây dựng công thức.

Xét cho cột I (tương ứng với thời gian ca 1 của máy chấm công)

Xét các trường hợp có thể xảy ra:

• Không có dữ liệu => Kết quả sẽ không có
• Giờ vào và giờ ra tương ứng với điều kiện là ca 2 => Lấy kết quả giờ làm đêm theo ca 2
• Giờ vào và giờ ra tương ứng với điều kiện là ca 3 => Lấy kết quả giờ làm đêm theo ca 3

Các trường hợp này đều là dạng luận lý Nếu… thì… => Sử dụng hàm IF để thực hiện.

• Trường hợp không có dữ liệu ở Giờ vào hoặc Giờ ra => IF(OR(C3=””,D3=””),0, kết quá nếu cả 2 ô giờ vào và giờ ra có dữ liệu) => IF1
• Trường hợp điều kiện là ca 2 => IF(AND(HOUR(C3)>12,HOUR(C3)<17,HOUR(D3)<24),1, kết quả nếu không thỏa mãn điều kiện ca 2) => IF2
• Trường hợp điều kiện là ca 3 => IF(AND(HOUR(C3)>=22,HOUR(D3)<8),8, kết quả nếu không thỏa mãn điều kiện ca 3) => IF3

Nếu giá trị không thỏa mãn IF1 thì xét IF2, nếu giá trị không thỏa mãn IF2 thì xét IF3, nếu giá trị không thỏa mãn IF3 thì = 0

Như vậy khi chúng ta lồng ghép cả 3 hàm IF này vào 1 để làm công thức chung cho ô I3 như sau:

Làm tương tự với công thức tính ca 2 ở cột J ( ô J3) , ca 3 ở cột K (ô K3) như sau

J3 =IF(OR(E3=””,F3=””),0,IF(AND(HOUR(E3)>12,HOUR(E3)<16,HOUR(F3)<24),1,IF(AND(HOUR(E3)>=22,HOUR(F3)<8),8,0)))

K3 =IF(OR(G3=””,H3=””),0,IF(AND(HOUR(G3)>12,HOUR(G3)<16,HOUR(H3)<24),1,IF(AND(HOUR(G3)>=22,HOUR(H3)<8),8,0)))

Copy công thức xuống các dòng phía dưới, chúng ta có kết quả như sau

Như vậy chúng ta đã tính được giờ làm đêm cho từng trường hợp chấm công rồi. Bây giờ có thể chuyển sang bước cuối cùng.

Đăng kí ngay: khóa học Excel kế toán 1 thầy 1 trò

Bước 4: Hoàn thiện bảng tính

Tạo các công thức tính tổng số công để hoàn thiện bảng tính.

Việc này sẽ giúp chúng ta có được kết quả cuối cùng như mong muốn.

L3 = SUM(I3:K3) => Filldown cho vùng L3:L23

Dòng tổng cộng L24 = SUM(L3:L23)

Bảng tính sau khi hoàn thiện như sau:

Chúng ta có thể mở rộng bảng tính cho đủ số ngày trong tháng (31 ngày) để tính cho bất kỳ tháng nào, và chỉ cần thay đổi thời gian chấm công của nhân viên khác vào bảng là có ngay kết quả cho nhân viên đó rồi.

Sử dụng Excel để ứng dụng cho việc lọc và tính toán dữ liệu rất hiệu nghiệm phải không nào. Để tận dụng tối đa tính năng vi diệu của Excel, bạn cần thành thạo các hàm, công cụ cũng như các tính năng khác. Tại khóa học