- ベストアンサー
プルダウン 在庫数の出し方
- Excel2000をご利用の場合、シート1のD列に特定の商品とサイズの在庫数を求める式を入力します。
- 同じ商品とサイズを入力すると、前回計算した在庫数が表示されます。
- 新しい商品とサイズを入力する場合は、計算式を変更する必要があります。
- みんなの回答 (6)
- 専門家の回答
質問者が選んだベストアンサー
「QNo.7678516 エクセル プルダウン 別シート計算について」の御質問をされた方ですね。 要するに、Sheet1のA列のセルに商品名を、列のセルにBサイズを、それぞれ入力規則のドロップダウンリストを使用して入力し、Sheet1のC列のセルに在庫から出入りした数量を(キーボード入力によって)入力すると、各商品の各サイズごとの残りの在庫数が自動的に表示される様にしたい、という事であると考えれば宜しいのですね。 それでしたら、この御質問の直接の回答ではないのですが、私の場合でしたら以下の様に致します。 まず、Sheet1の1行目に A1 B1 C1 D1 E1 商品名 サイズ 入出庫の種別 数量 在庫数 という具合に各項目名を入力します。 Sheet1のC列の「入出庫の種別」欄には、「貸出」だけではなく、「返却」や「入荷」(新たに商品を仕入れて、在庫に追加した場合)、「廃棄」(劣化して使用に耐えなくなった商品等を処分した場合)も入力出来る様にして、「貸出」以外の場合に関しても、在庫管理計算を自動化させます。 一方、Sheet2の1行目には A1 B1 C1 D1 商品名 サイズ 前回からの引継在庫数 現在の在庫数 という具合に各項目名を入力します。 Sheet2のC列の「入出庫の種別」欄には、Sheet1で貸出等の記録を付け始める前から、元々存在していた在庫数を入力します。 又、Sheet2のA~B列は、例えば次のように入力しておきます。 A列 B列 1行目 商品名 サイズ 2行目 商品A S 3行目 M 4行目 L 5行目 LL 6行目 商品B S 7行目 M 8行目 L それと、Sheet3のA~E列をを作業列として使用します。 具体的には、まず、Sheet3のA2セルに次の関数を入力して下さい。 =IF(OR(ROW()<=ROW(Sheet2!$A$1),INDEX(Sheet2!$A:$A,ROW())=""),"",COUNT(A$1:A1)+1) 次に、Sheet3のB2セルに次の関数を入力して下さい。 =IF(ROWS($B$2:$B2)>COUNT($A:$A),"",INDEX(Sheet2!$A:$A,MATCH(ROWS($B$2:$B2),$A:$A))) 次に、Sheet3のC2セルに次の関数を入力して下さい。 =IF(INDEX(Sheet2!$B:$B,ROW())="","",INDEX(Sheet2!$A:$A,MATCH(99999,$A$1:A2)+ROW($A$1)-1)&"■"&INDEX(Sheet2!$B:$B,ROW())) 次に、Sheet3のD2セルに次の関数を入力して下さい。 =IF(ISNUMBER(INDEX(Sheet1!$D:$D,ROW())),INDEX(Sheet1!$A:$A,ROW())&"■"&INDEX(Sheet1!$B:$B,ROW()),"") 次に、Sheet3のE2セルに次の関数を入力して下さい。 =IF(ISNUMBER(MATCH(INDEX(Sheet1!$C:$C,ROW()),{"貸出","返却","入荷","廃棄"},0)+INDEX(Sheet1!$D:$D,ROW())),INDEX(Sheet1!$D:$D,ROW())*IF(OR(INDEX(Sheet1!$C:$C,ROW())="返却",INDEX(Sheet1!$C:$C,ROW())="入荷"),1,-1),"") 次に、Sheet3のA2~E2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。 次に、Sheet1のE2セルに次の関数を入力して下さい。 =IF(AND(INDEX($A:$A,ROW())<>"",INDEX($B:$B,ROW())<>"",ISNUMBER(MATCH(INDEX($C:$C,ROW()),{"貸出","返却","入荷","廃棄"},0)),ISNUMBER(INDEX($D:$D,ROW()))),IF(COUNTIF(Sheet3!$C:$C,INDEX($A:$A,ROW())&"■"&INDEX($B:$B,ROW())),INDEX(Sheet2!$C:$C,MATCH(INDEX($A:$A,ROW())&"■"&INDEX($B:$B,ROW()),Sheet3!$C:$C,0))+SUMIF(Sheet3!$D$1:INDEX(Sheet3!$D:$D,ROW()),INDEX($A:$A,ROW())&"■"&INDEX($B:$B,ROW()),Sheet3!$E$1:INDEX(Sheet3!$E:$E,ROW())),"該当無し"),"") 次に、Sheet1のE2セルをコピーして、Sheet1のE3以下に貼り付けて下さい。 次に、Sheet2のD2セルに次の関数を入力して下さい。 =IF(ISNUMBER(INDEX($C:$C,ROW())),INDEX($C:$C,ROW())+SUMIF(Sheet3!$D:$D,INDEX($A:$A,MATCH(99999,Sheet3!$A$1:INDEX(Sheet3!$A:$A,ROW()))+ROW(Sheet3!$A$1)-1)&"■"&INDEX($B:$B,ROW()),Sheet3!$E:$E),"") 次に、Sheet2のD2セルをコピーして、Sheet2のD3以下に貼り付けて下さい。 次に、Sheet1のA2セルに対する入力規則で、[設定]タブの「入力値の種類」欄を[リスト]とした上で、「元の値」欄に次の数式を入力して下さい。 =INDIRECT("Sheet3!B2:B"&ROW(INDIRECT("Sheet3!B2"))+COUNT(INDIRECT("Sheet3!A:A"))-1) 次に、Sheet1のB2セルに対する入力規則で、[設定]タブの「入力値の種類」欄を[リスト]とした上で、「元の値」欄に次の数式を入力して下さい。 =INDIRECT("Sheet2!B"&MATCH(INDEX($A:$A,ROW()),INDIRECT("Sheet2!A:A"),0)&":B"&MATCH(INDEX($A:$A,ROW()),INDIRECT("Sheet2!A:A"),0)+COUNTIF(INDIRECT("Sheet3!C:C"),INDEX($A:$A,ROW())&"■*")-1) 次に、Sheet1のC2セルに対する入力規則で、[設定]タブの「入力値の種類」欄を[リスト]とした上で、「元の値」欄に次の様に入力して下さい。 貸出,返却,入荷,廃棄 次に、Sheet1のA2~C2の範囲をコピーした後、Sheet1の表中でA~C列の3行目以下にある全てのセルをまとめて範囲選択してから、形式を選択して貼付けで、入力規則のみを貼り付けて下さい。 次に、Sheet2のA~C列の2行目以下に、各項目ごとのデータを入力して下さい。 その際、Sheet2のA列に入力されているデータには、重複が無い様にして下さい。 又、Sheet2のB列に入力されているデータには、途中に空欄の行が挟まっている事が無い様にして下さい。 尚、今回挙げさせて頂きました方法では、普通の関数を使う方法とは異なり、Sheet1のA~D列やSheet2のA~C列の内容を変更する際に、セルに対して、セルの切取りや削除、挿入等を行って、セルの位置関係が上下方向にずれてしまった場合においても、正しい結果が表示される様になっています。(左右方向のずれに対しては対応してはおりません) これで、Sheet1のA2以下のドロップダウンリストには、Sheet2のA列に存在する商品名のみが選択肢として現れ、Sheet1のB2以下のドロップダウンリストには、左隣のセル(Sheet1のA列のセル)で指定している商品において存在しているサイズのみが選択肢として現れ、余計な選択肢(左隣のセルで指定している商品には存在していないサイズ)は現れない様になります。 後は、ドロップダウンリストを使用してSheet1のA~C列の2行目以下に、在庫から出入りした商品の商品名やサイズ、「貸出/返却/入荷/廃棄」の区分を入力し、Sheet1のD2以下に出入りした数量をキーボード入力しますと、貸出と廃棄の場合には、数量に入力した数が在庫から差し引かれ、返却と入荷の場合には、数量に入力した数が在庫に加えられ、「各行ごとの商品の出入りがあった際の在庫」が同じ行のE列のセルに表示されます。 それと同時に、Sheet2のD列には、各商品の各サイズごとの最新の在庫数のデータが表示されます。 もし、文章による説明では解り難い場合には、下記の添付画像を御参照下さい。 尚、もしも、質問者様が利用されているサイトでは画像の表示サービスが行われていない場合には、下記のURLのページを御参照下さい。 【参考URL】 【OKWave】 QNo.7678516 プルダウン 在庫数の出し方 http://okwave.jp/qa/q7678516.html
その他の回答 (5)
- kagakusuki
- ベストアンサー率51% (2610/5101)
>sheet1 b2の入力規則 元の値 > =INDIRECT("Sheet2!B"&MATCH(INDEX($A:$A,ROW()),INDIRECT("Sheet2!A:A"),0)&":B"&MATCH(INDEX($A:$A,ROW()),INDIRECT("Sheet2!A:A"),0)+COUNTIF(INDIRECT("Sheet3!C:C"),INDEX($A:$A,ROW())&"■*")-1) >が、違うと表示されてしまいます。 違うと表示されてしまうとはどういう事なのでしょうか? もし、数式に誤りがある場合には、「入力した数式は正しくありません。」という表示ならば現れますし、数式で計算を行った際にエラーとなって結果を求める事が出来なかった場合には、「元の値はエラーと判断されます。続けますか?」という表示ならば現れますが、「違う」という表示は現れない筈です。 状況が不明なままでは原因を特定する事は出来ませんので、問題を解決するためには、なるべく正確な情報を御教え願います。 もし、A1セルが空欄か、或いはSheet2のA列に存在していない値が入力されている状態で、ANo.5で述べましたSheet1のB2セルに対する入力規則を設定しようとされた際に、「Microsoft Excel」のダイアログボックスが現れて「元の値はエラーと判断されます。続けますか?」と尋ねてきたのでしたら、それは予定通りですので、気になさらずに、そのダイアログボックスの[はい(Y)]ボタンをクリックして下さい。 Sheet1のB列に設定する入力規則は、Sheet1のA列のセルに入力されている商品名に該当するデータが、Sheet2の何行目から何行目にかけて存在しているのかによって、ドロップダウンリストに表示する範囲を変更する様になっているのですから、Sheet2に存在するデータがA列に入力され無い限りは、エラーとなってドロップダウンリストが表示されないのは当然の事ですので、Sheet2に存在するデータがA列に入力されさえすれば、エラーは解消されます。
お礼
ほんとうにありがとうございました
- KURUMITO
- ベストアンサー率42% (1835/4283)
次のようなマクロにすればいでしょう。 シート見出しでSheet1を右クリックして「コードの表示」をクリックして表示される画面に次のマクロを入力します。 Private Sub Worksheet_Change(ByVal Target As Range) Dim i, Zaiko, Zaiko1 As Long If Target.Column <> 3 Then Exit Sub i = 0 Do i = i + 1 If Worksheets("Sheet2").Cells(i, 1).Value = Cells(Target.Row, 1).Value And Worksheets("Sheet2").Cells(i, 2).Value = Cells(Target.Row, 2).Value Then Zaiko = Worksheets("Sheet2").Cells(i, 3).Value Zaiko1 = Zaiko - Target.Value Cells(Target.Row, 4).Value = Zaiko1 Worksheets("Sheet2").Cells(i, 3).Value = Zaiko1 Exit Sub End If Loop Until Worksheets("Sheet2").Cells(i, 1) = "" End Sub これでシート1のC列に貸し出し数を入力することでD列のもデータが表示されますし、シート2のC列の在庫数も自動的に変更されます。
お礼
お礼が遅くなりました。ありがとうございました。
- KURUMITO
- ベストアンサー率42% (1835/4283)
ごめんなさい。回答1は無視してください。マクロでないと対応できません。
- keithin
- ベストアンサー率66% (5278/7941)
そういうお話ですと,計算しなきゃならないのは,商品名サイズについて =(シート2の当初の在庫)-(シート1の既出の貸し出し数)-(シート1の今回の貸出数) という事になります。 実際にはシート1の今回までの商品名サイズの貸出数累計を引き算します。 シート1のD2: =IF(COUNTA(A2:C2)=3,SUMPRODUCT((Sheet2!$A$1:$A$1000=A2)*(Sheet2!$B$1:$B$1000=B2), Sheet2!$C$1:$C$1000)-SUMPRODUCT(($A$1:A2=A2)*($B$1:B2=B2), $C$1:C2),"")
お礼
お礼が遅くなりました。ありがとうございました。
- KURUMITO
- ベストアンサー率42% (1835/4283)
D2セルには次の式を入力して下方にドラッグコピーします。 =IF(A2="","",INDEX(Sheet2!C:C,SUMPRODUCT((Sheet2!A$1:A$1000=A2)*(Sheet2!B$1:B$1000=B2)*ROW(A$1:A$1000)))-C2)
お礼
お礼が遅くなりました。ありがとうございました。
お礼
お礼が遅くなりました。ありがとうございました。
補足
お忙しい中ありがとうございます。 sheet1 b2の入力規則 元の値 =INDIRECT("Sheet2!B"&MATCH(INDEX($A:$A,ROW()),INDIRECT("Sheet2!A:A"),0)&":B"&MATCH(INDEX($A:$A,ROW()),INDIRECT("Sheet2!A:A"),0)+COUNTIF(INDIRECT("Sheet3!C:C"),INDEX($A:$A,ROW())&"■*")-1) が、違うと表示されてしまいます。 どうすればよいでしょうか