Bảng điều khiển 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 động trong Excel. Nó cung cấp một cách động để phân tích dữ liệu để 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 bảng điều khiển 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 nút bấm, thanh trượt, hộp tổ hợp và hộp kiểm. Trong bài viết này, chúng ta sẽ chỉ cách tạo bảng điều khiển tương tác với điều khiển biểu mẫu trong Excel.
Xét mộ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 để tạo bảng điều khiển tương tác.
Bước 1: Chuẩn bị Tập 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.
- Vào tab Chèn >> chọn Bảng.
- Chọn Bảng của tôi có tiêu đề >> nhấp 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 với Ô
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 liên quan.
- Nút tùy chọn (để lọc theo khu vực):
- Vào tab Nhà phát triển >> từ Chèn >> chọn Nút tùy chọn.
- Đặt Biểu tượng cộng (+) lê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 Định dạng điều khiển.
-
- Trong hộp thoại Định dạng Đối tượng:
- Liên kết ô: Chọn ô I1 để lưu trữ khu vực được chọn.
- Chọn Đổ bóng 3-D >> nhấp 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 Khu vực (ví dụ: Bắc, Nam, Đông, Tây) lần lượt.
- Trong hộp thoại Định dạng Đối tượng:
- 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ó lên khu vực bảng điều khiển.
- Vào tab Nhà phát triển >> từ Chèn >> chọn Hộp tổ hợp.
- Đặt Biểu tượng cộng (+) lê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 Định dạng điều khiển.
- Trong hộp thoại Định dạng Đối tượng:
- 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ữ sản phẩm được chọn.
- Chọn Đổ bóng 3-D >> nhấp OK.
- Hộp kiểm (để bật/tắt chỉ số như Doanh thu, Số lượng bán, Lợi nhuận):
- Vào tab Nhà phát triển >> từ Chèn >> chọn Hộp kiểm.
- Đặt Biểu tượng cộng (+) lên khu vực bảng điều khiển.
- Nhấp chuột phải vào Hộp kiểm >> chọn Định dạng điều khiển.
- Trong hộp thoại Định dạng Đối tượng:
- Liên kết ô: Chọn ô K1 để lưu trữ chỉ số được chọn.
- Chọn Đổ bóng 3-D >> nhấp 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 liên kết với ô (ví dụ: K1, K2, K3 cho Doanh thu, Số lượng bán và Lợi nhuận tương ứng).
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 biểu mẫu để làm cho bảng điều khiển tương tác. Chúng ta sẽ sử dụng các hàm FILTER, 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 động.
Sử dụng Hàm FILTER Động
Lọc Dữ liệu theo Khu vực:
Chèn công thức sau vào ô được chọn để lọc dữ liệu bán hàng dựa trên khu vực được chọn.
=FILTER(SalesData, SalesData[Khu vực] = SWITCH(J1, 1, "Bắc", 2, "Nam", 3, "Đông", 4, "Tây"))
- Hàm FILTER trích xuất bảng SalesData để hiển thị các hàng nơi Khu vực khớp với giá trị được chọn trong ô J1.
- Hàm SWITCH dịch giá trị số trong J1 (ví dụ: 1, 2, 3 hoặc 4) thành tên khu vực tương ứng ("Bắc," "Nam," "Đông" hoặc "Tây"). Chỉ các hàng khớp được bao gồm trong kết quả.
Lọc Dữ liệu theo Sản phẩm:
Chèn công thức sau vào ô được chọn để lọc dữ liệu bán hàng dựa trên sản phẩm được chọn.
=FILTER(SalesData, SalesData[Sản phẩm] = SWITCH(K1, 1, "Sản phẩm A", 2, "Sản phẩm B", 3, "Sản phẩm C", 4, "Sản phẩm D"))
Công thức này sẽ lọc dữ liệu bán hàng dựa trên lựa chọn của 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 & Sản phẩm:
Chèn công thức sau vào ô được chọn để lọc dữ liệu bán hàng dựa trên khu vực và sản phẩm được chọn.
=FILTER(SalesData, (SalesData[Khu vực] = SWITCH(J1, 1, "Bắc", 2, "Nam", 3, "Đông", 4, "Tây")) * (SalesData[Sản phẩm] = SWITCH(K1, 1, "Sản phẩm A", 2, "Sản phẩm B", 3, "Sản phẩm C", 4, "Sản phẩm D")))
Công thức này sẽ lọc dữ liệu bán hàng dựa trên lựa chọn của hộp tổ hợp và nút tùy chọn. Chúng tôi đã chọn khu vực Nam và Sản phẩm B. Hàm Filter sẽ chỉ trả về thông tin bán hàng của Sản phẩm B từ khu vực Nam.
Hiển thị Chỉ số Động
Bạn có thể sử dụng hàm IF để hiển thị các chỉ số bán hàng dựa trên lựa chọn hộp kiểm.
Chèn logic có điều kiện sau cho các chỉ số 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ó trả về TRUE ngược lại FALSE trong ô liên kết. Dựa trên giá trị của ô liên kết, hàm IF sẽ trả về kết quả.
Doanh thu:
Chèn công thức sau để lấy tổng doanh thu.
=IF(L1, SUM(SalesData4[Doanh thu ($)]), "")
Để lấy tổng từ dữ liệu bán hàng đã lọc, 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 để lấy tổng lợi nhuận.
=IF(M1, SUM(SalesData4[Lợi nhuận ($)]), "")
Để lấy tổng từ dữ liệu bán hàng đã lọc, 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 để lấy tổng số lượng bán.
=IF(N1, SUM(SalesData4[Số lượng bán]), "")
Để lấy tổng từ dữ liệu bán hàng đã lọc, chèn công thức sau:
Các công thức này sẽ tính tổng số lượng nếu bạn chọn hộp kiểm Số lượng bán.
Đầu ra:
Bước 4: Chèn Biểu đồ Động
- Chọn dữ liệu đã lọc.
- Vào tab Chèn >> 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 hiển thị bán hàng của Sản phẩm A ở khu vực Bắc.
- Thay đổi tùy chọn lọc để xem cập nhật động của biểu đồ. Chúng tôi đã chọn khu vực Đông và Sản phẩm C.
Bước 5: Tùy chỉnh Bảng điều khiển
- Chèn Hình dạng:
- Thêm các hình dạng khác nhau theo yêu cầu bảng điều khiển của bạn để tách các phần như bộ lọc, biểu đồ và chỉ số.
- Định dạng:
- Bạn có thể sử dụng màu sắc, font chữ và phong cách nhất quán.
- Bạn có thể sử dụng định dạng có điều kiện để làm nổi bật.
Kết luận
Với các bước trên, bạn có thể tạo bảng điều khiển tương tác với đ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 một bảng điều khiển tương tác, hấp dẫn về mặt trực quan sử dụng một 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 động, biến nó thành một công cụ mạnh mẽ cho việc ra quyết định. Đừng quên thử nghiệm với các tính năng bổ sung của điều khiển biểu mẫu.
Nhận bài tập Excel nâng cao miễn phí kèm lời giải!