Bảng điều khiển Excel - Điều khiển tương tác

Nếu bạn có nhiều dữ liệu hơn để hiển thị trên trang tổng quan không vừa với một màn hình, bạn có thể chọn sử dụng các điều khiển Excel đi kèm như một phần của Excel Visual Basic. Các điều khiển thường được sử dụng nhất là thanh cuộn, nút radio và hộp kiểm. Bằng cách kết hợp những thứ này vào trang tổng quan, bạn có thể làm cho nó tương tác và cho phép người dùng xem các khía cạnh khác nhau của dữ liệu theo các lựa chọn có thể.

Bạn có thể cung cấp các điều khiển tương tác như thanh cuộn, hộp kiểm và nút radio trong trang tổng quan của mình để tạo điều kiện cho người nhận xem động các khía cạnh khác nhau của dữ liệu được hiển thị dưới dạng kết quả. Bạn có thể quyết định một bố cục cụ thể của trang tổng quan cùng với những người nhận và sử dụng cùng một bố cục sau đó trở đi. Các điều khiển tương tác trong Excel rất dễ sử dụng và không yêu cầu bất kỳ chuyên môn nào về Excel.

Các điều khiển tương tác trong Excel sẽ có sẵn trong tab DEVELOPER trên Ribbon.

Nếu bạn không tìm thấy tab DEVELOPER trên Ribbon, hãy làm như sau:

Bấm vào Tùy chỉnh ruy-băng trong hộp Tùy chọn Excel.
Chọn Tab chính trong hộp Tùy chỉnh ruy-băng.
Chọn hộp Nhà phát triển trong danh sách Tab chính.

Nhấp vào OK. Bạn sẽ tìm thấy tab DEVELOPER trên Ribbon.

Thanh cuộn trong Trang tổng quan

Một trong những tính năng của bất kỳ bảng điều khiển nào là mỗi thành phần trong bảng điều khiển càng nhỏ gọn càng tốt. Giả sử kết quả của bạn trông như sau:

Nếu bạn có thể trình bày bảng này bằng một thanh cuộn như dưới đây, thì việc duyệt qua dữ liệu sẽ dễ dàng hơn.

Bạn cũng có thể có Đường mục tiêu động trong Biểu đồ thanh với thanh cuộn. Khi bạn di chuyển thanh cuộn lên và xuống, Đường mục tiêu di chuyển lên và xuống và những thanh vượt qua Đường mục tiêu sẽ được đánh dấu.

Trong các phần sau, bạn sẽ học cách tạo một thanh cuộn và cách tạo một dòng mục tiêu động được liên kết với một thanh cuộn. Bạn cũng sẽ học cách hiển thị nhãn động trong thanh cuộn.

Tạo thanh cuộn

Để tạo thanh cuộn cho bảng, trước tiên hãy sao chép tiêu đề của các cột vào vùng trống trên trang tính như hình dưới đây.

Chèn thanh cuộn.
- Bấm vào tab NHÀ PHÁT TRIỂN trên Ruy-băng.
- Bấm vào Chèn trong nhóm Điều khiển.
- Nhấp vào biểu tượng Thanh cuộn bên dưới Điều khiển biểu mẫu trong danh sách biểu tượng thả xuống.

Đưa con trỏ đến cột I và kéo xuống để chèn thanh cuộn dọc.
Điều chỉnh chiều cao và chiều rộng của thanh cuộn và căn chỉnh nó với bảng.

Nhấp chuột phải vào thanh cuộn.
Nhấp vào Kiểm soát định dạng trong danh sách thả xuống.

Hộp thoại Định dạng Kiểm soát xuất hiện.

Bấm vào tab Điều khiển.
Nhập nội dung sau vào các hộp xuất hiện.

Nhấp vào nút OK. Thanh cuộn đã sẵn sàng để sử dụng. Bạn đã chọn ô O2 làm liên kết ô cho thanh cuộn, sẽ nhận các giá trị từ 0 - 36, khi bạn di chuyển thanh cuộn lên và xuống. Tiếp theo, bạn phải tạo bản sao dữ liệu trong bảng với tham chiếu dựa trên giá trị trong ô O2.
Trong ô K3, gõ như sau:

= OFFSET (Tóm tắt [@ [S. No.]], $ O $ 2,0).

Nhấn nút Enter. Điền vào các ô trong cột sao chép công thức.

Điền vào các ô trong các cột khác sao chép công thức.

Bảng động và có thể cuộn của bạn đã sẵn sàng để được sao chép vào trang tổng quan của bạn.

Di chuyển thanh cuộn xuống.

Như bạn có thể quan sát, giá trị trong liên kết ô - thanh cuộn thay đổi và dữ liệu trong bảng được sao chép dựa trên giá trị này. Tại một thời điểm, 12 hàng dữ liệu được hiển thị.

Kéo thanh cuộn xuống dưới cùng.

12 hàng cuối cùng của dữ liệu được hiển thị dưới dạng giá trị hiện tại là 36 (như được hiển thị trong ô O2) và 36 là giá trị lớn nhất mà bạn đã đặt trong hộp thoại Điều khiển biểu mẫu.

Bạn có thể thay đổi vị trí tương đối của bảng động, thay đổi số hàng được hiển thị tại một thời điểm, liên kết ô với thanh cuộn, v.v. dựa trên yêu cầu của bạn. Như bạn đã thấy ở trên, chúng cần được đặt trong hộp thoại Điều khiển định dạng.

Tạo đường mục tiêu động và tương tác

Giả sử bạn muốn hiển thị khu vực bán hàng thông minh trong 6 tháng qua. Bạn cũng đã đặt mục tiêu cho mỗi tháng.

Bạn có thể làm như sau:

Tạo một biểu đồ cột hiển thị tất cả thông tin này.
Tạo Dòng Mục tiêu trên các cột.
Làm cho Dòng mục tiêu trở nên tương tác với một thanh cuộn.
Làm cho Đường mục tiêu động thiết lập các giá trị mục tiêu trong dữ liệu của bạn.
Đánh dấu các giá trị đáp ứng mục tiêu.

Tạo biểu đồ cột hiển thị tất cả thông tin này

Chọn dữ liệu. Chèn biểu đồ cột theo cụm.

Tạo Dòng Mục tiêu trên các cột

Thay đổi loại biểu đồ thành kết hợp. Chọn loại biểu đồ làm Đường cho chuỗi Mục tiêu và Cột theo cụm cho phần còn lại của chuỗi.

Tạo bảng cơ sở cho Dòng mục tiêu. Bạn sẽ làm cho điều này động sau.

Thay đổi các giá trị chuỗi dữ liệu cho Dòng mục tiêu thành cột Mục tiêu trong bảng trên.

Nhấp vào nút OK.

Thay đổi cách phối màu cho Cột được Nhóm. Thay đổi Dòng Mục tiêu thành một đường chấm màu xanh lá cây.

Làm cho Dòng mục tiêu trở nên tương tác với một thanh cuộn

Chèn một thanh cuộn và đặt nó bên dưới biểu đồ và kích thước nó kéo dài từ tháng Giêng đến tháng Sáu.
Nhập các tham số của thanh cuộn vào hộp thoại Điều khiển Định dạng.

Tạo một bảng có hai cột - Tháng và Mục tiêu.
Nhập các giá trị dựa trên bảng dữ liệu và liên kết ô thanh cuộn.

Bảng này hiển thị Tháng và Mục tiêu tương ứng dựa trên vị trí thanh cuộn.

Làm cho Dòng mục tiêu động thiết lập các giá trị mục tiêu trong dữ liệu của bạn

Bây giờ, bạn đã thiết lập để làm cho Đường mục tiêu của bạn động.

Thay đổi giá trị cột Mục tiêu trong bảng cơ sở bạn đã tạo cho Dòng Mục tiêu bằng cách nhập = $ G $ 12 vào tất cả các hàng.

Như bạn đã biết, ô G12 hiển thị giá trị Mục tiêu động.

Như bạn có thể quan sát, Dòng Mục tiêu di chuyển dựa trên thanh cuộn.

Đánh dấu các giá trị đáp ứng mục tiêu

Đây là bước cuối cùng. Bạn muốn làm nổi bật các giá trị đáp ứng mục tiêu tại bất kỳ thời điểm nào.

Thêm các cột vào phía bên phải của bảng dữ liệu của bạn - Kết quả phía Đông, Kết quả phía Bắc, Kết quả phía Nam và Kết quả phía Tây.
Trong ô H3, nhập công thức sau:

= IF (D3> = $ G $ 12, D3, NA ())

Sao chép công thức vào các ô khác trong bảng. Thay đổi kích thước bảng.

Như bạn có thể quan sát, các giá trị trong các cột - Kết quả phía Đông, Kết quả phía Bắc, Kết quả phía Nam và Kết quả phía Tây thay đổi động dựa trên thanh cuộn (tức là Giá trị mục tiêu). Các giá trị lớn hơn hoặc bằng Mục tiêu được hiển thị và các giá trị khác chỉ là # N / A.

Thay đổi Phạm vi dữ liệu biểu đồ để bao gồm các cột mới được thêm vào bảng dữ liệu.
Nhấp vào Thay đổi loại biểu đồ.
Làm cho chuỗi Mục tiêu là Dòng và phần còn lại là Cột Cụm.
Đối với chuỗi dữ liệu mới được thêm vào, hãy chọn Trục phụ.
Định dạng chuỗi dữ liệu theo cách sao cho chuỗi East, North, South và West có màu tô là màu cam và chuỗi East-Results, North-Results, South-Results và WestResults có màu tô xanh lục.
Nhập Nhãn dữ liệu cho Dòng mục tiêu và làm cho nó động với tham chiếu ô đến giá trị Tháng trong bảng dữ liệu động.

Biểu đồ của bạn với Đường mục tiêu động đã sẵn sàng để đưa vào trang tổng quan.

Bạn có thể xóa trục phụ vì nó không bắt buộc. Khi bạn di chuyển thanh cuộn, Dòng mục tiêu sẽ di chuyển và các Thanh sẽ được đánh dấu tương ứng. Dòng Mục tiêu cũng sẽ có Nhãn hiển thị Tháng.

Các nút tùy chọn Excel (Radio)

Các nút radio thường được sử dụng để chọn một tùy chọn từ một nhóm tùy chọn nhất định. Nó luôn được mô tả bằng một vòng tròn nhỏ, sẽ có một dấu chấm trong đó khi được chọn. Khi bạn có một tập hợp các nút radio, bạn chỉ có thể chọn một trong số chúng.

Trong Excel, các nút Radio được gọi là Nút tùy chọn.

Bạn có thể sử dụng các Nút tùy chọn Excel trong biểu đồ để chọn các chi tiết dữ liệu cụ thể mà người đọc muốn xem. Ví dụ, trong ví dụ ở phần trước, bạn đã tạo một thanh cuộn để lấy Dòng mục tiêu động với các giá trị mục tiêu dựa trên Tháng. Bạn có thể sử dụng các Nút tùy chọn để chọn Tháng và do đó làm giá trị mục tiêu, và đặt Đường mục tiêu dựa trên giá trị mục tiêu. Sau đây sẽ là các bước -

Tạo một biểu đồ cột hiển thị tất cả thông tin này.
Tạo Dòng Mục tiêu trên các cột.
Làm cho Dòng mục tiêu tương tác với các Nút tùy chọn.
Làm cho Đường mục tiêu động thiết lập các giá trị mục tiêu trong dữ liệu của bạn.
Đánh dấu các giá trị đáp ứng mục tiêu.

Bước 1 và bước 2 tương tự như trong trường hợp trước. Đến cuối bước thứ hai, bạn sẽ có biểu đồ sau.

Làm cho Dòng mục tiêu tương tác với các Nút tùy chọn

Chèn một nút tùy chọn.
- Bấm vào tab NHÀ PHÁT TRIỂN trên Ruy-băng.
- Bấm vào Chèn trong nhóm Điều khiển.
- Nhấp vào biểu tượng Nút tùy chọn bên dưới Điều khiển biểu mẫu trong danh sách biểu tượng thả xuống.

Đặt nó ở góc trên cùng bên phải của biểu đồ.

Nhấp chuột phải vào nút Tùy chọn. Nhấp vào tùy chọn Kiểm soát Định dạng trong danh sách thả xuống.

Nhập các tham số của Nút Tùy chọn trong hộp thoại Đối tượng Định dạng, trong tab Điều khiển.

Ô F10 được liên kết với Nút tùy chọn. Tạo 5 bản sao của Nút tùy chọn theo chiều dọc.

Như bạn có thể quan sát, tất cả các Nút tùy chọn có cùng tên, được gọi là Tên phụ đề. Tuy nhiên, bên trong Excel sẽ có các tên khác nhau cho các Nút tùy chọn này, bạn có thể xem các tên này trong hộp Tên. Hơn nữa, vì Nút tùy chọn 1 được đặt để liên kết với ô F10, tất cả các bản sao cũng tham chiếu đến cùng một ô.

Nhấp vào bất kỳ Nút tùy chọn nào.

Như bạn có thể quan sát, số trong ô được liên kết sẽ thay đổi thành số sê-ri của Nút tùy chọn. Đổi tên các Nút tùy chọn thành tháng 1, 2, 3, 4, 5 và 6.

Tạo một bảng có hai cột - Tháng và Mục tiêu. Nhập các giá trị dựa trên bảng dữ liệu và liên kết ô thanh cuộn.

Bảng này hiển thị Tháng và Mục tiêu tương ứng dựa trên Nút Tùy chọn đã chọn.

Làm cho Dòng mục tiêu động thiết lập các giá trị mục tiêu trong dữ liệu của bạn

Bây giờ, bạn đã thiết lập để làm cho Đường mục tiêu của bạn động.

Thay đổi giá trị cột Mục tiêu trong bảng cơ sở bạn đã tạo cho Dòng Mục tiêu bằng cách nhập = $ G $ 12 vào tất cả các hàng.

Như bạn đã biết, ô G12 hiển thị giá trị Mục tiêu động.

Như bạn có thể quan sát, Dòng Mục tiêu được hiển thị dựa trên Nút Tùy chọn đã chọn.

Đánh dấu các giá trị đáp ứng mục tiêu

Đây là bước cuối cùng. Bạn muốn làm nổi bật các giá trị đáp ứng mục tiêu tại bất kỳ thời điểm nào.

Thêm các cột vào phía bên phải của bảng dữ liệu của bạn - Kết quả phía Đông, Kết quả phía Bắc, Kết quả phía Nam và Kết quả phía Tây.
Trong ô H3, nhập công thức sau:

= IF (D3> = $ G $ 12, D3, NA ())

Sao chép công thức vào các ô khác trong bảng. Thay đổi kích thước bảng.

Thay đổi Phạm vi dữ liệu biểu đồ để bao gồm các cột mới được thêm vào bảng dữ liệu.
Nhấp vào Thay đổi loại biểu đồ.
Làm cho chuỗi Mục tiêu là Dòng và phần còn lại là Cột Cụm.
Đối với chuỗi dữ liệu mới được thêm vào, hãy chọn Trục phụ.
Định dạng chuỗi dữ liệu theo cách sao cho chuỗi East, North, South và West có màu tô là màu cam và chuỗi East-Results, North-Results, South-Results và WestResults có màu tô xanh lục.

Thêm Nhãn dữ liệu động vào Dòng mục tiêu với giá trị từ ô $ G $ 12.
Xóa trục phụ vì nó không bắt buộc.
Trong tab CHẾ ĐỘ XEM trên Ruy-băng, bỏ chọn hộp Đường lưới.
Thay đổi tùy chọn Nhãn thành Cao trong các tùy chọn Trục Định dạng. Điều này sẽ dịch chuyển Nhãn trục dọc sang bên phải, làm cho Nhãn dữ liệu đường mục tiêu của bạn dễ thấy.

Biểu đồ của bạn với Đường mục tiêu động và các Nút tùy chọn đã sẵn sàng để đưa vào trang tổng quan.

Khi bạn chọn Nút tùy chọn, Dòng mục tiêu được hiển thị theo giá trị mục tiêu của Tháng đã chọn và các Thanh sẽ được đánh dấu tương ứng. Dòng Mục tiêu cũng sẽ có Nhãn dữ liệu hiển thị giá trị mục tiêu.

Hộp kiểm Excel

Hộp kiểm thường được sử dụng để chọn một hoặc nhiều tùy chọn từ một nhóm tùy chọn nhất định. Hộp kiểm luôn được mô tả bằng các hình vuông nhỏ, sẽ có dấu tích khi được chọn. Khi bạn có một bộ Hộp kiểm, bạn có thể chọn bất kỳ số nào trong số chúng. Ví dụ,

Bạn có thể sử dụng Hộp kiểm Excel trong biểu đồ để chọn dữ liệu cụ thể mà người đọc muốn xem. Ví dụ, trong ví dụ ở phần trước, bạn đã tạo biểu đồ cột hiển thị dữ liệu của 4 khu vực - Đông, Bắc, Nam và Tây. Bạn có thể sử dụng Hộp kiểm để chọn Khu vực mà dữ liệu được hiển thị. Bạn có thể chọn bất kỳ số lượng Khu vực cùng một lúc.

Bạn có thể bắt đầu với bước cuối cùng của phần trước -

Chèn Hộp kiểm.
- Bấm vào tab NHÀ PHÁT TRIỂN trên Ruy-băng.
- Bấm vào Chèn trong nhóm Điều khiển.
- Nhấp vào biểu tượng Hộp kiểm bên dưới Điều khiển biểu mẫu trong danh sách biểu tượng thả xuống.

Đặt nó ở góc trên cùng bên trái của biểu đồ.
Thay đổi tên của Hộp kiểm thành Đông.

Nhấp chuột phải vào hộp kiểm. Nhấp vào Kiểm soát định dạng trong danh sách thả xuống.
Nhập các tham số Hộp kiểm trong hộp thoại Điều khiển định dạng, trong tab Điều khiển.

Nhấp vào nút OK. Bạn có thể quan sát thấy rằng trong ô được nối kết C19, TRUE sẽ được hiển thị nếu bạn chọn Hộp kiểm và FALSE sẽ được hiển thị nếu bạn bỏ chọn Hộp kiểm.
Sao chép Hộp kiểm và dán 3 lần theo chiều ngang.
Thay đổi tên thành Bắc, Nam và Tây.

Như bạn có thể quan sát, khi bạn sao chép Hộp kiểm, ô được liên kết vẫn giữ nguyên đối với Hộp kiểm đã sao chép. Tuy nhiên, vì Hộp kiểm có thể có nhiều lựa chọn, bạn cần làm cho các ô được liên kết khác nhau.