Bài Tập EXCEL ÔN THI TIN HỌC ĐẠI CƯƠNG - 123doc

Các dạng bài tập về các hàm trong excel phiên bản 2013. File dành cho các bạn ôn thi tin học đại cương rèn luyện thêm bài tập. Các bài tập đều được đa dạng để ôn được hầu hết tất cả các hàm và thao tác.

BÀI THỰC HÀNH

TIN HỌC ĐẠI CƯƠNG

BIÊN SOẠN: TÔN THẤT HÒA AN

KHOA CÔNG NGHỆ THÔNG TIN

III BÀI TẬP MICROSOFT EXCEL

Bài E1 Tính phụ cấp thâm niên cho nhân viên công ty trong bảng theo các yêu cầu bên dưới

Yêu cầu:

1 Đổi tên Sheet 1 thành Họ tên của sinh viên

2 Điền tên cột Phòng Ban là 2 ký tự đầu của Mã NV

3 Điền cột Chức vụ là 2 ký tự thứ 3 và 4 của Mã NV và đổi thành chữ hoa

4 Điền số năm công tác là 2 ký tự cuối của Mã NV

5 Tính Hệ số Phụ cấp: Số năm công tác * 1.5

6 Tính Tổng phụ cấp = Hệ số phụ cấp x 300.000

7 Tính ô tổng cộng của cột Tổng phụ cấp

8 Định dạng dấu phân cách hàng ngàn và có dấu hiệu tiền tệ cho cột Tổng phụ cấp, ví dụ 1.000 đ

9 Tô màu vàng nhạt cho cột Số năm công tác, màu xanh nhạt cho cột Tổng phụ cấp và màu

đỏ nhạt cho ô Tổng cộng

Bài E2 Hoàn thành báo cáo sau đây, biết rằng:

Kw tiêu thụ - Định mức, ngược lại không có vượt định mức

Yêu cầu:

1 Điền cột định mức tiêu thụ dựa vào Bảng 1

2 Tính Vượt định mức

3 Tính thành tiền = Số Kw tiêu thụ x 1.350 + vượt định mức x 1.500

4 Tính thu = Thành tiền + Phụ thu

5 Lập công thức cho cột Ghi chú

6 Tính Tổng thu cho từng khu vực cho bảng thống kê

7 Từ Bảng thống kê, vẽ biểu đồ thể hiện sự so sánh tổng thu của các khu vực

Bài E3 Hoàn thành báo cáo doanh thu sau đây, biết rằng:

- Mã hàng: ký tự đầu của mã hàng cho biết tên hàng, 2 ký tự kế tiếp cho biết tháng bán hàng,

ký tự cuối cùng cho biết phiếu nhập hoặc xuất

Yêu cầu:

1 Trình bày bảng báo cáo (định dạng số có dấu phân cách hàng ngàn và có ký hiệu tiền tệ (ví

dụ 5.000 đ)

2 Dựa vào mã hàng, điền giá trị cột Tên hàng, Thành tiền Biết rằng: nếu hàng xuất thì tăng đơn giá lên 12%

3 Tính thuế, biết rằng: thuế = tỉ lệ thuế x thành tiền

4 Tính cột Trả trước, biết rằng nếu hóa đơn trên 5 triệu thì trả trước 75%, ngược lại trả trước 50%

5 Tính giá trị Còn lại, biết rằng: Còn lại = Thành tiền - Thuế - Trả trước

6 Thống kê số lượng theo mẫu sau:

Bài E4 Hoàn thành báo cáo doanh thu sau đây, biết rằng:

Mã hàng: ký tự đầu tiên đại diện cho một mặt hàng, ký tự thứ 2 cho biết mặt hàng sẽ được bán theo giá sỉ (S) hay giá lẻ (L), ký tự cuối cùng cho biết khu vực

Yêu cầu:

1 Điền vào cột tên hàng

2 Tính Thành tiền, biết rằng: Thành tiền = số lượng x đơn giá với đơn giá được trích từ bảng

1 dựa vào tên của mã hàng

3 Tính phí chuyên chở Nếu ký tự cuối là 0 thì phí chuyên chở là 0, ngược lại phí chuyên chở tính theo

4 Tính doanh thu, biết rằng doanh thu = thành tiền + chuyên chở

5 Định dạng dấu phân cách hàng ngàn cho cột Thành tiền, cột Chuyên chở và cột Doanh thu (ví dụ: 1.000 đ)

6 Thống kê doanh thu của các mặt hàng đã bán và điền vào bảng 3

Bài E5 Nhập bảng tính như sau:

Yêu cầu:

1 Điền số thứ tự cho cột STT dùng chức năng Autofill (không gõ)

2 Tính giá trị số ngày thuê, biết rằng số ngày thuê = ngày đi - ngày đến

3 Dựa vào loại phòng (2 ký tự cuối của số phòng) và bản giá phòng điền giá trị cột Tiền thuê, Tiền giảm Tiền thuê = giá 1 ngày x số ngày thuê

4 Điền giá trị Tiền phải trả và định dạng ký hiệu tiền tệ VNĐ cho cột này

5 Tại ô F21 điền tổng số người thuê phòng trong ngày 15/05/2015

6 Tại ô F22 điền ổng số phòng loại Premium Deluxe được thuê vào ngày 15/05/2015

7 Tại ô F23 điền Tổng số phòng được trả vào ngày 20/05/2015

8 Tại ô F24 điền Số tiền thu được vào ngày 20/05/2015

Bài E6 Nhập bảng báo cáo sau đây:

Yêu cầu:

1 Tên hàng căn cứ vào bảng 1 và 2 kí tự đầu cho biết nhãn hàng, kí tự thứ 3 cho biết loại hàng Dầu xả (X) hay Dầu gội (G) (VD: SUXU: Dầu xả SunSilk)

2 Nhà cung cấp căn cứ vào bảng 2 và ký tự cuối của mã hàng

3 Đơn giá căn cứ vào 2 kí tự đầu của mã hàng và bảng 1

4 Thuế VAT: 10% giá trị đơn hàng

5 Thành tiền: Số lượng x đơn giá + thuế VAT

6 Điền giá trị cho bảng 3

7 Định dạng dấu phân cách hàng ngàn cho các cột số tiền (0.000 đ)

8 Dựa vào bảng 3 vẽ biểu đồ dạng bánh thể hiện số lượng nhập hàng của từng loại hàng

Bài 7 Hoàn thành bảng lương tháng 01/2015, biết rằng:

Yêu cầu:

1 Dựa vào 2 ký tự đầu và ký tự thứ 3 của Mã NV, điền giá trị cho cột chức vụ, biết rằng nội dung cột này được lấy ra từ bảng Chức vụ & Lương (Ví dụ, Mã NV là KDT01 thì chức vụ

là TP Kinh doanh)

2 Dựa vào ký tự thứ 3 của Mã NV, điền nội dung cột L-Ngày

3 Điền gía trị cho cột Lương, Thưởng, Thực lãnh

Thực lãnh = Lương + Thưởng

4 Tính số liệu cho bảng thống kê thưởng và thực lãnh theo từng phòng ban

Bài E8 Hoàn thành báo cáo doanh thu sau đây, biết rằng:

Nếu khách hàng mua sản phẩm với số lượng từ 5 trở lên thì được 5% huê hồng

Yêu cầu:

1 Trị giá = đơn giá * số lượng

2 Hoa hồng: nếu số lượng > 3, thì hoa hồng = 5% trị giá

3 Tổng tiền = Trị giá + Tiền cước - Hoa hồng

4 Điền giá trị cho bảng doanh thu

5 Dựa vào bảng doanh thu, vẽ đồ thị cho thấy tỷ lệ doanh thu của từng mặt hàng

Bài E9 Hoàn thành báo cáo doanh thu sau đây

Yêu cầu:

1 Điền nội dung cột Tên hàng dựa vào Mã hàng

2 Từ bảng 1, điền số liệu cho cột đơn giá (dựa vào mã hàng và ngày bán)

3 Tính Thành tiền = Số lượng x Đơn giá

4 Tính tổng của cột thành tiền

5 Tính số lượng và doanh thu của từng mặt hàng, kết quả điền vào Bảng 3

6 Vẽ biểu đồ so sánh doanh thu của các mặt hàng đã bán

Bài E10 Nhập bảng báo cáo sau đây:

Yêu cầu:

1 Căn cứ vào 2 ký tự đầu của mã hàng cho biết tên hàng, ký tự thứ 4 cho biết loại hàng, ký tự cuối cho biết nơi chuyên chở

2 Tính đơn giá của các loại hàng dựa vào bảng 1

3 Thêm cột chuyên chở sau cột Số Lượng, tính số tiền chuyên chở dựa trên bảng 2 (đơn giá chuyên chở trên 1 đvt)

4 VAT được tính 10% trên trị giá đơn hàng

5 Thành tiền = Số Lượng * Đơn giá + Thuế VAT + Chuyên chở

6 Định dạng các cột số tiền có dấu phân cách hàng ngàn (0.000)

7 Tính tổng doanh thu của từng loại mặt hàng (theo tên hàng)

8 Sắp xếp dữ liệu theo nơi bán

9 Rút trích những đơn hàng có giá trị từ 15 triệu-20 triệu ra vùng riêng trên bảng tính

10 Định dạng trang in theo giấy ngang

Bài E11 Nhập bảng tính sau đây:

Yêu cầu:

1 Chép bảng tính vào thư mục bài làm (0.25đ)

2 Đổi tên bảng tính với tên STT_HoTenSV_Deso.xls (0.25đ)

3 Dựa vào kí tự thứ nhất của SBD và bảng MÃ NGÀNH hãy ghi ngành thi vào cột NGÀNH (0.5đ)

4 Tính cột TỔNG ĐIỂM là tổng điểm thi của cả 3 môn thi (0.25đ)

5 Dựa vào TỔNG ĐIỂM và BẢNG ĐIỂM CHUẨN, ghi vào cột KẾT QUẢ là "Đậu" nếu đủ điểm đậu, ngược lại ghi "Rớt",

6 biết rằng kí tự thứ 1 trong SBD cho biết NGÀNH, ký tự thứ 2 cho biết ĐỐI TƯỢNG (0.5đ)

7 Tính số thí sinh đậu (0.25đ)

8 Tính số thí sinh có tổng điểm >20 (Bảng thống kê) (0.5đ)

9 Tính số thí sinh có một môn thi Toán <3 (Bảng thống kê) (0.5đ)

10 Tính số thí sinh ngành Vật lý có tổng điểm >18 (Bảng thống kê) (0.5đ)

Bài E12 Cho số liệu như bảng sau:

Yêu cầu:

1 Nhập dữ liệu và lưu tập tin tại thư mục: D:\TenSinhVienLop\

2 Thiết lập ràng buộc (Data Validation) với tùy chọn giá trị nhập vào là kiểu decimal và có giá trị dữ liệu (Data) nằm trong khoảng minimum = 0,0 và maximum = 100.000.000

3 Dùng hàm tính tổng cho từng cột tại dòng tổng cộng

4 Định dạng dấu phân cách và dấu tiền tệ theo mẫu

5 Định dạng styles cho các dòng tiêu đề: CÔNG TY MỸ PHẨM THIÊN HƯƠNG là Heading

1, BÁO CÁO DOANH THU QUÝ 1/2015 LÀ Heading 2

6 Thiết lập tùy chọn lọc dữ liệu cho các cột: TƯ VẤN có giá trị trên VNĐ 10.000,00 và TIẾP THỊ có giá trị trên VNĐ 13.000,00

7 Chuyển dữ liệu thành dạng bảng (Table) với kiểu bảng là Table Style Dark 7 với dòng tiêu

đề trên cùng là các Header

8 Sử dụng chức năng Conditional Formating để định dạng với Red, Yellow, Green Color

Scale của chức năng Color Scales

9 Tắt chức năng điền tự động tại Enable AutoComplete for cell values

10 Thay đổi đường dẫn Default File location là D:\

Bài E13 Nhập bảng dữ liệu sau đây và dùng chức năng Sparkline để hiển thị xu hướng phát

triển của các mặt hàng

Bài E14 Cho số liệu như bảng sau:

Yêu cầu:

1 Lưu tập tin với tên là tên sinh viên

2 Định dạng dấu phân cách cho cột Lương khoán Thiết lập ràng buộc (Data Validation) với tùy chọn giá trị nhập vào là kiểu số và phải lớn hơn hoặc bằng 0 (greater than or equal to)

3 Thiết lập tùy chọn lọc dữ liệu cho cột (Filter) và lọc dữ liệu với phòng ban là Phòng Kinh doanh

4 Chuyển dữ liệu thành dạng (Table) với kiểu bảng là Table Style Light 17

5 Sử dụng chức năng Conditional Formating để định dạng với Blue Data Bar của chức năng Data Bars

Bài E15 Cho số liệu như bảng sau:

Yêu cầu:

1 Lưu tập tin với tên do sinh viên định nghĩa và tiến hành nhập dữ liệu

2 Thiết lập định dạng khung viền như bảng trên

3 Dựa vào số liệu của hình 1 tạo đồ thị dạng Clustered Column như hình 2

4 Thêm tiêu đề cho đồ thị là Above Chart

5 Chuyển đổi cột thành dòng và dòng thành cột của đồ thị

6 Thay đổi kiểu đồ thị (Chart Styles) là style 28

7 Sử dụng chức năng Move Chart để di chuyển đồ thị sang Sheet mới

Bài E16 Tạo tập tin HoTenSinhVien_BàiOnSo1.xlxs có nội dung sau:

Yêu cầu:

1 Đổi tên sheet thành Họ tên sinh viên

2 Tô nền cho dòng tiêu đề báo cáo

3 Dựa vào mã chức vụ điền giá trị cột chức vụ, lương căn bản

4 Dựa vào bảng 2, điền giá trị cột tiền thưởng

5 Tính thực lãnh = Lương CB + Thưởng

6 Định dạng dấu phân cách hàng ngàn và ký hiệu tiền tệ (đ) cho các cột: Lương CB, Thưởng, Thực lãnh (Ví dụ: 5.000.000 đ)

Bài E17 Tạo tập tin HoTenSinhVien_BàiOnSo2.xlxs, thực hiện các yêu cầu sau:

Câu 1: Nhập bảng báo cáo giống mẫu sau:

Câu 2: Hoàn thành bảng báo cáo doanh thu năm 2015

Ý nghĩa mã hóa đơn: 2 ký tự đầu cho biết mã hàng, ký tự cuối cho biết khu vực

Câu 3: Định dạng bảng in như sau:

0.3 inch

