Tính lại công thức trong Power Pivot

Khi bạn làm việc với dữ liệu trong Power Pivot, bạn có thể cần làm mới dữ liệu từ nguồn của nó, tính toán lại công thức trong các cột được tính toán hoặc cập nhật PivotTable sử dụng Mô hình Dữ liệu.

Bài viết này giải thích sự khác biệt giữa làm mới dữ liệu và tính toán lại, cung cấp cái nhìn tổng quan về cách kích hoạt tính toán lại và mô tả các tùy chọn của bạn để kiểm soát tính toán lại.

Hiểu về việc làm mới và tính toán lại dữ liệu

Power Pivot sử dụng cả việc làm mới và tính toán lại dữ liệu:

Làm mới dữ liệu có nghĩa là lấy dữ liệu cập nhật từ các nguồn dữ liệu bên ngoài. Power Pivot không tự động phát hiện thay đổi trong các nguồn dữ liệu bên ngoài. Để đưa vào dữ liệu cập nhật, hãy làm mới mô hình dữ liệu.

Tính toán lại nghĩa là cập nhật tất cả các cột, bảng, biểu đồ và PivotTable trong sổ làm việc của bạn có chứa công thức. Vì việc tính toán lại một công thức sẽ ảnh hưởng đến hiệu suất, nên điều quan trọng là phải hiểu rõ các phụ thuộc liên quan đến mỗi phép tính.

Tính toán lại thủ công so với tự động

Theo mặc định, Power Pivot tự động tính toán lại khi cần thiết trong khi tối ưu hóa thời gian cần thiết để xử lý. Mặc dù việc tính toán lại có thể mất thời gian, nhưng đó là một nhiệm vụ quan trọng. Trong quá trình tính toán lại, Power Pivot sẽ kiểm tra các cột phụ thuộc và thông báo cho bạn nếu cột thay đổi, nếu dữ liệu không hợp lệ hoặc nếu có lỗi xuất hiện trong công thức trước đây đã hoạt động. Tuy nhiên, bạn có thể chọn bỏ xác thực và chỉ cập nhật phép tính theo cách thủ công, đặc biệt nếu bạn đang làm việc với các công thức phức tạp hoặc tập dữ liệu rất lớn và muốn kiểm soát thời gian cập nhật.

Cả chế độ thủ công và tự động đều có lợi thế. Tuy nhiên, hãy dùng chế độ tính lại tự động. Chế độ này đồng bộ siêu dữ liệu Power Pivot và ngăn các vấn đề xảy ra do xóa dữ liệu, thay đổi tên hoặc kiểu dữ liệu hay thiếu phụ thuộc. 

Sử dụng tính toán tự động lại

Khi bạn sử dụng chế độ tính lại tự động, bất kỳ thay đổi nào đối với dữ liệu sẽ dẫn đến kết quả của bất kỳ công thức nào thay đổi sẽ kích hoạt tính toán lại của toàn bộ cột chứa công thức. Những thay đổi sau đây luôn yêu cầu tính toán lại các công thức:

• Các giá trị từ nguồn dữ liệu bên ngoài được làm mới.
• Định nghĩa của công thức đã thay đổi.
• Tên của bảng hoặc cột được tham chiếu trong công thức sẽ được thay đổi.
• Mối quan hệ giữa các bảng sẽ được thêm, sửa đổi hoặc xóa.
• Các số đo hoặc cột được tính mới sẽ được thêm vào.
• Các thay đổi được thực hiện cho các công thức khác trong sổ làm việc, vì vậy các cột hoặc phép tính phụ thuộc vào tính toán đó nên được làm mới.
• Các hàng sẽ được chèn hoặc bị xóa.
• Bạn đã áp dụng bộ lọc yêu cầu thực thi truy vấn để cập nhật tập dữ liệu. Bộ lọc có thể được áp dụng trong công thức hoặc như một phần của PivotTable hay PivotChart.

Sử dụng tính toán lại thủ công

Sử dụng tính toán lại thủ công để tránh phát sinh chi phí tính toán kết quả công thức cho đến khi bạn đã sẵn sàng. Chế độ thủ công đặc biệt hữu ích trong những tình huống này:

• Bạn đang thiết kế công thức bằng cách sử dụng mẫu và muốn thay đổi tên của các cột cũng như bảng được sử dụng trong công thức trước khi xác thực nó.
• Bạn biết rằng một số dữ liệu trong sổ làm việc đã thay đổi, nhưng bạn đang làm việc với một cột khác không thay đổi, vì vậy bạn muốn hoãn việc tính toán lại.
• Bạn đang làm việc trong một sổ làm việc có nhiều phụ thuộc và muốn trì hoãn việc tính toán lại cho đến khi bạn chắc chắn tất cả các thay đổi cần thiết đã được thực hiện.

Nếu bạn đặt sổ làm việc ở chế độ tính toán thủ công, Power Pivot trong Excel không thực hiện bất kỳ xác thực hay kiểm tra công thức nào. Các kết quả sau xảy ra:

• Bất kỳ công thức mới nào mà bạn thêm vào sổ làm việc đều được gắn cờ là chứa lỗi.
• Không có kết quả nào xuất hiện trong các cột được tính toán mới.

Để cấu hình sổ làm việc cho việc tính toán lại thủ công

1. Trong Power Pivot, chọn Thiết kế>Tính toán>Tùy chọn> Tính toánChế độ Tính toán Thủ công.
2. Để tính toán lại tất cả các bảng, hãy chọn Tùy chọn> Tính toánTính toán Ngay.
   Sổ làm việc kiểm tra lỗi trong công thức và cập nhật bảng với kết quả. Tùy thuộc vào lượng dữ liệu và số lần tính toán, sổ làm việc có thể tạm thời không phản hồi.

Khắc phục sự cố tính toán lại

Phụ thuộc

Khi một cột phụ thuộc vào cột khác và nội dung của cột kia thay đổi theo bất kỳ cách nào, bạn có thể cần tính toán lại tất cả các cột có liên quan. Bất cứ khi nào bạn thực hiện thay đổi đối với sổ làm việc Power Pivot, Power Pivot trong Excel sẽ phân tích dữ liệu Power Pivot hiện có để xác định xem tính toán lại có cần thiết không, và nó thực hiện cập nhật theo cách hiệu quả nhất có thể.

Ví dụ: giả sử bạn có một bảng Sales, có liên quan đến các bảng Product và ProductCategory; và công thức trong bảng Sales phụ thuộc vào cả hai bảng còn lại. Bất kỳ thay đổi nào đối với các bảng Sản phẩm hoặc Thể loại Sản phẩm đều khiến tất cả các cột được tính toán trong bảng Doanh số đều được tính toán lại. Hành vi này có ý nghĩa khi bạn cân nhắc rằng bạn có thể có các công thức giúp tăng doanh số theo thể loại hoặc theo sản phẩm. Để đảm bảo kết quả là chính xác, bạn phải tính toán lại các công thức dựa trên dữ liệu.

Power Pivot luôn thực hiện tính toán lại toàn bộ cho một bảng, vì việc tính toán lại toàn bộ sẽ hiệu quả hơn kiểm tra các giá trị thay đổi. Những thay đổi kích hoạt tính toán lại có thể bao gồm những thay đổi lớn như xóa cột, thay đổi loại dữ liệu số của cột hoặc thêm cột mới. Tuy nhiên, những thay đổi có vẻ tầm thường, như thay đổi tên cột, cũng có thể kích hoạt tính toán lại. Hiện tượng này xảy ra do tên cột được dùng làm mã định danh trong công thức.

Trong một số trường hợp, Power Pivot xác định rằng nó có thể loại trừ các cột khỏi việc tính toán lại. Ví dụ: nếu bạn có một công thức tra cứu một giá trị như [Màu Sản phẩm] từ bảng Sản phẩm và cột mà bạn thay đổi là [Số lượng] trong bảng Doanh số , công thức không cần tính toán lại ngay cả khi bảng Doanh số vàSản phẩm có liên quan với nhau. Tuy nhiên, nếu bạn có bất kỳ công thức nào dựa vào Sales[Quantity], thì cần phải tính toán lại.

Trình tự tính toán lại cho cột phụ thuộc

Power Pivot sẽ tính toán các phần phụ thuộc trước khi tính toán lại bất kỳ cột nào. Nếu nhiều cột phụ thuộc vào nhau, Power Pivot sẽ tuân theo trình tự phụ thuộc. Trình tự này đảm bảo Power Pivot xử lý các cột theo đúng thứ tự và ở tốc độ tối đa.

Giao dịch

Các thao tác tính toán lại hoặc làm mới dữ liệu diễn ra như một giao dịch. Nếu bất kỳ phần nào của thao tác làm mới không thành công, các thao tác còn lại sẽ quay lui. Hành vi này đảm bảo rằng dữ liệu không bị để lại ở trạng thái được xử lý một phần. Bạn không thể quản lý các giao dịch như bạn làm trong cơ sở dữ liệu quan hệ và bạn không thể tạo điểm kiểm tra.

Tính lại các hàm biến đổi

Một số hàm, như NOW, hoặc RANDTODAY, không có giá trị cố định. Để tránh vấn đề về hiệu suất, việc thực hiện truy vấn hoặc việc lọc thường không khiến Power Pivot đánh giá lại các hàm này nếu bạn sử dụng chúng trong cột được tính. Power Pivot chỉ tính toán lại kết quả cho những hàm này khi nó tính toán lại toàn bộ cột. Những trường hợp này bao gồm làm mới từ nguồn dữ liệu bên ngoài hoặc chỉnh sửa dữ liệu theo cách thủ công dẫn đến phải đánh giá lại các công thức chứa những hàm này. Tuy nhiên, nếu bạn dùng các hàm biến đổi chẳng hạn như NOW, , RANDhoặc TODAY trong việc xác định một trường được tính toán, Power Pivot luôn tính toán lại chúng.