Hướng dẫn how to check formula in excel pivot - cách kiểm tra công thức trong excel pivot

Trong một bảng xoay, bạn có thể tạo các trường được tính toán, sử dụng các công thức tùy chỉnh của riêng bạn hoạt động với tổng các trường xoay khác. Có những hạn chế đối với những gì một trường được tính toán có thể làm, nhưng họ thêm một công cụ mạnh mẽ vào các bảng trục trong sổ làm việc Excel của bạn. Ngoài ra, xem sự khác biệt giữa các mục được tính toán và các trường được tính toán

Tác giả: Debra Dalgleish

Trường tính toán bảng Pivot

Trong một bảng Pivot, bạn có thể tạo một trường mới thực hiện tính toán trên tổng các trường xoay khác, sử dụng các công thức của riêng bạn.

Ví dụ, trong ảnh chụp màn hình bên dưới, một trường được tính toán -bonus - tính toán 3% tổng số, nếu hơn 100 đơn vị được bán.

Làm việc với các trường được tính toán

Trong các phần bên dưới, xem cách thêm, thay đổi hoặc xóa các trường được tính toán và hiểu các tính năng và giới hạn của chúng. Có video, các bước bằng văn bản và các tập tin mẫu để tải xuống.

- Giới thiệu về các trường được tính toán

- Thêm một trường tính toán

- Các vấn đề với tổng số trường được tính toán

- Thay đổi hoặc xóa trường tính toán

- Tạo danh sách các công thức bảng trục

- Tải xuống các tệp mẫu

Về các trường tính toán

Dưới đây là một số tính năng và giới hạn của các trường được tính toán:

Đặc trưng

• Đối với các trường được tính toán, số lượng riêng lẻ trong các trường giá trị khác được tổng hợp và sau đó tính toán được thực hiện trên tổng số tiền.
• Các trường được tính toán được tự động có sẵn trong tất cả các bảng trục dựa trên cùng một bộ đệm xoay vòng

Giới hạn

• Các công thức trường được tính toán không thể tham khảo tổng số bảng hoặc phụ
• Các công thức trường được tính toán không thể tham khảo các ô bảng tính theo địa chỉ hoặc theo tên.
• Tổng là chức năng duy nhất có sẵn cho một trường tính toán.
• Các trường được tính toán không có sẵn trong bảng trục dựa trên OLAP.

Thêm một trường tính toán đơn giản

Trong ví dụ này, báo cáo bảng Pivot có hai trường giá trị:

• Tổng số cột hiển thị tổng số đô la bán hàng cho mỗi đại diện bán hàng, cho mỗi sản phẩm
• Trường đơn vị tóm tắt số lượng đơn vị được bán

Chúng ta cần thêm một cột khác trong khu vực giá trị, với một công thức đặc biệt:

• Nhân tổng số doanh số trong mỗi hàng với 3%, để tính toán các khoản thanh toán tiền thưởng

Lưu ý: Bạn có thể tải xuống tệp mẫu cho ví dụ này, trong phần tải xuống, bên dưới.: You can download the sample file for this example, in the download section, below.

Tính tiền thưởng bán hàng

Để tạo một công thức đặc biệt trong bảng trục, bạn có thể thêm một trường tính toán. Chúng tôi sẽ tạo một lĩnh vực được tính toán để hiển thị số lượng đại diện bán hàng sẽ kiếm được bao nhiêu, dựa trên phần thưởng 3% trên tổng doanh số của họ.

Để tính toán phần thưởng 3% trong ô bảng tính bình thường, bạn có thể sử dụng công thức này, trong ô D5:

• = C5 * 3%

Trong trường được tính toán của chúng tôi, công thức sẽ tương tự, nhưng nó sẽ:

• Tham khảo tên trường (tổng cộng)Total)
• Thay vì ô bảng tính (C5)C5)

Để thêm một trường tính toán:

Để hiển thị các phần thưởng, hãy làm theo các bước bên dưới, để thêm một trường tính toán vào bảng trục.

• Để bắt đầu, hãy chọn bất kỳ ô nào trong bảng trục
• Tiếp theo, trên dải ruy băng Excel, đi đến tab Phân tích Pivottable
• Trong nhóm tính toán, nhấp vào các trường, mục và bộ
• Sau đó, trong menu thả xuống, nhấp vào lệnh trường được tính toán

Chèn hộp thoại trường tính toán

Hộp thoại Chèn được tính toán, trong đó bạn có thể tạo một trường được tính toán mới hoặc thay đổi các trường được tính toán hiện có

Để tạo trường tính toán bảng Pivot mới, hãy làm theo các bước sau:

• Đầu tiên, nhập một tên duy nhất cho trường được tính toán - trong ví dụ này, trường được tính toán mới được đặt tên, Repbonusunique name for the calculated field - in this example, the new calculated field is named, RepBonus
  • Một bảng trục không thể có tên trường được tính toán trùng lặp
• CẢNH BÁO: Nếu bạn nhập tên của trường được tính toán hiện có, Excel sẽ thay thế công thức trường tính toán đó bằng công thức mới mà bạn nhập: If you enter the name of an existing calculated field, Excel will replace that calculated field's formula with the new formula that you enter
• Nhấn phím tab trên bàn phím của bạn, để di chuyển đến hộp công thức
• Trong hộp công thức, nhập công thức tính toán tiền thưởng: = tổng cộng * 3%=Total * 3%
  • Mẹo: Thay vì gõ tên trường, bạn có thể nhấp đúp vào chúng trong danh sách các trường, bên dưới hộp công thức: Instead of typing field names, you can double-click on them in the Fields list, below the Formula box
• Tiếp theo, nhấp vào nút Thêm để lưu trường được tính toán
  • Trường được tính toán mới được thêm vào danh sách các trường trong hộp thoại, bên dưới các trường hiện có
• Sau đó, nhấp vào nút Đóng, đóng hộp thoại và quay lại bảng tính.

Trường mới trong bảng xoay vòng

Khi bạn trở lại bảng tính Excel, bảng Pivot có một trường mới có tên Repbonus

Trường mới xuất hiện ở 2 nơi:

1. Trong khu vực giá trị của bố cục bảng tính bảng, ở bên phải của các trường khác
2. Ở dưới cùng của danh sách hộp kiểm của các trường, trong danh sách trường PivotTable.

Thay đổi chú thích trường (tùy chọn)

Trong bố cục bảng Pivot, trường mới có "tổng" khi bắt đầu chú thích của nó.

Để làm cho chú thích ngắn hơn, bạn có thể nhập một chú thích khác trong ô tiêu đề.

• Lưu ý: Chú thích không thể giống hệt như tên trường bảng Pivot.cannot be exactly the same as the pivot table field's name.

Trong ảnh chụp màn hình bên dưới, tôi đã chọn ô D4

Sau đó, để thay thế chú thích dài, tôi đã gõ "repbonus" - có một ký tự không gian ở cuối chú thích đó

Thêm một trường được tính toán phức tạp

Trong ví dụ này, bảng Pivot hiển thị tổng doanh số cho mỗi đại diện bán hàng trên mỗi sản phẩm và trường đơn vị tóm tắt số lượng đơn vị được bán. Chúng tôi sẽ tạo một trường được tính toán sử dụng hai trường này và hàm IF.

Các đại diện bán hàng sẽ kiếm được 3 phần trăm tiền thưởng nếu họ đã bán được hơn 100 đơn vị của bất kỳ sản phẩm nào. Để hiển thị tiền thưởng, bạn có thể thêm một trường tính toán vào bảng trục. Trong ví dụ này, công thức sẽ kiểm tra trường đơn vị, để xem liệu có hơn 100 đơn vị đã được bán và nhân tổng số trường với 3%.

Bạn có thể tải xuống tệp mẫu cho hướng dẫn này, trong phần tải xuống, bên dưới.

Để thêm một trường tính toán:

1. Thực hiện theo các bước trên, để mở hộp thoại trường được tính toán chèn
2. Nhập một tên cho trường tính toán, ví dụ, tiền thưởng.
3. Trong hộp công thức, nhập công thức IF đề cập đến các đơn vị và tổng trường: = if (đơn vị> 100, tổng*3%, 0)
   =IF(Units>100,Total*3%,0)
4. Nhấp vào Thêm để lưu trường tính toán, sau đó nhấp vào Đóng.

5. Trường tiền thưởng xuất hiện trong khu vực giá trị của bảng Pivot và trong danh sách trường trong danh sách trường PivotTable.

Trường tính toán với vòng tròn

Ngoài hàm IF, như được hiển thị trong ví dụ trước, bạn có thể sử dụng các hàm Excel khác để tạo các công thức trường được tính toán.

Trong ví dụ này, xem cách sử dụng các chức năng làm tròn Excel - Round hoặc Mround - trong một trường được tính toán, để dự kiến ​​doanh số sản phẩm cho quý sắp tới.

Lưu ý: Bạn có thể lấy tệp mẫu cho ví dụ này - đó là tệp số 1 trong phần tải xuống, bên dưới: You can get the sample file for this example -- it's file #1 in the download section, below

Bảng Pivot cho dự đoán bán hàng

Trong bảng xoay vòng này được hiển thị bên dưới, doanh số quý đầu tiên cho mỗi sản phẩm được tóm tắt

Để dự báo doanh số quý hai, chúng tôi sẽ tạo hai trường được tính toán, dựa trên số lượng đơn vị được bán.

1. DN03 - một dự đoán bi quan - doanh số sẽ giảm 3% - a pessimistic projection - sales will go down 3%
2. UP05 - một dự đoán lạc quan - doanh số sẽ tăng 5% - an optimistic projection - sales will go up 5%

1) Tạo phép chiếu bi quan

Để tạo trường tính toán cho dự báo bán hàng bi quan, hãy làm theo các bước sau:

• Đầu tiên, chọn một ô trong bảng Pivot và làm theo các bước trên, để mở hộp thoại Trường được tính toán chèn
• Nhập tên cho trường được tính toán mới, DN03Dn03
• Trong hộp công thức, nhập công thức sau (xem ghi chú bên dưới, về cách hoạt động của hàm này):
  • = Vòng (đơn vị * 0,97, 0)
• Nhấp vào Thêm để lưu trường được tính toán mới
• Tiếp theo, nhấp vào nút Đóng, để quay lại bảng tính.

Phép chiếu bi quan trong bảng xoay

Trường được tính toán mới xuất hiện trong bảng trục, ở bên phải của trường giá trị hiện có.

Tiêu đề mặc định của nó là tổng của DN03 và tôi đã thay đổi tiêu đề thành "DN 3%".Sum of Dn03, and I changed the heading to "Dn 3%".

Chức năng tròn Excel

Công thức trường được tính toán mới sử dụng hàm tròn, làm tròn một số vào số gần nhất và với một số chữ số cụ thể được làm tròn.

• = Vòng (đơn vị * 0,97, 0)

Nhấp vào Thêm để lưu trường được tính toán mới

• Tiếp theo, nhấp vào nút Đóng, để quay lại bảng tính. - the value to be rounded (Units * 0.97)
• Phép chiếu bi quan trong bảng xoay - the number of digits to which the value should be rounded, to the left or right of the decimal point (0)

Trường được tính toán mới xuất hiện trong bảng trục, ở bên phải của trường giá trị hiện có.

Tiêu đề mặc định của nó là tổng của DN03 và tôi đã thay đổi tiêu đề thành "DN 3%".

• Đầu tiên, chọn một ô trong bảng Pivot và làm theo các bước trên, để mở hộp thoại Trường được tính toán chèn
• Nhập tên cho trường được tính toán mới, DN03Up05
• Trong hộp công thức, nhập công thức sau (xem ghi chú bên dưới, về cách hoạt động của hàm này):
  • = Vòng (đơn vị * 0,97, 0)
• Nhấp vào Thêm để lưu trường được tính toán mới
• Tiếp theo, nhấp vào nút Đóng, để quay lại bảng tính.

Phép chiếu bi quan trong bảng xoay

Trường được tính toán mới xuất hiện trong bảng trục, ở bên phải của trường giá trị hiện có.

Tiêu đề mặc định của nó là tổng của DN03 và tôi đã thay đổi tiêu đề thành "DN 3%".Sum of Up05, and I changed the heading to "Up 5%".

Chức năng tròn Excel

Công thức trường được tính toán mới sử dụng hàm tròn, làm tròn một số vào số gần nhất và với một số chữ số cụ thể được làm tròn.

• = Vòng (đơn vị * 0,97, 0)

Nhấp vào Thêm để lưu trường được tính toán mới

• Tiếp theo, nhấp vào nút Đóng, để quay lại bảng tính. - the value to be rounded (Units * 1.05)
• Phép chiếu bi quan trong bảng xoay - the multiple to which the value should be rounded (5)

Trường được tính toán mới xuất hiện trong bảng trục, ở bên phải của trường giá trị hiện có.

Tiêu đề mặc định của nó là tổng của DN03 và tôi đã thay đổi tiêu đề thành "DN 3%".

• Chức năng tròn Excelnumber by the multiple
• Công thức trường được tính toán mới sử dụng hàm tròn, làm tròn một số vào số gần nhất và với một số chữ số cụ thể được làm tròn.remainder of that division
• Hàm tròn có hai đối số và cả hai đều được yêu cầu:multiple in half

Số - Giá trị được làm tròn (đơn vị * 0,97)remainder less than half of the multiple?

• num_digits - số chữ số mà giá trị phải được làm tròn, sang trái hoặc bên phải của dấu thập phân (0)
• Tạo hình chiếu lạc quan

Tiếp theo, để tạo một trường tính toán cho dự báo bán hàng lạc quan, hãy làm theo các bước sau:

Nhập tên cho trường được tính toán mới, UP05

• D2: Phần còn lại từ 126/5 là 1, dưới 2,5, làm tròn xuống: remainder from 126/5 is 1, less than 2.5, round down
• D3: Phần còn lại từ 133,4/5 là 3,4, lớn hơn 2,5, làm tròn: remainder from 133.4/5 is 3.4, greater than 2.5, round up

Vấn đề - Tổng số trường được tính toán

Khi bạn tạo một trường được tính toán, bạn có thể mong đợi sẽ thấy một khoản tiền của các số tiền được tính toán, trong tổng số tổng số và tổng số lớn của bảng. Tuy nhiên, trường được tính toán sử dụng cùng một tính toán trong tổng số tổng số và lớn, thay vì hiển thị một khoản tiền.

Ví dụ: trong trường Tính toán được tính toán ở trên, tổng tiền thưởng cho Andrew là 164,23, thay vì 70,13 (23,65+33,83+12,65). Điều này xảy ra bởi vì Excel sử dụng cùng một tính toán trong các hàng tổng và phụ, thay vì tổng hợp.

Tổng số đơn vị cho Andrew lớn hơn 100, vì vậy tổng tiền thưởng được tính là 3% tổng số của Andrew.

Bạn có thể tải xuống tệp mẫu cho hướng dẫn này, trong phần tải xuống, bên dưới.

Không có cài đặt bảng Pivot mà bạn có thể thay đổi, vì vậy nó sẽ tổng hợp các trường được tính toán, thay vì sử dụng công thức trường được tính toán trên tổng số. Tuy nhiên, bạn có thể sử dụng một trong các cách giải quyết sau:

Áp dụng một bộ lọc

Để ẩn các hàng không đủ điều kiện nhận tiền thưởng, hãy lọc trường sản phẩm cho các đơn vị> 100, để phù hợp với tính toán tiền thưởng. Với bộ lọc được áp dụng, các phép phụ và tổng số lớn là chính xác.

Ẩn các phần phụ và tổng số lớn

Một cách giải quyết khác là loại bỏ các phép phụ và tổng số lớn từ trường đại diện bán hàng.

• Để tắt các phép phụ, nhấp chuột phải vào một trong các tên đại diện bán hàng và nhấp vào "Rep", để xóa dấu kiểm.
• Để tắt tổng số Grand, nhấp chuột phải vào Grand Total Label và bấm Xóa tổng số Grand.

Tính toán bên ngoài bảng trục

Nếu bố cục bảng trục của bạn sẽ không thay đổi, một cách giải quyết khác là tính toán các phép phụ và tổng số, bên ngoài bảng trục, trong các cột ở bên phải. Để kỹ thuật này hoạt động chính xác, thay đổi bố cục bảng trục từ hình thức nhỏ gọn sang hình thức phác thảo.

Trong ảnh chụp màn hình bên dưới, phần thưởng là một trường được tính toán, trong cột P. Công thức đã được thêm vào trong các cột Q, R và S và cột Q có định dạng có điều kiện, do đó, nó phù hợp với kiểu bảng Pivot.

• Công thức trong ô R12: = if (l12 = "", r11, sum (r11,1)))=IF(L12="",R11,SUM(R11,1))
• Công thức trong ô S12: = if (m12 = "", 0, p12)=IF(M12="",0,P12)
• Công thức trong ô Q12: = if (l12 = "tổng số lớn", sum (s: s), if (l12 "", sumif (r: r, r12, s: s), s12))=IF(L12="Grand Total",SUM(S:S),IF(L12"",SUMIF(R:R,R12,S:S),S12))

Sao chép các công thức xuống hàng 22, nơi có tổng số lớn.

Sau đó, các cột P, R và S có thể được ẩn, chỉ để lại phần thưởng được tính toán trong cột Q.

Thay đổi hoặc xóa trường tính toán

Sau khi bạn tạo một trường tính toán, bạn có thể cần thay đổi công thức của nó. Hoặc, bạn có thể muốn xóa một trường tính toán, nếu bạn không còn cần nó trong bảng trục.

- Sửa đổi trường tính toán

- Xóa trường tính toán bảng trục

- Macro để loại bỏ trường tính toán bảng trục

Sửa đổi một trường được tính toán

Sau khi bạn tạo một trường tính toán, bạn có thể cần thay đổi công thức của nó. Trong ví dụ này, chúng tôi sẽ thay đổi phần trăm tiền thưởng cho công thức trường được tính toán được tạo trong phần trước.

Xem video để xem các bước và các hướng dẫn bằng văn bản nằm dưới video. Để lấy tệp mẫu cho video này, hãy chuyển đến phần tải xuống bên dưới.

1. Thực hiện theo các bước trên, để mở hộp thoại trường được tính toán
2. Nhấp vào hộp mũi tên trong tên tên và chọn trường được tính toán mà bạn muốn sửa đổi - phần thưởng trong ví dụ này.
3. Trong hộp công thức, thực hiện các thay đổi đối với công thức - trong ví dụ này, tỷ lệ phần trăm đã được thay đổi từ 3%thành 4%: = if (đơn vị> 100, tổng số*4%, 0)
   =IF(Units>100,Total*4%,0)
4. Nhấp vào nút Sửa đổi, để lưu các thay đổi trường được tính toán, sau đó nhấp vào Đóng.

Xóa trường tính toán

Trong ví dụ này, bảng Pivot có một trường được tính toán có tên là tiền thưởng. Nó xuất hiện trong khu vực giá trị dưới dạng tổng tiền thưởng. Bạn có thể tạm thời ẩn trường được tính toán, hoặc xóa vĩnh viễn nó khỏi bảng trục.

Tạm thời xóa một trường tính toán

Để tạm thời xóa một trường tính toán khỏi bảng trục, hãy làm theo các bước sau:

1. Trong bảng trục, nhấp chuột phải vào một ô trong trường tính toán. Trong ví dụ này, chúng tôi sẽ nhấp chuột phải vào trường thưởng.
2. Trong menu bật lên, nhấp vào lệnh Xóa hiển thị tên của trường được tính toán. & Nbsp; & nbsp;

Trường được tính toán được loại bỏ khỏi bố cục bảng trục, nhưng vẫn nằm trong danh sách trường có thể pivott.

Sau đó, bạn có thể thêm một dấu kiểm vào trường được tính toán trong danh sách trường có thể pivott, để trả nó vào bố cục bảng Pivot.

Xóa vĩnh viễn một trường tính toán

Để loại bỏ vĩnh viễn một trường được tính toán, hãy làm theo các bước này để xóa nó:

1. Chọn bất kỳ ô trong bảng Pivot.
2. Trên ruy băng, dưới tab Công cụ PivotTable, & NBSP; Nhấp vào tab Tùy chọn (Phân tích trong Excel 2013).
3. Trong nhóm công cụ, nhấp vào công thức, sau đó nhấp vào trường tính toán.
4. Từ danh sách thả xuống tên, chọn tên của trường được tính toán bạn muốn xóa.
5. Nhấp vào Xóa, sau đó bấm OK để đóng hộp thoại.

Macro để loại bỏ trường tính toán

Trong Excel VBA, nếu bạn cố gắng thay đổi hướng cho trường được tính toán, Excel sẽ hiển thị thông báo lỗi "Lỗi thời gian chạy '1004': Không thể đặt thuộc tính định hướng của lớp Pivotfield"

Bạn có thể bỏ chọn thủ công các hộp trường được tính toán và xóa chúng khỏi bảng trục, sau đó chọn lại hộp, để đặt nó trở lại bố cục. Tuy nhiên, nếu bạn ghi lại mã trong khi xóa trường được tính toán, mã được ghi lại sẽ hiển thị cùng một thông báo lỗi khi bạn cố gắng chạy nó.

Xem video này để xem một macro sẽ xóa tất cả các trường được tính toán mà không hiển thị thông báo lỗi. Liên kết tải xuống và mã mẫu nằm dưới video.

Mã sau đây loại bỏ tất cả các trường được tính toán bằng cách thay đổi thuộc tính hiển thị thành sai. Bạn có thể tải xuống tệp mẫu bên dưới, để kiểm tra mã. Nhờ Rory Archibald, từ Excel Matters, đã đề xuất giải pháp này.

Bạn phải có một ô bảng xoay được chọn và chỉ có bảng xoay vòng đó bị ảnh hưởng.

Sub RemoveALLCalculatedFields() Dim pt As PivotTable Dim pf As PivotField Dim df As PivotField On Error Resume Next Set pt = ActiveCell.PivotTable If pt Is Nothing Then MsgBox "Select a pivot table cell" Exit Sub End If For Each pf In pt.CalculatedFields For Each df In pt.DataFields If df.SourceName = pf.Name Then With df .Parent.PivotItems(.Name) _ .Visible = False End With Exit For End If Next df Next pf End Sub

Tạo một danh sách các công thức bảng trục

Sau khi bạn thêm các trường được tính toán hoặc các mục được tính toán trong bảng trục, bạn có thể tạo một danh sách nhanh tất cả các công thức trong các tính toán đó.

- Tạo danh sách các công thức bảng trục

- Danh sách macro tất cả các công thức cho tất cả các bảng trục

VIDEO: Tạo danh sách nhanh các công thức bảng Pivot

Với lệnh tích hợp, bạn có thể nhanh chóng tạo một danh sách các trường được tính toán và các mục được tính toán trong bảng Pivot đã chọn. Xem video ngắn này để xem các bước và các hướng dẫn bằng văn bản nằm dưới video.

Tạo danh sách các công thức bảng trục

Với lệnh bảng Pivot tích hợp, bạn có thể nhanh chóng tạo một danh sách các trường được tính toán và các mục được tính toán trong bảng trục đã chọn.

LƯU Ý: Tất cả các bảng trục có chung bộ đệm xoay cũng sẽ chia sẻ cùng các trường được tính toán và các mục được tính toán.

Liệt kê các công thức bảng xoay trong Excel 2010 và Excel 2013

1. Chọn bất kỳ ô trong bảng Pivot.
2. Trên dải ruy băng, trong tab Công cụ PivotTable, nhấp vào tab Tùy chọn (phân tích trong Tab Excel 2013).
3. Trong nhóm tính toán, nhấp vào các trường, mục & bộ
4. Nhấp & NBSP; Danh sách công thức.

Liệt kê các công thức bảng xoay trong Excel 2007

1. Chọn bất kỳ ô trong bảng Pivot.
2. Trên dải ruy băng, trong tab Công cụ PivotTable, nhấp vào tab Tùy chọn (phân tích trong Tab Excel 2013).
3. Trong nhóm tính toán, nhấp vào các trường, mục & bộ
4. Nhấp & NBSP; Danh sách công thức.

Liệt kê các công thức bảng xoay trong Excel 2007

Trên dải ruy băng, dưới tab Công cụ PivotTable, nhấp vào tab Tùy chọn.

1. Chọn bất kỳ ô trong bảng Pivot.
2. Trên dải ruy băng, trong tab Công cụ PivotTable, nhấp vào tab Tùy chọn (phân tích trong Tab Excel 2013).
3. Trong nhóm tính toán, nhấp vào các trường, mục & bộ

Nhấp & NBSP; Danh sách công thức.

Liệt kê các công thức bảng xoay trong Excel 2007

Trên dải ruy băng, dưới tab Công cụ PivotTable, nhấp vào tab Tùy chọn.

Trong nhóm công cụ, nhấp vào công thức

Một tờ mới được chèn vào sổ làm việc, với một danh sách các trường được tính toán và danh sách các mục được tính toán.

Liệt kê các công thức bảng xoay trong Excel 2003

Sub ListAllPivotFormulas() 'www.contextures.com 'print all the pivot table formulas 'in the active workbook Dim lRow As Long Dim wb As Workbook Dim ws As Worksheet Dim wsFP As Worksheet Dim pt As PivotTable Dim pf As PivotField Dim cf As Variant 'calculated field Dim ci As Variant 'calculated item Dim strSh As String Dim lPI As Long On Error Resume Next Application.DisplayAlerts = False Set wb = ActiveWorkbook strSh = "FP_" & Format(Date, "yyyymmdd") On Error Resume Next Worksheets(strSh).Delete On Error GoTo exitHandler Set wsFP = Worksheets.Add With wsFP .Name = strSh .Columns("A:E").NumberFormat = "@" 'text format .Range(.Cells(1, 1), .Cells(1, 7)).Value _ = Array("ID", "Sheet", "PivotTable", _ "Type", "Field", "Name", "Formula") .Rows(1).Font.Bold = True End With lRow = 2 For Each ws In wb.Worksheets If ws.PivotTables.Count > 0 Then For Each pt In ws.PivotTables For Each cf In pt.CalculatedFields wsFP.Range(wsFP.Cells(lRow, 1), _ wsFP.Cells(lRow, 7)).Value _ = Array(lRow - 1, _ ws.Name, pt.Name, _ "Calc Field", , cf.Name, _ " " & cf.Formula) lRow = lRow + 1 Next cf For Each pf In pt.PivotFields On Error Resume Next lPI = 0 lPI = pf.CalculatedItems.Count On Error GoTo errHandler If lPI > 0 Then For Each ci In pf.CalculatedItems wsFP.Range(wsFP.Cells(lRow, 1), _ wsFP.Cells(lRow, 7)).Value _ = Array(lRow - 1, _ ws.Name, pt.Name, _ "Calc Item", pf.Name, _ ci.Name, " " & ci.Formula) lRow = lRow + 1 Next ci End If Next pf Next pt End If Next ws wsFP.Columns("A:G").EntireColumn.AutoFit exitHandler: Application.DisplayAlerts = True Exit Sub errHandler: MsgBox "Could not list formulas" Resume exitHandler End Sub

Trên thanh công cụ Pivot, nhấp vào PivotTable.

1. Nhấp vào Công thức, sau đó nhấp vào & NBSP; Danh sách công thức.: Sample file for the Simple Calculated field and Complex Calculated Field tutorials: Calculated Field Samples
2. Một tờ mới được chèn vào sổ làm việc, với danh sách các trường được tính toán và các mục được tính toán (xem ví dụ Excel 2007 ở trên).: Sample file with the code to create a list of all pivot table formulas. The file is zipped, and is in xlsm format. The file contains macros, so enable them to test the code.
3. Danh sách macro tất cả các công thức cho tất cả các bảng trục: Sample file with the code to remove all Calculated Fields. The file is zipped, and is in xlsm format. The file contains macros, so enable them to test the code:

Để tạo một danh sách tất cả các công thức trong một bảng xoay cụ thể, bạn có thể sử dụng lệnh Danh sách các công thức, như được hiển thị ở trên.

Không có lệnh tích hợp nào sẽ liệt kê các công thức cho tất cả các bảng trục trong sổ làm việc, nhưng bạn có thể sử dụng macro để làm điều đó.

Trong mã mẫu được hiển thị bên dưới, một bảng tính mới được thêm vào sổ làm việc đang hoạt động, với danh sách tất cả các mục được tính toán và trường được tính toán, trong tất cả các bảng trục.

Để tải xuống tệp mẫu, chứa mã, hãy chuyển đến phần tải xuống bên dưới.

Tải xuống các tệp mẫu

Ví dụ trường tính toán: Tệp mẫu cho trường tính toán đơn giản và hướng dẫn trường được tính toán phức tạp: Các mẫu trường được tính toán

Danh sách công thức Macro: Tệp mẫu với mã để tạo danh sách tất cả các công thức bảng Pivot. Tệp được nén và ở định dạng XLSM. Tệp chứa macro, vì vậy cho phép chúng kiểm tra mã.

Xóa macro CF: Tệp mẫu với mã để xóa tất cả các trường được tính toán. Tệp được nén và ở định dạng XLSM. Tệp chứa macro, vì vậy hãy cho phép chúng kiểm tra mã:

Nhiều hướng dẫn hơn

Các mục được tính toán so với các trường được tính toán

Trường được tính toán - Đếm

Làm cách nào để xem chi tiết trong một pivottable?

Tại sao trường tính toán bị vô hiệu hóa trong pivottable?

Bạn có thể làm một vlookup trong một trục không?

Làm thế nào để bạn phân tích dữ liệu từ một pivottable in excel?