[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『貸し出し品の在庫管理を関数で行いたいです。出荷日の入力で“貸出中”の表示。返却日の入力で“在庫あり”としたい！』（こてつ）
良く似た質問があったのですが、応用できなかったので、質問させてください。
会社の製品をサンプルとして貸し出しをしており、その管理表を作成したいです。
具体的には以下の通りです。
sheet1（リスト）
A B C D E F G
1 品番 品名 出荷日 着日 返却予定日 実返却日 出荷先
2 123 ○○ 11/20 11/21 11/30 ○○
3
リストには、品番（A）から返却予定日（E）までを入力します。
sheet2（在庫表）
A B C D E
1 品番 品名 在庫 出荷先 返却予定日
2 123 ○○ 在庫なし ○○ 11/30
在庫表は貸し出しできるアイテム一覧を掲載しており、リストの出荷日に日付を入力すると、該当製品の欄の在庫のセル(C2）に“在庫なし”と表示されるよう作成したいです。
併せて、出荷先と返却予定日も転記されるようにしたいです。
貸し出し品なので、当然返却されるのですが、リストの実返却日（F2）に日付が入力されると、表示が“貸し出し中”から“在庫あり”に変更されるようにしたいです。
こんな要望は難しいでしょうか？
＜ 使用 Excel：Excel2010、使用 OS：WindowsVista ＞
リストのほうは同じ品番のものが複数あり、一番下のものが最新のデータということだろうか？ （ねむねむ） 2015/11/20(金) 11:46
Sheet2の
C2に
=IF(ISERROR(MATCH(Sheet2!$A2,Sheet1!$A:$A,0)),"",IF(OFFSET(Sheet1!$A$1,MAX(IF(Sheet1!A$1:A$10000=$A2,ROW(A$1:A$10000),""))-1,5)="",IF(OFFSET(Sheet1!$A$1,MAX(IF(Sheet1!A$1:A$10000=$A2,ROW(A$1:A$10000),""))-1,2)="","","在庫無し"),"在庫あり"))
でCtrl+Shift+Enter
D2に
=IF(ISERROR(MATCH(Sheet2!$A2,Sheet1!$A:$A,0)),"",OFFSET(Sheet1!$A$1,MAX(IF(Sheet1!A$1:A$10000=$A2,ROW(A$1:A$10000),""))-1,6))
でCtrl+Shift+Enter
E2に
=IF(ISERROR(MATCH(Sheet2!$A2,Sheet1!$A:$A,0)),"",OFFSET(Sheet1!$A$1,MAX(IF(Sheet1!A$1:A$10000=$A2,ROW(A$1:A$10000),""))-1,4))
でCtrl+Shift+Enter
C2:E2を下方にフィルコピー
と思ったのですが、同じ品番のものが複数あって上から返却されたら次のデータが入力される
という規則が守られないとおかしくなっちゃいますね。
また、Sheet1のデータが行削除されると数式がダメになっちゃいます。
うまく行かない・・・
（ウッシ） 2015/11/20(金) 12:10
上に書いた条件でよい場合。
C2:=IF(IFERROR(INDEX(Sheet1!F:F,MATCH(1,INDEX(0/(Sheet1!A:A=A2),0),1)),0),"在庫なし","在庫有り") D2:=IF(C2="在庫なし",INDEX(Sheet1!B:B,MATCH(1,INDEX(0/(Sheet1!A:A=A2),0),1)),"") E2:=IF(C2="在庫なし",INDEX(Sheet1!E:E,MATCH(1,INDEX(0/(Sheet1!B:B=B2),0),1)),"") 入力後C2セルからE2セルを下へフィルコピー。
（ねむねむ） 2015/11/20(金) 12:46
そこで、ねむねむさんの書いてくださった上の関数をコピーしてみたのですが、C2に“在庫あり”と表示されるのですが、リストに出荷日を入れても変わらないんです・・・
出荷先、返却予定日の欄も空欄のままです。
上記の関数より変更した点は、Sheet1！からリスト!に変更したのみです。
本当に初心者のため、もう少々おつきあいください。
（こてつ） 2015/11/20(金) 13:25
すまない、C2セルの式を下記にしてくれ。 =IF(IFERROR(INDEX(Sheet1!F:F,MATCH(1,INDEX(0/(Sheet1!A:A=A2),0),1)),1),"在庫有り","在庫なし")
（ねむねむ） 2015/11/20(金) 13:34
たぶん大丈夫だとは思うがE2セルの式を念のため =IF(C2="在庫なし",INDEX(Sheet1!E:E,MATCH(1,INDEX(0/(Sheet1!A:A=A2),0),1)),"") としておいてくれ。 （ねむねむ） 2015/11/20(金) 13:40
重ね重ねすまないがD2セルの式は =IF(C2="在庫なし",INDEX(Sheet1!G:G,MATCH(1,INDEX(0/(Sheet1!A:A=A2),0),1)),"") としてくれ。
>変更してみたのですが、変化なしでした・・・
>リストに出荷日を入れても変わらないんです・・・ もしかして品番は入力されているが出荷日は入力されていない、というデータがあるのだろうか？
こちらでは貸し出しを行う際にリストに入力する(品番がある場合は貸し出しを行っている)という前提で式を作っているのだが。
もし、上記の場合であればたとえば A B C D E F G 1 品番 品名 出荷日 着日 返却予定日 実返却日 出荷先 2 123 ○○ 11/20 11/21 11/30 ○○ 3 123 ○○ のようなこともあるのだろうか？ （ねむねむ） 2015/11/20(金) 14:02
Sheet1(リスト）
リストは、貸し出し依頼があるごとにどんどん下の方へ書き込みしてゆく形式です。
貸出品コード 品名 出荷日 着日 返却予定日 実返却日 出荷先名称
貸出品コードを入力すると品名が出ますが、後は手入力です。
sheet2（在庫表）
在庫は決まったものですので、こちらの表に書き込みはしません。
貸出品コード 品名 在庫有無 出荷先名称 返却予定日
こちらの在庫有りの分に対して、リストを入力します。リストの出荷日欄に日付を入れることで
“在庫あり”から“在庫なし”表示に自動的に変更したいことと、同じくリストに出荷先名称と
返却予定日を入力すると、こちらの在庫表にも表示させたいです。
最終的には貸出品が返却されますと、リストの実返却日に日付を入れますので、その段階で、在庫表の
在庫有無も“在庫あり”に変更したいです。
さて、関係あるかどうかわかりませんが、教えていただいた関数をコピペすると、値の更新というポップアップが出てきます。キャンセルボタンを押していますが・・・・
本当に度々申し訳ありません。
（こてつ） 2015/11/20(金) 14:23
併せて教えていただけたら幸いです。
（こてつ） 2015/11/20(金) 14:31
式を
C2:=IF(IFERROR(INDEX(Sheet1!F:F,MATCH(1,INDEX(0/(Sheet1!A:A&""=A2&""),0),1)),1),"在庫aあり","在庫なし") D2:=IF(C2="在庫なし",INDEX(Sheet1!G:G,MATCH(1,INDEX(0/(Sheet1!A:A&""=A2&""),0),1)),"") E2:=IF(C2="在庫なし",INDEX(Sheet1!E:E,MATCH(1,INDEX(0/(Sheet1!A:A&""=A2&""),0),1)),"") とするとどうだろうか？
これで正しく値が出る場合はリストと在庫表での品番のデータの型が異なっているのが原因だと思うが。 (片方が数値で片方が文字列の数字)
>出荷した際の出荷先名称と返却予定日を空欄にする これは上記の式ですでに「在庫なし」の時だけ出荷先名称と返却予定日を表示するようになっている。 （ねむねむ） 2015/11/20(金) 14:40
(片方が数値で片方が文字列の数字)
これは、リストは日付入力なので、日付にしており、在庫表の方は標準となっていましたが
一応日付に変えてみましたが同じでした・・・（涙）
本当にすみません
（こてつ） 2015/11/20(金) 14:56
では、数式タブの計算方法-計算方法の設定を開いた時に自動にチェックが入っているだろうか？ もし、ほかにチェックが入っていたら自動に入れてみてくれ。
あと、型が違って問題になるのはそれで検索を行っている品番だけ。 他の部分は表示が異なってくるくらいで式そのものには影響ない。 （ねむねむ） 2015/11/20(金) 15:06
まだ見てらっしゃたら教えていただけないでしょうか？
C2:=IF(IFERROR(INDEX(Sheet1!F:F,MATCH(1,INDEX(0/(Sheet1!A:A&""=A2&""),0),1)),1),"在庫aあり","在庫なし")
D2:=IF(C2="在庫なし",INDEX(Sheet1!G:G,MATCH(1,INDEX(0/(Sheet1!A:A&""=A2&""),0),1)),"") E2:=IF(C2="在庫なし",INDEX(Sheet1!E:E,MATCH(1,INDEX(0/(Sheet1!A:A&""=A2&""),0),1)),"")
上記の数式の中で、Sheet1!をリスト!に変更して貼り付けています。
リストと在庫表の中で共通なのは貸出品コードなのですが、そこがうまく結びついていないのかな？
と思うのですが・・・数式が読み取れないので、なんとも言えないです。
もうすこしおつきあいお願いできますか？
（こてつ） 2015/11/25(水) 11:02
>そこがうまく結びついていないのかな？ これの確認。
C2セルに =SUMPRODUCT((リスト!A:A=A2)*1) と入力して下へフィルコピーしてみてくれ。 もし、両方の貸出品コードが同じであればリストにある貸出品コードの個数が表示されるはずだ。
もし、これが「0」になるようであれば前後にスペースがあったりなどでリストと在庫表で貸出品コードが異なっていると思われる。 （ねむねむ） 2015/11/25(水) 11:14
それでは =SUMPRODUCT((リスト!A:A&""=A2&"")*1) ではどうだろうか？
また、 =SUMPRODUCT((TRIM(リスト!A:A)=TRIM(A2))*1) ではどうかも試してみてくれ。 （ねむねむ） 2015/11/25(水) 11:52
=SUMPRODUCT((CLEAN(リスト!A:A)=A2&"")*1) ではどうだろうか？
で、念のため =SUMPRODUCT((TRIM(CLEAN(リスト!A:A))=TRIM(A2))*1) でも。 （ねむねむ） 2015/11/25(水) 15:37
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.