Bảng lương tháng 10 năm 2022

A B C D E F G H I J K L
4 ST Tên MNV MĐV Đ SLSP Đ L HS T T T
T V G SP N Ư L
5 1 An 01DH4 SX-PX1 ? 300 ? ? 1.8 ? ? ?
6 2 Bình 02NH2 SX-PX2 ? 150 ? ? 1.4 ? ? ?
7 3 Công 03NH6 QL-PX1 ? 100 ? ? 2.2 ? ? ?
8 4 Danh 04DH4 QL-PX2 ? 100 ? ? 1.8 ? ? ?
9 5 Đào 05NH2 SX-PX3 ? 180 ? ? 1.4 ? ? ?
10 6 Giang 06DH2 SX-PX3 ? 390 ? ? 1.4 ? ? ?
11 7 Hùng 07DH1 SX-PX1 ? 300 ? ? 1.2 ? ? ?
12 8 Khoa 08DH7 QL-PX3 ? 120 ? ? 2.4 ? ? ?
13 9 Loan 09NH5 QL-PX4 ? 100 ? ? 2.0 ? ? ?
14 10 Minh 10NH3 SX-PX4 ? 290 ? ? 1.6 ? ? ?

16 Bảng đơn giá

17 SX QL
18 PX1 18 185
0
19 PX2 11 120
0
20 PX3 15 150
0
12 PX4 20 215
0

Hãy nhập và trình bày bảng tính trên, thực hiện các yêu cầu sau:

1) Điền thông tin cột "Đơn vị" dựa vào 2 ký tự đầu của Mã đơn vị: SX: Sản xuất;
QL: Quản lý
=IF(LEFT(D5,2)="SX","Sản xuất","Quản lý")
2) Điền thông tin cho cột Đơn giá dựa vào bảng phụ
=VLOOKUP(RIGHT(D5,3),$A$17:$C$21,IF(LEFT(D5,2)="SX",2,3),0)
3) Lập công thức tính lương sản phẩm: LƯƠNG SP = SỐ LƯỢNG SP * ĐƠN GIÁ
=F5*G5
4) THU NHẬP: Nếu thuộc bộ phận quản lý (QL) thì THU NHẬP = LƯƠNG SP * HỆ SỐ,
ngược lại: THU NHẬP chính là LƯƠNG SP.
 =IF(E5="Quản lý",H5*I5,H5)
5) TẠM ỨNG: Công nhân có cấp bậc từ 5 trở lên sẽ được tạm ứng bằng 1/3 của mức
THU NHẬP, ngược lại TẠM ỨNG là 1/5 mức THU NHẬP.
=ROUND(IF(RIGHT(C5,1)>=5,1/3*J5,1/5*J5),-2)
6) Điền thông tin cho cột THỰC LĨNH = THU NHẬP - TẠM ỨNG
=J5-K5
7) Rút trích ra những công nhân viên có mức thu nhập >= 50.000
8) Sử dụng Subtotal tính tổng số lượng sản phẩm theo đơn vị
Sắp xếp đơn vị từ a – z, subtotal chọn đơn vị + sum + số lượng sản
phẩm, ok
9) Tạo danh sách trỏ xuống của mã nhân viên, hiển thị thông tin về họ tên, đơn vị,
thực lĩnh của nhân viên tương ứng
10) Sử dụng Conditional formatting đổi màu nền thông tin nhân
viên có thực lĩnh cao nhất

BẢNG THEO DÕI NHẬP XUẤT HÀNG

Mã
STT Loại Hàng Nhập Xuất Đơn giá Tiền Thuế
hàng
1 A001Y A 2650 1590 110000 174900000 13992000
6 A002N A 2410 1446 135000 195210000 21473100
4 A011N A 2820 1692 135000 228420000 25126200
8 A012N A 2680 1608 135000 217080000 23878800
A Average 128750
3 B003Y B 1430 1001 110000 110110000 24224200
7 B004Y B 2710 1897 110000 208670000 45907400
2 B012N B 2240 1568 135000 211680000 35985600
9 B013Y B 1460 1022 110000 112420000 24732400
5 B054Y B 1160 812 110000 89320000 19650400
B Average 115000
Grand
Average 121111.1

Yêu cầu:

1) Nhập và định dạng dữ liệu như trên:

2) Tính cột Xuất như sau:
+ Nếu Mã hàng có ký tự đầu là A thì Xuất = 60% * Nhập
+ Nếu Mã hàng có ký tự đầu là B thì Xuất = 70% * Nhập
=IF(LEFT(B4,1)="A",60%*D4,70%*D4)
3) Tính Đơn giá như sau:
+ Nếu Mã hàng có ký tự cuối là Y thì Đơn giá = 110000
+ Nếu Mã hàng có ký tự cuối là N thì Đơn giá = 135000
=IF(RIGHT(B4,1)="Y",110000,135000)

4) Tính cột Tiền = Xuất * Đơn giá.

5) Cột Thuế được tính như sau:
+ Nếu Mã hàng có ký tự đầu là A và ký tự cuối là Y thì Thuế = 8% của Tiền
+ Nếu Mã hàng có ký tự đầu là A và ký tự cuối là N thì Thuế = 11% của Tiền
+ Nếu Mã hàng có ký tự đầu là B và ký tự cuối là Y thì Thuế = 17% của Tiền
+ Nếu Mã hàng có ký tự đầu là B và ký tự cuối là N thì Thuế = 22% của Tiền.
=IF(AND(LEFT(B4,1)="A",RIGHT(B4,1)="Y"),8%*G4,
IF(AND(LEFT(B4,1)="A",RIGHT(B4,1)="N"),11%*G4,
IF(AND(LEFT(B4,1)="B",RIGHT(B4,1)="Y"),17%*G4,22%*G4)))
6) Chèn cột "Loại hàng" vào sau cột "Mã hàng", điền thông tin biết Loại hàng là ký tự đầu tiên
của Mã hàng
Chọn cột bên phải – insert
Loại hàng =LEFT(B4,1)
7) Sử dụng Subtotal tính trung bình cộng đơn giá theo loại hàng
Bôi đen – data – subtotal – loại hàng + average + đơn giá – ok
8) Tạo danh sách trỏ xuống cho phép tra cứu toàn bộ thông tin của một mặt hàng theo
mã hàng
Tạo bảng – chọn ô cần tạo ds – data – data validation – list – bôi đen cột mã hàng – ok
Loại hàng =VLOOKUP(B24,$B$3:$H$11,2,0)
Nhập =VLOOKUP(B24,$B$3:$H$11,3,0)
Xuất =VLOOKUP(B24,$B$3:$H$11,4,0)
Đơn giá =VLOOKUP(B24,$B$3:$H$11,5,0)
Tiền =VLOOKUP(B24,$B$3:$H$11,6,0)
Thuế =VLOOKUP(B24,$B$3:$H$11,7,0)
24 Mã hàng Loại hàng, nhập, xuất, đgia, tiền, thuế

25
9) Trích ra danh sách các mặt hàng loại B, vẽ biểu đồ dạng Column phản ánh tỉ lệ
tổng SL nhập và xuất của các mặt hàng này
Bôi đen bảng – data – filter – loại hàng – tắt select all – chọn B – ok – copy paste – tắt filter
Sắp xếp - Bôi đen ( mã hàng, loại hàng, nhập, xuất) – insert – column ( đầu tiên)
– chart layouts ( đầu tiên)
10) Sử dụng Conditional formatting đổi màu nền thông tin mặt hàng có số lượng xuất
nhiều nhất
Bôi đen Xuất – home – conditional formatting – top/botton rules – top 10
items - chọn số 1 – oke
 Phụ
S
Chứ Lương Ngày cấp Tạm
T Họ Tên Đơn vị Lương Còn lại
c vụ CB công chức ứng
T
vụ
3 Đoàn An TP Tổng hợp 3000 25 300 75,000 25,000 50,300
5 Hồ Kim PGĐ Tổng hợp 4000 26 400 104,000 25,000 79,400
Tổng hợp
Total 179,000
8 Lê Nam TP Quản trị 3000 30 300 90,000 25,000 65,300
Quản trị
Total 90,000
Nguyễn
7 Văn Sơn KT Kế toán 1000 30 250 30,000 20,000 10,250
Kế toán
Total 30,000
9 Hồ Tấn Tài NV Tổng hợp 1000 26 100 26,000 17,000 9,100
4 Lê Thanh GĐ Tổng hợp 5000 28 500 140,000 25,000 115,500
Tổng hợp
Total 166,000
2 Nguyễn Thành BV Quản trị 1000 30 100 30,000 20,000 10,100
Quản trị
Total 30,000
6 Trần Thế TP Kế toán 2000 29 300 58,000 25,000 33,300
Trần
1 Thị Yến NV Kế toán 1000 24 100 24,000 16,000 8,100
Kế toán
Total 82,000
577,000
Grand
Total
198,00
TỔNG CỘNG: 2,350 1,072,000 0 381,350
TRUNG BÌNH: 261 76,571 22,000 42,372
CAO NHẤT: 500 179,000 25,000 115,500
CAO THỨ HAI: 400 166,000 25,000 79,400
THẤP NHẤT: 100 24,000 16,000 8,100
Yêu cầu

1) Nhập và xác định dữ liệu như trên:

Tổng cộng: =SUM(H4:H12) Trung bình=AVERAGE(H4:H12)
Cao nhất: =LARGE(H4:H12,1) Cao thứ 2=LARGE(H4:H12,2)
Thấp nhất: =MIN(H4:H12)
2) Phụ cấp chức vụ được tính dựa vào chức vụ:
 + GĐ: 500 + KT: 250
+ PGĐ: 400 + Các trường hợp khác: 100
+ TP: 300
=IF(D4="GĐ",500,IF(D4="PGĐ",400,IF(D4="TP",300,IF(D4="KT",250,1
00))))
3) Lương = lương căn bản*ngày công: =F4*G4
4) Tạm ứng được tính như sau:
- Nếu (phụ cấp chức vụ + lương)*2/3 < 25000 thì tạm ứng = (phụ cấp chức
vụ + lương)*2/3
- Ngược lại: tạm ứng = 25000 ( làm tròn đến hàng nghìn)

=ROUND(IF((H4+I4)*2/3<25000,(H4+I4)*2/3,25000),-3)

5) Còn lại = phụ cấp chức vụ + lương – tạm ứng =H4+I4-J4

6) Tháng: dùng hàm lấy ra tháng hiện hành =MONTH(TODAY()) tháng mới:
=MONTH(NOW())
7) Điền vào bảng thống kê phía trên
8) Sử dụng subtotal tính tổng lương theo đơn vị
+ sắp xếp lại đơn vị theo thứ tự A – Z
+ data – subtotal – (đơn vị + sum + lương) – ok
9) Tạo bảng tổng hợp số liệu lương và ngày công theo đơn vị, tạo biểu đồ minh
họa
10) Tạo danh sách trỏ xuống của tên nhân viên, hiển thị thông tin về đơn
vị, chức vụ của nhân viên tương ứng.

Tạo bảng ( tên, đơn vị, chức vụ ) – data – data vali – list – bôi đen cột tên – ok

Chức vụ = VLOOKUP(M28,$C$3:$E$12,2,0)

Đơn vị = VLOOKUP(M28,$C$3:$E$12,3,0)