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

Khi bạn đang làm việc với dữ liệu trong Power Pivot, thỉnh thoảng bạn có thể cần làm mới dữ liệu từ nguồn, tính toán lại các công thức mà bạn đã tạo trong các cột được tính toán, hoặc đảm bảo dữ liệu được trình bày trong PivotTable luôn cập nhật.

Chủ đề này giải thích sự khác biệt giữa làm mới dữ liệu so với tính toán lại dữ liệu, cung cấp 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 việc tính toán lại.

Hiểu về Làm mới Dữ liệu và Tính toán lại

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 các thay đổi trong các nguồn dữ liệu bên ngoài, nhưng dữ liệu có thể được làm mới theo cách thủ công từ cửa sổ Power Pivot hoặc tự động nếu sổ làm việc được chia sẻ trên SharePoint.

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 làm phát sinh chi phí hiệu suất, điều quan trọng là cần 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 đây là một nhiệm vụ quan trọng, vì trong quá trình tính toán lại, các phần phụ thuộc cột sẽ được kiểm tra và bạn sẽ được thông báo nếu một cột thay đổi, liệ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 đã từng 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ó ưu điểm; Tuy nhiên, chúng tôi khuyến nghị bạn nên sử dụng chế độ tính toán 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 lại tự động

Khi bạn sử dụng chế độ tính lại tự động, mọi thay đổi dữ liệu có thể 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 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 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 đã được thay đổi.
• Mối quan hệ giữa các bảng đã được thêm, sửa đổi hoặc xóa.
• Các số đo hoặc cột được tính mới đã được thêm.
• Các thay đổi đã được thực hiện cho các công thức khác trong sổ làm việc, do đó 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 đã bị 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

Bạn có thể 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 nên 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 chắc chắn đã thực hiện tất cả các thay đổi cần thiết.

Xin lưu ý rằng, chừng nào sổ làm việc được đặt ở chế độ tính toán thủ công, Power Pivot trong Excel sẽ không thực hiện bất kỳ xác thực hay kiểm tra công thức nào, với kết quả như sau:

• Bất kỳ công thức mới nào mà bạn thêm vào sổ làm việc sẽ được gắn cờ là chứa lỗi.
• Sẽ 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, bấm 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 bấm Tùy chọn >Tính toánTính toán Ngay.
   Các công thức trong sổ làm việc được kiểm tra lỗi và bảng sẽ được cập nhật kết quả, nếu có. 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ể trở nên không phản hồi trong một thời gian.

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

Phụ thuộc

Khi 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, tất cả các cột có liên quan có thể cần được tính toán lại. Bất cứ khi nào thay đổi được thực hiện đối với sổ làm việc Power Pivot, Power Pivot trong Excel thực hiện phân tích dữ liệu Power Pivot hiện có để xác định tính toán lại có cần thiết không, đồng thời 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 sẽ khiến tất cả các cột được tính toán trong bảng Doanh số được tính toán lại. Điều 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 tổng hợp doanh số theo thể loại hoặc theo sản phẩm. Do đó, để chắc chắn kết quả là chính xác; Các công thức dựa trên dữ liệu phải được tính toán lại.

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 kiểu 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. Đây là vì tên cột được sử dụng làm mã định danh trong công thức.

Trong một số trường hợp, Power Pivot có thể xác định rằng có thể loại trừ cột khỏi 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 được thay đổi là [Số lượng] trong bảng Doanh số thì 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

Các phần phụ thuộc được tính toán trước khi tính toán lại. Nếu có nhiều cột phụ thuộc lẫn nhau, Power Pivot sẽ tuân theo trình tự phụ thuộc. Điều này đảm bảo rằng các cột được xử lý theo đúng thứ tự ở 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. Điều này có nghĩa là 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ẽ được quay lui. Điều này nhằm đảm bảo rằng dữ liệu không bị bỏ lại trong 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ệ hay 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, RAND hoặc TODAY không có giá trị cố định. Để tránh vấn đề về hiệu suất, việc thực thi truy vấn hoặc việc lọc thường sẽ không khiến những hàm đó được đánh giá lại nếu chúng được dùng trong cột được tính. Kết quả cho những hàm này chỉ được tính lại khi toàn bộ cột được tính lại. 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 thủ công dữ liệu 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, các hàm biến đổi như NOW, RAND hoặc TODAY sẽ luôn được tính lại nếu hàm này được dùng trong việc xác định một Trường Tính toán.