Excelで下記のような在庫管理表があります。
A列 B列 C列 D列 E列 F列 G列
----------------------------------------------------------------------
1 品名 発売日 項目 9/20 9/21 9/22 9/23
2 (火) (水) (木) (祝)
----------------------------------------------------------------------
3 A 09/20 日初在庫数 4000 3900 3750 3640
4 A 09/20 販売予測数 100 150 110 120
5 A 09/20 販売予測累計100 250 360 480
6 A 09/20 仕入数 0 0 0 0
7 B 09/20 日初在庫数 5000 4800 4550 4360
8 B 09/20 販売予測数 200 230 250 190
9 B 09/20 販売予測累計200 430 650 840
10 B 09/20 仕入数 0 0 0 0
G列より右以降は、発売開始から90日目までのデータが入っています。
10行目以下は、商品C・D・E・F...と、約10種類の商品がAやBと同じような形式で続いています。
このリストをもとにして、下記の条件を満たす商品の必要データを
自動的に別シートに書き出したいのです。
抽出条件は、
・発売開始から64日目の日初在庫がゼロ以下の商品
抽出したいデータは、
・品名:A列そのまま
・日付:日初在庫が1500を切る日。
但し、この日付が土・日・祝日の場合は、1つ手前の営業日。
・数量:発売開始から64日目の日初在庫数の絶対値。
書き出したい形式は、↓のようなイメージです。
A列B列C列D列
品名記号日付 数量
関数でできますでしょうか?
それともマクロになるのでしょうか??
教えて(助けて)頂けると、本当に嬉しいです。
分かりにくい説明&リストで恐縮ですが、よろしくお願いします。
A 回答 (1件)
- 最新から表示
- 回答順に表示
No.1
- 回答日時:
こんにちは~
思いつきですが、一応ご参考まで。
とんでもないカン違いをしているかもしれませんので悪しからず。
< 前提 >
> 但し、この日付が土・日・祝日の場合は、1つ手前の営業日。
(1)↑を求めるには、WORKDAY 関数が必要です。WORKDAY 関数は、標準関数ではありません。メニューの「ツール」-「アドイン」-「分析ツール」にチェックを入れてください。
分析ツールがない場合は、メッセージにしたがって、OFFICEのCDからインストールしてください。
http://www.atmarkit.co.jp/fwin2k/win2ktips/383wo …
(2)祝日も考慮するなら祝日一覧表が必要です。
どのシートでもいいので、たとえばA列に今年の祝日の日付をすべて入力。入力後、日付をすべて選択して( 隣のセルに祝日名を入力したとしても選択するのは日付のみ ) 、メニューの「挿入」-「名前」-「定義」で、いちばん上の名前欄に<祝日>と入力してください。
http://homepage1.nifty.com/kenzo30/ex_kisotoku/e …
※この祝日一覧表は、年が変わるたび、その年の祝日に変更する必要あり。
(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の作業列が邪魔なら非表示にしてください。
★補足質問をされる場合はできるだけ具体的にお願いします。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) VBAで質問があります 1 2022/10/19 10:32
- その他(Microsoft Office) Excelで該当しない項目(#N/Aの商品名)を簡単に表示・抽出させる方法についてです 1 2022/08/25 22:12
- Visual Basic(VBA) VBAで、1つのエクセルで、2つのシートからもう1つのシートに条件のある転記コードを教えてください。 1 2023/03/16 18:07
- その他(Microsoft Office) Excelの関数(FILTER関数)について教えてください 2 2023/07/31 16:11
- Visual Basic(VBA) VBAで最新のデータを別シートに転記する方法をお教えください。 3 2022/04/07 19:20
- Visual Basic(VBA) VBAコードで質問があります 2 2022/10/20 15:27
- Visual Basic(VBA) VBAでの共有パスにつきまして 1 2023/03/04 17:24
- Excel(エクセル) 【困っています】VBA 追加処理の記述を教えてください。 1 2022/08/25 22:54
- Excel(エクセル) エクセルの条件付き書式 個人シートを参照して集計シートに色付けしたい 1 2023/06/22 00:39
- Excel(エクセル) 【条件付き書式】countifsで複数条件を満たしたセルを赤くする方法 2 2023/02/09 23:53
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルでの作業計算方法について
-
Microsoft1Officeの互換ソフト...
-
【マクロ】その時、その時で変...
-
はがきについて。
-
【マクロ】読取専用のファイル...
-
エクセル初心者です 関数の入れ...
-
【関数】適切な文字数の数字を...
-
LOOKUP関数を使えばいいのでし...
-
【関数】先頭だけにある、半角...
-
Excel ピボットテーブルで日付...
-
Excelのpivotについて質問です
-
時間によってファイル名が変わ...
-
エクセル 白黒印刷で白線を印刷...
-
Aというブックの1というシート...
-
エクセル関数を教えてください
-
WPS OFFICEでの縦書きについて
-
Excelのチェックボックスの使い...
-
エクセルの条件付き書式につい...
-
エクセルのセルに同じ大きさの...
-
エクセルの関数について教えて...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報