エクセル2003で部材ごとの必要数量がわかる表を作りたいのですがどのようにしたらよいでしょうか？

質問

エクセル2003で製品名およびサブタイプ（プルダウンリストで選択）と個数を入力すると、
部材ごとの必要数量がわかる表を作りたいのですが、どのようにしたらよいでしょうか？
製品名を選択しない行は個数の欄だけ0と表示する または全て空白になり、製品リストは別
シートに作るようにしたいと考えています。

--------------------------------------------------------------------------------------------
[製品名]　[サブタイプ]　[個数]　　[部材A]　　[部材B]　　[部材C]　　[部材D]
　イ▼　　　　　　　　　　50　　　　500　　　　　0　　1,500　　　2,000
　ロ▼　　　　　　　　　　1　　　　　50　　　　　40　　　30　　　　　20
　ハ▼　　　　　　　　　100　　　9,000　　　8,000　　　　0　　　6,000
　　　　　　　　　　　　　0
　ニ▼　　　　a▼　　　300　　　7,500　　　10,500　　13,500　　16,500　
--------------------------------------------------------------------------------------------
　　　　　　 [部材ごとの合計] 　　17,050　　18,540　　15,030　　24,520

製品リスト
---------------------------------------------------------------------
[製品名]　[サブタイプ]　[部材A]　[部材B]　[部材C]　[部材D]
　イ　　　　　　　　　　　10　　　 　　　　　30　　　40
　ロ　　　　　　　　　　　50　　　40　　　　30　　　20
　ハ　　　　　　　　　　　90　　　80　　　　　 　　　60
　ニ　　　　　　a　　　　 25　　　 35　　　 45　　　 55
　ニ　　　　　　b　　　　 60　　　 70　　　 80　　　 90
---------------------------------------------------------------------

回答No.4

KURUMITO · ベストアンサー

シート1では製品名から部材Ｄまでの項目名がＡ1セルからＧ1セルまでに入力されているとします。
作業列としてＪ2セルには次の式を入力して下方にオートフィルドラッグします。
=IF(A2="","",IF(B2="",A2,A2&"/"&B2))
シート2ではＡ1セルからＦ1セルまでに項目名があり下方にデータが入力されているとします。
作業列としてＪ2セルには次の式を入力して下方にオートフィルドラッグします。
=IF(A2="","",IF(B2="",A2,A2&"/"&B2))
なお、サブタイプがある商品名については、その商品名が最初に記載されている行のＨ列から右横の列にa,b,cなどと入力します。例の場合ですと、商品名がニの行でＫ5セルにa, Ｌセルにｂと入力します。
以上でシート２での作業は終わりですがシート1では次のような操作を行います。
Ａ2セルから例えばＡ１０0までを選んで「データ」から「入力規則」で入力値の種類に「リスト」を選択し、元の値には例えば次の式を入力して「ＯＫ」します。
=INDIRECT("Sheet2!A1:A100")
これで、Ａ列では商品名がリスト表示されるようになり、選択できるようになります。
問題はＢ列です。商品名にサブタイプが無い場合にはリストにサブタイプの名前が表示されずにサブタイプがある場合にのみ名前が表示されることが必要でしょう。そのために次のようにします。
Ｂ2セルからＢ100までを選択したのちに入力規則のリストでは次の式を入力します。
=INDEX(INDIRECT("Sheet2!A:O"),MATCH(A2,INDIRECT("Sheet2!A:A"),0),11):INDEX(INDIRECT("Sheet2!A:O"),MATCH(A2,INDIRECT("Sheet2!A:A"),0),15)
次に部材の価格表示ですがＤ2セルには次の式を入力し、Ｇ2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。
=IF($A2="","",$C2*INDEX(Sheet2!$A:$G,MATCH($J2,Sheet2!$J:$J,0),COLUMN(C1)))
以上で完成です。

お礼

リストを作るのに手間取ってしまい返事が遅くなりました。　少しうまくいかない所もありましたがなんとか完成できました。　ありがとうございました。

回答No.3

tom04

No.2です！
たびたびごめんなさい。

投稿した後、No.1のASIMOV様の数式の方がすっきりしていると思いましたので
便乗させてもらいます。

部材Aだけ数式ですが、
先ほどの表そのまま利用して、
Sheet1の
D2セルを　　=IF(OR(A2="",C2="",ISBLANK(VLOOKUP(A2&B2,Sheet2!$C$2:$G$6,2,0))),"",VLOOKUP(A2&B2,Sheet2!$C$2:$G$6,2,0)*C2)
としてみてください。
他の部材に関してもVLOOKUP関数の列の部分だけを変更すれば、OKかと思います。

どうも何度も失礼しました。m(__)m

お礼

リストを作るのに手間取ってしまい返事が遅くなりました。　参考になりました。　ありがとうございました。

回答No.2

tom04

こんばんは！
参考になるかどうか分かりませんが・・・
当方使用のExcel2003での回答です。

↓の画像のように表を作ってみました。

表を参照するに当たってSheet2のC2セルに
=A2&B2　としてオートフィルで下へコピーしています。

まず、リスト表示させる場合
Sheet2のA2～A6を範囲指定して「名前ボックス」に　製品名　としてOK
Sheet2のB4～B6を範囲指定して同様に　サブタイプ　としてOK

次にSheet1のA2～A10（どこまででも構いません）を範囲指定して
メニュー　→　データ　→　入力規則　のリストを選択
「元の値」の欄に　=製品名　としてOK
これでA列のリスト表示は出来るはずです。
同じようにB2～B10を範囲指定　→　入力規則の「元の値」の欄に
=サブタイプ　と入力してOK
これでA・B列はリスト表示できるようになりました。

次に各セルに数式を入れていきます。
Sheet1の
D2セル　=IF(OR(A2="",C2="",ISBLANK(INDEX(Sheet2!$D$2:$D$6,MATCH(A2&B2,Sheet2!$C$2:$C$6,0)))),"",INDEX(Sheet2!$D$2:$D$6,MATCH(A2&B2,Sheet2!$C$2:$C$6,0))*Sheet1!C2)
E2セル　=IF(OR(A2="",C2="",ISBLANK(INDEX(Sheet2!$E$2:$E$6,MATCH(A2&B2,Sheet2!$C$2:$C$6,0)))),"",INDEX(Sheet2!$E$2:$E$6,MATCH(A2&B2,Sheet2!$C$2:$C$6,0))*Sheet1!C2)
F2セル　=IF(OR(A2="",C2="",ISBLANK(INDEX(Sheet2!$F$2:$F$6,MATCH(A2&B2,Sheet2!$C$2:$C$6,0)))),"",INDEX(Sheet2!$F$2:$F$6,MATCH(A2&B2,Sheet2!$C$2:$C$6,0))*Sheet1!C2)
G2セル　=IF(OR(A2="",C2="",ISBLANK(INDEX(Sheet2!$G$2:$G$6,MATCH(A2&B2,Sheet2!$C$2:$C$6,0)))),"",INDEX(Sheet2!$G$2:$G$6,MATCH(A2&B2,Sheet2!$C$2:$C$6,0))*Sheet1!C2)
として、D2～G2セルを範囲指定した後、オートフィルで下へコピーしています。

以上で希望に近い形にならないでしょうか？

どうも長々と失礼しました。

参考になれば幸いですが、
的外れなら読み流してくださいね。m(__)m

回答No.1

ASIMOV

[部材A]がＤ列とします
D2 =IF($C2=0,"",VLOOKUP($A2&$B2,Sheet2!$C$2:$G$6,2)*$C2)
E2 =IF($C2=0,"",VLOOKUP($A2&$B2,Sheet2!$C$2:$G$6,3)*$C2)
F2 =IF($C2=0,"",VLOOKUP($A2&$B2,Sheet2!$C$2:$G$6,4)*$C2)
G2 =IF($C2=0,"",VLOOKUP($A2&$B2,Sheet2!$C$2:$G$6,5)*$C2)
（違いは後ろの方の２～５だけ）

以下、下にコピー

なお、VLOOKUP を使うため「製品リスト」の方にちょっと細工をします

まず、[サブタイプ]と[部材A]の間に１列挿入し、そこに
　=A2&B2
として、下にコピーします
また、[製品名]と[サブタイプ]をキーにして並べ替えしておく必要があります

イ
ニ　a
ニ　b
ハ
ロ

という感じです

お礼

リストを作るのに手間取ってしまい返事が遅くなりました。　参考になりました。　ありがとうございました。