Cuộc thi online: Vẽ biểu đồ động nhiều trục tung

Liên hệ QC

ptm0412

Bad Excel Member
Thành viên BQT
Administrator
Tham gia
4/11/07
Bài viết
13,762
Được thích
36,257
Donate (Momo)
Donate
Giới tính
Nam
Nghề nghiệp
Consultant
Ban tổ chức Sinh nhật Giải Pháp Excel lần thứ 4 thông báo:

1. Tổ chức cuộc thi vẽ biểu đồ chào mừng sinh nhật

2. Đối tượng tham dự: Tất cả mọi thành viên, mỗi thành viên chỉ nộp 1 bài dự thi (không sử dụng 2 nick để nộp nhiều hơn 1 bài).

3. Đề bài và yêu cầu: Xem bài 2 topic này

4. Thời hạn nộp bài: từ khi ra thông báo đến 24h 00 ngày 30/06/2010

5. Quy định nộp bài: bài dự thi phải gởi vào địa chỉ mail thanhmypham@giaiphapexcel.com, không gởi bằng đường khác hoặc post lên topic này.

6. Có 3 giải thưởng (nhất, nhì, ba) cho 3 thành viên đạt điểm cao nhất theo thang điểm công bố trong bài 3 topic này. Nếu nhiều thành viên bằng điểm nhau, sẽ tính thứ hạng cao hơn cho bài được nhận trước. Thời điểm nhận bài thi là thời điểm nhận mail. Thứ hạng và tên người nhận giải sẽ công bố trong topic này, Giải thưởng sẽ phát trong ngày tổ chức sinh nhật.
 
Lần chỉnh sửa cuối:
Đề bài cuộc thi online vẽ biểu đồ động nhiều trục tung

Đề bài cuộc thi online vẽ biểu đồ

1. Dữ liệu gốc: trong file đính kèm. Dữ liệu của 5 năm, mỗi năm 12 tháng, gồm 8 cột số liệu, có cột số liệu hàng trăm triệu, có cột số liệu hàng chục triệu, hàng triệu, hàng trăm ngàn, và %.

2. Yêu cầu:

- Nhiều trục tung: Do mỗi cột dữ liệu có số liệu cách quãng xa nhau, nên phải thể hiện được trên nhiều trục tung (trục giả, cụ thể là thể hiện số liệu trên 5 trục tung giả), không kể trục tung phụ. Xem hình dưới đây.

ThiBD01.jpg


- Thông số trục có tính động: Có thể thay đổi thông số các trục giả: Giá trị cao nhất, giá trị cắt trục hoành, số khoảng chia trục. Khi thay đổi các thông số này thì trục phải thay đổi theo.
Xem hình:

ThiBD02.jpg


- Đồ thị động: Chỉ thể hiện 12 tháng của 1 năm. Năm được chọn trong 1 list. Chọn năm nào thì biểu đồ thể hiện số liệu của năm đó.

- Trang trí: Sử dụng màu sắc để phân biệt trục nào ứng với đường biểu diễn nào. Các đường biểu diễn càng dễ phân biệt càng nhiều điểm. Ngoài ra trang trí theo ý thích hoặc khiếu thẩm mỹ riêng.

Thí dụ 2 cách trang trí và trình bày, cách trình bày thứ 2 nhiều điểm hơn do tách ra được các cặp series cùng nhóm:

ThiBD03.jpg


ThiBD04.jpg


3. Quy định:

- Chỉ dùng Excel 2003, không dùng 2007 (bãi bỏ theo yêu cầu)
- Chỉ dùng Excel chart, không dùng công cụ khác
- Không dùng VBA điều khiển hoặc hàm tự tạo

Bổ sung (do bỏ quy định 1)
- Dùng định dạng hoặc cấu trúc 2007 mà 2003 không làm được, sẽ không được cộng thêm điểm.
 

File đính kèm

  • DethiBieuDoSN04.xls
    23 KB · Đọc: 120
Lần chỉnh sửa cuối:
Một thí dụ về định dạng, trang trí và trình bày

ThiBD05.jpg
 
Lần chỉnh sửa cuối:
Nhà nghèo, không có Excel 2003, dùng tạm Excel 2007 làm bài có được không sư phụ ơi?
 
Làm xong save as 2003 đi nhen, Giám Khảo mở bằng 2003, cái nào định dạng, trình bày, trang trí bằng 2007 mất ráng chịu.
Kể cả dùng hàm của 2007, mở bằng 2003, bị lỗi #Name, ráng chịu.
Ngoài ra, may mắn là lão chết tiệt biết cách kiểm tra cái nào do 2007 có, mà 2003 không có.
 
Làm xong save as 2003 đi nhen, Giám Khảo mở bằng 2003, cái nào định dạng, trình bày, trang trí bằng 2007 mất ráng chịu.
Kể cả dùng hàm của 2007, mở bằng 2003, bị lỗi #Name, ráng chịu.
Ngoài ra, may mắn là lão chết tiệt biết cách kiểm tra cái nào do 2007 có, mà 2003 không có.
Hic, lẽ ra không nên hạn chế cái chuyện này. Muốn làm bằng Excel nào cũng được, miễn là làm cho đúng yêu cầu.
 
Hic, lẽ ra không nên hạn chế cái chuyện này. Muốn làm bằng Excel nào cũng được, miễn là làm cho đúng yêu cầu.

Ban đầu tính rằng có điểm trang trí, vì trang trí bằng 2007 và 2003 có những điểm khác nhau. Thí dụ Fill: 2007 có những fill effect mà 2003 không có và ngược lại.

Nếu vậy, bây giờ không tính điểm trang trí nữa, mà chỉ tính điểm trình bày sao cho thấy rõ serie nào tương ứng trục tung nào, các serie đừng chồng lên nhau quá nhiều, rối mắt.

Tuy nhiên, có những định dạng mà Excel 2003 không làm được thí dụ như:

2007 làm được như vầy:

ThiBD07.jpg


nhưng 2003 làm không được:

ThiBD08.jpg


Do đó, có thể dùng 2007 để làm, nhưng khi chấm điểm, Giám khảo sẽ cân nhắc.
 
Do đề bài có câu thuộc loại thách đố nên gây khó khăn cho người tham dự, nay tôi xin gợi ý như sau:
đề bài đã viết:
1. Dữ liệu gốc: trong file đính kèm. Dữ liệu của 5 năm, mỗi năm 12 tháng, gồm 8 cột số liệu, ...
2. Yêu cầu:
- Nhiều trục tung: Do mỗi cột dữ liệu có số liệu cách quãng xa nhau, nên phải thể hiện được trên nhiều trục tung (trục giả, ...

E rằng các bạn sẽ làm đến 8 trục giả, tôi xin gợi ý:

- Nhận xét rằng serie A và Serie B có cùng mức giá trị hàng trăm triệu, Min và Max của 2 serie chênh lệch không đáng kể, ta có thể dùng 1 trục giả cho 2 series này.
- Tương tự, 2 series C và D có cùng mức giá trị hàng chục triệu, chỉ dùng 1 trục giả thứ 2 cho 2 series này
- Kể cả 2 series E và F, cùng là mức %, vậy dùng 1 trục giả thứ 3 cho 2 series này
- Còn lại 2 series lạc loài G và H, mỗi chú 1 trục.

trong các hình minh hoạ nếu các bạn chú ý cũng sẽ thấy chỉ có 5 trục.


Tổng cộng chỉ có 5 trục giả có gán label dùng cho 8 series.

Ngoài ra:

Với 3 topics hướng dẫn các yêu cầu riêng biệt nêu trong bài trên, các bạn có quyền hỏi trong các topics đó, tôi sẽ trả lời trong phạm vi từng topic. Cái duy nhất các bạn phải làm là ráp 3 cái hướng dẫn đó lại thành 1 biểu đồ đạt đầy đủ yêu cầu.

- Biểu đồ nhiều hơn 2 trục tung. Trục tung giả không cắt trục hoành tại zero.
- Thay đổi khoảng chia của trục giả
- Dữ liệu động cho biểu đồ (chỉ thể hiện 1 phần của toàn bộ dữ liệu, phần dữ liệu này có thể dịch chuyển)
 
Lần chỉnh sửa cuối:
Đã có 1 thành viên nộp bài.

Mọi người đoán xem ai? (đạt hầu hết yêu cầu)

Ban đầu làm 8 trục giả, sau đó đọc bài trên, sửa thành 5 trục giả, rồi mới nộp.
 
Lần chỉnh sửa cuối:
Ngon dị ta, mình mày mò mãi mới được 1 trục thiệt và 0,5 trục giả à...
 
Tưởng Excel 2003 làm không được, không ngờ vẫn làm được:

ThiBD09.jpg
 
Lần chỉnh sửa cuối:
Do chưa thấy ai tham gia cuộc thi vẽ biểu đồ này, huynh chết tiệt đã nhờ Ngọc Mai viết bài gợi ý hoặc hướng dẫn thực hiện bài thi này. (Lão í bận rộn cái vớ vẩn gì đó không biết). Phiền 1 nỗi là ngọc Mai chỉ mới nghiên cứu nhưng làm chưa xong, lão í bèn đưa file đáp án cho viết. THế là vô tình lão ấy loại Ngocmai ra khỏi cuộc thi này! Buồn thế chứ!


HƯỚNG DẪN THỰC HIỆN BÀI THI

CÁC YÊU CẦU VỀ KIẾN THỨC:

1. Dữ liệu động cho biểu đồ:

Muốn tạo vùng dữ liệu động cho biểu đồ, phải nắm vững kỹ thuật tạo và sử dụng name động. Cụ thể trong bài thi, dữ liệu 5 năm 12 tháng nghĩa là 60 dòng. Yêu cầu đề bài là khi chọn năm, vùng dữ liệu sẽ dịch chuyển đến đúng 12 dòng dữ liệu của năm đó. Và vì ta sẽ dùng 1 kỹ thuật chuyển đổi dữ liệu, không dùng dữ liệu gốc, nên ta phải thể hiện vùng dữ liệu được chọn lên 1 trang tính mới để tính toán ra dữ liệu quy đổi. Ta sẽ phải biết sử dụng hàm index() hoặc công thức mảng.

2. Đồ thị nhiều trục tung:

Excel chỉ hỗ trợ tối đa 2 trục tung, muốn có nhiều hơn ta phải tạo trục giả. Trục giả là 1 đường biểu diễn dạng XY Scatter thẳng đứng, số liệu do ta tự lập. Kỹ năng cần thiết:
- Tạo được số liệu thích hợp cho trục giả để được trục thẳng đứng, nằm đúng vị trí cần thiết.
- Để thể hiện các thông số thực cho trục giả, ta phải biết kỹ năng sử dụng Add-in XY Chart Labeler tải miễn phí từ mạng xuống. Và phải biết kỹ năng tính toán cho ra cột Label sẵn sàng cho việc add label.
- Phải quy đổi mọi giá trị của tất cả mọi serie về 1 hệ chung, để thể hiện trên cùng 1 vùng của đồ thị, dù cho số liệu hàng trăm triệu, hàng triệu, hay chỉ hàng trăm, thậm chí hàng phần trăm (%). Do đó phải biết suy luận và kỹ năng tính toán (cụ thể là quy đổi về khoảng [0, 1]).
- Phải nhận xét: Có cần thiết phải làm cho mỗi serie 1 trục giả không, hay có thể gom nhóm, 2 hoặc 3 serie chung 1 trục.

3. Thông số trục có tính động:

Muốn trục tung có tính động, nghĩa là có thể thay đổi số khoảng chia, thay đổi khoảng nhìn thấy (min, max), phải có suy luận:
- Khoảng nhìn thấy thí dụ từ 400 triệu đến 800 triệu, muốn quy về [0,1] phải tính toán ra sao. Label thể hiện tính tương ứng như thế nào.
- Khi thay đổi min từ 400 triệu thành 200 triệu thì tính toán thế nào. Label của trục chạy theo ra sao.
- Khi thay đổi khoảng chia, tức là số điểm của serie trục giả thay đổi, những điểm còn lại chạy đi đâu.
- Phải biết dự phòng: tính toán sao cho người ta có thể chia trục đến 1 số khoảng tối đa có thể.

4. Kỹ năng vẽ biểu đồ:

- Là kỹ năng đương nhiên phải có. Ngoài ra phải biết tính chất sau đây của biểu đồ dạng line và XY Scatter: với dữ liệu nguồn lớn hơn vùng giá trị số, đồ thị sẽ băng ngang qua các điểm có giá trị 0, hoặc nhảy tọt xuống trục hoành khi có giá trị 0 ở cuối vùng dữ liệu. Tuy nhiên, ô trống và ô lỗi sẽ bị bỏ qua, đường biểu diễn sẽ cắt quãng tại điểm có gí trị lỗi hoặc ô trống. Ta sẽ tận dụng tính chất này để làm vùng dữ liệu nguồn cho trục lớn đến mức có số khoảng chia tối đa, dù cho số khảong chia có thể chọn nhỏ hơn.

5. Khiếu thẩm mỹ và óc nhạy bén:

Kỹ năng này giúp ta trình bày trang trí biểu đồ vừa đẹp, vừa rõ ràng, dễ đọc, dễ phân biệt trục nào ứng với đường biểu diễn nào.

Lão chết tiệt mong rằng sẽ có thêm người tham gia sau khi đọc bài này.

Ngọc Mai
 
Các bạn có thể tham khảo thêm bài phỏng vấn ngắn, trong đó ngocmaipretty có thêm vài gợi ý rất hữu dụng.
 
Trong bài gợi ý, ngocmaipretty đã quên không nói tới Add-in XY Chart Labeler. Add-in này dùng để gán label bất kỳ cho 1 serie biểu đồ, trong đề bài sẽ ứng dụng gán label giá trị thực cho serie trục giả, dù cho giá trị quy đổi dùng để vẽ biểu đồ là từ 0 đến 1.
Add-in XY Chart Labeler có thể tải miễn phí tại trang http://www.appspro.com/Utilities/ChartLabeler.htm. Đó là 1 add-in, sau khi cài đặt sẽ tạo trong menu Tools 1 mục con:

15.jpg

Gán Label cho các serie trục giả:
Trước khi gán label cho trục nào, ta hãy set số khoảng chia tối đa là 20, để vùng label được phủ đầy các giá trị số, không có ô NA().
Click chọn trục giả, vào Menu Tools như hình trên, chọn add chart labels:
Trong ô Select a label Range, chọn vùng chứa label thí dụ AE2:AE22
Trong ô Label Position, chọn Right, hoặc Left tuỳ theo ý thích
Nhấn OK

16.jpg
 
Em cũng rất muốn tham gia nhưng đang suy nghĩ rất nhiều về giải thưởng.
Nếu giải thưởng là 1 thùng Ken, hôm nay em sẽ nộp bài tham gia cho xôm tụ.
E xin hứa sẽ chia cùng ban giám khảo. BGK chịu mồi.
 
Cám ơn bài viết của sư nương ngocmaipretty đã gợi ý.
Tôi đã làm thử không rõ đạt được đến cửu âm chân kinh như lai thần chưởng của bác Mỹ không? Nhưng cố gắng mọi nội công cũng gởi bài dự thi. Nhờ bác Mỹ vui lòng test email bài dự thi của tôi. Tôi đã gởi tối qua.
 
Rất cám ơn TKT đã tham gia dự thi, có tham gia là có giải (TKT là người thứ 2 dự thi)

Tuy nhiên, TKT xem gửi mail vào địa chỉ nào mà tôi không nhận được?

5. Quy định nộp bài: bài dự thi phải gởi vào địa chỉ mail thanhmypham@giaiphapexcel.com, không gởi bằng đường khác hoặc post lên topic này.
 
Web KT
Back
Top Bottom