Show
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 PivotTrong 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ánTrong 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ánDướ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
Giới hạn
Thêm một trường tính toán đơn giảnTrong ví dụ này, báo cáo bảng Pivot có hai trường giá trị:
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:
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:
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ẽ:
Để 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.
Chèn hộp thoại trường tính toánHộ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:
Trường mới trong bảng xoay vòngKhi 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:
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 đề.
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ạpTrong 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:
Trường tính toán với vòng trònNgoà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àngTrong 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) 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:
Phép chiếu bi quan trong bảng xoayTrườ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 ExcelCô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.
Nhấp vào Thêm để lưu trường được tính toán mới
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%".
Phép chiếu bi quan trong bảng xoayTrườ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 ExcelCô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.
Nhấp vào Thêm để lưu trường được tính toán mới
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%".
Số - Giá trị được làm tròn (đơn vị * 0,97)remainder less than half of the multiple?
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
Vấn đề - Tổng số trường được tính toánKhi 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ớnMộ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ính toán bên ngoài bảng trụcNế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.
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ánSau 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ánSau 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.
Xóa trường tính toánTrong 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:
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ó:
Macro để loại bỏ trường tính toánTrong 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ụcSau 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 PivotVớ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ụcVớ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
Liệt kê các công thức bảng xoay trong Excel 2007
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.
Nhấp & NBSP; Danh sách công thức. Liệt kê các công thức bảng xoay trong Excel 2007Trê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.
Để 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?Hiển thị chi tiết trường giá trị trong một pivottable, hãy thực hiện một trong những điều sau đây: Nhấp chuột phải vào một trường trong khu vực giá trị của pivottable, sau đó nhấp vào hiển thị chi tiết. Bấm đúp vào một trường trong khu vực giá trị của pivotTable. Dữ liệu chi tiết mà trường giá trị dựa trên, được đặt trên một bảng tính mới.Right-click a field in the values area of the PivotTable, and then click Show Details. Double-click a field in the values area of the PivotTable. The detail data that the value field is based on, is placed on a new worksheet.
Tại sao trường tính toán bị vô hiệu hóa trong pivottable?Khi sử dụng bảng trục, bạn có thể khám phá trường tính toán Excel bị mờ đi.Một trong những lý do chính cho điều này là dữ liệu cơ bản là từ nguồn OLAP.the underlying data is from an OLAP source.
Bạn có thể làm một vlookup trong một trục không?Một trong những chức năng phổ biến nhất trong các công thức Excel là Vlookup.Nhưng, bạn không thể sử dụng vlookup trong trục nguồn.Điều này chủ yếu là do trong trục nguồn, các biểu thức phân tích dữ liệu (DAX) không lấy phạm vi ô hoặc ô như một tham chiếu như Vlookup thực hiện trong Excel.you can't use VLOOKUP in Power Pivot. This is primarily because in Power Pivot, Data Analysis Expressions (DAX) functions don't take a cell or cell range as a reference—as VLOOKUP does in Excel.
Làm thế nào để bạn phân tích dữ liệu từ một pivottable in excel?Tạo một pivottable trong Excel cho Windows.. Chọn các ô bạn muốn tạo ra một pivottable..... Chọn Chèn> PivotTable .. Điều này sẽ tạo ra một pivottable dựa trên một bảng hoặc phạm vi hiện có..... Chọn nơi bạn muốn báo cáo có thể được đặt..... Bấm OK .. |