Tương tác với biểu đồ bằng Combo Box hoặc CheckBox (1 người xem)

[BNTT]

1. Tương tác với biểu đồ bằng Combo Box

Bạn có muốn để cho người khác có thể chọn xem mỗi lần một serie nào đó trong biểu đồ của bạn? Ví dụ sau đây sẽ hướng dẫn cho bạn dùng một Combo Box (Dropdown Box) để quyết định serie nào sẽ hiển thị trên biểu đồ.

Giả sử bạn muốn vẽ biểu đồ cho dữ liệu sau đây, trong Sheet Chart By Combo Box của bảng tính có tên là ChartByComboBoxOrCheckBox.xls, và đang nằm ở dãy ô A59

Dùng dãy H2:H4 để nhập 3 giá trị Y1, Y2 và Y3. Nếu như Forms Toolbar chưa có sẵn, bạn nhấn chuột phải vào đâu đó trên thanh menu rồi chọn Forms từ cái popup menu mở ra, sau đó nhấn vào Combo Box (hoặc List Box cũng được), rồi dùng chuột vẽ công cụ này vào trong bảng tính. Nhấn phải vào Combo Box, chọn Format từ popup menu. Thiết lập cho Input Range là $H$2:$H$4 và Cell link là $H$1. Nếu làm đúng, khi nhấn vào Combo Box, nó phải mở ra danh sách gồm có Y1, Y2 và Y3:

__________

Tại ô E5, bạn nhập công thức =OFFSET(A5,0,$H$1), ròi copy nó xuống hết ô E9. Công thức này sẽ điền Y1, Y2 hoặc Y3 cùng với những giá trị liên quan đến nó vào E5:E9 mỗi khi bạn chọn một giá trị cho Y từ Combo Box.

Chọn A5:A9, nhấn Ctrl trong khi chọn tiếp E5:E9 (nghĩa là bạn chọn 2 dãy ô không liền nhau), rồi gọi Chart Wizard ra và vẽ biểu đồ với bất kỳ kiểu nào bạn thích. Ví dụ, tôi chọn kiểu Line Chart, một kiểu đơn giản nhất.

Bạn đừng ngạc nhiên khi thấy trong biểu đồ chỉ vẽ một serie, trong khi dữ liệu của bạn có đến 3 serie (Y1, Y2, Y3). Đó là do bạn đã sử dụng Combo Box để quyết định serie nào sẽ hiển thị trên biểu đồ. Muốn xem serie nào, bạn chọn nó từ Combo Box.

Hình sau đây minh họa đầy đủ những gì tôi đã trình bày trong bài này, cùng một biểu đồ khi tôi chọn Y3 từ Combo Box.

​

 

[BNTT]

2. Tương tác với biểu đồ bằng Check Box

Ở bài trên, bạn đã biết dùng một Combo Box (Dropdown Box) để quyết định serie nào sẽ hiển thị trên biểu đồ. Nhưng mỗi lần chỉ được phép chọn một serie. Bây giờ bạn muốn làm sao đó để có thể dùng chuột chọn xem một serie, hay 2 serie, hay tất cả các serie có trong biểu đồ? Mời bạn xem bài sau đây.

Tôi vẫn sử dụng dữ liệu trong bảng tính có tên là ChartByComboBoxOrCheckBox.xls đã sử dụng trong bài trước, nhưng copy sang một Sheet khác: Chart By CheckBox, và dữ liệu vẫn nằm ở dãy ô A59

Nếu bạn đã tắt cái Forms Toolbar, bạn nhấn chuột phải vào đâu đó trên thanh menu rồi chọn Forms từ cái popup menu mở ra, sau đó nhấn vào Check Box rồi dùng chuột vẽ từng cái Check box trên đầu các cột Y1, Y2 và Y3 trong bảng tính. Nhấn phải vào mỗi Check Box, chọn Format từ popup menu, rồi Cell link tương ứng cho từng cái. Trong trường hợp này, tôi đặt Cell link cho Y1, Y2 và Y3 lần lượt là $B$3, $C$3, $D$3.

Tạo ô E6, nhập công thức: =IF(B$3,B5,""), copy công thức sang F6 và G6.

Tại ô E7, nhập công thức: =IF(B$3,B6,NA()), copy công thức này cho cả dãy E6:G9.

Nếu bạn làm đúng như tôi đã trình bày, khi bạn nhấn chọn kích hoạt cả 3 Check Box, các ô tương ứng ở B3, C3 và D3 có giá trị TRUE, và các công thức vừa tạo ra ở trên sẽ trả về một bản sao y hệt dữ liệu gốc ở B59 trong dãy E5:G9; còn nếu bạn bỏ chọn các Check Box, các ô tương ứng ở B3, C3 và D3 có giá trị FALSE, và các công thức vừa tạo ra ở trên sẽ trả về những ô rỗng ở E5:G5, và giá trị lỗi #NA ở E6:G9. Hình dưới đây minh họa những gì hiển thị trên bảng tính khi tôi kích hoạt Check Box ở Y1, Y2 và không kích hoạt Check Box ở Y3:

Chọn A5:A9, nhấn Ctrl và chọn tiếp E5:E9, rồi tạo một biểu đồ bằng công cụ Chart Wizard với bất kỳ kiểu gì bạn thích. Ở đây, tôi chọn loại Line Chart. Và bạn có thể "bật/tắt" các Check Box để xem thành quả của mình.

Khi tôi kích hoạt cả 3 Check Box, biểu đồ của tôi sẽ như thế này:

Bỏ kích hoạt 2 Check Box ở Y1 và Y3, chỉ để lại Y2, biểu đồ của tôi sẽ như thế này:

​

 

[BNTT]

3. Biểu đồ tọa độ song song tương tác (Interactive Parallel Coordinates Chart)

Thú thật, tôi không biết phải dịch cái chữ "Interactive Parallel Coordinates Chart" ra làm sao cho dễ hiểu. Đại khái nó là một biểu đồ của nhiều serie, nhưng hầu hết các serie đều được làm mờ đi, ngoại trừ những serie cần nhấn mạnh (và sẽ được xác định từ những Combo Box) thì đậm lên. Chẳng hạn như biểu đồ này:

Để hiểu nó là như thế nào, và làm sao để tạo ra một biểu đồ như vậy, mời các bạn xem ví dụ sau đây.​

1. Chuẩn bị dữ liệu

Giả sử tôi có một bảng tính, trình bày lượng người truy cập trong mỗi năm của 8 trang web (tạm thời tôi cho ẩn từ dòng 1 đến dòng 3 và từ cột B đến cột F, để dễ xem):

Yêu cầu của bảng tính này là vẽ ra một biểu đồ so sánh lượng người truy cập hằng năm của những trang web đó. Vẽ thì vẽ hết, nhưng mỗi lần thì chỉ so sánh một cặp với nhau thôi, serie của cặp này sẽ hiện lên và có màu khác nhau, còn những serie khác thì mờ đi.

Muốn so sánh chính xác, ta phải quy chuẩn các dữ liệu. Trước tiên, tôi xác định con số lớn nhất và con số nhỏ nhất của mỗi năm trong hai hàng đầu tiên (1 và 2). Tại G1, tôi nhập công thức =MAX(G5:G12), và tại G2 là =MIN(G5:G12), rồi tôi điền cả hai công thức này ngang qua đến cột K:

Tiếp tục, tôi quy chuẩn dữ liệu trong dãy G5:K12 vào dãy B5:F12. Tôi chọn dãy G4:K4, và copy tiêu đề hàng (các năm) này sang B4:F4. Tại B5, tôi đặt công thức =(G5-G$2)/(G$1-G$2), rồi điền công thức vào cả dãy B5:F12. Công thức này sẽ trả về một giá trị nằm trong khoảng từ 0 (thấp nhất) đến 1 (cao nhất) cho mỗi giá trị tương ứng trong dãy G5:K12:

Tôi chọn L1 và L2, đặt cho chúng có giá trị lần lượt là 1 và 5. Rồi trở lui về A1, tôi nhập công thức sau: =INDEX(A$5:A$12,$L1) và điền công thức này vào trong dãy A1:F2. Nhiệm vụ của công thức này là "bốc" hai hàng dữ liệu được chọn ở A5:F12 mang lên trên A1:F2. Lúc này, tôi đang quy định ở L1 và L2 là 1 và 5, nên trong hình sau, bạn sẽ thấy nội dung trong A1:F1 và A2:F2 giống y chang nội dung của hàng thứ 1 (GPE...)và thứ 5 (APOLI...) trong A5:F12. Bạn thử thay đổi con số trong L1 và L2, sẽ thấy A1:F2 thay đổi như thế nào.

Nói vậy thôi, chẳng lẽ bạn chọn L1 và L2 rồi nhập số vào? Tiếp theo đây tôi sẽ bày cho bạn dùng Combo Box để làm việc đó.​

(còn tiếp)

 

[BNTT]

Interactive Parallel Coordinates Chart

(tiếp theo)

2. Tạo các Combo Box

Tôi sẽ vẽ một cái Combo Box vào trong bảng tính. Và thiết lập Input range, Cell link cho nó như hình sau:

Tôi vẽ thêm một Combo nữa, đặt bên cạnh cái vừa rồi. Input range cho nó thì giống y như của cái vừa vẽ, chỉ khác cái Cell link lần này là $L$2. Tôi có hai cái Combo box dùng để chọn các "trang web" cần xem như sau đây (ở trên mỗi Combo box, tôi ghi thêm một dòng quy định màu cho serie của "trang web" trên biểu đồ so sánh, cái bên trái cho serie màu đỏ, còn cái bên phải là cho serie màu xanh, giống như hai màu tôi đã tô cho nội dung ở A1:F1 và A2:F2):

__________

(còn tiếp)

 

[BNTT]

(tiếp theo)

3. Vẽ biểu đồ

Tôi chọn dãy A4:F12, chạy Chart Wizard, chọn Line Chart ở bước 1, sang bước 2, tôi quy định rằng các serie vẽ theo các hàng (Series in Rows), và nhấn Finish luôn cho lẹ. Tôi có được biểu đồ như sau:

Xấu chi lạ! Tôi phải làm một vài thay đổi cho nó: Bỏ cái khung (border) và màu tô (fill) của vùng vẽ (plot area); bỏ luôn các đường gióng ngang (horizontal gridlines) nhưng thêm vào các đường gióng dọc (vertical gridlines); đặt giá trị maximum cho trục Y là 1, và thiết lập cho giá trị đầu tiên của trục X nằm sát vào trục Y, không cần chừa cái khoảng trắng ở đầu làm gì. Tôi cũng định dạng cho tất cả các serie, các con số ở trục Y trong biểu đồ đều có màu xám nhạt. Sau cùng, tôi dời cái chú thích cho các serie lên trên đầu biểu đồ, rồi kéo cả cái biểu đồ giãn theo chiều dọc một tí, tôi có kết quả sau đây:

Có lẽ bạn đang thắc mắc là, nếu tôi cho các serie màu xám nhạt hết, thì làm cách gì để mỗi khi chọn một "trang web" trong hai cái Combo Box, serie của "trang web" được chọn sẽ đậm lên, và có hai màu khác nhau? Dùng VBA?

Thưa không. Bài này tôi chẳng dùng tí VBA nào hết. Mà dùng một cái "mánh" sau đây:

Tôi copy dãy A1:F2, rồi chọn cái biểu đồ, gọi lệnh Paste Special ra, tôi "dán" dãy đang chọn vào biểu đồ như là thêm những serie mới vào đó. Đây là một tuyệt chiêu của Paste Special mà có lẽ ít người biết đến.

Sau đó, trong biểu đồ, tôi chọn hai cái serie mới thêm vào, làm cho một cái có màu xanh và một cái có màu đỏ, và cả hai đều đậm hơn một tí (đường kẻ mập hơn). Biểu đồ của tôi hiện giờ giống như sau:

Để cho đẹp hơn tí nữa, và rõ ràng hơn, tôi xóa hết chú thích của những serie có màu xám (cùng màu với nhau thì có chú thích cũng như không!), chỉ chừa lại hai chu thích cho serie màu xanh và màu đỏ. Để làm việc này, bạn phải chọn cái phần text ở trong mỗi mục chú thích (chứ đừng chọn cái hình dạng của serie, vì không khéo thì bạn sẽ gỡ bỏ luôn serie đó ra khỏi biểu đồ) và nhấn Delete. Sau đó, tôi chỉnh lại vị trí của 2 cái chú thích còn lại cho cân một tí với biểu đồ. Và, cuối cùng thì tôi đã có một cái gọi là Interactive Parallel Coordinates Chart!

Như đã nói ở đầu bài, tôi không biết phải dịch cái chữ "Interactive Parallel Coordinates Chart" ra tiếng Việt như thế nào cho dễ hiểu. Cứ nghĩ là viết xong bài này, tôi sẽ hiểu hơn. Nhưng... cũng chẳng hơn gì!

Tuy nhiên, qua bài này, tôi học thêm được rất nhiều về biểu đồ, kể cả cái tuyệt chiêu Paste Special mà tôi nói tới khi nãy. Dùng loại biểu đồ này để làm nổi bật sự so sánh giữa các phần tử với nhau thì rất thích hợp. Ví dụ, bạn giữ nguyên đường màu xanh của GPE lại, chỉ thay đổi đường màu đỏ thôi (bằng cái Combo box), quả là rất thú vị!
​

 

[ptm0412]

Góp với BNTT 1 thí dụ về tương tác với biểu đồ bằng combobox (bài 1) thay đổi serie dữ liệu, thí dụ này dùng phân tích giá thành sản xuất.
Nếu vừa ý, BNTT có thể sử dụng file này cho bài 1 trong Excel Chart Project, minh họa cho oai.

Tuy nhiên, qua bài này, tôi học thêm được rất nhiều về biểu đồ, kể cả cái tuyệt chiêu Paste Special mà tôi nói tới khi nãy

Mình cũng thế, và còn 2 cách nữa mình mới mày mò ra khi viết bài cho ECP mà lại nhanh:
- Copy bằng Ctrl C, click chọn biểu đồ, Ctrl V. Cách này có đôi khi lộn nhào giữa serie by column và serie by row, nhưng nếu cùng loại serie thì OK.
- Kéo vùng dữ liệu và thả vào biểu đồ, sẽ xuất hiện hộp thoại như khi Paste Special, nhưng nhanh hơn vào Menu Edit.

 

[m&h]

Đã tim được cái đúng ý nên tải về học hỏi thì thánh
ptm0412

Excel Ordinary Member lại khóa cái w/sheets không thể châm cứu được...thanks so much

 

[ptm0412]

Làm gì có chuyện khoá sheet? Cả đời tôi viết bài trên GPE không hề khoá sheet, khoá VBA các loại.