[[20220928005412]]　『参照テーブル(Pivot)が変動する際の集計関数』（あい）　ページの最後に飛ぶ

『参照テーブル(Pivot)が変動する際の集計関数』（あい）

Pivotで集計したテーブルが２つあり、それを関数で集計して、名前＆コード毎の金額を算出したいです。関数を教えていただけないでしょうか。
集計用テーブルはPivotのため、名前やコードの並びが変動してしまいます。
宜しくお願い致します。

↓金額を関数で集計したいです。A10000などはコードです。
名前　　　A10000　　　B20000　　　C30000　　　D40000
AAA　　　　100　　　200　　　　　300　　　　400
BBB　　　　1000　　　2000　　　　3000　　　　4000
CCC　　　　10000　　　20000　　　　30000　　　40000
DDD　　　　100000　　　200000　　　300000　　　400000

↓集計用テーブル１（Pivotで集計）
名前　　　D40000 B20000 C30000 A10000
DDD　　　200000 100000 150000 50000
BBB　　　2000 1000 1500 500
AAA　　　200 100 150 50

↓集計用テーブル２（Pivotで集計）
名前 B20000 D40000 A10000 C30000
CCC 20000 40000 10000 30000
BBB 1000 2000 500 1500
AAA 100 200 50 150
DDD 100000 200000 50000 150000

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

 (1)INDEX関数とMATCH関数を組み合わせて、それぞれの表の対応する値を取得して、
 加算すればよいと思います。

 (2)元になるデータをひとつにしたうえで、
 ピボットテーブルを再作成するという方法もあるでしょう。

 (3)このほか、
 こうしたマトリックス形式の表が複数あり、
 これを一つのマトリックス形式の表にまとめる時には、
 「ピボットテーブル/ピボットグラフ ウイザード」を使うと便利かもしれません。
 「複数のセル範囲からピボットテーブルを作成する」
 https://www.crie.co.jp/chokotech/detail/205/
 この方式ですと、行や列のアイテムを手で統合しなくても
 自動で作成してくれるという利点があります。
 　
（γ） 2022/09/28(水) 05:58

γさん、ありがとうございます。

集計用テーブル2はピボットではなく、マニュアルで名前、コード、金額を入力することも考えておりまｓ。名前（縦軸）、コード（横軸）別の金額を集計する際の関数が組み立てできず、
ご教示頂けると幸いです。
宜しくお願い致します。

（あい） 2022/09/28(水) 06:42

 > マニュアルで
 >名前（縦軸）、コード（横軸）別の金額を集計する
 SUM関数でそれぞれ縦、横を合計する
    |[A]       |[B]   |[C]   |[D]   |[E]   |[F]     
 [1]|名前      |A10000|B20000|C30000|D40000|名前合計
 [2]|AAA       |   100|   200|   300|   400|    1000
 [3]|BBB       |  1000|  2000|  3000|  4000|   10000
 [4]|CCC       | 10000| 20000| 30000| 40000|  100000
 [5]|DDD       |100000|200000|300000|400000| 1000000
 [6]|コード合計|111100|222200|333300|444400| 
 違うならスルーしてください。
（?） 2022/09/28(水) 09:29

２つのテーブル（縦と横の列が可変）から、おそらくindex関数とmatch関数を使って集計可能と思いますが、うまく集計できないです。
お力おかし頂けると幸いでございます。
よろしくお願いいたします。

（あい） 2022/09/28(水) 10:00

 集計用テーブル１がSheet1で集計用テーブル２がSheet2で行の最大が1000行、列の最大がZ列までとして集計結果の表のB2セルに
 =SUMPRODUCT((Sheet1!$A$2:$A$1000=$A2)*(Sheet1!$B$1:$Z$1=B$1),Sheet1!$B$2:$Z$1000)+
  SUMPRODUCT((Sheet2!$A$2:$A$1000=$A2)*(Sheet2!$B$1:$Z$1=B$1),Sheet2!$B$2:$Z$1000)
 と入力して右及び下にフィルコピーではだどうだろうか？
（ねむねむ） 2022/09/28(水) 10:22

ねむねむさん、ありがとうございます。

参照テーブルがピボットではないケースでは、下記数式でうまくいきそうですが、
ピボットテーブルを参照しようとすると、「#value!」と表示されてしまいます。
文字列か数値かを統一する必要がありそうなのですが、回避策はありますでしょうか？
よろしくお願いいたします。

=SUMPRODUCT((Sheet1!$A$2:$A$1000=$A2)*(Sheet1!$B$1:$Z$1=B$1),Sheet1!$B$2:$Z$1000)+

  SUMPRODUCT((Sheet2!$A$2:$A$1000=$A2)*(Sheet2!$B$1:$Z$1=B$1),Sheet2!$B$2:$Z$1000)
（あい） 2022/09/28(水) 11:53

 実際のピボットの場合。行ラベルが4行目になるのでそれに合わせているだろうか？
 =SUMPRODUCT((Sheet1!$A$5:$A$1000=$A2)*(Sheet1!$B$4:$Z$4=B$1),Sheet1!$B$5:$Z$1000)+
  SUMPRODUCT((Sheet2!$A$5:$A$1000=$A2)*(Sheet2!$B$4:$Z$4=B$1),Sheet2!$B$5:$Z$1000) 
（ねむねむ） 2022/09/28(水) 13:32

 既に解決済みのようですが、私の念頭にあったのはこんなことでした。
 Sheet1の表からデータを引っ張るところは例えばこんな風にできます。
 IFERROR(INDEX(Sheet1!$A$1:$E$4,MATCH($A2,Sheet1!$A$1:$A$4,0),MATCH(B$1,Sheet1!$A$1:$E$1,0)),0)
 キーが無いこともありうるので、それに備えてIFERRORでラップしておきます。
 Sheet2からの部分も同じようにできますから、あとは、二つを加算するだけです。
 # セル位置は適宜調整してください、考え方だけ示しています。
 　
（γ） 2022/09/28(水) 15:26

  ＞IFERROR(INDEX(Sheet1!$A$1:$E$4,MATCH($A2,Sheet1!$A$1:$A$4,0),MATCH(B$1,Sheet1!$A$1:$E$1,0)),0) 
 この式は VLOOKUP でもできます。

 =IFERROR(VLOOKUP($A2,Sheet1!$A$2:$E$4,MATCH(B$1,Sheet1!$A$1:$E$1,0),FALSE),0)

 365なら XLOOKUP でもできるでしょうし
 ピボットテーブルから値を取り出すのなら GETPIVOTDATA 関数でもできます。

 どこに何があって、どこに何を表示するのか、具体的なことが何もわからないので
 具体的な回答はできませんけど･･･

 参考まで
（笑） 2022/09/28(水) 16:28

コメント返信：

[ 一覧(最新更新順) ]

[[20220928005412]] 『参照テーブル(Pivot)が変動する際の集計関数』（あい） ページの最後に飛ぶ

[[20220928005412]]　『参照テーブル(Pivot)が変動する際の集計関数』（あい）　ページの最後に飛ぶ