LOOKUP？月末の在庫数を下から順に検索

質問

この先ずっと入・出庫を延々記録する在庫管理表を作成しているのですが、
希望する月の月末在庫数を求める関数がどうにも作れません。

C12の処理年月の欄には、月末在庫数を知りたい年月を入力します。
C12の年月を元に、B17～無限の範囲で下から順に検索したいのです、
毎月末日に必ず入・出庫があるわけではないので、一致したその月の最後の取引を探してほしいんです。

簡潔に言うとVLOOKUPの下から探すバージョンなのですが、
ネットで色々と調べて過去の質問を参考にLOOKUPやSUMPRODUCT等考えてみたのですが、頭が足りず上手く出来ませんでした。

処理年月が空白の時は空白を表示させたいので、
=IF($C$12="","",☆)
の関数の中で☆の部分をどのようにしたらいいか、ご教授宜しくお願い致します。

文で上手く説明が出来なかったので、詳細は添付した図から読み取ってもらえると幸いです。

回答No.5

keithin · ベストアンサー

＞同様にB17以下の日付のセルについても、表示形式「文字列」入力規則「10文字」で、必ず「yyyy/mm/dd」の形で打たせるようにしています。

リクツの上では，問題ありません。
Ｃ１２を含め文字列で記入するということでしたら，若干変則的な計算ですが，たとえば
Ｃ１３：
=IF(C12="","",SUMIF(B17:B9999,"<"&C12&"/99",C17:C9999)-SUMIF(B17:B9999,"<"&C12&"/99",D17:D9999))
と言った具合にすることはできます。

ただ，文字列ではなく日付として記入し，セルの書式設定の表示形式のユーザー定義で
yyyy/mm/dd
などのようにしておいた方が，エクセル的にはより使いやすくなります。
たとえば今作成しようとしている入力表を元に，四半期毎の集計とか棚卸しといった「データの二次加工」を行うのが容易になります。

お礼

度々のご回答ありがとうございます。
無事動作致しました！

データは増えていくので、
=IF(C12="","",SUMIF(B17:B9999,"<"&C12&"/99",C17:C9999)-SUMIF(B17:B9999,"<"&C12&"/99",D17:D9999))
↓
=IF(C12="","",SUMIF(B:B,"<"&C12&"/99",C:C)-SUMIF(B:B,"<"&C12&"/99",D:D))
というセル参照に変えさせてもらいました。
これでも無事動作したので、これを使わせて頂きたいと思います。
もしエラーが出たら大人しく「B17:B65536」といったセル参照にしたいと思います。

データの二次加工につきましては、
商品ごと(EXCELファイルごと)の月末在庫数を抜き出したり、現在庫数量を抜き出したりといったことはすることになると思います。
そちらのシステムはまだ作り出してもいないんですが、予定ではセルの名前を用いて絶対参照で在庫数量を抜き出すつもりです。
ユーザー定義「yyyy/mm/dd」などの設定にし直してみたのですが、やはり入力規則で文字列(文字数指定)を設定したいので、やはり年月日は文字列でいきたいと思います。

無事運用までいければ良いのですけど・・・それまでには教えてgooにたくさんお世話になりそうです。

説明下手な質問に答えて頂き、本当にありがとうございました。
関数の仕組みはじっくり考えてみます。

回答No.6

ki-aaa

#3,#4です。

捕捉を読みました。

合計行「B44:J44」の一行上に行を挿入し、合計行が「B45:J45」となったところで合計行の二行上「B43:J43」をコピーして合計行の一行上「B44:J44」に貼り付けをする。

　↓のように変えたらどうなります。

合計行「B44:J44」をコピーして、B45:J45」に貼り付けます。
合計行が「B45:J45」となったところで合計行の二行上「B43:J43」をコピーして合計行の一行上「B44:J44」に貼り付けをする。

それから、マクロを使って検索するのでしたら
下のほうから検索すれば、簡単です。

お礼

度々のご回答ありがとうございます。

それも考えてやってみたのですが、関数に組まれている合計行のセルが下に動いた時点で、
その関数は崩れてしまいました。
例えば、
I43=sum(C43:C44)の状態で44行目を45行目に移すと、I43の関数は
I43=sum(C43:C45)になってしまうということです。
上手くオートフィルさせるには、関数の崩れない場所「I42」から、合計行「45」の一行上と二行上に正しくオートフィルさせないといけないという風に考えました。

あと、マクロでの検索はしません。
マクロを改善しなければ動作しないのであれば、マクロを組み直すという意味です。

今回の質問については解決致しましたので、これで締め切らせて頂きます。
度重なるご回答、お知恵を貸して頂きありがとうございました。

回答No.4

ki-aaa

#3です。

式に間違いがありました。
何も入力していないセルは一月になりその判定がありませんでした。

=IF(MONTH(B17)<>MONTH(B18),TEXT(B17,"yyyy/mm"),"")
を次の式に変えてください。
=IF(TEXT(B17,"yyyy/mm")<>TEXT(B18,"yyyy/mm"),TEXT(B17,"yyyy/mm"),"")

お礼

ありがとうございます。
自分には理解すら精一杯の世界です・・
VLOOKUPを下からというのは、思ったよりも難しいんですね・・
一生懸命マクロと関数を頑張ってみます。

まだ不具合だらけのシステムなので、続けてご教授頂ければ幸いです。

回答No.3

ki-aaa

こんにちわ

I列の前に一列挿入して、
=IF(MONTH(B17)<>MONTH(B18),TEXT(B17,"yyyy/mm"),"")
の式を入力すれば、

あとはVlookupで簡単にできると思います。
=VLOOKUP(TEXT($C$12,"yyyy/mm"),I:J,2,0)

お礼

ご回答ありがとうございました。
=IF(MONTH(B17)<>MONTH(B18),TEXT(B17,"yyyy/mm"),"")
の関数には脱帽しました。凄いです・・
活用の機会を探してみます。参考になるご回答をありがとうございました。

補足

こんにちわ*・ｖ・
ご回答ありがとうございます。

教えて頂いた通りに作ってみました。
(利用する側にとっては関係がないので)不可視化させる列が出来てしまうのは、
あまり綺麗でないのですがそれでもちゃんと動くのならこの際・・・と思ったのですが
合計行の一行上に空白行を挿入した際に、上手くオートフィルされませんでした。

これは別の問題になってしまうのですが・・・。

現在B44:J44が合計を表示する行になっています。
B43:J43まで入出記録がいっぱいになった時には、「行追加」のマクロで合計を表示する行の一行上に行を追加させるようにしています。

例えばこの場合、
合計行「B44:J44」の一行上に行を挿入し、合計行が「B45:J45」となったところで合計行の二行上「B43:J43」をコピーして合計行の一行上「B44:J44」に貼り付けをする。
というマクロになっています。

ですがこのマクロを実行した時、オートフィルによるセル参照が崩れてしまいます。
今回ki-aaaさんに頂いた関数をアレンジして入れてみたのですが、例えば
I43=IF(AND(B44=合計,B43=""),"",IF(TEXT(B43,"yyyy/mm")<>TEXT(B44,"yyyy/mm"),TEXT(B43,"yyyy/mm"),""))
と入れて行追加のマクロを実行すると、I43の関数が
=IF(AND(B45=合計,B43=""),"",IF(TEXT(B43,"yyyy/mm")<>TEXT(B45,"yyyy/mm"),TEXT(B43,"yyyy/mm"),""))
と変わってしまいます・・。

行追加のマクロ内の「合計の二行上から一行上にコピペ」の作業を、
「合計の三行上から、合計の一行上までオートフィル」というマクロに書き換えられればいいんでしょうか？
これも中々、すぐには完成しそうにありません・・・。

もしマクロを変えて上手くいけば、ki-aaaさんに頂いた関数で動作しそうなのですが、
出来れば不可視化列や不可視化行というものを作りたくないので、続けてご回答を募集致します。

続けてご教授頂ければ幸いです。

回答No.2

shinkami

的外れの回答ですが最新の在庫数でよければ
16行目以下だけのシートを追加して、
累計項目名列(I列)、累計計算列（J列）を追加して添付のような式をにします。

※MAX関数,SUM関数の添え字設定の時に
列名をクリックすると添付のような式になります。
ここでA:AとはA列の全てがMAX関数の対象になります。

お礼

無事解決致しました。
この度はありがとうございました。

補足

ご回答ありがとうございます。

添付して頂いた画像通りに作ってみましたが、私が求めているものとは違いました。
説明不足で申し訳ありません。

最新の在庫数量は、B17:I17から下に向かって適当なところに合計を表示するための行を設けてあります。
その合計行に、C44=入庫数累計・D44=出庫数累計・I44=現在庫数と表示するようにしています。
データが増えてきた時には合計行の一行上に行を追加するマクロを組んでいます。
このマクロも、改善したい点があって出来ずにいるのですが・・・。それはまた別の話とさせて頂きます。

今回求めたいのは、最新の、現在の在庫数量ではなく、
指定した年月の月末在庫数量を求めたいのです。
例えば今日は2012年6月25日だけど、2012年4月末時点の在庫数量を知りたい。だとかそういうことです。

続けてご教授頂ければ幸いです。

回答No.1

keithin

先に（ご質問では解決済みになっていますが）「毎日の在庫」の計算方法から。

Ｉ１７：
=IF(B17="","",SUM($C$17:C17)-SUM($D$17:D17))
以下コピー。

この計算をよく分析して，その日までの累計入庫数からその日までの累計出庫数を差し引けば，当該日の在庫が求まる事を理解してください。
ご質問で掲示されたサンプルでは考慮されていませんが，リストの先頭に「前期から繰越された在庫数」がどこか別にあるときは，その数を繰り込まないと間違える事も注意してください。

その上で，指定月末の在庫数は，これと全く同じ考え方で計算できます。

手順：
これもご相談ではっきり書かれていませんが，仮に今Ｃ１２には「2012/3/1」のように年月日形式でキチンと日付を記入して，その上で表示だけで年/月を表しているとして。
Ｄ１２には
=IF(C12="","",SUMIF(B17:B9999,"<"&DATE(YEAR(C12),MONTH(C12)+1,1),C17:C9999)-SUMIF(B17:B9999,"<"&DATE(YEAR(C12),MONTH(C12)+1,1),D17:D9999))
のようにして，指定年月末日時点での在庫を計算します。

お礼

毎日の在庫数量の関数や繰越在庫数量までご配慮頂き、ありがとうございます。
大変助かりました。
この度は本当にありがとうございました。

補足

ご回答ありがとうございます。

本日在庫量の関数はそれで完璧でしたΣ(゜д゜)
I17から合計行までオートフィルで問題なく動きました。ありがとうございます。

前期からの繰越は、欄を作るかどうか相談してみましたところ、
期毎にシートを変えたりする予定はないので、これから在庫数０からのスタートをした後はひたすら書き連ねていくそうです。

指定月末在庫数について、
処理年月のセルには表示形式「文字列」入力規則「7文字」で、必ず「yyyy/mm」の形で打たせるようにしています。
また、同様にB17以下の日付のセルについても、表示形式「文字列」入力規則「10文字」で、必ず「yyyy/mm/dd」の形で打たせるようにしています。
たくさんの部署で利用してもらう為、人によって入力の差異が出ると困るのでこのようにしました。
なので頂いた関数ではうまく動作致しませんでしたｏｒｚ
表示形式「文字列」では厳しいのでしょうか・・

続けてご教授頂ければ幸いです。