Bài tập thực hành Microsoft Excel

Bài tập thực hành MicroSoft Excel Bài tập 1 Câu 1 Khởi động Excel, từ một Workbook mới hãy tạo bảng dữ liệu sau và thực hiện tuần tự các thao tác theo yêu cầu của bài thực hành. Yêu Cầu: 1. Nhập dữ liệu. 2. Chèn một dòng trước người tên THANH nhập thêm dữ liệu như sau: SƠN Nam 14/07/1974 2.56 3. Chèn thêm một cột trước cột CÒN LẠI và nhận tên cột là TẠM ỨNG. Thực hiện tính toán các cột như sau: 4. LƯƠNG (1) = LCB*450000. 5. TẠM ỨNG (2) = 25%*LƯƠNG. 6. CÒN LẠI = LƯƠNG - TẠM ỨNG. 7. Tính tổng lương (3), tổng tạm ứng và tổng còn lại (4) 8. Trang trí và lưu bảng tính với tên Bai Tap 01.XLS (phần đuôi .XLS không cần gõ vào). Đổi tên Sheet1 thành Bai Tap 1. 9. Sau khi lưu, đóng và mở lại bảng tính để kiểm tra, thực hiện việc định dạng văn bản như : đổi Font, tạo khung viền, thay đổi độ rộng các cột cho khít với số liệu. Câu 2 Yêu Cầu: 1. (1) STT dụng kỹ thuật tự động. 2. (3) TÊN TS: sinh viên tự đặt tên 3. (6) KHCB lọai A được tính theo tỉ lệ 7.00% trên nguyên giá nếu loại Mã TS(ký tự đầu) là A, ngược lại là 0 4. (7) KHCB loại B được tính theo tỉ lệ 6.50% trên nguyên giá nếu loại Mã TS là B, ngược lại là 0 5. (8) KHCB lọai C được tính theo tỉ lệ 6.00% trên nguyên giá nếu loại Mã TS là C, ngược lại là 0 6. (9) KHSLC = nguyên giá * tỷ lệ chung là 2.50% 7. (10) Tổng khấu hao = (6) + (7) + (8) + (9) 8. (11) = (5) – (10) 9. Trang trí bảng tính cho đẹp mắt. 1 Bài tập 2 Yêu Cầu: 1. Nhập dữ liệu, STT dụng kỹ thuật tự động. 2. Thêm vào bên phải cột ĐKQuả 2 cột Xếp loại và Xếp hạng 3. Điểm Cộng(ĐCộng) được tính như sau: - Khu vực 3: điểm cộng là 1.5 - Khu vực 2: điểm cộng là 1 - Khu vực 1: điểm cộng là 0 4. Điểm thấp nhất(ĐTN): là điểm thấp nhất trong 3 điểm của từng học viên 5. Điểm trung bình(ĐTB): là điểm trung bình của 3 điểm và làm tròn 1 số lẻ ở phần thập phân. 6. Điểm kết quả(ĐKQuả): những thí sinh nào có ĐTB>=5 thì ĐKQuả chính là ĐTB, ngược lại ĐKQuả chính là ĐTB + ĐCộng, nhưng nếu sau khi cộng với ĐCộng mà ĐKQuả lớn hơn 5 thì chỉ được lấy 5 mà thôi. 7. Xếp loại dựa vào điều kiện sau: - Giỏi: nếu ĐKQuả >=8 và không có môn nào dưới 7 - Khá: nếu ĐKQuả >=7 và không có môn nào dưới 5 - Trung bình: nếu ĐKQuả >=5 và không có môn nào dưới 3.5 - Yếu: nếu ĐKQuả >=3.5 và không có môn nào dưới 2 - Kém: còn lại. 8. Xếp hạng theo ĐKQuả 9. Tính Trung bình, Cao nhất, Thấp nhất theo cột cho từng môn Toán, Lý, Hóa, ĐTB, ĐKQuả 10. Trình bày và lưu bảng tính. 2 Bài tập 3 A B C D E F G H I J K 1 BẢNG LƯƠNG NHÂN VIÊN 2 Qũy lương 4000000 LCB 2000 3 STT HỌ TÊN C.V HỆ SỐ N.CÔNG P.CẤP LƯƠNG THƯỞNG TẠM ỨNG CÒN LẠI 4 Phòng kinh doanh 5 1 TRẦN VĂN AN KT 1.8 25 6 2 LÊ THANH LONG PGD 3.5 24 7 3 NGUYỄN THỊ MAI TP 3.0 26 8 4 LÊ VĨNH TRANG KT 1.5 24 9 5 HỒ ĐẮC LỢI PP 2.8 25 10 6 TRẦN THỊ YẾN NV 1.9 23 11 7 MAI ĐĂNG QUANG TX 2.0 26 12 8 LÊ THỊ HẠNH NHÂN NV 2.3 24 13 9 HỒNG THANH QUANG NV 1.7 25 14 10 ÔNG VĂN CHỮ KT 2.1 23 15 Phòng kế hoạch 16 11 LÊ BÁ KHOA GD 3.5 24 17 12 HÀ THỊ THANH PGD 3.4 20 18 13 HOÀNG ANH THƯ TP 2.9 24 19 14 LAI VĂN CẦU KT 2.0 24 20 15 TÔN NỮ DIỆU UYÊN PP 3.0 24 21 16 NGUYỄN ĐỨC THANH NV 2.4 24 22 17 PHẠM THỊ THANH NGA NV 2.5 24 23 18 PHẠM THANH ĐA NV 2.7 23 24 19 LÊ THỊ KIM LONG TX 2.3 23 25 20 LÊ THỊ HOA HỒNG KT 2.1 25 26 TỔNG CỘNG : 27 CAO NHẤT 28 THẤP NHẤT 29 BÌNH QUÂN Yêu Cầu: 1. Tính Phụ cấp(P.CẤP) theo điều kiện sau: - Nếu chức vụ là giám đốc(GD) thì phụ cấp là 50000 - Nếu chức vụ là phó giám đốc(PGD) hoặc trưởng phòng(TP) thì phụ cấp là 40000 - Nếu chức vụ là phó phòng (PP) hoặc kế toán (KT) thì phụ cấp là 30000 - Nếu là chức vụ là nhân viên(NV) và ngày công >=25 thì phụ cấp là 10000 - Còn lại không có phụ cấp. 2. LƯƠNG = HỆ SỐ * LCB * N.CÔNG , nhưng nếu số ngày công làm việc >=25 ngày thì mỗi ngày làm vượt giờ được tính gấp đôi. 3. TỔNG QUỸ THƯỞNG = QUỸ LƯƠNG - ( TỔNG PHỤ CẤP + TỔNG LƯƠNG) 4. Tiền thưởng cho từng người= (TỔNG QUỸ THƯỞNG / TỔNG NGÀY CÔNG) * NGAYCÔNG của từng người, làm tròn đến hàng trăm. Riêng tiền thưởng người cuối cùng = TỔNG QUỸ THƯỞNG - Số tiền đã phân bố cho những người trước. 5. TẠM ỨNG = 2/3 * (LƯƠNG + PHỤ CẤP), tính tròn đến hàng trăm nhưng tạm ứng không được vượt quá 95000. 6. CÒN LẠI = (LƯƠNG+PHỤ CẤP+THƯỞNG)-TẠM ỨNG 7. Tính tổng cho Phòng kinh doanh, phòng kế hoạch và cho cả 2 phòng. 8. Tính CAO NHẤT, THẤP NHẤT, BÌNH QUÂN cho các cột tương ứng 9. Trình bày bảng tính theo ý thích. 3 Bài tập 4 Yêu cầu: Giải thích: - Biểu giá phòng có ý nghĩa: Nếu phòng cho thuê có loại A thì đơn giá thuê cho 1 tuần là $55 và cho 1 ngày là $9,… - Mã phòng có 3 ký tự: 2 ký tự đầu bên trái là số tầng, ký tự bên phải là Loại phòng 1. Chèn vào giữa Ngày đi và Thành tiền 4 cột: SỐ TUẦN, SỐ NGÀY LẺ, ĐGTUẦN, ĐGNGÀY. 2. Tính SỐTUẦN, SỐ NGÀY LẺ (dùng hàm INT, MOD) 3. Tính ĐGTUẦN, ĐGNGÀY dựa vào Loại phòng và Bảng biểu giá 4. Tính THÀNHTIỀN = Tiền tuần + Tiền ngày lẻ - Tiền tuần = SỐTUẦN * ĐGTUẦN - Tiền ngảy lẻ = Số nhỏ hơn giữa (ĐGNGÀY*SỐ NGÀY LẺ ) với ĐGTUẦN. Có nghĩa nếu ĐGNGÀY*SỐ NGÀY LẺ lớn hơn ĐGTUẦN thì xem tiền thuê phòng của các ngày lẻ này là đơn giá của 1 tuần. 5. Tính TIỀN GIẢM cho mỗi khách hàng biết rằng: - Nếu NGÀY ĐẾN là Chủ Nhật và SỐ NGÀY Ở >=30 thì được giảm 10% THÀNH TIỀN - Nếu SỐ NGÀY Ở >=25 thì được giảm 5% THÀNH TIỀN - Các trường hợp khác không giảm 6. Tính PHẢI TRẢ = THÀNH TIỀN - TIỂN GIẢM 7. Làm tròn TIỀN GIẢM và PHẢI TRẢ đến 2 số ở phần thập phân. 4 Bài tập 5 Câu 1 Yêu cầu 1. Tên hàng dựa vào Mã hàng và bảng Danh mục hàng, Đơn giá dựa vào Mã hàng và Bảng đơn giá. 2. Thành tiền=Sốlượng *Đơn giá 3. Phí vận chuyển dựa vào ký tự đầu của cột Mã hàng. Nếu ký tự đầu của cột Mã hàng là “S” hoặc “V” thì Phí vận chuyển=10%*Thành tiền. Ngược lại Phí vận chuyển= 5%*Thành tiền. 4. Tổng cộng=Thành tiền+Phí vận chuyển Câu 2 Yêu cầu: 1. Tên hàng dựa vào 3 số đầu của Mã hàng và bảng Danh mục hàng 2. Thêm cột Loại hàng sau Tên hàng, Loại hàng là số cuối của Mã hàng và chuyển qua trị số. 3. Thành tiền = Số lượng * Đơn giá, Đơn giá dựa vào Loại hàng và bảng Danh mục hàng 4. Tiền giảm được tính theo điều kiện sau: - Nếu Số lượng >=250, giảm 5% trên Thành tiền - Nếu Số lượng >=200, giảm 3% trên Thành tiền - Ngược lại thì không giảm. 5. Phải trả = Thành tiền - Tiền giảm 5 Bài tập 6 Yêu cầu 1. Xếp loại: A: Nếu ngày công>=27 B: Nếu ngày công >=26 C: Nếu ngày công >=25 D: Nếu ngược lại 2. Phụ cấp: Dựa vào ký tự đầu của Mã số và Bảng phụ cấp 3. Lương = LCB*Ngày công. Nhưng nếu số ngày là việc lớn hơn ngày công qui định thì mỗi ngày dư được tính gấp đôi. 4. Thưởng: Dựa vào xếp loại và Bảng tiền thưởng để tính thưởng theo phần trăm của lương, nếu Xếp loại không có trong Bảng tiền thưởng thì Thưởng = 0 5. Thu nhập=Phụ cấp+Lương+Thưởng 6. Thuế: Dựa vào tiền thu nhập và bảng Thuế thu nhập để tính. 7. Thực lãnh: Thực lãnh=Thu nhập-Thuế, Thực lãnh được làm tròn đến hàng đơn vị. 8. Trang trí bảng tính 9. Sắp xếp bảng tính tăng dần theo thực lãnh. 6 Bài tập 7 Yêu cầu: Giải thích: ký tự đầu trong SỐĐK cho biết khu vực, ký tự bên phải cho biết loại đã đăng ký. 1. Tính số Kw tiêu thụ : TIÊU THỤ = SỐ MỚI - SỐ CŨ 2. ĐƠN GIÁ dựa vào ký tự đầu của Số điện kế(SỐĐK) và Bảng đơn giá. 3. Tiền trong định mức(TRONG ĐM) = Số Kw tiêu thụ trong định mức * Đơn giá 4. Tiền vượt định mức(VƯỢT ĐM) = Số Kw tiêu thụ vượt định mức * Đơn giá * hệ số vượt định mức, biết rằng: - Số lần vượt định mức = (TIÊU THỤ - ĐỊNH MỨC)/ ĐỊNH MỨC - Hệ số vượt định mức được tính dựa vào số lần vượt định mức và Bảng hệ số. 5. TỔNG = TRONG ĐM + VƯỢT ĐM 6. Trang trí bảng tính. 7. Sắp xếp bảng tính giảm dần theo SỐĐK 8. Sử dụng Autofilter lọc những mẫu tin có TIÊU THỤ < 400 9. Sử dụng Autofilter lọc những mẫu tin có ĐỊNH MỨC =180 hoặc ĐỊNH MỨC >200 10. Sử dụng Autofilter lọc những mẫu tin có SỐĐK = “A001” và tiêu thụ vượt định mức. 11. Sử dụng Advanced Filter lọc dữ liệu gồm những mẫu tin có ký tự bên phải của SỐĐK là 1 12. Sử dụng Advanced Filter lọc dữ liệu gồm những mẫu tin có ký tự bên trái là “B” hoặc “C”. 13. Sử dụng Advanced Filter lọc dữ liệu gồm những mẫu tin có ký tự bên trái là “B” hoặc “C” và tiêu thụ không vượt định mức 7 Bài tập 8 Yêu cầu: 1. Sắp xếp bảng tính sao cho cột Mã hàng có thứ tự tăng dần 2. Điền dữ liệu cho cột Loại hàng, biết loại hàng là 1 ký tự bên phải của Mã hàng và chuyển sang dạng số 3. Điền Tên hàng dựa vào 1 ký tự đầu của Mã hàng với Bảng đơn giá 4. Thành tiền = Số lượng * Đơn giá, Đơn giá được tính dựa vào Loại hàng và Bảng đơn giá, nếu Loại hàng là 1 thì lấy ĐG1, nếu loại hàng là 2 thì lấy ĐG2. 5. Thuế = Thành tiền * Tỷ lệ thuế và chỉ tính cho các mặt hàng không thuộc loại miễn thuế, trong đó Tỷ lệ thuế được tính dựa vào ký tự giữa của Mã hàng và bảng Tỷ lệ thuế. 6. Còn lại = Thành tiền - Thuế 7. Từ bảng kết toán trên, lọc ra những dòng được miễn thuế. 8. Từ bảng kết toán trên, lọc ra những dòng có Tên hàng là “Hột điều” hoặc “Gạo” 9. Từ bảng kết toán trên, lọc ra những dòng có Số lượng lớn hơn 35 và nhỏ hơn 80 10. Thực hiện thống kê sau: Tên hàng Tổng gtrị xuất(sau thuế) Hột điều Gạo Cà phê Đường 8 Bài tập 9 Biết ký tự bên phải của Mã hàng là Loại hàng, yêu cầu: 1. Sử dụng chức năng Advanced Filter, lọc ra các danh sách: a) Lọc ra danh sách các mặt hàng Loại 1 b) Lọc ra danh sách các mặt hàng có Nhập Kho >=500 và Tồn Kho>400 c) Lọc ra danh sách các mặt hàng là Tôm hoặc có Tồn Kho>=600 d) Lọc ra danh sách các mặt hàng có NhậpKho, Xuất Kho và Tồn Kho<500 e) Lọc ra danh sách các mặt hàng có Xuất Kho trong tháng 5 hoặc tháng 10 f) Lọc ra danh sách các mặt hàng có ký tự đầu mã hàng là “C” hoặc “B” và xuất kho từ ngày 1 đến ngày 15. 2. Hoàn thành bảng thống kê sau: a) Có bao nhiêu mặt hàng có Mã Hàng bắt đầu bằng chữ T ? b) Có bao nhiêu mặt hàng Loai 2 ? c) Tổng Nhập Kho của các mặt hàng Loai 3 là bao nhiêu? d) Tổng Xuất Kho của các mặt hàng Tôm là bao nhiêu? e) Giá trị nhỏ nhất Tồn Kho của các mặt hàng Loai 2 là bao nhiêu? f) Giá trị lớn nhất Xuất Kho của mặt hàng Mực là bao nhiêu? g) Tổng Tồn Kho của các mặt hàng Loai 1 và Loai 2 là bao nhiêu ? h) Tổng Nhập Kho và Xuất Kho của các mặt hàng Loai 3 là bao nhiêu ? i) Tổng Tồn Kho của mặt hàng Bào Ngư và Nghêu là bao nhiêu? j) Tổng Xuất Kho của mặt hàng Tôm Loai 1 và Mực Loai 3 là bao nhiêu ? k) Giá trị Nhập Kho nhỏ nhất của các mặt hàng có Tồn Kho >=500 là bao nhiêu? l) Tổng Tồn Kho của các mặt hàng có Nhập Kho =200 là bao nhiêu? m) Tổng Xuất Kho của các mặt hàng Loại 1 trong tháng 10 là bao nhiêu ? n) Tổng Nhập Kho và Xuất Kho của các mặt hàng trong ngày Thứ Bảy và Chủ Nhật là bao nhiêu? 3. Hoàn thành bảng thông kê sau và vẽ đồ thị dạng Column minh hoạ: 9 Bài tập 10 Câu 1 Yêu cầu: 1. Tên hàng dựa vào các ký tự đầu của SHĐ và Bảng giá, biết rằng SHĐ có chiều dài thay đổi. 2. Đơn giá dựa vào trước hay sau ngày 20/06/2006 và Mua hay Bán 3. Tính Thành tiền = Số lượng * Đơn giá 4. Sắp xếp danh sách trên theo thứ tự tăng dần của SHĐ 5. Thống kê doanh số bán hàng của từng nhóm hàng, vẽ đồ thị Column để biểu diễn. 6. Thống kê số lượng bán hàng theo từng ngày, vẽ đồ thị Line để biểu diễn tốc độ tăng trưởng. 10 Bài tập 11 Yêu cầu 1. Điền tháng hiện hành vào (1) và năm hiện hành vào (2) 2. Nhập dữ liệu NGÀYHĐ(3) bắt đầu từ ngày đầu tiên của tháng năm hiện hành và sau đó mỗi ô dưới cách ô kề trên 2 ngày. 3. TÊN THUỐC(4) dựa vào Bảng 1 với dòng là 2 ký tự đầu và cột là 3 ký tự sau của Mã thuốc 4. NGUỒN GỐC(5) dựa và Bảng tổng hợp và Bảng 2 5. NHẬP(6)= SỐ LƯỢNG * ĐƠN GIÁ NHẬP, với đơn giá nhập dựa vào ký tự đầu của SỐHĐ và Bảng tổng hợp 6. XUẤT(7)= SỐ LƯỢNG * ĐƠN GIÁ XUẤT, với đơn giá xuất dựa vào ký tự đầu của SỐHĐ và Bảng tổng hợp 7. Thực hiện thống kê để có (8), (9) 8. (10) = (8) – (9) 11