Bài Tập Excel Bảng Thanh Toán Tiền Lương

Có thể bạn quan tâm: Cách Gắn Ảnh Thư Vị (attach A Picture To A Cell) Trong Excel: Hướng Dẫn Chi Tiết

Giới thiệu

Trong môi trường doanh nghiệp hiện đại, việc quản lý tiền lương là một công việc quan trọng và phức tạp. Đối với những người làm việc trong bộ phận nhân sự hoặc kế toán, Excel vẫn là công cụ chủ đạo để lập bảng tính, tính toán lương và phát hành bảng lương. Tuy nhiên, khi bạn chỉ biết cách nhập dữ liệu cơ bản và sử dụng một vài hàm đơn giản, bạn sẽ mất nhiều thời gian và dễ mắc lỗi khi thực hiện công việc hàng ngày.

Để giúp bạn nâng cao kỹ năng sử dụng Excel trong công việc lập bảng thanh toán tiền lương, bài viết này sẽ giới thiệu một loạt bài tập thực tế, từ cơ bản đến nâng cao. Mỗi bài tập sẽ đi kèm với hướng dẫn chi tiết, công thứcwag, và mẹo làm việc nhanh hơn. Bạn có thể áp dụng ngay vào công việc thực tế hoặc sử dụng làm tài liệu đào tạo cho đồng nghiệp.

Có thể bạn quan tâm: Ứng Dụng Của Microsoft Excel Trong Hóa Học Phân Tích

1. Chuẩn bị dữ liệu đầu vào

Trước khi bắt đầu xây dựng bảng tính, bạn cần chuẩn bị một số dữ liệu cơ bản:

1. Danh sách nhân viên: Mã NV, Họ tên, Phòng ban, Chức vụ, Khối lương, Ngày vào làm, Lương cơ bản, Thưởng, Phạt, Hệ số phụ cấp,κή.
2. Bảng chấm công: Mã NV, Tháng, Số ngày công, Số ngày nghỉ phép, Số ngày nghỉ bệnh, Số giờ làm thêm.
3. Quy định lương: Hệ số phụ cấp theo phòng ban, mức lương tối thiểu, khoản phụ cấp cố định (đi lại, cơm, phúc lợi), và các quyurcharge.

Bạn có thể tạo 3 sheet riêng//$\text{Sheet1} = Danh sách nhân viên$, $\text{Sheet2} = Chấm công$, $\text{Sheet3} = Quy định lương$.

Có thể bạn quan tâm: Thêm Số 0 Phía Trước Số Trong Excel: Hướng Dẫn Chi Tiết Và Các Phương Pháp Thực Tiễn

2. Bài tập 1: Tính lương cơ bản và phụ cấp

Mục tiêu

• Tính lương cơ bản hàng tháng dựa trên số ngày công thực tế.
• Áp dụng phụ cấp theo phòng ban và chức vụ.
• Lưu kết quả vào sheet “Bảng lương”.

Bước 1: Tạo bảng “Bảng lương”

Bước 2: Công thức tính lương cơ bản

Trong ô Lương cơ bản (cột D), nhập công thức:

=IF(E3=0,0,(C3/30)$B$1)

Giải thích:
– C3 là số ngày công thực tế trong tháng.
– $B$1 là mức lương cơ bản hàng tháng (đặt trong ô B1).
– Nếu số ngày công bằng 0, lương cơ bản sẽ là 0.

Bước 3: Tính phụ cấp

Phụ cấp 1: Phụ cấp phòng ban

Trong ô Phụ cấp 1 (cột E), sử dụng VLOOKUP:

=VLOOKUP(B3,PhòngBan!$A$2:$B$10,2,FALSE)

Giải thích:
– B3 là phòng ban của nhân viên.
– PhòngBan là sheet chứa bảng phụ cấp phòng ban.

Phụ cấp 2: Phụ cấp chức vụ

=VLOOKUP(C3,ChứcVụ!$A$2:$B$10,2,FALSE)

Phụ cấp 3: Phụ cấp cố định

=IF(D3>=5000000,200000,0)

Điều kiện: pas, nếu lương cơ bản ≥ 5 triệu, nhân viên nhận phụ cấp cố định 200k.

Bước 4: Tính tổng phụ cấp và tổng lương

• Tổng phụ cấp: =E3+F3+G3.
• Tổng lương: =D3+H3.

Kết quả của bài tập 1 sẽ tạo ra bảng lương cơ bản, bao gồm lương cơ bản, phụ cấp và tổng lương.

3. Bài tập 2: Tính lương dựa trên hệ số và tính thuế thu nhập cá nhân

Mục tiêu

• Tính lương cuối cùng sau khi trừ thuế TNCN.
• Sử dụng bảng hệ số lương và bảng thuế TNCN.

Bước 1: Bảng hệ số lương

Tạo sheet “HệSoLuong”:

Bước 2: Tính lương trước thuế

Trong ô Lương trước thuế (cột I), nhập:

=J3K3

Giải thích:
– J3 là lương cơ bản đã tính ở bài tập 1.
– K3 là hệ số lương.

Bước 3: Tính thuế TNCN

Bảng thuế TNCN dạng luỹ kế:

Trong sheet “ThuếTNCN”, nhập công thức:

=IF(I3<=5000000, I30.05, IF(I3<=10000000, (I3-5000000)0.10+250000, IF(I3<=18000000, (I3-10000000)0.15+750000, ...)))

Bước 4: Tính lương cuối cùng

• Lương cuối: =J3-L3.

Kết quả là lương thực nhận sau khi trừ thuế.

4. Bài tập 3: Tính lương theo giờ làm thêm

Mục tiêu

• Tính tiền làm thêm dựa trên số giờ làm thêm và mức lương/giờ chuẩn.
• Đưa vào bảng lương cuối cùng.

Bước 1: Tính lương/giờ

Trong ô Lương/giờ (cột M), nhập:

=I3/30/8

Giải thích:
– I3 lương cơ bản.
– 30 ngày, 8 giờ/ngày.

Bước 2: Tính tiền làm thêm

Trong ô Tiền làm thêm (cột N), nhập:

=O3L31.5

Giải thích:
– O3 số giờ làm thêm trong tháng.
– Mức lương làm thêm 1.5 lần mức lương/giờ.

Bước 3: Cập nhật lương cuối

• Lương cuối: =J3+N3-L3.

5. Bài tập 4: Tạo bảng lương tổng hợp với PivotTable

Mục tiêu

• Tạo báo cáo tổng hợp lương theo phòng ban, chức vụ, hoặc theo tháng.
• Dễ dàng tra cứu và phân tích dữ liệu.

Bước 1: Chuẩn bị dữ liệu

Sắp xếp dữ liệu vào một bảng duy nhất có các cột: Mã NV, Họ tên, Phòng ban, Chức vụ, Tháng, Tổng lương, Thuế, Tiền làm thêm.

Bước 2: Tạo PivotTable

• Chọn toàn bộ bảng dữ liệu.
• Vào Insert > PivotTable.
• Chọn New Worksheet.

Bước 3: Định dạng PivotTable

• Rows: Phòng ban.
• Columns: Tháng.
• Values: Tổng lương (Sum), Thuế (Sum), Tiền làm thêm (Sum).

Bước 4: Thêm slicer

Có thể bạn quan tâm: Automating Data Pull From Websites Into Excel Using Vba

• Vào PivotTable Analyze > Insert Slicer.
• Chọn Phòng ban, Chức vụ, Tháng.
• Sử dụng slicer để lọc dữ liệu nhanh.

Kết quả: Bảng PivotTable sẽ hiển thị tổng lương, thuế, tiền làm thêm theo phòng ban và tháng.

6. Bài tập 5: Tự động cập nhật lương cao hơn mức tối thiểu

Mục tiêu

• Kiểm tra lương cơ bản có vượt mức tối thiểu. Nếu thấp hơn, tự động tăng lên mức tối thiểu.
• Cập nhật lại bảng lương.

Bước 1: Định nghĩa mức tối thiểu

Trong ô P1, nhập mức lương tối thiểu hiện hành: 5,000,000.

Bước 2: Kiểm tra và điều chỉnh lương cơ bản

Trong ô Lương cơ bản (cột D) thay vì công thức =IF(E3=0,0,(C3/30)$B$1), dùng:

=MAX((C3/30)$B$1,$P$1)

Điều kiện: Nếu lương cơ bản tính được thấp hơn mức tối thiểu, thì lấy mức tối thiểu.

7. Bài tập 6: Tự động tính lương theo bảng dữ liệu nhân viên

Mục tiêu

• Khi nhập dữ liệu nhân viên, bảng lương tự động điều chỉnh.
• Sử dụng công thức INDEX + MATCH để tra cứu dữ liệu.

Bước 1: Tạo danh sách nhân viên

Sheet 1: DanhSachNV

| Mã NV | Họ tên | Phòng ban | Chức vụ | Lương cơ bản |…|

Bước 2발: Tạo bảng lương động

• Trong ngăn Bảng lương, dùng công thức INDEX/MATCH để lấy dữ liệu:

=INDEX(DanhSachNV!$D:$D,MATCH(A3,DanhSachNV!$A:$A,0))

• Tương tự cho các cột khác.

8. Bài tập 7: Tính phụ cấp phụ thuộc vào ngày công và ngày nghỉ

Mục tiêu

• Nếu nhân viên làm việc trong ngày lễ, tính phụ cấp ngày lễ.
• Nếu nghỉ phép trả lương, tính phụ cấp nghỉ phép.

Bước 1: Tạo danh sách ngày lễ

Sheet 3: NgayLe

Bước hack: Tính phụ cấp ngày lễ

Trong ô Phụ cấp ngày lễ (cột O):

=IF(COUNTIF(NgayLe!$A:$A,C3)>0,0.2D3,0)

• C3 là ngày công.
• Nếu ngày công nằm trong danh sách ngày lễ, nhân viên nhận 20% phụ cấp.

Bước 2: Tính phụ cấp nghỉ phép

Trong ô Phụ cấp nghỉ phép (cột P):

=IF(D3>0,0.1D3,0)

• Nếu nhân viên nghỉ phép trả lương (được tính lương trong số ngày nghỉ), nhận 10% phụ cấp.

9. Bài tập 8: Tạo bảng lương tự động cập nhật khi thay đổi dữ liệu

Mục tiêu

• Sử dụng tính năng Data Validation và Conditional Formatting để tránh lỗi nhập liệu.
• Tự động cập nhật bảng lương khi thay đổi dữ liệu.

Bước 1: Data Validation

• Chọn cột “Phòng ban”, vào Data > Data Validation > List.
• Chọn danh sách PhòngBan!$A$2:$A$10.

Bước 2: Conditional Formatting

• Đánh dấu các ô lương thấp hơn mức tối thiểu bằng màu đỏ.
• Chọn cột “Lương cơ bản”, vào Conditional Formatting > New Rule > Use a formula to determine which cells to format.
• Công thức: =$D3<$P$1.
• Đặt màu nền đỏ.

Bước 3: Sử dụng INDIRECT để tự động trích dữ liệu

• Khi nhập Mã NV mới, dùng:

=VLOOKUP(A3,$A$2:$B$20,2,FALSE)

opard.

10. Bài tập 9: Tự động gửi email lương

Mục tiêu

• Tự động gửi bảng lương cho từng nhân viên qua email.
• Sử dụng VBA để thực hiện.

Bước 1: Chuẩn bị dữ liệu

• Tạo cột Email trong bảng lương.

Bước 2: VBA

Sub GửiEmailLuong() Dim OutlookApp As Object Dim OutlookMail As Object Dim i As Integer Set OutlookApp = CreateObject("Outlook.Application") For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row Set OutlookMail = OutlookApp.CreateItem(0) OutlookMail.To = Cells(i, "J").Value 'cột Email OutlookMail.Subject = "Bảng lương tháng " & Cells(i, "K").Value OutlookMail.Body = "Kính gửi " & Cells(i, 슬릭) & "..." 'Nội dung email OutlookMail.Attachments.Add ThisWorkbook.FullName OutlookMail.Send Next i
End Sub

• Lưu macro và chạy.

11. Bài tập 10: Báo cáo lương theo KPI

Mục tiêu

• Tính lương dựa trên KPI hoàn thành.
• KPI có thể là số lượng bán hàng, số dự án hoàn thành, v.v.

Bước 1: Tạo bảng KPI

Sheet “KPI”:

Bước 2: Tính lương KPI

Trong ô Lương KPI (cột Q):

=IF(K3>=80,0.1J3, 0)

• Nếu KPI đạt ≥ 80%, nhân viên nhận thêm 10% lương.

Bước 3: Cập nhật tổng lương

• Lương cuối: =J3+Q3-L3.

12. Tổng kết

Như vậy, qua 10 bài tập thực tế, bạn đã học được cách:

1. Tạo bảng lương cơ bản, phụ cấp và tính lương cuối cùng.
2. Áp dụng thuế TNCN và làm thêm.
3. Tạo báo cáo tổng hợp bằng PivotTable.
4. Kiểm tra lương tối thiểu, tự động điều chỉnh.
5. Tính phụ cấp ngày lễ, nghỉ phép.
6. Sử dụng Data Validation và Conditional Formatting.
7. Gửi email lương ngosuku.
8. Tính lương KPI.

Để nâng cao hơn, bạn có thể thực hành:

• Tận dụng Power Query để nhập dữ liệu từ hệ thống ERP.
• Dùng Power Pivot để phân tích dữ liệu lương sâu hơn.
• Tự động cập nhật bảng lương khi dữ liệu nhân viên thay đổi.

Khi bạn đã thành thạo các bài tập này, việc lập bảng thanh toán tiền lương sẽ trở nên nhanh chóng, chính xác và dễ dàng quản lý. Chúc bạn thành công trong công việc!

Tiến sĩ Lê Thống Nhất

Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.

Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.

Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.