Vì sao nên thay thế Excel PivotTable bằng Power Pivot?

PivotTable từng là giải pháp an toàn mỗi khi dữ liệu trở nên quá tải, nhưng chúng luôn khiến bạn phải nheo mắt nhìn hàng loạt số liệu. Vấn đề nằm ở việc kết nối mọi thứ lại với nhau. PivotTable truyền thống buộc bạn phải làm việc với các khối dữ liệu riêng biệt, đòi hỏi phải phân tích riêng biệt cho những khía cạnh khác nhau của cùng một tập dữ liệu. Sau đó, Power Pivot xuất hiện và mọi thứ đã thay đổi.

Power Pivot làm được mọi thứ mà PivotTable có thể làm

Mặc dù PivotTable hoạt động với các nguồn dữ liệu đơn lẻ, Power Pivot coi toàn bộ workbook như một cơ sở dữ liệu được kết nối. Thay vì buộc các hàm và công thức Excel thường dùng phải tạo những kết nối pseudo, bạn có thể nhập nhiều bảng liên quan và để Power Pivot tự động xử lý các mối quan hệ mô hình.

Cách tiếp cận này loại bỏ chu kỳ cập nhật công thức và sửa các tham chiếu bị lỗi vô tận đã gây khó khăn cho quy trình làm việc cũ. Với Power Pivot, việc thêm dữ liệu mới trở thành một thao tác làm mới đơn giản, cập nhật tất cả các phân tích cùng một lúc.

Để kích hoạt Power Pivot, hãy vào File > Options, nhấp vào Add-ins, chọn COM Add-ins từ menu drop-down và chọn hộp kiểm Microsoft Power Pivot for Excel.

Mô hình quan hệ giúp việc tóm tắt và phân tích dễ dàng hơn bao giờ hết

Power Pivot xử lý dữ liệu như một cơ sở dữ liệu thực sự thay vì các bảng tính riêng biệt. Bạn chỉ cần nhập từng tập dữ liệu và sau đó xác định mối quan hệ giữa các trường chung, cho phép Excel tự động kết hợp các bảng và cung cấp báo cáo hợp nhất mà không cần tra cứu thủ công. Trước khi sử dụng Power Pivot (hoặc bất kỳ ứng dụng nào khác trong Excel), điều quan trọng là bạn phải dọn dẹp và chuẩn bị workbook trước để đảm bảo kết quả đáng tin cậy. Nhiều người sử dụng Power Query thay vì các chức năng dọn dẹp truyền thống, vì nó có khả năng mở rộng tốt hơn và giúp tôi tiết kiệm rất nhiều thời gian dọn dẹp bảng.

Để minh họa sức mạnh của mô hình quan hệ, bài viết sẽ sử dụng một tập hợp các workbook dùng để điền dữ liệu vào cơ sở dữ liệu backend trong quá trình phát triển. Đây là một cơ sở dữ liệu thương mại điện tử với các bảng tính riêng biệt cho khách hàng, sản phẩm, đơn hàng và chi tiết đơn hàng, tất cả đều có những trường chung như Customer_ID, Order_ID và Product_ID.

Trước tiên, mở Power Pivot bằng cách khởi chạy bảng tính khách hàng, nhấp vào Power Pivot từ thanh ribbon, sau đó chọn Add to Data Model trong phần Tables. Thao tác này sẽ mở menu Power Pivot. Từ đây, thêm các bảng tính khác bằng cách nhấp vào From Other Sources > Excel File, sau đó duyệt và mở các file, nhấn Next, rồi Finish. Hãy thực hiện thao tác này trên tất cả các bảng tính của mình.

Sau khi đã thêm tất cả dữ liệu, hãy chuyển đến Diagram View, nằm trong phần View của Power Pivot. Thao tác này hiển thị tất cả 4 workbook: customers, order_details, orders và products. Power Pivot thường có thể tự động phát hiện và đề xuất những mối quan hệ, nhưng bạn cũng có thể xác định chúng theo cách thủ công bằng cách kéo các trường giữa những bảng trong Diagram View.

Trong ví dụ này, mỗi workbook đều chia sẻ những trường chính liên kết các bảng với nhau. Cả workbook customers và orders đều bao gồm trường Customer_ID. Workbook orders và order_details đều chia sẻ trường Order_ID. Workbook order_details và products sử dụng cùng một trường Product_ID. Các trường được chia sẻ này tạo thành mối quan hệ "một-nhiều". Một khách hàng có thể có nhiều đơn hàng, mỗi đơn hàng có thể bao gồm nhiều sản phẩm và mỗi sản phẩm có thể xuất hiện trong nhiều chi tiết đơn hàng. Power Pivot sử dụng các mã định danh duy nhất này để tự động kết nối tất cả dữ liệu.

Các phép tính DAX cho phép linh hoạt hơn và có thông tin chi tiết tốt hơn

Vì chúng ta đã thiết lập các mối quan hệ và chứng minh việc xây dựng báo cáo dễ dàng như thế nào, đã đến lúc triển khai DAX. DAX (Data Analysis Expressions) là ngôn ngữ công thức đằng sau Power Pivot, được thiết kế đặc biệt cho việc mô hình hóa dữ liệu và tính toán nâng cao. Các công thức DAX của Power Pivot mở ra những khả năng phân tích gần như không thể thực hiện được với PivotTable.

Các công thức này cho phép bạn tạo những phép tính tùy chỉnh tự động theo các mối quan hệ trong bảng, thực hiện những phân tích phức tạp với cú pháp đơn giản đến bất ngờ. Nếu bạn mới làm quen với DAX, tài liệu chính thức của Microsoft là một nơi tuyệt vời để bắt đầu.

Với 3 phép đo, bạn có thể thực hiện các phép tính gần như bất khả thi với PivotTable truyền thống.

Trước tiên, hãy tính giá trị vòng đời khách hàng. Trên thanh công cụ Power Pivot trong Excel, nhấp vào Measures > New Measure và chọn bảng customers. Đặt tên cho phép đo này là "Customer LTV" và nhập công thức:

= SUM(order_details[Line_Total])

Sau đó, nhấp vào OK. Power Pivot sẽ theo dõi chuỗi từ khách hàng đến đơn hàng rồi đến chi tiết đơn hàng và tự động tính tổng các giao dịch mua của mỗi khách hàng.

Tiếp theo, chúng ta muốn biết quy mô đơn hàng trung bình của mỗi khách hàng. Một lần nữa, mở New Measure trong bảng customers, đặt tên là "Avg Order Value" và sử dụng công thức:

= DIVIDE([Customer LTV], DISTINCTCOUNT( orders[Order_ID]))

Nhấp vào OK sẽ cho bạn một phép đo chia tổng chi tiêu cho số lượng đơn hàng của mỗi khách hàng mà không cần bất kỳ cột hỗ trợ nào.

Cuối cùng, hãy khám phá các tùy chọn vận chuyển theo danh mục. Trong bảng products, hãy tạo một thước đo có tên "Audio Express %" với công thức sau:

= DIVIDE(
CALCULATE( SUM(order_details[Line_Total]),
products[Category] = "Audio",
orders[Shipping_Method] = "Express"
),
CALCULATE( SUM(order_details[Line_Total]),
products[Category] = "Audio"
))

Sau đó, đánh dấu vào ô cho từng thước đo để xem chúng trên bảng.

Với các thước đo DAX này, bạn có thể ngay lập tức xem tổng chi tiêu của từng khách hàng theo danh mục cùng với tỷ lệ chính xác các đơn hàng Audio được vận chuyển Express trong một PivotTable duy nhất.