Excelで構成表の作成

会社で急遽棚卸表をつくることになり、マクロや関数などほとんど触ったことがなかったので困っています。
どなたか教えてください。

まずシート１には製品リストが表記されています

シート１
製品番号　製品名　在庫数
○○○　　　○○　　　　○○
×××　　　××　　　　××
△△△　　　△△　　　　△△

シート３以降にはその製品を作るための材料が展開されています
シート３以降のシート名については製品番号になっています

シート３～（製品番号）
材料番号　材料名　　使用数
○○○　　　○○　　　　○○
×××　　　××　　　　××
△△△　　　△△　　　　△△

上記の状態からシート１の在庫数に数を入力すると
シート３以降の構成表よりその製品に使われる材料の使用数を導き出し
その材料が実際に何個あるかをシート２へとまとめたい。
また製品毎に同じ材料を使用している場合については
合算して表記したい。

自分で試してみましたが
締切が押し迫り、どなたかのお力添えをいただきたいです。
プログラムの理解度はＶＢはないですがPerl、PHPについては触ったことがある程度です。
マクロでもなんでも結構ですのでご教授願います。

No.4 回答者： kagakusuki 回答日時： 2013/01/29 03:13

　Sheet1に製品番号と在庫数を入力し、各製品番号のシートに材料番号等のデータを入力しておけば、使用されている全ての材料番号が昇順に並べられている一覧表が自動的に作成され（材料番号を網羅したリストを元データとして作成しておく必要が無い）、その横に材料名や総使用数が自動的に表示される方法です。

　今仮に、Sheet1において、製品番号が入力されている列がA列であり、在庫数が入力されているセルがC列であるものとします。
　同様に、各製品番号のシートにおいて、材料番号が入力されている列がA列であり、使用数が入力されているセルがC列であるものとします。
　又、「作業用」というシート名のシートを新たに作成しておき、そのA列～H列を作業列として使用するものとします。

　まず、

作業用

というシート名のシートを新たに作成して下さい。
　次に、作業用シートのB1セルに次の関数を入力して下さい。

=IF(COUNTIF(Sheet1!$A$1:INDEX(Sheet1!$A:$A,ROW()),INDEX(Sheet1!$A:$A,ROW()))=1,INDEX(Sheet1!$A:$A,ROW()),"")

　次に、作業用シートのC1セルに次の関数を入力して下さい。

=IF(ISERROR(1/COUNT(INDIRECT("'"&$B1&"'!C"&MATCH("使用数",INDIRECT("'"&$B1&"'!C:C"),0)&":C"&ROWS(C:C)))),"",MATCH(9E+307,INDIRECT("'"&$B1&"'!C:C"))-MATCH("使用数",INDIRECT("'"&$B1&"'!C:C"),0))

　次に、作業用シートのA1セルに次の関数を入力して下さい。

=IF(ISNUMBER($C1),SUM($C$1:INDEX($C:$C,ROW()-1)),"")

　次に、作業用シートのA1～C1の範囲をコピーして、同じ列の2行目以下に、（Sheet1のリストの行数を上回るのに十分な行数となるまで）貼り付けて下さい。
　次に、作業用シートのH2セルに次の関数を入力して下さい。

=IF(ROWS($2:2)>SUM($C:$C),"",IF(ISNUMBER(INDIRECT("'"&VLOOKUP(ROWS($2:2)-1,$A:$B,2)&"'!C"&ROWS($2:2)-LOOKUP(ROWS($2:2)-1,$A:$A)+MATCH("使用数",INDIRECT("'"&VLOOKUP(ROWS($2:2)-1,$A:$B,2)&"'!C:C"),0))),INDIRECT("'"&VLOOKUP(ROWS($2:2)-1,$A:$B,2)&"'!C"&ROWS($2:2)-LOOKUP(ROWS($2:2)-1,$A:$A)+MATCH("使用数",INDIRECT("'"&VLOOKUP(ROWS($2:2)-1,$A:$B,2)&"'!C:C"),0))*SUMIF(Sheet1!$A:$A,VLOOKUP(ROWS($2:2)-1,$A:$B,2),Sheet1!$C:$C),""))

　次に、作業用シートのF2セルに次の関数を入力して下さい。

=IF(ISNUMBER($H2),INDIRECT("'"&VLOOKUP(ROWS($2:2)-1,$A:$B,2)&"'!R"&ROWS($2:2)-LOOKUP(ROWS($2:2)-1,$A:$A)+MATCH("使用数",INDIRECT("'"&VLOOKUP(ROWS($2:2)-1,$A:$B,2)&"'!C:C"),0)&"C"&COLUMN(INDIRECT("C3"))+COLUMN()-COLUMN($H$1),FALSE),"")

　次に、作業用シートのF2セルをコピーして、作業用シートのG2セルに貼り付けて下さい。
　次に、作業用シートのE2セルに次の関数を入力して下さい。

=IF(OR($F2="",COUNTIF($F$1:$F2,$F2)>1),"",COUNTIF($F:$F,">"&$F2))

　次に、作業用シートのE2～H2の範囲をコピーして、同じ列の3行目以下に、（全ての製品番号のシートのリストの行数を合計した数を上回るのに十分な行数となるまで）貼り付けて下さい。

　次に、Sheet2のA2セルに次の関数を入力して下さい。

=IF(ROWS($2:2)>COUNT(作業用!$E:$E),"",IF(VLOOKUP(LARGE(作業用!$E:$E,ROWS($2:2)),作業用!$E:$G,COLUMNS(作業用!$E:F),FALSE)="","",VLOOKUP(LARGE(作業用!$E:$E,ROWS($2:2)),作業用!$E:$G,COLUMNS(作業用!$E:F),FALSE)))

　次に、Sheet2のA2セルをコピーして、Sheet2のB2セルに貼り付けて下さい。
　次に、Sheet2のC2セルに次の関数を入力して下さい。

=IF(OR(INDEX($A:$A,ROW())="",COUNT(Sheet1!$C:$C)=0),"",SUMIF(作業用!$F:$F,$A2,作業用!$H:$H))

　次に、Sheet2のA2～C1の範囲をコピーして、同じ列の2行目以下に、（全種類の材料番号を表示するのに十分な行数となるまで）貼り付けて下さい。

　以上です。

No.3 回答者： mt2008 回答日時： 2013/01/28 15:59

Sheet1でA2セル以降に記載されている製品番号＝シートからA:C列をSheet2にコピーし、使用数をSheet1の在庫数倍するマクロを作ってみました。

材料名などがかぶる場合は、マクロの結果をさらにピボッドテーブルなどで処理してください。
サンプルですので、該当シートが無い場合等のエラー処理は一切含めておりません。悪しからず。

Sub Sample()
　　Dim nRow, nMax, sShtName, i
　　nRow = 2 'sheet2の2行目から貼り付け
　　
　　For i = 2 To Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
　　　　sShtName = Sheets(1).Cells(i, 1)
　　　　
　　　　'対象シートの行数を確認
　　　　nMax = Sheets(sShtName).Range("C1").End(xlDown).Row
　　　　
　　　　'Sheet2に貼り付け
　　　　Sheets(sShtName).Range("A2:C" & nMax).Copy Sheets(2).Range("A" & nRow)
　　　　
　　　　'貼り付けた部品数を在庫数倍する
　　　　Sheets(1).Cells(i, 3).Copy
　　　　Sheets(2).Range("A" & nRow & ":C" & nRow + nMax - 2).PasteSpecial Operation:=xlMultiply
　　　　
　　　　'Sheet2の貼り付け行変更
　　　　nRow = nRow + nMax - 1
　　　　
　　Next i
End Sub

No.2 回答者： KURUMITO 回答日時： 2013/01/28 15:21

初心者が取り組まれるにしては内容が複雑ですが次のようにしてはどうでしょう。

一度こちらの提案する方法で試験してもてください。
シート1ではA1セルに製品番号、B1セルに製品名、C1セルに在庫数とそれぞれ項目名が有り下方にそれぞれのデータが入力されるとします。
そこでD列を作業列としてD2セルには次の式を入力して下方にドラッグコピーします。

=IF(AND(A2<>"",C2<>""),IF(COUNTIF(Sheet2!G$2:XX$2,A2),A2,"シート2の2行目に記載がありません"),"")

シート2での操作が済んでいない状況ではシート2の2行目に記載が有りませんなどの表示がされるでしょう。
シート2はお求めの表になりますが作業列を作って対応します。
以下にはシート2での作業を示します。
G1セルにはSheet3、H1セルにはSheet4、I1セルにはSheet5・・・と右側の列に製品番号と同じ数のシート名を入力します。
G2セルにはシート3に含まれる製品番号を入力します。同様に各シートに対応した製品番号を右横の列に入力します。
E4セルには材料番号、F4セルには材料名、G4セルには使用数、H4セルにも使用数、…右横のセルに1行目のセルにシート名が有るところまで使用数の文字列を入力します。
E5セルから下方には使われるすべての材料番号を入力します。F5セルから下方には材料名を入力します。
その上でG5セルには次の式を入力して右横方向にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(OR(G$1="",$E5=""),"",IF(COUNTIF(Sheet1!$D:$D,G$2)=0,"",IFERROR(INDEX(INDIRECT(G$1&"!A:C"),MATCH($E5,INDIRECT(G$1&"!A:A"),0),3)*INDEX(Sheet1!$C:$C,MATCH(G$2,Sheet1!$D:$D,0)),"")))

それぞれのシートに記載された製品番号における材料の使用数にシート1で入力した在庫数を掛けた形で表示されます。
E4セルからF列のデータの最後までを範囲としてコピーし、A4セルに貼り付けます。その後にC4セルには使用数の項目名を入力します。C5セルには次の式を入力して下方にドラッグコピーします。

=IF(SUM(G5:XX5)=0,"",SUM(G5:XX5))

お求めの表はA,B,C列に表示されることになります。表が使用数が空白の行も含まれていますので別のシートに空白の行を除いた形の表を作成する場合には作業列としてD5セルに次の式を入力して下方にドラッグコピーします。

=IF(C5="","",MAX(D$4:D4)+1)

目障りでしたらD列を選択してからフォントの色を白に設定してもよいでしょう。
次にシート3以降のシートについてはA列に材料番号、B列に材料名、C列に使用数をそれぞれのシート名と製品番号に対応して入力します。

以上の操作でお望みの表がシート2に表示されますが全く別のシートに空白セルのない状態でまとめて表示させるのでしたら
例えばA1セルからC1セルまでにシート2のA4セルからC4セルの項目名をコピーして貼り付けます。
その後にA2セルには次の式を入力してC2セルまで横にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(ROW(A1)>MAX(Sheet2!$D$5:$D$1000),"",INDEX(Sheet2!$A$5:$C$1000,MATCH(ROW(A1),Sheet2!$D$5;$D$1000,0),COLUMN(A1)))

No.1 回答者： web2525 回答日時： 2013/01/28 14:01

>シート１の在庫数に数を入力すると

>シート３以降の構成表よりその製品に使われる材料の使用数を導き出し
>その材料が実際に何個あるかをシート２へとまとめたい

無理なんじゃ？
製品在庫数ってことは既に材料は使用済みと言う事になりますが
使用済みの材料数だけでは現状の材料在庫は求めることはできません

※製品在庫0の時点（もしくは全開棚卸時点）での材料在庫数が必要

上記在庫数が判ったとして、製品は在庫するだけで出荷はしないのでしょうか？
出荷済み製品があった場合、その製品作成に使用された材料も材料在庫から差し引く必要があります

それらの条件が全て揃わない限り、計算上の在庫数と棚卸在庫数が一致することはないでしょう
まずは生産・在庫管理の基礎の勉強が必要と思われます