Tạo sổ làm việc Excel tương tác không cần VBA:Hướng dẫn từng bước

Trong hướng dẫn này, chúng tôi sẽ chỉ cho bạn cách xây dựng các tệp tương tác này mà không cần bất kỳ VBA nào.

1. Chuẩn bị tập dữ liệu của bạn

Để tạo một tệp Excel tương tác, bạn phải dọn dẹp và cấu trúc tập dữ liệu của mình. Tập dữ liệu có cấu trúc và định dạng phù hợp là yếu tố quan trọng để tạo ra các hành vi tương tác hiệu quả.

• Làm sạch tập dữ liệu của bạn.
• Xóa các mục trùng lặp và dấu cách.
• Sửa các vấn đề về định dạng và loại dữ liệu.

Chuyển đổi dữ liệu sang bảng

• Chọn tập dữ liệu.
• Đi tới Chèn tab>> chọn Bảng .
• Kiểm tra Bảng của tôi có tiêu đề .
• Nhấp vào OK .

• Đổi tên bảng:
  • Đi tới Thiết kế bảng tab>> chọn Tên bảng và đặt tên phù hợp, chẳng hạn như Sales .

2. Sử dụng danh sách thả xuống xác thực dữ liệu

Danh sách thả xuống là thành phần cần thiết để tạo các tệp Excel tương tác. Danh sách thả xuống cho phép người dùng nhập dữ liệu nhanh chóng, nhất quán và có kiểm soát. Sau này, bạn có thể sử dụng nó để lọc dữ liệu, biểu đồ và KPI một cách tương tác.

Các bước:

Xây dựng danh sách người trợ giúp: Xây dựng danh sách người trợ giúp ở phía trên hoặc bên phải trang tính của bạn. Bạn có thể ẩn những thứ này sau.

• Chọn một ô và nhập công thức sau để liệt kê các vùng duy nhất:

=SORT(UNIQUE(Sales[Region]))

• Chọn một ô và nhập công thức sau để liệt kê tên sản phẩm:

=SORT(UNIQUE(Sales[Product]))

• Tùy chọn: Thêm “Tất cả” phía trên mỗi danh sách trước khi chèn công thức để tràn danh sách duy nhất.

Tạo danh sách thả xuống:

• Chọn ô nơi bạn muốn thả xuống (ví dụ:B2).
• Đi tới Dữ liệu tab>> chọn Xác thực dữ liệu .
• Trong Cho phép , chọn Danh sách .
• Trong Nguồn , hãy chọn danh sách khu vực từ cột trợ giúp.

• Nhấp vào OK .

• Làm theo các bước tương tự, tạo danh sách thả xuống cho Sản phẩm.
• Trong Cho phép , chọn Danh sách .
• Trong Nguồn , hãy chọn danh sách sản phẩm từ cột trợ giúp.

Mẹo: Bạn có thể tạo một phạm vi được đặt tên cho danh sách rồi sử dụng nó để xác thực dữ liệu.

3. Sử dụng Công thức động với menu thả xuống

Việc tích hợp các công thức động với trình đơn thả xuống sẽ tự động cập nhật KPI và lọc dữ liệu.

Tạo KPI:

• Tổng doanh thu:

=SUMIFS(
Sales[Revenue],
Sales[Region], IF($B$2="All","*", $B$2),
Sales[Product], IF($B$4="All","*", $B$4))

• Mức chiết khấu trung bình:

=AVERAGEIFS(
Sales[Discount],
Sales[Region], IF($B$2="All","*", $B$2),
Sales[Product], IF($B$4="All","*", $B$4))

Áp dụng định dạng phần trăm để có giao diện rõ ràng hơn.

• Tổng số đơn đặt hàng:

=COUNTIFS(
Sales[Region], IF($B$2="All","*", $B$2),
Sales[Product], IF($B$4="All","*", $B$4))

• Tổng số căn đã bán:

=SUMIFS(
Sales[Units],
Sales[Region], IF($B$2="All","*", $B$2),
Sales[Product], IF($B$4="All","*", $B$4))

Thử nghiệm tính tương tác:

• Chọn Khu vực và Sản phẩm từ danh sách thả xuống.
• KPI tự động cập nhật.

4. Tạo biểu đồ động với phạm vi được đặt tên

Tạo biểu đồ cập nhật tự động dựa trên lựa chọn của người dùng.

Dữ liệu đã lọc:

=FILTER(
CHOOSE({1,2}, Sales[Date], Sales[Revenue]),
IF($B$2="All", Sales[Region]<>"", Sales[Region]=$B$2) *
IF($B$4="All", Sales[Product]<>"", Sales[Product]=$B$4),
"No rows")

Xác định phạm vi được đặt tên động:

• Đi tới Công thức tab>> chọn Trình quản lý tên>> chọn Mới .
• Tên: Ngày lọc
• Đề cập đến:

=INDEX('Interactive Sheet'!$B$7#, ,1)

• Tên: Doanh thu đã lọc
• Đề cập đến:

=INDEX('Interactive Sheet'!$B$7#, ,2)

Tạo biểu đồ:

• Đi tới Chèn tab>> từ Biểu đồ >> chọn Biểu đồ đường .
• Nhấp chuột phải vào biểu đồ>> chọn Chọn dữ liệu .

• Đối với Giá trị chuỗi, hãy nhập:

='Interactive Sheet'!FilteredRevenue

• Đối với Nhãn Trục Ngang:

='Interactive Sheet'!FilteredDates

Thử nghiệm tính tương tác:

• Chọn khu vực phía Đông và tất cả các sản phẩm.
• Biểu đồ hiển thị tất cả dữ liệu có liên quan.

• Chọn một Khu vực cụ thể và một Sản phẩm cụ thể.
• Biểu đồ lọc ngay lập tức.
• Điều này phù hợp với mọi kết hợp không có VBA.

5. Sử dụng định dạng có điều kiện cho phản hồi trực quan

Làm nổi bật các giá trị chính hoặc lựa chọn của người dùng ngay lập tức bằng Định dạng có điều kiện.

Các bước:

• Chọn phạm vi dữ liệu của bạn.
• Truy cập Trang chủ tab>> chọn Định dạng có điều kiện>> chọn Quy tắc mới .
• Chọn Sử dụng công thức để xác định ô nào cần định dạng .
• Chèn công thức sau:

=AND(OR('Interactive Sheet'!$B$2="All", $C2='Interactive Sheet'!$B$2), OR('Interactive Sheet'!$B$4="All", $F2='Interactive Sheet'!$B$4))

• Chọn màu đánh dấu.
• Nhấp vào OK .

Chỉ những hàng phù hợp mới được đánh dấu, củng cố việc lựa chọn bộ lọc một cách trực quan.

6. PivotTable, Bộ cắt và Biểu đồ tương tác

PivotTable, PivotChart và Slicer là các tính năng tương tác cốt lõi trong Excel giúp việc lọc dữ liệu cực kỳ dễ dàng.

Tạo PivotTable:

• Chọn phạm vi dữ liệu của bạn.
• Đi tới Chèn tab>> chọn PivotTable .
• Chọn vị trí:Mới hoặc Bảng tính hiện có .
• Nhấp vào OK .

• Từ Trường PivotTable :
  • Kéo Sản phẩm đến Hàng khu vực.
  • Kéo Doanh thu đến Giá trị khu vực.

Tạo PivotChart:

• Chọn PivotTable.
• Đi tới Phân tích PivotTable tab>> chọn PivotChart .
• Chọn một Cột được nhóm biểu đồ.
• Nhấp vào OK .

Thêm bộ cắt tương tác:

• Đi tới Phân tích PivotTable tab>> chọn Chèn Bộ cắt .
• Chọn trường để lọc, chẳng hạn như:
  • Khu vực, Sản phẩm, Nhân viên bán hàng, v.v.
• Bạn có thể nhấp vào các nút trong bộ cắt để lọc biểu đồ và bảng ngay lập tức.

• Để giữ các bộ cắt và biểu đồ lại với nhau, bạn có thể nhóm chúng lại.
• Đặt các bộ cắt gần hoặc trên biểu đồ, chọn tất cả các đối tượng (biểu đồ và các bộ cắt), sau đó nhấp chuột phải và chọn Nhóm .

Kết nối nhiều PivotTable bằng Bộ cắt chia sẻ

• Tạo nhiều bảng tổng hợp từ cùng một nguồn dữ liệu.
• Chèn Bộ cắt .
• Nhấp chuột phải vào bộ cắt>> chọn Báo cáo kết nối .
• Kết nối bộ cắt với nhiều PivotTable.

• Bây giờ, hãy sao chép các bộ cắt và biểu đồ vào trang tổng quan của bạn.
• Bạn có thể ẩn trang tính PivotTable.

7. Sử dụng siêu liên kết để điều hướng

Để làm cho tệp Excel của bạn trông giống một ứng dụng có thể nhấp hơn, bạn có thể sử dụng siêu liên kết để điều hướng. Điều này cho phép người dùng chuyển đến các trang tính, biểu đồ hoặc bảng khác nhau.

Các bước:

• Đi tới Chèn tab>> từ Minh họa>> chọn Hình dạng>> chọn Hình chữ nhật .
• Đặt tên cho hình là “Dữ liệu bán hàng”.
• Nhấp chuột phải vào hình>> chọn Liên kết .

• Trong phần Chèn siêu liên kết hộp thoại:
• Chọn Đặt vào tài liệu này>> nhập tham chiếu ô A1>> chọn Bán hàng tờ giấy.
• Nhấp vào OK .

• Chọn Đặt vào tài liệu này>> nhập tham chiếu ô A3>> chọn PivotTable tờ giấy.
• Nhấp vào OK .

• Định dạng nó giống như một nút để có giao diện rõ ràng hơn.
• Các siêu liên kết điều hướng như thế này cho phép người dùng dễ dàng chuyển đổi giữa “Trang tổng quan” và các trang “Chi tiết” khác nhau.

Tệp Excel tương tác cuối cùng:

• Ví dụ bên dưới hiển thị tất cả các khu vực và sản phẩm “Phụ kiện” được chọn.
• Các lựa chọn cũng có thể được thực hiện từ bộ cắt.
• Tất cả dữ liệu được cập nhật tự động.

Mẹo

• Chúng tôi đã trình diễn cả biểu đồ động (sử dụng hàm LỌC) và PivotChart để hiển thị các phương pháp tương tác khác nhau. Bạn có thể chọn loại nào phù hợp nhất với nhu cầu của mình.
• Bạn cũng có thể khám phá Điều khiển biểu mẫu, nơi cung cấp nhiều tùy chọn tương tác hơn mà không yêu cầu VBA.
• Để tạo hộp tìm kiếm, bạn có thể sử dụng hàm XLOOKUP động.

Tải xuống sổ tay thực hành

Kết luận

Bằng cách sử dụng các tính năng và kỹ thuật này, bạn có thể tạo các tệp Excel tương tác phức tạp mà không cần bất kỳ VBA nào. Điều quan trọng là kết hợp nhiều kỹ thuật để có khả năng tương tác tối đa. Hãy nhớ luôn kiểm tra kỹ lưỡng các yếu tố tương tác của bạn và cung cấp hướng dẫn rõ ràng cho người dùng. Bằng thực hành, bạn sẽ có thể tạo các trang tổng quan và công cụ ấn tượng giúp phân tích dữ liệu trở nên mạnh mẽ và thân thiện với người dùng mà không cần phải viết một dòng mã VBA nào.