"BOKU"のITな日常

還暦越えの文系システムエンジニアの”BOKU”は新しいことが大好きです。

土日等の背景色を変えて、一カ月分の曜日付きカレンダーを作る/EXCELの小ネタ

EXCELで1月分の日付を曜日付で一覧する表を作ります。

スケジュール表とか簡易出勤簿とか、使う機会の多いやつです。

VBAとかは使わず関数だけでやります。

f:id:arakan_no_boku:20190201221826j:plain

 

まず日付の一覧から

 

対象の年月だけ入力したら、月末まで日付を自動表示するようにしてみます。

f:id:arakan_no_boku:20190224215831j:plain

想定仕様としては、B1セルに「2019/1」みたいに、年月を入力する。

そうすると、A列の日付の列に1日から末日までの日付を表示する。

みたいな感じです。

 

仕掛け

 

B1セルに「2019/1」と入力でも、EXCELが勝手に「2019/1/1」と1日の日付にします。

だから。

A3セル=1日の日付は「=B1」のように参照すればよいだけです。

仕掛けが必要なのは、A4セルから(2日以降)です。

こんな計算式を31日迄分コピーします。

=IF(A3<>"",IF(MONTH(A3+1)=MONTH($B$1),A3+1,""),"")

上記例はA4セルを想定しているので。

  • ひとつ上(A3)が空("")なら、ここ(A4セル)に空(””)をセットする。
  • でなければ、ひとつ上(A3)の日付の翌日(+1)の月(MONTH)と、B1セルに指定した年月の月(MONTH)が同じ(=同月内)かチェックする。
  • 同じなら、ここ(A4セル)に、翌日(A3+1)をセットする。
  • 同じでなければ空(””)をセットする。

ということをやるわけです。

たったこれだけで、うるう年迄考慮した指定月の日付を一覧できます。

 

ここはフィルでもいいんですけどね

 

もちろん。

日付の一覧ごとき、フィルの「連続データの作成」でできるから、関数なんぞ使う必要はない・・という方もいるでしょう。

f:id:arakan_no_boku:20190224231721j:plain

それでも全然OKだと思います。

ただ、自分はフィルすら・・面倒だと思ってるだけなので(笑)

 

曜日の表示

 

曜日の表示は、以下の関数をB列にコピーすればできます。

=IF(A3<>"",INDEX({"日","月","火","水","木","金","土"},WEEKDAY(A3)),"")

この例はB3セルに貼り付けた状態を想定しています。

だからとなりのセル(A3)を見ています。

となりのセルが空("")でない=日付がセットされているなら、曜日表示をして、でなければ空("")をセットします・・というわけです。

曜日表示はこの部分。

INDEX({"日","月","火","水","木","金","土"},WEEKDAY(A3))

 WEEKDAYは、日付から週の何番目の日なのかを返します。

その週は日曜はじまり、土曜日終わりなので、その数字と曜日文字の配列をぶつけてやると曜日が表示される・・というわけです。

f:id:arakan_no_boku:20190224231233j:plain

 

土日に色を付けて見やすくする

 

こういうカレンダー的なものを作ると、休みの日・・例えば土曜日・日曜日は背景色を変更して見やすくしたくなります。

ま・・、実際、便利ですし。

EXCELの条件付き書式で、それは簡単にできます。

背景色をつけたいセルを全部選択して、条件付き書式の「新しいルール」を開きます。

それで「数式を使用して、書式設定するセルを決定」を選び、以下のような計算式を入力して、書式で「背景色」や「パターン」を選びます。

f:id:arakan_no_boku:20190225091247j:plain

計算式のポイントです。

=OR($B3="土",$B3="日")

これは、B列に曜日があって、B3セルがその一番上の場合です。

条件付き書式で複数行選択してても、ここで入力するのは、その一番上の行のセルでいいと考えておけばわかりやすいです。

ポイントは「$B3」のように行数を示す数字の部分は絶対参照にしないで、列だけ絶対参照にしていること。

今回は複数(土曜日・日曜日)あるので、OR()で「$B3が土 または 日」という条件にしてます。

これでOKすると。

f:id:arakan_no_boku:20190225091834j:plain

はは・・、いい感じ。

 

おまけ:祝日も色を変えたい時はどうするか

 

上記の応用なんですが、祝日や会社の記念日とかの土日以外の休みも色を変えたいということはままあります。

さすがに、曜日みたいに自動判定は無理です。

こんな感じで、日付のリストは作らないといけません。

f:id:arakan_no_boku:20190225092440j:plain

でも、これさえあれば条件付書式のさっきの条件に、以下のような計算式をつけくわえてやればできます。

NOT(ISERROR(VLOOKUP($A3,$F$2:$F$100,1,0)))

 ちょっと難しいですけど。

A列の日付をキーにしてF列の祝日日付のリストに対してVLOOKUPをしてます。

VLOOKUPで見つからなかったらISERRORが「TRUE」になるのですが、今回は「見つかった!」時に色をつけたいので、NOT()で反転してるわけです。

こうすると、VLOOKUPで祝日のリストに存在する日付であれば・・という条件設定ができるわけです。

上記の条件式と合体させると

=OR($B3="土",$B3="日",NOT(ISERROR(VLOOKUP($A3,$F$2:$F$100,1,0))))

 こんな感じ。

OKすると。

f:id:arakan_no_boku:20190225093720j:plain

いい感じです。

ではでは。