[[20110621205953]]　『検索条件に一致する日付を返す』（エクセルの学校　新1年生）　ページの最後に飛ぶ

『検索条件に一致する日付を返す』（エクセルの学校　新1年生）

以前下記のご相談をさせていただきました。

＝＝＝＝＝＝＝＝＝＝＝＝＝＝

エクセルの関数をうまく使って在庫切れを起こすタイミングを知りたいと考えています。

例えば下記のような表で

　　　　　　　　6/1　　　6/2　　6/3　　　6/4　・・　6/25　 ←日付

 品物Ａ　★　　　７　　　３　　　１　　 　５ 　・・ 　１　　←在庫量
 品物Ｂ  ☆　　１０　　　５　　　０　　　 １０ ・・ 　２　  ←在庫量

品物Ａ、Ｂの在庫推移を示しているとします。品物Ａは在庫数が１以下、Ｂは３以下
になる日付をそれぞれ★、☆のセルの位置に返したいと考えています。

（上記の例ですと★の部分に6/3、☆の部分に6/3を返したい）

以前は6/3くらいまでで

=INDEX($C$1:$E$1,MATCH(1,INDEX((C2:E2<=1)*1,),0))

=INDEX($C$1:$E$1,MATCH(1,INDEX((C3:E3<=3)*1,),0))
により狙いどおりの数字がばっちり出ました。

しかし、６月の在庫推移表を作っていく中で途中で在庫補充をしていき、
上記関数式を下記のように修正しました。

=INDEX($C$1:$AA$1,MATCH(1,INDEX((C2:AA2<=1)*1,),0))

=INDEX($C$1:$AA$1,MATCH(1,INDEX((C3:AA3<=3)*1,),0))

上記の例ですと本当は★の部分に6/25、☆の部分に6/25を返したいのですが、

あいかわらず、★の部分に6/3、☆の部分に6/3が返されます。

できれば今日が6/21とした場合に本日(6/21）以降で在庫切れになる日を
セットしたいのですが、何かいい関数はないでしょうか？

Excel2000、WindowsXPです。
よろしくご教授のほどお願いします。

 少々お尋ねします。　m(__)m

 １．6月は30日間あるのですが、C3～A3のセル数は25しかありません。
 　　土日とかが抜けている表なんでしょうか？

 　　※6/4は土曜日なので、サンプルを見る限り、
 　　　そうとも言えないような気もしているのですが。

 ２．品物に応じて、警告すべき個数を変えた数式になっていますが、
 　　とても面倒な気がします。

 　　何処かに品物別の警告個数を書いて置き、数式はそれを参照する様にして、
 　　同じ数式で処理した方が楽だと思うのですが、実際どうなんでしょうか？

 　　※ 掲示された数式はサンプルであって、実際は既にそんな工夫がされているのでしょうか？

 (半平太) 2011/06/22 10:33

 半平太さんの疑問はもっともですが、
 とりあえず、6月は30日分の範囲を指定して、
 本日以降のデータで、1以下が複数あり場合は本日に一番近い日付を表示させるものとして、

 B2=INDEX($A$1:$AF$1,1,MIN(IF((C2:AF2<>"")*(C2:AF2<=1)*($C$1:$AF$1>=TODAY()),COLUMN(C2:AF2),"")))

 と入力して、CtrlとShiftキーを押しながらEnterで確定、配列数式にします。
 この数式を下にコピーして　<=1 を　<=3 に修正、
 もう一度CtrlとShiftキーを押しながらEnterで確定して下さい。
 (sato)

(sato)さんへ
コメントありがとうございます。

Ｂ２のセルに
=INDEX($A$1:$AF$1,1,MIN(IF((C2:AF2<>"")*(C2:AF2<=1)*($C$1:$AF$1>=TODAY()),COLUMN(C2:AF2),"")))

をコピーして貼付したのですが、#VALUE!と表示されてしまいます。

式の中にある＜＞の意味等、日付の部分はC1からはじまっているのにA1から

指定している理由など式の意味をアドバイスいただければ、ありがたいです。

すみませんが、よろしくお願い致します。

（半平太さんへ）
上記の説明は本来は6/30まですべきところ、6/25で止めているため
セルの数が25になっています。

２のご質問は最終的にはおっしゃるとおりのやり方でやりたいのですが、

今はまだそこまでできていません。

あわせてアドバイスいただけるとありがたいです。

以上、よろしくお願い致します。m(__)m

（エクセル新１年生）

 >Ｂ２のセルに =INDEX($A$1:$AF$1,1,MIN(IF((C2:AF2<>"")*(C2:AF2<=1)*($C$1:$AF$1>=TODAY()),COLUMN(C2:AF2),"")))
 >をコピーして貼付したのですが、#VALUE!と表示されてしまいます。

 「CtrlとShiftキーを押しながらEnterで確定」
 これを実行しましたか?
 普通にEnterだけだと、#VALUE!となります。

 式の意味は、
 IF((C2:AF2<>"")*(C2:AF2<=1)*($C$1:$AF$1>=TODAY()),COLUMN(C2:AF2),"")

 この部分は、
 C2:AF2 が空白でなく、かつ1以下であり、かつC1:AF1がTODAY()以上であるならば、
 そのセルがA列から数えて何行目であるかを求めています。

 複数の該当があれば、複数個の値を返します。（これが配列数式にする意味です)

 MIN(    )で、その中で一番小さい数字を求めます。
 最後にINDEX(     )でA1:AF1 の該当する日付を求めています。

 MIN(    )で求めた数字は前述のとおり、A列から数えて何列目にあるかというものですから、
 INDEX(     )で検索するときも元の範囲はA1からを指定しています。

 お分かりになりましたでしょうか。(sato)

 >できれば今日が6/21とした場合に本日(6/21）以降で在庫切れになる日をセットしたいのですが、
 >何かいい関数はないでしょうか？
 その条件を付加すればいいのでは

 =INDEX($C$1:$AA$1,MATCH(1,INDEX((C2:AA2<=1)*($C$1:$AF$1>=TODAY()),),0))
 =INDEX($C$1:$AA$1,MATCH(1,INDEX((C3:AA3<=3)*($C$1:$AF$1>=TODAY()),),0))

By

(sato)さんへ
やることがひとつ抜けていたのですね。
ばっちりできました。式の意味もよくわかりました。
今後に応用していきたいと思います。
どうもありがとうございました。

（By）さんへ
教えていただいたやり方もうまくいきました。
ありがとうございました。

コメント返信：