Cách biến Pivot Table trong Excel thành bảng tương tác trong 5 phút

Hầu hết mọi người đều cho rằng việc xây dựng bảng điều khiển (dashboard) trong Excel luôn có nghĩa là thiết kế biểu đồ, tô màu, hoặc thậm chí phải dùng đến Power BI hay Tableau chỉ để cho sếp thấy xu hướng bán hàng. Tuy nhiên, thực tế PivotTable trong Excel có thể giúp bạn xử lý những công việc đó nhanh hơn nhiều.

Cách xây dựng bảng điều khiển bằng PivotTable trong Excel

Bí quyết để xây dựng bảng điều khiển tương tác bằng PivotTable trong Microsoft Excel là chia dữ liệu thành nhiều bảng PivotTable nhỏ hơn, tập trung hơn và đặt chúng cạnh nhau trên cùng một trang tính. Hãy coi mỗi bảng như đang trả lời một câu hỏi duy nhất thay vì cố gắng trả lời mọi thứ cùng một lúc. Đối với bảng điều khiển quản lý dự án, điều đó có thể trông giống như một bảng liệt kê các dự án đang hoạt động, một bảng khác phân tích những nhiệm vụ theo người phụ trách và một bảng thứ ba theo dõi tỷ lệ hoàn thành. Đặt tất cả cùng nhau, chúng kể một câu chuyện hoàn chỉnh mà không làm người dùng cảm thấy quá tải. Giữ dữ liệu thô của bạn trên một trang tính riêng biệt trong khi dành riêng một tab bảng điều khiển cho các bảng PivotTable tóm tắt dễ đọc.

Sau khi các bảng đã được đặt đúng vị trí, bạn có thể bắt đầu làm cho dữ liệu dễ hiểu hơn. Thay vì hiển thị một danh sách dài các ngày riêng lẻ, hãy nhóm chúng theo tháng hoặc năm. Thay vì hiển thị các số liệu doanh thu rải rác, hãy nhóm chúng theo phạm vi như $0–$1.000, để các xu hướng trở nên dễ phát hiện hơn nhiều.

Bố cục cũng tạo ra sự khác biệt lớn hơn bạn nghĩ. Trong tab PivotTable Design, chuyển sang Tabular Form hoặc Outline Form sẽ làm cho PivotTable trông giống một báo cáo có cấu trúc hơn là một bản xuất bảng tính mặc định. Việc bật tùy chọn Repeat All Item Labels sẽ củng cố bố cục giống như cơ sở dữ liệu, điều này đặc biệt hữu ích nếu bạn chia sẻ tệp với người không quen thuộc sử dụng PivotTable.

Bạn cũng có thể thêm các trường tính toán với công thức của riêng mình trực tiếp vào PivotTable để xây dựng KPI, đánh dấu trạng thái hoàn thành hoặc tạo các chỉ số tùy chỉnh không tồn tại trong dữ liệu thô của bạn.

Để làm cho bảng điều khiển có tính tương tác thực sự, hãy thêm bộ lọc (slicer). Bộ lọc là các nút lọc có thể nhấp chuột nổi trên các bảng, giúp bạn dễ dàng khám phá dữ liệu mà không cần thay đổi PivotTable cơ bản. Mặc dù chúng khá hữu ích, nhưng chúng cũng là một tính năng của Excel mà nhiều người dùng thậm chí không biết đến. Chọn Insert Slicer, chọn một trường như "Phòng ban" hoặc "Doanh thu" và đặt bộ lọc ở bất kỳ đâu trên bảng điều khiển. Nếu sử dụng tùy chọn Report Connections để liên kết mọi PivotTable với bộ lọc đó, việc chọn một danh mục sẽ lọc tất cả các bảng được kết nối cùng một lúc.

Mỗi khi bạn thêm các hàng mới vào dữ liệu nguồn của mình, tất cả những gì bạn cần làm là chọn Refresh All từ tab Data và mọi PivotTable trên bảng điều khiển sẽ tự động tính toán lại.

Xây dựng bảng điều khiển bán hàng tương tác từ tập dữ liệu 1.000 dòng

Lấy ví dụ một tập dữ liệu 1.000 dòng bao gồm các cột về ngày tháng (từ năm 2024 đến năm 2026), danh mục sản phẩm, nhân viên bán hàng, khu vực, số lượng, doanh thu và chi phí. Tranh tính được đổi tên thành Raw Data và chuyển đổi tập dữ liệu thành Bảng Excel. Khi chuyển đổi tập dữ liệu thành Bảng Excel, hãy đảm bảo tích box My table has headers. Việc chuyển đổi dữ liệu thành Bảng Excel giúp dữ liệu trở nên linh hoạt, vì vậy khi bạn thêm các hàng mới sau này, Pivot Table bạn sẽ tự động phát hiện chúng mà không cần bạn phải xác định lại phạm vi dữ liệu.

Tiếp theo, thiết lập tab bảng điều khiển. Sau khi tạo một trang tính mới và đổi tên thành Dashboard, chọn các cột A–Z, điều hướng đến tab Home và thay đổi màu nền thành màu xám nhạt (#F2F2F2) để tạo cho bảng điều khiển một nền sạch sẽ, giống như ứng dụng.

Với trang tính bảng điều khiển đã sẵn sàng, tạo Pivot Table đầu tiên để trực quan hóa xu hướng doanh thu theo thời gian. Đặt bảng PivotTable vào ô B3 trên trang tính Dashboard, kéo trường Date vào vùng Row và Total Revenue vào vùng Values, sau đó nhóm các ngày bằng cách nhấp chuột phải vào bảng PivotTable, chọn Group, và chỉ chọn Tháng và Năm. Cuối cùng, định dạng các giá trị doanh thu bằng cách chọn Field Settings -> Number Format -> Currency, đặt số thập phân là 0 và nhấp OK.

Thay vì quay lại trang tính dữ liệu gốc để tạo từng bảng PivotTable từ đầu, sao chép bảng đầu tiên và dán hai lần để tiết kiệm thời gian. Ví dụ sử dụng một bản sao để hiển thị thứ hạng của nhân viên bán hàng bằng cách kéo Region và Salesperson vào vùng Rows và Total Revenue vào vùng Values. Đối với bản sao còn lại, kéo Product Category vào vùng Rows và thêm hai trường tính toán vì dữ liệu nguồn không bao gồm các cột Gross Profit hoặc Profit Margin.

Để tạo các trường tính toán, nhấp vào bên trong PivotTable, mở tab PivotTable Analyze  và chọn Fields, Items, & Sets -> Calculated Field. Trong ô Name đầu tiên, nhập Lợi nhuận gộp (Gross Profit) và sử dụng công thức này:

='Total Revenue' - 'Total Cost'

Ở trường tính thứ hai, nhập Profit Margin % và dùng công thức này:

=('Total Revenue' - 'Total Cost') / 'Total Revenue'

Bạn có thể thực hiện một vài thay đổi về bố cục để cải thiện khả năng đọc. Ví dụ ở đây đối với bảng PivotTable thứ hai, mở tab Design trong PivotTable Tools, chọn Report Layout -> Show in Tabular Form, quay lại Report Layout và chọn Repeat All Item Labels, sau đó chọn Subtotals -> Do Not Show Subtotals. Những thay đổi này làm cho bảng trông giống như một lưới cơ sở dữ liệu gọn gàng hơn là một bảng PivotTable mặc định. Đối với bảng PivotTable thứ ba, định dạng Sum of Gross Profit as Currency với 0 chữ số thập phân và Sum of Profit Margin % dưới dạng Phần trăm với 1 chữ số thập phân.

Sau khi thực hiện những điều chỉnh đó, thêm hai bộ lọc để làm cho bảng điều khiển tương tác hơn: một cho Region và một cho Product Category. Chúng được đặt bên dưới bảng PivotTable thứ hai và thứ ba và liên kết chúng với cả ba bảng. Vì tất cả đều được liên kết, chỉ cần một cú nhấp chuột sẽ lọc toàn bộ bảng điều khiển cùng một lúc.

Giờ đây, mỗi khi thêm các giao dịch bán hàng mới vào cuối bảng trong trang tính Raw Data, tất cả những gì phải làm là vào tab Data và chọn Refresh All. Ngoài ra, nhấn tổ hợp phím Ctrl + Alt + F5, và tất cả các bảng PivotTable và biểu đồ trên bảng điều khiển sẽ cập nhật ngay lập tức để trình bày dữ liệu mới nhất.