Hướng dẫn giải Đề 9 Excel Tin Học Văn Phòng

BÀI TẬP EXCEL BẢNG TÍNH THUẾ THU NHẬP

Đề bài:

1. Chức vụ và phụ cấp dựa vào ký tự đầu của Mã CV và tra trong bảng Chức vụ và phụ cấp

2. HS Lương: Nếu ký tự cuối của Mã NV là T thì = 0.75 còn lại tra trong bảng Hệ số lương và bậc năm công tác là hai ký tự 2 và 3 của Mã NV

3. Thu nhập:  = Lương + Phụ. cấp trong đó Lương = Lương CB*HSL*Ngày Công.

Nếu ngày công >20 thì phần ngày công vượt lên 20 được tính gấp đôi.

4. Thuế: Nếu Thu nhập >=15,000,000 thì phần thu nhập vượt lên phải nộp 25%, Nếu Thu nhập vượt lên 9,000,000 thì phần vượt lên phải nộp 10%, còn dưới 9,000,000 không phải nộp thuế.

5. Sắp xếp bảng Lương theo Tên tăng dần, nếu trùng thì theo thu nhập

6. Lập bảng thống Kê

7. Trích ra các đối tượng có thu nhập >= 10,000,000

Bài làm:

1. Chức vụ: =VLOOKUP(LEFT(B4,1),$A$16:$C$21,2,0). Trong đó LEFT(B4,1) là lấy 1 ký tự đầu của Mã NV, 2 là số thứ tự của cột Chức vụ trong bảng Chức vụ và Phụ cấp.

Phụ cấp:  =VLOOKUP(LEFT(B4,1),$A$16:$C$21,3,0).  Trong đó 3 là số thứ tự của cột Phụ Cấp trong bảng Chức vụ và Phụ cấp.

2. Hệ số lương:  =IF(RIGHT(B4,1)="T",0.75,VLOOKUP(VALUE(MID(B4,2,2)),$E$16:$F$20,2,0)). Trong đó RIGHT(B4,1)="T" là ký tự cuối của Mã NV là T, MID(B4,2,2) là lấy hai ký tự 2 và 3 của Mã NV, VALUE là hàm chuyển ký tự số sang dạng số.

3. Thu nhập:  =F4+D4*IF(H4>20,20+(H4-20)*2,H4). Trong đó (H4-20)*2 là số ngày vượt lên 20 ngày sẽ được tăng gấp đôi.

4. Thuế:

=IF(I4>15000000,(I4-15000000)*25%+(6000000*10%),IF(I4>9000000,(I4-9000000)*10%,0)). Trong đó (I4-15000000)*25% là 25%  Phần vượt lên mức quy định 15,000,000.  6000000*10% là mức chênh lệch giữa 15 triệu và 9 triệu. (I4-9000000)*10% 10% của phần vượt lên 9 triệu.

5. Sắp xếp (Làm các bước sau)

Bước 1: Giữ chuột kéo hết bảng chính chọn Data/Sort xuất hiện hộp thoại sau đó

Bước 2: Tại Sort By chọn Tên NV, Sort on chọn Value, Order chọn A to Z

             Tại Then By chọn Thu nhập, Sort on chọn Value, Order chọn Smallast to Largest. (Như hình ở dưới)

6. Thống kê:

Tổng số lương của người là nhân viên =SUMIF(E4:E13,E11,I4:I13)

Số người là nhân viên =COUNTIF(E4:E13,E11)

7. Trích ra các đối tượng có thu nhập >= 10,000,000

Bước 1: Chọn Data/Advanced xuất hiện hộp thoại

Bước 2: Tại Action Chọn coppy....

Tại List Range giữ chuột bôi đen bảng chính

Tại Creatian Range Bôi đen bảng nhỏ vừa tạo (bảng thu nhập >=15,000,000

Tại Coppy to chọn ô phía dưới bảng A34 sau đó nhấn OK kết thúc.