数据透视表计算项产生错误 [英] Pivot table calculated item produces error
本文介绍了数据透视表计算项产生错误的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
这是我的数据 - INPUT 1
型号日期产品付款模式
黑莓1/31/2010在线观看卡
黑莓2/1/2010电视卡在线
黑莓1 / 31/2010在线观看现金
黑莓2/3/2010电视卡在线
黑莓2/4/2010电视现金在线
黑莓1/31/2010手机现金店
苹果2/6/2010 HeadPhone Cash Store
苹果1/31/2010观看现金店
苹果2/8/2010手表现金店
我正在尝试构建一个使用两个计算字段的数据透视表。
- Seller1，它刚刚添加了移动和耳机
- 添加电视和手表的Seller2。
Input2，我没有耳机。所以，为Input1工作的代码不起作用。
Cells.Select
ActiveWorkbook.PivotCaches.Create（SourceType ：= xlDatabase，SourceData：= _
Input1！R1C1：R1048576C5，版本：= xlPivotTableVersion12）.CreatePivotTable _
TableDestination：=Output1！R8C2：R20C14，TableName：=PivotTable2 _
DefaultVersion：= xlPivotTableVersion12
表单（Output1）。选择
使用ActiveSheet.PivotTables（PivotTable2）。PivotFields（Product）
。方向= xlColumnField
.Position = 1
结束
ActiveSheet.PivotTables（PivotTable2）。AddDataField
ActiveSheet.PivotTables（_
PivotTable2 ）.PivotFields（Model），Count of Model，xlCount
Range（C9）。选择ActiveSheet.PivotTables（PivotTable2）。PivotFields（Product）。CalculatedItems。 _
添加Seller1，= Mobile + HeadPhone，True
ActiveSheet.PivotTables（PivotTable2）。PivotFields（Product）。CalculatedItems。 _
添加Seller2，= Watch + TV，True
使用ActiveSheet.PivotTables（PivotTable2）。PivotFields（Product）
.PivotItems（HeadPhone）。 Visible = False
.PivotItems（Mobile）Visible = False
.PivotItems（TV）Visible = False
.PivotItems（Watch）Visible = False
.PivotItems（Watch）.Visible = False
.PivotItems（（blank））。Visible = False
End with
End Sub
这是我正在寻找的输出 -
解决方案
只是.PivotItems（'HeadPhone'）。 Visible = False，这是创建错误？最简单的修复是不包括这个项目。然而，您可以通过一些调整使您的代码更加动态：
Dim wb as Workbook
'使用变量，它们更准确，它有助于使您的代码
'更具动态性。
Dim wsIn_1 as Worksheet，wsIn_2 as Worksheet，_
wsOut_1 as Worksheet，wsOut_2 as Worksheet
Dim pt1 as PivotTable，pt2 as PivotTable
'如果你需要
Dim pi as PivotItem
Dim sCalculate as String
'设置它，但是你希望，这只是提示用户。
sCalculate = Inputbox（请输入您要使用的公式）
'假设工作簿运行代码与正在编辑的代码相同。
设置wb = ThisWorkbook
'设置工作表变量
使用wb
设置wsIn_1 = .Sheets（Input1）
设置wsIn_2 = .Sheets （Input2）
设置wsOut_1 = .Sheets（Output1）
设置wsOut_2 = .Sheets（Output2）
结束
'始终命名你的枢轴表。这是好习惯在这种情况下，
'我创建了一个通用名称，但是你可以做一些更精确的事情。
wb.PivotCaches.Create（SourceType：= xlDatabase，SourceData：= _
Input1！R1C1：R1048576C5，版本：= xlPivotTableVersion12）.CreatePivotTable _
TableDestination：=Output1！R8C2 ：R20C14，TableName：=pt_Output_1，_
DefaultVersion：= xlPivotTableVersion12
'最好避免激活并选择，但是我将其包含
'在
'上工作表是非常重要的，所以你可以看到代码运行。没有必要激活它只是为了工作
'它。
wsOut_1.Activate
使用wsOut_1.PivotTables（pt_Output_1）。PivotFields（Product）
.Orientation = xlColumnField
.Position = 1
结束
设置pt2 = wsOut_1.PivotTables（PivotTable2）
带有pt2
.AddDataField
.PivotFields（Model）， Count of Model，xlCount
'所有你需要做的是用字符串变量代替代码中的字符串
'在这里进行某种数据验证是理想的，以确保
'只有有效的字符串可以传入。您还需要以某种方式确保
'您正在输入适当的公式。
.PivotFields（Product）。CalculatedItems.AddSeller1，sCalculate，True
.PivotFields（Product）。CalculatedItems.AddSeller2，= Watch + TV，True
'我注释掉了下面的整个块，因为我不认为这个
'是你想要完成的。相反使用这个：
.PivotFields（Product）。Visible = xlHidden
'With .PivotFields（Product）
'由于它看起来像只是隐藏所有
'使用for循环代替。
'我已经保留所有这些注释掉，因为
'，因为如果没有
'至少一个可见项目，您将遇到错误。
'对于.PivotItems中的每个pi
'pi.Visible = False
'Next
'.PivotItems（HeadPhone）。Visible = False
'.PivotItems（Mobile）。Visible = False
'.PivotItems（TV）。Visible = False
'.PivotItems（Watch）。Visible = False
' .PivotItems（Watch）.Visible = False
'.PivotItems（（blank））。Visible = False
'End With
End with
End Sub
I have two different worksheets (Input1 and Input2) and I am writing a piece of code to build a report. My Code runs successfully on Input1 - worksheet but not on Input 2 - Worksheet.
This is my data - INPUT 1
Model Date Product Payment Mode
Blackberry 1/31/2010 Watch Card Online
Blackberry 2/1/2010 TV Card Online
Blackberry 1/31/2010 Watch Cash Online
Blackberry 2/3/2010 TV Card Online
Blackberry 2/4/2010 TV Cash Online
Blackberry 1/31/2010 Mobile Cash Store
Apple 2/6/2010 HeadPhone Cash Store
Apple 1/31/2010 Watch Cash Store
Apple 2/8/2010 Watch Cash Store
I am trying to build a Pivot table that makes use of two calculated fields.
- Seller1 which just adds Mobile and Headphones
- Seller2 which adds TV and Watch.
In Input2, I don't have a headphone. So, the code that worked for Input1 is not working.
Cells.Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Input1!R1C1:R1048576C5", Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:="Output1!R8C2:R20C14", TableName:="PivotTable2", _
DefaultVersion:=xlPivotTableVersion12
Sheets("Output1").Select
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Product")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField
ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Model"), "Count of Model", xlCount
Range("C9").Select ActiveSheet.PivotTables("PivotTable2").PivotFields("Product").CalculatedItems. _
Add "Seller1", "=Mobile +HeadPhone", True
ActiveSheet.PivotTables("PivotTable2").PivotFields("Product").CalculatedItems. _
Add "Seller2", "=Watch +TV", True
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Product")
.PivotItems("HeadPhone").Visible = False
.PivotItems("Mobile").Visible = False
.PivotItems("TV").Visible = False
.PivotItems("Watch").Visible = False
.PivotItems("Watch ").Visible = False
.PivotItems("(blank)").Visible = False
End With
End Sub
This is the Output that I am looking for -
解决方案
Is it just ".PivotItems('HeadPhone').Visible = False" that is creating the error? The simplest fix would be to not include this item. However you can make your code far more dynamic with a few tweaks:
Dim wb as Workbook
' Use variables, they are more accurate and it helps make your code
' more dynamic.
Dim wsIn_1 as Worksheet, wsIn_2 as Worksheet, _
wsOut_1 as Worksheet, wsOut_2 as Worksheet
Dim pt1 as PivotTable, pt2 as PivotTable
' For use in looping pivotitems if you need to
Dim pi as PivotItem
Dim sCalculate as String
' Set it however you wish, this just prompts the user.
sCalculate = Inputbox("Please enter the formula you would like to use.")
' Assumes that workbook running code is the same one being edited.
Set wb = ThisWorkbook
' Set the worksheet variables
With wb
Set wsIn_1 = .Sheets("Input1")
Set wsIn_2 = .Sheets("Input2")
Set wsOut_1 = .Sheets("Output1")
Set wsOut_2 = .Sheets("Output2")
End With
' Always name your pivot tables. It is good practice. In this case
' I created a generic name but you can make something more precise.
wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Input1!R1C1:R1048576C5", Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:="Output1!R8C2:R20C14", TableName:="pt_Output_1", _
DefaultVersion:=xlPivotTableVersion12
' It is best to avoid activate and select, but I am including it
' in case it is somehow important for the sheet to be active on the
' so you can see the code running. No need to activate it just to work
' on it though.
wsOut_1.Activate
With wsOut_1.PivotTables("pt_Output_1").PivotFields("Product")
.Orientation = xlColumnField
.Position = 1
End With
Set pt2 = wsOut_1.PivotTables("PivotTable2")
With pt2
.AddDataField
.PivotFields("Model"), "Count of Model", xlCount
' All you need to do is replace the string within the code with a string variable
' It would be ideal to have some kind of data validation in here to ensure
' only valid strings can be passed in. You will also need to somehow ensure
' that you are entering an appropriate formula.
.PivotFields("Product").CalculatedItems.Add "Seller1", sCalculate, True
.PivotFields("Product").CalculatedItems.Add "Seller2", "=Watch +TV", True
' I commented out the entire block below since I dont think this
' is what you are trying to accomplish. Instead use this:
.PivotFields("Product").Visible = xlHidden
' With .PivotFields("Product")
' Since it looks like you are simply hiding everything
' use a for loop instead.
' I have made kept all of this commented out because
' because you will run into an error if there isn't at
' least one visible item.
' For each pi in .PivotItems
' pi.Visible = False
' Next
' .PivotItems("HeadPhone").Visible = False
' .PivotItems("Mobile").Visible = False
' .PivotItems("TV").Visible = False
' .PivotItems("Watch").Visible = False
' .PivotItems("Watch ").Visible = False
' .PivotItems("(blank)").Visible = False
' End With
End With
End Sub
这篇关于数据透视表计算项产生错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
