Giáo trình Chuyên đề Xử lý bảng tính Excel nâng cao

sau: Nếu thí sinh có điểm Tổng cộng >= 
 Điểm chuẩn của ngành mình dự thi thì sẽ có kết quả là "Đậu", ngược lại là 
 "Rớt". 
 7. Cột Học bổng lập công thức điền vào đó là "Có" nếu điểm Tổng cộng của thí 
 sinh >= Điểm học bổng, trường hợp ngược lại để trống. 
 8. Sắp xếp lại danh sách Kết quả tuyển sinh theo thứ tự tăng dần của cột điểm 
 Tổng Cộng. 
 9. Rút trích: thông tin của các thí sinh thuộc ngành thi Máy Tính . 
 0812 114 345 |  tinhocsaoviet.com Trang 105 
Trung Tâm Tin Học Sao Việt 90/47 Đồng Khởi, Tân Phong, Biên Hòa 
 BÀI TẬP EXCEL 6 
Nhà sách XYZ có hệ thống 2 chi nhánh. 
Yêu cầu: 
 1. Tạo một Sheet Tổng kết cuối năm để tínhTổng số lượng sách đã bán. ( Dùng 
 chức năng Consolidate ). 
 2. Sử dụng chức năng SparkLine vẽ đồ thị cho bảng thống kê của 2 chi nhánh. 
 3. Vẽ biểu đồ cột cho bảng tổng kết của cửa hàng. 
 4. Thực hiện chức năng Conditional Formatting, để đánh dấu số lượng cao nhất 
 của từng loại sách. 
 0812 114 345 |  tinhocsaoviet.com Trang 106 
Trung Tâm Tin Học Sao Việt 90/47 Đồng Khởi, Tân Phong, Biên Hòa 
 BÀI TẬP EXCEL 7 
 Doanh Thu Bán Hàng 
 Tỉ giá 21000 
 MÃ LOẠI TÊN XUẤT SỐ ĐƠN THÀNH TIỀN THÀNH TIỀN 
 STT 
 HÀNG HÀNG HÀNG XỨ LƯỢNG GIÁ (VNĐ) (USD) 
 1 AS200HK 
 2 AS300TL 
 3 BN450TL 
 4 AN200TQ 
 5 AQ300TQ 
 6 AS500TQ 
 7 BQ220TL 
 8 AI175TL 
 9 BS450TQ 
 10 AI400HK 
 Tổng Tiền 
 Bảng tra tên hàng & đơn giá Bảng tra xuất xứ 
 Mã hàng Tên hàng Đơn giá Mã 
 S Sony Ericson 500 USD hàng HK TQ TL 
 N Nokia 700 USD Xuất xứ Hồng Kông Trung Quốc Thái Lan 
 Q Q-Mobile 400 USD 
 I Iphone 900 USD 
 Lập công thức cho các cột: (mỗi câu 1 điểm) 
 1) Cột loại hàng dựa vào ký tự đầu tiên của mã hàng 
 2) Cột tên hàng dựa vào ký tự thứ 2 của mã hàng và bảng tra tên hàng & đơn giá. 
 3) Cột xuất xứ dựa vào 2 ký tự cuối của mã hàng và bảng tra xuất xứ . 
 4) Cột số lượng dựa vào các ký tự thứ 3,4,5 của mã hàng . 
 5) Cột đơn giá dựa vào mã hàng và bảng tra tên hàng & đơn giá, định dạng tiền 
 tệ giống bảng tra . 
 6) Cột thành tiền VNĐ= số lượng * đơn giá, định dạng tiền tệ giống bảng tra 
 7) Cột thành tiền USD = thành tiền VNĐ / tỉ giá, TÊN HÀNG TỔNG TIỀN(USD) 
 chỉ lấy giá trị nguyên. Sony Ericson 
 Nokia 
 8) Thực hiện bảng thống kê. 
 Q-Mobile 
 9) Vẽ biểu đồ cho bảng thống kê ở câu 8. Iphone 
 0812 114 345 |  tinhocsaoviet.com Trang 107 
Trung Tâm Tin Học Sao Việt 90/47 Đồng Khởi, Tân Phong, Biên Hòa 
 BÀI TẬP EXCEL 8 
 BẢNG DOANH SỐ BÁN HÀNG 
 Số Đơn Số Đơn Tiền Tiền 
 Mã Tên Ngày Ngày 
 STT lượng giá lượng giá chênh hoa 
 hàng hàng mua bán 
 mua mua bán bán lệch hồng 
 1 S100X 01/01/2011 01/01/2011 100 
 2 S150Y 02/01/2011 05/02/2011 120 
 3 M300X 01/01/2011 03/01/2011 250 
 4 N310X 04/01/2011 02/03/2011 300 
 5 D120Y 05/01/2011 05/01/2011 125 
 6 N250X 01/01/2011 01/01/2011 200 
 7 M120Y 01/02/2011 07/02/2011 100 
 8 D100X 01/01/2011 01/01/2011 95 
 9 S200X 09/01/2011 09/01/2011 170 
 10 S120X 01/02/2011 10/02/2011 120 
 Bảng tra 1 Bảng tra 2 
 Mã hàng Tên hàng Đơn giá mua Mã hàng S M D N 
 S Sữa 300,000 Giá 1 350,000 200,000 300,000 100,000 
 M Muối 150,000 Giá 2 320,000 190,000 270,000 90,000 
 D Đường 250,000 
 N Nước khoáng 75,000 
Học viên thực hiện các yêu cầu sau: 
1) Định dạng bảng tính như trên, tạo header là họ và tên của học viên. 
2) Tên hàng dựa vào ký tự đầu tiên của mã hàng và bảng tra 1 . 
3) Số lượng mua dựa vào 3 ký tự giữa của mã hàng . 
4) Đơn giá mua dựa vào tên hàng và bảng tra 1 . 
5) Đơn giá bán dựa vào ký tự đầu của mã hàng và bảng tra 2. Chú ý rằng nếu ký tự cuối 
 của mã hàng là X thì chọn đơn giá 1, nếu ký tự cuối của mã hàng là Y thì chọn đơn 
 giá 2. 
6) Tiền chênh lệch = Tiền bán – Tiền mua . 
7) Cột tiền hoa hồng được tính như sau: Nếu như số ngày tồn kho <3 và các mặt hàng 
 phải được bán hết, thì tiền hoa hồng bằng 10% tiền chênh lệch . 
8) Dùng Advanced Filter trích ra những mặt hàng có số lượng bán >=200 . 
 Tên hàng Sữa Muối Đường Nước khoáng 
 Tổng tiền chênh lệch 
9) Thực hiện bảng thống kê bên dưới. 
 0812 114 345 |  tinhocsaoviet.com Trang 108 
Trung Tâm Tin Học Sao Việt 90/47 Đồng Khởi, Tân Phong, Biên Hòa 
10) Vẽ đồ thị cho bảng thống kê ở câu 9 . 
 BÀI TẬP EXCEL 9 
 BẢNG DANH MỤC SẢN PHẨM 
 Học viên định dạng bảng tính và thực hiện các yêu cầu bên Bảng tỉ giá 
 dưới: Tỉ giá 1 21 
 Tỉ giá 2 22 
 Số Đơn Thành Thành 
 Mã Tên Ngày Đơn Ghi 
 STT lượn vị tiền tiền 
 hàng hàng nhập giá chú 
 g tính USD VNĐ 
 1 CF01X 01/01/2011 
 2 CF02X 01/02/2011 
 3 DG01Y 01/02/2011 
 4 DG01X 01/04/2011 
 5 NC01X 01/05/2011 
 6 SU02X 01/06/2011 
 7 CF03Y 01/09/2011 
 8 SU01Y 01/10/2011 
 9 DG03X 01/10/2011 
 Bảng tra dữ liệu 1 Bảng tra dữ liệu 2 
 Mã 
 Mã hàng CF DG NC SU 
 hàng Đơn giá 1 Đơn giá 2 
 Tên hàng Cà phê Đường Ngũ Cốc Sữa CF 100 USD 95 USD 
 Số lượng 15 75 40 150 DG 2 USD 2 USD 
 Đơn vị tính Bao ký Thùng Lốc NC 60 USD 55 USD 
 SU 3 USD 4 USD 
 1) Cột tên hàng dựa vào 2 ký tự đầu của mã hàng và bảng tra dữ liệu 1 . 
 2) Cột số lượng dựa vào tên hàng và bảng tra dữ liệu 1 . 
 3) Cột đơn vị tính dựa vào tên hàng và bảng tra dữ liệu 1 . 
 4) Cột đơn giá dựa vào mã hàng và bảng tra dữ liệu 2. Nếu ký tự ngoài cùng của 
 mã hàng là X thì lấy đơn giá 1, là Y thì lấy đơn giá 2 . 
 5) Cột thành tiền USD = đơn giá * số lượng . 
 6) Cột thành tiền VNĐ = cột thành tiền USD * tỉ giá, biết rằng nếu hàng nhập từ 
 tháng 1 tới tháng 7 thì lấy tỉ giá 1, còn lại lấy tỉ giá 2 (1đ). 
 7) Cột ghi chú: Nếu ký tự ngoài cùng của mã hàng là X thì ghi Hàng Ngoại, là Y thì 
 ghi Hàng Nội. 
 8) Thực hiện bảng thống kê bên dưới . 
 Tên hàng Cà phê Đường Ngũ Cốc Sữa 
 Tổng tiền(VNĐ) 
 9) Vẽ biểu đồ cho bảng thống kê ở câu 8 . 
 10) Tạo Header có tiêu đề Bài Thi Excel, Footer có tiều đề Họ Tên của học viên . 
 0812 114 345 |  tinhocsaoviet.com Trang 109 
 Trung Tâm Tin Học Sao Việt 90/47 Đồng Khởi, Tân Phong, Biên Hòa 
 BÀI TẬP EXCEL 10 
 CƯỚC PHÍ BƯU ĐIỆN 
 Trọng 
 Mã bưu Nơi Phương Hình Giá Thành 
 STT lượng 
 kiện đến tiện thức cước tiền 
 (gram) 
 1 MBUSN 500 
 2 TLUSE 200 
 3 TTAUE 50 
 4 TLSIE 250 
 5 MBUSN 150 
 6 TTSIN 800 
 7 TLAUN 250 
 8 MBAUE 600 
 GIÁ VẬN CHUYỂN LOẠI PHƯƠNG TIỆN 
 Mã phương 
Mã Tên nước Giá 1 Giá 2 Giá 3 MB TT 
 tiện TL 
 Tên phương Máy Tàu Tàu 
US USA 19000 18000 17000 
 tiện bay thủy lửa 
FR France 17000 16000 14500 
AU Australia 14000 12000 11200 
SI Singapore 12000 10500 10000 
 Câu 1. Tạo Header là “họ và tên sinh viên” ở vị trí trung tâm (center). 
 Câu 2. Nơi đến: Dựa vào hai ký tự thứ 3 và thứ 4 của Mã bưu kiện và dò tìm trong bảng GIÁ 
 VẬN CHUYỂN. 
 Câu 3. Phương tiện: Dựa vào 2 ký tự đầu trong Mã bưu kiện và dò tìm trong bảng LOẠI 
 PHƯƠNG TIỆN. 
 Câu 4. Sắp xếp bảng tính theo Trọng lượng tăng dần. 
 Câu 5. Hình thức: Nếu ký tự cuối của Mã bưu kiện là N thì ghi là “Bình thường”, ngược lại 
 ghi “Nhanh”. 
 Câu 6. Giá cước: Dựa vào hai ký tụ 3 và 4 của Mã bưu kiện và dò tìm trong bảng GIÁ 
 VẬN CHUYỂN. 
  Nếu hai ký tự đầu của Mã bưu kiện là “MB” thì lấy giá 1 
 0812 114 345 |  tinhocsaoviet.com Trang 110 
Trung Tâm Tin Học Sao Việt 90/47 Đồng Khởi, Tân Phong, Biên Hòa 
  Nếu hai ký tự đầu của Mã bưu kiện là “TT” thì lấy giá 2 
  Nếu hai ký tự đầu của Mã bưu kiện là “TL” thì lấy giá 3 
 Câu 7. Thành tiền: = Trọng lượng * Giá cước, nếu hình thức gởi là “Nhanh” thì tăng 
 10% thành tiền. Định dạng đơn vị tiền tệ là “VNĐ”. 
 Câu 8. Thực hiện bảng thống kê 
 DOANH THU CÁC HÌNH THỨC GỬI 
 theo mẫu: 
 Phương tiện Bình thường Nhanh 
 Máy bay ? ? 
 Tàu thủy 
 Tàu lửa 
 Câu 9. Trích lọc danh sách các bưu kiện đi USA và có trọng lượng > 300 (gram). 
 Câu 10. Đếm tổng số bưu kiện được giao hàng bằng “Máy bay” và có trọng lượng 
 >300 (gram). 
 0812 114 345 |  tinhocsaoviet.com Trang 111 
 Trung Tâm Tin Học Sao Việt 90/47 Đồng Khởi, Tân Phong, Biên Hòa 
 BÀI TẬP EXCEL 11 
 BẢNG 1 
 MÃ SỐ SỐ TÊN SẢN ĐƠN GHI THƯỞN MỨC 
STT HỌ TÊN 
 ĐƠN VỊ LƯƠNG PHẨM GIÁ CHÚ G LƯƠNG 
 1 TRẦN MINH APX1 24 
 2 LÊ NAM BPX2 22 
 3 NGUYỄN HOA APX1 31 
 4 LÝ LAN CPX1 37 
 5 LÊ AN CPX2 43 
 6 NGUYỄN VŨ BPX1 20 
 7 PHAN MAI APX2 24 
 8 TRẦN HÙNG CPX2 19 
 BẢNG 2 BẢNG 3 
 A B C Số lượng Thưởng 
 Sản phẩm Nón Túi Xách Dây nịt 15 150,000 
 Đơn giá 15,000 20,000 5,000 20 200,000 
 30 300,000 
 Yêu cầu 
 1. Nhập liệu và trình bày các bảng được cho như trên 
 2. Điền cột TÊN SẢN PHẨM: Dựa vào 1 kí tự thứ nhất của MÃ SỐ ĐƠN VỊ và 
 BẢNG 2 
 3. Điền cột GHI CHÚ: nếu 1 kí tự cuối của MÃ ĐƠN VỊ là 1 thì ghi “Phân xưởng 
 1”, ngược lại ghi “ Phân xưởng 2” 
 4. Dựa vào BẢNG 3 tính cột THƯỞNG 
 5. Tính tóan cho cột MỨC LƯƠNG= SỐ LƯƠNG * ĐƠN GIÁ + THƯỞNG 
 6. Chèn thêm cột PHỤ CẤP, biết nếu MỨC LƯƠNG thấp hơn 600,000 thì sẽ phụ 
 cấp thêm cho đủ 600,000 . 
 7. Chèn cột THỰC LÃNH = MỨC LƯƠNG + PHỤ CẤP 
 8. Thực hiện bảng thống kê sau: 
 Sản phẩm Nón Túi xách Dây nịt 
 Tổng số luợng 
 9. Trích danh sách những nhân viên thuộc đơn vị A 
 10. Tạo Header (canh trái) có nội dung là Họ Tên Học Viên; Footer (canh phải) là 
 số trang. 
 0812 114 345 |  tinhocsaoviet.com Trang 112 
Trung Tâm Tin Học Sao Việt 90/47 Đồng Khởi, Tân Phong, Biên Hòa 
 BÀI TẬP EXCEL 12 
 Số 
 Doanh 
 Mã hàng Phân loại Xuất xứ lượng Giá nhập Giá bán Thuế 
 thu 
 (m3) 
 okal1m 20 
 okal2m 23 
 vnia1i 34 
 okal1i 21 
 vnia2i 41 
 vnia1m 22 
 okal2i 15 
 vnia2m 35 
 Bảng tra 
 okal1 okal2 vnia1 vnia2 
 m 100 80 200 180 
 i 130 90 195 180 
 Tổng lợi nhuận từng mặt hàng 
 okal1 okal2 vnia1 vnia2 
 m 
 i 
 1. Dựa vào ký tự thứ 5 của mã hàng điền vào cột Phân loại 
 2. Dựa ký tự cuối của mã hàng điền cột xuất xứ: nếu là "m" ghi là "malaysia", còn 
 lại ghi "indonesia" 
 3. Điền giá nhập dựa vào bảng tra 
 4. Điền cột giá bán theo quy định sau: 
 - Đối với mặt hàng của "malaysia" : nếu hàng loại 1 thi giá bán > giá nhập 
 12%, 
 hàng loại 2 thi giá bán hơn giá nhập 8% 
 - Đối với mặt hàng của indonesia: giá bán hơn giá nhập 15% 
 5. Cột doanh thu được tính như sau: (giá bán - giá nhập). số lượng 
 6. Cột thuế được xác định: nếu hàng loại 1: 5%doanh thu, hàng loại 2: 3% doanh 
 thu 
 7. Cột lợi nhuận = doanh thu - thuế 
 8. Thống kê theo bảng "Tổng lợi nhuận các mặt hàng" 
 9. Trích các mặt hàng malaysia loại 1 có lợi nhuận >324 
 0812 114 345 |  tinhocsaoviet.com Trang 113 
Trung Tâm Tin Học Sao Việt 90/47 Đồng Khởi, Tân Phong, Biên Hòa 
 BÀI TẬP EXCEL 13 
 BẢNG TÍNH TIỀN THUẾ 
 Tên Đối Đơn Số Thành Tiền 
 STT Số CT 
 Vật Tư Tượng Giá Lượng Tiền Thuế 
 AC20 
 BM12 
 CC50 
 DM60 
 DK30 
 AL40 
 BC70 
 AM80 
 CM90 
 BC15 
 BẢNG 1 BẢNG 2 
 MÃ M C K L 
 Tên vật Màn Đĩa CD Bàn Linh 
 tư Hình Phím Tinh 
 MÃ TÊN ĐỐI TƯỢNG TL TRỢ GIÁ 
 A Quốc Doanh 10% 
 B Công ty Cổ Phần 7% 
 C Công ty TNHH 5% 
 D Tư Nhân 3% 
 BẢNG THỐNG KÊ 
 Tên Vật Số 
 BẢNG ĐƠN GIÁ 
 Tư Lượng 
 Mã M C K L 
 Màn Hình 
 A 200 400 20 300 
 Đĩa CD 
 B 105 410 21 290 
 Bàn Phím 
 C 102 415 20 310 
 Linh Tinh 
 D 210 420 23 320 
 1. Nhập dữ liệu, điền số thứ tự theo 001,002,003 
 2. Tính tên vật tư dựa vào bảng 2, tính tên đối tượng dựa vào bảng 1. 
 3. Tính đơn giá dựa vào bảng đơn giá. 
 4. Tính số lượng dựa vào các cột trong CT , và đổi thành số. 
 5. Thành tiền = SL* ĐG. 
 6. Tính thuế = Thành tiền* TL Trợ Giá( Số liệu TL Trợ Giá của từng Công Ty lấy 
 từ Bảng 1) . 
 0812 114 345 |  tinhocsaoviet.com Trang 114 
Trung Tâm Tin Học Sao Việt 90/47 Đồng Khởi, Tân Phong, Biên Hòa 
 7. Sắp xếp bảng theo thứ tự giảm dần của số lượng . 
 8. Trích ra danh sách các đối tượng là Công Ty Tư Nhân. 
 9. Tính Bảng Thống Kê, vẽ biểu đồ cho bảng thống kê. 
 0812 114 345 |  tinhocsaoviet.com Trang 115 
Trung Tâm Tin Học Sao Việt 90/47 Đồng Khởi, Tân Phong, Biên Hòa 
 BÀI TẬP EXCEL 14 
 Số báo Khối Trườn Điểm Điểm Điểm Điểm Tổng Kết 
 Mã điểm 
 danh thi g thi Toán Hóa Lý Sinh điểm quả 
 DNTU- 2.00-
 359-A 4.00-2.50 
 KHAD- 4.25-
 277-A 5.25-5.50 
 DNLS- 5.00-
 318-A 6.50-6.50 
 QSBK- 4.25-
 769-A 1.50-0.75 
 QSTH- 3.50-
 479-B 8.00-3.00 
 DNTU- 5.00-
 356-B 2.00-5.50 
 KHAD- 3.50-
 427-A 3.75-4.25 
 DNLS- 9.25-
 205-B 2.25-6.00 
 QSBK- 2.50-
 949-A 6.75-2.25 
 QSTH- 6.75-
 872-A 3.50-4.75 
 DNTU- 7.50-
 363-A 5.00-6.00 
 QSBK- 8.00-
 568-B 4.50-9.00 
 Bảng Mã Tên Trường Điểm Chuẩn Bảng thống kê 
 Mã Khối Khối A B 
 Trường Tên Trường A B Đậu Rớt Đậu Rớt 
 DNTU ĐH Công Nghệ ĐN 16 16.5 ĐH Công Nghệ ĐN 
 KHAD ĐH Kinh Tế Q Dân HN 19 ĐH Nông Lâm 
 DNLS ĐH Nông Lâm HCM 15 15 HCM 
 QSBK ĐH Bách Khoa HCM 18.5 17 ĐH Bách Khoa 
 QSTH ĐH Khoa Học HCM 15.5 17.5 HCM 
Yêu cầu: 
 Câu 1. Điền Khối thi dựa vào ký tự cuối cùng của số báo danh. 
 Câu 2. Điền vào tên Trường thi dựa vào 4 ký tự đầu của Số báo danh và tra vào 
 bảng Bảng mã Tên trường. 
 0812 114 345 |  tinhocsaoviet.com Trang 116 
Trung Tâm Tin Học Sao Việt 90/47 Đồng Khởi, Tân Phong, Biên Hòa 
 Câu 3. Điền vào Điểm Toán dựa vào 4 ký tự đầu của mã điểm chuyển sang số. 
 Câu 4. Điền vào Điểm Hóa dựa vào 4 ký tự giữa của mã điểm chuyển sang số. 
 Câu 5. Điền vào Điểm Lý dựa vào 4 ký tự sau cùng của mã điểm chuyển sang số. 
 Nếu khối thi là A khác bỏ trống. 
 Câu 6. Điền vào Điểm Sinh dựa vào 4 ký tự sau cùng của mã điểm chuyển sang 
 số. Nếu khối thi là B khác bỏ trống. 
 Câu 7. Tổng điểm là tổng số điểm 3 môn thi của thí sinh. 
 Câu 8. Kết quả ghi là Đậu nếu tổng điểm lớn hơn hay bằng điểm chuẩn theo 
 khối. Ngược lại là Rớt. 
 Câu 9. Rút trích danh sách các thí sinh thi đậu. 
 0812 114 345 |  tinhocsaoviet.com Trang 117 
Trung Tâm Tin Học Sao Việt 90/47 Đồng Khởi, Tân Phong, Biên Hòa 
 BÀI TẬP EXCEL 15 
 Yêu cầu: 
 Câu 1. Dựa ký tự đầu của mã thí sinh dò theo bảng điền tên trường. 
 Câu 2. Dựa ký tự sau của mã thí sinh điền môn thí sinh thi 
 Câu 3. Dựa vào điểm thi xếp loại kết quả của thí sinh. 
 Câu 4. Dựa vào tên trường và môn thi đếm danh sách thí sinh thi từng môn ( Dùng 
 công thức mảng ). 
 Câu 5. Dựa vào tên trường và kết quả môn thi đếm danh sách loại thí sinh theo 
 kết quả từng trường ( Dùng công thức mảng ). 
 0812 114 345 |  tinhocsaoviet.com Trang 118 
Trung Tâm Tin Học Sao Việt 90/47 Đồng Khởi, Tân Phong, Biên Hòa 
 Câu 6. Rút trích danh sách thí sinh đạt loại giỏi hay trung bình. 
 Câu 7. Vẽ đồ thị thống kê thí sinh dự thi. 
 Câu 8. Vẽ đồ thị thống kê kết quả thí sinh dự thi. 
 Câu 9. Sắp xếp bảng tính theo trường và dùng Subtotal đếm số thí sinh dự thi mỗi 
 trường. 
 0812 114 345 |  tinhocsaoviet.com Trang 119 
Trung Tâm Tin Học Sao Việt 90/47 Đồng Khởi, Tân Phong, Biên Hòa 
 BÀI TẬP EXCEL 16 
 Tạo một Form Control theo mẫu: 
 Và tạo một Form để hiển thị và cho kết quả được vay bao nhiêu tiền như sau: 
 - Các thông tin được truyền trực tiếp khi ta nhập liệu từ Form nhập 
 - Số tiền được vay : 
 o Nếu là Nam và Tài chính dưới 10 triệu thì được vay 5 triệu 
 o Nếu là nữ và Tài chính dưới 10 triệu thì được vay 4 triệu. 
 Trường hợp còn lại được vay 10 triệu 
 0812 114 345 |  tinhocsaoviet.com Trang 120