Excel cung cấp các tính năng tích hợp mạnh mẽ cho phép bạn tạo file động và tương tác—như báo cáo, dashboard và form—mà không dùng bất kỳ VBA nào. Bạn có thể tạo báo cáo tương tác cập nhật dữ liệu ngay lập tứ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 file 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
Để tạo file Excel tương tác, bạn phải làm sạch và cấu trúc tập dữ liệu. Tập dữ liệu được định dạng tốt và có cấu trúc là rất quan trọng để tạo hành vi tương tác hiệu quả.
- Làm sạch tập dữ liệu.
- Xóa trùng lặp và khoảng trắng.
- Sửa lỗi định dạng và kiểu dữ liệu.
Chuyển Dữ Liệu Thành Bảng
- Chọn tập dữ liệu.
- Vào tab Insert >> chọn Table.
- Chọn My table has headers.
- Click OK.
- Đổi tên bảng:
- Vào tab Table Design >> chọn Table Name và đặt tên phù hợp, như Sales.
2. Dùng Data Validation Drop-Down Lists
Danh sách thả xuống là yếu tố thiết yếu để tạo file Excel tương tác. Drop-down list cho phép nhập liệu nhanh, nhất quán và được kiểm soát. Sau đó, bạn có thể dùng nó để lọc dữ liệu, biểu đồ và KPI tương tác.
Các Bước:
Xây Danh Sách Phụ Trợ: Xây danh sách phụ trợ ở phía trên hoặc bên phải sheet. Bạn có thể ẩn chúng sau.
- Chọn một ô và nhập công thức sau để liệt kê khu vực 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 "All" (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 Drop-Down List:
- Chọn ô nơi bạn muốn drop-down (ví dụ: B2).
- Vào tab Data >> chọn Data Validation.
- Dưới Allow, chọn List.
- Trong Source, chọn danh sách khu vực từ cột phụ trợ.
- Click OK.
- Làm tương tự, tạo drop-down list cho Product.
- Dưới Allow, chọn List.
- Trong Source, chọn danh sách sản phẩm từ cột phụ trợ.
Mẹo: Bạn có thể tạo named range cho danh sách và dùng nó trong data validation.
3. Dùng Công Thức Động Với Drop-Down
Kết hợp công thức động với drop-down tự động cập nhật KPI và lọc dữ liệu.
Tạo KPIs:
- Tổng Doanh Thu (Total Revenue):
=SUMIFS( Sales[Revenue], Sales[Region], IF($B$2="All","*", $B$2), Sales[Product], IF($B$4="All","*", $B$4))
- Giảm Giá Trung Bình (Average Discount):
=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 cho giao diện sạch hơn.
- Tổng Đơn Hàng (Total Orders):
=COUNTIFS( Sales[Region], IF($B$2="All","*", $B$2), Sales[Product], IF($B$4="All","*", $B$4))
- Tổng Số Lượng Bán (Total Units Sold):
=SUMIFS( Sales[Units], Sales[Region], IF($B$2="All","*", $B$2), Sales[Product], IF($B$4="All","*", $B$4))
Kiểm Tra Tương Tác:
- Chọn Khu Vực và Sản Phẩm từ danh sách thả xuống.
- KPIs tự động cập nhật.
4. Tạo Biểu Đồ Động Với Named Range
Tạo biểu đồ tự động cập nhật dựa trên lựa chọn 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")
Định Nghĩa Named Range Động:
- Vào tab Formulas >> chọn Name Manager >> chọn New.
- Name: FilteredDates
- Refers to:
=INDEX('Interactive Sheet'!$B$7#, ,1)
- Name: FilteredRevenue
- Refers to:
=INDEX('Interactive Sheet'!$B$7#, ,2)
Tạo Biểu Đồ:
- Vào tab Insert >> từ Charts >> chọn Line Chart.
- Click chuột phải biểu đồ >> chọn Select Data.
- Đối với Series Values, gõ:
='Interactive Sheet'!FilteredRevenue
- Đối với Horizontal Axis Labels:
='Interactive Sheet'!FilteredDates
Kiểm Tra Tương Tác:
- Chọn khu vực East và tất cả sản phẩm.
- Biểu đồ hiển thị tất cả dữ liệu 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 hoạt động cho bất kỳ tổ hợp nào không cần VBA.
5. Dùng Định Dạng Có Điều Kiện Cho Phản Hồi Trực Quan
Làm nổi bật giá trị chính hoặc lựa chọn người dùng ngay lập tức với Định Dạng Có Điều Kiện.
Các Bước:
- Chọn phạm vi dữ liệu của bạn.
- Vào tab Home >> chọn Conditional Formatting >> chọn New Rule.
- Chọn Use a formula to determine which cells to format.
- 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 làm nổi bật.
- Click OK.
Chỉ các hàng khớp được làm nổi bật, củng cố trực quan lựa chọn bộ lọc.
6. PivotTable, Slicer, Và Biểu Đồ Tương Tác
PivotTable, PivotChart và Slicer là tính năng tương tác cốt lõi trong Excel khiến lọc dữ liệu cực kỳ dễ dàng.
Tạo PivotTable:
- Chọn phạm vi dữ liệu.
- Vào tab Insert >> chọn PivotTable.
- Chọn vị trí: New hoặc Existing Worksheet.
- Click OK.
- Từ PivotTable Fields:
- Kéo Product vào vùng Rows.
- Kéo Revenue vào vùng Values.
Tạo PivotCharts:
- Chọn PivotTable.
- Vào tab PivotTable Analyze >> chọn PivotChart.
- Chọn Clustered Column chart.
- Click OK.
Thêm Slicer Tương Tác:
- Vào tab PivotTable Analyze >> chọn Insert Slicer.
- Chọn trường để lọc, như:
- Region, Product, Salesperson, v.v.
- Bạn có thể click nút trong slicer để lọc biểu đồ và bảng ngay lập tức.
- Để giữ slicer và biểu đồ cùng nhau, bạn có thể nhóm chúng.
- Đặt slicer gần hoặc trên biểu đồ, chọn tất cả đối tượng (biểu đồ và slicer), sau đó click chuột phải và chọn Group.
Kết Nối Nhiều PivotTable Với Slicer Chung
- Tạo nhiều pivot table từ cùng nguồn dữ liệu.
- Chèn Slicers.
- Click chuột phải slicer >> chọn Report Connections.
- Kết nối slicer với nhiều PivotTable.
- Bây giờ, sao chép slicer và biểu đồ sang sheet dashboard.
- Bạn có thể ẩn sheet PivotTable.
7. Dùng Hyperlink Cho Điều Hướng
Để file Excel cảm giác giống app có thể click hơn, bạn có thể dùng hyperlink cho điều hướng. Điều này cho phép người dùng nhảy đến sheet, biểu đồ hoặc bảng khác nhau.
Các Bước:
- Vào tab Insert >> từ Illustrations >> chọn Shapes >> chọn Rectangle.
- Đặt tên shape "Sales Data".
- Click chuột phải shape >> chọn Link.
- Trong hộp thoại Insert Hyperlink:
- Chọn Place in This Document >> gõ tham chiếu ô A1 >> chọn sheet Sales.
- Click OK.
- Chọn Place in This Document >> gõ tham chiếu ô A3 >> chọn sheet PivotTable.
- Click OK.
- Định dạng nó như nút bấm cho giao diện sạch hơn.
- Hyperlink điều hướng như vậy cho phép người dùng dễ dàng nhảy giữa "Dashboard" và các sheet "Details".
File Excel Tương Tác Cuối Cùng:
- Ví dụ dưới đây hiển thị tất cả khu vực và sản phẩm "Accessory" được chọn.
- Lựa chọn cũng có thể làm từ slicer.
- Tất cả dữ liệu tự động cập nhật.
Mẹo
- Chúng tôi đã demo cả biểu đồ động (dùng hàm FILTER) và PivotChart để chỉ các phương pháp tương tác khác nhau. Bạn có thể chọn loại phù hợp nhất nhu cầu.
- Bạn cũng có thể khám phá Form Controls, cung cấp thêm tùy chọn tương tác không cần VBA.
- Để tạo ô tìm kiếm, bạn có thể dùng hàm XLOOKUP động.
Tải Workbook Thực Hành
Kết Luận
Sử dụng các tính năng và kỹ thuật này, bạn có thể tạo file Excel tinh vi, tương tác mà không cần bất kỳ VBA nào. Chìa khóa là kết hợp nhiều kỹ thuật để có tính tương tác tối đa. Hãy luôn kiểm tra kỹ các yếu tố tương tác và cung cấp hướng dẫn rõ ràng cho người dùng. Với thực hành, bạn sẽ tạo được dashboard và công cụ ấn tượng làm cho phân tích dữ liệu vừa mạnh mẽ vừa thân thiện, tất cả không viết một dòng code VBA.
Nhận MIỄN PHÍ Bài Tập Excel Nâng Cao Có Giải Pháp!