列出第一行字段中给定特定枢轴项的第二行字段(多行字段)的优化枢轴项 [英] List excel pivot items of the second row field (multiple row fields) given a particular pivot item in the first row field
问题描述
Rowlabel1 Rowlabel2值和值
a A 1
B 2
C 3
d D 4
e E 5
我想能够列出 Rowlabel2
的枢轴项,给定一个特定的 Rowlabel1
,以获取ABC
循环,然后连接到ABC
。无论我尝试什么,它只输出所有的枢轴项,ABCDE
。
由于实际数据更复杂,因此无法手动进行。它还有3个行标签,而不是2个标签。
不知道是否有帮助,但解决方案也可以使用这些值。例如,当 letter 时返回值
<= 3
的 Rowlabel2
code>是a
。
感谢任何输入!
code> Sub getPiv()
Dim piv As PivotTable
'将此更改为任何范围,表名称您的pivotottable是
设置piv = ThisWorkbook。表格(SecondTable_Output)。范围(A3)。数据透视表
Dim r As Range
Dim dr As PivotItem
'这将捕获您的第一个条目rowlabel1 - 根据需要更改
设置dr = piv.PivotFields(1).PivotItems(1)
Dim str As String
str =
'如果项目被折叠,宏将不起作用,因此我们将showDetail设置为true以展开它
如果dr.ShowDetail = False然后
dr.ShowDetail = True
End If
与dr
'地址似乎是一个错误(?)如果有多个数据值列,
'datarange似乎将一行向下移动
如果.DataRange.Columns.Count> 1然后
'在这里我们从datarange转回到可枢纽的
的第二列Set r = .DataRange.Offset(-1, - (。DataRange.Cells(1,1).Column - 2))
Else
'这里我们从datarange转回到pivotottable
的第二列Set r = .DataRange.Offset(0, - (。DataRange.Cells(1, 1).Column - 2))
结束如果
结束
'根据需要删除
r.Select
'获取范围为
的单元格值字符串对于i = 1 To r.Rows.Count
str = str& & r.Cells(i,1)
Next i
str = Trim(str)
MsgBox str
End Sub
I searched a long time for this, but still inconclusive. Say I have a pivot table with 2 row labels and 1 data variable looking like:
Rowlabel1 Rowlabel2 Sum of value
a A 1
B 2
C 3
d D 4
e E 5
I want to be able to list the pivot items of Rowlabel2
given a particular Rowlabel1
, to get "A"" B" "C"
in loop, then concat into "ABC"
. Whatever I try, it only outputs all of the pivot items, "ABCDE"
.
Since the real data is more complex, it is not possible to do it manually. It also have 3 row labels rather than 2 labels.
Don't know if it would help but the solution can also make use of the values. For example, return the Rowlabel2
items whose value <= 3
when "letter
" is "a"
.
Thanks for any input!!
Sub getPiv()
Dim piv As PivotTable
'change this to whatever range and sheet names your pivottable is
Set piv = ThisWorkbook.Sheets("SecondTable_Output").Range("A3").PivotTable
Dim r As Range
Dim dr As PivotItem
'this captures your first entry in rowlabel1 - change as necessary
Set dr = piv.PivotFields(1).PivotItems(1)
Dim str As String
str = ""
'the macro won't work if the item is collapsed, so we set showDetail to true to expand it
If dr.ShowDetail = False Then
dr.ShowDetail = True
End If
With dr
'address what seems to be a bug(??) where if there are more than one data value columns,
'the datarange appears to shift one row down
If .DataRange.Columns.Count > 1 Then
'here we shift back from the datarange to the 2nd column of the pivottable
Set r = .DataRange.Offset(-1, -(.DataRange.Cells(1, 1).Column - 2))
Else
'here we shift back from the datarange to the 2nd column of the pivottable
Set r = .DataRange.Offset(0, -(.DataRange.Cells(1, 1).Column - 2))
End If
End With
'delete as necessary
r.Select
'obtain the string of cell values from the range
For i = 1 To r.Rows.Count
str = str & " " & r.Cells(i, 1)
Next i
str = Trim(str)
MsgBox str
End Sub
这篇关于列出第一行字段中给定特定枢轴项的第二行字段(多行字段)的优化枢轴项的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!