子部品の実際の使用数量を求める方法

質問

お世話になります.

Excel 上に,添付資料のような多段階の部品表が用意されています.
各列に,
[階層の深さ][部品番号][使用数量][単位]
が記入されています.
各行に入力されている[使用数量]は,親部品に対する使用数量です.
ここで,各子部品の実際の使用数量を知るためには,親部品の使用数量と各子部品の使用数量とを掛け合わせて求めることになるのですが,それを自動処理する方法が思い付かず,お知恵を拝借いたしたく.
よろしくお願いします.

回答No.2

redfox63 · ベストアンサー

こんな具合でしょう
Sub m()
　　Dim r As Range, n As Integer, m As Integer
　　' 表の左肩がA1セル以外の場合は Range("A1")を変更してください
　　' データ範囲の取得
　　Set r = Range("A1").CurrentRegion
　　' 1番目の個数の転記
　　r.Cells(2, r.Columns.Count).Offset(0, 1) = r.Cells(2, 3)
　　For n = 3 To r.Rows.Count
　　　　For m = n - 1 To 2 Step -1
　　　　　　If r.Cells(m, 1) = r.Cells(n, 1) - 1 Then
　　　　　　　　' 親部品が見つかった
　　　　　　　　' 親部品の個数と自身の個数の乗算
　　 r.Cells(n, 5) = r.Cells(n, 3) * r.Cells(m, 4)
　　　　　　　　Exit For
　　　　　　End If
　　　　Next
　　Next
End Sub

数式でやるなら配列数式になるでしょう
階層1の行には =C2
階層2以上行には
=C3*INDEX($D$1:D2,MAX(ROW($D$2:D2)*($A$2:A2=A3-1)))
と入力して SHIFT + CTRL + ENTER
この数式をコピーして 次の行から最終行までを選択して貼り付け
といった具合でしょう

お礼

redfox63 さん,おはようございます.
どうもありがとうございました.
最初,うまく動いていないようでしたが,
> r.Cells(n, 5) = r.Cells(n, 3) * r.Cells(m, 4)
を,
r.Cells(n, 5) = r.Cells(n, 3) * r.Cells(m, 3)
としたところ,乗算の結果も正しくなりました.

For ... Next 文で, Step 値を負の値にすることで,こんなに便利になるとは驚きました.

さらに VBA ではなく配列数式を使った解決法までご指南いただきまして,どうもありがとうございました.

おかげさまで私もようやく一年を締めくくれそうです.
皆さん,よいお年をお迎え下さい.

回答No.1

n-jun

「実際の使用数」を求める定義が今一つ不明な感じです。

親－子の関係はどのように決まっていて、そこからどの値を基に計算されるのか。。。？

補足

言葉足らずで申し訳ないです.
親-子の関係について:
ある子部品から見て上の行に自分よりも小さい数字の階層番号を持つ部品が見つかったら,それが自分の親,という法則で,階層番号[1]が出てきたらそれより小さい値はないので終了,ということです.

値(実際の使用数)は,自分自身の使用数量と,自分の親,さらにその親...の使用数量を掛け合わせた結果得られた値です.