BÀI TẬP EXCEL NÂNG CAO (DÙNG THI CCUD CNTT NÂNG CAO)

 BÀI TẬP EXCEL NÂNG CAO

Bài 1:

Tập tin DLThiExcel.xlsx

Hướng dẫn

1. Xác thực dữ liệu: 

        Tháng: nhập số nguyên từ 1 đến 12

         Số ngày nghỉ phép: Nhập số nguyên không quá 5.

2. Tên công việc: chọn trong danh sách trên sheet Bangdo. Lưu ý: danh sách công việc được cập nhật thêm tự động.

3.Hệ số lương (Dùng công thức mảng): Thâm niên từ 5 đến 9 năm là 1.25; Thâm niên từ 10 năm trở lên là 1.3; Còn lại là 1.15. Trong đó thâm niên được tính từ ngày vào làm cho đến hiện tại

Excel 2019:

 =IF(DATEDIF(D4,DATE(2013,$F$2,1),"y")>=10,1.3,IF(DATEDIF(D4,DATE(2013,$F$2,1),"y")<5,1.15,1.25))

Excel 2016:

=IF(DATEDIF(D4:D18,DATE(2013,F2,1),"y")>=10,1.3,IF(DATEDIF(D4:D18,DATE(2013,F2,1),"y")<5,1.15,1.25))

4. Đơn giá công (dùng công thức mảng): Dựa vào thông tin Tên công việc và bậc để dò tìm trong các bảng trên sheet Bangdo. Trong đó, ký tự cuối của Mã nhân viên là bậc.

=INDEX(bangdo!$B$3:$D$7,MATCH(chitiet!$E$4:$E$18,bangdo!$A$3:$A$7,0),MATCH(RIGHT(chitiet!$B$4:$B$18)*1,bangdo!$B$2:$D$2,0))

5.  Số ngày làm (Dùng hàm Weekday & EOMONTH): được tính bằng số ngày trong tháng trừ chosố ngày chủ nhật trong tháng. Trong đó: Int((Ngày cuối tháng – ngày đầu tháng – Weekday(cuối tháng)+8)/7) = số ngày chủ nhật trong tháng

=DAY(EOMONTH(D4,0)-D4)+1-INT((EOMONTH(D4,0)-D4-WEEKDAY(EOMONTH(D4,0))+8)/7)

6.  Thưởng / phạt: Nếu không nghỉ ngày nào thưởng 200,000; Nghỉ từ 1 đến 3 ngày phạt 50,000 * số ngày nghỉ; Nghỉ >3 ngày phạt 100,000 * số ngày nghỉ. Trong đó: Thưởng: số dương, Phạt: số âm.

=IF(H4=0,200000,IF(H4>3,-100000*H4,-50000*H4))

7. Lương: Số ngày làm * Đơn giá công * Hệ số lương + Thưởng / Phạt. Định dạng tất cả các cột tiền là 1,000 đồng.

=I4*G4*F4+J4

8. Dùng Conditional Formatting tô nền tím – chữ trắng cho những dòng được thưởng, nền vàng – chữđỏ những dòng bị phạt.

9.

10.

 Bài 2:

 Bài 3:

 Bài 4:

 Bài 5:

 Bài 6:

 Bài 7:

 Bài 8:

 Bài 9:

 Bài 10:

 Bài 11: