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

Trường Đại học Tài Chính - Marketing


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:
Đổi tên Sheet 1 thành Họ tên của sinh viên
Điền tên cột Phòng Ban là 2 ký tự đầu của Mã NV
Đ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
Điền số năm công tác là 2 ký tự cuối của Mã NV
Tính Hệ số Phụ cấp: Số năm cơng tác * 1.5
Tính Tổng phụ cấp = Hệ số phụ cấp x 300.000
Tính ơ tổng cộng của cột Tổng phụ cấp
Đị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
1.
2.
3.
4.

5.
6.
7.
8.

1


Bài E2. Hoàn thành báo cáo sau đây, biết rằng:
 Ký tự đều tiên của mã cơ quan cho biết khu vực của cơ quan
 Vượt định mức được tính như sau: Nếu Số Kw tiêu thụ > Định mức thì Vượt định mức = Số
Kw tiêu thụ - Định mức, ngược lại khơng có vượt định mức.
 Nếu hộ gia đình nào nộp tiền sau hạn cuối thanh tốn sẽ bị cắt điện.

Yêu cầu:
1.
2.
3.
4.
5.
6.
7.

Điền cột định mức tiêu thụ dựa vào Bảng 1
Tính Vượt định mức.
Tính thành tiền = Số Kw tiêu thụ x 1.350 + vượt định mức x 1.500.
Tính thu = Thành tiền + Phụ thu.
Lập cơng thức cho cột Ghi chú
Tính Tổng thu cho từng khu vực cho bảng thống kê.
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.


19


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:

20


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í chun 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.

21


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.

22


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.
5.
6.
7.
8.

Thuế VAT: 10% giá trị đơn hàng
Thành tiền: Số lượng x đơn giá + thuế VAT
Điền giá trị cho bảng 3
Định dạng dấu phân cách hàng ngàn cho các cột số tiền (0.000 đ)
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.

23


Bài 7. Hoàn thành bảng lương tháng 01/2015, biết rằng:
 Mã nhân viên: 2 ký tự đầu cho biết phòng ban làm việc, ký tự thứ 3 cho biết chức vụ.
 Lương = L-ngày * Ng-công
 Thưởng = (Tổng quỹ lương/Tổng số ngày công) * Ng-cô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.

24


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.
2.
3.
4.
5.

Trị giá = đơn giá * số lượng
Hoa hồng: nếu số lượng > 3, thì hoa hồng = 5% trị giá
Tổng tiền = Trị giá + Tiền cước - Hoa hồng
Điền giá trị cho bảng doanh thu
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.

25


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.

26


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.

27


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 Tố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đ)
11. Lập Bảng dữ liệu và vẽ biểu đồ dạng Bar (Clustered Horizontal Cone) so sánh số thí sinh
Đậu, Rớt (0.5đ).
28


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:\

29


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.
6. Tắt chế độ lặp AutoComplete khi nhập dữ liệu cho các ô.
7. Thay đổi đường dẫn tự động lưu trữ phục hồi (AutoRecover) của Excel là ổ đĩa C:\
8. Thiết lập tùy chọn in lặp lại tiêu đề của dòng 1.
30


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.

31


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

Yêu cầu:
1.
2.
3.
4.
5.
6.

Đổi tên sheet thành Họ tên sinh viên
Tô nền cho dòng tiêu đề báo cáo
Dựa vào mã chức vụ điền giá trị cột chức vụ, lương căn bản
Dựa vào bảng 2, điền giá trị cột tiền thưởng
Tính thực lãnh = Lương CB + Thưởng
Đị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 đ)


32


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
 Dò tìm trong Bảng tên hàng & Đơn giá điền giá trị cột Tên hàng
 Điền giá trị số thùng và số lon lẽ, biết rằng 1 thùng có 24 lon
 Điền giá trị thành tiền. Thành tiền = (24 * ĐG_Sỉ * số thùng) + (ĐG_Lẻ * Số lon lẻ)
 Điền giá trị chuyên chở. Chuyên chở = phí chuyên chở * Thành tiền
 Điền giá trị tổng cộng. Tổng cộng = Thành tiền + Chuyên chở
Câu 3: Định dạng bảng in như sau:
 In giấy A4, in giấy ngang, khoảng cách lề: top = bottom = left = right = 0.5 inch, footer =
0.3 inch
 Đánh số thứ tự trang ở cuối trang, canh lề giữa cho số thứ tự trang
 Canh lề bảng báo cáo chính giữa trang in
 Định dạng lập lại dòng tiêu đề khi in sang trang mới
 Thiết lập khu vực in: chỉ in bảng chi tiết xuất hàng (A2:A14), không in bảng thống kê.
33