【Excel2007：条件付き書式】残件管理用カレンダーを作りたい

質問

【Excel2007：条件付き書式】残件管理用カレンダーを作りたい

業務では扱うことはあるものの、未だ全てを使いこなせているとは言い難いExcel、
かゆい所に手が届かず困っております。

※添付の画像を見ながら読んでいただけたらと思います。

作ろうとしている物の仕様：
　手持ちの残件の進捗を管理するためのカレンダーを作成したい。
　年、月は手入力で変更。連動して、8,9行目の日付、曜日が切り替わる
　(万年カレンダーみたいに使う)。
　土日祝は自動でセルの色を変える。（土：青　日祝：赤　など…）
　今日の日付のセルも色を変える。（例えば緑に）

つまってる点：
　主に条件付き書式でセルの色を変えるところで詰まっております。

　(1)「10行目以下のセルの色を変える」
　　セルの色を変える条件となる、
　　8，9行目以外のセルも連動して
　　色を変えるというのがうまくできません。

　(2)「関数の結果を条件にセルの色を変える」
　　9行目(曜日)の「関数の出力結果を条件に」色を変える、
　　8行目(日付)の「関数の出力結果を条件に」色を変える、
　　ということがうまくできません。

使用ソフト：
　Excel2007

今回、仕事の進捗管理ということを例に質問させていただきましたが、
何かとカレンダーを用意したい場面が多く、
解決できると、色々な場面で応用が効きそうです。

ご回答、よろしくお願いします。

回答No.3

tom04 · ベストアンサー

こんにちは！
参考にならなかったらごめんなさい。
↓の画像で説明させていただきます。
当方使用のExcel2003の場合です。

せっかくある程度お考えのようなので余計なお世話になるかもしれませんが。
E5・E6セルに年と月の数値を入力するだけで大の月・小の月にも対応するようにしてみました。

Sheet1のE8セルに
=IF(COUNTBLANK($E$5:$E$6),"",IF(MONTH(DATE($E$5,$E$6,COLUMN(A1)))=$E$6,DATE($E$5,$E$6,COLUMN(A1)),""))
（セルの書式設定から表示形式はユーザー定義で　d　としておきます。

E9セルは
=IF(E8="","",E8)
（セルの表示形式はユーザー定義から　aaa　）
E8・E9セルを範囲指定し、月末の31日までオートフィルで右にコピーします。
これで小の月も31は表示しないと思います。

次に条件付き書式の設定ですが、
まず、Sheet2に祝日データを設定しておきます。
画像では今年と来年分を表示してあります。そして、条件付き書式で別Sheetを参照できなかったと思いますので、
Sheet2のB2～C24セルを範囲指定　→　仮に　「休日」と名前定義したとします。
名前定義の方法は範囲指定後、直接名前ボックスに　休日　と入力する方法が一番簡単かもしれません。

そして、色を付けたいセル（8行目も含む）すべてを範囲指定し、
条件付書式　→　数式が（Excel2007の場合は手元にないのでちょっと判りません）　→　数式欄に
条件1　→　=OR(WEEKDAY(E$8)=1,COUNTIF(祝日,E$8))
として塗りつぶしから「赤」を設定

条件2　→　同様に数式欄に
=WEEKDAY(E$8)=7
として、薄い「青」を選択しています。

今日の日付の場合も同様に条件3の数式欄に
=E$8=TODAY()
として好みの色を選択し、OK

これで画像のような感じになります。

以上、長々と書きましたが
参考になれば幸いです。m(__)m

お礼

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

大の月・小の月への対応はとてもスマートでいいですね。

祝日はやはりテーブルを別で持たないとだめですか。
どこか自動で拾ってきてくれるような仕組みが
Excelにあればなぁと、都合のいい淡い期待を持ったりしておりましたが、
そこまで都合よくはいきませんね＾＾；

しかし、別のカレンダーで、「非営業日を赤く…」とか
そんなことができたらいいなと考えていまして、
そちらには応用が効きそうです。
これはこれでありがたい。
（非営業日とか個人的な有給とかになると、
テーブル作らざるを得ないでしょうから。）

ところで、名前定義に「休日」「祝日」と二種類表記がありますが、
これは統一しないと動かないですよね？

<<< 他のご回答くださったみなさんへ >>>
　お返事遅くなってすいません。
　じっくり時間をかけて読ませていただきたいので、
　もう少し時間がかかりそうです。
　なにとぞご容赦ください。

補足

<<<ベストアンサーについて>>>

No.2とNo.3の回答は内容がよく似ているのですが、
祝日テーブル作成の個所を図入りで丁寧に書いてくださっていたので、
こちらをベストアンサーとさせていただきたいと思います。

初見の方は、No.1の方の回答が、
最低限のところが抑えられていて、
短い時間で内容を理解するにはよいかと思います。

みなさん、ご回答ありがとうございました。

回答No.4

tom04

No.3です！
再びお邪魔します。

お礼欄のご指摘ですが・・・
まさしく仰るとおりで単純なる私の思い違いです。
「休日」「祝日」は名前定義したものを条件付書式の数式欄に入れなければ
色が表示されません。
前回の方法ですと、名前定義を「祝日」としなければなりませんね。
どうも的確なご指摘をありがとうございました。

それから余計なお世話になりかもしれませんが、
同様の方法で、個人の休日等も色を付けることが可能になります。
当方使用のExcel2003では条件付書式は3条件までしか設定できませんが、
Excel2007をお使いということなのでかなりの条件数が設定できるはずです。
当然のことながら、個人の休日とうは祝日とは別の表にしておかなければなりませんけどね。

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

お礼

再度ご回答くださり、ありがとうございます。

個人のスケジュールにも
その方法でいけますね。

みなさんから教えていただいた方法で、
実際に使用している残件管理、家計簿、学習実績記録などなどに
これから反映していきたいと思います。

回答No.2

KURUMITO

日付の表示や曜日の表示について、次のようにすることをお勧めします。
Ｅ8セルには次の式を入力して右横方向にＡＩ8セルまでオートフィルドラッグします。

=IF(MONTH(DATE($E$5,$E$6,COLUMN(A1)))<>$E$6,"",DATE($E$5,$E$6,COLUMN(A1)))

これでＥ8セルには2010/7/1のように表示されますがＥ８セルからＡＩ8セルまでを選択してから「セルの書式設定」から「表示形式」で「ユーザー定義」の　d のように入力してＯＫすればよいでしょう。これで日にちのみが表示されますし、月の最終日も自動的に決まります。

曜日の表示では次の式をＥ9セルに入力してＡＩ9セルまでオートフィルドラッグします。

=IF(E8="","",TEXT(E8,"aaa"))

ところで土曜日や日曜日に色を付けるのは良いのですが祝日などにも色を付けるわけですね。そのためには祝日について例えばシート2のＡ1セルからＡ30セルに間にその日付を入力しておきます。

その後にＥ8セルからＡＩ列の最終行までのセルを範囲として選択します。
その後に「ホーム」タブから「条件付き書式」をクリックし、「新しいルール」を選択します。
「数式を使用して書式設定するセルを決定」を選択してから下の窓には次の式を入力します。

=AND(E$8<>"",OR(WEEKDAY(E$8)=1,COUNTIF(INDIRECT("Sheet2!$A$1:$A$30"),E$8)>0))

書式で色を設定すれば日曜日と祝日にあたる日ではその列に色が付くことになります。

土曜う日に色を付ける場合でしたら、新しく「条件付き書式」を設定すればよいでしょう。その時の式は次のようにします。

=AND(E$8<>"",WEEKDAY(E$8)=7)

また、当日に色を別に付けるのでしたら次の式を入力します。

=AND(E$8<>"",E$8=TODAY())

条件付き書式の設定では前にいろいろ試験していますとそれらの条件を消さないままに新しい条件を設定しても前の条件が生きていますので注意が必要です。一度すべての条件設定を消してからここでの操作を行ってください。

また、範囲の指定ではＥ8セルが起点となっています。Ｅ8セルがアクティブセルの状態となっています。そこで上記のような式が有効になるのです。入力した式が選択した範囲のすべてのセルにオートフィルドラッグコピーされると考えてください。それぞれのセルで条件が満たされなければ色を付けるなどのことは行われないのです。

お礼

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

どうも自分、条件付き書式の使い方が
きちんと整理して理解できていなかった気がします。

皆さんの回答例と、KURUMITOさんの説明で
ちょっと整理できたような気がします。

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

回答No.1

nattocurry

日付や曜日の式を変えましょう。

E8には、
=DATE($E$5,$E$6,1)
F8には
=E8+1
G8以右にはF8をコピペ。

E9には、
=E8
F9以右にはE9をコピペ。

日付のセルは、書式の表示形式をユーザー定義でd
曜日のセルは、書式の表示形式をユーザー定義でaaa

そして、E8を左上隅とするカレンダー全体を範囲選択し、E8がアクティブ(E8だけが他の選択範囲と違って白く反転している)になっているのを確認したら、

条件付き書式で

条件1
　「数式が」「=E$9=TODAY()」
　書式は緑
条件2
　「数式が」「=WEEKDAY(E$9)=1」
　書式は赤
条件3
　「数式が」「=WEEKDAY(E$9)=7」
　書式は青

とすればよいです。

お礼

TEXT関数で無理やりこねくり回して日付を組み立てるのはよくなかったですね。
DATE関数の存在までたどり着けなかったのは勉強不足でした。

それはさておき、
シンプルでわかりやすいご回答ありがとうございます。
スッキリまとまっていて、とてもわかりやすいですね。