Xây dựng bảng điều khiển Excel tương tác bằng cách sử dụng điều khiển biểu mẫu (Không cần VBA)

Bảng thông tin tương tác là công cụ mạnh mẽ để trực quan hóa và phân tích dữ liệu lớn một cách linh hoạt trong Excel. Nó cung cấp một cách năng động để phân tích dữ liệu để có được thông tin chi tiết nhanh chóng. Điều khiển biểu mẫu của Excel là một tính năng tuyệt vời để tạo các trang tổng quan như vậy mà không cần VBA. Nó cho phép người dùng tương tác với dữ liệu thông qua các nút, thanh trượt, hộp tổ hợp và hộp kiểm. Trong bài viết này, chúng tôi sẽ hướng dẫn cách tạo trang tổng quan tương tác bằng các điều khiển biểu mẫu trong Excel.

Hãy xem xét tập dữ liệu bán hàng để hiển thị các Điều khiển biểu mẫu cần thiết nhằm tạo trang tổng quan tương tác.

Bước 1:Chuẩn bị bộ dữ liệu

• Chuyển đổi dữ liệu bán hàng thành bảng Excel. Chọn phạm vi dữ liệu.
• Đi tới Chèn tab>> chọn Bảng .
  • Chọn Bảng của tôi có tiêu đề>> nhấp vào OK .

• Đặt tên bảng là SalesData để dễ đọc hơn.

Bảng Excel cung cấp phạm vi động cho công thức và biểu đồ.

Bước 2:Chèn điều khiển biểu mẫu và liên kết nó với các ô

Dựa trên loại tập dữ liệu của bạn, bạn có thể chọn Điều khiển biểu mẫu có liên quan.

• Nút tùy chọn (để lọc theo vùng):
  • Đi tới Nhà phát triển tab>> từ Chèn>> chọn Nút tùy chọn .
  • Đặt biểu tượng Dấu cộng (+) trên khu vực bảng điều khiển.
  • Nhấp chuột phải vào Nút tùy chọn>> chọn Kiểm soát định dạng .

• • Trong Đối tượng định dạng hộp thoại:
    • Liên kết ô :Chọn ô I1 để lưu trữ các vùng đã chọn.
    • Chọn Tạo bóng 3-D>> nhấp vào OK .
  • Nhấp chuột phải vào Nút tùy chọn>> chọn Chỉnh sửa văn bản>> chèn Vùng (ví dụ:Bắc, Nam, Đông, Tây) từng cái một.

• Hộp tổ hợp (để lọc theo Sản phẩm):
  • Tab nhà phát triển → Chèn → Hộp tổ hợp .
  • Đặt nó trên khu vực bảng điều khiển.
  • Đi tới Nhà phát triển tab>> từ Chèn>> chọn Hộp tổ hợp .
  • Đặt biểu tượng Dấu cộng (+) trên khu vực bảng điều khiển.
  • Nhấp chuột phải vào Hộp tổ hợp>> chọn Kiểm soát định dạng .
  • Trong Đối tượng định dạng hộp thoại:
    • Phạm vi đầu vào :  Chọn danh sách sản phẩm (ví dụ:Sản phẩm A, Sản phẩm B, Sản phẩm C, Sản phẩm D).
    • Liên kết ô :Chọn ô J1 để lưu trữ các sản phẩm đã chọn.
    • Chọn Tạo bóng 3-D>> nhấp vào OK .

• Hộp kiểm (để chuyển đổi các số liệu như Doanh thu, Số lượng hàng bán và Lợi nhuận):
  • Đi tới Nhà phát triển tab>> từ Chèn>> chọn Hộp kiểm .
  • Đặt biểu tượng Dấu cộng (+) trên khu vực bảng điều khiển.
  • Nhấp chuột phải vào Hộp kiểm>> chọn Kiểm soát định dạng .
  • Trong Đối tượng định dạng hộp thoại:
    • Liên kết ô :Chọn ô K1 để lưu trữ các số liệu đã chọn.
    • Chọn Tạo bóng 3-D>> nhấp vào OK .
  • Nhấp chuột phải vào Nút tùy chọn>> chọn Chỉnh sửa văn bản>> chèn tên chỉ số (ví dụ:Doanh thu, Số lượng đã bán, Lợi nhuận) lần lượt.
  • Mỗi hộp kiểm trả về TRUE hoặc FALSE khi được liên kết với một ô (ví dụ:K1, K2, K3 tương ứng cho Doanh thu, Số lượng đơn vị đã bán và Lợi nhuận).

Bước 3:Tạo công thức động

Bạn có thể sử dụng các hàm động để liên kết dữ liệu bán hàng với các điều khiển định dạng nhằm làm cho trang tổng quan có tính tương tác. Chúng ta sẽ sử dụng các hàm LỌC, SWITCH và IF để trích xuất dữ liệu dựa trên các lựa chọn biểu mẫu một cách linh hoạt.

Sử dụng chức năng LỌC ĐỘNG

Lọc dữ liệu theo khu vực :

Chèn công thức sau vào ô đã chọn để lọc dữ liệu bán hàng dựa trên các khu vực đã chọn.

=FILTER(SalesData, SalesData[Region] = SWITCH(J1, 1, "North", 2, "South", 3, "East", 4, "West"))

• Bộ lọc hàm trích xuất bảng SalesData để hiển thị các hàng trong đó Vùng khớp với giá trị đã chọn trong ô J1.
• CHUYỂN ĐỔI hàm dịch giá trị số trong J1 (ví dụ:1, 2, 3 hoặc 4) thành tên vùng tương ứng (“Bắc”, “Nam”, “Đông” hoặc “Tây”). Chỉ những hàng phù hợp mới được đưa vào kết quả.

Lọc dữ liệu theo sản phẩm :

Chèn công thức sau vào ô đã chọn để lọc dữ liệu bán hàng dựa trên các sản phẩm đã chọn.

=FILTER(SalesData, SalesData[Product] = SWITCH(K1, 1, "Product A", 2, "Product B", 3, "Product C", 4, "Product D"))

Công thức này sẽ lọc dữ liệu bán hàng dựa trên việc lựa chọn các hộp tổ hợp. Chúng tôi đã chọn Sản phẩm A từ hộp tổ hợp nên nó sẽ trả về dữ liệu bán hàng của Sản phẩm A.

Lọc dữ liệu theo khu vực và sản phẩm :

Chèn công thức sau vào ô đã chọn để lọc dữ liệu bán hàng dựa trên các vùng và sản phẩm đã chọn.

=FILTER(SalesData, (SalesData[Region] = SWITCH(J1, 1, "North", 2, "South", 3, "East", 4, "West")) * (SalesData[Product] = SWITCH(K1, 1, "Product A", 2, "Product B", 3, "Product C", 4, "Product D")))

Công thức này sẽ lọc dữ liệu bán hàng dựa trên việc chọn hộp tổ hợp và nút tùy chọn. Chúng tôi đã chọn khu vực phía Nam và Sản phẩm B. Chức năng lọc sẽ chỉ trả về thông tin bán hàng của Sản phẩm B từ khu vực phía Nam.

Hiển thị số liệu động

Bạn có thể sử dụng hàm IF để hiển thị số liệu bán hàng dựa trên lựa chọn hộp kiểm.

Chèn logic điều kiện sau cho số liệu dựa trên giá trị hộp kiểm. Nếu bạn chọn bất kỳ hộp kiểm nào, nó sẽ trả về TRUE nếu không FALSE trong ô được liên kết. Dựa trên giá trị của ô được liên kết, hàm IF sẽ trả về kết quả.

Doanh thu :

Chèn công thức sau để có được tổng doanh thu.

=IF(L1, SUM(SalesData4[Revenue ($)]), "")

Để lấy tổng số từ dữ liệu bán hàng đã lọc, hãy chèn công thức sau:

Các công thức này sẽ tính tổng doanh thu nếu bạn chọn hộp kiểm Doanh thu.

Lợi nhuận :

Chèn công thức sau để có được tổng lợi nhuận.

=IF(M1, SUM(SalesData4[Profit ($)]), "")

Để lấy tổng số từ dữ liệu bán hàng đã lọc, hãy chèn công thức sau:

Các công thức này sẽ tính tổng lợi nhuận nếu bạn chọn hộp kiểm Lợi nhuận.

Số lượng đã bán :

Chèn công thức sau để tính tổng số đơn vị đã bán.

=IF(N1, SUM(SalesData4[Units Sold]), "")

Để lấy tổng số từ dữ liệu bán hàng đã lọc, hãy chèn công thức sau:

Các công thức này sẽ tính tổng tổng số đơn vị nếu bạn chọn hộp kiểm Số đơn vị đã bán.

Đầu ra:

Bước 4:Chèn biểu đồ động

• Chọn dữ liệu đã lọc.
• Đi tới Chèn tab>> nhấp vào Tất cả biểu đồ>> chọn Cột biểu đồ.

• Biểu đồ sẽ tự động cập nhật dựa trên Hộp tổ hợp và Nút tùy chọn . Biểu đồ này thể hiện doanh số bán Sản phẩm A tại khu vực phía Bắc.

• Thay đổi tùy chọn bộ lọc để xem cập nhật động của biểu đồ. Chúng tôi đã chọn khu vực phía Đông và Sản phẩm C.

Bước 5:Tùy chỉnh Trang tổng quan

• Chèn hình dạng:
  • Thêm các phần khác nhau theo yêu cầu trên trang tổng quan của bạn vào các phần riêng biệt như bộ lọc, biểu đồ và chỉ số.
• Định dạng:
  • Bạn có thể sử dụng màu sắc, phông chữ và kiểu nhất quán.
  • Bạn có thể sử dụng định dạng có điều kiện để đánh dấu.

Kết luận

Với các bước trên, bạn có thể tạo bảng thông tin tương tác với các điều khiển biểu mẫu trong Excel. Chúng tôi đã trình bày tất cả các bước quan trọng bằng cách tạo trang tổng quan tương tác, hấp dẫn trực quan bằng cách sử dụng tập dữ liệu lớn. Bạn có thể lọc và phân tích dữ liệu một cách linh hoạt, biến nó thành một công cụ mạnh mẽ để đưa ra quyết định. Đừng quên thử nghiệm các tính năng bổ sung của điều khiển biểu mẫu.