エクセルの日付で半年毎のアラームを出したい

質問

エクセル2010でA列に日付け、B列に製品名、C列にロットNo等を入力した表が有るのですが、同じロットのデータを入力しようとしたら、最初のデータの入力日付けから6ヶ月以上経過していたらアラーム（赤字）が出るようにしたいのですが。
次はその次にまた6ヶ月以上経過していたらアラームが出るようにしたいのですが。

目的は半年以内は前回の数値を使用し、前回測定時から半年以上経過していたら再試験を実施しその結果を入力するようにしたいのです。
再測定した場合は、そのロットのデータ行のどこかに（再１）（再２）とかの印をつけることは可能です。

わがままですが、当方マクロは不得手なので出来れば関数で、だめならマクロでも頑張る所存ですので宜しくお願いします。

回答No.3

mt2015 · ベストアンサー

添付の画像を見てください。
同ロットNoの前回検査日付が6カ月以上前の場合、D列に「★」を表示します。

D2セルには★を入れておきます(初回は必ず検査ですよね？）。
D3セルに以下の式を入れて下にコピーしてください
=IF(DATEDIF(SUMPRODUCT(MAX(A$2:A2*(C$2:C2=C3)*(D$2:D2="★"))),A3,"M")>=6,"★","")

また、D列の2行目以降の文字色を赤に設定しておきます。

お礼

やってみました。
うまく行きました。
列と行を適当に変更して何度かトライ＆エラーが有りましたが、完全に思った通りに出来ます。
今日の夜にもう少し練習して明日以降本番のシートに細工します。

感謝！　感激！！　雨あられ！！！

補足

早々のご回答ありがとうございます。
素晴らしいです！！
早速、試してみたいと思いますが、当方がやるとなかなかうまく行かないことも多いのですが、頑張ってみます。

欲を言えば（本当に欲ですが）出来れば列の挿入は避けたいのです。
なぜなら、表が横に広く、ロット入力時には★が見ません。
ならばAーE列の間に列を挿入、となると、この表からワードに自動転記されるようにリンクが（苦労して）貼ってあって、これを貼り直す必要があるので、出来ればロット入力時に、日付けかロットのセルに何らかのアラームが出るように出来れば最高なのですが。

とにかく、出来ることが分かったので、先ずはA列を挿入して、リンクを貼り直すことを検討します。（良く考えたら、数枚のワードの表へのリンクを貼り直すだけですので）
更に良く考えたら、★が付いたらロットNoが赤字になるようにすれば良いだけですね。　解決しました！！！

ありがとうございました。感謝！！

回答No.7

tsubu-yuki

なるほど、補足ありがとうございます。
ある程度把握しました。
補足を頂いてしてしまったので、少し考えてみました。
何やらワードで云々という追加もあるようですが、
それは見なかったことにさせていただいて。
※マクロを使わずにやるなら差し込み印刷とかですね。
　それはそれ、別途質問を立てた方が良いかもです。（コッソリ）

要件として、
・製品名が同じものがある
・前回直近の出現（測定）が半年（６カ月）以前
・表は日付順に昇順で並んでいる（必須）
に重きを置いて、式を組んでみました（図もご覧ください）。

Ａ列に日付、Ｂ列に製品名。１行目はタイトル行にしています。
２行目からデータが続くとして・・
　　Ｃ３セル：=SUMPRODUCT(MAX(($B$2:B2=B3)*$A$2:A2))
としてやり、必要分、コピーしました。
この式により、過去において
「直近の（最大の）同じ製品が出現している日」
を返してきます。
初出（２／１の製品ｂのような）については「０」を返してきますから、
ここ（Ｃ列）にはセルの書式設定で
　　ユーザー定義：yyyy/mm/dd;;
を設定してあります。

同時に、Ａ３セルから行方向に条件付き書式
　　式：=AND(EDATE(A2,-6)>C2,C2>0)
　　書式：赤塗りつぶし
を設定しました。
これで
　・半年以前に出現
　・初出ではない
を同時に満たす場合に赤塗りされる仕組みです。
※条件付き書式の式は
　　式：=AND(EDATE(A3,-6)>SUMPRODUCT(MAX(($B$2:B2=B3)*$A$2:A2)),SUMPRODUCT(MAX(($B$2:B2=B3)*$A$2:A2))>0)
　とまとめて指定してやっても可能です。
　この場合は図中のＣ列が不要です。
が、まとめないでＣ列がある方が逆にすっきりするかもしれません。
この辺はお好みによります。

Ｃ２セル（というか２行目）についてはエラー処理を面倒くさがった私の手抜きです。
Ｃ４セル・Ｃ７セルも得る意味では手抜きですが、お気になさらず。

なお、
> 再測定した場合は、そのロットのデータ行のどこかに（再１）（再２）とかの印をつける
サラリとおっしゃっていますが(笑)。
これをやろうとしたら多分、ＣＯＵＮＴＩＦ関数ですね。
そんなおまけがＨ列です。
ここにはＣＯＵＮＴＩＦ関数を使って、一つ前の行までをカウント。
セルの書式設定で表示を決めています。「"（再"0"）";;("初")」です。

何かの参考にどうぞ。

回答No.6

imogasi

#5の追加参考事項。
エクセル２０１６ならＭＡＸＩＦＳ関数が使えるので、「ロット」指定して最大値が求めやすい。
その他ならロットaの最最近日は
データ例
ロット記号　日付
a 2017/2/5
b 2017/2/6
a 2017/2/7
b 2017/2/8
c 2017/2/9
c 2017/2/10
a 2017/2/11
a 2017/2/12
b 2017/2/13
b 2017/2/14
c 2017/2/15
b 2017/2/16
b 2017/2/17
b 2017/2/18
として
=SUMPRODUCT(MAX((A1:A15="a")*B1:B15))
のように関数を入れると　2017/2/12　（aのロットの最新日付）
のようになる。こういうのが使えないか考えてみるとよい。

回答No.5

imogasi

アドバイス。
(1)日付に関することなので、エクセルの標準的な日付入力すると、日付シリアル値という、整数の値になることを」知ってますか。初耳ならば、Ｇｏｏｇｌｅででも「エクセル　日付シリアル値」で照会して勉強のこと。
だから、日付の前後は、日付に対応した整数値（日付シリアル値）の２整数の大小で判別できるのだ。
ーー
（２）入力した値のチェックは、「入力規則」というエクセルの有名な仕組みでチェックできる場合が多い。本件も使えないか勉強のこと。このエクセルの言葉を知ってましたか。
（３）２つの比較におけるＡ：ＢのＢは
　　　現在日か
　　　設定日（特定の日）か（（業界・業務）慣習などで決まるような）
　　　基準日（データが作成基準日など）　か
などを質問にはっきり質問に書くべきだ
（４）それにA:BのAは
　　　　ロット番号単位で考えるのだろう。
　　　　そしてその中で一番新しい最近の（または古いか？）日付に６か月経過した日付を　　　入力すると警告するのだろうね。
　　６か月は１８０日でよいなら簡単だか、月表示で６か月で何考えたいんか？
　　月以下は応当日（数字数で同じひでよいのか（明確に質問以書くこと。
　　　　（A)日付＋１８０　実際に１日単位で、１８０日間の後の日付。
　　　　（B)月＋６の応当日（言い換えると同日）　　エクセル関数で、月に足し算すると１２か月を超える場合だと、年を繰り上げてくれることを。知ってますか？
これらの日付を、関数で質問者が書けるかという問題になる。
ーー
（５）　同じロットで最少の値を求める関数を勉強のこと
　　これが今回質問麻最大のテーマかな。
　（６）入力規則を使えるとして、関数を使っての設定操作は判りますか。
質問ぶりから、回答まる写しにならざるを得ないのかな。

回答No.4

tsubu-yuki

回答ではないので申し訳ないと思いつつ・・・

最初のデータが１年前、直近のデータが７カ月前・・
（表の中腹辺りで比較対象が出てくる）
というケースは存在するのでしょうか。

それとも、前お二人の決め打ち通り、
あくまでも最新データが一番上にある、
あるいはあくまでも比較対象は「最初の」データ・・
という考え方の下に動けばいいのか・・
でも、それだと「ロット番号ごとに」表を作れば良いのだから・・・
それをしないということは・・・

更に言うと、同製品・異ロットもあるんだろうなぁ・・・

とか考え出して、回答に悩んでおります。

補足

やはり質問が下手なようで皆様に無駄なお手数をお掛けすることになっているようで恐縮です。

具体的には２つの品名のデータが入ります。
ロットの最初は検査して、日付け、品名、ロット、（特性）・・・・
というように行方向に入力していきます。
製品が出荷されるたびに、試験結果表を発行する必要があるのですが、半年以内なら半年前（以内）の検査結果を報告書に記載（ワードに自動転記）。
もし、検査後半年以上経過していたら、改めて検査してその結果を報告する、というようにしたいのです。

ということで、異なる製品で同一ロットも可能性としては有りますが（レアケースで無視でもOK）、一番の目的は同一製品で同一ロットの成績表を作成しようとした時に、「検査結果の有効期間切れ」というアラームが出るようにしたいのです。

書いていてもなかなか全て説明できたかどうか自身が有りませんが、よろしくお願いします。

最悪、同一ロットが入力されたら、その直近の同一ロットがハイライトされるだけでも良いのですが。　ただし、それ（その時の入力ロット）以外のハイライトが消える＝他のロットの重複と区別できるようにする必要が有ります。
現在は、書式設定でロットの「重複」を見つけるようにはしてあるのですが、データが増えてきて、着色セルだらけで、今の入力ロットと直近の同一のロットの行（日付）を探すのが面倒になってきているので改善策を探しています。
よろしくお願いします。

回答No.2

aokii

A2セルに記載されている日付から6ヶ月以上経過していたらアラーム（6ヶ月以上経過しています。）が出るようにするには以下の式をD2セルに入力して下のセルにドラッグコピーしてみてください。なお、E２セルに何か文字を記入したら、アラーム（赤字）は消えます。
=IF(AND(TODAY()>EDATE(A2,6),E2=""),"6ヶ月以上経過しています。","")

また、6ヶ月以上経過していたらA２セルの文字を赤くするのでしたら、A2セルの条件付き書式として以下の数式を記入して、フォントを赤に設定してみて下さい。
=AND(TODAY()>EDATE(A2,6),E2="")

補足

早々のご回答ありがとうございます。
説明が足りなくてご回答の内容が少しやりたいことと違っています。

＞同じロットのデータを入力しようとしたら、最初のデータの入力日付けから6ヶ月以上経過していたらアラーム（赤字）が出るようにしたい

先ず、入力されたロットNoから、過去に入力されているかを参照し、入力されていれば、その結果が入力された日付から6ヶ月以上が経過していたらアラームが出るようにしたいのですが。

更にまたそれと同じロットNoが再度入力された時には、直近の入力日から起算して6ヶ月以上かどうかでアラームが出るようにしたいのです。

つまり日々入力される違うロットのデータで、過去に同じロットが有るかないか、あれば直近データーの入力日から6ヶ月以上かどうか、を判断したいのですが。

説明不足でした。

回答No.1

ushi2015

こんにちは
1行目には項目名が入っているとして、セルA2からC列の必要な範囲を選択して、
条件付き書式で、新しいルールを作成し、数式に
=COUNTIFS($A$1:$A1,"<"&EDATE($A2,-6),$C$1:$C1,$C2)>0
として、書式でフォント色を赤にしてみて下さい。

補足

早々のご回答ありがとうございます。
説明が足りなくてご回答の内容が少しやりたいことと違っています。

＞同じロットのデータを入力しようとしたら、最初のデータの入力日付けから6ヶ月以上経過していたらアラーム（赤字）が出るようにしたい

先ず、入力されたロットNoから、過去に入力されているかを参照し、入力されていれば、その結果が入力された日付から6ヶ月以上が経過していたらアラームが出るようにしたいのですが。

更にまたそれと同じロットNoが再度入力された時には、直近の入力日から起算して6ヶ月以上かどうかでアラームが出るようにしたいのです。

つまり日々入力される違うロットのデータで、過去に同じロットが有るかないか、あれば直近データーの入力日から6ヶ月以上かどうか、を判断したいのですが。