カレンダー

Excel

マクロを使わない自動罫線のExcelカレンダー(2018年度/2019年度)

個人での予定管理から、プロジェクトのタスク管理まで、カレンダーはあらゆる場面で使いますね。システム手帳や社内イントラを使っている方もいると思いますが、意外と使えるのがExcelカレンダー。新年度にも、気になる平成31年(2019年)にも対応済。

1.Excelカレンダー(スケジュール表)

そろそろ新年度が始まります。来年度のスケジュール計画を立てるのにカレンダーを使いますよね。祝日設定や、罫線の設定など、面倒な設定もありますが、カレンダーなんて毎年、毎年度使うもの。

ということで、1か所日付入力するだけで、いろいろと書式設定してくれるスケジュール表を作ってみました。関数の使い方や、パワポの見せ方などをまとめてばかりでしたが、今回は直接そのままでも使えるツールを提供します。

ちなみに、自動的に書式設定されますがマクロは使っていません。条件付書式設定のみで作っていますので、興味があるひとは、2章の解説とあわせて仕組みを理解してみてください。

もちろん、2018年度にも、2019年にも対応しています!!

スケジュール表01

Excelスケジュール表(縦)

 

雛形をダウンロードして使う方はここまででOK。スキルアップ系ブログですので、ここからは、作り方と考え方を紹介します。

2.作り方

 2-1.Excelでの日付の考え方

Excelは1900年1月1日を1とし、1日進むごとに1増える仕組みで、次のようなっています(日付のシリアル値といわれています)。

2   = 1900年1月2日
3   = 1900年1月3日
366   = 1901年1月1日
43,101= 2018年1月1日

日付を数字として扱うことができるので、2018年1月1日+1=2018年1月2日となります。そこでこの雛形は、日付を1か所のみ入力しそれ以降は+1で日付を1日ずつ自動的に増やしていく仕組としています。

 2-2."今日"の判定

今日がカレンダーのどこかを瞬時に見つけられると効率アップです。A列でIF関数を用いて"今日"の判定をしています。

「カレンダー日付」がToday()と同じ日付の場合に"1"とし、A列の値が"1"の場合に条件付き書式で行全体を塗りつぶするようにしています。

  2-3."曜日"の判定

B列でMod関数を用いて"曜日"の判定をしています。

日付を数字の足し算で扱うことで、「ある日付」+7で次の週の同じ曜日になります。言い方を変えると、同じ曜日は7で割った余も同じになります。具体例を挙げると、

2018/1/1(月) = 43,101 = 6,157 × 7 + 2
2018/1/2(火) = 43,102 = 6,157 × 7 + 3

2018/1/8(月) = 43,108 = 6,158 × 7 + 2

 

上の例の最後の余りを算出できれば、曜日を自動算出することができます。それができるのが、次のMod関数です。

=MOD(対象、除数) :[対象]を[除数]で割った余り

 

2018/1/1を対象に、7で割った余りを求めると次のようになります。

=mod(2018/1/1,7)
=mod(43101,7)
=2

 

この考え方を用いれば、

Mod(日付,7)=  0 =  土
Mod(日付,7)=  1 =  日
Mod(日付,7)=  2 =  月
Mod(日付,7)=  3
 =  火
Mod(日付,7)=  4 =  水
Mod(日付,7)=  5 =  木
Mod(日付,7)=  6 =  金

 

と判定することができるので、B列で曜日判定し、土(Mod=0)と日(Mod=1)の行を条件付き書式で、グレーアウトさせています。

 2-4.国民の休日・祝日の判定

休日をすべて機械判定することはできません。そこで国民の休日・祝日においては別のシートで手動で管理します。

スケジュール表02

 

といっても、祝日の一覧は内閣府のHPにいけば一覧になったCSVファイルがあるので、それを拝借し貼り付けるだけです。内閣府HPにたどり着けさえすれば、祝日リストはあらあっというま。
国民祝日について(内閣府HP)

ちなみに、国民の祝日は、「国民の祝日に関する法律」によって定められています。昭和23年の制定から、昭和41年の建国記念日の制定など幾度かの改定を経て現在に至ります。内閣府のHPのリストは平成31年(2019年)の一覧がすでに提供されています。

さらに、祝日のCSVファイルは2種類あり、純粋に国民の祝日のみのものと、振替休日の日付も列挙されたバージョンと2種類ありますので、振替休日のあるバージョンを貼り付ければ、もっとあっという間。

で、C列に、カレンダーの日付と、祝日一覧の日付とをvlookup関数で付け合わせれば祝日を判定できます。あとは、土日のグレーアウトと同様に、C列が1の場合に、行ごとグレーアウトする条件付き書式を設定します。

祝日のリストには、カレンダーのすべての日付がないため、祝日以外はエラー表示になってしまいますので、エラー処理も忘れずに。

 

 2-5.日付の設定方法(表示形式)

D2に入力した日付をもとに、値はD2=E2=F2=G2と同じ行は同じ値になっています。年月日・曜日の表示が違うのは、表示形式の違いのみです。[セルの支書式設定]-[表示形式]-[ユーザー定義]で、それぞれyyyy、m、d、aaaaと設定すると年・月・日・曜の表示になります。
スケジュール表03

 2-6.書式設定(条件付書式)

条件付書式は4つ設定しています。①月の変わり目で横方向の罫線が実線になる、②C列=1ならグレーアウトとなる祝日設定、③B列≦1ならグレーアウトとなる土日設定、④今日の日付行を強調、と全部で4つの条件付書式を設定しています。
スケジュール表04

3.最後に

この手のファイルは、最初に作るときにちょっと時間をかけて工夫すれば、その後の作業が何倍も早く、楽になります。仕事の効率化は、ただ作業を早くするだけではなく、作業をする順番を変えたり、一度時間をかけても次にやるときに時間を省略できるようにすれば、中長期的には大きな効果が出てきます。

スポンサーリンク
スポンサーリンク

関連記事

  1. Excel

    【Excel関数】年齢を年代に3秒で IF文を使わず簡単変換

    年齢を年代に変換するとき、IF文を使っていたりしませんか?INT関数を…

  2. Excel

    【Excel関数】住所情報から都道府県を抽出する方法

    "東京都千代田区1-1-1"のように連結した住所情報から都道府県だけを…

  3. 白黒折線サムネ

    Excel

    【Excel】折れ線グラフを白黒印刷でも見やすく!わかりやすく!

    お疲れさまです。ゆうぞうです。今回のビズ技は、折れ線グラフを白…

  4. Excel

    【Excel】見やすいきれいな折れ線グラフの作り方

    今回のビズ技は、基本中の基本。グラフを見やすくするコツを紹介します。基…

  5. vlookupサムネ

    Excel

    【Excel関数】応用の前にまずは基本!vlookupの使い方

    vlookup関数は、別シートや別表のデータを参照できる関数です。でも…

  6. サムネイル折線

    Excel

    【Excel】折れ線グラフを塗りつぶして見やすくする作り方

    サムネイルのグラフのような、折れ線グラフを塗りつぶして効果的に魅せる方…

スポンサーリンク




特集

  1. 折れ線グラフ特集1
  2. 特集働き方改革サムネ
  3. SC特集サムネ

スポンサーリンク




仕事効率アップ系記事

  1. ショートカットサムネ
  2. 折れ線グラフ点線サムネ (2)
  3. Keyboad
  4. 働き方改革-職場
  5. サムネ-揃える

スキルアップ系記事

  1. enphasis_top
  2. ハイローマトリクス-サムネ
  3. サムネイル折線
  4. 円グラフ色使い

スポンサーリンク




ピックアップ記事

  1. 愚痴-サムネ
  2. ボスマネ-サムネ
  3. regret
  4. analysis
  5. サムネイル
  1. SC特集サムネ

    PC

    【特集】ショートカットキーで仕事効率化!働き方改革の小さな一歩👣…
  2. 特集働き方改革サムネ

    Column

    【特集】「働き方改革」を独り歩きさせない!「働き方改革」の実現に向けて
  3. 折れ線グラフ特集1

    PC

    【特集】"使い方"では足りない!「折れ線グラフ」の"使いこなし方"
PAGE TOP