わたしの会社では、紙ベースの日報を専用の給与計算システムへ入力しています。
ですが、
手渡された日報からさまざまな調整が入ったり
記入間違いがたくさんあります。
そのため、
一度Excelで正誤確認と調整作業を行ないます。
毎日、そして月末で集計に行われるその作業、
いかにわかりやすく、迅速に行えるか。
給与計算システムへの入力への橋渡しとして
わかりやすくするためにもExcelの機能をいろいろ学んでいきます。
(CSV形式でインポートしてくれればいいのに…)
今回はそんなかで日付を判断して土日祝日によってセルに色を付ける
条件付き書式の使い方を学んでいきます。
条件付き書式とは
Excelの機能のひとつ。
設定したルールに基づいてセルに特定の書式や塗りつぶしなどを設定したり、数字の大小によってセル内で塗りつぶしを棒グラフのように表示させたりすることができる。
関数を使用したルールの設定も行えるので
発想しだいでさまざまな使用が行える。
”土日の判別”と”祝日の判別”を分離
曜日判断の関数はあるが祝日は？
曜日は世界共通なので
Excelの関数にも専用のものがあり、
すぐに判別することができます。
ですが、
祝祭日は日本固有の日付のため、
Excelの関数で一発で判断することはできません。
なので
土日の判別と祝祭日の判別を
別の方法で行なっていきます。
いろんな方法があるかとおもいますが
今回は以下の関数を使用して
土日祝日を判断させていきます。
土日の判別方法 WEEKDAY関数
指定した日付の曜日を数字にして表示する。
表示される数字は3種類指定できる。
関数
=WEEKDAY(シリアル値,[種類])
・シリアル値
日付が入力されているセルを指定する。
・種類
”1”,”2”,”3”のどれかを指定。
返される数については下の表を参照。
省略すると1が入力されたことになる。
| 種類 | 月曜日 | 火曜日 | 水曜日 | 木曜日 | 金曜日 | 土曜日 | 日曜日 |
|---|---|---|---|---|---|---|---|
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 1 |
| 2 | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| 3 | 0 | 1 | 2 | 3 | 4 | 5 | 6 |
今回はこの数字を利用して土日を判断するようにルール設定を行います。
祝日判定にMATCH関数を使う
別記事で紹介していますがさらっとおさらい！
くわしくはそっちの記事をみてね！
指定した1列、または1行から値を検索し
対象が何番目にあるかを表示します。
関数
=MATCH(検査値,検査範囲,[照合の種類])
・検査値
検索する値や、値の入ったセルを指定する
・検査範囲
検索する範囲を1行か1列だけ指定する。
・照合の種類
「1」「0」「-1」のいずれかを選択。
入力を省略すると「1」を選んだことになる、
そういう意味なので［］がついています
下準備に祝日リストを作成する！
MATCH関数はリストから検索する関数です。
利用するにはリストが必要ですね。
カレンダーをめくりながら打ち込むと手間です。
ということで内閣府のデータを参考にリストを作成します。
内閣府-「国民の祝日」について
https://www8.cao.go.jp/chosei/shukujitsu/gaiyou.html
これをもとに作成！
参照にしてExcelのデータリストにしました！
（結局手打ち）
これを参照してMATCH関数で適合した数字があれば何かしら数字を返すので、返した数字があれば祝日だと判定するルールが取り決められます。
条件付き書式への入力
入力の仕方
設定したいルールによって異なりますが今回は土日祝日判断についてです。
”おなじ日付で”設定をしたい対象のセルを選択した状態で
ホームタブ ＞ 条件付き書式 ＞ ルールの管理 を選択
下の画像では2020年5月1日が対象になるセルを選んだ状態です。
ルールの管理ウインドウが開きますので
新規ルール をクリック
選ぶと”新しい書式ルール”ウィンドウが開きます
一番下の「数式を使用して、書式設定するセルを決定」を選びます。
ここまでが共通です。
これに続けて2つのルールを設定していきます。
WEEKDAY関数+条件付き書式で
土日のセルに色を付ける
数式として変に見えますが、
最初の「＝」は「この条件に当てはまったら書式を反映させる」
という意味だと思ってください。
今回私はこのように設定しました。
=WEEKDAY($B5,3)>4
$B5 → B列の同じ行の日付が入っているセルを指定
3 → 月曜の0から始まって日曜が6になる設定
>4 → WEEKDAY関数の結果が4を超える
数式を入力しただけでは意味がありません。
「書式」ボタンをクリックして書式を設定します。
設定した書式はボタンの左にプレビューされます。
土日は上の画像のように色塗りされるように書式設定しました。
設定したら「OK」ボタンで戻りましょう。
MATCH関数+条件付き書式で
祝日に色をつける
引き続き、祝日の書式ルールを設定します。
こちらはこのように設定しました
=MATCH($B5,祝日!$C$3:$C$52,0)>0
$B5 → B列の同じ行の日付が入っているセルを指定
祝日!$C$3:$C$52 →
別シートに作った祝日リストを選択
0 → 完全一致のみを検索
>0 →MATCH関数で該当日付がある（1以上が返される）
同じく書式も設定しましょう
色のセンスが問われるところですが
気にせずこのままいきます。
土日と祝日が複合した場合の優先順位は？
このばあい、土日と祝日が重なるとどうなるのか？
答えは”ルールの管理で上のほうが優先される”でした。
並び替えは対象のルールを選んでで優先順位の変更ができます。
今回の場合は祝日が優先されて書式反映される並びになっています。
他の日にも条件付き書式をコピー
ここまでの設定では
「2020年5月1日」の行だけが
条件付き書式設定になっています。
WEEKDAY関数とMATCH関数で
日付のセル指定が「$B5」になっていた理由が
ここにあります。
「$B$5」では書式コピーしても
「2020年5月1日」が設定したセルだけを
参照してしまいます。
あとから、横にも書式コピーしてもよいように
B行は固定させておきます。
ともかくコピーの仕方をみていきましょう。
おかしなことになりました。
あわてずに右下にあるコピー方式をみてください！
ここの「書式のみコピー(フィル)」を選びましょう。
これで「条件付き書式」がコピーされます。
正常に判断は行われているか？確認！
しっかりと設定は反映されたのでしょうか……？
おお！しっかりと色分けされています！
別の月に日付を変えてみて見ましょう
しっかりと反映されております
（４月の最終行に5月1日が表示されているけど……。）
ともかくこれで目的は達成です。お疲れさまでした。
まとめ
条件付き書式の設定についてでした。
他にもいろいろなルールが存在しています。
使い方を覚えると視覚的に見やすくて、わかりやすい表を
作ることができます。
わたしの場合、こんな感じの表を使い、
毎日30人以上を入力しています。
そうすると
「8日に入力をしたつもりがずれて9日に入力してた」
なんてことがよくあります……
そんなとき、それを防ぐためにも
入力したい日だけに色塗りするように
条件付き書式を設定することなんてできちゃいます。
これがすごい便利！
他にもありえない数字があったら
色塗りをさせたりというような
入力ミス防止にも利用できます！
そんなべんりな条件付き書式。
ぜひぜひ使ってみてはいかがでしょうか。
-Excel MOS対策講座-（順次追加作成中）
MOS2016 もくじへ
MOS2016 エキスパートもくじへ
MOS365&2019 もくじへ
-VBA講座-
未作成
-関数の使い方-
VLOOKUP関数の使い方
MATCH関数の使い方
INDEX関数の使い方
IFS関数の使い方
CLEAN関数の使い方
AND OR NOT 関数の使い方
PHONETIC関数の使い方
XLOOKUP関数の使い方
-条件付き書式-
土日祝日に条件付き書式で色をつけろ！
