lOMoAR cPSD| 47708777
BÀI TẬP THỰC HÀNH MICROSOFT EXCEL
Bài tập số 1: Hãy nhập vào bảng số liệu sau
Bảng lương tháng 1 năm 2005 Stt Họ ệm Tên NC HS LC
BHXH Thưởng Lĩnh 1 Nguyễn Văn Thìn 23 2.90 2 Đỗ Hồng Quân 26 3.12 3 Trần Thuý Hằng 25 2.90 4 Bùi Thị Hương 22 4.50 5 Lê Văn Minh 27 3.37 6 Nguyễn Đình Chiến 23 5.12 7 Lê Thu Hà 24 3.37 8 Trần Văn Đoàn 21 2.90 9 Ngô Thu An 26 5.12 Tổng Trung bình Max Min
0 - Định dạng bảng tính và trang in theo yêu cầu: Định dạng bảng giống như trên, khổ giấy A4,
lề trên 2.5 cm, lề dưới 2.5 cm, lề trái 2 cm, lề phải 2 cm 1 - Tính các cột, các dòng theo các công thức sau:
+ LC = HS * 310000 + BHXH = 0.05 * LC
+ Thưởng: Nếu NC > 25 thì Thưởng = LC * 2
Nếu 23 < NC <= 25 thì Thưởng = LC * 1.5
Nếu NC <= 23 thì Thưởng = LC * 1.2
+ Lĩnh = LC + Thưởng - BHXH
+ Tính các dòng Tổng, Trung bình, Max, Min cho các cột số. 2 -
Sắp xếp theo cột NC giảm dần và HS tăng dần
3 - Tìm những người có HS >= 3 và NC > 24.
4 - Vẽ ồ thị dạng 3-D column của các cột NC, HS. lOMoAR cPSD| 47708777
Bài tập số 2: Hãy nhập vào bảng số liệu sau
BẢNG THỐNG KÊ THU NHẬP TRONG 3 NĂM Thu Thu Thu Trung Tên Tổng Loại nhập 95 Đầu tư
nhập 96 nhập 97 bình Đơn vị 1 1,100 1,250 1,800 Đơn vị 2 1,200 1,300 1,700 Đơn vị 3 1,600 1,500 1,900 Đơn vị 4 1,500 1,500 1,600 Đơn vị 5 1,000 1,600 1,900 Đơn vị 6 1,500 1,600 1,500 Đơn vị 7 1,300 1,400 1,600 Đơn vị 8 1,500 1,550 1,700 Đơn vị 9 1,100 1,200 1,300 Max Min Mức ầu tư Loại Tiền A 300,000 B 250,000 C 150,000
0 - Định dạng bảng tính và trang in theo yêu cầu: Định dạng bảng giống như trên, khổ giấy A4, lề
trên 2.5 cm, lề dưới 2.5 cm, lề trái 2 cm, lề phải 2 cm
1 - Tính các cột và các dòng: Tổng, Trung bình, Max, Min, Loại, Đầu tư.
- Tính cột Loại như sau:
Loại = ‘A’ nếu Trung bình > 1600
Loại = ‘B’ nếu 1400 <= Trung bình <= 1600
Loại = ‘C’ nếu Trung bình < 1400
- Tính cột Đầu tư: Dùng hàm Vlookup tra cứu mức ầu tư ở dưới ể iền dữ liệu vào cột Đầu tư.
2 - Sắp xếp theo cột Thu nhập 95 giảm dần và Thu nhập 96 tăng dần.
3 - Tìm các ơn vị có Thu nhập 97 > 1600 và thu nhập Trung bình > 1300. 4 - Vẽ ồ thị dạng 3-
D Pie của các cột Thu nhập 97.
Bài tập số 3: Hãy nhập vào bảng số liệu sau 132 lOMoAR cPSD| 47708777
Bảng iểm thi học kỳ I năm 1999
Điểm Điểm Điểm Trung Học Stt Họ ệm Tên Tổng Loại Toán Tin Lý bình bổng 1 Nguyễn Văn Thìn 8 7 8 2 Đỗ Hồng Quân 4 6 5 3 Trần Thuý Hằng 8 9 8 4 Bùi Thị Hương 9 10 9 5 Lê Văn Minh 9 7 5 6 Nguyễn Đình Chiến 6 4 5 7 Ngô Thu Hà 8 7 9 8 Lê Mai Hương 5 5 4 9 Đỗ Thu Anh 9 9 9 Max Min Mức học bổng Loại Tiền A 300,000 B 250,000 C 150,000
0 - Định dạng bảng tính và trang in theo yêu cầu: Định dạng bảng giống như trên, khổ giấy A4, lề
trên 2.5 cm, lề dưới 2.5 cm, lề trái 2 cm, lề phải 2 cm.
1 - Tính các cột và các dòng: Tổng, Trung bình, Max, Min, Loại, Học bổng.
- Trung bình = (Điểm Toán * 2 + Điểm Tin * 2 + Điểm Lý)/5
- Tính cột Loại như sau:
Loại = ‘A’ nếu Trung bình >= 9
Loại = ‘B’ nếu 7 <= Trung bình < 9
Loại = ‘C’ nếu Trung bình < 7
- Tính cột Học bổng: Dùng hàm Vlookup tra cứu mức học bổng
ở dưới ể iền dữ liệu vào cột Học bổng.
2 - Sắp xếp theo cột Điểm Toán giảm dần, Điểm Tin giảm dần, Điểm Lý giảm dần 3 - Tìm các
học sinh có Điểm Toán >= 9 18 hoặc Điểm Tin >= 9.
4 - Vẽ ồ thị dạng 3-D Column của các cột Điểm Toán, Điểm Tin. lOMoAR cPSD| 47708777
Bài tập số 4: Hãy nhập vào bảng số liệu sau
BẢNG THỐNG KÊ HÀNG Cửa Doanh Thực Đánh Mặt hàng Vốn Dịch vụ Thuế hàng thu thu giá CH1 Gạo 45 55 CH1 Xăng dầu 65 85 CH1 Bia rượu 90 120 CH2 Gạo 75 90 CH2 Bia rượu 60 88 CH2 Xăng dầu 90 125 CH3 Gạo 43 62 CH3 Xăng dầu 77 95 CH3 Quần áo 80 90 Tổng Gạo
0 - Định dạng bảng tính và trang in theo yêu cầu: Định dạng bảng giống như trên, khổ giấy A4,
lề trên 2.5 cm, lề dưới 2.5 cm, lề trái 2 cm, lề phải 2 cm.
1 - Tính các cột, dòng: Dịch vụ, Thuế, Thực thu, Đánh giá, Tổng Gạo.
- Dịch vụ = 5% Vốn.
- Thuế = 10% Doanh thu.
- Thực thu = Doanh thu - Vốn - Thuế - Dịch vụ
- Cột Đánh giá: Nếu Thực thu > 0 ghi “Lãi”, bằng 0 ghi “Hoà vốn”, trái lại ghi “Lỗ”.
- Tính tổng Vốn và Doanh thu của mặt hàng Gạo. 2 - Sắp xếp theo cột Vốn giảm
dần. 3 - Tìm các cửa hàng có Doanh thu > 60 và Vốn > 70.
4 - Vẽ ồ thị dạng XY biểu diễn tương quan giữa hàm y là Doanh thu và biến x là Vốn.
Bài tập số 5: Hãy nhập vào bảng số liệu sau
Thống kê bán hàng trong tuần 1 tháng 10/2004 Số lượng
Đơn vị tính Đơn giá Tiền nhập Stt Tên hàng 1 Đèn iện 120 150 Bộ 22,000 2 Dây iện 227 Mét 75,000 3 Biến thế 240 Cái 255,000 4 Cầu giao 20a 1117 Cái 75,000 5 ổn áp 167 Cái 55,000 6 Cầu giao 50a 267 Cái 10,000 134 lOMoAR cPSD| 47708777 7 Công tắc Thái 222 Cái 2,300 8 Máy o iện 231 Cái 12,000 9 Cầu chì 101 Bộ 3,000 10 Băng keo 1912 Cuộn 25,000
* Yêu cầu thực hiện
1. Tiền nhập = Đơn giá * Số lượng.
2. Copy trang tính sang ba sheet khác trong Book hiện thời.
3. Thay ổi số liệu các cột số lượng, ơn giá của từng trang tính.
4. Đổi tên các Sheet thành Tuan 1, Tuan 2, Tuan 3, Tuan 4
5. Tính tổng số tiền thu ược trong 4 tuần, kết quả ghi vào trang tính Tuan 1
6. Chèn thêm vào trang tính Tuan 2 các cột Ngày nhập, Thuế VAT, Thành tiền. Tự iền dữ
liệu vào cột Ngày nhập, Thuế VAT = 5% Tiền nhập,
Thành tiền = Tiền nhập + Thuế VAT
7. Thay ổi kích thước chữ trong trang tính Tuan 2 sao cho trang tính không rộng quá lề phải của khổ giấy A4.
8. Sắp xếp dữ liệu trong Tuan 1 theo chiều tăng dần của Số lượng.
Bài tập số 6: Hãy nhập vào bảng số liệu sau
BẢNG KÊ CHI PHÍ THUÊ KHÁCH SẠN Loại Ngày Số Tiền Phí Tiền Tên khách Ngày Đi phòng ến
ngày ở phòng phục vụ nộp Nguyễn thị Nga A 5/10/98 5/23/98 lOMoAR cPSD| 47708777 Nguyễn Chương B 7/3/98 7/23/98 Đặng Phước Cường A 6/12/98 7/23/98 Nguyễn Đức Huy C 8/19/98 8/20/98 Nguyễn Chí Kiên B 9/23/98 9/30/98 Phạm Cao Đằng C 9/13/98 10/13/98 Phạm Hải Nam C 8/25/98 9/13/98 Hoàng thị Long A 1/5/98 1/13/98 Nguyễn Hà Mi B 7/24/98 7/27/98
*Yêu cầu thực hiện:
Định dạng cột Ngày i, Ngày ến ể hiển thị theo kiểu ngày tháng của Việt nam (Ngày/ Tháng/Năm)
1. Cột Số ngày ở = Ngày i –Ngày ến.
2. Cột Tiền phòng = Đơn giá ngày * Số ngày ở.
3. Phí phục vụ: căn cứ vào Loại phòng (dùng hàm Vlookup).
4. Tiền nộp = Tiền phòng + Phí phục vụ. Phí phục vụ Loại phòng (tính trên tiền Đơn giá ngày phòng) A 8% 150,000 B 5% 100,000 C 3% 80,000
5. Tính tổng tiền phòng của các loại phòng.
6. Sắp xếp dữ liệu theo chiều tăng dần của Loại phòng, và giảm dần theo Tiền phòng.
8. Lọc riêng dữ liệu của phòng loại C.
9. Vẽ ồ thị dạng Line cho các cột Tiền phòng, Tiền nộp.
Bài tập số 7: Lập trang tính ể thực hiện các tính toán sau 136 lOMoAR cPSD| 47708777
Bảng thanh toán tiền iện Loại Định Số Số
Tiền Tiền vượt Thuế Họ và tên Tiền nộp
hộ mức ầu
cuối ịnh mức
ịnh mức VAT Nguyễn Hồng Ngát C 200 278 Chu Thị Minh A 120 160 Vũ Văn Công B 235 565 Trần Hùng B 1,245 1,800 Ngô Triều Dương C 125 400 Phạm Văn Xuyến A 1,012 1,290 Cao Hùng Cường C 505 720 Lâm Viết Đồng A 700 850 Hà Huy Phong B 321 542
Yêu cầu: Trang tính thiết kế cho khổ giấy A4, in theo chiều dọc, chỉ làm trong Sheet 1.
Câu 1: Hãy bổ sung vào trang tính các cột cần thiết ể tính toán theo yêu cầu sau:
Dùng hàm VlookUp iền dữ liệu vào cột Định mức biết rằng
Hộ loại A: ịnh mức 100 số, Hộ loại B: ịnh mức 500 số, Hộ loại C: ịnh mức 200 số
Tiền ịnh mức = Số ịnh mức * 450
Tiền vượt ịnh mức = Số vượt ịnh mức * 1000
Thuế VAT = 5% Tiền vượt ịnh mức
Tiền nộp = Tiền ịnh mức + Tiền vượt ịnh mức + Thuế VAT
Tính tổng số Tiền ịnh mức, Tiền vượt ịnh mức của hộ loại B
Tính tiền nộp bình quân trong cả danh sách
Tìm xem hộ nộp nhiểu tiền nhất là bao nhiêu và ít tiền nhất là bao nhiêu?
Câu 2: Sắp xếp trang tính theo chiều tăng dần của loại hộ, tiền nộp.
Câu 3: Dùng công cụ Advanced Filter tìm các hộ loại A dùng vượt ịnh mức.
Câu 4: Vẽ ồ thị dạng Line với số liệu trong các cột Tiền ịnh mức, Tiền vượt ịnh mức, Tiền nộp.