[[20150305110919]]　『機械貸出表の作成』（奮闘中）　ページの最後に飛ぶ

『機械貸出表の作成』（奮闘中）

機械の貸出表を作りたいのですが、
現在二つのシートに分けて

シート１が

　　　　　A　　　　　B　　　　　C　　　　　D
1　　　　　　　　　機械１(セル結合)
2　　　貸出先　　　貸出日　　返却日　　担当
3　　　倉庫1　　　2015/2/27　2015/3/4　　○○

シート2に貸出品一覧として

　　　A　　　　　　B
1　　製品名　　　状況
2　　機械1

となってます。
貸出機械は何台もありますが、とりあえず一台だけ例で出しました。
シート2のB1の状況にシート1の貸出日から返却日がパソコンの日付と比例するように
過ぎている場合は”返却日超過”、貸出中の場合は”貸出中”、貸出日の日付がまだ先の場合”予約”と出るようにしたいのですが、数式はどのようにしたらよろしいでしょうか？
掲示板に出ているものでやってみてもできなかったので・・・
申し訳ありませんがよろしくお願いいたします。

＜使用 Excel：Excel2010、使用 OS：unknown ＞

	A	B	C	D	E	F	G
1	機械１					製品名	状況
2	貸出先	貸出日	返却日	担当		機械1	予約
3	倉庫1	3/7	3/10	○○

 同シートで例。

 G2 =IF(MAX(C:C)<TODAY(),"超過",IF(MAX(B:B)>TODAY(),"予約","貸出中"))
　
（GobGob） 2015/03/05(木) 11:31

できました！
ありがとうございます。
申し訳ありませんが、もう一件あったのですが、、
予約が入っていない場合、”在庫”とも出るようにしたいのですが、
どういった数式になるのでしょうか？
エクセル初心者なので本当に申し訳ありません。
（奮闘中） 2015/03/05(木) 11:45

例えば機械2があるとしてそれは機械1が書かれている次の行ですか？

 A B C D
1　　　　　　　　　機械１
2	貸出先	貸出日	返却日	担当
3	倉庫1	3/7	3/10	○○
4       倉庫2   3/11    3/13    △△　　　
5                  機械2
6       倉庫1   3/7     3/11    ○○

もしこのようなレイアウトの場合は、数式だけではできないです。
もしこの場合でしたらE列などに機械1などの製品名を表示できないでしょうか？
（デイト） 2015/03/05(木) 11:49

機械2は下の行ではなく
右の行に追加という感じになってます。

　　　　　A　　　　　B　　　　　C　　　　　D 　　　　　E　　　　F　　　　G　　　　H
1　　　　　　　　　機械１(セル結合) 　　　　　　　　　　　　　　機械2
2　　　貸出先　　　貸出日　　返却日　　担当　　　貸出先　　　貸出日　　返却日　　担当
3　　　倉庫1　　　2015/2/27　2015/3/4　　○○　　　　倉庫2　　　2015/3/1　2015/3/5　　××

といった感じです。
（奮闘中） 2015/03/05(木) 11:54

何度もすいません。
上記の表で無理ならば何かいい方法はありませんか？
よろしくお願いいたします。
（奮闘中） 2015/03/05(木) 13:40

 回答じゃないですが、こういう表ならなんとかできそう？
     [A]    [B]     [C]     [D]     [E]
[ 1]機器名  貸出先  貸出日  返却日  担当
[ 2]機械１  倉庫１  3/7     3/10    ○○
[ 3]機械１  倉庫２  3/11    3/13    △△
[ 4]機械２  倉庫１  3/7     3/10    ××
（稲葉） 2015/03/05(木) 14:08

できれば機械ごとに表を分けたいのですが無理でしょうか？
無理を言って申し訳ありません。
（奮闘中） 2015/03/05(木) 14:09

何度もすいません。
　　　　　A　　　　　B　　　　　C　　　D 　　　E　　　　F　　　　G　　　　H 　　　　　I　　　　J

 1　　　　　　　　　機械１(セル結合) 　　　　　　　　　　　　　　機械2(セル結合) 
 2　　　貸出先　　　貸出日　 返却日 　担当 　結果　　貸出先　　貸出日　　返却日　 　担当 　　結果
3　　　倉庫1　　　2015/2/27　2015/3/4　　○○　済　　　倉庫2　　2015/3/1　2015/3/5　×× 　　未

こちらでは無理でしょうか？
結果という行を追加いたしました。
貸出日から返却日がパソコンの日付と比例するように
過ぎている尚且つ結果が”未”場合は”超過”日にちが過ぎており結果が”済”の場合は”在庫有”、貸出中の場合は”貸出中”、貸出日の日付がまだ先の場合”予約”と出るようには不可能でしょうか？
（奮闘中） 2015/03/05(木) 14:16

あくまでも一例として出します。
A列に製品名を追加し、G列に製品名　H列に製品名の最後の貸出日を参照
I列に製品名の最後の返却日を参照　J列で状況を確認
A B C D E G H I J
1 製品名貸出先貸出日返却日担当
2 機械1 倉庫1 3月7日 3月10日 ○○ 製品名貸出日返却日状況
3 機械1 倉庫2 3月11日 3月13日 △△ 機械1 2015/3/2
4 機械1 倉庫1 3月2日 3月2日 ○○ 機械2 2015/3/3
5 機械2 3月3日 3月3日

H3 =IF(COUNTIF($A:$A,$G3),(INDEX(C:C,MAX(IF($A$2:$A$999=$G3,ROW($A$2:$A$999))))))
I3 =IF(COUNTIF($A:$A,$G3),(INDEX(D:D,MAX(IF($A$2:$A$999=$G3,ROW($A$2:$A$999))))))

Ctrl Shift Enter配列数式で確定

J3 =IF(H3=0,"在庫",IF(H3<TODAY(),"超過",IF(I3>TODAY(),"予約","貸出中")))

すいませんこれが私の限界です。どうやっても一つにまとまりませんでした。申し訳ありません。

（デイト） 2015/03/05(木) 14:17

 その表でやりたいなら、GobGobさんの式を必要な数だけ用意すればいいんじゃないの？
 まさかやってもいないで聞いてないよね？
（稲葉） 2015/03/05(木) 14:26

追加してやったのですが、数式が多すぎるとエラーでました。
（奮闘中） 2015/03/05(木) 14:32

 上級者からもっとすばらしい関数が提示されると思いますが･･･
 Sheet2のB2セル =IF(MAX(OFFSET(INDIRECT("Sheet1!"&ADDRESS(1,MATCH(A2,Sheet1!1:1,0))),2,2))<TODAY(),"超過",IF(MAX(OFFSET(INDIRECT("Sheet1!"&ADDRESS(1,MATCH(A2,Sheet1!1:1,0))),2,1))>TODAY(),"予約","貸出中"))
（se_9） 2015/03/05(木) 15:03

	A	B	C	D	E	F	G	H	I	J	K	L
1	機械1					機械2					製品名	予約
2	貸出先	貸出日	返却日	担当		貸出先	貸出日	返却日	担当		機械2	貸出中
3	倉庫1	2/27	3/4	○○		倉庫2	3/1	3/5	××			
4												
5												
6												
 またまためんどくさいので同じシートで。

 L2 =IF(COUNT(INDEX(A:I,0,MATCH(K2,A$1:I$1,0)+1)),IF(MAX(INDEX(A:I,0,MATCH(K2,A$1:I$1,0)+2))<TODAY(),"超過",IF(MAX(INDEX(A:I,0,MATCH(K2,A$1:I$1,0)+1))>TODAY(),"予約","貸出中")),"在庫")
（GobGob） 2015/03/05(木) 16:08

お返事遅くなってすいません。
できました！！
ありがとうございました。

（奮闘中） 2015/03/06(金) 11:35

すいません。
上記の数式で、できたと思ったのですが、Cの返却日を3/4にしても超過とならず在庫となるのですが、
何か間違ってますか？
上記のとおりに引用させていただきました。

（奮闘中） 2015/03/06(金) 11:44

 貸出日が設定されてない（予約なし）と「在庫」としてま。
　
（GobGob） 2015/03/06(金) 11:47

もしGに2/27と入力してHに3/1と入力したら超過と出ずに
在庫として出ます
そして下2/27の下に3/13と入力し3/1の下に3/15と入力しても在庫としてでます。
どこか変えないといけませんか？

（奮闘中） 2015/03/06(金) 12:03

 ＞もしGに2/27と入力してHに3/1と入力したら超過と出ずに 
 ＞在庫として出ます 
 ＞そして下2/27の下に3/13と入力し3/1の下に3/15と入力しても在庫としてでます。 
 ＞どこか変えないといけませんか？

 K2 を 「機械2」が全角、半角とか違ってんじゃないの？
（機械2 なら 貸し出し中とでるけど）
（GobGob） 2015/03/06(金) 12:09

現在下記のような形で入力しております。

　　A　　　　B 　　C 　　D 　E　F　　　G　　　　H 　　I 　 J　　　K　　
1　機械1 　　機械2 　　　　　製品名予約
2　貸出先　貸出日　返却日　担当　　貸出先　貸出日　返却日　担当　機械2　在庫
3　倉庫　　12/27　　3/4 　 ○○ 倉庫2 3/1 3/4 ××
4　　　　　　　　　　　　　　　　　倉庫2　 3/13　　3/15　　△△

Kがどうしても在庫と出てしまします。
本当にお手間をおかけして申し訳ありません。
（奮闘中） 2015/03/06(金) 13:31

何度もすいません
数式の+1、+2は何を表しているかおしえていただけませんか？
（奮闘中） 2015/03/06(金) 16:33

できました！！
GobGob様本当にありがとうございます。
お忙しい中ありがとうございました！！
（奮闘中） 2015/03/06(金) 17:08

すいません
もう一度質問させてください。
返却日に日付を入力していない場合
教えていただいた数式だと、超過とでてしまします。
返却日が空白の場合、貸出中と出るようにするのは可能でしょうか？
（奮闘中） 2015/03/10(火) 16:44

 正直な話、旨く行くハズないと思っていたのですけど、
 突然「できました！！」になったので驚いてしまった。

 例えばですけど、3/1に貸出して、3/10に返却予定の場合
 　2　貸出先　貸出日　返却日　担当
 　3　倉庫　　3/1　　　3/10 　○○

 と書きますよね？

 それで、3/11になった時、
 予定通り返却されても、返却されなくても、その管理台帳は全く同じなのでしょう？
 どこで「在庫/超過」の区別をするんですか？

 上の方で結果欄とか作って「未／済」でその区別をしたらどうか、とか言う話が
 出ていたと思うのですけど、どうなっちゃったんですか？

（半平太） 2015/03/11(水) 07:15

現在は完了の分はセルから消して
違うシートに履歴として残しております。

（奮闘中） 2015/03/11(水) 15:05

 ＞現在は完了の分はセルから消して 違うシートに履歴として残しております。

 納得です。　m(__)m

 念のため、今使っている数式を再確認したいので、ここに貼り付けて頂けませんか？

（半平太） 2015/03/11(水) 16:03

現在はGobGob様に教えていただいた数式

	A	B	C	D	E	F	G	H	I	J	K	L
1	機械1					機械2					製品名	予約
2	貸出先	貸出日	返却日	担当		貸出先	貸出日	返却日	担当		機械2	貸出中
3	倉庫1	2/27	3/4	○○		倉庫2	3/1	3/5	××			
4												
5												
6												
 またまためんどくさいので同じシートで。

 L2 =IF(COUNT(INDEX(A:I,0,MATCH(K2,A$1:I$1,0)+1)),IF(MAX(INDEX(A:I,0,MATCH(K2,A$1:I$1,0)+2))<TODAY(),"超過",IF(MAX(INDEX(A:I,0,MATCH(K2,A$1:I$1,0)+1))>TODAY(),"予約","貸出中")),"在庫")

こちらを使わせていただいております。

（奮闘中） 2015/03/12(木) 09:09

 GobGobさんにやってもらった方がいいかもしれませんが、一案です。

 以下に変更

 L2セル =IF(K2="","",IF(COUNT(INDEX(A:I,0,MATCH(K2,A$1:I$1,0)+1)),IF(MOD(MAX(INDEX(A:I,0,MATCH(K2,A$1:I$1,0)+2))-1,400000)+1<TODAY(),"超過",IF(MAX(INDEX(A:I,0,MATCH(K2,A$1:I$1,0)+1))>TODAY(),"予約","貸出中")),"在庫"))

（半平太） 2015/03/12(木) 10:40

ありがとうございます。
一度こちらの数式でやってみます。
（奮闘中） 2015/03/12(木) 13:41

半平太様

ありがとうございます。
何とかできました。
もしよければでいいのですが、
こちらの数式の400000や+1は何を表しているか教えていただけませんか?

（奮闘中） 2015/03/12(木) 13:51

 ＞こちらの数式の400000や+1は何を表しているか

 MAX関数は空白しかないときは、0を返してきます。
 「0」は日付なら「1900/1/0」意味であり、大昔の日ですから、当然「超過」になります。

 この0をなんとか未来の日付に変えなきゃなりません。
 (そう考えたと言うことです。別の方法がないと云う訳ではないです）

 =MOD(0-1,400000)+1　
 これで、400000になってくれます。
 400000は日付の2995/2/27と同じなので、十分に未来の日です。

 何故、わざわざ1マイナスしてから、1プラスするのか・・・ですね？
 0をマイナス1にしないと上の式は400000になってくれません。
 すると、今度は正常な日付が前日になってしまい具合が悪い。
 なので、それらを元の日付に戻してやるため（+1）です。

（半平太） 2015/03/12(木) 16:01

お忙しい中ありがとうございます。
なんとなくですが、納得できました。
本当にありがとうございました！
（奮闘中） 2015/03/13(金) 14:54

コメント返信：

[ 一覧(最新更新順) ]

[[20150305110919]] 『機械貸出表の作成』（奮闘中） ページの最後に飛ぶ

[[20150305110919]]　『機械貸出表の作成』（奮闘中）　ページの最後に飛ぶ