[[20180818215859]]　『ピボットテーブルの上位項目を抽出したい』（ロア）　ページの最後に飛ぶ

『ピボットテーブルの上位項目を抽出したい』（ロア）

初投稿です。
今、夏休みの自由研究で

　　　　　　8月18日
果物　　 65
　バナナ 45
　りんご 20

魚　　 58
　シャケ 53
　ヒラメ 5

穀物　 17
　　コメ 17

のように、大項目の下に小項目が表示されるピボットテーブルを
作成しています。

このデータを
今日一番多かったのは
【大項目の一番大きな項目名】で、【その件数】です。
内訳は　【小項目その?@】【その件数】

	　 【小項目その?A】		【その件数】	
	　 【小項目その?B】		【その件数】	でした。

のように出力したいです。
イメージ　

大項目の一番大きな項目名と件数については、
ピボットテーブルを件数の大きい順に並び替えると、
項目名と件数については必ず同じセルに存在するため、
=B5 と =C5で解決します。
=Vlookup(rank(範囲),範囲,1,FALSE)　にしたら何故かN/Aになります。。。

ですが、その下の小項目を抽出できないのです。
また、日によって小項目の数が1つだったり2つだったりするので、
安易に大項目を参照してその下3つを抽出、というふうにすると、
小項目ではなく大項目を抽出してしまいます。

なにかいい方法はないでしょうか…。
GETPIBOTDATAも挑戦しているのですが、
その大項目の何番目の項目名を抜き出す
という処理ができなくて困っています。

＜使用 Excel：Excel2016、使用 OS：Windows10 ＞

＞夏休みの自由研究

ということですが
抽出方法を知ることは目的ではないですよね。

手作業で、コピーするのが手っ取り早い思いますが
何か問題ありますか。

関数での抽出は、わたしにはできませんが
マクロで該当箇所をコピーするのはだめですか。

（マナ） 2018/08/19(日) 08:45

 ＞項目名と件数については必ず同じセルに存在するため、 
 ＞=B5 と =C5で解決します。

 それが本当であるとの前提で、ちょっと考えてみました。

 (1) G5セル =B5
 (2) J5セル =C5
 (3) H6セル =IF(ISERR(GETPIVOTDATA("件数",$B$3,"日付",C$4,"大項目",$G$5,"小項目",B6)),"",B6)
 (4) J6セル =IF(H6="","",C6)

 H6とJ6の式を下にコピー

（半平太） 2018/08/19(日) 10:54

半平太さん

貼り付けてみたのですが、
こうなりました。

あと、(3)の式をうまいこと修正したとして、
H6セルには果物、という大項目が出てくることにならないですかね？(´・ω・｀)

野菜 232 件でした。
内訳はレタス 156

	ニンジン	48	
	キャベツ	20	
	タマネギ	8	
というふうに、大項目の中の小項目を出力したいのです。

自分も一晩考えて、うまいことマクロでなんとかできないかと思ってマクロの記録機能を試してみました。

?@ピボットテーブルのスライサーを使って、下のピボットテーブルで、指定の日付で値が一番大きい大項目以外は非表示にする
?A大項目のセルの一つしたのセルから小項目が始まるため、そこを選択して、Ctrl+Shift+↓+→で小項目とその件数をコピー
?B小項目を抽出したいセルの始点を選択して貼り付け。

というふうな３段階の手順でやろうとしました。
こんな感じで出来上がりました。

Sub Macro7()
'
' Macro7 Macro
'

    With ActiveWorkbook.SlicerCaches("スライサー_大項目")
        .SlicerItems("野菜").Selected = True
        .SlicerItems("果物").Selected = False
        .SlicerItems("魚").Selected = False
        .SlicerItems("穀物").Selected = False
        .SlicerItems("肉").Selected = False
        .SlicerItems("(空白)").Selected = False
    End With

    Range("B14").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("H6").Select
    ActiveSheet.Paste
    Range("H6").Select
    Selection.End(xlDown).Select
    Selection.End(xlToRight).Select

    Range("J9").Select
    Application.CutCopyMode = False

End Sub

スライサーで大項目を選別するところが、
「必ず野菜をTrueにし、果物、魚、穀物、肉、空白は必ずFalseにする」
という文章になっています。

この部分を、
「【G5セルにかかれている大項目】をTrueにして、【それ以外】はFalseにする、」
というふうに修正できないでしょうか？

（ロア） 2018/08/19(日) 15:12

大項目を並べ替えてもよいなら、ピボットテーブルは１つでよいですよ
そうすれば半平太さんの式でできました

（マナ） 2018/08/19(日) 15:43

 ピボットテーブルがこちらで(勝手に）１種類だと思ってしまった。　m(__)m

 ２種類あったんですね。確かにそういう図になっていました　(-_-;)

 すると、下のピボットテーブルは、
 実際にはどこのセルから始まるか、いつも決まっている訳じゃないって事になりますね？

（半平太） 2018/08/19(日) 16:12

シートモジュールに貼り付けてください。
（標準モジュールとまちがえないこと）

 Option Explicit

 Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim pvf As PivotField
    Dim t As Range
    Dim n As Long
    Dim r1 As Range, r2 As Range

    If Target.Name <> "pvt1" Then Exit Sub  '★実際の下のピボットの名前に修正

    Application.EnableEvents = False

    With Target.TableRange1.EntireColumn
        n = .Columns.Count
        Set t = .Offset(, n).Resize(, 100)
        t.ClearContents
    End With

    Set pvf = Target.PivotFields("大項目")
    pvf.ClearValueFilters
    pvf.PivotFilters.Add xlTopCount, Target.DataFields(1), 1

    Set r1 = pvf.DataRange
    Set r2 = Intersect(r1, r1.Offset(1)).Resize(, n)

    t.Range("B4").Value = "一番多かったのは"
    t.Range("B5").Value = r1(1).Value
    t.Range("D5").Value = r1(1).Offset(, n - 1).Value
    t.Range("B6").Value = "内訳"
    t.Range("C6").Resize(r2.Rows.Count).Value = r2.Columns(1).Value
    t.Range("D6").Resize(r2.Rows.Count).Value = r2.Columns(n).Value
    t.Range("E6").Offset(r2.Rows.Count - 1).Value = "でした"

    pvf.ClearValueFilters

    Application.EnableEvents = True

 End Sub

（マナ） 2018/08/19(日) 16:59

＞半平太さん
ですね、下のピボットテーブルは上のピボットテーブルの影響を受けるので
どこから始まるかは定まっていません。

自分が
?@上のピボットテーブルを使って一番大きな大項目を見つける
?Aそれを元に下のピボットテーブルから小項目を引き抜く

という回りくどいやり方をしているので、テーブルが2個存在している状況です。
マナさんの言うように、一つで済むのであれば、上のテーブルは必要のないものにはなります。

下のピボットテーブルを上のピボットテーブルの位置に移動させたら正しくマクロが動きました！

 (3) H6セル =IF(ISERR(GETPIVOTDATA("件数",$B$3,"日付",C$4,"大項目",$G$5,"小項目",B6)),"",B6)
はどういう関数なんでしょうか？

＞マナさん
このマクロ(？)でもできました！！！！
ありがとうございます！
（ロア） 2018/08/19(日) 20:47

 ＞ (3) H6セル =IF(ISERR(GETPIVOTDATA("件数",$B$3,"日付",C$4,"大項目",$G$5,"小項目",B6)),"",B6)
 ＞はどういう関数なんでしょうか？

 私宛の質問なんでしょうか？

 次の大項目との境を超えたかどうかを判断するものなのですが、
 少し配慮が足りない部分があったので、没にしてください。

 マナさんのマクロを使ってください。

（半平太） 2018/08/19(日) 22:07

＞一つで済むのであれば、上のテーブルは必要のないものにはなります。

年のため確認します。
マクロでも使用しているのですが
トップテンフィルターをご存じですか。

この機能を使うと、もっとも件数の多いものを
今回だと野菜だけをフィルター表示できます。

ピボットから別のセルに抽出しなくてもよいのであれば
マクロも関数も必要ありません。
ピボットテーブルだけで欲しい結果が得られます。

（マナ） 2018/08/19(日) 23:28

コメント返信：

[ 一覧(最新更新順) ]

[[20180818215859]] 『ピボットテーブルの上位項目を抽出したい』（ロア） ページの最後に飛ぶ

[[20180818215859]]　『ピボットテーブルの上位項目を抽出したい』（ロア）　ページの最後に飛ぶ