ＥＸＣＥＬで複数の表からの集計

ある製品αは複数のＵＮＩＴ（Ａ～Ｃ）で構成され、各ＵＮＩＴは複数の部品（あ～お）で構成されるとします。
添付画像のような製品、ＵＮＩＴ構成の場合に製品αを構成する部品数はどのように集計すればよいのでしょうか。

質問者からの補足コメント

• 

  実際の表はもっと大きいので数えるのは大変です。
  計算式で埋める方法を知りたいです。

  No.1の回答に寄せられた補足コメントです。 補足日時：2017/07/15 08:26

• 補足します。
  （１）実際は数千～数万の部品から構成されている自社製品の整理をしたいのですが、一つの製品に複数同じｕｎｉｔが使われいていると、部品リストとしては複数回でてきてしまうからです。
  （同じ部位であれば数が２とか３になりますが、部位が違うと今回の例のようになる。）
  これを集計するという手はありますが、一手間かかるので処理しなくて良い回答があればと思いこのような質問にしました。
  （２）同じです。
  （３）はい。

  No.4の回答に寄せられた補足コメントです。 補足日時：2017/07/15 19:19

No.9 ベストアンサー 回答者： tom04 回答日時： 2017/07/16 19:26

No.8です。

＞表に空欄があっても問題ないのでしょうか？

もちろん空白があっても問題ないはずです。
前回の画像の配置で、行方向・列方向にはいくらデータがあっても対応できます。
ただ投稿後思ったのですが、
D列「PART」のデータが各「UNIT」にすべて存在すれば問題ないのですが、
D列データが「UNIT」の中に存在しない！というコトはないですよね？
そうであれば前回のコードで大丈夫なのですが、万一「UNIT」にない場合のエラー処理のために
↓のコードに変更してみてください。

Sub Sample2() '//この行から//
Dim i As Long, k As Long, lastRow As Long
Dim c As Range, r As Range
lastRow = Cells(Rows.Count, "D").End(xlUp).Row
If lastRow > 2 Then
Range(Cells(3, "E"), Cells(lastRow, "E")).ClearContents
End If
For i = 3 To Cells(Rows.Count, "A").End(xlUp).Row
Set c = Rows(2).Find(what:=Cells(i, "A"), LookIn:=xlValues, lookat:=xlWhole)
For k = 3 To Cells(Rows.Count, "D").End(xlUp).Row
Set r = Columns(c.Column).Find(what:=Cells(k, "D"), LookIn:=xlValues, lookat:=xlWhole)
If Not r Is Nothing Then '//←追加★//
With Cells(k, "E")
.Value = .Value + r.Offset(, 1) * Cells(i, "B")
End With
End If '//←追加★//
Next k
Next i
End Sub '//この行まで//

※　「★」の行を追加しただけです。
「UNIT」にD列データがない場合の処理を追加しました。m(_ _)m

この回答へのお礼

回答ありがとうございます。
構成上はpartsがunitの中にかならずあるべきなのですが、現状システムの吐き出したデータを人間が加工してunitの表を作るしかないのでヒューマンエラーへの対応は必要と思います。
作っていただいたコードはこれから試すのでどう動くかまだ理解できていませんが、上記のようにunitの中にpartsが無い=表の作り間違い
なので検出されたらエラーで止まってしまうコードでも問題ないと思います。
まずは作っていただいたコードを試してみます。
毎日の仕事ではないので少し時間が空いてしまうかもしれません。

お礼日時：2017/07/18 09:00

No.10 回答者： ExcelDevTool 回答日時： 2017/07/17 05:05

No.8 回答者： tom04 回答日時： 2017/07/15 23:21

こんばんは！

横からお邪魔します。
＞実際は数千～数万の部品から構成されている・・・
というコトなので、↓の画像のような配置にし、行方向に関してはいくらデータがあっても対応できるようにしてみました。
「UNIT」数がたくさんある場合、数式で処理するのは大変ですので、
VBAでの一例です。

今回は↓の画像のような感じで、右へ「UNIT」が増えても対応できるような配置にしています。

画面左下の操作したいシート見出し上で右クリック　→　コードの表示　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　Excel画面に戻り（VBE画面を閉じて）マクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() '//この行から//
Dim i As Long, k As Long, lastRow As Long
Dim c As Range, r As Range
lastRow = Cells(Rows.Count, "D").End(xlUp).Row
If lastRow > 2 Then
Range(Cells(3, "E"), Cells(lastRow, "E")).ClearContents
End If
For i = 3 To Cells(Rows.Count, "A").End(xlUp).Row
Set c = Rows(2).Find(what:=Cells(i, "A"), LookIn:=xlValues, lookat:=xlWhole)
For k = 3 To Cells(Rows.Count, "D").End(xlUp).Row
Set r = Columns(c.Column).Find(what:=Cells(k, "D"), LookIn:=xlValues, lookat:=xlWhole)
With Cells(k, "E")
.Value = .Value + r.Offset(, 1) * Cells(i, "B")
End With
Next k
Next i
End Sub '//この行まで//

※　関数でないので、データ変更があるたびに
マクロを実行する必要があります。

※　あくまでアップした画像の配置でのコードです。
1行、1列でも違う場合はとんでもない結果になります。

お望みの方法でない場合は無視してください。m(_ _)m

この回答へのお礼

回答遅くなりもうしわけありません。
これは表に空欄があっても問題ないのでしょうか？
というのも、部品数は数千から数万、ｕｎｉｔも数十
から数百あるのですが、ここまではいかないだろうと
いう数の目途はつくので、たとえば部品数が１０万、
ｕｎｉｔ数が１０００という余裕のある表を作ってそ
れに合わせたマクロを組んでおけばデータを表に張り付
けて（後ろのほうは空白セル）マクロを走らせるだけな
らそれ手間はかからないかと思ったもので。

お礼日時：2017/07/16 16:24

No.7 回答者： banzaiA 回答日時： 2017/07/15 22:35

＃６です。

図はＡ列の空白列がありますが、数式の方は空白列を削除した数式ですので念のために申し添えます。

No.6 回答者： banzaiA 回答日時： 2017/07/15 22:32

スマートでは無いですが、無理矢理

B20:==SUMIF($A$11:$A$15,A20,$B$11:$B$15)*SUMIF($A$3:$A$7,"Ａ",$B$3:$B$7)+SUMIF($D$11:$D$15,A20,$E$11:$E$15)*SUMIF($A$3:$A$7,"Ｂ",$B$3:$B$7)+SUMIF($G$11:$G$15,A20,$H$11:$H$15)*SUMIF($A$3:$A$7,"Ｃ",$B$3:$B$7)
これで＃５のママチャリさんと同じになりますが。
モットスマートな方法はあると思います。

No.5 回答者： ママチャリ 回答日時： 2017/07/15 11:45

計算式（関数）だけで作るのであれば、こんな感じでしょうか。

部品表は画像のような構成にすると集計が簡単です。

【D3セル】=SUMIFS($L:$L,$J:$J,$A3,$K:$K,D$2)*$B3
【B10セル】=SUM(D:D)

この回答へのお礼

ありがとうございます。
やはり一度ｕｎｉｔ内での部品数の集計を挟まなければならないのでしょうか。
実際は部品の種類が数千あり、ｕｎｉｔ内での部品集計表と、最終結果としての製品内での部品集計表を作ると
大きな表が二つになってしまい扱いが悪いのでやらないで済めばと思ったのですが・・・。

お礼日時：2017/07/15 19:23

No.4 回答者： banzaiA 回答日時： 2017/07/15 10:02

補足説明してください。

（１）＃３の方の質問と同じですが、製品αの表のＵＮＩＴにＡとＢが2回でてくるのはなぜか。ＡＢＣＤＥのつもりではないのか。
（２）ＵＮＩＴ＿ＡからＣの3つの表にでてくる「あ」や「い」はみな同じものと考えていいのか。
（３）例としての作りたい表の「あ」の数は「７」「い」の数は「５」「う」の数は「１３」と単純に足していいのか。

No.3 回答者： mike_g 回答日時： 2017/07/15 09:42

素朴な疑問だけど…

最上段の製品αの表が

UNIT_数
A＿＿_6
B＿＿_6
C＿＿_3

でなく、A、B の数がそれぞれ 1と5、2と4 に分かれている理由は？

No.2 回答者： chonami 回答日時： 2017/07/15 08:32

いや、だからその計算方法がわからないんです。

だから例示の完成表を埋めてもらえたらわかるかと。
最終的には必要なパーツの数を合計するんじゃないんですか？

No.1 回答者： chonami 回答日時： 2017/07/15 08:13

完成表を埋めてもらえますか？

足せばいいの？

この回答へのお礼

すみません、この場合足すっていうのはどういうことを意図しているのでしょうか。
一つ一つ数えるのではなく、計算で式を埋められたらと考えています。

お礼日時：2017/07/15 08:28