【Excel2007:条件付き書式】残件管理用カレンダーを作りたい
業務では扱うことはあるものの、未だ全てを使いこなせているとは言い難いExcel、
かゆい所に手が届かず困っております。
※添付の画像を見ながら読んでいただけたらと思います。
作ろうとしている物の仕様:
手持ちの残件の進捗を管理するためのカレンダーを作成したい。
年、月は手入力で変更。連動して、8,9行目の日付、曜日が切り替わる
(万年カレンダーみたいに使う)。
土日祝は自動でセルの色を変える。(土:青 日祝:赤 など…)
今日の日付のセルも色を変える。(例えば緑に)
つまってる点:
主に条件付き書式でセルの色を変えるところで詰まっております。
(1)「10行目以下のセルの色を変える」
セルの色を変える条件となる、
8,9行目以外のセルも連動して
色を変えるというのがうまくできません。
(2)「関数の結果を条件にセルの色を変える」
9行目(曜日)の「関数の出力結果を条件に」色を変える、
8行目(日付)の「関数の出力結果を条件に」色を変える、
ということがうまくできません。
使用ソフト:
Excel2007
今回、仕事の進捗管理ということを例に質問させていただきましたが、
何かとカレンダーを用意したい場面が多く、
解決できると、色々な場面で応用が効きそうです。
ご回答、よろしくお願いします。
No.3ベストアンサー
- 回答日時:
こんにちは!
参考にならなかったらごめんなさい。
↓の画像で説明させていただきます。
当方使用の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
この回答への補足
<<<ベストアンサーについて>>>
No.2とNo.3の回答は内容がよく似ているのですが、
祝日テーブル作成の個所を図入りで丁寧に書いてくださっていたので、
こちらをベストアンサーとさせていただきたいと思います。
初見の方は、No.1の方の回答が、
最低限のところが抑えられていて、
短い時間で内容を理解するにはよいかと思います。
みなさん、ご回答ありがとうございました。
おお、できました。
ありがとうございます。
大の月・小の月への対応はとてもスマートでいいですね。
祝日はやはりテーブルを別で持たないとだめですか。
どこか自動で拾ってきてくれるような仕組みが
Excelにあればなぁと、都合のいい淡い期待を持ったりしておりましたが、
そこまで都合よくはいきませんね^^;
しかし、別のカレンダーで、「非営業日を赤く…」とか
そんなことができたらいいなと考えていまして、
そちらには応用が効きそうです。
これはこれでありがたい。
(非営業日とか個人的な有給とかになると、
テーブル作らざるを得ないでしょうから。)
ところで、名前定義に「休日」「祝日」と二種類表記がありますが、
これは統一しないと動かないですよね?
<<< 他のご回答くださったみなさんへ >>>
お返事遅くなってすいません。
じっくり時間をかけて読ませていただきたいので、
もう少し時間がかかりそうです。
なにとぞご容赦ください。
No.4
- 回答日時:
No.3です!
再びお邪魔します。
お礼欄のご指摘ですが・・・
まさしく仰るとおりで単純なる私の思い違いです。
「休日」「祝日」は名前定義したものを条件付書式の数式欄に入れなければ
色が表示されません。
前回の方法ですと、名前定義を「祝日」としなければなりませんね。
どうも的確なご指摘をありがとうございました。
それから余計なお世話になりかもしれませんが、
同様の方法で、個人の休日等も色を付けることが可能になります。
当方使用のExcel2003では条件付書式は3条件までしか設定できませんが、
Excel2007をお使いということなのでかなりの条件数が設定できるはずです。
当然のことながら、個人の休日とうは祝日とは別の表にしておかなければなりませんけどね。
どうも何度も失礼しました。m(__)m
再度ご回答くださり、ありがとうございます。
個人のスケジュールにも
その方法でいけますね。
みなさんから教えていただいた方法で、
実際に使用している残件管理、家計簿、学習実績記録などなどに
これから反映していきたいと思います。
No.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セルがアクティブセルの状態となっています。そこで上記のような式が有効になるのです。入力した式が選択した範囲のすべてのセルにオートフィルドラッグコピーされると考えてください。それぞれのセルで条件が満たされなければ色を付けるなどのことは行われないのです。
ご回答ありがとうございます。
どうも自分、条件付き書式の使い方が
きちんと整理して理解できていなかった気がします。
皆さんの回答例と、KURUMITOさんの説明で
ちょっと整理できたような気がします。
ありがとうございました。
No.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」
書式は青
とすればよいです。
TEXT関数で無理やりこねくり回して日付を組み立てるのはよくなかったですね。
DATE関数の存在までたどり着けなかったのは勉強不足でした。
それはさておき、
シンプルでわかりやすいご回答ありがとうございます。
スッキリまとまっていて、とてもわかりやすいですね。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセルでカレンダーを作りたい 5 2023/05/16 07:32
- Excel(エクセル) エクセルの祝日に色が反映しない 4 2022/05/18 09:58
- Excel(エクセル) エクセルの条件付き書式について エクセルでカレンダーを作りました。縦に日付が並んでいて、横にスケジュ 3 2022/10/10 15:32
- Excel(エクセル) エクセル 条件に合う日付に入力された時間数の合計したい 4 2022/06/17 22:18
- その他(Microsoft Office) 従業員増減対応で当番種類の増減対応な当番表 21 2022/07/19 07:30
- Excel(エクセル) マクロ、条件付き書式のfont.color 1 2023/03/28 01:10
- その他(Microsoft Office) Excelの条件付き書式についての質問です。 2 2022/09/08 01:25
- Visual Basic(VBA) 昨日、質問した件『VBA にて、条件付き書式で背景色を設定しているセルの範囲で、背景色付きのセルをカ 4 2022/04/07 14:39
- Excel(エクセル) 条件付き書式で文字入力 6 2022/08/29 10:40
- Excel(エクセル) エクセルで、土日や祝祭日に色をつけるには? 3 2022/05/05 08:11
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルで日付の入ったセルの...
-
エクセルでセルに「氏名を入力...
-
Excel ウインドウ枠の固定をす...
-
Excelファイルの「数式」タブ→...
-
関数を教えて下さい。
-
スマートな関数を教えて下さい。
-
EXACT関数とIF関数の組み合わせ...
-
スプレッドシートの関数VLOOKUP...
-
Excelのif関数で文字が見えなく...
-
Excel 2019 のピボットテーブル...
-
Excel 2019 は、SPILL機能があ...
-
各ページの1番上の表示について
-
写真のコピー
-
条件付き書式を教えてください
-
INDEX関数やMATCH関数で値を取...
-
エクセル グラフ軸について
-
excelの不要な行の削除ができな...
-
Excelで行をコピー、同じ行内に...
-
エクセル関数に詳しい方、教え...
-
Excel 改ページの文字色の変更...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ファイル内にある数字の出現回...
-
Excel関数の先頭に「@」が入っ...
-
エクセルの気味悪い不思議
-
Excel VBAで、実行時にsheet上...
-
表示されている人数だけを数え...
-
他人が作ったマクロの理解
-
Excelの関数について質問です。
-
Excel 集計表
-
エクセル 日時の計算式について
-
Excelの関数に関して質問です。...
-
エクセル:セル内の文字列の下...
-
絞り込み検索
-
エクセルの関数で
-
エクセルの書式設定について教...
-
余分なEXCELファイルに印刷され...
-
VBA 同一シート内での転記の仕方
-
長期休みの関数はありますか
-
Excelの空のセル
-
エクセルで入力してある文を別...
-
Excelのマクロで、セルを結合し...
おすすめ情報