Bài thực hành: Bảng tính Microsoft Excel

BÀI THỰC HÀNH: BẢNG TÍNH MICROSOFT EXCEL
1. Mục tiêu
Sinh viên có thể tạo lập các bảng biểu trong Excel và sử dụng thành thạo một số hàm để tính toán trong bảng biểu.
2. Nội dung
Định dạng bảng tính
Các tính toán với hàm.
3. Thực hiện
Bài 1: Định dạng bảng tính
a. Mở bảng tính mới và soạn thảo văn bản theo mẫu sau:
BẢNG ĐIỂM MÔN TIN HỌC CƠ SỞ
Lớp: Tin học cơ sở 01
TT
Họ và tên
Mã SV
Ngày sinh
CC
GK
TH
Thi
Điểm học phần
1
Nguyễn Thị An
1234107123
20/09/1995
8
9
7
8
2
Trần Thanh Bình
123410431
01/05/1994
5
4
8
3
3
Lê Văn Cường
1234104221
18/10/1994
6
7
7
5
	Nghệ An, ngày tháng năm
	Giáo viên
	Trần Văn A
b. Yêu cầu:
- Đánh số thứ tự tự động.
- Tính điểm học phần theo công thức: 
CC*0,1 + GK*0,2 + (TH*10/45+Thi*35/45)
- Cho xem văn bản trước khi in (Print Preview)
- Ghi tập tin với tên bangdiem.xlsx
- Ghi lại tập tin với tên BANGDIEM.xls
Bài 2: Định dạng bảng tính; địa chỉ tương đối, tuyệt đối. Một số hàm đơn giản: Sum(), Average(), Max(), Min().
a. Soạn thảo bảng tính theo mẫu sau:
CÔNG TY SẢN XUẤT THIẾT BỊ ĐIỆN
BẢNG THANH TOÁN LƯƠNG CÔNG NHÂN THÁNG 8
Lương cơ bản
540000
TT
Họ và tên
HSL
Lương chính
BHXH
BHYT
Tạm ứng
Thực lĩnh
1
2
3
?
?
?
?
?
1
Đặng Thuý Hà
3.3 
2
Đặng Trung Kiên
6.5 
3
Nguyễn Mạnh Tuấn
2.5 
Tổng cộng
?
?
Trung bình
?
?
Lớn nhất
?
?
Nhỏ nhất
?
?
b. Yêu cầu:
1. Ghi nội dung trên với tên Bai tap Excel.xls (C:\Bai thuc hanh\ Bai tap Excel.xls) 
2. Tính lương chính: Lương chính = HSL * Lương cơ bản
2. Tính BHXH: BHXH = 5% * Lương chính
3. Tính BHYT: BHYT = 1% * Lương chính
4. Tạm ứng 30% lương cho tất cả công nhân viên
5. Tính thực lĩnh: Thực lĩnh = Lương chính - BHXH - BHYT - Tạm ứng
6. Đổi tên Sheet1 thành Bai1 và thực hiện lưu file
Bài 3: Định dạng bảng tính: Định dạng dữ liệu ngày tháng. Hàm CountIf(), SumIf()
a. Chọn Sheet2 trong tệp C:\Bai thuc hanh\ Bai tap Excel.xls, tạo bảng tính sau:
DANH SÁCH KHÁCH HÀNG VAY TIỀN TIẾT KIỆM
Ngày trả
12/6/97
 % lãi quá hạn/ngày
0.0002
Tên khách
Đối tượng
Ngày vay
Ngày đến hạn
Tiền vay
Tiền lãi
Tiền lãi Quá hạn
Tổng cộng
Thanh Hà
1
12/6/96
12/6/97
2000000
Anh Xuân
2
20/2/96
15/6/96
1500000
Hà An
2
15/3/95
20/6/96
3000000
Anh Tuấn
1
30/6/95
10/4/96
2500000
b. Yêu cầu:
Chèn thêm một cột trước cột Tên khách làm cột Số thứ tự. Sử dụng chức năng đánh số thứ tự tự động để đánh số thứ tự cho cột Số thứ tự.
Tính Tiền lãi = tiền vay *(ngày đến hạn – Ngày vay)* 0.001
Tính tiền lãi quá hạn như sau: 
Tiền lãi quá hạn = tiền vay * (Ngày trả - Ngày đến hạn)* %Lãi quá hạn
Tính tiền tổng cộng như sau: Tổng cộng = Tiền vay + tiền lãi + Tiền quá hạn
Đếm số người thuộc đối tượng 1
Tính tổng tiền tổng cộng của những đối tượng 2
Đổi tên Sheet2 thành Bai2 và thực hiện lưu File
Bài 4: Một số hàm trong Excel: Hàm Int(), Mod(), If()
a. Soạn thảo văn bản theo mẫu sau:
BẢNG KÊ CHI PHÍ THUÊ KHÁCH SẠN
Họ và Tên
Loại phòng
Ngày đến
Ngày đi
Số tuần
Giá tuần
Số ngày dư
Giá ngày
Nguyễn Thành 
B
09/01/00
12/01/00
Phan Anh Tuấn
A
07/04/00
18/04/00
Nguyễn Thu Thuỷ
C
06/09/00
21/09/00
Anh Tuấn
A
05/10/00
25/10/00
b. Yêu cầu:
- Tính Số tuần = Int((Ngày đi- Ngày đến)/7)
- Tính Số ngày = Mod(Ngày đi- ngày đến,7)
- Điền Giá ngày như sau:
Nếu Loại phòng A thì 250000 đồng/ ngày
Nếu Loại phòng B thì 200000 đồng/ ngày
Nếu Loại phòng C thì 150000 đồng/ ngày
- Điền Giá tuần
Nếu Loại phòng A thì 1000000 đồng/ tuần
Nếu Loại phòng B thì 900000 đồng/ tuần
Nếu Loại phòng C thì 800000 đồng/ tuần
- Chèn thêm cột Số tiền phải trả và tính theo công thức sau:
Số tiền phải trả = Số tuần* Giá tuần + Số ngày dư * Giá ngày.
Bài 5: Hàm If(); sắp xếp dữ liệu
a. Tạo bảng tính sau trong Excel 
BẢNG GHI ĐIỂM
STT
Họ và tên
Điểm toán
Điểm tin
Điểm anh
ĐTBC
Kết quả
1
Trần Văn A
5
5
6
2
Nguyễn Thị B
5
7
5
3
Nguyễn C
4
5
6
b. Yêu cầu:
Tính ĐTBC theo công thức sau:
ĐTBC = (Điểm toán + Điểm tin + Điểm anh*2)/4
Căn cứ vào ĐTBC tính kết quả cho từng người biết rằng nếu ĐTBC >=5 thì kết quả là “Đậu”, ngược lại “Trượt”. 
Sắp xếp theo tự giảm dần của cột ĐTBC.
Bài 6: Hàm If(), And()
a. Tạo bảng biểu theo mẫu sau trong Excel:
BẢNG TÍNH LƯƠNG 
STT
HỌ TÊN
GIỚI TÍNH
SỐ CON
HỆ SỐ
NGÀY LÀM VIỆC
LOẠI
LƯƠNG
PHỤ CẤP CON
TIỀN LĨNH
1
Nguyễn A
1
3
4.46
27
2
Lê B
1
1
3.35
19
3
Nguyễn A
0
1
4.46
22
4
Trần C
0
1
4.36
26
Ghi chú: Cột Giới tính: số 0 là nữ, số 1 là nam
b. Yêu cầu: Tính toán ở các cột LOẠI, LƯƠNG, PHỤ CẤP CON, TIỀN LĨNH theo công thức:
* Cột LOẠI:
	+ Nếu ngày làm việc lớn hơn 26 ngày xếp loại A
	+ Nếu ngày làm việc từ 22 ngày đến 26 ngày xếp loại B
+ Nếu ngày làm việc nhỏ hơn 22 xếp loại C
* Cột LƯƠNG = HỆ SỐ * 210000
* Cột PHỤ CẤP CON:
	+ Nếu giới tính là nữ và có từ 2 con trở lên được lĩnh phụ cấp con là 100000
+ Nếu giới tính là nữ và có 1 con được lĩnh phụ cấp con là 50000
* Cột TIỀN LĨNH = LƯƠNG + PHỤ CẤP CON
Bài 7: Định dạng ngày tháng. Kết hợp hàm If() và hàm Left()
a. Tạo bảng tính sau trong Excel theo mẫu sau:
BẢNG TÍNH TIỀN THUÊ KHÁCH SẠN
STT
HỌ VÀ TÊN
LPH
N. ĐẾN
N. ĐI
NGÀY Ở
TIỀN PHÒNG
THUẾ GTGT
TỔNG
1
Nguyễn Bình
B11
2
Trần Kiên
A18
3
Lê Nguyên
C19
4
Phạm Thị Lan
A12
Trong đó: cột LPH sử dụng 1 ký tự đầu là loại phòng, 2 ký tự sau là số hiệu phòng.
b. Yêu cầu:
Điền cột STT tự động. Tự nhập số liệu vào cho các cột: N. ĐẾN, N. ĐI.
Tính NGÀY Ở = N. ĐI – N. ĐẾN
Tính TIỀN PHÒNG = NGÀY Ở * giá từng loại phòng được cho bởi:
A=350000, B=250000, C=200000
Tính THUẾ GTGT=10% TIỀN PHÒNG
Tính TỔNG CỘNG =TIỀN PHÒNG+THUẾ GTGT
Bài 8: Hàm Vlookup()
a. Tạo bảng theo mẫu sau trong Excel:
BÁO CÁO VẬT TƯ
STT
MÃ VT
TÊN VT
ĐƠN VỊ TÍNH
TỒN ĐẦU KỲ
NHẬP
XUẤT
TỒN CUỐI KỲ
1
V001
Màn hình
Bộ
10
15
2
V002
Vỏ máy tính
Chiếc
5
15
3
V003
Bàn phím
Bộ
20
30
4
V004
Loa
Bộ
15
5
 VẬT TƯ NHẬP
Mã VT
Số lượng
V001
10
V002
35
V003
45
V004
15
b. Yêu cầu:
- Dùng hàm VLOOKUP tra cứu số lượng NHẬP
- Tính TỒN CUỐI KỲ = TỒN ĐẦU KỲ + NHẬP – XUẤT
Bài 9: Hàm If(), And()
a. Tạo bảng tính như sau:
BẢNG LƯƠNG THÁNG 4
Stt
Họ và tên
Chức vụ
Lương ngày
Ngày công
Phụ cấp
Lương tháng
Tạm ứng
Còn lại
1
Nguyễn thanh
GĐ
100000
25
2
Lê Bình
PGĐ
85000
26
3
Phan Anh
TP
70000
28
4
Kim Dung
NV
60000
23
5
Trần Kiên
NV
45000
28
b. Yêu cầu:
1. Dùng hàm IF tính cột phụ cấp theo quy tắc:
	Nếu chức vụ là GĐ thì phụ cấp là 300000.
	Nếu chức vụ là PGĐ thì phụ cấp là 200000.
	Nếu chức vụ là TP thì phụ cấp là 100000.
	Nếu chức vụ là NV và số ngày công > 25 thì phụ cấp 50000.
	Nếu chức vụ là NV và số ngày công <=25 thì không có phụ cấp
2. Cột Lương tháng được tính theo công thức: 
Lương tháng = Lương ngày * Ngày công, nhưng nếu Ngày công > 25 thì mỗi ngày công dư được tính gấp đôi.
3. Tính cột tạm ứng theo công thức: Tạm ứng = (Lương tháng + Phụ cấp)*75%
4. Tính cột Còn lại theo công thức: Còn lại = Lương tháng + phụ cấp – Tạm ứng
Câu 10: 
a. Tạo bảng tính sau:
BẢNG GHI ĐIỂM
STT
Họ và tên
Điểm 
toán
Điểm 
Tin
Điểm 
văn
ĐTBC
Xếp loại
Thứ hạng
1
Trần A
5
5
6
2
Nguyễn B
5
7
9
3
Nguyễn C
4
8
6
4
Phan An
6
7
7
b. Yêu cầu:
1. Tính ĐTBC theo công thức sau:
	ĐTBC = (Điểm toán + Điểm tin + Điểm văn*2)/4 
(Làm tròn 2 chữ số thập phân)
2. Căn cứ vào ĐTBC để xếp loại học lực biết rằng: 
Nếu ĐTBC >=8 và không có môn nào dưới 6.5 xếp loại “Giỏi” 
Nếu 8>ĐTBC>=6.5 và không có môn nào dưới 5 xếp loại “Khá”
Còn lại xếp loại “Trung bình”
3. Căn cứ vào cột ĐTBC hãy xếp thứ hạng cho học sinh.
Bài 11: Bài tập tổng hợp
a. Tạo bảng biểu theo mẫu sau trong Excel:
SỔ THUÊ BĂNG VIDEO
STT
Tªn kh¸ch
m· kh¸ch
Ngµy thuª
Ngµy tr¶
SN thuª
Lo¹i
§¬n gi¸
TiÒn tr¶
1
Ch©u
CA1TB
09/04/07
11/04/07
2
Hång
NB2SL
08/05/07
09/05/07
3
Hïng
HB9SB
10/05/07
13/05/07
4
LiÔu
VB7TB
11/05/07
12/05/07
5
Mai
TA2SL
12/05/07
14/05/07
6
V©n
NA5TL
20/05/07
22/05/07
b. Yêu cầu:
1. SN thuê = Ngày trả - Ngày thuê	
2. Loại căn cứ theo mã ký tự cuối của Mã khách, biết L là Phim lẻ, còn B là Phim bộ.
3. Đơn giá: Nếu ký tự thứ 2 của Mã khách là A thì là 5000, là B thì 3000.	
4. Tiền trả= Số băng thuê * Số ngày thuê * Đơn giá
(Số băng thuê là ký tự thứ 3 của mã khách)
5. Định dạng cột Tiền trả theo dạng Curency ($) và không có số lẻ.
6. Sắp xếp cột Tên khách theo thứ tự tăng dần	
Bài 12: Bài tập tổng hợp
a. Tạo bảng tính như sau:
Stt
Họ tên
Ngày sinh
Khu vực
Điểm thi
Điểm
cộng KV
Tổng điểm
Trúng
tuyển
Toán
Văn
NN
1
Nguyễn Văn An
18/01/79
II
7
8
6
1
27
TT
2
Đoàn Văn Bình
23/06/83
I
0
7
5
2
25
Hỏng
3
Lê Thị Hồng Hoa
15/06/80
I
5
9
7
2
28
TT
4
Nguyễn Tiến Mạnh
03/06/80
III
5
0
6
0
25
Hỏng
5
Nguyễn Văn Xuân
12/08/85
II
4
7
3
0
23
TT
6
Trần Thị Kim Yến
26/08/84
I
4
6
2
2
20
TT
 Điểm chuẩn
18
BẢNG 1
Khu vực
I
II
III
Điểm cộng
2
1
0
b. Yêu cầu:	
1. Điểm cộng KV được dò tìm trong BẢNG 1 dựa theo Khu vực.
2. Tổng Điểm = Điểm Toán + Điểm Văn + Điểm NN * 2 + Điểm cộng KV.
3. Trúng Tuyển: Tổng điểm lớn hơn hoặc bằng Điểm chuẩn và không có môn nào bị điểm 0 thì đạt "TT" ngược lại "HỎNG".
4. Lọc ra những thí sinh có kết quả “TT”	
5. Sắp xếp danh sách theo cột Trúng tuyển " TT" lên trên. Nếu trùng thì sắp theo cột Tổng điểm giảm dần.
Bài 13: Bài tập tổng hợp
a. Tạo bảng tính như sau:
BẢNG TÍNH CÔNG
STT
Mã NV
Tên nhân viên
Khối lượng
Tầng
Thu nhập
1
V01AD
47
1
2
V02AD
25
2
3
V01AD
68
2
4
V04AD
60
3
BẢNG 1
BẢNG 2: ĐƠN GIÁ CÔNG
Mã NV
Tên NV
Tầng
<=30KG
>30-60KG
>60KG
01
Nghĩa
1
1000
4000
7000
02
Bình
2
2000
5000
8000
03
Tùng
3
3000
6000
9000
04
Bảo
b. Yêu cầu:
1. Tên NV: Dựa vào ký tự 2,3 của mã NV và tra trong Bảng 1
2. Thu nhập: tra trong Bảng 2: Đơn giá công
3. Tính bảng thu nhập của từng nhân viên.
4. Sắp xếp theo thứ tự giảm dần của cột tổng thu nhập.
5. Vẽ biểu đồ so sánh thu nhập của từng nhân viên.