- Tham gia
- 4/11/07
- Bài viết
- 14,632
- Được thích
- 37,319
- Donate (Momo)
- Giới tính
- Nam
- Nghề nghiệp
- Consultant
Biểu đồ với số liệu các series có khoảng cách lớn
Vấn đề:
Có nhiều trường hợp ta cần vẽ biểu đồ với nhiều dãy số liệu, mà dữ liệu cần vẽ lại nằm trong các khoảng số cách xa nhau. Nếu chỉ có 2 dãy số liệu, ta có thể dùng trục toạ độ thứ hai, nhưng từ 3 dãy trở lên đòi hỏi phải có 1 kỹ thuật khác.
Dữ liệu:
Giả sử ta có 3 serie dữ liệu A, B, C như bảng dưới và muốn thực hiện trên cùng 1 đồ thị. Lưu ý rằng 3 dãy số liệu có khoảng cách rất xa nhau: Serie A có các số liệu nhỏ hơn 1, Serie B có các số liệu hàng trăm ngàn, trong khi Serie C có số liệu hàng chục triệu.
Với cách vẽ thông thường, ta sẽ có đồ thị như hình sau: Serie B nằm gần sát trục Y trong khi serie A hầu như không nhận thấy nếu không có màu và không có tick mark.
Nếu dùng trục Y thứ 2 cho serie A thì đường biểu diễn serie A nằm lẫn lộn với serie C khó phân biệt và không đẹp. Hơn nữa người xem phải đi tìm xem đường nào tương ứng với trục Y nào.
Ta cũng có thể thiết lập trục Y chia theo logarith như hình sau, nhưng dù 2 serie A và B đã rời xa trục X nhưng lại có 1 khoảng trống lớn giữa serie A và B. Đồng thời do chia theo logarith, 2 serie B và C trở thành gần như đường thẳng.
Hoặc ta làm thủ công, vẽ 3 đồ thị cho 3 serie rồi đặt chúng theo thứ tự chồng lên nhau sau khi format Chart Area của chúng là noline, no fill, X axis của 2 serie B và C là No tick mark, No tick mark labels.
Nhưng thật là khó để canh cho đúng các kích thước vùng đồ thị (Plot Area) sao cho chúng lắp ráp khớp với nhau: lý do là vì yêu cầu chỉnh kích thước vùng Plot Area chứ không phải kích thước Chart Area.
Hết sức khó khăn khi phải dùng chuột nắm kéo, điều chỉnh kích thước, vị trí cho 3 biểu đồ ăn khớp nhau. Một bài toán nan giải của thử - sai - sửa. Bạn cũng có thể can thiệp bằng VBA với độ chính xác là pixel, cũng không dễ dàng gì (vì như đã nói, điều chỉnh kích thước của Plot Area, nhưng điều chỉnh vị trí lại là ChartObjects). Hơn nữa, một khi dữ liệu thay đổi, bạn sẽ phải điều chỉnh lại từ đầu.Thí dụ dữ liệu serie A tăng quá 10% hoặc dữ liệu serie B vượt quá 1 triệu … hay sếp bạn muốn tăng kích thước toàn bộ lên, quá trình thử - sai - sửa lại tiếp diễn.
Dữ liệu giả lập
Dữ liệu gốc là vùng A1
7 màu vàng. Dữ liệu giả lập để đưa lên biểu đồ là vùng E1:G7 màu cam. Cách tính cho vùng này sẽ giải thích sau. Tỷ lệ chia cho trục Y (scales) nằm trong vùng A9:G11 màu xanh dương nhạt. Các giá trị Min và Max của dữ liệu nằm trong các ô B910 tính bằng công thức Min và Max. Các giá trị trong vùng E9:G11 do bạn gõ tay vào với 1 sự tính toán tương đối.
Độ cao tương đối của mỗi vùng đồ thị bằng 1 nằm trong vùng E12:G12. Nghĩa là mỗi vùng đồ thị cho mỗi serie có độ cao bằng nhau và bằng 1/3 của toàn vùng Plot Area. Đặt giá trị này vào các ô sẽ giúp bạn có thể thay đổi kích thước từng vùng đồ thị 1 cách nhanh chóng.
Sau khi hoàn tất các ô trên ta bắt đầu tính cho vùng dữ liệu giả lập E2:G7. Công thức E2 là:
=((B2-E$9)/(E$10-E$9)*E$12+SUM($D$12$12))/SUM($E$12:$G$12)
Phần (B2-E$9)/(E$10-E$9)*E$12 của công thức là vị trí tương đối của giá trị B2 trong vùng serie A của mình, còn +SUM($D$12$12) là thêm vào 1 giá trị tương ứng với vị trí đáy serie của mình, (với serie A là zero), /SUM($E$12:$G$12) là so sánh với chiều cao của toàn vùng đồ thị. Fill công thức cho hết vùng E2:G7.
Biểu đồ sẽ dùng các serie số giả lập dạng XY làm các label trên trục Y giả lập ở cả 2 bên. Các serie số này được tạo như bảng dưới. Thông thường 1 đồ thị sẽ cần không quá 10 con số minh hoạ trên trục Y, và 1 vùng con sẽ không quá 5 hoặc 6. Tuy vậy bạn cũng hãy tạo sẵn 1 con số chừng gấp đôi mức dự trù, nghĩa là 12 dòng kể cả tiêu đề. Nếu cần bạn có thể làm nhiều hơn.
Các cột I, J (X-left và X-right) là các giá trị X để tạo ra các tickmark cho 2 trục Y bên trái và bên phải. X-left là ngày đầu (A2) trừ 0,5 và định dạng số. X-right là ngày cuối (A7) cộng với 0,5 và cũng định dạng sồ. Bạn có thể gõ công thức trực tiếp =A2-0,5, =A7+0,5 hoặc dùng hàm Min() Max() tuỳ bạn.
Số liệu trong các cột K:M tính toán dựa trên vùng E9:G11. Ô K2 có công thức đơn giản là Min của Serie tương ứng (=E$9, fill ngang qua M2).
Công thức K3 tính cho các mức của Label kế tiếp, hoặc cho giá trị #N/A khi đã đạt mức Max (E10). Hãy nhớ lại rằng ta cần #N/A vì giá trị lỗi #N/A sẽ không hiện lên đồ thị dạng Line hay XY Scatter:
K3 =IF(K2+E$11>E$10,NA(),K2+E$11)
Fill ngang qua M3 và Fill xuống M12. Định dạng lại số cho chúng theo cách bạn muốn thấy trên trục đồ thị, kể cả màu chữ vì ta sẽ dùng 1 tiện ích XY Chart Labeller tự động lấy Label theo định dạng.
Giá trị cho các trục Y giả nằm trong các cột N
, tính toán bằng công thức tương tự như tính cho các cột E:G nhưng không dùng các giá trị Serie A, B, C mà dùng các giá trị Label A, B, C.
Công thức N2 là:
=((K2-E$9)/(E$10-E$9)*E$12+SUM($D$12$12))/SUM($E$12:$G$12)
FIll hết cho vùng N212.
Vấn đề:
Có nhiều trường hợp ta cần vẽ biểu đồ với nhiều dãy số liệu, mà dữ liệu cần vẽ lại nằm trong các khoảng số cách xa nhau. Nếu chỉ có 2 dãy số liệu, ta có thể dùng trục toạ độ thứ hai, nhưng từ 3 dãy trở lên đòi hỏi phải có 1 kỹ thuật khác.
Dữ liệu:
Giả sử ta có 3 serie dữ liệu A, B, C như bảng dưới và muốn thực hiện trên cùng 1 đồ thị. Lưu ý rằng 3 dãy số liệu có khoảng cách rất xa nhau: Serie A có các số liệu nhỏ hơn 1, Serie B có các số liệu hàng trăm ngàn, trong khi Serie C có số liệu hàng chục triệu.
Với cách vẽ thông thường, ta sẽ có đồ thị như hình sau: Serie B nằm gần sát trục Y trong khi serie A hầu như không nhận thấy nếu không có màu và không có tick mark.
Nếu dùng trục Y thứ 2 cho serie A thì đường biểu diễn serie A nằm lẫn lộn với serie C khó phân biệt và không đẹp. Hơn nữa người xem phải đi tìm xem đường nào tương ứng với trục Y nào.
Ta cũng có thể thiết lập trục Y chia theo logarith như hình sau, nhưng dù 2 serie A và B đã rời xa trục X nhưng lại có 1 khoảng trống lớn giữa serie A và B. Đồng thời do chia theo logarith, 2 serie B và C trở thành gần như đường thẳng.
Hoặc ta làm thủ công, vẽ 3 đồ thị cho 3 serie rồi đặt chúng theo thứ tự chồng lên nhau sau khi format Chart Area của chúng là noline, no fill, X axis của 2 serie B và C là No tick mark, No tick mark labels.
Nhưng thật là khó để canh cho đúng các kích thước vùng đồ thị (Plot Area) sao cho chúng lắp ráp khớp với nhau: lý do là vì yêu cầu chỉnh kích thước vùng Plot Area chứ không phải kích thước Chart Area.
Hết sức khó khăn khi phải dùng chuột nắm kéo, điều chỉnh kích thước, vị trí cho 3 biểu đồ ăn khớp nhau. Một bài toán nan giải của thử - sai - sửa. Bạn cũng có thể can thiệp bằng VBA với độ chính xác là pixel, cũng không dễ dàng gì (vì như đã nói, điều chỉnh kích thước của Plot Area, nhưng điều chỉnh vị trí lại là ChartObjects). Hơn nữa, một khi dữ liệu thay đổi, bạn sẽ phải điều chỉnh lại từ đầu.Thí dụ dữ liệu serie A tăng quá 10% hoặc dữ liệu serie B vượt quá 1 triệu … hay sếp bạn muốn tăng kích thước toàn bộ lên, quá trình thử - sai - sửa lại tiếp diễn.
Dữ liệu giả lập
Dữ liệu gốc là vùng A1
7 màu vàng. Dữ liệu giả lập để đưa lên biểu đồ là vùng E1:G7 màu cam. Cách tính cho vùng này sẽ giải thích sau. Tỷ lệ chia cho trục Y (scales) nằm trong vùng A9:G11 màu xanh dương nhạt. Các giá trị Min và Max của dữ liệu nằm trong các ô B910 tính bằng công thức Min và Max. Các giá trị trong vùng E9:G11 do bạn gõ tay vào với 1 sự tính toán tương đối.Độ cao tương đối của mỗi vùng đồ thị bằng 1 nằm trong vùng E12:G12. Nghĩa là mỗi vùng đồ thị cho mỗi serie có độ cao bằng nhau và bằng 1/3 của toàn vùng Plot Area. Đặt giá trị này vào các ô sẽ giúp bạn có thể thay đổi kích thước từng vùng đồ thị 1 cách nhanh chóng.
Sau khi hoàn tất các ô trên ta bắt đầu tính cho vùng dữ liệu giả lập E2:G7. Công thức E2 là:
=((B2-E$9)/(E$10-E$9)*E$12+SUM($D$12
$12))/SUM($E$12:$G$12)Phần (B2-E$9)/(E$10-E$9)*E$12 của công thức là vị trí tương đối của giá trị B2 trong vùng serie A của mình, còn +SUM($D$12
$12) là thêm vào 1 giá trị tương ứng với vị trí đáy serie của mình, (với serie A là zero), /SUM($E$12:$G$12) là so sánh với chiều cao của toàn vùng đồ thị. Fill công thức cho hết vùng E2:G7.Biểu đồ sẽ dùng các serie số giả lập dạng XY làm các label trên trục Y giả lập ở cả 2 bên. Các serie số này được tạo như bảng dưới. Thông thường 1 đồ thị sẽ cần không quá 10 con số minh hoạ trên trục Y, và 1 vùng con sẽ không quá 5 hoặc 6. Tuy vậy bạn cũng hãy tạo sẵn 1 con số chừng gấp đôi mức dự trù, nghĩa là 12 dòng kể cả tiêu đề. Nếu cần bạn có thể làm nhiều hơn.
Các cột I, J (X-left và X-right) là các giá trị X để tạo ra các tickmark cho 2 trục Y bên trái và bên phải. X-left là ngày đầu (A2) trừ 0,5 và định dạng số. X-right là ngày cuối (A7) cộng với 0,5 và cũng định dạng sồ. Bạn có thể gõ công thức trực tiếp =A2-0,5, =A7+0,5 hoặc dùng hàm Min() Max() tuỳ bạn.
Số liệu trong các cột K:M tính toán dựa trên vùng E9:G11. Ô K2 có công thức đơn giản là Min của Serie tương ứng (=E$9, fill ngang qua M2).
Công thức K3 tính cho các mức của Label kế tiếp, hoặc cho giá trị #N/A khi đã đạt mức Max (E10). Hãy nhớ lại rằng ta cần #N/A vì giá trị lỗi #N/A sẽ không hiện lên đồ thị dạng Line hay XY Scatter:
K3 =IF(K2+E$11>E$10,NA(),K2+E$11)
Fill ngang qua M3 và Fill xuống M12. Định dạng lại số cho chúng theo cách bạn muốn thấy trên trục đồ thị, kể cả màu chữ vì ta sẽ dùng 1 tiện ích XY Chart Labeller tự động lấy Label theo định dạng.
Giá trị cho các trục Y giả nằm trong các cột N
, tính toán bằng công thức tương tự như tính cho các cột E:G nhưng không dùng các giá trị Serie A, B, C mà dùng các giá trị Label A, B, C.Công thức N2 là:
=((K2-E$9)/(E$10-E$9)*E$12+SUM($D$12
$12))/SUM($E$12:$G$12)FIll hết cho vùng N2
12.
