Tạo bảng thông tin tương tác thời gian thực trong Excel bằng PivotTable &Slicers

Trong bài viết này, chúng tôi sẽ hướng dẫn cách xây dựng trang tổng quan động bằng PivotTable &Slicers trong Excel.

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

Trước khi tạo trang tổng quan, điều quan trọng là phải cấu trúc dữ liệu của bạn đúng cách.

Yêu cầu đối với tập dữ liệu của bạn:

• Phải ở định dạng bảng (không có ô được hợp nhất).
• Mỗi cột phải có tiêu đề rõ ràng, duy nhất.
• Không có hàng hoặc cột trống.
• Đảm bảo tất cả các cột có định dạng phù hợp:
  • Ngày cột dưới dạng Ngày ngắn .
  • Đơn giá và Tổng doanh thu dưới dạng Tiền tệ .
  • Số lượng đã bán dưới dạng Số không có Số thập phân .
• Chuyển đổi sang bảng Excel:
  • Chọn tất cả dữ liệu hoặc nhấn Ctrl+A .
  • Nhấn Ctrl+T hoặc đi tới phần Chèn tab>> chọn Bảng .
  • Kiểm tra Bảng của tôi có tiêu đề .
  • Nhấp vào OK .

• Đặt tên cho bảng của bạn:
  • Với bất kỳ ô nào trong bảng được chọn,
  • Đi tới Thiết kế bảng tab>> thay đổi Tên bảng tới Dữ liệu bán hàng .

Bước 2:Tạo PivotTable đầu tiên của bạn

Hãy tạo một PivotTable hiển thị doanh số 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 Dữ liệu bán hàng của bạn bàn.
• Đi tới Chèn tab>> chọn PivotTable .
• Đảm bảo phạm vi bảng hoặc tên là chính xác và chọn Bảng tính mới .
• Nhấp vào OK .

• Trong ngăn Trường PivotTable:
  • Kéo Vùng trường vào Hàng khu vực.
  • Kéo Danh mục sản phẩm trường vào Cột khu vực.
  • Kéo Tổng doanh số trường vào Giá trị khu vực.
• Bây giờ, PivotTable đầu tiên sẽ hiển thị doanh thu mà mỗi danh mục sản phẩm tạo ra ở mỗi khu vực.

Bước 3:Thêm bộ cắt và dòng thời gian

Bây giờ, hãy thêm bộ cắt và bộ đếm thời gian để lọc dữ liệu này một cách tương tác.

Chèn bộ cắt:

• Chọn bất kỳ ô nào của PivotTable.
• Đi tới Phân tích PivotTable tab>> chọn Chèn Bộ cắt .
• Chọn Đại diện bán hàng và Ngày .
• Nhấp vào OK .

Chèn dòng thời gian:

• Chọn bất kỳ ô nào của PivotTable.
• Đi tới Phân tích PivotTable tab>> chọn Chèn dòng thời gian .
• Chọn Ngày .
• Nhấp vào OK .

• Định vị bộ cắt và dòng thời gian bên cạnh PivotTable của bạn.

Kiểm tra bộ cắt và bộ đếm thời gian của bạn bằng cách nhấp vào các đại diện bán hàng khác nhau và xem cách PivotTable tự động cập nhật!

Bước 4:Tạo PivotTable bổ sung

Hãy thêm hai PivotTable nữa vào trang tổng quan của chúng ta.

Bảng tổng hợp 2:Xu hướng bán hàng hàng tháng

• Nhấp vào bất kỳ đâu trong Dữ liệu bán hàng của bạn bàn.
• Đi tới Chèn tab>> chọn PivotTable .
• Đảm bảo phạm vi bảng là chính xác và chọn Bảng tính mới .
• Nhấp vào OK .

• Trong ngăn Trường PivotTable:
  • Kéo Ngày tới Hàng khu vực. Nó sẽ hiển thị.
    • Tháng (Ngày)
    • Ngày (Ngày)
    • Ngày
  • Kéo Tổng doanh số tới Giá trị khu vực.

PivotTable 3:Sản phẩm hàng đầu theo đơn vị bán

• Tạo một PivotTable khác trên cùng trang tính với PivotTable 3.
• Trong ngăn Trường PivotTable:
  • Kéo Tên sản phẩm tới Hàng khu vực.
  • Kéo Số lượng đã bán tới Giá trị khu vực.

Bước 5:Kết nối nhiều PivotTable với cùng một Bộ cắt

Hãy kết nối các bộ cắt hiện có của chúng ta với tất cả các PivotTable:

• Nhấp chuột phải vào Đại diện bán hàng máy thái.
• Chọn Báo cáo kết nối .

• Kiểm tra tất cả PivotTable trong danh sách.
• Nhấp vào OK .

• Lặp lại cho Ngày máy thái.

Bây giờ, khi bạn bấm vào một đại diện bán hàng hoặc phạm vi ngày cụ thể, cả ba PivotTable sẽ cập nhật đồng thời!

Bước 6:Tạo PivotChart

Chuyển đổi PivotTable của bạn thành biểu đồ trực quan.

Đối với PivotTable Khu vực/Danh mục:

• Nhấp vào bất kỳ đâu trong PivotTable.
• Đi tới Phân tích PivotTable tab>> chọn PivotChart .
• Chọn Cột được nhóm biểu đồ.
• Nhấp vào OK .
• Định dạng biểu đồ:
  • Thêm tiêu đề biểu đồ.
  • Tùy chỉnh trục.
  • Xóa các phần tử không cần thiết (ví dụ:đường lưới, chú giải nếu không cần thiết).

Đối với Xu hướng bán hàng hàng tháng:

• Nhấp vào bất kỳ đâu trong PivotTable.
• Đi tới Phân tích PivotTable tab>> chọn PivotChart .
• Chọn Dòng bằng điểm đánh dấu biểu đồ.
• Nhấp vào OK .

Dành cho sản phẩm hàng đầu:

• Nhấp vào bất kỳ đâu trong PivotTable.
• Đi tới Phân tích PivotTable tab>> chọn PivotChart .
• Chọn Thanh biểu đồ.
• Nhấp vào OK .

Mọi thay đổi trong PivotTable sẽ tự động được phản ánh trong PivotChart của bạn.

Bước 7:Thiết kế bố cục trang tổng quan của bạn

Bây giờ, hãy sắp xếp mọi thứ thành một trang tổng quan gắn kết.

• Đổi tên bảng tính trang tổng quan của bạn thành Trang tổng quan bán hàng .
• Định vị biểu đồ của bạn:
  • Vùng/Danh mục biểu đồ ở bên trái.
  • Xu hướng hàng tháng biểu đồ ở giữa.
  • Sản phẩm hàng đầu biểu đồ bên phải.
• Đặt bộ cắt ở trên cùng để dễ dàng truy cập.
• Thêm tiêu đề bằng hộp văn bản:Trang tổng quan hiệu suất bán hàng .

Bước 8:Nâng cao bằng định dạng

Hãy làm cho trang tổng quan trở nên hấp dẫn hơn về mặt trực quan:

• Áp dụng định dạng có điều kiện cho Bảng tổng hợp vùng/danh mục :
  • Chọn ô dữ liệu.
  • Đi tới Trang chủ tab>> từ Định dạng có điều kiện>> từ Thang màu>> chọn Xanh-Vàng-Đỏ .
• Định dạng Xu hướng hàng tháng biểu đồ:
  • Nhấp vào biểu đồ.
  • Đi tới Thiết kế biểu đồ>> chọn kiểu 5 (hoặc bất kỳ phong cách nào bạn thích).
  • Thêm tiêu đề biểu đồ:Xu hướng bán hàng hàng tháng .
• Định dạng Sản phẩm hàng đầu biểu đồ:
  • Thêm nhãn dữ liệu.
  • Đi tới Thiết kế biểu đồ>> chọn Thêm thành phần biểu đồ>> chọn Nhãn dữ liệu .
  • Sắp xếp theo thứ tự giảm dần.
  • Thêm tiêu đề biểu đồ:Sản phẩm hàng đầu .

Kiểm tra tính tương tác:

Mẹo khắc phục sự cố

• Nếu ngày không được nhóm chính xác, hãy đảm bảo chúng được định dạng là ngày trong dữ liệu nguồn.
• Nếu bộ cắt không cập nhật tất cả các PivotTable, hãy kiểm tra kỹ các Kết nối báo cáo.
• Hãy cân nhắc sử dụng “Trì hoãn cập nhật bố cục” trong tùy chọn PivotTable để giải quyết các vấn đề về 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ị tỷ suất lợi nhuận:

• Nhấp vào PivotTable của bạn.
• Đi tới Phân tích PivotTable tab>> từ Trường, Mục và Bộ>> chọn Trường được tính toán .
• Đặt tên là Lợi nhuận và nhập công thức sau.

• Nhấp vào Thêm và Được .

Thêm tiêu đề động thay đổi theo lựa chọn:

• Sử dụng GETPIVOTDATA() để lấy tổng số hiện đã 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 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ể sử dụng PivotTable để tóm tắt và phân tích dữ liệu cũng như PivotChart để trực quan hóa dữ liệu. Bộ cắt tương tác và dòng thời gian giúp tự động lọc dữ liệu. Bảng thông tin Excel động dễ dàng làm mới khi dữ liệu được thêm vào. Loại trang tổng quan này hữu ích cho các báo cáo bán hàng, theo dõi KPI, phân tích hàng tồn kho, v.v.