セルの色をつけたい

質問

windowsXP EXCELL2003です。

いつも御世話になります。
売上のシートがあり入金状態を容易に確認出来るようにセルの色で喚起したいです。
色々トライしてみましたができませんでした。
もし可能ならばご指導いただけませんか。

L列には毎日の売上の日付(mm/dd)が入ってます

この場合に
３ヶ月後
４ヶ月後
５ヶ月後
にセルの色を変えたいです。

例えば
可能ならば今日の日が９／１になると、

３ヶ月後の　６／１～６／３０
４ヶ月後の　５／１～５／３１
５ヶ月後の　４／１～４／３０
が日に関係なく一斉に変えられるようにできますか。

是非ご指導いただきたくよろしく御願いします。

回答No.11

telescope · ベストアンサー

MONTH関数だけですと、複数年にまたがった場合、同じ月にも適用されてしまいます。
また、１月に　MONTH（A1)-3　としても　-2　をかえしますので１０月は得られません。

売上げ日の列を選択して、「書式」－「条件付書式」で「数式が」に
=AND(L1>=DATE(YEAR(TODAY()),MONTH(TODAY())-5,1),L1<=DATE(YEAR(TODAY()),MONTH(TODAY())-4,0))
と入力して書式を決めます。
5ヶ月前の1日から月末までの日付を求めます。日付を０にすると前月の月末日が得られます。

同様にして、
=AND(L1>=DATE(YEAR(TODAY()),MONTH(TODAY())-4,1),L1<=DATE(YEAR(TODAY()),MONTH(TODAY())-3,0))
=AND(L1>=DATE(YEAR(TODAY()),MONTH(TODAY())-3,1),L1<=DATE(YEAR(TODAY()),MONTH(TODAY())-2,0))

あるいは、売り上げの日付と本日より5ヶ月前の日付のそれぞれの月の1日を比較して、
=DATE(YEAR(L1),MONTH(L1),1)=DATE(YEAR(TODAY()),MONTH(TODAY())-5,1)
=DATE(YEAR(L1),MONTH(L1),1)=DATE(YEAR(TODAY()),MONTH(TODAY())-4,1)
=DATE(YEAR(L1),MONTH(L1),1)=DATE(YEAR(TODAY()),MONTH(TODAY())-3,1)
この方が若干、式が短くなります。

売上げ日の列が質問文と補足で違っていますが、とりあえず最初の質問文の通り　L列にしておきました。
L列でなかったら、変更してください。

お礼

補足の中でもご報告しましたとおりです。

ご協力いただき本当に有難うございました。
今後ともご指導の程よろしく御願いします。

補足

ご協力いただき誠にありがとうございました。
結果をご報告します。

１　　下記の３点はうまくいきませんでした。
というのは同じ日の売上があったときはひとつだけが表示されません。
　　　※例えば　06/01 06/01 06/01 06/01 と４つの日付が記入されて　　　　いると３つのみ選択され１つは対象外になります。
=AND(L1>=DATE(YEAR(TODAY()),MONTH(TODAY())-5,1),L1<=DATE(YEAR(TODAY()),MONTH(TODAY())-4,0))

=AND(L1>=DATE(YEAR(TODAY()),MONTH(TODAY())-4,1),L1<=DATE(YEAR(TODAY()),MONTH(TODAY())-3,0))

=AND(L1>=DATE(YEAR(TODAY()),MONTH(TODAY())-3,1),L1<=DATE(YEAR(TODAY()),MONTH(TODAY())-2,0))
２　下記は私が思うようにばっちりとうまくいきました。
=DATE(YEAR(L1),MONTH(L1),1)=DATE(YEAR(TODAY()),MONTH(TODAY())-5,1)
=DATE(YEAR(L1),MONTH(L1),1)=DATE(YEAR(TODAY()),MONTH(TODAY())-4,1)
=DATE(YEAR(L1),MONTH(L1),1)=DATE(YEAR(TODAY()),MONTH(TODAY())-3,1)

ご協力いただき本当に有難うございました。
今後ともご指導の程よろしく御願いします。

回答No.12

tom04

何度もごめんなさい。
今回で最後になると思います。

No.11さんの回答を読ませてもらうと
確かにMONTHを使うと単なる数値になってしまいますので
年をまたぐ場合は不具合が生じます。

今までの私の回答は全て無視してください。

その代わりにといっては失礼ですが、
↓の画像のように作業列を使った方法を書かせてもらいます。

画像のA2セルに
=IF(AND(B2>=EOMONTH(TODAY(),-4)+1,B2<=EOMONTH(TODAY(),-3)),3,IF(AND(B2>=EOMONTH(TODAY(),-5)+1,B2<=EOMONTH(TODAY(),-4)),4,IF(AND(B2>=EOMONTH(TODAY(),-6)+1,B2<=EOMONTH(TODAY(),-5)),5,"")))

という数式を入れ、オートフィルで下にコピーします。
（EOMONTH関数を使っていますので、Excel2003以前のバージョンだと
メニュー　→　ツール　→　アドイン　→　「分析ツール」にチェックを入れておく必要があります。）

この数値を参照して、条件付書式の設定をするようにしています。

B列の日付のデータを範囲指定し、3ヶ月前の場合
条件付書式から　数式が　=A2=3
同様に4ヶ月前・5ヶ月前も設定してみてください。

尚、作業用の列が目障りであれば非表示にしてみてください。

ほんとぉ～～～！っに！何度も失礼しました。m(__)m

お礼

最後までお付き合いしていただき又ご協力いただき本当に有難うございました。
今後ともご指導の程よろしく御願いします。

補足

本当に有難うございます。
ご報告します。
５　４　３とかの数字ではうまくいきました。
条件付書式を設定すると　月初め日日がひとつ残りました。

回答No.10

tom04

こんにちは！
なんか、交換日記状態になってしまっていますが・・・

補足に
>注:画像では１日が３日が並んでいますが全部対象に(セルに色をつける)したいです。

とありますので、
対象の日付全てのセル（会社名・金額等）にも色を付けたいということですよね？

もしそうであれば
↓の画像（3ヶ月前だけしか設定していません）のように
元データ全てを範囲指定し、（画像ではA1セルからデータが入っています）
数式欄に
=MONTH($A1)=MONTH(TODAY())-3

のように「$」マークを付けてみてください。
そうすると↓のようになります。m(__)m

この投稿のマルチメディアは削除されているためご覧いただけません。

補足

何回も済みません。
言葉のやりとりって難しいですね。
できるだけと思っても。

前の画像で
４／１が３つ並んでいましたがこの日付を全部対象にしたいという事です。
行ではありません。

本当済みません。

回答No.9

tom04

こんにちは！
No.3・5・6・7です。

再度補足を読ませてもらいました。

結局↓の画像のような感じで良いのでしょうか？

もしそうであれば最初の回答のNo.3とまったく一緒になってしまいます。
（セル番地が若干変わっているくらい・・・）

もしかして、補足の4/1が続いている場合は
最初の4/1だけ色を付けないとか？

もしそうであれば、条件付書式だけで処理するより
他の方法を考えたほうが良いかもしれません。

以上、親の仇のように「これでもかっ！」というくらい顔を出してしまいました。

どうも失礼しました。m(__)m

この投稿のマルチメディアは削除されているためご覧いただけません。

補足

本当にご無理を言います。
例えば
下記のように可能ならばセルに色づけをしたい。
今日は９／２７とします。９月として日付は無視したい。
３ヶ月前は
　６／１～３１日
４ヶ月前は
　５／１～３１日
５ヶ月前は
　４／１～３１日
のそれぞれのセルに色をつけたい。
　注:画像では１日が３日が並んでいますが全部対象に(セルに色をつける)したいです。

※元になる売上の日付のセルは
例えば
　売上日　会社名　金額
　６／１　＊＊＊　１０００
　６／１　OOOOOO ２０００
６／１　VVVVVV　３０００
　６／２　ZZZZZZ １０００
というように日付が重なって、
売上日が１件と言うことはまずありえません。

回答No.8

KURUMITO

解答Ｎｏ1です。
遅くなってごめんなさい。
条件1での式は最初の式の通りで次のようにします。
=AND(A1>=DATE(YEAR(TODAY()),MONTH(TODAY())-3,DAY(TODAY())),A1<DATE(YEAR(TODAY()),MONTH(TODAY())-2,DAY(TODAY())))
条件2での式は次のようにしてください。
=AND(A1>=DATE(YEAR(TODAY()),MONTH(TODAY())-4,DAY(TODAY())),A1<DATE(YEAR(TODAY()),MONTH(TODAY())-3,DAY(TODAY())))
条件3では次の式にしてください。
=AND(A1>=DATE(YEAR(TODAY()),MONTH(TODAY())-5,DAY(TODAY())),A1<DATE(YEAR(TODAY()),MONTH(TODAY())-4,DAY(TODAY())))

お礼

いつも　Mr.KURIMOTO にご協力していただきます。
ご協力いただき本当に有難うございました。
今後ともご指導の程よろしく御願いします。

補足

ご指導いただいたままで書式を設定した結果を下記します。
　　※今現在使用している物で試しました。
条件１
緑の文字　６／２９～７／２７
希望　　　６／１～６／３０

条件２
青の文字　５／２７～６／２９
希望　　　５／１～５／３１

条件３
赤の文字　４／２７～５／２７
希望　　　４／１～４／３０

になりすこしずれているようでどう修正すればよろしいでしょうか。
ご協力下さい。
よろしく御願いします。

回答No.7

tom04

こんばんは！
No．3・5・6です。

ん～～～～！　当方の頭の中が混乱しています。

補足欄に
>５ヶ月前
04/01
05/01　　ここが 04/30になりませんか
４ヶ月前
05/01
06/01　　ここが 05/31になりませんか
３ヶ月前
06/01
07/01　　ここが 06/31になりませんか

とあるのですが、

これは5ヶ月前は4/1～5/1までが同じ色
4ヶ月前は5/1～6/1までが同じ色
3ヶ月前は6/1～7/1までが同じ色
と言う意味でしょうか？

もしそうであれば月初めと月末が重複してしまうような気がします。
それともうひとつ、もし　TODAY()　が1日の場合はどのように判断するのか・・・

私だけが意味を取り違えているのかもしれませんが、
ちょっと悩んでいます。

このまま回答してもまた、的外れのような気がしますので
その辺がはっきりした上で今一度回答させていただきたいと思います。

今回も回答になっていなくて、ごめんなさいね。m(__)m

補足

いえいえご協力に感謝しています。

改めてテストした結果をご報告を下記します。
入力　 結果
03/01
03/30
04/01　　　　　　色が付きません
04/01 04/01 赤　
04/01　04/01 赤
04/16 04/16 赤
04/20 04/20 赤
04/30 04/30 赤
05/01 05/01 赤　下の青にならないか
05/02 05/02 青
05/15　05/15 青
05/20 05/20 青
05/31 05/31 青
06/01 06/01 青　下の緑にならないか
06/04 06/04 緑
06/10 06/10 緑　　　　　　　　　
06/15 06/15 緑
06/30 06/30 緑
07/01 07/01 緑　下と同じ色が付かないように
07/02

本当にご迷惑掛けます。
ご協力いただけると幸です。
よろしく御願いします。

回答No.6

tom04

No．3・5です！
何度もすみませんねぇ～！

私も回答した責任上、こちらのSheetで同じように条件付書式を設定してみると

ちゃんと↓のように色がつきました。

どこに原因があるのでしょうかね？

二つ引っかかるところがあります。

一つ目は条件付書式の範囲指定はL1セルからになっているでしょうか？
もし、範囲指定がずれると色づけもずれますので
範囲指定した一番上のセル番地を数式に入力します。

もうひとつは日付がシリアル値になっているかどうか？です。

もし、シリアル値でなければ正確に表示されません。

今一度以上のことを確認していただいて、
再度挑戦してみてください。

回答まではいきませんが、
ごめんなさいね。m(__)m

この投稿のマルチメディアは削除されているためご覧いただけません。

補足

=MONTH(O1-1)=MONTH(TODAY())-3
を
=MONTH(O1)=MONTH(TODAY())-3
にしたら下記のようにできました。

５ヶ月前
04/01
05/01　　ここが 04/30になりませんか
４ヶ月前
05/01
06/01　　ここが 05/31になりませんか
３ヶ月前
06/01
07/01　　ここが 06/31になりませんか

いろいろと追加をして済みませんが何とかしていただくと非常にありがたいのですがもう一踏ん張りご協力下さい。
よろしく御願いします。

回答No.5

tom04

No．3です！
たびたびごめんなさい。

単純ではなかったのですね？
結局1日は前の月という判断で条件付書式を設定すれば良いと思いますので、

前回、私が回答した数式を少し変更してみてください。

L列だということなので、もう一度式を訂正して画像をアップしてみます。

L列全てを範囲指定し、
条件1の数式は
=MONTH(L1-1)=MONTH(TODAY())-3
というような感じで式を変えています。

今度は希望通りになることを望んでいます。

どうも何度も失礼しました。m(__)m

この投稿のマルチメディアは削除されているためご覧いただけません。

補足

何度もご協力いただいて申し訳ないのですが、
=MONTH(L1-1)=MONTH(TODAY())-3
で下記の部分が色づけされます。
07/01
07/02
07/03
07/30
08/04
08/06
08/07
08/30
09/01
恐れ入ります。
ご協力いただけませんか。
どうぞよろしく御願いします。

回答No.4

imogasi

＞日に関係なく一斉に変えられるようにできますか。
9/1でも、９/２３でも,6/1-6/30の間の日付の行は色を着けるなら、＃３のご回答のように、しないといけないと思いますが。
他の式では
例データA1:A6
9月1日
2009/5/5
2009/6/1
2009/6/21
2009/6/30
2009/7/2
A2:A6を範囲指定して、
書式ー条件付書式ー数式が
数式に　=AND(A1>=DATE(YEAR($A$1),MONTH($A$1)-3,1),A1<=DATE(YEAR($A$1),MONTH($A$1)-3,31))
書式でパターン色を淡色に設定
同じように第2条件にー３の部分をー４にした式を入れる。
ーー
他のご回答のように日の指定を、DAY(TODAY()にすると意味が変わるのでは。

お礼

いつも　Mr.IMOGASHIには御世話になります。
ご協力いただき本当に有難うございました。
今後ともご指導の程よろしく御願いします。

補足

=AND(A1>=DATE(YEAR($A$1),MONTH($A$1)-3,1),A1<=DATE(YEAR($A$1),MONTH($A$1)-3,31))

でテストしてみましたがセルの色になんの変化もみれません。
誠に恐れ入りますが再度ご指導いただけませんか。
よろしく御願いします。

回答No.3

tom04

こんばんは！
すでに回答は出ていますので、
参考程度で目を通してみてください。

質問では3～5ヶ月後　となっていますが、
3～5ヶ月前ではないでしょうか？

一応そういうことだとしての回答です。

色をつけたいセルを範囲指定します。

仮に本日（今月）より3ヶ月前（6月分全て）に色をつけたい場合は
↓の画像のように、

条件1の数式欄に
=MONTH(A1)=MONTH(TODAY())-3
という数式を入れています。
同様に、4ヶ月前なら最後の「3」を「4」に、5ヶ月前なら「5」に変更して
条件3まで数式を入力してみてください。

尚、画像の範囲指定はA1セルからの範囲指定の場合ですので
範囲指定の列・行がまったく別の位置であれば
範囲指定した一番左端の一番上側のセル番地を数式のA1の代わりに入力してみてください。
これでなんとか希望に近い形にならないでしょうか？

以上、参考になれば幸いです。m(__)m

この投稿のマルチメディアは削除されているためご覧いただけません。

補足

L列に無作為に入れた日付です。
　１　02/01
　２　02/02
　３　02/27
　４　03/01
　５　03/02
　６　03/04
　７　03/30
　８　04/01
　９　04/02
１０　04/03
１１　04/04
１２　04/30
１３　05/01
１４　05/02
１５　05/04
１６　05/30
１７　06/01
１８　06/02
１９　06/03
２０　07/01
２１　07/02
２２　07/03
２３　07/30
２４　08/04
２５　08/06
２６　08/07
２７　08/30
２８　09/01
２９　09/02
３０　09/03
３１　09/30

１　５ヶ月
　04/01含まず05/01が含まれる。
　04/02 04/03 04/04 04/30 05/01
２　４ヶ月 05/01含まれず　06/01含まれる。
05/02 05/04 05/30 06/01
３　３ヶ月 06/01含まず　07/01ふくまれる。
06/02 06/03 07/01
　どうしたらいいでしょうかよろしく御願いします。

回答No.2

有田 賢治（@ARIKEN43）

dorasukeさん 今日は！
既に回答者のKURUMITOさんより詳細な回答がされていますが。
3ヶ月前
=DATE(YEAR(TODAY()),MONTH(TODAY())-3,DAY(TODAY()))>G1
4ヶ月前
=DATE(YEAR(TODAY()),MONTH(TODAY())-4,DAY(TODAY()))>G1
5ヶ月前
=DATE(YEAR(TODAY()),MONTH(TODAY())-5,DAY(TODAY()))>G1
後は書式の「パターン」タブで3～5ヶ月前の色を選択設定して「ＯＫ」ボタンをクリックします。
■“売上目標達成”のセルだけ自動で色を変える(Excel2003)↓
http://itpro.nikkeibp.co.jp/article/COLUMN/20060825/246459/?ST=slfkng&P=1
内容：対象となる範囲を選択→「書式」→「条件付き書式」や「条件付き書式」で条件に合うセルだけ書式を自動で変更について解説されています。(２ページ目は登録「無料」しないと見られない？)

お礼

ご協力いただき本当に有難うございました。
今後ともご指導の程よろしく御願いします。

補足

選択して条件付書式を入れましたが
すべてのセルに色が付きます。
どうすれはいいでしょうかよろしく御願いします。