Một số bài tập thực hành Excel

Chú ý: Học viên tạo một tập tin với tên là tenhocvien_số của bài thực hành Bài thực hành số 1 Công thức và Hàm A B A+ B A-B A*B A/B A lũy thừa B A&B 1 10 3 8 5 6 7 4 9 2 A B A2 + B (A + B)2 (A - B)*A (A+B)*A - B (A+B)*(A-B) ((A+B)/(A-B))4 16 4 5 3 Yêu cầu: học viên hoàn thành các công thức trên Bài thực hành số 2 BẢNG LƯƠNG NHÂN VIÊN THÁNG …../2005 STT HỌ VÀ TÊN CHỨC VỤ LƯƠNG CƠ BẢN NGÀY LƯƠNG TẠM ỨNG CÒN LẠI 1 Nguyễn Văn A TP 350000 25 2 Trần Văn B NV 330000 24 3 Lương Sơn Bá PP 340000 26 4 Chúc Anh Đài NV 330000 26 5 Nguyễn Y Chang KT 335000 20 6 Vũ Như Cẩn BV 340000 28 7 Đổng Như Khôi TQ 335000 26 8 Sang Song Luôn PP 350000 24 9 Nguyễn Trùng Dương NV 330000 27 10 Lục Vân Tiên BV 335000 29 Tổng cho các cột Lương, Tạm Ứng, Còn lại Giá trị cao nhất cho các cột Lương, Tạm Ứng, Còn lại Giá trị thấp nhất cho các cột Lương, Tạm Ứng, Còn lại Giá trị trung bình cho các cột Lương, Tạm Ứng, Còn lại Yêu Cầu: 1. Trình bày bảng tính trên 2. Tính lương tháng:=Lương Cơ Bản * Ngày Công 3. Tạm Ứng =50%*Lương Tháng 4. Còn Lại:=Lương Tháng - Tạm Ứng 5. Tính Tổng, Cao nhất, Thấp nhất, Trung bình cho các cột Lương, Tạm Ứng, Còn Lại 6. Định dạng các cột: Lương, Tạm Ứng, Còn Lại ở dạng "VNĐ" Bài thực hành số 3 BẢNG KÊ HÀNG NHẬP THÁNG …./2004 TẠI KHO QUẢNG NGÃI STT CHỨNG TỪ DIỄN GIẢI SỐ LƯỢNG ĐƠN GIÁ THUẾ CHUYÊN CHỞ CỘNG 1 B50 Bàn gỗ 40 1000000 2 B51 Bàn sắt 45 1500000 3 B52 Bàn Formica 85 550000 4 B53 Ghế dựa 42 360000 5 B54 Ghế xếp 12 450000 6 B55 Ghế đẩu 36 120000 7 B56 Tủ gổ 52 2500000 8 B57 Tủ sắc 54 3700000 Yêu Cầu: 1. Trình bày bảng tính trên 2. Chèn thêm cột trị giá vào sau cột đơn giá rồi tính Trị Giá:=Số Lượng*Đơn Giá 3. Tính Thuế:=Trị Giá*3.75% làm tròn đến hàng trăm. 4. Cước chuyên chở:=Số Lượng*0.75% Trị giá, làm tròn đến hàng nghìn. 5. Tính Tổng cộng:=Trị giá+Thuế+Chuyên chở 6. Định dạng cột Đơn giá, Thuế, Trị giá, Chuyên chở, Cộng có dấu phân cách phần nghìn. Bài thực hành số 4 DOANH SỐ BÁN HÀNG CỦA TT THƯƠNG MẠI VÀ PHÁT TRIỂN CÔNG NGHỆ ĐẠI VIỆT TỪ 1- 5/7/2001 Bảng 1 NGÀY MÃ HÀNG TÊN HÀNG ĐƠN VỊ TÍNH SỐ LƯỢNG ĐƠN GIÁ THUẾ THÀNH TIỀN 1 MT 3 1 C 2 1 MI 1 2 BP 2 2 MT 2 2 UPS 1 3 HDD 4 3 HDD 2 3 MT 1 3 MH 2 4 MT 2 4 C 10 4 MI 3 5 ODC 6 5 CS 4 5 FDD 8 5 MT 3 Bảng 2 MÃ HÀNG TÊN HÀNG ĐƠN VỊ TÍNH ĐƠN GIÁ MT Máy tính Bộ 5200000 C Chuột Con 35000 MI Máy in Cái 4200000 BP Bàn phím Cái 60000 UPS Tích điện Cái 850000 HDD Ổ đĩa cứng Ổ 1000000 CDR Ổ đĩa CDROM Ổ 450000 MH Màn hình Cái 1420000 CS Case Cái 170000 FDD Đĩa mềm Hộp 65000 Yêu cầu: Câu 1: Dựa vào số liệu đã cho ở Bảng 2, dùng công thức để lấy Tên hàng, Đơn vị tính, Đơn giá cho Bảng 1 trên. Câu 2: Tính tiền thuế (cột Thuế), biết rằng: Nếu là Máy tính hoặc Máy in thì Thuế = 5% đơn giá. Còn những thiết bị khác thì Thuế = 10% đơn giá. Câu 3: Tính số tiền (cột Tiền) cho các mặt hàng được bán ra trong Bảng 1. Câu 4: Chèn vào cuối Bảng 1 3 dòng để hiển thị các kết quả sau: Lần lượt tính tổng số tiền bán Máy tính, Máy in, Màn hình, .... Tính số lần bán mặt hàng Máy tính, Máy in, Màn hình, .... Bài thực hành số 5 Tạo bảng tính với số liệu thô như sau: KẾT QUẢ TUYỂN SINH NĂM 2006 - ĐẠI HỌC ĐÀ NẴNG STT SBD HỌ TÊN ĐTCS TOÁN LÝ HÓA ĐUT TỔNG KẾT QUẢ 1 BK001 Tô Linh Kha LS  ? ?  ? ? ? ? 2 BK002 Trúc Lan Mai TB 3 BK003 Cổ Gia Trưởng BB 4 BK004 Chúc Anh Đài KH 5 KT001 Hồ Mỹ Nhân TB 6 KT002 Đoàn Tự Cổ KH 7 SP001 Cao Bất Hứa LS 8 SP002 Vi Nhân Gian TB BẢNG CHẤM BẢNG ĐIỂM ƯU TIÊN SBD MÔN 1 MÔN 2 MÔN 3 ĐTCS LS TB BB BK001 6 7 6 ĐIỂM UT 2 1 0.5 KT001 9 9 5 BK002 3 7 5 SP001 9 7 6 BK003 8 8 7 KT002 7 6 9 SP002 7 4 5 BK004 4 3 4 Trong đó: SBD: Số báo danh dự thi của thí sinh ĐTCS: Mã đối tượng chính sách, TB (thương binh), LS (liệt sỹ); BB (bệnh binh); KH(không) ĐUT: Điểm ưu tiên, tuỳ thuộc vào ĐTCS cho trong Bảng điểm ưu tiên, nếu thí sinh nào có ĐTCS là “KH” thì Điểm ưu tiên (ĐUT) bằng không TỔNG: Tổng số điểm bao gồm điểm 3 môn thi cộng với điểm ưu tiên KẾT QUẢ: Kết quả thi (ĐẬU hay KHÔNG), biết rằng chỉ tiêu của Nhà trường là tuyển vào 5 thí sinh có tổng điểm thi cao nhất HƯỚNG DẪN Các công thức sử dụng: IF, VLOOKUP, HLOOKUP, RANK kết hợp với địa chỉ tuyệt đối Tính điểm Toán (môn 1): =VLOOKUP() Tính điểm Lý (môn 2): =VLOOKUP() Tính điểm Hoá (môn 3): =VLOOKUP() Tính Điểm ưu tiên: =IF(HLOOKUP()) Tính Tổng điểm: = Toán + Lý + Hóa + Điểm Ưu Tiên. Tính Kết quả:=IF(RANK("ĐẬU", "KHÔNG") Bài thực hành số 6 Bảng Giá Thanh Toán Tiền Điện Tháng 12/2005 CHỦ HỘ LOẠI SÔCŨ SỐMỚI SỐKW ĐM TĐM NĐM T.TĐM T.NĐM CỘNG MỘT CQ-A 200 250  ? ?  ? ? ? ? ? HAI NN-D 240 280 BA CB-A 340 450 BỐN CQ-A 430 540 NĂM CB-B 230 450 SÁU CB-C 120 540 BẢY CN-D 210 350 TÁM CN-C 260 410 CHÍN CQ-B 290 450 MƯỜI CQ-D 320 540 NHẤT NN-A 110 230 NHÌ KD-C 230 410 Chú thích: Bảng số KW định mức SỐKW: Số KW tiêu thụ A B C D ĐM: Số KW định mức CQ 50 60 70 75 TĐM: Số KW tiêu thụ trong định mức CB 40 42 55 65 NĐM: Số KW tiêu thụ ngoài định mức CN 20 50 70 95 T.TĐM: Số tiền trong định mức KD 60 80 95 120 T.NĐM: Số tiền ngoài định mức NN 30 60 75 85 CỘNG: Tổng số tiền phải trả Bảng đơn giá (trong và ngoài định mức) CB CQ CN KD NN Chú thích: TĐM 1200 2300 5400 5500 3000 TĐM: Đơn giá trong định mức NĐM 1000 1800 4500 4000 2500 NĐM: Đơn giá ngoài định mức Bài thực hành số 7 KẾT QUẢ KỲ THI TIN HỌC KHOÁ NGÀY ....../....../200.... STT HỌ TÊN NĂM SINH QUÊ QUÁN ĐLT ĐTH ĐTBKT T.BÌNH VỊ THỨ GHI CHÚ 1 Nguyễn Văn A 1985 Đà Nẵng 3 6 5 2 Trần Văn B 1968 Quảng Nam 7 2 7 3 Lương Sơn Bá 1974 Quảng Ngãi 5 7 5 4 Chúc Anh Đài 1972 Huế 3 6 7 5 Vũ Như Cẩn 1977 Bình Định 4 4 5 6 Nguyễn Y Vân 1958 Quảng Ngãi 7 2 3 7 Wright York 1971 Tobaco&Trinida 4 8 7 8 Trần Bình Trọng 1968 Quảng Ngãi 8 9 7 9 Zidine Zidane 1972 France 6 9 4 10 David Beckham 1973 England 7 9 4 Yêu cầu: 1. Trình bày bảng tính trên. 2. Tính điểm trung bình cho các học viên: T.BÌNH=(ĐLT+ĐTH+ĐTBKT)/3, làm tròn số với 2 số lẻ ở phần thập phân. 3. Xếp hạng cho lớp theo dạng [Vị thứ] / [Tổng số]. Ví dụ : 1/10, 5/10. 4. Nếu các học viên có cùng vị thứ với nhau thì cột GHI CHÚ phải ghi chú là Đồng hạn. 5. Sắp xếp danh sách học viên theo thứ tự tăng dần của Năm sinh, nếu trùng thì theo thứ tự giảm dần của cột T.BÌNH. 6. Dùng Data\Filter\Advanced Filter để lọc trích ra danh sách những học viên ở Quảng Ngãi. Bảng kết quả sau khi thực hiện các yêu cầu trên STT HỌ TÊN NĂM SINH QUÊ QUÁN ĐLT ĐTH ĐTBKT T.BÌNH VỊ THỨ GHI CHÚ 1 Nguyễn Văn A 1985 Đà Nẵng 3 6 5 4.67 8/10 2 Trần Văn B 1968 Quảng Nam 7 2 7 5.33 6/10 Đồng hạn 3 Lương Sơn Bá 1974 Quảng Ngãi 5 7 5 5.67 5/10 4 Chúc Anh Đài 1972 Huế 3 6 7 5.33 6/10 Đồng hạn 5 Vũ Như Cẩn 1977 Bình Định 4 4 5 4.33 9/10 6 Nguyễn Y Vân 1958 Quảng Ngãi 7 2 3 4.00 10/10 7 Wright York 1971 Tobaco&Trinida 4 8 7 6.33 3/10 Đồng hạn 8 Trần Bình Trọng 1968 Quảng Ngãi 8 9 7 8.00 1/10 9 Zidine Zidane 1972 France 6 9 4 6.33 3/10 Đồng hạn 10 David Beckham 1973 England 7 9 4 6.67 2/10 QUÊ QUÁN Đây là vùng điều kiện để lọc trích(Criteria) Quảng Ngãi Kết quả sau khi lọc trích STT HỌ TÊN NĂM SINH QUÊ QUÁN ĐLT ĐTH ĐTBKT T.BÌNH VỊ THỨ GHI CHÚ 3 Lương Sơn Bá 1974 Quảng Ngãi 5 7 5 5.67 5/10 6 Nguyễn Y Vân 1958 Quảng Ngãi 7 2 3 4.00 10/10 8 Trần Bình Trọng 1968 Quảng Ngãi 8 9 7 8.00 1/10 Bài thực hành số 8 BÁO CÁO KINH DOANH KHO VẬT TƯ STT HỌ & TÊN CHỨNG TỪ TÊN VẬT TƯ SỐ LƯỢNG ĐƠN GIÁ THÀNH TIỀN 1 A X01C 50 2 B D02K 1050 3 C N03C 150 4 D X04C 2550 5 E D05K 250 6 F G06C 320 7 G D08K 120 8 H G08C 240 9 I X01C 300 10 J N09K 125 TỔNG CỘNG TRUNG BÌNH BẢNG PHỤ 1 MÃ CT D G N X TÊN VẬT TƯ DẦU LỬA GASOL NHỚT XĂNG ĐƠN GIÁ 120 140 130 100 BẢNG PHỤ 2 SCT THUẾ D 70% G 80% N 90% X 95% Lưu bảng tính trên với tên Kinhdoanhvattu 1. Xác định Tên vật tư, Đơn Giá thông qua bảng phụ 1 2. Tính cột Thành Tiên:=Số lượng*Đơn giá*Thuế 3. Tính Tổng Cộng và Trung Bình cho các cột. 4. Sắp xếp bảng tính theo thứ tự tăng dần của cột thành tiền 5. Vẽ đồ thị dạng 3D Pie cho cột số lượng (theo cột họ tên) 6. Tạo bảng dữ liệu(Lọc) gồm các mẫu tin có tên vật tư là Dầu lửa có số lượng bán ra lớn hơn 200 7. Lập bảng thống Kê: Tổng số tiền bán được của các mặt hàng Dầu lửa Xăng Nhớt Bài thực hành số 9 BẢNG BÁO CÁO KINH DOANH XĂNG TRAM XĂNG DẦU 1/5 KD CC XUẤT 3500 2500 NHẬP 3300 1200 STT K-HÀNG SỐ-CT SỐ-LG TIỀN CHI TIỀN THU T-NHẬP T-XUẤT 1 A N100C 2 B X2000K 3 C X120K 4 D N230C 5 E X1200C 6 F N2230K 7 G N1000C 8 H X200K 9 I X300K 10 J N1200K 11 K X2000K 12 L X120K 13 M N2000C 14 N N300K 15 O X1220K TỔNG SỐ Diễn Giải: - Ký tự đầu tiên của số chứng từ cho biết hàng xuất hay nhập: + "X" là xuất, "N" là nhập - Ký tự cuối cùng cho biết hàng xuất(nhập) theo giá cung cấp hay kinh doanh + "K":kinh doanh, "C": cung cấp - Các ký tự còn lại cho biết số lượng hàng xuất hay nhập Yêu cầu: 1. Tính(a): Số lượng hàng xuất, nhập 2. Tính tiền chi và tiền thu: Số lượng * Đơn giá + Tiền chi là tiền nhập hàng hóa và tiền thu là tiền xuất hàng hóa + Ký tự cuối cùng cho biết tính theo giá kinh doanh hay cung cấp 3. Tính thuế nhập và thuế xuất biết: + Nếu số lượng Nhập theo giá Cung cấp lớn hơn 800 thì phải nộp thuế là 7% Tiền chi, ngược lại 10% Tiền chi + Nếu số lượng Xuất theo giá Kinh doanh lớn hơn 800 thì phải nộp thuế là 5% Tiền thu, ngược lại 10% Tiền thu 4. Tính tổng của các cột 5. Lưu bảng tính với tên tenhocvien_9 6. Định dạng cột tiền có dấu phần cách phần nghìn và theo đơn vị tiền tệ "Đồng" Bài thực hành số 10 TÍNH TIỀN Ở KHÁCH SẠN STT HỌ VÀ TÊN MÃ SỐ NGÀY ĐẾN NGÀY ĐI SNGAY TUẦN NGÀY T.NGÀY T.TUẦN 1 Nguyễn Văn Hay A1 4/12/2001 25/04/2001 100000 2 Trần Tự Lực B2 5/23/2001 12/06/2001 120000 3 Nguyễn Thị Mai Lê A1 3/14/2001 16/04/2001 100000 4 Phan Thanh Thùy Linh C2 6/16/2001 20/06/2001 160000 5 Lê Thị Tuyết Mai A2 5/1/2001 30/05/2001 100000 6 Trấn Vân Quỳnh B1 5/2/2001 14/05/2001 120000 7 Dương Cao Anh Dương C2 6/12/2001 26/06/2001 160000 8 Trần Minh Hảo C1 6/10/2001 17/06/2001 160000 9 Lê Văn Phận B3 6/16/2001 24/06/2001 120000 Tổng cộng BẢNG 1:TIỀN NGÀY BẢNG 2:TIỀN TUẦN Phòng đơn Phòng đôi Phòng đơn Phòng đôi A 100000 150000 A 600000 900000 B 120000 170000 B 720000 1020000 C 160000 180000 C 960000 1080000 Yêu cầu: 1. Nhập và trình bày bảng tính 2. Tính cột số ngày: Ngày đi - Ngày đến 3. Dùng hàm INT() Tính số tuần ở khác sạn của khách cho cột Tuần (13 ngày = 1 tuần + 6 ngày) 4. Dùng hàm MOD() Tính số ngày lẻ ở khách sạn của khách cho cột Ngày 5. Tính cột T.Ngày (Tiền ngày) dựa vào Mã số và Bảng 1, số 1 bên phải chỉ phòng đơn Số 2 chỉ phòng đôi 6. Tính tiền tuần dựa vào mã số và Bảng 2 7. Cột Tổng tiền := Tiền Tuần + Tiền Ngày Bài thực hành số 11 SỬ DỤNG HÀM MATCH VÀ INDEX HÃY NHẬP BẢNG TÍNH SAU Ngày Nơi Đi Nơi Đến Cự Ly Đơn Giá Thành Tiền 05/03/2003 Đà Nẵng Tam Kỳ 06/05/2003 Tam kỳ Huế 12/05/2003 Sài Gòn Đà Nẳng 17/06/2003 Đà Nẵng Đà Nẳng 20/06/2003 Quảng Ngãi Đà Nẳng 25/06/2003 Hội An Huế Bảng Cự Ly Nơ Đi/Nơi Đến Đà Nẵng Tam Kỳ Sài Gòn Hội An Huế Đà Nẵng 10 74 1000 30 100 Tam Kỳ 74 10 960 44 144 Sài Gòn 1000 960 30 970 1100 Hội An 30 44 970 5 130 Huế 100 144 1100 130 15 Quảng Ngãi 135 60 900 104 204 Yêu Cầu 1. Điền dữ liệu cho cột cự ly 2. Tính đơn giá theo yêu cầu sau - Cự ly <100 Km: Đơn giá là 3.000 đồng - 100 <= cự ly < 500 Km: Đơn giá là 2.500 đồng - Cự ly >=100 Kmlà 2.200 đồng 3. Tính thành tiền =Cự ly * Đơn giá 4. Tính Tổng cộng cho cột thành tiền 5. Hãy chèn thêm một cột tháng vào sau cột ngày. Sau đó, dựa vào số liệu cột ngày để điền dữ liệu cho cột tháng. 6. Tính thống kê Tổng tiền của từng tháng 7. Lọc danh sách cho những chuyến có Nơi đi hoặc Nơi đến là Đà Nẵng Bài thực hành số 12 BÁO CÁO DOANH THU THÁNG 10/2002 STT MÃ VT TÊN VT NƠI BÁN SỐ LG TRỊ GIÁ CƯỚC PHÍ HUÊ HỒNG THÀNH TIỀN 1 S001L HN 200 2 C002S CT 150 3 X003S SG 250 4 G004L VT 500 5 S005L HN 260 6 S006S SG 368 7 C007L CT 640 8 X008L HN 100 9 G009S VT 680 10 S010L SG 420 TỔNG CỘNG PHỤ BẢNG 1: MÃ VT TÊN VT GIÁ SỈ (S) GIÁ LẺ(L) C CÁT 40000 45000 G GẠCH 1800 2000 S SẮT 3000 3500 X XI MĂNG 75000 90000 PHỤ BẢNG 2 NƠI BÁN CT HN SG VT ĐƠN GIÁ BÁN 1000 600 200 800 YÊU CẦU : 1.Tên vật tư : Dựa vào lý tự đầu tiên của cột mã và bảng phụ 1 2. Trị giá:số lượng * đơn giá (đơn giá được dò đến bảng phụ 1 ,kí tự cuối của mã vt là "S" là giá sỉ ; "L" là giá lẻ) 3. Cột cước phí dựa vào Nơi bán và bảng phụ 2 4. Tính huê hồng : Nếu số lượng >=500 thì huê hồng =5% trị giá ngược lại huê hồng =0 5. Tính cột thành tiền 6. Lập bảng thống kê: Tên hàng Cát Gạch Sắt Xi măng Tổng doanh thu 7. Dựa vào bảng thống kê vẽ đồ thị biểu diễn doanh thu cho từng vật tư 8. Sắp xếp thứ tự theo mã vt 9. Kẻ khung và lưu trữ ----Hết phần thực hành---- Chúc bạn thành công!