会社で急遽棚卸表をつくることになり、マクロや関数などほとんど触ったことがなかったので困っています。
どなたか教えてください。
まずシート1には製品リストが表記されています
シート1
製品番号 製品名 在庫数
○○○ ○○ ○○
××× ×× ××
△△△ △△ △△
シート3以降にはその製品を作るための材料が展開されています
シート3以降のシート名については製品番号になっています
シート3~(製品番号)
材料番号 材料名 使用数
○○○ ○○ ○○
××× ×× ××
△△△ △△ △△
上記の状態からシート1の在庫数に数を入力すると
シート3以降の構成表よりその製品に使われる材料の使用数を導き出し
その材料が実際に何個あるかをシート2へとまとめたい。
また製品毎に同じ材料を使用している場合については
合算して表記したい。
自分で試してみましたが
締切が押し迫り、どなたかのお力添えをいただきたいです。
プログラムの理解度はVBはないですがPerl、PHPについては触ったことがある程度です。
マクロでもなんでも結構ですのでご教授願います。
A 回答 (4件)
- 最新から表示
- 回答順に表示
No.4
- 回答日時:
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
- 回答日時:
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
- 回答日時:
初心者が取り組まれるにしては内容が複雑ですが次のようにしてはどうでしょう。
一度こちらの提案する方法で試験してもてください。
シート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
- 回答日時:
>シート1の在庫数に数を入力すると
>シート3以降の構成表よりその製品に使われる材料の使用数を導き出し
>その材料が実際に何個あるかをシート2へとまとめたい
無理なんじゃ?
製品在庫数ってことは既に材料は使用済みと言う事になりますが
使用済みの材料数だけでは現状の材料在庫は求めることはできません
※製品在庫0の時点(もしくは全開棚卸時点)での材料在庫数が必要
上記在庫数が判ったとして、製品は在庫するだけで出荷はしないのでしょうか?
出荷済み製品があった場合、その製品作成に使用された材料も材料在庫から差し引く必要があります
それらの条件が全て揃わない限り、計算上の在庫数と棚卸在庫数が一致することはないでしょう
まずは生産・在庫管理の基礎の勉強が必要と思われます
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) 条件に一致した場合の処理 2 2022/10/18 06:30
- Excel(エクセル) excelで検索した商品の画像(ネットワーク上の)を表示させたい。 3 2023/06/28 00:32
- Excel(エクセル) Excelについて 4 2023/03/12 17:43
- Visual Basic(VBA) VBAで、1つのエクセルで、2つのシートからもう1つのシートに条件のある転記コードを教えてください。 1 2023/03/16 18:07
- その他(パソコン・スマホ・電化製品) エクセル初心者です。 仕事でエクセルを使っていて、普段は素人でもできる簡単な関数を使ったことがある程 1 2022/05/25 11:17
- Excel(エクセル) Excel関数 情報引用する方法 4 2022/07/31 20:59
- その他(コンピューター・テクノロジー) デジタル化推進でうちの会社では、ペーパーレス化が進んでいます。 そこで、質問です。 会社では、検査課 3 2023/06/11 00:17
- リフォーム・リノベーション 玄関扉(品番記載致します)の材質を知りたい 4 2023/03/14 00:08
- 財務・会計・経理 1、製造指図書…の製造のため、材料A50000円を出庫し、外出先の工場に加工を依頼した。 なお当工場 4 2022/06/18 10:46
- Excel(エクセル) Excelで在庫表(クエリ、ピボット) 2 2022/04/11 17:11
関連するカテゴリからQ&Aを探す
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Excelのマクロについて教えてく...
-
Excel 範囲指定スクショについ...
-
プログラミング
-
ユーザーフォームに別シートか...
-
VBAコードについて教えてくださ...
-
Excelのマクロについて教えてく...
-
エクセルVBAコードで教えて下さ...
-
vba アクティブシートの名前変...
-
Outlookの「受信日時」「件名」...
-
VBA 別ブックからコピペしたい...
-
【ExcelVBA】インデックスが有...
-
VBAに詳しい方教えてください。
-
ExcelのVBAコードについて教え...
-
Outlookの「受信日時」「送信者...
-
エクセルのマクロについて教え...
-
エクセルのマクロについて教え...
-
VBA listBoxから
-
Excel VBA 定義されたプロージ...
-
エクセルVBAの配列について
-
配列のペースト出力結果の書式...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
VBAで大量のファイルをシート名...
-
VBA レジストリの値の読み方に...
-
Excelのマクロについて教えてく...
-
ユーザーフォームに別シートか...
-
VBAの計算で@が出てしまう件
-
エクセルVBAについて
-
Vba 実数および実数タイプの変...
-
【ExcelVBA】値を変更しながら...
-
VBA一覧取得 再投稿
-
VBA指定行削除
-
エクセルVBAについて
-
VBA ユーザーフォーム ボタンク...
-
VBA 何かしら文字が入っていたら
-
エクセルについて
-
2つのマクロでチェックボックス...
-
【マクロ】1つのマクロの中に...
-
ExcelのVBAコードについて教え...
-
VB.net(VB)で、フォームにExcel...
-
Vba SelStart、SelLen教えてく...
-
Excel-VBAのmsgBox()の不思議
おすすめ情報