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

質問

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

回答No.8

kkkkkm · ベストアンサー

回答No.7の補足です。

No.7のH3の式はF11：＝B11ということでしたのでF列の日付を取得していますが、将来F11：＝B11ではなくなったとかでB列の日付を取得しておいた方が良かったなぁの可能性があるのでしたら以下の式に変更してください。

=IFERROR(INDEX(B4:$D$1000,MATCH(D3,D4:$D$1000,0),1),"")

お礼

出来ました。素晴らしいです。こんなに簡単に、案が出るなんて羨ましい過ぎます。 本当にありがとうございました。助かりました！！

Powered by GRATICA

回答No.7

kkkkkm

> ③D列を「ﾘﾝｺﾞ」でﾌｨﾙﾀｰかける。その時、前回分と今回分のみが在庫40g残っているので、前回分を11/16の日付で40g払い出し、在庫をｾﾞﾛにする。なので、最終的にすべてのｻﾝﾌﾟﾙは在庫40ｇとなります。

なるほど、下に同じものがあればそれが2回目の分析日ということでしたか。

H3に
=IFERROR(INDEX(D4:$F$1000,MATCH(D3,D4:$D$1000,0),3),"")
として下にコピーしてください。とりあえず検索対象は1000行までにしています。

I3に
=IF(AND(H3<>"",ISNUMBER(H3)),E3-G3,"")
として下にコピーしてください。

J3に
=E3-SUM(G3,I3)
として下にコピーしてください。

回答No.6

kkkkkm

回答No.5で忘れてました

残ですが6行目とかで60とかになっているのが正しいとすれば
J3は
=IF(I3="",G3,E3-SUM(G3,I3))

回答No.5

kkkkkm

回答No.3とNo.4ですが
分析日は手入力でということでしたら
H3に2回目の分析日を入力したらI3に計算結果が表示される
H3が未入力もしくは文字などを入力した場合は表示なし

I3に
=IF(AND(H3<>"",ISNUMBER(H3)),E3-G3,"")
J3は
=E3-SUM(G3,I3)
とか

補足

出来ましたら、分析日も自動で出せたらと思っております。

回答No.4

kkkkkm

回答No.3の

J3はE3-G3
じゃなくて
J3はE3-G3-I3
でしょうか

補足

そのとおりです。

回答No.3

kkkkkm

> 1回目の分析は自動化できました

どのように自動化したのでしょうか
3行目で見て日付をF3はB3参照してG3は手入力J3はE3-G3くらいしか思いつかないのですが。
2回目の分析日と払い出しの量とはどこを参照すればいいのかわかりません。

補足

申し訳ありません、表が間違っていました。J列の残はすべて40gでした。
1回目の分析量は60g、２回目の分析量は40gと毎回変わりません。なので、いつもの作業は以下になります。

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

この作業を繰り返しています。なので、ﾌｨﾙﾀｰで絞る作業、払い出しの作業が無くなれば業務の効率化になると考えています。

最初はﾌｨﾙﾀｰで絞るのを効率化したいと考えｽﾗｲｻｰとか考えたのですが、ｱｲﾃﾑ数が多く断念しました。また、表の上に入力するだけで、ﾌｨﾙﾀｰをかける検索機能があればと思ったのですが、おそらくﾏｸﾛになると思い、分からず断念した次第です・・・

回答No.2

bardfish

Excelはなんでもできる万能など万能では無いと思ってください。

私ならExcelは使いません。
元々システムエンジニアですからExcelでそこまで面倒なことはせず、利用頻度を考えながらデータベースを使い、コードをゴリゴリと記述して作り上げます。
御希望の関数クラスモジュールとして使い回せるようにするので、セルに複雑な関数を記述することもありません。
その替わり入力用の画面を作ったり、必要な宣言を１文字単位で記述する必要があるので知らない人にとっては非常に敷居が高いと思いますが、一度覚えてしまうとExcelでそこまでする気にはならなくなります。
反面、使えるようになるまで時間と手間が必須になってしまいますが・・・

補足

ご回答ありがとうございます。申し訳ありません。内容が難しくて。マクロをくむといことでしょうか？マクロの経験がないので、どう進めればいいでしょうか？

回答No.1

柊 斗真（@Menhera_camo）

こんばんわ

IF関数
特定の条件を満たした場合に値を返します。
例えば、サンプルが最初に使用されてから次に使用するまでの日付が一定期間を超えた場合に2回目の使用日を返すように設定できます

LOOKUP関数
一覧から特定のデータを検索し、対応する値を返すことができます。例えば、最初に使用した日から次に使用するべき日を検索するのに使えます

DATEDIF関数
2つの日付の差を計算します。
これを使って、最初の分析から一定の日数が経過しているかを判断する基準として使用していきます

具体的な構築には現在の表のデータ構造や必要とする条件など、もう少し詳細な情報が必要なんですが、一例として以下のような形で関数を組み合わせてみました

=IF(DATEDIF(最初の使用日, 今日の日付, "days") >= 特定の日数, 今日の日付, "")

次に使用量ですね　
最初の使用後に残量が40gになるという点を基に、次のような関数を設定できます。

=IF(最初の使用日からの経過日数が特定の日数を超えている, 40, 0)

この関数を各サンプルの行に適用することで、そのサンプルに対する2回目の分析日と使用量を自動で払い出すことができると思います！

補足

早々にご回答ありがとうございます。
情報が足りていませんでした。申し訳ありません。
ｻﾝﾌﾟﾙについては定期的に分析するものではなく、不定期です。１年に１回分析するものや３日に１回分析するものといろいろで、ｻﾝﾌﾟﾙの種類も200種を超えます。ですので、11/15新規にﾘﾝｺﾞのｻﾝﾌﾟﾙを受け入れたら、前回受け入れたｻﾝﾌﾟﾙを11/15その日に払い出すようにしたいのです。いわゆる在庫受払表なので、払い出した日付をしっかり記録しなければなりません。すみません、説明下手で。わかりますでしょうか？