今あるリストから条件に合ったものを別シートに書き出したい！【Excel2002】

質問

Excelで下記のような在庫管理表があります。

　　A列　　B列　　C列　　　　D列　　E列　　F列　G列
----------------------------------------------------------------------
１　品名　発売日　項目　　　9/20　9/21　9/22　9/23
２　　　　　　　　　　　　　　(火）　（水）　（木）　（祝）
----------------------------------------------------------------------
３　Ａ　　09/20　日初在庫数　4000　3900　3750　3640
４　Ａ　　09/20　販売予測数　100　　150　　110　120
５　Ａ　　09/20　販売予測累計100　　250　　360　480
６　Ａ　　09/20　仕入数　　　　0　　　0　　　0　　0
７　Ｂ　　09/20　日初在庫数　5000　4800　4550　4360
８　Ｂ　　09/20　販売予測数　200　　230　　250　190
９　Ｂ　　09/20　販売予測累計200　　430　　650　840
10　Ｂ　　09/20　仕入数　　　　0　　　0　　　0　　0

Ｇ列より右以降は、発売開始から90日目までのデータが入っています。
10行目以下は、商品Ｃ・Ｄ・Ｅ・Ｆ...と、約10種類の商品がＡやＢと同じような形式で続いています。

このリストをもとにして、下記の条件を満たす商品の必要データを
自動的に別シートに書き出したいのです。

抽出条件は、
・発売開始から64日目の日初在庫がゼロ以下の商品

抽出したいデータは、
・品名：Ａ列そのまま
・日付：日初在庫が1500を切る日。
　　　　 但し、この日付が土・日・祝日の場合は、1つ手前の営業日。
・数量：発売開始から64日目の日初在庫数の絶対値。

書き出したい形式は、↓のようなイメージです。
　A列 B列 C列 D列
品名 記号 日付　　数量

関数でできますでしょうか？
それともマクロになるのでしょうか？？
教えて（助けて）頂けると、本当に嬉しいです。

分かりにくい説明＆リストで恐縮ですが、よろしくお願いします。

回答No.1

shiotan99

こんにちは～

思いつきですが、一応ご参考まで。
とんでもないカン違いをしているかもしれませんので悪しからず。

＜ 前提 ＞
> 但し、この日付が土・日・祝日の場合は、1つ手前の営業日。
（1）↑を求めるには、WORKDAY 関数が必要です。WORKDAY 関数は、標準関数ではありません。メニューの「ツール」－「アドイン」－「分析ツール」にチェックを入れてください。
分析ツールがない場合は、メッセージにしたがって、OFFICEのCDからインストールしてください。
http://www.atmarkit.co.jp/fwin2k/win2ktips/383workday/workday.html

（2）祝日も考慮するなら祝日一覧表が必要です。
どのシートでもいいので、たとえばA列に今年の祝日の日付をすべて入力。入力後、日付をすべて選択して（ 隣のセルに祝日名を入力したとしても選択するのは日付のみ ） 、メニューの「挿入」－「名前」－「定義」で、いちばん上の名前欄に＜祝日＞と入力してください。
http://homepage1.nifty.com/kenzo30/ex_kisotoku/ex_ks_tokubetu6.htm

※この祝日一覧表は、年が変わるたび、その年の祝日に変更する必要あり。

（3）「在庫管理表」は Sheet1、抽出シートは Sheet2 だとします。
（4）Sheet1 のシート構成は、質問にある表の通りだとします。
（5）Sheet1 の 1行目は、12/18（ CO列 ）まで日付が入っているものとします。

■作業列を使います。
作業列はどの列でもかまいません。
ここでは例として、Sheet1 の CQ、CR、CS列を使います。

※1行目の日付が CO列以降にもある場合は、下の（1）から（3）の数式すべて COを最終列の列記号に変更してください。
※その場合は作業列も右にずれると思いますので、（2）（3）の数式の CQ3 も変更してください。

（1）Sheet1 の CQ3に

=IF(MOD(ROW(A1),4)=1,IF(LOOKUP(B3+63,$D$1:$CO$1,D3:CO3)<1,ROW(),""),"")

※64日目というのは、発売日を含めて、でよろしいですか？
発売日を含めないのなら、B3+63 の 63を 64にしてください。

★仕入数という項目があるということは、日初在庫数というのは減る一方ではなく、増えることもあるのですか？
たとえば 1400 → 1600 → 1300
というようなケースがあるのでしょうか。

↑のようなことはなく、日初在庫数は前日と同じか、またはマイナスかのどちらかなら、
（2）Sheet1 の CR3に（ 日付 ）

=IF(CQ3="","",WORKDAY(INDEX($D$1:$CO$1,COUNTIF(D3:CO3,">=1500")+2),-1,祝日))

※祝日というのは＜前提＞（2）の祝日一覧表

★1400 → 1600 → 1300 のような場合もあるのでしたら上の数式ではうまくいきません。別の数式になりますのでお知らせください。
その場合、1500を切る日というのは 1400の日でしょうか、1300の日でしょうか？

（3）Sheet1 の CS3に（ 数量 ）

=IF(CQ3="","",ABS(LOOKUP(B3+63,$D$1:$CO$1,D3:CO3)))

※絶対値というのは、日初在庫数がたとえば -1000でも、1000と表示するという意味ですか？
※（1）に同じく、64日目というのが発売日を含めないのなら、B3+63 の 63を 64にしてください。

※（1）から（3）の数式を、すべて A列のデータ最終行までフィルコピー
一応、CR列の表示形式は日付にしてください。

***
※上の（1）から（3）の数式が CQ、CR、CS 列以外に入っている場合は、下の（4）（5）の数式のCQ、CR の列記号を変更してください。

（4）Sheet2 の A2に（ 品名 ）

=IF(COUNT(Sheet1!CQ:CQ)<ROW(A1),"",INDEX(Sheet1!A:A,SMALL(Sheet1!CQ:CQ,ROW(A1))))

★ B列の記号というのがなんのことかわかりませんでしたのでパスします。

（5）Sheet2 の C2に（ 日付 ）

=IF($A2="","",INDEX(Sheet1!CR:CR,SMALL(Sheet1!$CQ:$CQ,ROW(C1))))

※そのまま右のD2にフィルコピー

※ A2、C2、D2 の数式を商品の種類数だけ、たとえば商品が10種類だとすれば11行目までフィルコピー
※C列の表示形式は日付（ A列、D列は標準 ）
※Sheet1の作業列が邪魔なら非表示にしてください。

★補足質問をされる場合はできるだけ具体的にお願いします。