Đề thi Pivot Table nhân dịp Sinh Nhật GPE lần 7
Cho dữ liệu có sẵn trong file đính kèm. Dữ liệu mẫu có 9 cột như sau:
|
A
|
B
|
C
|
D
|
E
|
F
|
G
|
H
|
I
|
1
|
No.
|
Date
|
Customer
|
Product
|
Note
|
Payment Status
|
Sale
|
Contract
|
Paid
|
2
|
1
|
13/04/12
|Café AS|Hàng điện tử|Mua hàng, trả tiền lần 1|Mới|Nhơn|
807.750
|
161.550
|
3
|
2
|
11/07/12
|Fresh|Chén bát đĩa|Mua hàng, trả tiền lần 1|Mới|Khanh|
5.269.000
|
0
|
Ghi chú:
Product = Sản phẩm
Contract = Doanh số, nếu giảm giá, trả hàng, ... thì doanh số âm
Payment = Tiền thu của khách hàng, nếu hoàn tiền cho Kh thì có giá trị âm.
Sale = Tên nhân viên bán hàng
Payment Status = tình trạng hợp đồng, gồm 3 tình trạng theo thời gian: Mới, Chưa hoàn thành, và hoàn thành
hoàn thành = đã hoàn tất hợp đồng, công nợ bằng không.
Câu 1:
Không tạo cột phụ, Tạo Pivot Table cho ra các báo cáo tổng cả 2 năm như sau, mỗi báo cáo 1 Pivot table:
- Báo cáo doanh số theo sản phẩm
- Báo cáo doanh số theo nhân viên bán hàng
- Báo cáo doanh số theo tháng và năm
- Báo cáo doanh số theo tháng, năm và theo sản phẩm
Câu 2:
Không tạo cột phụ, Tạo Pivot Table cho ra các báo cáo sau, mỗi báo cáo 1 Pivot table:
- Báo cáo cho riêng năm 2012, doanh số theo tháng, và tỷ lệ % doanh số so với doanh số tháng 7/2012
- Báo cáo cho riêng năm 2012, doanh số theo tháng và mặt hàng, và tỷ lệ % của từng mặt hàng trên tổng số tháng đó.
Câu 3:
Có thể tạo cột phụ hoặc trường tính toán, tao Pivot table tính được số nợ còn lại của từng khách hàng theo mẫu trong sheet cau3:
Khách hàng|Doanh số|Đã trả|Còn lại|
Alex|
3.402.500
|
3.402.500
|
0
|
AQ CAFÉ|
450.000
|
450.000
|
0
|
Bank Robber 1|
2.518.538
|
2.043.927
|
474.611
|
Bank Robber 2|
7.555.613
|
7.555.613
|
0
|
Câu 4:
Phân loại khách hàng theo tình trạng mới nhất. Một khách hàng có thể có nhiều tình trạng theo thời gian, nhưng phải lấy được tình trạng mới nhất, theo mẫu trong sheet cau4.
Có thể dùng cột phụ.
Row Labels|Doanh số|Đã trả|Còn lai|
Chưa hoàn thành|
129.891.886
|
100.836.119
|
29.055.767
|
Hoàn thành|
126.817.362
|
126.817.362
|
0
|
Mới|
11.770.625
|
5.138.828
|
6.631.797
|
Café Air|
2.593.750
|
1.197.892
|
1.395.858
|
Cafe SPACE|
1.394.375
|
697.186
|
697.189
|
CHIP|
5.627.500
|
1.687.500
|
3.940.000
|
NH Hkong|
2.155.000
|
1.556.250
|
598.750
|
Grand Total|
268.479.873
|
232.792.309
|
35.687.564
|
Câu 5:
Liệt kê chi tiết khách hàng từng bước theo thời gian, mỗi bước 1 dòng: mua. mua thêm, trả tiền, trả hàng ...
Có thể dùng cột phụ
Nội dung mỗi bước theo mẫu: STT bước, ngày tháng, nội dung, thí dụ
Khách hàng|Doanh số|Đã trả|Còn lại|
FOOD|
55.081.063
|
43.897.569
|
11.183.494
|
1. (05/11/12) - Mua hàng, trả tiền lần 1|
52.633.563
|
15.790.069
|
36.843.494
|
2. (14/12/12) - Thu tiền|
0
|
6.768.750
|
-6.768.750
|
3. (27/12/12) - Mua thêm|
208.750
|
0
|
208.750
|
4. (03/01/13) - Mua thêm & thanh toán|
2.238.750
|
1.791.250
|
447.500
|
5. (03/01/13) - Thu tiền đợt 4|
0
|
19.547.500
|
-19.547.500
|
Hoàn thành|
126.817.362
|
126.817.362
|
0
|
Alex|
3.402.500
|
3.402.500
|
0
|
1. (17/08/12) - Mua hàng, trả tiền lần 1|
3.402.500
|
0
|
3.402.500
|
2. (18/08/12) - Thu tiền|
0
|
1.020.750
|
-1.020.750
|
3. (24/08/12) - Trả tiền hàng|
0
|
2.381.750
|
-2.381.750
|
Câu 6:
Nhập liệu thêm 2 dòng dữ liệu giả định. Phải bảo đảm rằng chỉ cần refresh lại là 2 dòng mới nhập được cập nhật vào Pivot mà không cần chỉnh sửa nguồn dữ liệu của Pivot.
Yêu cầu phụ:
1. Định dạng số có dấu phân cách hàng ngàn, không có số thập phân. Text không định dạng (trừ tiêu đề).
- Số không có dấu phân cách, trừ điểm
- Số nằm bên trái hoặc giữa, trừ điểm
- Kéo thả để đổi mẫu báo cáo bị mất định dạng, trừ điểm
- Text không phải tiêu đề cột mà nằm giữa hoặc bên phải, trừ điểm
2. Chỉnh sửa tên field trong Pivot thành tiếng Việt. Trừ Grand total không sửa được, hễ thấy 1 từ "Sum of" là trừ điểm.
Ghi chú quan trọng:
Trong quá trình thực hành, nếu vì lỗi dữ liệu mà không làm được, hoặc ra kết quả sai thì phải sửa dữ liệu, và cho biết đã sửa cell nào, lý do gì. (Có 2 ô dữ liệu không đúng chuẩn)