エクセルで1年前の日付になったらセルの塗りつぶし

エクセルで、CSVファイルで納品される商品があります。

納品されたときに有効期限が付いています。（有効期限が2年）


1年1ヶ月前になったらその有効期限のセルを塗りつぶす方法というのはあるのでしょうか。

どなたかお分かりになる方いらっしゃいましたら、回答お願い致します。

No.3 ベストアンサー 回答者： kagakusuki 回答日時： 2011/08/15 23:13

　回答番号ANo.2です。

>=DATEDIF(TODAY(),A1-1,"M")<13

>の数式はどうゆう指示になりますか？

　DATEDIF関数は、

DATEDIF（開始日,終了日,単位）

と言う形式で記述され、1つ目の項目に入力した開始日から、2つ目の項目に入力した終了日にかけて経過した期間を、3つ目の項目で指定した単位で換算した数値を算出する関数です。

【参考URL】
　インストラクターのネタ帳 > Excel エクセルの使い方-関数/計算式-日付・時間 > 生年月日から年齢を計算－DATEDIF関数
　　http://www.relief.jp/itnote/archives/000423.php

　エクセルへの近道 > 関数辞典　→ 名前順で表示 > ＤＡＴＥＤＩＦ
　　http://maglog.jp/excel/Article223046.html

　例えば、

DATEDIF（"2009/8/15","2011/7/16","M")

という関数では、2009年8月15日から2011年7月16日までの間に経過する期間は、1年11ヶ月と1日ですから、何カ月が経過したかで数えれば、23ヶ月であり、3つ目の項目にある"M"は単位が「月（month）」である事を表しますから、算出される値は23となります。

　TODAY()関数はパソコン内の時計に設定されている日時のデータを基にして、現在の日付をシリアル値で表したデータを算出する関数です。
　本日は2011年8月15日ですから、

TODAY()

の部分は、今日は2011/8/15を表し、日付が変わって2011年8月16日になれば、2011/8/16を表す事になります。

A1-1

の部分は、A1セルに入力されている数値から、1を差し引いた値を算出する関数です。
　Excelでは、日時のデータを、シリアル値というデータ形式で表します。
　シリアル値とは、1900年1月1日を第1日目として、数値の1として扱い、1900年1月2日を第2日目として、数値の2として扱い、1901年1月1日を第367日目として、数値の367として扱い、という具合に、1899年12月31日から経過した日数を、数値とし扱っています。（現在使われているグレゴリオ暦では、1900年は閏年ではないのですが、Excelではそこまでは考慮されていません）
　例えば、A1セルに2012年9月15日と言う意味の

2012/9/15

と入力されている場合、表示上は「2012/9/15」と表されますが、パソコンの内部ではシリアル値である41167と言う数値のデータとして扱われます。
　そして、

A1-1

の部分では、2012年9月15日から1日を差し引いた、2012年9月14日として扱われます。
　2012年9月14日は、本日の日付である2011年8月15日から数えて、1年と30日であり、13ヶ月には1日足りませんから、

DATEDIF(TODAY(),A1-1,"M")

の部分は12（ヶ月）と言う値となります。
　12は13よりも小さな値ですから、

DATEDIF(TODAY(),A1-1,"M")<13

という条件に合致している事になりますから、条件付き書式で設定した書式で表示が行われる事になります。

　又、A1セルに2012年9月16日と言う意味の

2012/9/16

が入力されている場合には、

A1-1

の部分は、2012年9月16日から1日を差し引いた、2012年9月15日として扱われます。
　2012年9月15日は、本日の日付である2011年8月15日から数えて、丁度1年と1ヶ月ですから、

DATEDIF(TODAY(),A1-1,"M")

の部分は13と言う値となり、13は13に等しい値であり、小さな値ではありませんから、

DATEDIF(TODAY(),A1-1,"M")<13

という条件に合致していない事になりますから、条件付き書式で設定した書式ではなく、セルの書式設定で設定されている書式で、表示が行われる事になります。

この回答へのお礼

ありがとうございました。非常に分かりやすくありがとうございます。

お礼日時：2011/08/17 18:10

No.5 回答者： KURUMITO 回答日時： 2011/08/16 06:44

回答No1,4です。

回答No4の式はD1セルになっていました。D2セルに条件付き書式を設定するのですからD2セルを選択したのちに条件付きの書式設定では次の式を入力します。

=AND(D2>=TODAY(),D2<=DATE(YEAR(TODAY())+1,MONTH(TODAY()),DAY(TODAY())))

この回答へのお礼

ご丁寧にありがとうございます。
無事に書式設定できました。

お礼日時：2011/08/17 18:09

No.4 回答者： KURUMITO 回答日時： 2011/08/16 06:38

回答No1です。

最初のご質問は1年1ヵ月前になったらとなっていますが補足では1年前になったら今日から1年先の日付の場合にセルを塗りつぶすのですね。
D2セルについてそのような条件を付けるのでしたらD2セルを選択したのちに「条件付き書式」で数式の窓には次の式を入力し、その後に書式で「塗りつぶし」のタブで色を指定すればよいでしょう。

=AND(D1>=TODAY(),D1<=DATE(YEAR(TODAY())+1,MONTH(TODAY()),DAY(TODAY())))

No.2 回答者： kagakusuki 回答日時： 2011/08/15 16:46

　Excelの条件付き書式を利用すると良いと思います。

　条件付き書式の設定方法は、使用するExcelのバージョンが、Excel2007以降のバージョンの場合と、それよりも前のバージョンの場合では、操作方法が異なります。

　今仮に、有効期限が入力されているセルの範囲が、A1～A9の範囲であるものとします。

【Excel2007以降のバージョンの場合】
A1セルを選択
　　↓
[ホーム]タグをクリック
　　↓
[条件付き書式] をクリック
　　↓
現れた選択肢の中にある[新しいルール] をクリック
　　↓
現れた「新しい書式ルール」ウィンドウの[数式を使用して、書式設定するセルを決定] をクリック
　　↓
「次の数式を満たす場合に値を書式設定」と記されている欄に次の数式を入力

=DATEDIF(TODAY(),A1-1,"M")<13

　　↓
「新しい書式ルール」ウィンドウの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ウィンドウの[塗りつぶし] タグをクリック
　　↓
好きな色の四角形をクリック
　　↓
「セルの書式設定」ウィンドウの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ウィンドウの[OK]ボタンをクリック
　　↓
選択しているセルを変えずに、再度[条件付き書式] をクリック
　　↓
現れた選択肢の中にある[ルールの管理] をクリック
　　↓
現れた「条件付き書式ルールの管理」ウィンドウの「書式ルールの表示」欄が[現在の選択範囲]となっていることを確認
　　↓
「ルール(表示順で適用)」欄が「数式: =DATEDIF...」となっている行の「適用先」欄の内容を

=$A$1

から

=$A$1:$A$9

に変更する
　　↓
「条件付き書式ルールの管理」ウィンドウの[OK]ボタンをクリック


【Excel2007よりも前のバージョンの場合】
A1セルを選択
　　↓
メニューの[書式]をクリック
　　↓
現れた選択肢の中にある[条件付き書式]をクリック
　　↓
現れた「条件付き書式の設定」ウィンドウの左端の欄をクリック
　　↓
現れた選択肢の中にある「数式が」をクリック
　　↓
「条件付き書式の設定」ウィンドウの左から2番目の欄に次の数式を入力

=DATEDIF(TODAY(),A1-1,"M")<13

　　↓
「条件付き書式の設定」ウィンドウの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ウィンドウの[パターン]タグをクリック
　　↓
好きな色の四角形をクリック
　　↓
「セルの書式設定」ウィンドウの[OK]ボタンをクリック
　　↓
「条件付き書式の設定」ウィンドウの[OK]ボタンをクリック
　　↓
A1セルにカーソルを合わせて、マウスを右クリック
　　↓
現れた選択肢の中にある[コピー]をクリック
　　↓
A1～A9の範囲を範囲選択
　　↓
選択範囲を示す黒い太枠の内側にカーソルを合わせて、マウスを右クリック
　　↓
現れた選択肢の中にある[形式を選択して貼り付け]をクリック
　　↓
現れた「形式を選択して貼り付け」ウィンドウの中にある「書式」と記されている箇所をクリックして、チェックを入れる
　　↓
「形式を選択して貼り付け」ウィンドウのの[OK]ボタンをクリック

この回答へのお礼

詳しくありがとうございます。

=DATEDIF(TODAY(),A1-1,"M")<13

の数式はどうゆう指示になりますか？

希望としては、有効期限の１年前になったら赤くしたいのですが・・・
今日をたとえにするならば、2012年8月15日～2011年8月15日のものを赤くする数式を作りたいです。
明日になれば、2012年8月16日から2011年8月16日のものを赤くしたいです。

宜しくお願い致します。

お礼日時：2011/08/15 17:23

No.1 回答者： KURUMITO 回答日時： 2011/08/15 16:43

例えばB1セルから下方にデータっがるとしたらB1セルから下方のセルを範囲として選択したのちに「条件付き書式」で「新しいルール」から「数式を使用して…」を選択し、数式の窓には次の式を入力します。

=AND(DATEDIF(TODAY(),B1,"Y")<=1,DATEDIF(TODAY(),B1,"YM")<1)

その後に「書式」から「塗りつぶし」のタブで色を選択してOKします。

この回答へのお礼

ご回答ありがとうございます。


=AND(DATEDIF(TODAY(),B1,"Y")<=1,DATEDIF(TODAY(),B1,"YM")<1)

の数式を入れてみました。
ちなみにD2セルに入力をしたいので、
=AND(DATEDIF(TODAY(),D2,"Y")<=1,DATEDIF(TODAY(),D2,"YM")<1)


と入れてみましたが、赤くなりません・・・。

希望としてはD2のセルに入力してある日付の1年前を経過してしまったセルを赤くしたいです。

今日でたとえれば、2012/8/15～2011/8/15の範囲のものを全て赤くしたいです。
明日になったら、2012/8/16～2011/8/16の範囲のものを赤くする計算式がベストです。



宜しくお願い致します。

お礼日時：2011/08/15 17:34