EXCEL表の手入力を関数で自動化したい

質問

こちらで質問させていただいて、素晴らしい回答頂いたのですが、新たな問題が出てきましたので、再度質問させていただきます。

前回の質問は以下になります。
私は仕事で、毎日、10個ほどのｻﾝﾌﾟﾙ（１個100g）について、理化学分析と味の評価をおこなっています。使用するｻﾝﾌﾟﾙについては、EXCEL表で在庫管理しています。受け入れた日に全部使用するわけではなく、最初に60g使用し、残りの40gは次回の比較に使用します。日々の手入力を改善したく、分析の払い出しを関数で自動化できないか検討しています。1回目の分析は自動化できましたが、2回目の分析日と使用量の払い出しの関数が、なかなかいい案がでません。いい案がありませんでしょうか？

作業の流れは以下になります。※EXCEL表1を参照
①日付2023年11月16日に、ｵﾚﾝｼﾞ、ｲﾁｺﾞ、ﾘﾝｺﾞを100g受け入れる。
②1回目の分析は、その時に自動で60g払い出す。
　ﾘﾝｺﾞでしたら、F11：＝B11、G11：E11-40
③D列を「ﾘﾝｺﾞ」でﾌｨﾙﾀｰかける。その時、前回分と今回分のみが在庫40g残っているので、前回分を2023年11月16日の日付で40g払い出し、在庫をｾﾞﾛにする。なので、最終的にすべてのｻﾝﾌﾟﾙは在庫40ｇとなる。

頂いた回答は、
H3に「=IFERROR(INDEX(B4:$D$1000,MATCH(D3,D4:$D$1000,0),1),"")」
I3に「=IF(AND(H3<>"",ISNUMBER(H3)),E3-G3,"")」
J3に「=E3-SUM(G3,I3)」
でした。

新たな質問というのが、EXCEL表1のH3の日付を、EXCEL表2のように年、月、日で分けるとそれぞれ、H3の関数をどうあてはめればいいかということです。
また、EXCEL表2の11/16のｲﾁｺﾞのように、同じｻﾝﾌﾟﾙが２個あった場合は、№の数が大きい方を１回目の分析で全量払い出し、１個だけ在庫を残すようにするためにはどうすればいいでしょうか？

EXCEL表2の青枠4つの式を教えていただきたいです。教えていただければ、他の列のセルにもｺﾋﾟｰする予定です。よろしくお願いいたします。

回答No.22

kkkkkm · ベストアンサー

位置が変わるのでしたら以下の方法でいけると思います。

今回の例にした表のシートをコピペ(全く同じシートを作成する)したシートで行や列の挿入で位置合わせをして
元のL3の式の
IDIRECT("$B" & $T3+1)
の$B
INDIRECT("$F" & $T3+1)
の$F
が変化していないと思いますからそれを元のB列とF列を見てその項目の列に変更してください。
あと
COLUMN(A1))
のA1が変化していると思いますからそれはA1に戻して右と下にコピーしてください。

あと行を挿入した場合
元のS3の式で
後の方のIFERROR(MATCH(R3,$R$1:R2,0),""))
の$R$1の$1が変化していると思いますからそれを$1に戻して下にコピーして下さい。

表の間に行や列を挿入した場合も上記が変化していたら変更してください。

それで正しい結果が出たらその式を本来の表にコピペしてください。セルをコピペではなく式そのものをコピペです。

お礼

kkkkkm様、すべて解決しました。諦めずに最後まで検討していただき、本当にありがとうございました🙇また、こちらでお世話になること多々あると思います。また、ご教授いただければ幸いです。本当にありがとうございました😊

Powered by GRATICA

回答No.1

kkkkkm

同じサンプルは同じ日に2個までで、Noの大きいものは必ず下にあるという状態でしたら

K3に
=IF(B3&C3&D3&F3=B2&C2&D2&F2,G3,G3-40)
下にコピーしてください。

L3に
=IF($K3=$G3,"",IFERROR(IF(AND($B3&$C3&$D3&$F3=$B4&$C4&$D4&$F4,$P4=0),INDEX($B5:$F$1000,MATCH($F3,$F5:$F$1000,0),COLUMN(A1)),INDEX($B4:$F$1000,MATCH($F3,$F4:$F$1000,0),COLUMN(A1))),""))
右と下にコピーしてください

補足

再度、ご回答いただきありがとうございます。

同じサンプルは同じ日に2個までで、Noの大きいものは必ず下にあるというわけでなく、1、3，2と入力することもあります。今回、№はわかりやすい例として、1，2，3という単純な例にしていますが、実際は、134，324など№の桁は多いです。情報不足で申し訳ありません。