【Excel】要素数が不定な場合のGETPIVOTDATAの使い方を教えてください。
Excel 2003で添付画像のようなサンプルデータを作成しています。
以下の4つのテーブルがあります。
・テーブル(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)から全メーカー/タイプのテレビを拾い上げるわけにはいきません。
つたない文章でわかりづらいかと思いますが、ご教示お願いいたします。
A 回答 (2件)
- 最新から表示
- 回答順に表示
No.2
- 回答日時:
>=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
- 回答日時:
ピボットテーブルは表1での新たなデータの追加に対してはその都度操作をすることになりますし、その技術が無いと対応できません。
関数を使って処理をすることがお勧めです。次のようにしてはどうでしょう。
シート1は表1に当たるものでA1セルには品名、B1セルにはメーカー、C1セルにはタイプ、D1セルには数量の項目名がありそれぞれのデータが下方に入力されているとします。
シート1では作業列をE列およびF列に作ります。
E2セルには次の式を入力して下方にオートフィルドラッグします。
=TRIM(A2)&TRIM(B2)&TRIM(C2)
F2セルには次の式を入力して下方にオートフィルドラッグします。
=IF(A2="","",IF(COUNTIF(E$2:E2,E2)>1,"",INDEX(Sheet2!F:F,MATCH(E2,Sheet2!E:E,0))))
シート2は表2に対応するものでA1セルにはメーカー、B1セルには品名、C1セルにはタイプ、D1セルには価格の項目名がありそれぞれのデータは下方に入力されているとします。
シート2にも作業列を設けます。
E2せるには次の式を入力して下方にオートフィルドラッグします。
=TRIM(B2)&TRIM(A2)&TRIM(C2)
F2セルには次の式を入力して下方にオートフィルドラッグします。
=IF(OR(B2="",Sheet3!$A$1=""),"",IF(TRIM(B2)=Sheet3!$A$1,MAX(F$1:F1)+1,""))
そこでシート3にはお求めの表を作ることにします。
A1セルにはテレビなどの項目名を入力します。B1セルには合計受注数量、価格とでも入力します。
A2セルにはメーカー、B2セルには品名、C2セルにはタイプ、D2セルには単価、E2セルには合計数量、F2セルには小計とでも項目名をそれぞれ入力します。
A3セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方にもオートフィルドラッグします。
=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に新たなデータが追加されても自動的に表が更新されます。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・「みんな教えて! 選手権!!」開催のお知らせ
- ・漫画をレンタルでお得に読める!
- ・【選手権お題その2】この漫画の2コマ目を考えてください
- ・2024年に成し遂げたこと
- ・3分あったら何をしますか?
- ・何歳が一番楽しかった?
- ・治せない「クセ」を教えてください
- ・【大喜利】看板の文字を埋めてください
- ・【大喜利】【投稿~12/17】 ありそうだけど絶対に無いことわざ
- ・【選手権お題その1】これってもしかして自分だけかもしれないな…と思うあるあるを教えてください
- ・【穴埋めお題】恐竜の新説
- ・我がまちの「給食」自慢を聞かせてっ!
- ・冬の健康法を教えて!
- ・一番好きな「クリスマスソング」は?
- ・集合写真、どこに映る?
- ・自分の通っていた小学校のあるある
- ・フォントについて教えてください!
- ・これが怖いの自分だけ?というものありますか?
- ・スマホに会話を聞かれているな!?と思ったことありますか?
- ・それもChatGPT!?と驚いた使用方法を教えてください
- ・見学に行くとしたら【天国】と【地獄】どっち?
- ・これまでで一番「情けなかったとき」はいつですか?
- ・この人頭いいなと思ったエピソード
- ・あなたの「必」の書き順を教えてください
- ・10代と話して驚いたこと
- ・14歳の自分に衝撃の事実を告げてください
- ・人生最悪の忘れ物
- ・あなたの習慣について教えてください!!
- ・都道府県穴埋めゲーム
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
同一セルに日時があるものを日...
-
OFFSET関数を使用した印刷範囲...
-
1年分のデータから特定の月分...
-
EXCELでCSVファイル保存すると...
-
Excelで指定した条件と一致する...
-
エクセルVBAで、複数セルのデー...
-
COUNTIFの反対の関数はあるので...
-
エクセル数式・等間隔ごとの範...
-
EXCELで年月日の表記から日付部...
-
excelで平均差を出したい
-
Excelで連続データを行飛ばしで...
-
エクセルでセルを一括で右詰に...
-
エクセル
-
トラックスケールの計量器から...
-
1つのセルにまとまっている情報...
-
色なしセルの合計値の出し方に...
-
【EXCEL】vlookup関数で横の行...
-
Excel 空白セルの掛け算で答え...
-
エクセルで数列を使う方法はあ...
-
エクセルVBA オートフィルの最...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
同一セルに日時があるものを日...
-
EXCELでCSVファイル保存すると...
-
1年分のデータから特定の月分...
-
Excelで指定した条件と一致する...
-
COUNTIFの反対の関数はあるので...
-
エクセルVBAで、複数セルのデー...
-
ドロップダウンリストで空白の...
-
エクセルでセルを一括で右詰に...
-
エクセルで数字、文字列混在の...
-
Excelで連続データを行飛ばしで...
-
1つのセルにまとまっている情報...
-
OFFSET関数を使用した印刷範囲...
-
EXCELで年月日の表記から日付部...
-
excelで平均差を出したい
-
【VBA】指定フォルダに格納中の...
-
エクセルで特定の行を除いて計...
-
エクセル数式・等間隔ごとの範...
-
複数のシートの日付データを、...
-
Excelで、表紙に、住所を自動で...
-
エクセルVBA オートフィルの最...
おすすめ情報