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.

Tính năng tích hợp sẵn này của Excel biến bảng tính của bạn thành một mô hình dữ liệu quan hệ, tự động xử lý nhiều nguồn dữ liệu được kết nối. Thay vì mất hàng giờ để chuẩn bị dữ liệu thủ công, giờ đây bạn có thể phân tích các mối quan hệ phức tạp chỉ trong vài phút!

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. Sau khi được kích hoạt, một tab Power Pivot mới sẽ xuất hiện trên thanh ribbon của Excel, cung cấp cho bạn quyền truy cập vào các công cụ giúp thay đổi cách bạn làm việc với dữ liệu.

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.

Khi những mối quan hệ giữa các bảng đã được thiết lập, việc báo cáo trở nên đơn giản như kéo và thả các trường dữ liệu. Bạn không còn phải loay hoay với hàm VLOOKUP hay các cột hỗ trợ nữa, và có thể ngay lập tức phân tích và xử lý dữ liệu trên cả 4 bảng.

Ví dụ, để xem tổng doanh thu theo khách hàng, hãy nhấp vào PivotTable trong cửa sổ Power Pivot, chọn New Worksheet, và mở rộng bảng khách hàng trong danh sách trường. Sau đó, kéo Customer_Name vào Rows và Line_Total từ bảng order_details vào Values. Ngay lập tức, bạn sẽ thấy doanh thu trọn đời của mỗi khách hàng mà không cần phải kết nối thủ công.

Nếu muốn phân tích doanh thu đó theo danh mục sản phẩm, hãy thêm Category từ bảng products vào Columns. Excel tự động xử lý các kết nối thông qua orders và order_details rồi tính tổng các giá trị chính xác trong mỗi danh mục.

Để so sánh hiệu suất của các phương thức vận chuyển khác nhau, hãy kéo Shipping_Method từ bảng orders vào Filters và chọn Express hoặc Standard. Bảng tổng hợp cập nhật ngay lập tức và chỉ hiển thị các giao dịch đó.

Vì Power Pivot biết cách các bảng được kết nối, bạn có thể tự do thử nghiệm. Thêm City từ customers để xem xu hướng địa lý hoặc thêm Order_Date để lọc theo khoảng thời gian. Mọi thay đổi đều diễn ra trong thời gian thực và cho phép bạn khám phá các câu hỏi và tìm ra những hiểu biết sâu sắc mà không cần xây dựng lại mô hình dữ liệu hoặc viết lại công thức.

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. Trong ảnh chụp màn hình, bạn có thể thấy tổng doanh số bán hàng cho các mặt hàng Audio, Cables, Computer và nhiều mặt hàng khác, trong khi cột Audio Express % cho thấy, ví dụ, Alexis Parker đã vận chuyển 75% đơn hàng Audio của mình qua dịch vụ Express.

Việc tổng hợp những thông tin chi tiết này bằng các phương pháp truyền thống sẽ đòi hỏi phải xây dựng nhiều bảng phụ trợ và viết hàng tá hàm VLOOKUP hoặc các phép tính thủ công. Cách tiếp cận hiện đại trong Excel để xử lý các bảng tính như thế này là sử dụng những công thức DAX để lọc và tổng hợp dữ liệu trên nhiều bảng.