Tài liệu Bài tập thực hành Excel: Chú ý: Học viên tạo một tập tin với tên là tenhocvien_số của bài thực hành
Bài thực hành số 1
Công thức và Hàm
A
B
A+ B
A-B
A*B
A/B
A lũy thừa B
A&B
1
10
3
8
5
6
7
4
9
2
A
B
A2 + B
(A + B)2
(A - B)*A
(A+B)*A - B
(A+B)*(A-B)
((A+B)/(A-B))4
16
4
5
3
Yêu cầu: học viên hoàn thành các công thức trên
Bài thực hành số 2
BẢNG LƯƠNG NHÂN VIÊN THÁNG …../2005
STT
HỌ VÀ TÊN
CHỨC VỤ
LƯƠNG
CƠ BẢN
NGÀY
LƯƠNG
TẠM ỨNG
CÒN LẠI
1
Nguyễn Văn A
TP
350000
25
2
Trần Văn B
NV
330000
24
3
Lương Sơn Bá
PP
340000
26
4
Chúc Anh Đài
NV
330000
26
5
Nguyễn Y Chang
KT
335000
20
6
Vũ Như Cẩn
BV
340000
28
7
Đổng Như Khôi
TQ
335000
26
8
Sang Song Luôn
PP
350000
24
9
Nguyễn Trùng Dương
NV
330000
27
10
Lục Vân Tiên
BV
335000
29
Tổng cho các cột Lương, Tạm Ứng, Còn lại
Giá trị cao nhất cho các cột Lương, Tạm Ứng, Còn lại
Giá trị thấp nhất cho các cột Lương, Tạm Ứng, Còn lại
Giá trị trung bình cho các cột Lương, Tạm Ứng, Còn ...
11 trang |
Chia sẻ: hunglv | Lượt xem: 2776 | Lượt tải: 1
Bạn đang xem nội dung tài liệu Bài tập thực hành Excel, để tải tài liệu về máy bạn click vào nút DOWNLOAD ở trên
Chú ý: Học viên tạo một tập tin với tên là tenhocvien_số của bài thực hành
Bài thực hành số 1
Công thức và Hàm
A
B
A+ B
A-B
A*B
A/B
A lũy thừa B
A&B
1
10
3
8
5
6
7
4
9
2
A
B
A2 + B
(A + B)2
(A - B)*A
(A+B)*A - B
(A+B)*(A-B)
((A+B)/(A-B))4
16
4
5
3
Yêu cầu: học viên hoàn thành các công thức trên
Bài thực hành số 2
BẢNG LƯƠNG NHÂN VIÊN THÁNG …../2005
STT
HỌ VÀ TÊN
CHỨC VỤ
LƯƠNG
CƠ BẢN
NGÀY
LƯƠNG
TẠM ỨNG
CÒN LẠI
1
Nguyễn Văn A
TP
350000
25
2
Trần Văn B
NV
330000
24
3
Lương Sơn Bá
PP
340000
26
4
Chúc Anh Đài
NV
330000
26
5
Nguyễn Y Chang
KT
335000
20
6
Vũ Như Cẩn
BV
340000
28
7
Đổng Như Khôi
TQ
335000
26
8
Sang Song Luôn
PP
350000
24
9
Nguyễn Trùng Dương
NV
330000
27
10
Lục Vân Tiên
BV
335000
29
Tổng cho các cột Lương, Tạm Ứng, Còn lại
Giá trị cao nhất cho các cột Lương, Tạm Ứng, Còn lại
Giá trị thấp nhất cho các cột Lương, Tạm Ứng, Còn lại
Giá trị trung bình cho các cột Lương, Tạm Ứng, Còn lại
Yêu Cầu:
1. Trình bày bảng tính trên
2. Tính lương tháng:=Lương Cơ Bản * Ngày Công
3. Tạm Ứng =50%*Lương Tháng
4. Còn Lại:=Lương Tháng - Tạm Ứng
5. Tính Tổng, Cao nhất, Thấp nhất, Trung bình cho các cột Lương, Tạm Ứng, Còn Lại
6. Định dạng các cột: Lương, Tạm Ứng, Còn Lại ở dạng "VNĐ"
Bài thực hành số 3
BẢNG KÊ HÀNG NHẬP THÁNG …./2004 TẠI KHO QUẢNG NGÃI
STT
CHỨNG
TỪ
DIỄN GIẢI
SỐ
LƯỢNG
ĐƠN GIÁ
THUẾ
CHUYÊN
CHỞ
CỘNG
1
B50
Bàn gỗ
40
1000000
2
B51
Bàn sắt
45
1500000
3
B52
Bàn Formica
85
550000
4
B53
Ghế dựa
42
360000
5
B54
Ghế xếp
12
450000
6
B55
Ghế đẩu
36
120000
7
B56
Tủ gổ
52
2500000
8
B57
Tủ sắc
54
3700000
Yêu Cầu:
1. Trình bày bảng tính trên
2. Chèn thêm cột trị giá vào sau cột đơn giá rồi tính Trị Giá:=Số Lượng*Đơn Giá
3. Tính Thuế:=Trị Giá*3.75% làm tròn đến hàng trăm.
4. Cước chuyên chở:=Số Lượng*0.75% Trị giá, làm tròn đến hàng nghìn.
5. Tính Tổng cộng:=Trị giá+Thuế+Chuyên chở
6. Định dạng cột Đơn giá, Thuế, Trị giá, Chuyên chở, Cộng có dấu phân cách phần nghìn.
Bài thực hành số 4
DOANH SỐ BÁN HÀNG CỦA TT THƯƠNG MẠI VÀ PHÁT TRIỂN CÔNG NGHỆ ĐẠI VIỆT
TỪ 1- 5/7/2001
Bảng 1
NGÀY
MÃ HÀNG
TÊN HÀNG
ĐƠN VỊ TÍNH
SỐ LƯỢNG
ĐƠN GIÁ
THUẾ
THÀNH TIỀN
1
MT
3
1
C
2
1
MI
1
2
BP
2
2
MT
2
2
UPS
1
3
HDD
4
3
HDD
2
3
MT
1
3
MH
2
4
MT
2
4
C
10
4
MI
3
5
ODC
6
5
CS
4
5
FDD
8
5
MT
3
Bảng 2
MÃ HÀNG
TÊN HÀNG
ĐƠN VỊ TÍNH
ĐƠN GIÁ
MT
Máy tính
Bộ
5200000
C
Chuột
Con
35000
MI
Máy in
Cái
4200000
BP
Bàn phím
Cái
60000
UPS
Tích điện
Cái
850000
HDD
Ổ đĩa cứng
Ổ
1000000
CDR
Ổ đĩa CDROM
Ổ
450000
MH
Màn hình
Cái
1420000
CS
Case
Cái
170000
FDD
Đĩa mềm
Hộp
65000
Yêu cầu:
Câu 1: Dựa vào số liệu đã cho ở Bảng 2, dùng công thức để lấy Tên hàng, Đơn vị tính, Đơn giá cho Bảng 1 trên.
Câu 2: Tính tiền thuế (cột Thuế), biết rằng:
Nếu là Máy tính hoặc Máy in thì Thuế = 5% đơn giá.
Còn những thiết bị khác thì Thuế = 10% đơn giá.
Câu 3: Tính số tiền (cột Tiền) cho các mặt hàng được bán ra trong Bảng 1.
Câu 4: Chèn vào cuối Bảng 1 3 dòng để hiển thị các kết quả sau:
Lần lượt tính tổng số tiền bán Máy tính, Máy in, Màn hình, ....
Tính số lần bán mặt hàng Máy tính, Máy in, Màn hình, ....
Bài thực hành số 5
Tạo bảng tính với số liệu thô như sau:
KẾT QUẢ TUYỂN SINH NĂM 2006 - ĐẠI HỌC ĐÀ NẴNG
STT
SBD
HỌ TÊN
ĐTCS
TOÁN
LÝ
HÓA
ĐUT
TỔNG
KẾT QUẢ
1
BK001
Tô Linh Kha
LS
?
?
?
?
?
?
2
BK002
Trúc Lan Mai
TB
3
BK003
Cổ Gia Trưởng
BB
4
BK004
Chúc Anh Đài
KH
5
KT001
Hồ Mỹ Nhân
TB
6
KT002
Đoàn Tự Cổ
KH
7
SP001
Cao Bất Hứa
LS
8
SP002
Vi Nhân Gian
TB
BẢNG CHẤM
BẢNG ĐIỂM ƯU TIÊN
SBD
MÔN 1
MÔN 2
MÔN 3
ĐTCS
LS
TB
BB
BK001
6
7
6
ĐIỂM UT
2
1
0.5
KT001
9
9
5
BK002
3
7
5
SP001
9
7
6
BK003
8
8
7
KT002
7
6
9
SP002
7
4
5
BK004
4
3
4
Trong đó:
SBD: Số báo danh dự thi của thí sinh
ĐTCS: Mã đối tượng chính sách, TB (thương binh), LS (liệt sỹ); BB (bệnh binh); KH(không)
ĐUT: Điểm ưu tiên, tuỳ thuộc vào ĐTCS cho trong Bảng điểm ưu tiên, nếu thí sinh nào có ĐTCS là “KH” thì Điểm ưu tiên (ĐUT) bằng không
TỔNG: Tổng số điểm bao gồm điểm 3 môn thi cộng với điểm ưu tiên
KẾT QUẢ: Kết quả thi (ĐẬU hay KHÔNG), biết rằng chỉ tiêu của Nhà trường là tuyển vào 5 thí sinh có tổng điểm thi cao nhất
HƯỚNG DẪN
Các công thức sử dụng: IF, VLOOKUP, HLOOKUP, RANK kết hợp với địa chỉ tuyệt đối
Tính điểm Toán (môn 1): =VLOOKUP()
Tính điểm Lý (môn 2): =VLOOKUP()
Tính điểm Hoá (môn 3): =VLOOKUP()
Tính Điểm ưu tiên: =IF(HLOOKUP())
Tính Tổng điểm: = Toán + Lý + Hóa + Điểm Ưu Tiên.
Tính Kết quả:=IF(RANK("ĐẬU", "KHÔNG")
Bài thực hành số 6
Bảng Giá Thanh Toán Tiền Điện Tháng 12/2005
CHỦ HỘ
LOẠI
SÔCŨ
SỐMỚI
SỐKW
ĐM
TĐM
NĐM
T.TĐM
T.NĐM
CỘNG
MỘT
CQ-A
200
250
?
?
?
?
?
?
?
HAI
NN-D
240
280
BA
CB-A
340
450
BỐN
CQ-A
430
540
NĂM
CB-B
230
450
SÁU
CB-C
120
540
BẢY
CN-D
210
350
TÁM
CN-C
260
410
CHÍN
CQ-B
290
450
MƯỜI
CQ-D
320
540
NHẤT
NN-A
110
230
NHÌ
KD-C
230
410
Chú thích:
Bảng số KW định mức
SỐKW: Số KW tiêu thụ
A
B
C
D
ĐM: Số KW định mức
CQ
50
60
70
75
TĐM: Số KW tiêu thụ trong định mức
CB
40
42
55
65
NĐM: Số KW tiêu thụ ngoài định mức
CN
20
50
70
95
T.TĐM: Số tiền trong định mức
KD
60
80
95
120
T.NĐM: Số tiền ngoài định mức
NN
30
60
75
85
CỘNG: Tổng số tiền phải trả
Bảng đơn giá (trong và ngoài định mức)
CB
CQ
CN
KD
NN
Chú thích:
TĐM
1200
2300
5400
5500
3000
TĐM: Đơn giá trong định mức
NĐM
1000
1800
4500
4000
2500
NĐM: Đơn giá ngoài định mức
Bài thực hành số 7
KẾT QUẢ KỲ THI TIN HỌC KHOÁ NGÀY ....../....../200....
STT
HỌ TÊN
NĂM SINH
QUÊ QUÁN
ĐLT
ĐTH
ĐTBKT
T.BÌNH
VỊ THỨ
GHI CHÚ
1
Nguyễn Văn A
1985
Đà Nẵng
3
6
5
2
Trần Văn B
1968
Quảng Nam
7
2
7
3
Lương Sơn Bá
1974
Quảng Ngãi
5
7
5
4
Chúc Anh Đài
1972
Huế
3
6
7
5
Vũ Như Cẩn
1977
Bình Định
4
4
5
6
Nguyễn Y Vân
1958
Quảng Ngãi
7
2
3
7
Wright York
1971
Tobaco&Trinida
4
8
7
8
Trần Bình Trọng
1968
Quảng Ngãi
8
9
7
9
Zidine Zidane
1972
France
6
9
4
10
David Beckham
1973
England
7
9
4
Yêu cầu:
1. Trình bày bảng tính trên.
2. Tính điểm trung bình cho các học viên: T.BÌNH=(ĐLT+ĐTH+ĐTBKT)/3, làm tròn số với 2 số lẻ ở phần thập phân.
3. Xếp hạng cho lớp theo dạng [Vị thứ] / [Tổng số]. Ví dụ : 1/10, 5/10.
4. Nếu các học viên có cùng vị thứ với nhau thì cột GHI CHÚ phải ghi chú là Đồng hạn.
5. Sắp xếp danh sách học viên theo thứ tự tăng dần của Năm sinh, nếu trùng thì theo thứ tự giảm dần của cột T.BÌNH.
6. Dùng Data\Filter\Advanced Filter để lọc trích ra danh sách những học viên ở Quảng Ngãi.
Bảng kết quả sau khi thực hiện các yêu cầu trên
STT
HỌ TÊN
NĂM SINH
QUÊ QUÁN
ĐLT
ĐTH
ĐTBKT
T.BÌNH
VỊ THỨ
GHI CHÚ
1
Nguyễn Văn A
1985
Đà Nẵng
3
6
5
4.67
8/10
2
Trần Văn B
1968
Quảng Nam
7
2
7
5.33
6/10
Đồng hạn
3
Lương Sơn Bá
1974
Quảng Ngãi
5
7
5
5.67
5/10
4
Chúc Anh Đài
1972
Huế
3
6
7
5.33
6/10
Đồng hạn
5
Vũ Như Cẩn
1977
Bình Định
4
4
5
4.33
9/10
6
Nguyễn Y Vân
1958
Quảng Ngãi
7
2
3
4.00
10/10
7
Wright York
1971
Tobaco&Trinida
4
8
7
6.33
3/10
Đồng hạn
8
Trần Bình Trọng
1968
Quảng Ngãi
8
9
7
8.00
1/10
9
Zidine Zidane
1972
France
6
9
4
6.33
3/10
Đồng hạn
10
David Beckham
1973
England
7
9
4
6.67
2/10
QUÊ QUÁN
Đây là vùng điều kiện để lọc trích(Criteria)
Quảng Ngãi
Kết quả sau khi lọc trích
STT
HỌ TÊN
NĂM SINH
QUÊ QUÁN
ĐLT
ĐTH
ĐTBKT
T.BÌNH
VỊ THỨ
GHI CHÚ
3
Lương Sơn Bá
1974
Quảng Ngãi
5
7
5
5.67
5/10
6
Nguyễn Y Vân
1958
Quảng Ngãi
7
2
3
4.00
10/10
8
Trần Bình Trọng
1968
Quảng Ngãi
8
9
7
8.00
1/10
Bài thực hành số 8
BÁO CÁO KINH DOANH KHO VẬT TƯ
STT
HỌ & TÊN
CHỨNG TỪ
TÊN VẬT TƯ
SỐ LƯỢNG
ĐƠN GIÁ
THÀNH TIỀN
1
A
X01C
50
2
B
D02K
1050
3
C
N03C
150
4
D
X04C
2550
5
E
D05K
250
6
F
G06C
320
7
G
D08K
120
8
H
G08C
240
9
I
X01C
300
10
J
N09K
125
TỔNG CỘNG
TRUNG BÌNH
BẢNG PHỤ 1
MÃ CT
D
G
N
X
TÊN VẬT TƯ
DẦU LỬA
GASOL
NHỚT
XĂNG
ĐƠN GIÁ
120
140
130
100
BẢNG PHỤ 2
SCT
THUẾ
D
70%
G
80%
N
90%
X
95%
Lưu bảng tính trên với tên Kinhdoanhvattu
1. Xác định Tên vật tư, Đơn Giá thông qua bảng phụ 1
2. Tính cột Thành Tiên:=Số lượng*Đơn giá*Thuế
3. Tính Tổng Cộng và Trung Bình cho các cột.
4. Sắp xếp bảng tính theo thứ tự tăng dần của cột thành tiền
5. Vẽ đồ thị dạng 3D Pie cho cột số lượng (theo cột họ tên)
6. Tạo bảng dữ liệu(Lọc) gồm các mẫu tin có tên vật tư là Dầu lửa có số lượng bán ra lớn hơn 200
7. Lập bảng thống Kê:
Tổng số tiền bán được của các mặt hàng
Dầu lửa
Xăng
Nhớt
Bài thực hành số 9
BẢNG BÁO CÁO KINH DOANH XĂNG
TRAM XĂNG DẦU 1/5
KD
CC
XUẤT
3500
2500
NHẬP
3300
1200
STT
K-HÀNG
SỐ-CT
SỐ-LG
TIỀN CHI
TIỀN THU
T-NHẬP
T-XUẤT
1
A
N100C
2
B
X2000K
3
C
X120K
4
D
N230C
5
E
X1200C
6
F
N2230K
7
G
N1000C
8
H
X200K
9
I
X300K
10
J
N1200K
11
K
X2000K
12
L
X120K
13
M
N2000C
14
N
N300K
15
O
X1220K
TỔNG SỐ
Diễn Giải:
- Ký tự đầu tiên của số chứng từ cho biết hàng xuất hay nhập:
+ "X" là xuất, "N" là nhập
- Ký tự cuối cùng cho biết hàng xuất(nhập) theo giá cung cấp hay kinh doanh
+ "K":kinh doanh, "C": cung cấp
- Các ký tự còn lại cho biết số lượng hàng xuất hay nhập
Yêu cầu:
1. Tính(a): Số lượng hàng xuất, nhập
2. Tính tiền chi và tiền thu: Số lượng * Đơn giá
+ Tiền chi là tiền nhập hàng hóa và tiền thu là tiền xuất hàng hóa
+ Ký tự cuối cùng cho biết tính theo giá kinh doanh hay cung cấp
3. Tính thuế nhập và thuế xuất biết:
+ Nếu số lượng Nhập theo giá Cung cấp lớn hơn 800 thì phải nộp thuế là 7% Tiền chi, ngược lại 10% Tiền chi
+ Nếu số lượng Xuất theo giá Kinh doanh lớn hơn 800 thì phải nộp thuế là 5% Tiền thu, ngược lại 10% Tiền thu
4. Tính tổng của các cột
5. Lưu bảng tính với tên tenhocvien_9
6. Định dạng cột tiền có dấu phần cách phần nghìn và theo đơn vị tiền tệ "Đồng"
Bài thực hành số 10
TÍNH TIỀN Ở KHÁCH SẠN
STT
HỌ VÀ TÊN
MÃ SỐ
NGÀY ĐẾN
NGÀY ĐI
SNGAY
TUẦN
NGÀY
T.NGÀY
T.TUẦN
1
Nguyễn Văn Hay
A1
4/12/2001
25/04/2001
100000
2
Trần Tự Lực
B2
5/23/2001
12/06/2001
120000
3
Nguyễn Thị Mai Lê
A1
3/14/2001
16/04/2001
100000
4
Phan Thanh Thùy Linh
C2
6/16/2001
20/06/2001
160000
5
Lê Thị Tuyết Mai
A2
5/1/2001
30/05/2001
100000
6
Trấn Vân Quỳnh
B1
5/2/2001
14/05/2001
120000
7
Dương Cao Anh Dương
C2
6/12/2001
26/06/2001
160000
8
Trần Minh Hảo
C1
6/10/2001
17/06/2001
160000
9
Lê Văn Phận
B3
6/16/2001
24/06/2001
120000
Tổng cộng
BẢNG 1:TIỀN NGÀY
BẢNG 2:TIỀN TUẦN
Phòng đơn
Phòng đôi
Phòng đơn
Phòng đôi
A
100000
150000
A
600000
900000
B
120000
170000
B
720000
1020000
C
160000
180000
C
960000
1080000
Yêu cầu:
1. Nhập và trình bày bảng tính
2. Tính cột số ngày: Ngày đi - Ngày đến
3. Dùng hàm INT() Tính số tuần ở khác sạn của khách cho cột Tuần (13 ngày = 1 tuần + 6 ngày)
4. Dùng hàm MOD() Tính số ngày lẻ ở khách sạn của khách cho cột Ngày
5. Tính cột T.Ngày (Tiền ngày) dựa vào Mã số và Bảng 1, số 1 bên phải chỉ phòng đơn Số 2 chỉ phòng đôi
6. Tính tiền tuần dựa vào mã số và Bảng 2
7. Cột Tổng tiền := Tiền Tuần + Tiền Ngày
Bài thực hành số 11
SỬ DỤNG HÀM MATCH VÀ INDEX
HÃY NHẬP BẢNG TÍNH SAU
Ngày
Nơi Đi
Nơi Đến
Cự Ly
Đơn Giá
Thành Tiền
05/03/2003
Đà Nẵng
Tam Kỳ
06/05/2003
Tam kỳ
Huế
12/05/2003
Sài Gòn
Đà Nẳng
17/06/2003
Đà Nẵng
Đà Nẳng
20/06/2003
Quảng Ngãi
Đà Nẳng
25/06/2003
Hội An
Huế
Bảng Cự Ly
Nơ Đi/Nơi Đến
Đà Nẵng
Tam Kỳ
Sài Gòn
Hội An
Huế
Đà Nẵng
10
74
1000
30
100
Tam Kỳ
74
10
960
44
144
Sài Gòn
1000
960
30
970
1100
Hội An
30
44
970
5
130
Huế
100
144
1100
130
15
Quảng Ngãi
135
60
900
104
204
Yêu Cầu
1. Điền dữ liệu cho cột cự ly
2. Tính đơn giá theo yêu cầu sau
- Cự ly <100 Km: Đơn giá là 3.000 đồng
- 100 <= cự ly < 500 Km: Đơn giá là 2.500 đồng
- Cự ly >=100 Kmlà 2.200 đồng
3. Tính thành tiền =Cự ly * Đơn giá
4. Tính Tổng cộng cho cột thành tiền
5. Hãy chèn thêm một cột tháng vào sau cột ngày. Sau đó, dựa vào số liệu cột ngày để điền dữ liệu cho cột tháng.
6. Tính thống kê Tổng tiền của từng tháng
7. Lọc danh sách cho những chuyến có Nơi đi hoặc Nơi đến là Đà Nẵng
Bài thực hành số 12
BÁO CÁO DOANH THU THÁNG 10/2002
STT
MÃ VT
TÊN VT
NƠI BÁN
SỐ LG
TRỊ GIÁ
CƯỚC PHÍ
HUÊ HỒNG
THÀNH TIỀN
1
S001L
HN
200
2
C002S
CT
150
3
X003S
SG
250
4
G004L
VT
500
5
S005L
HN
260
6
S006S
SG
368
7
C007L
CT
640
8
X008L
HN
100
9
G009S
VT
680
10
S010L
SG
420
TỔNG CỘNG
PHỤ BẢNG 1:
MÃ VT
TÊN VT
GIÁ SỈ (S)
GIÁ LẺ(L)
C
CÁT
40000
45000
G
GẠCH
1800
2000
S
SẮT
3000
3500
X
XI MĂNG
75000
90000
PHỤ BẢNG 2
NƠI BÁN
CT
HN
SG
VT
ĐƠN GIÁ BÁN
1000
600
200
800
YÊU CẦU :
1.Tên vật tư : Dựa vào lý tự đầu tiên của cột mã và bảng phụ 1
2. Trị giá:số lượng * đơn giá (đơn giá được dò đến bảng phụ 1 ,kí tự cuối của mã vt là "S" là giá sỉ ;
"L" là giá lẻ)
3. Cột cước phí dựa vào Nơi bán và bảng phụ 2
4. Tính huê hồng : Nếu số lượng >=500 thì huê hồng =5% trị giá
ngược lại huê hồng =0
5. Tính cột thành tiền
6. Lập bảng thống kê:
Tên hàng
Cát
Gạch
Sắt
Xi măng
Tổng doanh thu
7. Dựa vào bảng thống kê vẽ đồ thị biểu diễn doanh thu cho từng vật tư
8. Sắp xếp thứ tự theo mã vt
9. Kẻ khung và lưu trữ
----Hết phần thực hành----
Chúc bạn thành công!
Các file đính kèm theo tài liệu này:
- Bài tập Excel 02.doc