- Tham gia
- 4/11/07
- Bài viết
- 13,810
- Được thích
- 36,327
- Donate (Momo)
- Giới tính
- Nam
- Nghề nghiệp
- Consultant
HƯỚNG DẪN VẺ BIỂU ĐỒ NHIỀU HÆ N 2 TRỤC TUNG (tiếp theo)
III. VẺ CÁC TRỤC GIẢ
A. TẠO VÙNG DỮ LIỆU CHO 5 TRỤC
1. Cơ sở lý luận:
- Trục giả phải giống y như trục thật, thông số thực của nó phải giống như trục thật. Trục tung giả, Min và Max bằng Min và Max của trục tung thật (0 và 1). Dù chia bao nhiêu khoảng, trục giả cũng phải giữ vững Min và Max này.
- 1 trục tung giả sẽ có cùng giá trị X, và bằng vị trí của nó so với trục tung thật. Các giá trị Y chia đều cho số khoảng chia và từ 0 đến 1. (Một trục X giả, sẽ có cùng giá trị Y, và các giá trị X chia đều từ 0 đến 1 giá trị cho trước)
- Phải dự trù rằng người dùng muốn chia trục trong khoảng tối đa là bao nhiêu, thí dụ chia 25 là quá nhiều khoảng, các vạch chia quá sát nhau, ta giới hạn số khoảng chia tối đa là 20, số vạch chia là 21 suy ra số ô dữ liệu cho 1 trục là 21 ô. Tuy vậy, khi người dùng chia số khoảng ít hơn 20, thí dụ 10, thì ô thứ 11 (vạch chia thứ 11), cũng đã đạt tối đa của trục là 1. Các ô còn lại phải là giá trị lỗi #N/A hoặc ô trống. Tính chất của biểu đồ Line và XY scatter là bỏ qua ô trống hoặc ô lỗi, còn ô có giá trị "" hoặc 0 vẫn thể hiện. Công thức tính giá trị cho trục phải thể hiện lỗi bằng hàm NA().
Tuy nhiên, có 1 biện pháp để không thể hiện các giá trị dư thừa lên đồ thị bằng cách ấn định Min và Max của trục Y là cố định.Thí dụ ấn định Max trục là 1, thì các giá trị lớn hơn 1 sẽ không thể hiện lên biểu đồ, chính xác hơn, nó vượt ra ngoài giới hạn nhìn thấy của biểu đồ.
2. Tính toán:
- Khi chia trục làm n khoảng, ta sẽ có n+1 điểm dữ liệu, các điểm còn lại bằng NA(), hoặc cứ fill đầy giá trị.
- Giá trị Y: Điểm đầu tiên = 0, điểm n+1 có giá trị bằng 1
- Giá trị Y: mỗi điểm cách nhau 1 khoảng = 1/n. Các điểm lớn hơn n+1 cũng có thể tính tiếp, hoặc để NA()
- Giá trị X: từ điểm 1 đến điểm n+1, giá trị bằng nhau và bằng vị trí trục so với trục thật. Các điểm còn lại bằng NA() hoặc bằng luôn số đó.
3. Công thức trên sheet:
- Công thức X serie trục A:
U1 = 13.5
U2 =$U$1
Với L24 là số khoảng chia. Kéo công thức xuống dòng 22
- Tương tự công thức X các serie khác.
- Công thức Y serie trục A:
Z2 =(ROW()-2)/L$24. Kéo công thức xuống dòng 22
Y liên quan đến X, và số khoảng chia, nếu số khoảng chia nhỏ hơn 20, nó fill đủ 21 ô đến giá trị cuối lớn hơn 1, nhưng chỉ các giá trị Y <=1 mới thể hiện trên biểu đồ. Tương tự là công thức tính Y các trục khác.
4. Bổ sung trục chặn:
Do đồ thị chính chỉ vẽ 12 tháng tức là đến vị trí 12 của trục hoành, ta nên làm 1 trục giả chặn bên phải của đồ thị, ngăn cách các đường biểu diễn và các trục.
X = 12.5
T1 = 12.5
T2 =;$T$1 (dựa vào cột U, giá trị X serie trục A, số điểm bằng cột U, số khoảng chia bằng cột U)
Y tính theo Y trục A, số khoảng chia bằng số khoảng chia của trục A, để sau này khi dùng X Error Bar sẽ giống như Grid Line của trục thật, mà lại chạy theo trục A. Dùng luôn cột Z làm Y trục chặn.
5. Bổ sung trục X giả:
Trục X thật có Max = 25, trong khi biểu đồ chỉ vẽ tới 12, nếu định dạng label cho trục X thật, sẽ là số từ 0 đến 25, coi kỳ cục. Vả lại ta muốn label là tháng 1, tháng 2, … cơ. Do đó ta vẽ thêm 1 trục X giả ngắn 12 thôi. Dữ liệu như sau:
- Giá trị X: dùng cột A, từ 1 đến 12
- Giá trị Y: dùng cột K, tất cả zero.
B. VẺ TRỤC GIẢ
1. Vẽ trục chặn:
Vẽ trục chặn: Tô chọn và Copy T1:T22 & Z1:Z22. Click chọn đồ thị, vào menu Edit - Paste Speccial:
Đánh dấu Series Names in First Rows, X value in First Column, New Serie và Serie in Column, nhấn OK. Trục chặn đã được vẽ xong.
2. Vẽ các trục giả khác:
Tương tự như trục chặn, hãy copy 2 cột X trục 1 và Y trục 1, Paste Special vào đồ thị, ta sẽ có trục 1. Thực hiện tiếp cho các trục còn lại.
Định dạng màu tương ứng cho các trục, trục của serie nào định dạng màu cho giống serie đó.
Định dạng có tickmack cùng màu như sau:
Kết quả:
3. Vẽ các ErrorBar cho trục chặn:
Error bar để giả làm Major GridLine, hay quá! Click chọn trục chặn, format, trong tab X ErrorBar, chọn Minus, chọn Fix value, gõ 12.5:
Chọn màu cho các ErrorBar luôn:
Xoá bỏ các legend không cần thiết: Click chọn legends box, click cái nữa chọn từng legend, và nhấn delete.
4. Vẽ trục X giả:
Copy 2 cột A1:A13 và K1:K13, Paste special vào đồ thị. Định dạng màu phù hợp.
C. GÁN LABEL CHO TRỤC GIẢ:
1. Tính toán:
Mỗi trục có những giá trị cần hiển thị khác nhau, nên ta phải tính toán các giá trị tương ứng dùng làm label. Thí dụ trục giả cho Serie A: min = 0, Max = 800.000.000, chia 16 khoảng:
Label cho trục 1 ở cột AE:
AE2 = L22 (min)
AE3 =AE2+L$26
Fill xuống tới AE22. Từ ô AE19 trở đi, các giá trị > 800 triệu, nhưng yên tâm, nó không thể hiện vì vượt ra khỏi vùng nhìn thấy của biểu đồ.
Tương tự cho những label khác. Vì ta sẽ sử dụng Add-in XY Chart Labeler, mà công cụ này tự lấy định dạng số và màu của các cells chứa label, nên ta định dạng luôn:
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:
2. 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ả thứ nhất, vào Menu Tools như hình trên, chọn add chart labels:
Trong ô Select a label Range, chọn vùng AE2:AE22
Trong ô Label Position, chọn Right
Nhấn OK
Nếu các label quá lớn, chỉnh font size lại, nếu nó đè lên trục kế bên, hãy dời trục kế bên qua phải. Thí dụ trục 2 bị đè và đang ở vị trí 15.5, dời nó sang vị trí 17 chẳng hạn, bằng cách đơn giản nhất là thay 17 vào ô V1:
____
Lần lượt gán label cho các trục khác:
3. Gán label cho trục X giả:
Dùng cột B làm Label cho serie trục X giả. Định dạng màu label và Alignment quay 90 độ.
IV. TRANG TRÍ:
1. Tô màu nền, màu chữ:
Kiểm tra màu sắc, dộ tương phản giữa màu nền và màu chữ, nếu không rõ thì định dạng lại.
2. Đổi dạng biểu đồ:
Ta thấy 2 serie G và H đứng đơn độc và chồng lên các serie khác khó coi. Ta hãy đổi dạng biểu đồ khác cho dễ nhìn.
Click chuột phải vào Serie G, chọn change chart type, chọn loại Area. Tô màu lại.
Click chuột phải vào Serie H, chọn change chart type, chọn loại Column. Tô màu lại.
3. Đổi đơn vị tính:
Các label hàng trăm triệu và chục triệu chiếm khá nhiều chỗ trên biểu đồ, ta muốn chia cho 1 triệu và ghi chú là "triệu đồngâ€.
- Trước tiên chỉnh Min max trong vùng thông số trục, các label tự đổi theo.
- Chỉnh công thức quy đổi:
L2 = (C2-L$22)/L$25 sửa thành:
L2 = (C2/1000000-L$22)/L$25
Kéo xuống
Tương tự là cột M, N, và P
- Dùng công cụ vẽ 2 textbox, gõ vào "Triệu đồngâ€, định dạng màu chữ.
Trên đây là toàn bộ các hướng dẫn chi tiết để thực hiện đề bài. Mong rằng các bạn hiểu và vận dụng được tốt trong công việc.
Một số bài viết có liên quan:
1/ Hướng dẫn vẽ biểu đồ nhiều trục tung (P1)
2/ Phân tích tồn kho theo phương pháp ABC
3/ 30 mẹo siêu đơn giản giúp hoàn thiện biểu đồ (phần 1)
4/ 30 mẹo siêu đơn giản giúp hoàn thiện biểu đồ (phần 2)
5/ Đánh dấu điểm MAX và MIN trong biểu đồ Excel
6/ Biểu đồ đường hay phân tán?
7/ Kết hợp biểu đồ và thêm 1 trục phụ
8/ Nhãn danh mục không chen ngang vào biểu đồ
9/ Hướng dẫn từng bước vẽ biểu đồ Bubble
10/ Làm thế nào để đồ thị hoạt động hiệu quả?
III. VẺ CÁC TRỤC GIẢ
A. TẠO VÙNG DỮ LIỆU CHO 5 TRỤC
1. Cơ sở lý luận:
- Trục giả phải giống y như trục thật, thông số thực của nó phải giống như trục thật. Trục tung giả, Min và Max bằng Min và Max của trục tung thật (0 và 1). Dù chia bao nhiêu khoảng, trục giả cũng phải giữ vững Min và Max này.
- 1 trục tung giả sẽ có cùng giá trị X, và bằng vị trí của nó so với trục tung thật. Các giá trị Y chia đều cho số khoảng chia và từ 0 đến 1. (Một trục X giả, sẽ có cùng giá trị Y, và các giá trị X chia đều từ 0 đến 1 giá trị cho trước)
- Phải dự trù rằng người dùng muốn chia trục trong khoảng tối đa là bao nhiêu, thí dụ chia 25 là quá nhiều khoảng, các vạch chia quá sát nhau, ta giới hạn số khoảng chia tối đa là 20, số vạch chia là 21 suy ra số ô dữ liệu cho 1 trục là 21 ô. Tuy vậy, khi người dùng chia số khoảng ít hơn 20, thí dụ 10, thì ô thứ 11 (vạch chia thứ 11), cũng đã đạt tối đa của trục là 1. Các ô còn lại phải là giá trị lỗi #N/A hoặc ô trống. Tính chất của biểu đồ Line và XY scatter là bỏ qua ô trống hoặc ô lỗi, còn ô có giá trị "" hoặc 0 vẫn thể hiện. Công thức tính giá trị cho trục phải thể hiện lỗi bằng hàm NA().
Tuy nhiên, có 1 biện pháp để không thể hiện các giá trị dư thừa lên đồ thị bằng cách ấn định Min và Max của trục Y là cố định.Thí dụ ấn định Max trục là 1, thì các giá trị lớn hơn 1 sẽ không thể hiện lên biểu đồ, chính xác hơn, nó vượt ra ngoài giới hạn nhìn thấy của biểu đồ.
2. Tính toán:
- Khi chia trục làm n khoảng, ta sẽ có n+1 điểm dữ liệu, các điểm còn lại bằng NA(), hoặc cứ fill đầy giá trị.
- Giá trị Y: Điểm đầu tiên = 0, điểm n+1 có giá trị bằng 1
- Giá trị Y: mỗi điểm cách nhau 1 khoảng = 1/n. Các điểm lớn hơn n+1 cũng có thể tính tiếp, hoặc để NA()
- Giá trị X: từ điểm 1 đến điểm n+1, giá trị bằng nhau và bằng vị trí trục so với trục thật. Các điểm còn lại bằng NA() hoặc bằng luôn số đó.
3. Công thức trên sheet:
- Công thức X serie trục A:
U1 = 13.5
U2 =$U$1
Với L24 là số khoảng chia. Kéo công thức xuống dòng 22
- Tương tự công thức X các serie khác.
- Công thức Y serie trục A:
Z2 =(ROW()-2)/L$24. Kéo công thức xuống dòng 22
Y liên quan đến X, và số khoảng chia, nếu số khoảng chia nhỏ hơn 20, nó fill đủ 21 ô đến giá trị cuối lớn hơn 1, nhưng chỉ các giá trị Y <=1 mới thể hiện trên biểu đồ. Tương tự là công thức tính Y các trục khác.
4. Bổ sung trục chặn:
Do đồ thị chính chỉ vẽ 12 tháng tức là đến vị trí 12 của trục hoành, ta nên làm 1 trục giả chặn bên phải của đồ thị, ngăn cách các đường biểu diễn và các trục.
X = 12.5
T1 = 12.5
T2 =;$T$1 (dựa vào cột U, giá trị X serie trục A, số điểm bằng cột U, số khoảng chia bằng cột U)
Y tính theo Y trục A, số khoảng chia bằng số khoảng chia của trục A, để sau này khi dùng X Error Bar sẽ giống như Grid Line của trục thật, mà lại chạy theo trục A. Dùng luôn cột Z làm Y trục chặn.
5. Bổ sung trục X giả:
Trục X thật có Max = 25, trong khi biểu đồ chỉ vẽ tới 12, nếu định dạng label cho trục X thật, sẽ là số từ 0 đến 25, coi kỳ cục. Vả lại ta muốn label là tháng 1, tháng 2, … cơ. Do đó ta vẽ thêm 1 trục X giả ngắn 12 thôi. Dữ liệu như sau:
- Giá trị X: dùng cột A, từ 1 đến 12
- Giá trị Y: dùng cột K, tất cả zero.
B. VẺ TRỤC GIẢ
1. Vẽ trục chặn:
Vẽ trục chặn: Tô chọn và Copy T1:T22 & Z1:Z22. Click chọn đồ thị, vào menu Edit - Paste Speccial:
Đánh dấu Series Names in First Rows, X value in First Column, New Serie và Serie in Column, nhấn OK. Trục chặn đã được vẽ xong.
2. Vẽ các trục giả khác:
Tương tự như trục chặn, hãy copy 2 cột X trục 1 và Y trục 1, Paste Special vào đồ thị, ta sẽ có trục 1. Thực hiện tiếp cho các trục còn lại.
Định dạng màu tương ứng cho các trục, trục của serie nào định dạng màu cho giống serie đó.
Định dạng có tickmack cùng màu như sau:
Kết quả:
3. Vẽ các ErrorBar cho trục chặn:
Error bar để giả làm Major GridLine, hay quá! Click chọn trục chặn, format, trong tab X ErrorBar, chọn Minus, chọn Fix value, gõ 12.5:
Chọn màu cho các ErrorBar luôn:
Xoá bỏ các legend không cần thiết: Click chọn legends box, click cái nữa chọn từng legend, và nhấn delete.
4. Vẽ trục X giả:
Copy 2 cột A1:A13 và K1:K13, Paste special vào đồ thị. Định dạng màu phù hợp.
C. GÁN LABEL CHO TRỤC GIẢ:
1. Tính toán:
Mỗi trục có những giá trị cần hiển thị khác nhau, nên ta phải tính toán các giá trị tương ứng dùng làm label. Thí dụ trục giả cho Serie A: min = 0, Max = 800.000.000, chia 16 khoảng:
Label cho trục 1 ở cột AE:
AE2 = L22 (min)
AE3 =AE2+L$26
Fill xuống tới AE22. Từ ô AE19 trở đi, các giá trị > 800 triệu, nhưng yên tâm, nó không thể hiện vì vượt ra khỏi vùng nhìn thấy của biểu đồ.
Tương tự cho những label khác. Vì ta sẽ sử dụng Add-in XY Chart Labeler, mà công cụ này tự lấy định dạng số và màu của các cells chứa label, nên ta định dạng luôn:
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:
2. 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ả thứ nhất, vào Menu Tools như hình trên, chọn add chart labels:
Trong ô Select a label Range, chọn vùng AE2:AE22
Trong ô Label Position, chọn Right
Nhấn OK
Nếu các label quá lớn, chỉnh font size lại, nếu nó đè lên trục kế bên, hãy dời trục kế bên qua phải. Thí dụ trục 2 bị đè và đang ở vị trí 15.5, dời nó sang vị trí 17 chẳng hạn, bằng cách đơn giản nhất là thay 17 vào ô V1:
Lần lượt gán label cho các trục khác:
3. Gán label cho trục X giả:
Dùng cột B làm Label cho serie trục X giả. Định dạng màu label và Alignment quay 90 độ.
IV. TRANG TRÍ:
1. Tô màu nền, màu chữ:
Kiểm tra màu sắc, dộ tương phản giữa màu nền và màu chữ, nếu không rõ thì định dạng lại.
2. Đổi dạng biểu đồ:
Ta thấy 2 serie G và H đứng đơn độc và chồng lên các serie khác khó coi. Ta hãy đổi dạng biểu đồ khác cho dễ nhìn.
Click chuột phải vào Serie G, chọn change chart type, chọn loại Area. Tô màu lại.
Click chuột phải vào Serie H, chọn change chart type, chọn loại Column. Tô màu lại.
3. Đổi đơn vị tính:
Các label hàng trăm triệu và chục triệu chiếm khá nhiều chỗ trên biểu đồ, ta muốn chia cho 1 triệu và ghi chú là "triệu đồngâ€.
- Trước tiên chỉnh Min max trong vùng thông số trục, các label tự đổi theo.
- Chỉnh công thức quy đổi:
L2 = (C2-L$22)/L$25 sửa thành:
L2 = (C2/1000000-L$22)/L$25
Kéo xuống
Tương tự là cột M, N, và P
- Dùng công cụ vẽ 2 textbox, gõ vào "Triệu đồngâ€, định dạng màu chữ.
Trên đây là toàn bộ các hướng dẫn chi tiết để thực hiện đề bài. Mong rằng các bạn hiểu và vận dụng được tốt trong công việc.
Một số bài viết có liên quan:
1/ Hướng dẫn vẽ biểu đồ nhiều trục tung (P1)
2/ Phân tích tồn kho theo phương pháp ABC
3/ 30 mẹo siêu đơn giản giúp hoàn thiện biểu đồ (phần 1)
4/ 30 mẹo siêu đơn giản giúp hoàn thiện biểu đồ (phần 2)
5/ Đánh dấu điểm MAX và MIN trong biểu đồ Excel
6/ Biểu đồ đường hay phân tán?
7/ Kết hợp biểu đồ và thêm 1 trục phụ
8/ Nhãn danh mục không chen ngang vào biểu đồ
9/ Hướng dẫn từng bước vẽ biểu đồ Bubble
10/ Làm thế nào để đồ thị hoạt động hiệu quả?
Chỉnh sửa lần cuối bởi điều hành viên:
Upvote
0
