Bài thực hành MS Excel, Exercises of MS Microsoft Excel skills

Download Bài thực hành MS Excel and more Exercises MS Microsoft Excel skills in PDF only on Docsity! Bài tập 1: Hệ số 20 Mã nhân viên Chức vụ Phụ cấp chức vụ Ngày công Mức lương Lương Tiền lĩnh H01A Trần Thanh GĐ ? 23 420 ? ? T01B Lê Hoàng PGĐ ? 24 400 ? ? H02A Ngọc Vân TP ? 25 400 ? ? H01C Thanh Thảo NV ? 10 290 ? ? H02C Thuý Kiều PP ? 20 350 ? ? T02B Từ Hải KT ? 30 350 ? ? ? ? ? ? Yêu cầu: A) Tính toán 2. Lương: Hệ số nhân Mức lương nhân Ngày công 3. Tiền lĩnh: Phụ cấp chức vụ cộng với Lương 4. Tổng của các cột Phụ cấp chức vụ , Ngày công , Lương , Tiền lĩnh B) Định dạng bảng tính như trong bài tập C) Đổi tên Sheet thành: If-Sum D) Lưu tệp tin với tên là: BAI TAP EXCEL Họ tên Tổng cộng BẢNG THANH TOÁN LƯƠNG + THƯỞNG Tháng 8 năm 2014 1. Phụ cấp chức vụ: Nếu GĐ thì 5000, nếu PGĐ hoặc TP thì 4000, nếu PP hoặc KT thì 3000, còn lại thì không có phụ cấp LCB 350000 STT MANV HSL HSPC BHXH LƯƠNG CÒN LẠI 1 A01 Tôn Ngộ Không 3.01 ? ? ? ? 2 B02 Trư Bát Giới 2.9 ? ? ? ? 3 C01 Đường Tăng 3.3 ? ? ? ? 4 A02 Sa Ngộ Tĩnh 3.1 ? ? ? ? Yêu cầu: A) Tính toán 2. BHXH: Bằng 5% của HSL nhân với LCB 3. Lương: Bằng HSL nhân LCB cộng với HSPC nhân HSL nhân LCB 4. Còn lại: LƯƠNG trừ BHXH B) Định dạng bảng tính như trong bài tập C) Đổi tên Sheet thành: Phep toan co ban - Left D) Lưu tệp tin BẢNG LƯƠNG THÁNG 9 NĂM 2014  HỌ VÀ TÊN Bài tập 2: Mở tệp tin BAI TAP EXCEL đã làm ở bài tập 1, kích vào nút new Sheet  làm bài tập sau: 1. Hệ số phụ cấp (HSPC): Nếu ký tự đầu của MANV là "A" thì HSPC bằng 0.4, nếu là "B" thì 0.2, còn lại không có. STT P H Á I T O Á N L Ý H O Á ĐIỂM HỆ SỐ VỊ THỨ KẾT QUẢ 1 Trần Văn Thứ 1 6 5 7 2 Lê Anh Đào 0 8 8 7 3 Nguyễn Hoàng Gia 1 9 6 9 4 Võ Hữu Danh 1 6 3 6 5 Lương Công Tuấn 1 2 4 5 6 Cao Bình 1 5 7 7 7 Trần Thanh Vân 0 7 8 7 8 Trần Văn Long 1 8 8 9 9 Nguyễn Duy Hà 1 8 9 7 10 Đào Thu Trang 0 10 7 8 Yêu cầu: A) Tính toán 2. Xếp hạng: Căn cứ vào cột Điểm hệ số để xếp hạng B) Định dạng bảng tính như trong bài tập, những chữ "Đậu" có màu xanh, chữ "Hỏng" có màu đỏ C) Đổi tên Sheet thành: Rank-Định dạng ĐK D) Lưu tệp tin Bài tập 5: Mở tệp tin BAI TAP EXCEL đã làm ở bài tập 1, kích vào nút new Sheet  làm bài tập sau: 1. Điểm hệ số: = Toán nhân 2 cộng Lý cộng Hóa 3. Kết quả: Thí sinh có hạng nhỏ hơn 5 thì "Đậu ", còn lại thì "Hỏng " 4. Sắp xếp lại bảng tính theo cột Xếp hạng 5. Xóa bỏ cột Phái Sở Giáo dục TP Đà Nẵng Trường PTTH Lê Quý Đôn @&? KẾT QUẢ THI TUYỂN VÀO LỚP 10 CHUYÊN TOÁN NĂM 2014 HỌ VÀ TÊN STT Mã NV Họ tên Chức vụ HSL Phụ cấp Lương Bảo hiểm Tiền lĩnh 1 A1BC Trăm GĐ 4.25 GĐ 500 2 B1HĐ Năm PGĐ 3.12 PGĐ 400 3 A2HĐ Trong TP 2.78 TP 300 4 B2HĐ Cõi PP 3.12 PP 200 5 C3HĐ Người NV 1.92 NV 0 6 C1BC Ta NV 2.32 Yêu cầu: A) Tính toán 2. Lương: = HSL nhân 120000 B) Định dạng bảng tính như trong bài tập, cột Tiền lĩnh định dạng theo tiền USD C) Đổi tên Sheet thành: Vlookup-Logic D) Lưu tệp tin Bài tập 6: Mở tệp tin BAI TAP EXCEL đã làm ở bài tập 1, kích vào nút new Sheet  làm bài tập sau: 1. Phụ cấp: Dùng hàm Vlookup tham chiếu Bảng phụ cấp để tính 4. Tiền lĩnh: = Phụ cấp cộng Lương trừ Bảo hiểm 5. Tính tổng cho các cột tô màu 3. Bảo hiểm: Nếu ký tự đầu của Mã NV là "A" và 2 ký tự cuối là "BC" thì bằng 10% Lương , nếu ký tự đầu của Mã NV là "A" hoặc "B" và 2 ký tự cuối là "HĐ" thì bằng 5% Lương, còn lại bằng 0% Lương BẢNG THANH TOÁN LƯƠNG THÁNG 8 NĂM 2014 Tổng cộng BẢNG PHỤ CẤP SỐ TT HỌ VÀ TÊN THÍ SINH CHỨC VỤ PHÁI THĐCC TCC ANH LTĐT ĐTB ĐKQ KẾT QUẢ 1 Khi LP Nam 4.1 3.1 7 8 2 Trời TV Nam 8.4 5.6 4.7 6 3 Đất TV Nữ 5.4 4.3 3.4 4.5 4 Nổi LT Nam 9 9 9.5 9 5 Cơn BT Nữ 3 4.6 4.8 5 6 Giông TV Nữ 3 5.6 4.8 6 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 0 Lưu ban Loại Số Thí Sinh 3 Thi lại Lưu ban 5 Trung bình Thi lại 7 Khá Trung bình 9 Giỏi Khá Giỏi Yêu cầu: A) Tính toán Bài tập 7: Mở tệp tin BAI TAP EXCEL đã làm ở bài tập 1, kích vào nút new Sheet  làm bài tập sau: 1. ĐTB: = (THĐCC * 4 + TCC * 3 + ANH * 2 + LTĐT * 3)/12, làm tròn đến 2 số lẻ TỔNG HỢP SỐ LIỆU Trường Đại học Sư phạm Đà Nẵng Kết quả thi học kỳ 1 năm học 2013 - 2014  Cao nhất Thấp nhất Trung bình Bảng Kết Quả B) Định dạng bảng tính như trong bài tập C) Thiết lập mật khẩu để bảo vệ không cho sửa công thức và dữ liệu trong Sheet D) Đổi tên Sheet thành: Hlookup-Count-Sumif E) Lưu tệp tin 6. Lập Bảng TỔNG HỢP SỐ LIỆU ở trên 5. Sắp xếp bảng tính theo cột Phòng Bài tập 9: LOẠI TẦNG 1 Trần Văn Thứ 01-06-14 10-06-14 B 1 2 Lê Anh Đào 04-06-14 12-06-14 A 2 3 Nguyễn Hoàng Gia 10-06-14 15-06-14 C 4 4 Võ Hữu Danh 11-06-14 05-07-14 C 3 5 Lương Công Tuấn 13-06-14 20-06-14 A 5 6 Cao Bình 14-06-14 16-06-14 A 2 7 Trần Thanh Vân 17-06-14 25-06-14 B 2 8 Trần Văn Long 22-06-14 15-07-14 C 6 9 Nguyễn Duy Hà 25-06-14 30-06-14 B 3 10 Đào Thu Trang 03-07-14 30-07-14 B 5 11 Trương Văn Sử 04-07-14 30-07-14 B 6 12 Lê Đức Hạnh 06-07-14 20-07-14 C 4 13 Lê Hữu Hạnh 07-07-14 14-07-14 C 3 14 Trịnh Quốc Cường 10-07-14 22-07-14 A 2 15 Võ Văn Dũng 13-07-14 01-08-14 C 1 LOẠI GIÁ TẦNG 1 2 3 4 5 6 A 300000 HỆ SỐ 100% 95% 90% 85% 80% 75% B 250000 C 200000 STT TÊN KHÁCH HÀNG NGÀY ĐẾN NGÀY ĐI SỐ NGÀY ĐƠN GIÁ PHÒNG Tổng cộng BẢNG GIÁ BẢNG HỆ SỐ THÀNH TIỀN PHÒNG Yêu cầu: A) Tính toán B) Định dạng bảng tính như trong bài tập C) Thiết lập mật khẩu để bảo vệ cấu trúc bảng tính D) Đổi tên Sheet thành: H-Vlookup E) Lưu tệp tin 2. Đơn giá phòng: = giá loại phòng nhân hệ số tầng lầu, căn cứ vào BẢNG GÍA và BẢNG HỆ SỐ ở trên để tính 3. Thành tiền : = Đơn giá phòng nhân Số ngày 1. Số ngày: = 1 nếu Ngày đi bằng Ngày đến = Ngày đi - Ngày đến nếu Ngày đi khác Ngày đến Bài tập 11: XSTK PPNCKH HĐH MMT 3 1 3 3 1 Nguyễn Lan Anh 4.8 8.0 8.0 5.8 90 2 Lê Văn Bình 3.4 3.6 4.7 80 3 Nguyễn Văn Bình 3.8 7.4 8.6 5.7 85 4 Nguyễn Hữu Hoàng 8.0 8.0 7.4 89 5 Hoàng Điệp 7.4 8.4 10.0 9 90 6 Trần Thị Thanh 7.4 8.0 5.8 87 7 Võ Hà Giang 8.0 8.6 7.4 95 8 Trịnh Thị Hồng 8.6 8.0 9.6 6.8 80 9 Võ Duy Hoàng 4.8 9.0 8.0 5.8 85 10 Phan Thị Thảo 8.6 7.4 7.6 5.8 87 Yêu cầu: A) Tính toán: Hãy lập công thức mảng để thực hiện các yêu cầu sau B) Định dạng bảng tính như trong bài tập C) Đổi tên Sheet thành: Array 2 D) Lưu tệp tin KẾT QUẢ HỌC TẬP LỚP SPT Số sinh viên có học bổng Bảng thống kê 1. Tính Số TC của mỗi sinh viên Họ và tênSTT S ố T C T B C Số tín chỉ Đ R L 2. Tính ĐTB của mỗi sinh viên H ọ c b ổ n g 3. Xét Học bổng theo quy định: Sinh viên có ĐRL>=85 và ĐTB>=7 thì có "HB", ngược lại thì "KHB". 4. Thực hiện Bảng thống kê sau Số sinh viên học lực yếu Bài tập 12: STT NGÀY LÝ DO LOẠI BH THU CHI GHI CHÚ 1 08-04-04 Bán bảo hiểm Xe máy 85,000 2 10-04-04 Bán bảo hiểm Xe máy 145,000 3 12-04-04 Bán bảo hiểm Xe ô tô 500,000 4 14-04-04 Bán bảo hiểm Y tế 30,000 5 16-04-04 Chi viện phí BHYT Y tế 15,000 6 18-04-04 Bán bảo hiểm Xe ô tô 500,000 7 20-04-04 Chi bồi thường tai nạn Xe máy 100,000 8 22-04-04 Bán bảo hiểm Xe máy 85,000 9 24-04-04 Bán bảo hiểm Y tế 50,000 10 26-04-04 Bán bảo hiểm Xe ô tô 500,000 11 28-04-04 Chi bồi thường tai nạn Xe máy 120,000 12 30-04-04 Chi viện phí BHYT Y tế 5,000 13 05-05-04 Chi bồi thường tai nạn Xe máy 125,000 14 08-05-04 Bán bảo hiểm Y tế 45,000 15 11-05-04 Bán bảo hiểm Y tế 30,000 16 14-05-04 Chi viện phí BHYT Y tế 7,500 17 17-05-04 Bán bảo hiểm Xe ô tô 400,000 18 19-05-04 Chi bồi thường tai nạn Xe ô tô 750,000 19 20-05-04 Chi bồi thường tai nạn Xe máy 130,000 20 30-05-04 Chi viện phí BHYT Y tế 5,000 Yêu cầu: A) Sử dụng các công thức DFUNCTION để thực hiện các bảng thống kê sau xe máy xe ô tô y tế Số khách Tổng thu Tổng chi Lợi nhuận Cao nhất cho 1 vụ Thấp nhất cho 1 vụ Trung bình cho 1 vụ B) Định dạng bảng tính như trong bài tập C) Đổi tên Sheet thành: Dfunction D) Lưu tệp tin THỐNG KÊ CHI XE MÁY BẢNG DOANH THU BẢO HIỂM TỔNG CÔNG TY BẢO HIỂM VIỆT NAM NGHIỆP VỤ PHÁT SINH QUÝ II -----o0o----- Bài tập 13: Stt Khách hàng Loại xe Ngày thuê Ngày trả Số ngày Đơn giá Thành tiền 1 Long Máy xúc 10-01-07 27-01-07 Loại xe Đơn giá 2 Hà Nâng hàng 02-02-07 04-03-07 Tải nhẹ 2.5 T 200,000 3 Thanh Tải nhẹ 2.5 T 10-04-07 25-04-07 Tải nặng 5 T 450,000 4 Việt Tải nặng 5 T 20-05-07 25-05-07 Nâng hàng 300,000 5 Hùng Máy xúc 22-05-07 12-06-07 Máy xúc 400,000 6 Châu Tải nhẹ 2.5 T 13-06-07 16-06-07 Yêu cầu: A) Tính toán Loại xe Số lần thuê Tổng số ngày Tổng tiền Nâng hàng Máy xúc Tải nhẹ 2.5 T Tải nặng 5 T B) Định dạng bảng tính như trong bài tập C) Đổi tên Sheet thành: Array-Dfunction D) Lưu tệp tin Tên khách hàng trả tiền thuê nhiều nhất Bảng thống kê 2. Tham chiếu Bảng giá để tính cột Đơn giá 3. Hãy lập công thức mảng để tính Thành tiền = Số ngày * Đơn giá . Biết rằng: Trong số ngày từ Ngày thuê đến Ngày trả, nếu có ngày Chủ Nhật thì Đơn giá tính gấp 4. Dùng các công thức mảng và hàm Dfunction để tính bảng thống kê sau BẢNG TÍNH TIỀN THUÊ XE Bảng giá Tổng Cộng 1. Hãy lập công thức mảng để tính Số ngày thuê 4. Tính tổng cho các cột Số ngày và Thành tiền Bài tập 15: SBD Họ và tên Diện ưu tiên Toán Lý Hóa Sinh Điểm ưu tiên Tổng điểm Kết quả A01 Ngô Thị Lệ K 10.0 9.0 7.0 A B B02 Phạm Thị Linh K 10.0 9.0 10.0 16 17.5 A03 Nguyễn Thị Lan TB 9.0 10.0 9.0 B04 Dương Quang Long K 7.0 3.0 6.0 A05 Nguyễn Thế Luận LS 3.0 7.0 8.0 LS 1.5 B06 Nguyễn Duy Thanh TB 8.0 9.0 7.0 TB 1.0 A07 Trịnh Thị Mai K 4.0 5.0 8.0 MN 0.5 A08 Nguyễn Thị Lý MN 5.0 2.0 10.0 K 0.0 B09 Nguyễn Thị Mơ K 1.0 8.0 6.0 A10 Phan Thị Hương K 4.0 7.0 6.0 Yêu cầu: A) Tính toán: Hãy lập công thức mảng để thực hiện các yêu cầu sau B) 1. Định dạng cột Kết quả với điều kiện chữ "Đậu" có màu xanh và chữ "Hỏng" có màu đỏ C) Định dạng bảng tính như trong bài tập D) Đổi tên Sheet thành: Array-Sort & Filter 2 E) Lưu tệp tin KẾT QUẢ THI TUYỂN SINH KHÓA 2014 4. Dùng chức năng Advanced Filter để trích lọc danh sách sinh viên "Đậu", "Hỏng". 5. Xóa điều kiện lọc của các cột ĐIỂM CHUẨN ĐIỂM ƯU TIÊN 2. Sắp xếp bảng giảm dần theo cột Tổng điểm, nếu điểm trùng thì sắp xếp 3. Thực hiện chức năng AutoFilter để xem danh sách thí sinh đậu hoặc hỏng 1. Tham chiếu bảng ĐIỂM ƯU TIÊN để tính Điểm ưu tiên cho thí sinh 2. Tính Tổng điểm cho thí sinh 3. Xét Kết quả theo bảng ĐIỂM CHUẨN Sort & Filter Bài tập 16: STT Loại quả Số lượng (KG) Giá (USD) Thành tiền (USD) Ngày bán 1 Chuối 10 2.3 01-12-13 2 Dứa 6 2.7 01-12-13 3 Dứa 7 2.7 01-12-13 4 Táo 12 3.4 01-12-13 5 Lê 8 2.4 01-12-13 6 Cam 12 1.7 01-12-13 7 Chanh 6 1.2 01-12-13 8 Dứa 5 2.7 02-12-13 9 Táo 14 3.4 02-12-13 10 Táo 11 3.4 02-12-13 11 Táo 16 3.4 02-12-13 12 Lê 4 2.4 02-12-13 13 Lê 8 2.4 02-12-13 14 Chanh 12 1.2 02-12-13 15 Chuối 15 2.3 03-12-13 16 Táo 13 3.4 03-12-13 17 Lê 24 2.4 03-12-13 18 Cam 14 1.7 03-12-13 19 Cam 34 1.7 03-12-13 20 Chanh 3 1.2 03-12-13 Yêu cầu: A) B) Định dạng bảng tính như trong bài tập C) 1. Tổng tiền bán được của mỗi loại quả 2. Tổng tiền bán được của mỗi loại quả theo từng ngày 3. Số lượng hóa đơn đã bán theo mỗi loại 5. Số lượng (kg) bán được của các loại Cam, Táo, Chuối D) Đổi tên Sheet thành: Pivot Table 1 E) Lưu tệp tin Hãy thực hiện các thống kê sau: Tính toán: Hãy lập công thức mảng để tính cột Thành tiền = Số lượng * Giá 4. Số lượng (kg) bán được của mỗi loại theo từng ngày hoặc tất cả các ngày Bài tập 17: STT Ngày Nhân viên Cửa hàng Sản phẩm Số lượng Thành tiền 1 29-01-14 Hồng Số 1 Bánh 5 Bánh Kẹo Mứt 2 29-01-14 Cúc Số 1 Kẹo 4 35000 25000 40000 3 29-01-14 Huệ Số 2 Kẹo 7 4 30-01-14 Lan Số 2 Mứt 15 5 30-01-14 Cúc Số 1 Mứt 8 6 31-01-14 Cúc Số 1 Bánh 12 7 31-01-14 Lan Số 2 Bánh 11 8 31-01-14 Huệ Số 2 Kẹo 10 9 01-02-14 Hồng Số 1 Kẹo 8 10 01-02-14 Hồng Số 1 Mứt 9 11 01-02-14 Huệ Số 2 Bánh 15 12 01-02-14 Huệ Số 2 Kẹo 14 13 02-02-14 Cúc Số 1 Bánh 17 14 02-02-14 Hồng Số 1 Bánh 16 15 02-02-14 Cúc Số 2 Kẹo 13 16 02-02-14 Huệ Số 2 Mứt 12 17 03-02-14 Hồng Số 1 Kẹo 18 18 03-02-14 Huệ Số 2 Mứt 16 19 03-02-14 Cúc Số 2 Bánh 8 20 04-02-14 Lan Số 1 Kẹo 9 Yêu cầu: A) B) Định dạng bảng tính như trong bài tập C) D) Đổi tên Sheet thành: Pivot Table 2 E) Lưu tệp tin Tính toán: Hãy lập công thức mảng để tính cột Thành tiền = Số lượng * Giá BẢNG GIÁ Hãy dùng Pivot Table để tổng hợp số tiền theo từng cửa hàng, từng nhân viên và từng ngày