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

kkkkkm

回答No.20の記載漏れです

S列で
上を探す場合1行目から自分の上までです。

お礼

丁寧にご説明ありがとうございます。お手数をおかけします。なかなか複雑ですが、再ﾁｬﾚﾝｼﾞしてみます！

Powered by GRATICA

回答No.20

kkkkkm

> 恐れ入りますが、S列、T列の数字の意味と、上記の式の意味を教えていただけないでしょうか。

S列は
同じ値(R列)を自分より下方向で探して見つかればその行番号を、下で見つからなければ上から探して見つかった行番号を、両方見つからなければ空白です。
(MATCHは見つかれば検索範囲のその位置を数値で返すので範囲の一番上の位置からそのセルの行番号(ROW(A3)など)を足して実際の行番号にしています)
20231116イチゴ(式はR列を参照しています)
のグループで説明すると
11行からみて次は12行、12行から見て次が14行、14行の次は下が無いので上の11行が表示されています。
(これで自分の行がS列の値より大きければ自分が一番下だと判断できますので自分より下から探せば2回目のサンプルが見つかります。一番下でよければの時に考えたものです)

T列は
上記のグループの中でS列の最大値を表示しています。グループが一個しかない場合は0になりますのでその場合はROW(A3)などでその行番号を表示しています。
20231116イチゴ
のグループだと14が最大値(グループの最終行)なので15行目から2回目のサンプルを探します。

上記の2列はもしかしたら一つの式にできるかもしれませんが、S列が先にあったのでそれを利用する方法にしました。

L列の式
=IF($K3=$G3,"",IFERROR(INDEX(INDIRECT("$B" & $T3+1):$F$1000,MATCH($F3,INDIRECT("$F" & $T3+1):$F$1000,0),COLUMN(A1)),""))
K列とG列が同じなら2回目はいらないので空白

INDIRECT("$B" & $T3+1)
は
12行目だと
INDIRECT("$B" & $T12+1)
になっていますからT12の14に1を足して
$B15
になります。T列で説明したように最大値の次の行を指定しています。
セルの指定を「"$B" & $T3+1」ような形で指定する場合「INDIRECT」を使う事が多いと思います。

COLUMN(A1)
これは列番号(A列が[1]B列が[2]・・・)を返しますので右にコピーした時に1，2，3と値が増えるようにしています

ということで
INDEX(範囲, 行番号, [列番号]) (行番号とかは実際のセルの番号ではなく範囲の基点からの位置です)
なので
INDIRECT("$B" & $T3+1):$F$1000
で範囲を
MATCH($F3,INDIRECT("$F" & $T3+1):$F$1000,0)
でINDEXの範囲での行方向の位置を
COLUMN(A1)
で列方向の位置を指定しています。

IFERROR(式,"")
以降に同じサンプルがみつからなければエラーになるので空白です。

回答No.19

kkkkkm

chayamatiさんが何故当月の1日とか月末の出し方を回答しているのか分かりませんが

当月の1日は
=EOMONTH(A2,-1)+1
当月の末日は
=EOMONTH(A2,0)
で出せると思いますよ。

回答No.18

chayamati

＞ご助言ありがとうございます。
　ただ、月末在庫の集計や日付を特定して確認するとき、年、月、日に分かれている方が
　ﾌｨﾙﾀｰかけやすいです。表まで作っていただいたのに、申し訳ありません。
◎そうでしょうか
　年、月、日の単独フィルターでは意味を為さないのでは
　３つの組み合わせのフィルターの方が複雑になりそうな
　ご存知かと思いますが
　日付から年を引き出す：Year(日付)
　日付から月を引き出す：Month(日付)
　日付から日を引き出す：Day(日付)
きょうは2023/11/28、28日前は2023/10/31日これに1を加えると月初日
　この理屈を式に
　　月初日=日付-Day(日付)+1
月末日=日付-DAY(日付)+35-DAY(日付)-DAY(日付)+35)
　式中の35は大の月、小の月、閏年に対応するため31～50
　添付画像はセルC2です

回答No.17

kkkkkm

> すごい、できました。ありがとうございます。

これが(1)の式でできたという意味でしたら

T3は以下の式でできると思います。普通に入力して下にコピーしてください。
こちらで確認ができないのでエラーになるかどうかはわかりません。

=IF(MAXIFS($S$3:$S$1000,$R$3:$R$1000,R3)=0,ROW(A3),MAXIFS($S$3:$S$1000,$R$3:$R$1000,R3))

エラーになれば回答No.16のT3の式にしてください。

お礼

すべて、完璧にうまくできました。 ありがとうございます。今、頂いた関数を参考に、変更したい表に関数を組み込んでいますが、不慣れで時間かかりそうです。質問させていただくかもしれませんので、数日、お待ちいただけないでしょうか。

Powered by GRATICA

補足

なかなか、式の意味がわからないと難しいですね。
L列をチャレンジしていますが、=IF($K3=$G3,"",IFERROR(INDEX(INDIRECT("$B" & $T3+1):$F$1000,MATCH($F3,INDIRECT("$F" & $T3+1):$F$1000,0),COLUMN(A1)),""))の関数の対象ｾﾙがどれに当たるか苦労しています。
恐れ入りますが、S列、T列の数字の意味と、上記の式の意味を教えていただけないでしょうか。

回答No.16

kkkkkm

> すごい、できました。ありがとうございます。

私が確認したかったのは(1)がうまくいったかどうかです。

> ｲﾁｺﾞのみををﾌｨﾙﾀｰにかけたとき

その式は「マクロを使わなくて2個を超えても実行できる方法」での式ではありません。

確認が面倒なので、こちらでテストできる(2)の式でやります。

R3に
=$B3&$C3&$D3&$F3
下にコピー
S3に
=IFERROR(MATCH(R3,R4:$R$1000,0)+ROW(A3),IFERROR(MATCH(R3,$R$1:R2,0),""))
下にコピー
T3に
=IF(MAX(IF(($R$3:$R$1000=R3),$S$3:$S$1000,""))=0,ROW(A3),MAX(IF(($R$3:$R$1000=R3),$S$3:$S$1000,"")))
としてCtrlキーとShiftキーを押しながらEnterキーを押す
下にフィル

O3に
=IF(AND(L3<>"",ISNUMBER(L3)),G3-K3,"")
下にコピー
P3に
=G3-SUM(K3,O3)
下にコピー
K3に
=IF(MIN(IF(($R$3:$R$1000=R3),$E$3:$E$1000,""))=E3,G3-40,G3)
としてCtrlキーとShiftキーを押しながらEnterキーを押す
下にフィル
L3に
=IF($K3=$G3,"",IFERROR(INDEX(INDIRECT("$B" & $T3+1):$F$1000,MATCH($F3,INDIRECT("$F" & $T3+1):$F$1000,0),COLUMN(A1)),""))として右と下にコピー

2023/11/16のイチゴとリンゴでNoを同じ1にしているのはサンプルによって値が切り分けられているのを確認するためです。
なお、同じ日に同じサンプルでNoの最小値が重複している場合は2回目があれば両方とも表示されます。そこまでは面倒なのでチェックしていません。

回答No.15

chayamati

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

作業の流れは以下になります。※EXCEL表1を参照
①日付2023年11月16日に、ｵﾚﾝｼﾞ、ｲﾁｺﾞ、ﾘﾝｺﾞを100g受け入れる。
②1回目の分析は、その時に自動で60g払い出す。
　ﾘﾝｺﾞでしたら、F11：＝B11、G11：E11-40
③D列を「ﾘﾝｺﾞ」でﾌｨﾙﾀｰかける。その時、前回分と今回分のみが在庫40g残っているので、
-------------------------------------------------------------------------
先ず、日付の概念をご理解いただきます。
通常、数値は10毎に桁上がりが発生します。10進法
時の流れは60秒で1分、60分で1時間、24時間で1日と変則的に桁上がりします。
さらに28日，29日，30日，31日で月の桁上がり、12月の次の年へ桁上がりで
日付は1900/1/1よりの経過日数で、シリアル値と呼びます
故に実態は正の実数で小数点未満は時分秒で以上は日、月、年で
大の月、小の月、閏年にも対応しています。
日付のセルへの入力は《21/11/26》《11/26》と年の一部または全部を省いて入力できます
以上の理由で、表を添付のように再構築されては

補足

ご助言ありがとうございます。ただ、月末在庫の集計や日付を特定して確認するとき、年、月、日に分かれている方がﾌｨﾙﾀｰかけやすいです。表まで作っていただいたのに、申し訳ありません。

回答No.14

kkkkkm

> マクロを使わなくて2個を超えても実行できる方法が浮かびました。

Noの一番小さいサンプルが一番下とは限らずどこにあっても60(Gれ列-40)に、それ以外を100(G列)にします。

回答No.13

kkkkkm

最後に出てくるサンプル以外を100にして検索するという方法を考えているときに、マクロを使わなくて2個を超えても実行できる方法が浮かびました。

ただし、右側にR列S列T列に作業列が必要になります。

あと、数式が使えるかどうかの確認をしてください。

MINIFSという関数が新しくできたようなのですが私の環境では使えないので以下の(1)が正しいかどうかがわかりません。
B列からJ列までデータがあるとして

(1)
K3に

=IF(MINIFS($E$3:$E$1000,$B$3:$B$1000,B3,$C$3:$C$1000,C3,$D$3:$D$1000,D3,$F$3:$F$1000,F3)=E3,G3-40,G3)

としてエラーにならずに下までコピーして正しい値(60と100)が出るかどうか確認してださい。

上記がうまくいかなかった場合は

(2)こちらはテスト済みです。
K3に

=IF(MIN(IF(($B$3:$B$1000=B3)*($C$3:$C$1000=C3)*($D$3:$D$1000=D3)*($F$3:$F$1000=F3),$E$3:$E$1000,""))=E3,G3-40,G3)

として
CtrlキーとShiftキーを押しながらEnterキーを押すと以下のように{}で囲まれた状態で見えると思います。
実際に{}があるわけではないので直接{}を記載しても駄目です。

{=IF(MIN(IF(($B$3:$B$1000=B3)*($C$3:$C$1000=C3)*($D$3:$D$1000=D3)*($F$3:$F$1000=F3),$E$3:$E$1000,""))=E3,G3-40,G3)}

これを下までフィルしてみて正しい値(60と100)が出るかどうか確認してください。

補足

すごい、できました。ありがとうございます。
あと、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))),""))をいれると・・・
ｲﾁｺﾞのみををﾌｨﾙﾀｰにかけたとき
2023.11.16受入　№1ｲﾁｺﾞ　2回目分析日が2023.11.16になってしまいます。2023.11.19が正解なのですが、同じ日に同じｻﾝﾌﾟﾙが2個以上あり、下に100g全量払い出されたものがあると、上のｻﾝﾌﾟﾙがその日に払い出されるようです。
　　　　　　　　　　　　　1回目分析　　　　　2回目分析
2023.11.16受入　№1　ｲﾁｺﾞ　2023.11.16　60g　　2023.11.16　40g
2023.11.16受入　№2　ｲﾁｺﾞ　2023.11.16　100g

回答No.12

kkkkkm

とりあえずのマクロです。

添付画像の状態でテストしています。

あらかじめ
O3に
=IF(AND(L3<>"",ISNUMBER(L3)),G3-K3,"")
として下にコピーして下さい。

P3に
=IF(K3="","",G3-SUM(K3,O3))
として下にコピーしてください。

実行対象としたいセルのどこかを選択します。
16日でしたら16日がある行のどこでもいいです。
選択したらマクロを実行します。

選んだ日付の行がフィルターされてそこでいいのかの確認が入ります。
YESを選択すると選択した日付のデータをもとに必要な作業が行われます。

15日を選択して実行すると5行目のブドウの行の2回目に2023,11,15の日付が入ります。

その後16日を選択して実行すると3行目のオレンジと4行目のリンゴ及び7行目のイチゴと8行目のブドウに2023,11,16が入ります

間違って15日を実行せずに16日を実行した場合
5行目と8行目のブドウに2023,11,16が入ります。
その場合15日を実行すると5行目は2023,11,15になります。
自分より上の行で自分と一致するデータで2回目が自分より未来の日付で場合あった場合は自分の受入日で上書きします。

Sheets("Test")のTestは実際のシート名に変更してください。
FirstRow = 3の3はデータの始まりの行(受入日の下の行)

マクロは添付画像の状態の表でないと正しい結果は得られません。
実際の表と違う場合は添付画像と実際の行を見比べてコードの
"B"とか"G"とか「"」で囲まれたのが列指定ですのでご自身で変更してください。
Range("B2")は受入日のセルを指定してください。

Sub Test()
Dim Ws As Worksheet
Dim mRng As Range
Dim FirstRow As Long, LastRow As Long, FTopRow As Long, i As Long, j As Long

Set Ws = Sheets("Test")
FirstRow = 3

With Ws.Range("B2")
.AutoFilter 1, Cells(Selection.Row, "B").Value
.AutoFilter 2, Cells(Selection.Row, "C").Value
.AutoFilter 3, Cells(Selection.Row, "D").Value

FTopRow = Ws.AutoFilter.Range.Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row
LastRow = Ws.Cells(Rows.Count, "B").End(xlUp).Row
End With

If MsgBox("この日付のデータで実行します。よろしいですか?", vbYesNo + vbQuestion + vbDefaultButton2) = vbYes Then

For i = FTopRow To LastRow
For j = i + 1 To LastRow
If Ws.Cells(i, "F").Value = Ws.Cells(j, "F") Then
If Ws.Cells(i, "E").Value > Ws.Cells(j, "E") Then
Ws.Cells(i, "K").Formula = Ws.Cells(i, "G").Value
Else
Ws.Cells(j, "K").Value = Ws.Cells(j, "G").Value
End If
End If
Next j
If Ws.Cells(i, "K").Value = "" Then
Ws.Cells(i, "K").Value = Ws.Cells(i, "G").Value - 40
End If
Next i

For i = FTopRow To LastRow
If Ws.Cells(i, "G").Value <> Ws.Cells(i, "K").Value Then
For j = FirstRow To FTopRow - 1
If Ws.Cells(i, "F").Value = Ws.Cells(j, "F").Value Then
If Ws.Cells(j, "L").Value & Ws.Cells(j, "M").Value & Ws.Cells(j, "N").Value = "" Then
Ws.Cells(j, "L").Resize(1, 3).Value = Ws.Cells(i, "B").Resize(1, 3).Value
ElseIf IsDate(Ws.Cells(j, "L").Value & "/" & Ws.Cells(j, "M").Value & "/" & Ws.Cells(j, "N").Value) = True Then
If CDate(Ws.Cells(j, "L").Value & "/" & Ws.Cells(j, "M").Value & "/" & Ws.Cells(j, "N").Value) > _
CDate(Ws.Cells(i, "B").Value & "/" & Ws.Cells(i, "C").Value & "/" & Ws.Cells(i, "D").Value) Then
Ws.Cells(j, "L").Resize(1, 3).Value = Ws.Cells(i, "B").Resize(1, 3).Value
End If
End If
End If
Next j
End If
Next i

End If
Ws.AutoFilterMode = False
Set Ws = Nothing
End Sub

補足

凄すぎて言葉にならないです。本当にありがとうございます。私にはﾊｰﾄﾞﾙが高すぎて頭がﾌﾘｰｽﾞしています・・・下記、№の順番を検討してみて、どうにもならない場合、上記、ﾏｸﾛ、ﾁｬﾚﾝｼﾞしてみます！！