Một bảng điều khiển động trong Excel cho phép bạn tương tác với dữ liệu của mình theo thời gian thực. Excel cực kỳ mạnh mẽ cho phân tích dữ liệu, và việc kết hợp PivotTables với Slicers tạo ra các bảng điều khiển động, tương tác tự động cập nhật.
Trong bài viết này, chúng tôi sẽ chỉ cách xây dựng bảng điều khiển động với PivotTables & Slicers trong Excel.
Bước 1: Chuẩn Bị Dữ Liệu Của Bạn
Trước khi tạo bảng điều khiển, việc cấu trúc dữ liệu đúng cách rất quan trọng.
Yêu Cầu Cho Tập Dữ Liệu Của Bạn:
- Phải ở định dạng bảng (không ô đã hợp nhất).
- Mỗi cột nên có tiêu đề rõ ràng, duy nhất.
- Không hàng hoặc cột trống.
- Đảm bảo tất cả cột có định dạng phù hợp:
- Cột Date là Short Date.
- Cột Unit Price và Total Sales là Currency.
- Units Sold là Number không Decimals.
- Chuyển đổi thành Bảng Excel:
- Chọn tất cả dữ liệu hoặc nhấn Ctrl+A.
- Nhấn Ctrl+T hoặc chuyển đến tab Insert >> chọn Table.
- Chọn My table has headers.
- Nhấp OK.
- Đặt tên cho bảng của bạn:
- Với bất kỳ ô nào trong bảng được chọn,
- Chuyển đến tab Table Design >> thay đổi Table Name thành SalesData.
Bước 2: Tạo PivotTable Đầu Tiên Của Bạn
Hãy tạo một PivotTable hiển thị bán hàng theo Khu vực và Danh mục Sản phẩm.
- Nhấp vào bất kỳ đâu trong bảng SalesData của bạn.
- Chuyển đến tab Insert >> chọn PivotTable.
- Đảm bảo phạm vi bảng hoặc tên chính xác và chọn New Worksheet.
- Nhấp OK.
- Trong ngăn PivotTable Fields:
- Kéo trường Region vào khu vực Rows.
- Kéo trường Product Category vào khu vực Columns.
- Kéo trường Total Sales vào khu vực Values.
- Bây giờ, PivotTable đầu tiên sẽ cho thấy mỗi danh mục sản phẩm tạo ra bao nhiêu doanh thu trong mỗi khu vực.
Bước 3: Thêm Slicer Và Timeline
Bây giờ, thêm slicers và timeline để lọc dữ liệu này một cách tương tác.
Chèn Slicer:
- Chọn bất kỳ ô nào của PivotTable.
- Chuyển đến tab PivotTable Analyze >> chọn Insert Slicer.
- Chọn Sales Rep và Date.
- Nhấp OK.
Chèn Timeline:
- Chọn bất kỳ ô nào của PivotTable.
- Chuyển đến tab PivotTable Analyze >> chọn Insert Timeline.
- Chọn Date.
- Nhấp OK.
- Đặt slicers và timeline bên cạnh PivotTable của bạn.
Kiểm tra slicers và timeline của bạn bằng cách nhấp vào các nhân viên bán hàng khác nhau và xem PivotTable cập nhật tự động!
Bước 4: Tạo PivotTables Bổ Sung
Hãy thêm hai PivotTables nữa vào bảng điều khiển của chúng ta.
PivotTable 2: Xu Hướng Bán Hàng Hàng Tháng
- Nhấp vào bất kỳ đâu trong bảng SalesData của bạn.
- Chuyển đến tab Insert >> chọn PivotTable.
- Đảm bảo phạm vi bảng chính xác và chọn New Worksheet.
- Nhấp OK.
- Trong ngăn PivotTable Fields:
- Kéo Date vào khu vực Rows. Nó sẽ hiển thị.
- Months (Date)
- Days (Date)
- Date
- Kéo Total Sales vào khu vực Values.
- Kéo Date vào khu vực Rows. Nó sẽ hiển thị.
PivotTable 3: Sản Phẩm Hàng Đầu Theo Đơn Vị Bán Ra
- Tạo PivotTable khác trên cùng worksheet với PivotTable 3.
- Trong ngăn PivotTable Fields:
- Kéo Product Name vào khu vực Rows.
- Kéo Units Sold vào khu vực Values.
Bước 5: Kết Nối Nhiều PivotTables Với Cùng Slicers
Hãy kết nối slicers hiện có của chúng ta với tất cả PivotTables:
- Nhấp chuột phải vào slicer Sales Rep.
- Chọn Report Connections.
- Chọn tất cả PivotTables trong danh sách.
- Nhấp OK.
- Lặp lại cho slicer Date.
Bây giờ, khi bạn nhấp vào một nhân viên bán hàng cụ thể hoặc phạm vi ngày, cả ba PivotTables sẽ cập nhật đồng thời!
Bước 6: Tạo PivotCharts
Chuyển đổi PivotTables của bạn thành biểu đồ trực quan.
Cho PivotTable Khu Vực/Danh Mục:
- Nhấp vào bất kỳ đâu trong PivotTable.
- Chuyển đến tab PivotTable Analyze >> chọn PivotChart.
- Chọn biểu đồ Clustered Column.
- Nhấp OK.
- Định dạng biểu đồ:
- Thêm tiêu đề biểu đồ.
- Tùy chỉnh trục.
- Loại bỏ các yếu tố không cần thiết (ví dụ: lưới, chú thích nếu không cần).
Cho Xu Hướng Bán Hàng Hàng Tháng:
- Nhấp vào bất kỳ đâu trong PivotTable.
- Chuyển đến tab PivotTable Analyze >> chọn PivotChart.
- Chọn biểu đồ Line with Markers.
- Nhấp OK.
Cho Sản Phẩm Hàng Đầu:
- Nhấp vào bất kỳ đâu trong PivotTable.
- Chuyển đến tab PivotTable Analyze >> chọn PivotChart.
- Chọn biểu đồ Bar.
- Nhấp OK.
Mọi thay đổi trong PivotTable của bạn sẽ được phản ánh trong PivotChart tự động.
Bước 7: Thiết Kế Bố Cục Bảng Điều Khiển
Bây giờ hãy tổ chức mọi thứ thành một bảng điều khiển liên kết.
- Đổi tên worksheet bảng điều khiển thành Sales Dashboard.
- Đặt vị trí biểu đồ của bạn:
- Biểu đồ Khu Vực/Danh Mục ở bên trái.
- Biểu đồ Xu Hướng Hàng Tháng ở giữa.
- Biểu đồ Sản Phẩm Hàng Đầu ở bên phải.
- Đặt slicers ở trên cùng để dễ truy cập.
- Thêm tiêu đề bằng hộp văn bản: Sales Performance Dashboard.
Bước 8: Nâng Cao Với Định Dạng
Hãy làm cho bảng điều khiển đẹp mắt hơn:
- Áp dụng conditional formatting cho Region/Category PivotTable:
- Chọn các ô dữ liệu.
- Chuyển đến tab Home >> từ Conditional Formatting >> từ Color Scales >> chọn Green-Yellow-Red.
- Định dạng biểu đồ Xu Hướng Hàng Tháng:
- Nhấp vào biểu đồ.
- Chuyển đến Chart Design >> chọn Style 5 (hoặc bất kỳ kiểu nào bạn thích).
- Thêm tiêu đề biểu đồ: Monthly Sales Trend.
- Định dạng biểu đồ Sản Phẩm Hàng Đầu:
- Thêm nhãn dữ liệu.
- Chuyển đến Chart Design >> chọn Add Chart Element >> chọn Data Labels.
- Sắp xếp giảm dần.
- Thêm tiêu đề biểu đồ: Top Products.
Kiểm Tra Tương Tác:
Mẹo Khắc Phục Sự Cố
- Nếu ngày không được nhóm đúng, đảm bảo chúng được định dạng là ngày trong dữ liệu nguồn.
- Nếu slicers không cập nhật tất cả PivotTables, kiểm tra lại Report Connections.
- Xem xét sử dụng "Defer Layout Update" trong tùy chọn PivotTable cho vấn đề hiệu suất với tập dữ liệu lớn hơn.
Kỹ Thuật Nâng Cao
Tạo trường tính toán hiển thị biên lợi nhuận:
- Nhấp vào PivotTable của bạn.
- Chuyển đến tab PivotTable Analyze >> từ Fields, Items, & Sets >> chọn Calculated Field.
- Đặt tên Profit và nhập công thức sau.
- Nhấp Add và OK.
Thêm tiêu đề động thay đổi theo lựa chọn:
- Sử dụng GETPIVOTDATA() để kéo tổng hiện tại được lọc.
- Chèn công thức sau.
="Sales Dashboard: "&TEXT(GETPIVOTDATA("Total Sales",$A$3),"$#,##0")
Tải Xuống Sổ Làm Việc
Kết Luận
Bằng cách làm theo các bước trên, bạn có thể tạo các bảng điều khiển chuyên nghiệp và tương tác cho phép người dùng dễ dàng phân tích dữ liệu bán hàng. Bạn có thể dùng PivotTables để tóm tắt và phân tích dữ liệu, và PivotCharts để hình ảnh hóa dữ liệu. Slicers và timeline tương tác giúp lọc dữ liệu tự động. Bảng điều khiển Excel động làm mới dễ dàng khi dữ liệu được thêm vào. Loại bảng điều khiển này hữu ích cho báo cáo bán hàng, theo dõi KPI, phân tích tồn kho và hơn thế nữa.
Nhận Bài Tập Excel Nâng Cao Miễn Phí Kèm Giải Pháp!