Bài giảng môn Tin học văn phòng - Bài tập excel

1 BÀI TẬP EXCEL Bài 1 Một trường học dùng Excel để lập bảng tổng kết điểm cuối năm và xét lên lớp cho sinh viên trên cơ sở điểm các môn thi. Bảng điểm các môn thi của sinh viên hiện đang được lưu trong bảng tính (sheet BANG DIEM) như sau : Tiêu chuẩn phân loại học lực : 2 Yêu cầu 1) Tạo hai bảng nêu trên (sheet BANG DIEM) 2) Lập bảng tổng kết năm học theo mẫu nêu dưới đây(sheet TONG KET). Các chi tiết liên quan :  Điểm tổng kết = trung bìng cộng của các điểm thi  Vị thứ: Xếp vị thứ theo điểm tổng kết.  Loại học lực: phụ thuộc ĐTK (căn cứ vào bảng tiêu chuẩn) hoặc dùng hàm IF  Số môn không đạt : số môn có điểm thi dưới 5 điểm  Ghi chú : kết quả xét lên lớp (nợ môn, ở lại hoặc để trống trong trường hợp sinh viên được lên lớp). Sinh viên được lên lớp nếu số môn không đạt bằng 0, được nợ môn nếu có môn không đạt, nhưng điểm tổng kết vẫn đạt từ 5.0 trở lên. Trong trường hơp còn lại, sinh viên phải ở lại lớp. 3) Sắp xếp lại danh sách sinh viên theo thứ tự giảm dần của điểm tổng kết. Các sinh viên có cùng điểm tổng kết được sắp xếp theo thứ tự tăng dần của mã sinh viên. 4) Rút trích từ bảng trên danh sách các sinh viên lên lớp có điểm thi D1 đạt từ 7 điểm trở lên và điểm tổng kết đạt từ 5.5 trở lên. Lưu kết quả vào một bảng mới. 5) Thống kê số sinh viên lên lớp, ở lại, nợ môn. 6) Tạo biểu đồ cột, biểu đồ tỷ lệ cho các số liệu vừa thống kê. 7) Lưu bảng tính vào tập tin ‘Tong ket cuoi nam’. 3 Bài 2 Một xí nghiệp giao cho các công nhân gia công 6 loại sản phẩm (A, B, C, D, E, F) với đơn giá được nêu trong đơn giá gia công dưới đây. Mỗi công nhân chỉ được giao một loại sản phẩm nhất định, các sản phẩm giao nộp được xí nghiệp nghiệm thu và chia thành hai loại đạt và không đạt yêu cầu. Tỷ lệ sản phẩm không đạt cho phép là 2% (trên số sản phẩm thực hiện). Nếu vượt tỷ lệ này, công nhân sẽ bị phạt số tiền bằng 3 lần đơn giá gia công cho mỗi sản phẩm không đạt. Yêu cầu 1) Dùng Excel để lập bảng lương tháng 4/2005 cho các công nhân (mẫu dưới ) 4 Các công thức và chi tiết liên quan :  Thành tiền = Số SP đạt * Đơn giá gia công  Số SP không đạt = Số SP nhận – Số SP đạt  Tỷ lệ không đạt = Số SP không đạt/ Số SP nhận * 100%  Tiền phạt = 0 nếu tỷ lệ không đạt <= 2% Ngược lại : Tiền phạt = 3 * Đơn giá * Số SP không đạt  Được lãnh = Thành tiền – Tiền phạt 2) Sắp xếp lại danh sách công nhân theo thứ tự họ tên. 3) Dùng AutoFilter và Advanced Filter để rút trích các dữ liệu sau (ghi kết quả sang một vùng khác) :  Danh sách công nhân nhận gia công sản phẩm A.  Danh sách công nhân nhận có tỷ lệ sản phẩm không đạt từ 3% trở lên.  Danh sách công nhân gia công sản phẩm có số tiền được lãnh từ 2 tới 3 triệu đồng.  Danh sách công nhân nhận gia công sản phẩm C hoặc D có số tiền được lãnh từ 2 tới 3 triệu đồng. 4) Thống kê số sản phẩm gia công, số sản phẩm đạt yêu cầu, số sản phẩm không đạt mỗi loại 5) Vẽ biểu đồ cột biểu diễn các kết quả thống kê vừa nêu. 6) Lưu bảng tính vào tập tin BANG LUONG. 5 Bài 3 Một khách sạn dùng Excel để tính tiền thuê phòng cho khách. Khách sạn có 4 loại phòng với mã loại và đơn giá được nêu trong bảng dưới đây. (sheet DON GIA). Các khách thuê phòng từ 7 ngày trở lên được giảm 10% giá phòng. Danh sách khách thuê và trả phòng trong tháng 7/2005 : 6 Yêu cầu 1. Lập bảng tiền thanh toán tiền khách sạn tháng 7/2005 (sheet THANG 7)theo mẫu sau : Các công thức liên quan  Mã loại : tìm trong bảng danh sách phòng cho thuê  Đơn giá : tìm trong bảng giá  Số Ngày = Ngày đi (tới ngày) – ngày đến (từ ngày)  Giá phải trả : bằng 90% đơn giá nếu số ngày >= 7. Trong trường hợp ngược lại, giá phải trả bằng 100% đơn giá.  Số tiền = Số ngày * Giá phải trả 2. Sắp xếp lại danh sách khách thuê phòng trong bảng thanh toán theo thứ tự tăng của ngày trả phòng. Các khách thuê trả phòng cùng ngày được sắp xếp theo họ tên. 3. Cho biết danh sách khách có mặt trong khách sạn ngày 15/7/2005. Lưu kết quả vào một bảng mới. 4. Thống kê doanh số và hiệu suất khai thác mỗi loại phòng. Tạo biểu đồ cột và biểu đồ tỷ lệ biểu diễn các kết quả thống kê. 5. Lưu bảng tính vào tập tin KHACH SAN. 7 Bài 4 Một điểm dịch vụ ngoại hối muốn dùng Excel để quản lý các giao dịch mua bán ngoại tệ trong ngày. Điểm dịch vụ mua vào và bán ra một số loại ngoại tệ theo tỷ giá ấn định đầu ngày như sau : Yêu cầu 1) Lập bảng tính số tiền VNĐ phải trả hoặc phải thu của khách khi thực hiện các giao dịch mua/ bán ngọai tệ trong ngày (theo mẫu dưới đây). Ký hiệu loại giao dịch M – mua vào, B – bán ra. Nhập thêm dữ liệu cho bảng tính. 2) Rút trích danh sách khách hàng đã mua hoặc bán USD trong ngày với số lượng từ 2000 USD trở lên. 8 3) Lập bảng tổng kết cuối ngày theo mẫu sau : 4) Lập biểu đồ cột biểu diễn doanh số mua vào và bán ra của các loại ngoại tệ trong 5) Lưu bảng tính vào tập tin GIAO DICH NGOAI HOI. Bài 5 Một đại lý nước giải khát dùng Excel để quản lý hàng xuất cho các cửa hàng bán lẻ. Danh sách cửa hàng và danh mục loại hàng được ghi trong sheet BANG GIA. Sheet Thang 4 chứa chi tiết về hàng xuất (ngày, mã cửa hàng, mã loại hàng, số lượng) của đại lý trong tháng 4/2007. Mã loại hàng trong sheet Thang 4 là 3 ký tự đầu của mã loại hàng trong sheet BANG GIA, cịn ký tự cuối (L, C) cho biết đây là loại nước giải khát đĩng trong lon hay chai. Số lượng hàng xuất được tính theo số két (chai) hoặc thùng (lon). Yêu cầu : 9 1. Bổ sung thêm các cột Tên LH, Đơn vị, Đơn giá, Thành tiền vào bảng Danh sách hàng xuất tháng 4/2007 (theo mẫu dưới). Đơn giá loại hàng được tìm trong bảng giá; thành tiền được tính theo cơng thức : Thành tiền = Số lượng * Đơn giá 2. Thống kê tổng số tiền hàng đã xuất cho từng cửa hàng, số tiền lớn nhất, nhỏ nhất, bình quân, tổng số tiền hàng đã xuất cho tồn bộ các cửa hàng. 3. Rút trích các dịng hàng xuất cĩ thành tiền từ 1,500,000 tới 3,000,000 đ. 10 Bài 6 Cho bảng dữ liệu tính thuế hàng nhập : Thực hiện các cơng việc sau : 1) Lập bảng tính thuế nhập khẩu hồn chỉnh( theo mẫu kèm theo). Các chi tiết cĩ liên quan :  Ký hiệu hàng nhập : 3 ký tự đầu mơ tả mã loại hàng, 2 ký tự cuối mơ tả nhĩm hàng.  Đơn giá qui định = Đơn giá nêu trong bảng đơn giá  Đơn giá thực tế = 70% đơn giá quy định đối với hàng cũ hoặc hàng cĩ xuất xứ từ các nước ASEAN.  Thuế suất : phụ thuộc mã hàng (tìm trong bảng thuế suất)  Thuế = Số lượng * Đơn giá thực tế * Thuế suất * Tỷ giá  Thuế tiêu thụ đặc biệt = Thuế * 150% đối với hàng nhập thuộc nhĩm 07 và 08 = 0 đối với các nhĩm khác.  Tổng cộng = Thuế + Thuế tiêu thụ đặc biệt 2) Rút trích các dịng cĩ thuế tiêu thụ đặc biệt khác 0 và ghi vào một bảng riêng 3) Cho biết tổng thuế của từng loại hàng. 11 Bài 7 Một chi nhánh điện lực dùng Excel để quản lý số điện tiêu thụ và số tiền phải trả mỗi tháng của khách hàng. Bạn hãy giúp chi nhánh thực hiện cơng việc này. Cấu trúc Số điện kế :  Ký tự đầu : mã khu vực (A,B,C,D,E)  Ký tự cuối : loại khách hàng (1, 2) 12 Yêu cầu 1. Nhập liệu và thực hiện bảng tính: Điền STT, tính đơn giá, số điện tiêu thụ, số tiền phải trả của từng khách hàng, tính tổng số tiền đã thu của các khu vực. 2. Dùng chức năng Advanced Filter để rút trích danh sách khách hàng loại 1 cĩ số điện tiêu thụ lớn hơn 200, hoặc khách hàng loại 2 cĩ số điện tiêu thụ lớn hơn 250. Các cơng thức tính tốn cĩ liên quan :  Số điện trong định mức = Số điện tiêu thụ nếu Số điện tiêu thụ < định mức  Số điện trong định mức = Định mức nếu Số điện tiêu thụ > = định mức  Số điện vượt định mức = 0 nếu Số điện tiêu thụ <= định mức  Số điện vượt định mức = Số điện tiêu thụ - Định mức nếu Số điện tiêu thụ > định mức  Số điện tiêu thụ = Số mới – Số cũ  Số tiền phải trả = Tiền điện trong định mức + Tiền điện vượt định mức  Tiền điện trong định mức = Số điện trong định mức * Giá định mức  Giá định mức : phụ thuộc vào khu vực và loại khách hàng (sheet “Bang gia”)  Tiền điện vượt định mức = Số điện vượt định mức * Giá ngồi định mức  Giá ngồi định mức = Giá định mức * 1.5 nếu Số điện vượt định mức <= Số điện trong định mức  Giá ngồi định mức = Giá định mức * 2 nếu Số điện vượt định mức > Số điện trong định mức Bài 8 Cho bảng doanh số bán 3 loại mặt hàng A, B, C trong 5 năm )2001 – 2005) như sau : Mặt hàng Doanh số (triệu đồng) 2001 2002 2003 2004 2005 A 100 120 200 180 185 B 200 240 225 250 280 C 150 190 240 250 270 - Vẽ biểu đồ cột doanh số bán của các mặt hành trong các năm trên. - Vẽ biểu đồ tỷ lệ doanh số bán của các mặt hành trong năm 2005.