こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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

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


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

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

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

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

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

使用ソフト:
 Excel2007



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

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

投稿日時 - 2010-07-29 23:25:21

QNo.6073868

暇なときに回答ください

質問者が選んだベストアンサー

こんにちは!
参考にならなかったらごめんなさい。
↓の画像で説明させていただきます。
当方使用の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

投稿日時 - 2010-07-30 09:55:36

補足

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

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

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


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

投稿日時 - 2010-08-03 22:32:16

お礼

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

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

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

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

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



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

投稿日時 - 2010-08-02 21:35:52

ANo.3

このQ&Aは役に立ちましたか?

1人が「このQ&Aが役に立った」と投票しています

回答(4)

ANo.4

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

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

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

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

投稿日時 - 2010-08-02 22:16:21

お礼

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

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

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

投稿日時 - 2010-08-03 22:11:33

ANo.2

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

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

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

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

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

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

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

=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())

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

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

投稿日時 - 2010-07-30 09:40:55

お礼

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

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

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

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

投稿日時 - 2010-08-03 21:56:23

ANo.1

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

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」
 書式は青

とすればよいです。

投稿日時 - 2010-07-30 09:40:32

お礼

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

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

投稿日時 - 2010-08-03 22:21:59

あなたにオススメの質問