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
1
2
3

Họ và tên

Mã SV

Ngày sinh

Nguyễn Thị An 1234107123 20/09/1995

Trần Thanh Bình 123410431 01/05/1994
Lê Văn Cường 1234104221 18/10/1994

CC

GK

TH

Thi

8
5
6

9
4
7

7
8
7

8
3
5

Điểm
học
phần


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

1


BẢNG THANH TOÁN LƯƠNG CÔNG NHÂN THÁNG 8

TT
1
1
2
3

Họ và tên


HSL

2
Đặng Thuý Hà
Đặng Trung Kiên
Nguyễn Mạnh Tuấn
Tổng cộng
Trung bình
Lớn nhất
Nhỏ nhất

3
3.3
6.5
2.5

Lương
chính
?

BHXH
?

Lương cơ bản
Tạm
BHYT
ứng
?
?


540000
Thực
lĩnh
?

?
?
?
?

?
?
?
?

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

Tên khách

Thanh Hà
Anh Xuân
Hà An
Anh Tuấn

Đối
tượng
1
2
2
1

Ngày
vay
12/6/96
20/2/96
15/3/95
30/6/95

Ngày trả
% lãi quá hạn/ngày
Ngày
Tiền vay Tiền lãi
đến hạn
12/6/97
2000000
15/6/96
1500000
20/6/96
3000000

10/4/96
2500000

12/6/97
0.0002
Tiền lãi
Quá hạn

Tổng
cộng

b. Yêu cầu:
1. 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ự.
2. Tính Tiền lãi = tiền vay *(ngày đến hạn – Ngày vay)* 0.001
3. 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
4. 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
5. Đếm số người thuộc đối tượng 1

2


6. Tính tổng tiền tổng cộng của những đối tượng 2
7. Đổ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

Nguyễn Thành
Phan Anh Tuấn
Nguyễn Thu Thuỷ
Anh Tuấn

B
A
C
A

Ngày đến

Ngày đi

09/01/00
07/04/00
06/09/00
05/10/00

12/01/00
18/04/00
21/09/00
25/10/00

Số
tuần


Giá
tuần

Số ngày
dư

Giá
ngày

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
1
2
3


Họ và tên
Trần Văn A
Nguyễn Thị B
Nguyễn C

Điểm
toán
5
5
4

Điểm
tin
5
7
5

Điểm
anh
6
5
6

ĐTBC

Kết quả

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 anh*2)/4
2. 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”.

3


3. 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

1
2
3
4

HỌ TÊN

Nguyễn A
Lê B
Nguyễn A
Trần C

NGÀY
LÀM
VIỆC


GIỚI
TÍNH

SỐ
CON

HỆ
SỐ

1
1
0
0

3
1
1
1

4.46
3.35
4.46
4.36

LOẠI

LƯƠNG

PHỤ
CẤP

CON

TIỀN
LĨNH

27
19
22
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

1
2
3

4

HỌ VÀ TÊN

Nguyễn Bình
Trần Kiên
Lê Nguyên
Phạm Thị Lan

LPH

N. ĐẾN

N. ĐI

NGÀY Ở

TIỀN
PHÒNG

THUẾ
GTGT

TỔNG

B11
A18
C19
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:
1. Điền cột STT tự động. Tự nhập số liệu vào cho các cột: N. ĐẾN, N. ĐI.
2. Tính NGÀY Ở = N. ĐI – N. ĐẾN
3. 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

4


4. Tính THUẾ GTGT=10% TIỀN PHÒNG
5. 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

1
2
3
4

MÃ VT

V001
V002
V003
V004


TÊN VT

Màn hình
Vỏ máy tính
Bàn phím
Loa

ĐƠN VỊ
TÍNH

TỒN
ĐẦU KỲ

Bộ
Chiếc
Bộ
Bộ

NHẬP XUẤT

10
5
20
15

TỒN CUỐI
KỲ

15
15

30
5

VẬT TƯ NHẬP
Mã VT
V001
V002
V003
V004

Số lượng
10
35
45
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

1
2
3
4
5


Họ và tên

Nguyễn thanh
Lê Bình
Phan Anh
Kim Dung
Trần Kiên

Chức
vụ

GĐ
PGĐ
TP
NV
NV

Lương
ngày

Ngày
công

100000
85000
70000
60000
45000


Phụ
cấp

Lương
tháng

25
26
28
23
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.

5

Tạm
ứng

Còn lại


Nu chc v l NV v s ngy cụng <=25 thỡ khụng cú ph cp
2. Ct Lng thỏng c tớnh theo cụng thc:
Lng thỏng = Lng ngy * Ngy cụng, nhng nu Ngy cụng > 25 thỡ mi

ngy cụng d c tớnh gp ụi.
3. Tớnh ct tm ng theo cụng thc: Tm ng = (Lng thỏng + Ph cp)*75%
4. Tớnh ct Cũn li theo cụng thc: Cũn li = Lng thỏng + ph cp Tm ng
Cõu 10:
a. To bng tớnh sau:
BNG GHI IM

STT

H v tờn

im
toỏn
5
5
4
6

im
Tin
5
7
8
7

im
vn
6
9
6

7

Xp
loi

TBC

Th
hng

1
Trn A
2
Nguyn B
3
Nguyn C
4
Phan An
b. Yờu cu:
1. Tớnh TBC theo cụng thc sau:
TBC = (im toỏn + im tin + im vn*2)/4
(Lm trũn 2 ch s thp phõn)
2. Cn c vo TBC xp loi hc lc bit rng:
Nu TBC >=8 v khụng cú mụn no di 6.5 xp loi Gii
Nu 8>TBC>=6.5 v khụng cú mụn no di 5 xp loi Khỏ
Cũn li xp loi Trung bỡnh
3. Cn c vo ct TBC hóy xp th hng cho hc sinh.
Bi 11: Bi tp tng hp
a. To bng biu theo mu sau trong Excel:


S THUấ BNG VIDEO
ST
T

Tên
khách

mã
khách

Ngày thuê

1

Châu

CA1TB

09/04/07

2

Hồng

NB2SL

08/05/07

3


Hùng

HB9SB

10/05/07

4

Liễu

VB7TB

11/05/07

5

Mai

TA2SL

12/05/07

Ngày
trả
11/04/
07
09/05/
07
13/05/
07

12/05/
07
14/05/
07

6

SN
thuê

Loại

Đơn
giá

Tiền
trả


6

V©n

NA5TL

22/05/
07

20/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

1
2
3
4
5
6

Nguyễn Văn An
Đoàn Văn Bình
Lê Thị Hồng Hoa
Nguyễn Tiến Mạnh
Nguyễn Văn Xuân
Trần Thị Kim Yến

Ngày sinh Khu vực


18/01/79
23/06/83
15/06/80
03/06/80
12/08/85
26/08/84

Điểm thi
Toán

II
I
I
III
II
I

7
0
5
5
4
4

Văn
8
7
9
0

7
6

NN
6
5
7
6
3
2

Điểm
cộng KV

Tổng
điểm

Trúng
tuyển

1
2
2
0
0
2

27
25
28

25
23
20

TT
Hỏng
TT
Hỏng
TT
TT

Điểm chuẩn

18

BẢNG 1
I
2

Khu vực
Điểm cộng

II
1

III
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

7

Tầng

Thu nhập


1
2
3
4

lượng

47
25
68
60

V01AD
V02AD
V01AD
V04AD
BẢNG 1

Mã NV

01
02
03
04

1
2
2
3

BẢNG 2: ĐƠN GIÁ CÔNG

Tên NV

Tầng

Nghĩa

Bình
Tùng
Bảo

1
2
3

<=30KG

1000
2000
3000

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.

8

>30-60KG

4000
5000
6000

>60KG


7000
8000
9000