Cuộc thi "Excel toàn diện" online mừng sinh nhật GPE lần thứ 8

[leonguyenz]

Thành quả sau khi tập vẽ Chart bài dự thi, giờ mình có thể vẽ thành thạo Chart dạng này.
Cảm ơn Thầy Mỹ!

 

[ptm0412]

leo vừa nộp bài lại, so với lần trước có khác 1 xíu, nhưng ...

 

[leonguyenz]

leo vừa nộp bài lại, so với lần trước có khác 1 xíu, nhưng ...

Thầy cứ nửa vời vậy làm em mất ngủ luôn, ... thôi lỡ rồi thức luôn đến 3 giờ xem World Cup, ẹc ẹc ...
Khả năng 50% em được giải khuyến khích nộp bài sớm. Giờ cạn hết vốn rồi, em không nộp lại nữa đâu.
Xong bài thi, nhờ Thầy hướng dẫn tô màu trong 3 chữ GPE bài #21.

 

[tomichen27]

Thầy ơi, sau khi nhận giải thì thầy cho đáp án nhé thầy!

 

[ptm0412]

Vẫn chưa có bài nào toàn vẹn, nên vẫn còn rất nhiều cơ hội.

 

[hoangminhtien]

Vẫn chưa có bài nào toàn vẹn, nên vẫn còn rất nhiều cơ hội.

Bài 4 (VBA) em đã tự sửa đề và làm theo đề đã sửa nên không có điểm bài này. Chắc chắn đoạt giải tham gia nhiệt tình (nếu có bổ xung thêm giải này)

 

[zztantaibazz]

Thành quả sau khi tập vẽ Chart bài dự thi, giờ mình có thể vẽ thành thạo Chart dạng này.
Cảm ơn Thầy Mỹ!
View attachment 123231

Em cũng đang học kiểu này nhưng vẽ hoài không ra luôn ;(

 

[zztantaibazz]

Đang làm bài tới vẽ chart đuối .
Chắc làm 3 bài rùi nộp luôn Tham gia thui chứ giải thì em không nhận dc roài

 

[zztantaibazz]

Sau thời gian cố gắng học mảng em cũng đã làm xong bài 4 (cho dù cùi mía Nộp bài luôn rùi vì không làm dc chart )
Bài vẽ chart chắc em phải chờ bài giải để học .

Thật bổ ích khi có những bài thi như vậy ^^

 

[ptm0412]

Sau thời gian cố gắng học mảng em cũng đã làm xong bài 4 (cho dù cùi mía Nộp bài luôn rùi vì không làm dc chart )
Bài vẽ chart chắc em phải chờ bài giải để học .

Thật bổ ích khi có những bài thi như vậy ^^

Có cả 1 topic hướng dẫn vẽ từ A đến Z: http://www.giaiphapexcel.com/forum/showthread.php?71554-D%C3%B9ng-bi%E1%BB%83u-%C4%91%E1%BB%93-Excel-%C4%91%E1%BB%83-v%E1%BA%BD-k%E1%BB%B9-thu%E1%BA%ADt-m%E1%BA%B7t-c%E1%BA%AFt-c%C3%A1c-lo%E1%BA%A1i

Thậm chí lấy file bài 1, thêm 4 chỗ cong cong là ra.

 

[vocamxuc]

Nếu hợp tác, té ra 4 người cộng lại mới hoàn hảo toàn diện

vocamxuc thử làm 4 người xem sao?
Hôm qua mới chú ý thấy có bài thi hay này, tranh thủ đêm xem đá bóng làm thử, nick vocamxuc tại Giải pháp excel gửi tới bài dự thi, hy vọng được học hỏi thêm. cảm ơn nhiều

 

[ptm0412]

Ngày cuối cùng có thêm 3 người tham gia, tổng số là 7 người.
Qua khỏi 12 giờ đêm mới khóa sổ.

 

[ptm0412]

Kết quả cuộc thi "Excel toàn diện"

Công bố điểm:

Có tất cả 7 thành viên tham gia dự thi, số bài nộp là 12, nhưng chỉ chấm điểm và tính ngày giờ cho lần nộp sau cùng.

Bảng kết quả điểm như sau:

|

A​

​

|

B​

​

|

C​

​

|

D​

​

|

E​

​

|

F​

​

|

G​

​

|

H​

​

|

1​

​

|

STT​

​

|

Tên​

​

|

Ngày nộp cuối​

​

|

Pivot table​

​

|

Validation​

​

|

Chart​

​

|

VBA​

​

|

Cộng​

​

|

2​

​

| |Thang điểm| |

2,5​

|

2,5​

|

2,5​

|

2,5​

|

10​

|

3​

​

|

1​

|vocamxuc|

26/06/2014​

|

2,5​

|

2,5​

|

2,25​

|

2,5​

|

9,75​

|

4​

​

|

2​

|hoangminhtien|

11/06/2014​

|

2,5​

|

2,25​

|

2,25​

|

2,25​

|

9,25​

|

5​

​

|

3​

|dhn46|

10/06/2014​

|

2,5​

|

1​

|

2​

|

2,5​

|

8​

|

6​

​

|

4​

|zztantaibazz|

25/06/2014​

|

2,5​

|

2,25​

|

0​

|

2,5​

|

7,25​

|

7​

​

|

5​

|leonguyen|

11/06/2014​

|

2,5​

|

1​

|

2,25​

|

2​

|

7,75​

|

8​

​

|

6​

|linhngoc|

25/06/2014​

|

2,5​

|

2,5​

|

0​

|

0​

|

5​

|

9​

​

|

7​

|quancui|

12/06/2014​

|

2,5​

|

0​

|

0​

|

0​

|

2,5​

|

 

[ptm0412]

Kết quả cuộc thi "Excel toàn diện"

Công bố kết quả:

Giải nhất: Nick vocamxuc với số điểm 9.75

Giải nhì: Nick Hoangminhtien với số điểm 9.25

Giải ba: Nick dhn46 với số điểm 8.0

Giải khuyến khích nộp bài đầu tiên thuộc về leonguyen: 10/06/2014 14:46:00

 

[chuot0106]

Công bố kết quả:

Giải nhất: Nick vocamxuc với số điểm 9.75

Giải nhì: Nick Hoangminhtien với số điểm 9.25

Giải ba: Nick dhn46 với số điểm 8.0

Giải khuyến khích nộp bài đầu tiên thuộc về leonguyen: 10/06/2014 14:46:00

Chúc mừng các bạn đoạt giải!

 

[ptm0412]

Một số nhận xét về bài tham gia dự thi:

1. Phần pivot table:
Tất cả mọi người làm đúng nhưng về cột phụ có những công thức quá phức tạp như Sumproduct, If(SumProduct), If(CountIf), ... trong khi chỉ cần 1/Countif là đủ. Vì đằng nào vào Pivot table ta cũng áp dụng SUM. Linkngoc còn dùng tới 2 cột phụ, có lẽ nhóc cẩn thận sợ 1 PO trùng cho nhiều khách hàng.

2. Phần Data validation:
Không biết có ai đọc nhầm đề không, mà có người chỉ làm 1 cột cuối, có người chỉ làm 2 cột cuối, có người không làm cột nào? Phải làm validation cho cả 3 cột, và các validation này cột sau phụ thuộc vào cột phía trước nó.

Ngoài ra, cách dùng name chưa chuẩn xác dẫn đến việc lệch dòng hoặc dư dòng:
- Hoangminhtien: Validation quận và huyện càng xuống dòng dưới vùng name càng bị lệch dòng: Dữ liệu dòng 2 lệch 1 dòng, Dữ liệu dòng 3 lệch 2 dòng, ... do không cố định dòng.
- zztantaibazz: Số dòng của huyện bị thừa về phía dưới, càng xuống dòng dữ liệu dưới càng dư nhiều. name phức tạp quánên chưa tìm ra chính xác nguyên nhân.

3. Phần biểu đồ:
- Yêu cầu là khi thay đổi kích thước (cả 5 kích thước), mặt cắt phải thay đổi theo đúng tỷ lệ. nghĩa là phải cố định Min và Max của 2 trục bằng manual, không để auto. Nếu ai đang để quto thì chắc chắn khi thay kích thước hình sẽ biến dạng.

- Ngoài ra tỷ lệ ngang dọc cũng phải bảo đảm, không phải bằng cách lấy thước đo, mà là bằng VBA. Bước 6b trong topic http://www.giaiphapexcel.com/forum/s...c-lo%E1%BA%A1i ghi rõ việc này.

Tôi có 1 code test như sau:

Sub Test()
    ActiveSheet.ChartObjects(1).Activate
        ActiveChart.PlotArea.Select
        Rate1 = Selection.Width / Selection.Height
        Rate2 = (ActiveChart.Axes(xlCategory).MaximumScale - ActiveChart.Axes(xlCategory).MinimumScale) / _
        (ActiveChart.Axes(xlValue).MaximumScale - ActiveChart.Axes(xlValue).MinimumScale)
        MsgBox Rate1 & Chr(10) & Rate2

End Sub

Khi vừa mở file, chọn sheet chart và không click chạm gì vào biểu đồ, chạy code test, nếu ra 2 kết quả bằng nhau là đúng.
- Đường gióng có thể tạo bằng Errorbar hoặc thêm data point hoặc bất kỳ cách nào, nhưng có 1 bài thi tôi không tìm ra cách tạo đường gióng. Xin chịu thua.

- Hình như ít ai để ý đến việc format datapoint thay vì format data serie, nên những biểu đồ định dạng có mũi tên, thì thấy chỗ nào cũng có mũi tên. Nếu chỉ format data point, thì muốn để mũi tên ở đâu cũng được, chiều mũi tên thế nào cũng được.

3. Phần VBA:

- Câu b nâng cao yêu cầu tạo 1 sub duy nhất, mà cả 3 nút nhấn của 3 sheet đều gọi sub này chạy, nhưng ra kết quả là các báo cáo tương ứng. Nghĩa là 1 thủ tục chạy ra 3 báo cáo khác nhau. Phương pháp thì tùy từng người: thủ tục có tham số truyền hoặc thủ tục sử dụng biến có giá trị động theo sheet và cells trên sheet.
- leonguyen thì viết 1 sub gọi 3 thủ tục ra chạy lần lượt, nên phải tạo 1 nút nhấn mới.
- Hoangminhtien có phương pháp độc đáo là dùng advanced filter với vùng criteria là 1 ô có công thức mang giá trị động theo sheet. Tuy nhiên, Tiến không biết rằng advanced filter có thể sắp xếp thứ tự cột theo ý người dùng, nên đã tự ý thay đổi thứ tự cột của báo cáo. Chỗ này đề ra có mục đích nên các cột 2, 3, 4, 5, 6, 7 của report sẽ lấy dữ kiệu các cột lộn xộn: 2, 1, 7, 9, 8, 10. Tiến tự ý sắp thứ tự cột report để lấy 2, 1, 7, 8, 9, 10. Cột dư lại phải xóa đi. Chỉ vì việc này mà bị trừ 0.25 điểm mặc dù code chạy hoàn hảo.

Đính kèm là file bảng điểm kèm nhận xét

Còn đây là link download các bài dự thi: http://www.mediafire.com/download/z37ky2hbq0fdolh/Baiduthi.rar

 

[ptm0412]

File đính kèm là đáp án
Các bạn xem sheet biểu đồ, thử thay tán loạn các tham số và kiểm tra tỷ lệ. Còn tỷ lệ ngang dọc thì chạy đoạn code test ở trên.
Ngoài ra nguyên cái mặt cắt chỉ có 1 serie, chứ không cần nhiều series (hoặc quá nhiều).

Riêng phần VBA tôi chỉ post code câu b: Thủ tục có tham số truyền

Sub ExtractMultiType(Date1, Date2, Criteria, ColNum)
Dim EndRw As Long, SArr(), RArr(), i As Long, j As Long, RwCnt As Long
Application.ScreenUpdating = False
t = Timer
EndRw = Sheet1.[A100000].End(xlUp).Row
SArr = Sheet1.Range("A2:K" & EndRw).Value
RwCnt = UBound(SArr, 1)
ReDim RArr(1 To RwCnt, 1 To 7)
For i = 1 To RwCnt
    If SArr(i, 2) > Date2 Then Exit For
    If SArr(i, 2) <= Date2 And SArr(i, 2) >= Date1 And SArr(i, ColNum) = Criteria Then
        j = j + 1
        RArr(j, 1) = j
        RArr(j, 2) = SArr(i, 2)
        RArr(j, 3) = SArr(i, 1)
        RArr(j, 4) = SArr(i, 7)
        RArr(j, 5) = SArr(i, 9)
        RArr(j, 6) = SArr(i, 8)
        RArr(j, 7) = SArr(i, 10)
    End If
Next
ActiveSheet.[A7].Resize(RwCnt, 7).ClearContents
If j > 0 Then ActiveSheet.[A7].Resize(j, 7) = RArr
Application.ScreenUpdating = True
ActiveSheet.[G1] = Timer - t

End Sub

Rồi các nút nhấn của 3 sheet sẽ lần lượt là:

Private Sub CmdBtn1_Click()
ExtractMultiType [C4], [E4], [C5], 4
End Sub

Private Sub CmbBtn1_Click()
ExtractMultiType [C4], [E4], [C5], 5
End Sub

Private Sub CmbBtn1_Click()
ExtractMultiType [C4], [E4], [C5], 11
End Sub

 

[vocamxuc]

Công bố kết quả:

Giải nhất: Nick vocamxuc với số điểm 9.75

Giải nhì: Nick Hoangminhtien với số điểm 9.25

Giải ba: Nick dhn46 với số điểm 8.0

Giải khuyến khích nộp bài đầu tiên thuộc về leonguyen: 10/06/2014 14:46:00

Đọc tin này rất vui, may mắn vocamxuc đạt giải, xin chúc mừng các thành viên khác cùng đạt giải.

Hôm nay cũng là ngày sinh nhật Giải pháp excel, thật đáng tiếc là không thể kịp nữa để dự ngày vui này. Chúc toàn thể các thành viên tham dự có một cuộc vui, Diễn đàn có 1 dịp tổng kết các hoạt động thành công, và định hướng phát triển trong tương lai.

 

[zztantaibazz]

Công bố điểm:

Có tất cả 7 thành viên tham gia dự thi, số bài nộp là 12, nhưng chỉ chấm điểm và tính ngày giờ cho lần nộp sau cùng.

Bảng kết quả điểm như sau:

|

A​

|

B​

|

C​

|

D​

|

E​

|

F​

|

G​

|

H​

|

1​

|

STT​

|

Tên​

|

Ngày nộp cuối​

|

Pivot table​

|

Validation​

|

Chart​

|

VBA​

|

Cộng​

|

2​

| |Thang điểm| |

2,5​

|

2,5​

|

2,5​

|

2,5​

|

10​

|

3​

|

1​

|vocamxuc|

26/06/2014​

|

2,5​

|

2,5​

|

2,25​

|

2,5​

|

9,75​

|

4​

|

2​

|hoangminhtien|

11/06/2014​

|

2,5​

|

2,25​

|

2,25​

|

2,25​

|

9,25​

|

5​

|

3​

|dhn46|

10/06/2014​

|

2,5​

|

1​

|

2​

|

2,5​

|

8​

|

6​

|

4​

|zztantaibazz|

25/06/2014​

|

2,5​

|

2,25​

|

0​

|

2,5​

|

7,25​

|

7​

|

5​

|leonguyen|

11/06/2014​

|

2,5​

|

1​

|

2,25​

|

2​

|

7,75​

|

8​

|

6​

|linhngoc|

25/06/2014​

|

2,5​

|

2,5​

|

0​

|

0​

|

5​

|

9​

|

7​

|quancui|

12/06/2014​

|

2,5​

|

0​

|

0​

|

0​

|

2,5​

|

Xin chúc mừng mọi người . Nếu mà em làm dc bài chart chắc cũng có giải rùi

 

[zztantaibazz]

Một số nhận xét về bài tham gia dự thi:

2. Phần Data validation:
Không biết có ai đọc nhầm đề không, mà có người chỉ làm 1 cột cuối, có người chỉ làm 2 cột cuối, có người không làm cột nào? Phải làm validation cho cả 3 cột, và các validation này cột sau phụ thuộc vào cột phía trước nó.

Ngoài ra, cách dùng name chưa chuẩn xác dẫn đến việc lệch dòng hoặc dư dòng:
- Hoangminhtien: Validation quận và huyện càng xuống dòng dưới vùng name càng bị lệch dòng: Dữ liệu dòng 2 lệch 1 dòng, Dữ liệu dòng 3 lệch 2 dòng, ... do không cố định dòng.
- zztantaibazz: Số dòng của huyện bị thừa về phía dưới, càng xuống dòng dữ liệu dưới càng dư nhiều. name phức tạp quánên chưa tìm ra chính xác nguyên nhân.

Em đã tìm ra nguyên nhân. Em đã cố định nhầm, Thay vì $C2 thì em lại để là C$2, Do đó Quận Huyện luôn luôn lấy giá trị C2 để dùng trong countif