Bài thực hành MS Excel

lOMoARcPSD|35973522 Bài tập 1:
BẢNG THANH TOÁN LƯƠNG + THƯỞNG Tháng 8 năm 2014 Hệ số 20 Mã Ngày Mức nhân viên Họ tên Chức vụ Phụ cấp chức vụ công 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 ? ? Tổng cộng ? ? ? ? Yêu cầu: A) Tính toán
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
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
Downloaded by Di?p ??ng Ng?c (tanphatthpt@gmail.com) lOMoARcPSD|35973522
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:
BẢNG LƯƠNG THÁNG 9 NĂM 2014  LCB 350000 STT MANV HỌ VÀ TÊN 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
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ó.
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
Downloaded by Di?p ??ng Ng?c (tanphatthpt@gmail.com) lOMoARcPSD|35973522
Bài tập 3: 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:
Kết Quả Thi Học Kỳ I Năm Học 2013 - 2014 STT MAHS HỌ VÀ TÊN TOÁN LÝ HOÁ ĐT ĐTB XẾP LOẠI 1 01MN Trần Nhân Tông 8 8 9 ? ? ? 2 02NT Lê Thanh Thuỷ 6 7 5 ? ? ? 3 01NT Lê Minh Trí 9 8 10 ? ? ? 4 02MN Nguyễn Thành Vinh 4 6 5 ? ? ? 5 03BT Phan Thanh Thảo 5 7 6 ? ? ? 6 04MN Lê Công Minh 8 7 7 ? ? ? 7 03BT Nguyễn Anh Tuấn 7 6 6 ? ? ? Yêu cầu: A) Tính toán
1. Điểm thưởng (ĐT): = 1 nếu hai ký tự cuối của MAHS là "MN"
= 0.5 nếu hai ký tự cuối của MAHS là "NT" = 0 nếu là "BT"
2. ĐTB: = (Toán * 3 + Lý * 2 + Hóa * 2) / 7 + ĐT, làm tròn đến 1 số lẻ
3. Xếp loại: = "Giỏi" nếu ĐTB >= 9
= "Khá" nếu ĐTB >= 7
= "T Bình" nếu ĐTB >= 5
= Yếu" nếu ĐTB khác các trường hợp trên
B) Định dạng bảng tính như trong bài tập
C) Đổi tên Sheet thành: If-Round-Right D) Lưu tệp tin
Downloaded by Di?p ??ng Ng?c (tanphatthpt@gmail.com) lOMoARcPSD|35973522
Bài tập 4: 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:
BẢNG THANH TOÁN LƯƠNG THÁNG 7/2014 -------o0o------- L
CÁC KHOẢN KHẤU TRỪ STT HỌ VÀ TÊN ƠNG HS KÝ
NGÀY CÔNG TẠM ỨNG BHXH BHYT KHÁC CÒN LẠI NHẬN LƯ 1 Trần Văn Anh 4 24 2 Trương Văn Hiệp 5 25 3 Lê Hữu Đức 4 25 4 Phan Bảo 7 22 5 Trần Thanh Bảo 2 24 6 Võ Hoàng Nam 3 20 7 Nguyễn Duy Linh 6 25 8 Trương Văn Minh 4 12 9 Nguyễn Đình Bán 5 24 10 Lê Quang Minh 5 24 11 Lê Hoàng Nhân 3 25 12 Thái Văn Thanh 3 26 13 Hoàng Dục 3 23 14 Trần Hữu Hạnh 4 27 Tổng cộng Yêu cầu:
A) Bổ sung: Chèn thêm 1 cột có tên "Lương " vào bên phải cột Ngày công B) Tính toán
1. Lương: = HSL nhân Ngày công nhân 200000 chia 6, làm trong đến hàng nghìn
2. Tạm ứng: = HSL nhân 200000 chia 3
3. BHXH: = 5%Lương
4. BHYT: = 1%Lương
5. Lương còn lại: = Lương trừ các khoản khấu trừ
6. Tính tổng của các cột có tô nền
7. Sắp xếp bảng tính theo Tên
C) Định dạng bảng tính như trong bài tập, cột Lương còn lại định dạng theo tiền ĐVN
D) Đổi tên Sheet thành: SX-Định dạng ĐVN E) Lưu tệp tin
Downloaded by Di?p ??ng Ng?c (tanphatthpt@gmail.com) lOMoARcPSD|35973522
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:
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 Á ĐIỂM VỊ STT HỌ VÀ TÊN ÁN O PHÁI LÝ TO H HỆ SỐ 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
1. Điểm hệ số: = Toán nhân 2 cộng Lý cộng Hóa
2. Xếp hạng: Căn cứ vào cột Điểm hệ số để xếp hạng
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
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
Downloaded by Di?p ??ng Ng?c (tanphatthpt@gmail.com) lOMoARcPSD|35973522
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:
BẢNG THANH TOÁN LƯƠNG THÁNG 8 NĂM 2014
STT Mã NV Họ tên Chức vụ HSL Phụ cấp Lương Bảo hiểm Tiền lĩnh BẢNG PHỤ CẤP 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 Tổng cộng Yêu cầu: A) Tính toán
1. Phụ cấp: Dùng hàm Vlookup tham chiếu Bảng phụ cấp để tính
2. Lương: = HSL nhân 120000
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
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
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
Downloaded by Di?p ??ng Ng?c (tanphatthpt@gmail.com) lOMoARcPSD|35973522
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:
Trường Đại học Sư phạm Đà Nẵng
Kết quả thi học kỳ 1 năm học 2013 - 2014  SỐ HỌ VÀ TÊN CHỨC TT THÍ SINH 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 Cao nhất ? ? ? ? ? Trung bình ? ? ? ? ? Thấp nhất ? ? ? ? ? Bảng Kết Quả
TỔNG HỢP SỐ LIỆU 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
1. ĐTB: = (THĐCC * 4 + TCC * 3 + ANH * 2 + LTĐT * 3)/12, làm tròn đến 2 số lẻ
Downloaded by Di?p ??ng Ng?c (tanphatthpt@gmail.com) lOMoARcPSD|35973522
2. ĐKQ: = ĐTB + 1 nếu CHỨC VỤ là LT hoặc BT
= ĐTB + 0.5 nếu CHỨC VỤ là LP
= ĐTB nếu là TV
3. KẾT QUẢ: Căn cứ cột ĐKQ và tham chiếu Bảng kết quả để tính
4. Tính giá trị cao nhất, trung bình và thấp nhất cho các cột có dấu ?
5. Lập bảng TỔNG HỢP SỐ LIỆU theo kết quả
B) Định dạng bảng tính như trong bài tập
C) Đổi tên Sheet thành: Vlookup-Countif D) Lưu tệp tin
Downloaded by Di?p ??ng Ng?c (tanphatthpt@gmail.com) lOMoARcPSD|35973522 Bài tập 8: LCB 350000 Phụ cấp Ngày MNV Họ Tên Phòng Chức vụ chức vụ công HSL Lương Tiền lĩnh H01C Công GĐ 23 4.2 H01C Cha PGĐ 24 4 Đ02T Như TP 25 4 N03V Núi NV 10 2.9 Đ02T Thái PP 20 3.5 H02C Sơn KT 30 3.5 Tổng cộng Bảng PCCV
BẢNG TỔNG HỢP SỐ LIỆU GĐ PGĐ TP PP KT NV Phòng
Số người Tiền lĩnh 500000 400000 400000 300000 300000 0 HC ĐT NV Yêu cầu: A) Tính toán
1. Phòng: = "HC" nếu ký tự đầu của MNV là "H" và ký tự cuối là "C"
= "ĐT" nếu ký tự đầu của MNV là "Đ" và ký tự cuối là "T" = "NV" còn lại
2. Phụ cấp chức vụ: Căn cứ vào BẢng PCCV ở trên để tính
3. Lương: = HSL * LCB * Ngày công
4. Tiền lĩnh: = Phụ cấp chức vụ + Lương
Downloaded by Di?p ??ng Ng?c (tanphatthpt@gmail.com) lOMoARcPSD|35973522
5. Sắp xếp bảng tính theo cột Phòng
6. Lập Bảng TỔNG HỢP SỐ LIỆU ở trê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ệ 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
Downloaded by Di?p ??ng Ng?c (tanphatthpt@gmail.com) lOMoARcPSD|35973522 Bài tập 9: TÊN KHÁCH NGÀY NGÀY PHÒNG SỐ ĐƠN GIÁ THÀNH STT HÀNG ĐẾN ĐI
LOẠI TẦNG NGÀY PHÒNG TIỀN 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 Tổng cộng BẢNG GIÁ BẢNG HỆ SỐ 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
Downloaded by Di?p ??ng Ng?c (tanphatthpt@gmail.com) lOMoARcPSD|35973522 Yêu cầu: A) Tính toán
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
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
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
Downloaded by Di?p ??ng Ng?c (tanphatthpt@gmail.com) lOMoARcPSD|35973522 Bài tập 10: XH XH STT HỌ
TÊN NGÀY SINH NƠI SINH NỮ LỚP TRƯỜNG MÔN ĐIỂM XH
Môn Trường Trường - Môn 1 Huỳnh Tiến Bửu 28-03-90 TP.HCM 11A8 Bùi Thị Xuân Toán 9.6 2 Nguyễn Đạt Thịnh 18-03-90 TP.HCM 11A5 Bùi Thị Xuân Toán 0.1 3 Nguyễn Thị Thu Hằng 03-05-87 TP.HCM X 12A6 Bùi Thị Xuân Văn 0.9 4 Nguyễn Tiến Anh 06-07-89 TP.HCM 12A2 Bùi Thị Xuân Anh 3.6 5 Trần Ngọc Tươi 01-01-89 TP.HCM 12A1 Bùi Thị Xuân Anh 1.5 6 Nguyễn Khoa Huân 11-06-90 Ninh Thuận 11A1 Bùi Thị Xuân Toán 7.6 7 Mai Trọng Nghĩa 11-05-89 TP.HCM 12A1 Bùi Thị Xuân Văn 2.9 8 Nguyễn Phúc Long 16-08-89 TP.HCM 12A7 Bùi Thị Xuân Văn 8.2 9 Phạm Ngọc Tuấn 02-08-89 Lạng Sơn 12A1 Lê Độ Toán 9.6 10 Võ Hà Minh Thương 06-04-89 TP.HCM X 12A1 Lê Độ Văn 6.5 11 Châu Ngọc Đức 30-03-89 TP.HCM 12A1 Lê Độ Toán 9.3 12 Bùi Quang Thục Anh 01-11-89 TP.HCM X 12A1 Lê Độ Văn 1.4 13 Ngô Thị Ngọc Nữ 15-10-88 TP.HCM X 12A10 Củ Chi Toán 7.1 14 Trần Thị Thu Thảo 20-11-89 TP.HCM X 12a11 Củ Chi Toán 7.1 15 Lê Đức Anh 04-09-89 Đồng Nai 12A10 Củ Chi Toán 1.7 16 Nguyễn Lê Thu Cúc 12-09-89 TP.HCM X 12A1 Gia Định Toán 2.4 17 Nguyễn Thị Thanh Thảo 12-03-90 TP.HCM X 11C Gia Định Toán 5.3 18 Lương Thanh Anh Đức 16-03-89 Tp.HCM 12A1 Gia Định Toán 9.0 19 Nguyễn Văn Anh Việt 21-02-90 Tp.HCM 11A5 Gia Định Anh 5.3 20 Nguyễn Thị Ngọc Hiếu 11-08-88 TP.HCM X 12A4 Gia Định Văn 1.6 21 Trịnh Thị Thắm 15-11-89
Hải Dương X 12A5 Gia Định Văn 4.5 22 Đặng Thị Mỹ Dung 05-10-89 TP.HCM X 12A5 Gia Định Văn 7.8 23 Trần Thụy Đan Thảo 29-03-90 TP.HCM X 11A4 Gia Định Anh 6.3 24 Phạm Thái Bảo Ngọc 01-07-90 TP.HCM X 11A4 Gia Định Anh 8.4
25 Nguyễn Ngọc Thành Nam 11-02-89 Ninh Bình 12A1 Gia Định Toán 4.6 26 Đặng Trần Tấn Khoa 22-05-90 TP.HCM 11C Gia Định Văn 8.0
Downloaded by Di?p ??ng Ng?c (tanphatthpt@gmail.com) lOMoARcPSD|35973522 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
1. Thực hiện BẢNG THỐNG KÊ sau: BẢNG THỐNG KÊ
Tổng số thí sinh trường Gia Định
Số thí sinh trường Gia Định thi môn Văn
Số thí sinh trường Gia Định thi môn Văn dưới 5 điểm
Điểm trung bình cộng môn Toán của trường Gia Định
Số thí sinh dự thi đông nhất của cùng 1 trường
2. Căn cứ vào ĐIỂM để xếp hạng học sinh theo MÔN, TRƯỜNG, TRƯỜNG-MÔN; đồng điểm và đồng môn thì đồng hạng.
B) Định dạng bảng tính như trong bài tập
C) Đổi tên Sheet thành: Array 1 D) Lưu tệp tin
Downloaded by Di?p ??ng Ng?c (tanphatthpt@gmail.com) lOMoARcPSD|35973522 Bài tập 11:
KẾT QUẢ HỌC TẬP LỚP SPT XSTK PPNCKH HĐH MMT C g STT Họ và tên Số tín chỉ ĐRL TBC Học bổn 3 1 3 3 Số T 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
1. Tính Số TC của mỗi sinh viên
2. Tính ĐTB của mỗi sinh viên
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 Bảng thống kê
Số sinh viên học lực yếu
Số sinh viên có học bổng
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
Downloaded by Di?p ??ng Ng?c (tanphatthpt@gmail.com) lOMoARcPSD|35973522
TỔNG CÔNG TY BẢO HIỂM VIỆT NAM Bài tập 12:
NGHIỆP VỤ PHÁT SINH QUÝ II -----o0o----- 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
BẢNG DOANH THU BẢO HIỂM xe máy xe ô tô y tế Số khách Tổng thu Tổng chi Lợi nhuận THỐNG KÊ CHI XE MÁY 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
Downloaded by Di?p ??ng Ng?c (tanphatthpt@gmail.com) lOMoARcPSD|35973522 Bài tập 13:
BẢNG TÍNH TIỀN THUÊ XE Stt Khách hàng Loại xe Ngày thuê Ngày trả
Số ngày Đơn giá Thành tiền Bảng giá 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 Tổng Cộng Yêu cầu: A) Tính toán
1. Hãy lập công thức mảng để tính Số ngày thuê
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. Tính tổng cho các cột Số ngày và Thành tiền
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 thống kê 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
Tên khách hàng trả tiền thuê nhiều nhấ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
Downloaded by Di?p ??ng Ng?c (tanphatthpt@gmail.com) lOMoARcPSD|35973522 Bài tập 14:
Mức học phí cho 1 TC 120000 Học phí Học phí Học phí TT Họ và tên TCC
THĐC TLH PPNCKH HĐC Số TC
đăng ký phải đóng đã đóng còn nợ Kết luận BẢNG HỆ SỐ 1 Nguyễn Tuấn Anh X X X X 1 ,000,000 TCC 3 2 Trần Hoàng Bình X X X X 1 ,080,000 THĐC 2 3 Trương Thị Phương Chi X X X X 7 00,000 TLH 3 4 Mai Hùng Cường X X X X 1 ,320,000 PPNCKH 1 5 Nguyễn Thị Hạnh Dung X X X X 1 ,080,000 HĐC 3 6 Nguyễn Duy Hậu X X X X 1 ,080,000 7 Hoàng Đức Hiển X X X X 1 ,080,000 8 Thái Bá Hoàng X X X X 8 00,000 9 Nguyễn Thị Hồng X X X 9 60,000 10 Nguyễn Thị Thu Hồng X X X X 1 ,080,000 11 Lê Quang Hồng X X X X 1 ,080,000 12 Phan Vĩnh Hương X X X X 1 ,000,000 13 Nguyễn Hoàng Khải X X X X 1 ,080,000 14 Huỳnh Nguyễn Nhật Khánh X X X X 1 ,080,000 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
1. Tính Số TC đăng ký của sinh viên (ô có dấu X là đăng ký)
2. Tính Học phí đã đóng cho thí sinh = Số TC đăng ký * Mức học phí cho 1 TC
3. Tính Học phí còn nợ của mỗi sinh viên
4. Căn cứ Học phí còn nợ để Kết luận, những sinh viên còn nợ học phí thì không được thi. B) Sort & Filter
1. Định dạng cột Kết luận với điều kiện chữ "KĐT" có màu đỏ
2. Sắp xếp bảng giảm dần theo cột Học phí đã đóng, nếu trùng thì sắp xếp theo tên.
3. Thực hiện chức năng AutoFilter để xem danh sách thí sinh còn nợ học phí
Downloaded by Di?p ??ng Ng?c (tanphatthpt@gmail.com) lOMoARcPSD|35973522
4. Lọc những sinh đã đóng học phí từ 1,000,000 đến 1,200,000
5. Xóa điều kiện lọc của các cột
C) Định dạng bảng tính như trong bài tập
D) Đổi tên Sheet thành: Array-Sort & Filter 1 E) Lưu tệp tin
Downloaded by Di?p ??ng Ng?c (tanphatthpt@gmail.com) lOMoARcPSD|35973522 Bài tập 15:
KẾT QUẢ THI TUYỂN SINH KHÓA 2014 Diện Điểm Tổng Kết SBD Họ và
tên ưu tiên Toán Lý Hóa Sinh ưu tiên điểm quả ĐIỂM CHUẨN 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 ĐIỂM ƯU TIÊN 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
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 B) Sort & Filter
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 đỏ
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
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
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
Downloaded by Di?p ??ng Ng?c (tanphatthpt@gmail.com) lOMoARcPSD|35973522 Bài tập 16: Giá Thành tiền STT
Loại quả Số lượng Ngày bán (KG) (USD) (USD) 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) 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) Định dạng bảng tính như trong bài tập
C) Hãy thực hiện các thống kê sau:
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
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
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
Downloaded by Di?p ??ng Ng?c (tanphatthpt@gmail.com) lOMoARcPSD|35973522 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 BẢNG GIÁ 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) 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) Định dạng bảng tính như trong bài tập
C) 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
D) Đổi tên Sheet thành: Pivot Table 2 E) Lưu tệp tin
Downloaded by Di?p ??ng Ng?c (tanphatthpt@gmail.com) lOMoARcPSD|35973522 Bài tập 18: A)
Vẽ biểu đồ hình cột để minh hoạ cho các số liệu sau:
KẾT QUẢ HỌC TẬP CÁC KHÓA
KẾT QUẢ HỌC TẬP CÁC KHÓA KHÓA
GIỎI (%) KHÁ (%) T.BÌNH (%) YẾU (%) 60 % K2008 K2008 10 35 40 15 50 ỉ lệT K2009 K2009 15 40 35 10 40 K2010 K2010 20 20 50 10 30 K2011 K2011 25 30 40 5 50 20 40 40 40 35 35 30 25 B)
Thực hiện các hiệu chỉnh để có kết quả như đồ thị bên phải 10 20 20 15 15 10 10 10 5 C)
Đổi tên Sheet thành: Do thi 1 0 D)
Hãy xoay chiều biểu diễn của đồ thị GIỎI (%) KHÁ (%) T.BÌNH (%) YẾU (%) E) Lưu tệp tin
Loại tốt nghiệp
Downloaded by Di?p ??ng Ng?c (tanphatthpt@gmail.com) lOMoARcPSD|35973522
Bài tập 19: Cho bảng số liệu sau:
BẢNG THỐNG KÊ ĐỘI NGỦ GIÁO VIÊN TP ĐÀ NẴNG QUẬN GIỚI TÍNH ĐỘ TUỔI THÂM NIÊN CÔNG TÁC NAM NỮ Dưới 30 31-45
Trên 45 1 - 10 Năm 11 - 25 Năm > 25 Năm Hải Châu 344 537 310 450 121 365 430 86 Thanh Khê 534 389 370 425 128 410 460 53 Cẩm Lệ 315 325 320 185 135 350 225 65 Ngũ Hành Sơn 360 727 290 545 252 450 515 122 Sơn Trà 314 938 270 675 307 405 635 212 Liên Chiểu 475 730 345 525 335 390 625 190 Tổng 2342 3646 1905 2805 1278 2370 2890 728 A)
Vẽ đồ thị à thực hiện các thao tác hiệu chỉnh để được các đồ thị sau:
TỈ LỆ GIÁO VIÊN THEO GIỚI TÍNH
TỈ LỆ GIÁO VIÊN THEO GIỚI TÍNH CỦA CỦA 6 QUẬN TỪNG QUẬN iờ 1000 ưg 800 nốS 600 39% NAM Nam 400 61% NỮ 200 Nữ 0 Hải Thanh Cẩm Ngũ Sơn Liên Châu Khê Lệ Hành Trà Chiểu Sơn
Downloaded by Di?p ??ng Ng?c (tanphatthpt@gmail.com) lOMoARcPSD|35973522
TỈ LỆ GIÁO VIÊN THEO ĐỘ TUỔI
TỈ LỆ GIÁO VIÊN THEO THÂM NIÊN CỦA TỪNG QUẬN
CÔNG TÁC CỦA 6 QUẬN 800 100% 86 53 65 122 212 190 600 80% iờ 430 460 225 515 400 ư 60% 635 625 g 200 n 40% ốS 350 0 20% 365 410 450 405 390 < 30 Tuổi 31 - 45 Tuổi >45 Tuổi 0% Hải Châu Thanh Cẩm Lệ Ngũ Sơn Trà Liên Hải Châu Thanh Khê Khê Hành Chiểu Cẩm Lệ Ngũ Hành Sơn Sơn Sơn Trà Liên Chiểu 1 - 10 Năm 11 - 25 Năm > 25 Năm B)
Đổi tên Sheet thành: Do thi 2 C) Lưu tệp tin
Downloaded by Di?p ??ng Ng?c (tanphatthpt@gmail.com)
Document Outline

• bt1- if- sum.pdf (p.1)
• bt2- phép toán cơ bản- left.pdf (p.2)
• bt3- round- right.pdf (p.3)
• bt4- SX- định dạng ĐVN.pdf (p.4-5)
• bt5- rank- định dạng ĐK.pdf (p.6)
• bt6- vlookup- logic.pdf (p.7)
• bt7- vlookup- countif.pdf (p.8-9)
• bt8- hlookup- count- sumif.pdf (p.10-11)
• bt9- H-Vlookup.pdf (p.12-13)
• bt10- array 1.pdf (p.14-15)
• bt11- array 2.pdf (p.16)
• bt12- Dfunction.pdf (p.17)
• bt13- array- Dfunction.pdf (p.18)
• bt14- sort & filter.pdf (p.19-20)
• bt15- array sort & filter 2.pdf (p.21)
• bt16- pivot table 1.pdf (p.22)
• bt17- pivot table 2.pdf (p.23)
• bt18- đồ thị 1.pdf (p.24)
• bt19- đồ thị 2.pdf (p.25-26)