【Excel】要素数が不定な場合のGETPIVOTDATAの使い方を教

質問

【Excel】要素数が不定な場合のGETPIVOTDATAの使い方を教えてください。

Excel 2003で添付画像のようなサンプルデータを作成しています。

以下の４つのテーブルがあります。
・テーブル(1)：受注データ
　（条件）
　　・品名，メーカーは必須
　　・メーカーの要素数は不定
　　・タイプは空白の場合もあり

　　　品名　　｜メーカー　｜タイプ　｜　数量
　-------------------------------------------
　テレビ　　　｜　　A　　｜　1　　 ｜　1
　スピーカー　｜　　D　　｜　　　　｜　2
　テレビ　　　｜　　C　　｜　　　　｜　3
　エアコン　　｜　　A　　｜　1　　 ｜　4
　スピーカー　｜　　A　　｜　　　　｜　1
　テレビ　　　｜　　B　　｜　　　　｜　2
　エアコン　　｜　　A　　｜　1　　 ｜　3
　テレビ　　　｜　　A　　｜　1　　 ｜　4
　テレビ　　　｜　　A　　｜　2　　 ｜　1
　スピーカー　｜　　A　　｜　　　　｜　2

・テーブル(2)：価格データ
　メーカー　｜　　品名　　　 ｜　タイプ ｜　価格
　--------------------------------------------------
　　　A　　｜　　エアコン　　｜　　1　　｜　100,000
　　　A　　｜　　スピーカー　｜　　　　 ｜　10,000
　　　A　　｜　　テレビ　　　｜　　1　　｜　100,000
　　　A　　｜　　テレビ　　　｜　　2　　｜　70,000
　　　A　　｜　　テレビ　　　｜　　3　　｜　50,000
　　　B　　｜　　テレビ　　　｜　　　　 ｜　50,000
　　　C　　｜　　テレビ　　　｜　　　　 ｜　120,000
　　　D　　｜　　スピーカー　｜　　　　 ｜　7,000

・テーブル(3)：テレビの合計受注数量，価格
　メーカー　｜　タイプ｜　単価　　　｜　合計数量　｜　　小計　
　----------------------------------------------------------------
　　　A　　｜　　1　　｜　100,000　 ｜　　5　　　 ｜　　500,000
　　　A　　｜　　2　　｜　70,000　　｜　　1　　　 ｜　　70,000
　　　B　　｜　　　　 ｜　50,000　　｜　　2　　　 ｜　　100,000
　　　C　　｜　　　　 ｜　120,000　 ｜　　3　　　 ｜　　360,000

・ピボットテーブル：受注データの集計
　-------------------------------------------------------
　合計 / 数量
　-------------------------------------------------------
　　　品名　　｜　メーカー　｜　　タイプ　　｜　　集計
　-------------------------------------------------------
　エアコン　　｜　　A　　　 ｜　　1　　　　 ｜　　7
　スピーカー　｜　　A　　　 ｜　　(空白)　　｜　　3
　　　　　　　｜　　D　　　 ｜　　(空白)　　｜　　2
　テレビ　　　｜　　A　　　 ｜　　1　　　　 ｜　　5
　　　　　　　｜　　　　　　｜　　2　　　　 ｜　　1
　　　　　　　｜　　B　　　 ｜　　(空白)　　｜　　2
　　　　　　　｜　　C　　　 ｜　　(空白)　　｜　　3
　-------------------------------------------------------
　　　総計　　｜　　　　　　｜　　　　　　　｜　　23

価格データおよびピボットテーブルから、テーブル(3)を生成したいのですが、ピボットテーブルからの値の抽出に苦戦しています。

受注合計数はピボットテーブルから以下のような数式で取り出しています。
　=GETPIVOTDATA("合計 / 数量",F1,"品名","テレビ","メーカー","A","タイプ",1)
しかし品名，メーカー，タイプを1つ1つ手入力しているため、ピボットテーブルの要素数が増えた場合に動的に対応できません。
　　※新たにメーカーAのタイプ3の受注が入った場合など

このように要素数が不定なピボットテーブルから、テレビの行をすべて抽出するにはどうすればよいでしょうか？

なお、テーブル(2)は実際には莫大な行数なので、テーブル(2)から全メーカー／タイプのテレビを拾い上げるわけにはいきません。

つたない文章でわかりづらいかと思いますが、ご教示お願いいたします。

回答No.2

某HN クロメート(Chromate)（@CoalTar）

>=GETPIVOTDATA("合計 / 数量",F1,"品名","テレビ","メーカー","A","タイプ",1)
セル参照も使えますよ
E24セル
=GETPIVOTDATA($F$1,"テレビ "&B24&" "&C24&" 数量")
下へオートフィル

＞テーブル(2)は実際には莫大な行数なので、テーブル(2)から
＞全メーカー／タイプのテレビを拾い上げるわけにはいきません。

そんな難しくなんだけどなあ。
G13 品名
G14 テレビ
G16:H16 メーカー タイプ

データ フィルタ フィルタオプションの設定
●指定した範囲
リスト範囲 $A$13:$D$21
条件範囲 G13:G14
抽出範囲 G16:H16
[レ]重複するレコードは無視する

添付画像が細かすぎて見えないかも

回答No.1

KURUMITO

ピボットテーブルは表1での新たなデータの追加に対してはその都度操作をすることになりますし、その技術が無いと対応できません。関数を使って処理をすることがお勧めです。
次のようにしてはどうでしょう。
シート1は表1に当たるものでＡ1セルには品名、Ｂ1セルにはメーカー、Ｃ1セルにはタイプ、Ｄ1セルには数量の項目名がありそれぞれのデータが下方に入力されているとします。
シート1では作業列をＥ列およびＦ列に作ります。
Ｅ２セルには次の式を入力して下方にオートフィルドラッグします。

=TRIM(A2)&TRIM(B2)&TRIM(C2)

Ｆ２セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A2="","",IF(COUNTIF(E$2:E2,E2)>1,"",INDEX(Sheet2!F:F,MATCH(E2,Sheet2!E:E,0))))

シート2は表2に対応するものでＡ1セルにはメーカー、Ｂ1セルには品名、Ｃ1セルにはタイプ、Ｄ1セルには価格の項目名がありそれぞれのデータは下方に入力されているとします。
シート2にも作業列を設けます。
Ｅ２せるには次の式を入力して下方にオートフィルドラッグします。

=TRIM(B2)&TRIM(A2)&TRIM(C2)

Ｆ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(OR(B2="",Sheet3!$A$1=""),"",IF(TRIM(B2)=Sheet3!$A$1,MAX(F$1:F1)+1,""))

そこでシート3にはお求めの表を作ることにします。
Ａ1セルにはテレビなどの項目名を入力します。Ｂ1セルには合計受注数量、価格とでも入力します。
Ａ2セルにはメーカー、Ｂ2セルには品名、Ｃ２セルにはタイプ、Ｄ2セルには単価、Ｅ2セルには合計数量、Ｆ2セルには小計とでも項目名をそれぞれ入力します。
Ａ3セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(ROW(A1)>COUNT(Sheet1!$F:$F),"",IF(COLUMN(A1)=1,INDEX(TRIM(Sheet1!$B:$B),MATCH(SMALL(Sheet1!$F:$F,ROW(A1)),Sheet1!$F:$F,0)),IF(COLUMN(A1)=2,INDEX(Sheet1!$C:$C,MATCH(SMALL(Sheet1!$F:$F,ROW(A1)),Sheet1!$F:$F,0)),IF(COLUMN(A1)=3,INDEX(Sheet2!$D:$D,MATCH(TRIM($A$1&$A3&$B3),Sheet2!$E:$E,0)),IF(COLUMN(A1)=4,SUMIF(Sheet1!$E:$E,TRIM($A$1&$A3&$B3),Sheet1!$D:$D),IF(COLUMN(A1)=5,$C3*$D3,""))))))

これでシート1に新たなデータが追加されても自動的に表が更新されます。