Cuộc thi online chào mừng Sinh nhật GPE lần 7 ngày 7 tháng 7

[ptm0412]

Ban Tổ chức Sinh nhật GPE lần thứ 7 thông báo:

Cứ mỗi năm đến gần tháng bảy
Sinh nhật G PE lại ngóng trông.
Tổ chức gặp nhau cùng hoan hỉ
Tổ chức thi tài để tính công

Công thức, hàm, Name, cần nắm vững
Pivot, trích lọc cũng phải thông.
Kiến thức Excel càng tích lũy
Vẫy vùng thiên hạ ắt thành công.


Thế nên hôm nay lão chết tiệt
Công bố cuộc thi tài "chết tiệt"
Pivot Table ai cũng biết
Hãy cùng tham gia để khỏi tiếc

Ngày 15/06 sẽ ra đề, 05/07 sẽ kết thúc. Mọi người hãy ôn lại kiến thức Pivot Table ngay và tham gia để giật giải.

te te te tò te tí te tò tò ...

 

[ptm0412]

Đề sẽ ra nhiều câu từ dễ đến khó. Tuy nhiên câu khó nhất cũng có trong tài liệu hoặc đã có ứng dụng trên các bài viết có sẵn. Mọi người cứ tham khảo tài liệu ngoài trang chủ và các bài viết có sẵn là làm được.

 

[ptm0412]

Đề thi Pivot Table nhân dịp Sinh Nhật GPE lần 7

1. Điều kiện tham dự:
Tất cả thành viên GPE có quyền tham dự, không phân biệt bất cứ tiêu chí nào.

2. Thời hạn tham dự:
Từ ngày ra đề đến hết ngày 05/07 năm 2013

3. Hình thức nộp bài:
- Nộp bài làm qua mail vào địa chỉ thanhmypham@giaiphapexcel.com, tiêu đề ghi "<nick> tham gia cuộc thi Pivot table". Ai gởi bài vào topic này bị cóp bi ráng chịu.
- File bài làm đặt tên BaithiPivot-<nick>.xls (thí dụ BaithiPivot-ptm0412.xls)
- Một người có thể nộp bài nhiều lần, nhưng chỉ tính điểm và thời gian nộp cho lần cuối cùng

4. Giải thưởng:
- 01 giải nhất cho người có số điểm cao nhất, nếu nhiều người cùng điểm sẽ tính cho người có thời gian nộp bài sớm hơn
- 01 giải nhì cho người có số điểm cao thứ nhì, hoặc bằng điểm giải nhất nhưng nộp bài chậm hơn.
- 01 giải 3 cho người có số điểm cao thứ ba, hoặc bằng điểm giải nhất, nhì nhưng nộp bài chậm hơn.
- 5 giải khuyến khích cho 5 bài nộp đầu tiên, không tính bài trúng giải 1, 2, 3, người nộp nhiều lần chỉ tính 1 lần.

5. Phát giải thưởng:
Giải thưởng sẽ phát tại lễ Sinh nhật ngày 07/07/2013. Nếu người trúng giải không tham dự SN, sẽ gởi qua đường bưu điện. Nếu giải thưởng không thể phát qua đường bưu điện thì có thê ghi nợ cho lão chết tiệt.

 

[ptm0412]

Đề 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)

 

[ptm0412]

Lưu ý:
Thí sinh tham gia dự thi có thể làm bằng Excel 2003, 2007, 2010 hoặc 2013. Việc này không ảnh hưởng số điểm. Ban Giám khảo biết giới hạn của 2003, 2007, nên chấm đúng, đủ cho mọi người.

Khi chấm điểm, Ban giám khảo sẽ mở xls bằng 2003, xlsx bằng 2010.
Do đó, nếu làm bằng Excel 2007, 2010, 2013, nên lưu dạng xlsx. Nếu lưu dạng xls và mở bằng 2003 sẽ bị mất 1 số định dạng hoặc cấu trúc Pivot table mà 2003 không có.

Xin lỗi file đề sai mẫu ở sheet cau3: Cột còn lại sai. Các bạn tải lại file bài trên, hoặc tự sửa cột "còn lại" = "Doanh số" trừ "đã trả."

 

[hoangminhtien]

Còn 2 vấn đề chưa giải quyết được:
1. Chưa chuyển được "Total" thành "Doanh số" ở câu 1
2. Chưa chuyển định dạng tháng từ tiếng anh sang tiếng việt ở câu 2
Đã nộp bài để nhận giải khuyến khích.

 

[OverAC]

HoangminhTien này nếu có giải sẽ phải nhận ở sinh nhật, he he.

 

[ptm0412]

Còn 2 vấn đề chưa giải quyết được:
1. Chưa chuyển được "Total" thành "Doanh số" ở câu 1
2. Chưa chuyển định dạng tháng từ tiếng anh sang tiếng việt ở câu 2
Đã nộp bài để nhận giải khuyến khích.

1. Hình như 2003 không chuyển total thành tiếng Việt được, để về nhà test
2. định dạng tháng thì không cần chuyển tiếng Việt, vì nó không phải tên field
3. Giải khuyến khích cho người nộp bài đầu tiên thuộc về ..... Linh Ngoc

 

[ptm0412]

Tính đến thời điểm này, đã có 4 nick tham dự.

Theo thứ tự thời gian:

Linhngoc (nộp 2 lần)
Lê Duy Thương
Hoangminhtien
vangiap82 (nộp 2 lần)

 

[Lê Duy Thương]

Còn 2 vấn đề chưa giải quyết được:
1. Chưa chuyển được "Total" thành "Doanh số" ở câu 1
2. Chưa chuyển định dạng tháng từ tiếng anh sang tiếng việt ở câu 2
Đã nộp bài để nhận giải khuyến khích.

Tôi làm trên excel 2013 ở nhà Tháng cũng không có tiếng việt . mặc dù đã định dạng [$-101042A] mmm

 

[ptm0412]

Tôi làm trên excel 2013 ở nhà Tháng cũng không có tiếng việt . mặc dù đã định dạng [$-101042A] mmm

Muốn có tháng tiếng Việt phải chỉnh trong control panel chọn language là Vietnamese. Hoặc có thể gõ tay (ẹc ẹc)

Tuy nhiên việc này không yêu cầu trong đề bài. Đề chỉ yêu cầu sửa tên field.

 

[huyhoang1768]

Sao em ko gộp được năm, tháng vào mới nhau nhỉ nó cứ chạy cả dãy mà ko hiện theo từng tháng, tìm hoài mà ko thấy bài nào hướng dẫn

 

[ptm0412]

Sao em ko gộp được năm, tháng vào mới nhau nhỉ nó cứ chạy cả dãy mà ko hiện theo từng tháng, tìm hoài mà ko thấy bài nào hướng dẫn

Nhanh nhất là bạn tải sách ngoài trang chủ về đọc.

 

[Lê Duy Thương]

Muốn có tháng tiếng Việt phải chỉnh trong control panel chọn language là Vietnamese. Hoặc có thể gõ tay (ẹc ẹc)

Tuy nhiên việc này không yêu cầu trong đề bài. Đề chỉ yêu cầu sửa tên field.

cuối cùng cũng tìm ra cách để sửa total thành tiếng ẹc ẹc

chắc chắn giải khuyến khích
ẹc ẹc

 

[OverAC]

Đề nghị anh Thương không khoe kết quả để bà con còn hứng làm

Dù sao thì hình sơ sơ thấy kết quả cũng không ok lắm

 

[hoangminhtien]

cuối cùng cũng tìm ra cách để sửa total thành tiếng ẹc ẹc

chắc chắn giải khuyến khích
ẹc ẹc

Bác Thương sửa được rồi à, em thấy cái thông báo này thì thôi luôn. Chắc giờ phải dùng tiểu xảo để hi vọng bằng anh bằng em mới được.

 

[huyhoang1768]

Mục chén bát đĩa sao em kiểm tra không lỗi, không có khoảng trắng, coppy pate cho dữ liệu đồng nhất, vậy mà nó vẫn nhảy ra 1 mục nữa của tháng 4/2012, và tách riêng biệt, ---> kq sai
------------------------------------
Đã tìm đc nguyên nhân, mỗi tội ko chuyển đc sang tiếng việt của ngày tháng

 

[ptm0412]

Bác Thương sửa được rồi à, em thấy cái thông báo này thì thôi luôn. Chắc giờ phải dùng tiểu xảo để hi vọng bằng anh bằng em mới được.

Xin xác nhận lại là Excel 2003 không sửa được. Excel 2007 trở lên đều làm được. Thương xài 2013 mà không làm được là chưa nắm hết bí quyết, và không tận dụng được sự hơn hẳn của 2007 trở về sau.

 

[Lê Duy Thương]

Tính đến thời điểm này, đã có 4 nick tham dự.

Theo thứ tự thời gian:

Linhngoc (nộp 2 lần)
Lê Duy Thương
Hoangminhtien
vangiap82 (nộp 2 lần)

sao đến giờ này không thấy thêm ai .

 

[ptm0412]

sao đến giờ này không thấy thêm ai .

Đã thêm 1 người rồi Thương à