Bài tập thực hành Excel - Phần 2

BÀI THỰC HÀNH SỐ 9 
Cho bảng số liệu sau: 
A B C D E F G H I J K L M 
1 
DANH SÁCH LƯƠNG THÁNG 06/2004 
2 
 S T 
T 
TÊN Nhóm HSL HSTN Công Lương Loại Thưởng
Các khoản phải trừ 
Thực
lĩnh 
3 BHXH BHYT T.ỨNG
4 
1 Hà SX 1,78 1 22 
5 
2 Nhi VP 1,78 2 19 
6 
3 An SX 3,2 1 21 500000 
7 
4 Ti SX 1,56 2 23 
8 
5 Na VP 1,78 1 22 
9 
6 Uy VP 1,92 0,5 20 600000 
10 
7 Bi SX 2,15 0,5 20 
11 
8 Tèo SX 2,4 1,5 22 
12 
9 Ân VP 3,5 1,5 20 500000 
13 
10 Du SX 3,0 0,5 18 
14 
Công qui định : 
22 
 Bảng tiền thưởng 
Yêu cầu: 
Câu 1: Tính cột Lương biết rằng Lương = (HSL + HSTN)*Công * Đơn vị Tiền 
Lương/Công Qui định 
Câu 2: Tính Xếp loại cho nhân viên 
- Nếu Công >= Công qui định thì xếp loại A 
- Nếu trong tháng nghỉ không quá 2 ngày thì thì xếp loại B 
- Còn lại xếp loại C 
Câu 3: Tính Tiền thưởng cho nhân viên biết rằng Tiền thưởng sẽ được dựa theo Xếp 
loại của nhân viên (Trong Bảng Tiền thưởng) 
Câu 4: Tính cột BHXH = Lương * Tỷ lệ %BHXH và tính cột BHYT = Lương * Tỷ lệ %
BHYT 
Câu 5: Tính Thực Lĩnh = Lương + Thưởng - BHYT - BHXH - Tạm ứng 
Câu 6: Tính Tổng lương khối VP, Tổng lương khối SX 
Câu 7: Tính tổng số nhân viên nhóm VP: Đếm có bao nhiêu nhân viên thuộc nhóm VP 
15 
Đơn vị tiền lương: 290000 A B C 
16 
Bảo hiểm Y.Tế: 5% 300000 200000 50000 
17 
Bảo hiểm X.hội 2% 
18 Tổng lương khối VP: ? 
19 
Tổng lương khối SX: ? 
20 
Tổng số Nhân viên nhóm VP: ? 
BÀI THỰC HÀNH SỐ 10 
Cho bảng tính sau: 
BẢNG TÍNH TIỀN TRONG KHÁCH SẠN 
Yêu cầu: 
Câu 1: Tính cột số tuần biết rằng: Số tuần = (Ngày đi - Ngày đến)/7 và chỉ lấy phần 
nguyên 
Câu 2: Tính cột ĐG Tuần biết rằng ĐG Tuần được tính dựa theo Loại phòng (Bảng 
Giá theo Loại phòng). Kí tự đầu tiên bên trái của Cột Phòng Dịch vụ thể hiện Loại 
phòng 
Câu 3: Tính cột TT Tuần = Số tuần * ĐG Tuần 
Câu 4: Tính cột Số ngày = (Ngày đi -Ngày đến)/7 và chỉ lấy phần dư 
Câu 5: Tính cột ĐG Ngày : Tương tự như ĐG Tuần 
Câu 6: Tính cột TT Ngày = Số Ngày * ĐG Ngày 
Câu 7: Tính cột Dịch vụ:=(Ngày đi - Ngày đến)* Giá Dịch vụ mỗi ngày (Dựa theo Bảng 
Giá Dịch vụ và Loại phòng. Kí tự đầu tiên bên phải của Cột Phòng Dịch vụ thể hiện 
Loại phòng) 
Câu 8: Tính Cột Tổng = TT Tuần + TT Ngày + Dịch vụ 
 A B C D E F G H I J K L 
1 S 
T 
T 
TÊN NGÀY ĐẾN 
PHÒNG 
DỊCH 
VỤ 
TIỀN THANH TOÁN Ngày đi 20/05/2005 TỔNG
2 Số tuần
ĐG 
Tuần
TT 
Tuần
Số 
Ngày
ĐG 
Ngày
TT 
Ngày Dịch vụ 
3 1 Hà 10/05/2005 101-A 
4 2 Trung 07/05/2005 205-B 
5 3 Vinh 05/05/2005 109-C 
6 4 Mậu 07/05/2005 302-B 
7 5 Thanh 09/05/2005 108-C 
8 6 Dũng 16/05/2005 207-A 
9 7 Thu 14/05/2005 105-C 
10 8 Hoà 07/05/2005 301-C 
 A B C 
12 Loại phòng Giá tuần 
Giá 
ngày 
13 1 400 70 
14 2 280 50 
15 3 200 35 
 D E F 
12 A B C 
13 15 10 5 
BÀI THỰC HÀNH SỐ 11 
Cho bảng tính sau: 
BẢNG TIỀN HỌC BỔNG 
Yêu cầu: 
Câu 1: Tính cột Diện CS biết rằng Cột STT gồm 4 kí tự, kí tự thứ 2 cho biết Diện CS 
Câu 2: Tính cột Tổng Điểm: 
Tổng điểm = Môn CS + Môn CN1 + Môn CN2 + Điểm CS 
Trong đó: Điểm CS là 1 nếu Diện CS là 1, Điểm CS là 0,5 nếu Diện CS là 2. 
Còn lại Điểm CS là 0 
Câu 3: Tính Cột Loại HB biết rằng: 
- Loại HB = A nếu Tổng điểm >= 24 và không có môn nào nhỏ hơn 8 
- Loại HB = B nếu: 
Hoặc Tổng điểm >= 24 và không có môn nào nhỏ hơn 6 
Hoặc Tổng điểm >= 22 và không có môn nào nhỏ hơn 6 và Diện CS là 1 
- Còn lại là Loại C 
Câu 4: Tính cột Học Bổng (Dựa vào Loại Học bổng và được tra cứu trên bảng Loại 
học bổng) 
Câu 5: Tính cột Tổng: Tổng tiền học bổng đã phát cho từng Loại học bổng 
 A B C D E F G H I 
1 Loại HB Mức HB Tổng 
2 A 200000 
3 B 100000 
4 C 0 
5 
6 
STT Tên Diện CS 
Điểm thi Loại 
Học 
bổng 
Học 
bổng 7 Môn CS 
Môn 
CN1 
Môn 
CN2 
Tổng 
điểm 
8 D201 8 7 6 
9 D202 7 8 9 
10 N103 5 9 8 
11 D404 4 5 7 
12 D305 9 7 5 
13 N206 7 8 6 
BÀI THỰC HÀNH SỐ 12 
Nhập vào bảng số liệu sau: 
 Yêu cầu: 
Câu 1: Tính cột Xếp Loại: dựa vào số Ngày Công 
- Nếu Ngày Công >= 22 thì xếp loại A 
- Nếu Ngày Công >=20 thì xếp loại B 
- Nếu Ngày Công >=18 thì xếp loại C 
- Còn lại xếp loại D 
Câu 2: Tính cột Tiền Thưởng dựa vào Bảng Tiền thưởng sau: 
 A B C D E F 
1 STT Tên 
PL 
Lao 
động 
Ngày 
công 
Xếp 
Loại 
Tiền 
Thưởng 
2 1 CC 22 
3 2 TV 21 
4 3 DH 20 
5 4 CC 20 
6 5 TV 21 
7 6 DH 22 
8 7 DH 19 
9 8 TV 18 
10 9 DH 22 
 A B C D 
12 CC DH TV 
13 A 500000 400000 300000 
14 B 300000 200000 150000 
15 C 100000 0 0 
16 D 0 0 0 
BÀI TẬP PHẦN CSDL 
BÀI THỰC HÀNH SỐ 13 
Cho bảng số liệu sau: 
CHI TIẾT XUẤT NHẬP THÁNG 12/2003 
BẢNG ĐƠN GIÁ 
Yêu cầu: 
Câu 1: Nhập hai bảng trên vào Sheet1. Tính cột TTN biết rằng: TTN = SLN * ĐGN 
 A B C D E F G H 
1 STT NGAY MA HH SLN TTN SLX TTX MA KH 
2 1 02/02/2003 S12 50 0 SG01 
3 2 02/02/2003 GHN 140 0 HN15 
4 3 03/02/2003 S10 30 0 DN01 
5 4 03/02/2003 GTQ 30 0 DN03 
6 5 03/02/2003 S12 20 0 DN02 
7 6 04/02/2003 GTQ 45 0 SG02 
8 7 04/02/2003 GHN 0 75 SG01 
9 8 05/02/2003 V12 70 0 DN01 
10 9 06/02/2003 GTQ 0 45 HN15 
11 10 06/02/2003 S10 35 0 DN01 
12 11 07/02/2003 S10 0 60 HP14 
13 12 07/02/2003 X30 50 0 SG02 
14 13 08/02/2003 V24 50 0 HU02 
15 14 10/02/2003 S12 0 60 HP14 
16 15 10/02/2003 V12 0 45 DN12 
17 16 10/02/2003 V24 0 25 DN12 
18 17 12/02/2003 X30 0 40 HP03 
19 18 12/02/2003 X50 50 0 HU01 
20 19 15/02/2003 S10 0 25 DN01 
21 20 15/02/2003 X50 0 30 HP03 
 A B C D 
23 MA HH DVT GIA NHAP GIA XUAT 
24 GCO 1000 viên 200 215 
25 GHN 1000 viên 190 204 
26 GTQ 1000 viên 175 188 
27 S10 Tấn 500 537 
28 S12 Tấn 550 591 
29 S16 Tấn 450 483 
30 S20 Tấn 600 645 
31 V12 Tấm 25 26 
32 V24 Tấm 45 48 
33 X30 Tấn 300 322 
34 X40 Tấn 350 376 
35 X50 Tấn 400 430 
(ĐGN tra trên Bảng Đơn Giá dựa vào MA HH). Tính cột TTX biết rằng TTX = SLX * 
ĐGX (ĐGX tra trên Bảng Đơn giá dựa vào MA HH) 
Câu 2: Thực hiện Tổng hợp Dữ liệu theo từng mặt hàng 
Câu 3: Tiếp tục sao chép dữ liệu từ Sheet 1 sang Sheet 3 để có một CSDL tương tự. 
Sau đó thực hiện thao tác Lọc dữ liệu theo các yêu cầu sau: 
§ Sử dụng Lệnh Data - Filter - AutoFilter 
- Yêu cầu 1: Lọc dữ liệu chi tiết xuất nhập cho từng mặt hàng 
- Yêu cầu 2: Lọc chi tiết xuất nhập của mặt hàng Sắt (mặt hàng Sắt có MA HH bắt đầu 
bằng chữ S) 
- Yêu cầu 3: Lọc chi tiết hàng Nhập mà Khách hàng là ĐN 
- Yêu cầu 4: Lọc chi tiết hàng xuất nhập từ ngày 5 đến ngày 10/02/2003 
Chú ý: Sau khi thực hiện xong Yêu cầu 1 thì trả dữ liệu lại trạng thái ban đầu (hiển thị 
đầy đủ các bản ghi) rồi mới tiếp tục thực hiện yêu cầu tiếp theo. 
§ Sử dụng lệnh Data - Filter - Advanced Filter 
- Từ dữ liệu của bảng tính(Sheet 3), tạo một bảng dữ liệu mới (cũng nằm trong Sheet 
3) chỉ gồm dữ liệu của mặt hàng Nhập (Lúc đó TTX = 0) và Khách hàng từ Đà Nẵng 
(MA KH bắt đầu bằng chữ DN) 
Hướng dẫn: Tại Sheet 3 tạo một Bảng điều kiện lọc riêng nằm ở vị trí khác với Bảng 
dữ liệu gốc, sau đó dùng lệnh Data - Filter - Advanced Filter để lọc. 
 BÀI THỰC HÀNH SỐ 14 
Cho CSDL sau: 
Yêu cầu: 
Câu 1: Tính có bao nhiêu khách hàng xuất nhập từ ngày 05/02/2003 trở đi. Kết quả 
tính đựoc đặt trong ô D26 
Câu 2: Tính tổng SLN của những khách hàng là Đà Nẵng (những khách hàng có MA 
KH bắt đầu là DN). Kết quả tính được đặt trong ô B26 
Câu 3: Cho biết SLN lớn nhất đối với những khách hàng là Huế (MA KH bắt đầu là H) 
và xuất nhập trước ngày 10/02/2003. Kết quả đặt tại ô E26. 
Hướng dẫn: Trước hết phải tạo vùng điều kiện như bảng dưới, sử dụng các hàm trong 
CSDL để tổng hợp theo yêu cầu. 
 A B C D E F G H 
1 STT NGAY MA HH SLN TTN SLX TTX MA KH 
2 1 02/02/2003 S12 50 0 SG01 
3 2 02/02/2003 GHN 140 0 HN15 
4 3 03/02/2003 S10 30 0 DN01 
5 4 03/02/2003 GTQ 30 0 DN03 
6 5 03/02/2003 S12 20 0 DN02 
7 6 04/02/2003 GTQ 45 0 SG02 
8 7 04/02/2003 GHN 0 75 SG01 
9 8 05/02/2003 V12 70 0 DN01 
10 9 06/02/2003 GTQ 0 45 HN15 
11 10 06/02/2003 S10 35 0 DN01 
12 11 07/02/2003 S10 0 60 HP14 
13 12 07/02/2003 X30 50 0 SG02 
14 13 08/02/2003 V24 50 0 HU02 
15 14 10/02/2003 S12 0 60 HP14 
16 15 10/02/2003 V12 0 45 DN12 
17 16 10/02/2003 V24 0 25 DN12 
18 17 12/02/2003 X30 0 40 HP03 
19 18 12/02/2003 X50 50 0 HU01 
20 19 15/02/2003 S10 0 25 DN01 
21 20 15/02/2003 X50 0 30 HP03 
22 A B C D E F G H 
23 MA KH NGAY NGAY MA KH 
24 DN* >=05/02/2003 <10/02/2003 H* 
25 
26 ? ? ? 
BÀI THỰC HÀNH SỐ 15 
Cho bảng số liệu sau: 
 Hãy tạo đồ thị biểu diễn sự so sánh doanh thu giữa các quầy hàng qua các tháng 
bằng biểu đồ hình cột và hình tròn 
BÁO CÁO DOANH THU THÁNG 10 NĂM 2005 
CỬA HÀNG SỐ 1 
QUẦY HÀNG THÁNG 1 THÁNG 2 THÁNG 3 QUÝ 1 
Quầy điện tử 25.5 23 24.5 73 
Quầy xe máy 47.5 30 27 104.5 
Quầy đồ chơi trẻ em 7.5 2.5 5.5 15.5 
Quầy bếp ga 12.5 11.5 7.5 31.5 
Tổng cộng 93 67 64.5 224.5