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.11

kkkkkm

> ちなみに3個の場合、L～Nも関数では難しいでしょうか？

K列もS列を参照しているので変更する必要があります、2個だとS列に取り出している比較する対象の行番号で比較できますが、3個だと現状では無理でその方法はわかりません。同一サンプルで最後に出てくるサンプル以外はK列が100になるという事でしたらどうにかなりそうですが最小の値以外が100となるのでしたら関数での対応はちょっと浮かびません。

マクロでの対応はできますが
実行した後、手入力した時の「元に戻す」と同じようなことはできませんから自動では元に戻せません。
マクロのコードは示せますが、マクロの登録の方法などはご自身で調べてください。

3個の場合E列が最小の値以外の行はK列が100(G列と同じ)でいいのでしょうか。

補足

3個の場合E列が最小の値以外の行はK列が100(G列と同じ)になります。
同一サンプルで最後に出てくるサンプル以外はK列が100になるという事でしたらどうにかなりそうです。
⇒順番をどうにかできないか会社で検討してみます。

回答No.10

kkkkkm

2個より多い(3個とか)のでしたら対応していませんので、表示は正しくなりません。関数では分かりませんのでマクロになります。

関数がよろしければ新たに質問し直してください。

補足

いろいろとお手数をおかけして、大変申し訳ありません。質問が不十分でした。多くても３個までなのですが、ちなみに3個の場合、L～Nも関数では難しいでしょうか？
また、関数、ﾏｸﾛこだわりはありません。自動化できればと思っております。恐れ入りますが、ﾏｸﾛでしたら方法がありますでしょうか？

回答No.9

kkkkkm

> 添付の表でしたら、ｲﾁｺﾞの払い出しの日付が微妙にずれているようです。

どこがずれているのか教えてもらわないとわからないです

回答No.8

kkkkkm

同じサンプルは2個まで
2個が連続していなくてもいいパターンです
Noは大小は前後している可能性があり大きいほうのK列をG列と同じにする

R3に
=$B3&$C3&$D3&$F3
S3に
=IFERROR(VALUE(IFERROR(MATCH(R3,$R$1:R2,0),"")&IFERROR(MATCH(R3,R4:R1000,0)+ROW(R3),"")),"")
として下にコピーします。

R列S列は作業列ですので、どこにあってもいいですが変更した場合以下の数式のRとSは書き替えてください。

K3に
=IF(G3="","",IF(S3<>"",IF(E3>INDIRECT("E" & S3),G3,G3-40),G3-40))
として下にコピーしてください。

L3に
=IF($K3=$G3,"",IFERROR(IF($S3<>"",IF(AND(INDIRECT("$P" & $S3)=0, $S3>ROW()),INDEX(INDIRECT("$B" & $S3+1):$F$1000,MATCH($F3,INDIRECT("$F" & $S3+1):$F$1000,0),COLUMN(A1)),INDEX($B4:$F$1000,MATCH($F3,$F4:$F$1000,0),COLUMN(A1))),INDEX($B4:$F$1000,MATCH($F3,$F4:$F$1000,0),COLUMN(A1))),""))
として右と下にコピーしてください。

補足

表まで作成頂いて、ありがとうございます。
お手数をおかけします。

L3に教えて頂いた関数を下にｺﾋﾟｰすると、同じ日に２個以上受け入れがあるｻﾝﾌﾟﾙが、正しい値を示さないようです。添付の表でしたら、ｲﾁｺﾞの払い出しの日付が微妙にずれているようです。

回答No.7

kkkkkm

「2個以上はあるかどうか」は間違いで「2個を超えてあるかどうか」でした。
時間がなかったので焦ってました。

とりあえず
同じサンプルは2個まで
必ずその2個は連続している
Noは大小は前後している可能性があり大きいほうのK列をG列と同じにする
といったパターンでしたら

K3を以下に変更してみてください。
=IF(G3="","",IF(AND(B3&C3&D3&F3=B2&C2&D2&F2,E3>E2),G3,IF(AND(B3&C3&D3&F3=B4&C4&D4&F4,E3>E4),G3,G3-40)))

回答No.6

kkkkkm

> L3は、EXCEL表2のL18のことでしょうか？

回答No.4で「はい」と回答しましたが
2021_ 10_ 14_ 1_ オレンジの行です。L17です。

回答No.5

kkkkkm

あと、連続して入力されているかどうかはどうなのでしょうか

回答No.4

kkkkkm

> 同じサンプルは同じ日に2個までで、Noの大きいものは必ず下にあるというわ
> わけでなく、1、3，2と入力することもあります。

これは2個以上あるという事でしょうか。

> L3は、EXCEL表2のL18のことでしょうか？

はい。画像は説明用に下に表を作成していて本来は元の位置(上の表の位置)にあると思ったので、上の表の位置に下の表があると考えての数式になっています。

補足

すみません、ｻﾝﾌﾟﾙは２個以上で、連続でないときもあります。

回答No.3

kkkkkm

回答No.1の追加です。

K3はG3に何も入力されていないと-40になるので以下がいいかもしれません。

=IF(G3="","",IF(B3&C3&D3&F3=B2&C2&D2&F2,G3,G3-40))

回答No.2

kkkkkm

回答No.1の訂正です

同じサンプルは同じ日に2個までで
は
同じサンプルは同じ日に2個まででかつ連続で入力されている
です

補足

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))),""))
について、L3は、EXCEL表2のL18のことでしょうか？