【COUNTIF関数こうも使える！】シフト表作り方①
皆さんこんにちは。今回は、Excelを使ったシフト表づくりを
行っていきたいと思います。
シフト管理は専用ツールを使っている会社も多いですが
今回も条件付き書式などを使いシフトによる色変更、
出勤日の集計、労務アラートの実装まで行っていきます！
この記事で学べること！
-
IF関数を使った月の切り替えの仕方がわかる
-
条件付き書式を使った土・日・祝日の塗りつぶし
-
COUNTIF関数の意外な使い方
それでは、始めていきましょう！
目次
1.＜完成物＞のプレビュー
それでは、次の項から手順を追ってシフト表を作っていきましょう！
2.年月日の入力とセルの書式設定を使った表記変更
2-1.セルに1月単位で曜日を入力
まず、写真のようにE2セルに日付を入力(手入力でOK)
2-2.E3セルを日付に、E4セルを曜日に表記変更
まず、E3セルに写真のように年月日を反映する。
次にE3セルで右クリック→「セルの書式設定」→「ユーザー設定」
→入力欄に「d」と入力→E3セルが「日」に変換される。
更にE4セルに写真のように日付を反映する。
そしてE4セルで右クリック→「セルの書式設定」→「ユーザー設定」
→入力欄に「aaa」と入力→E4セルが「曜日」に変換される。
「OK」を押して表示変更すると
↓
2-3.1月分の日付、曜日をオートフィルで反映
F3セルをクリックし、写真のように翌日の日付を反映する。
次にF3セルを右クリックしオートフィルで月末まで日付を反映する。
続いて曜日を月末まで反映する。
E4セルを右クリックし、下の写真のようにオートフィルで
曜日を月末まで反映する。
3.IF関数とDAY関数を使った月変更
3-1.月の切り替え関数で、翌月を表示する
F2セルを左クリックし、下の写真のようにIF関数で
月が変わるタイミングで翌月を表示するようにする。
更に下の写真のように、オートフィルで月末まで関数を反映させる。
加えてAJセルを右クリック→セルの書式設定→ユーザー定義
→「ｍ」→「OK」でシリアル値を「月」の表記に変える。
同様のやり方でE2セルを「8」と表記変更してしまいましょう。
4.土・日・祝日の塗りつぶし
4-1.条件付き書式で土・日・祝日を塗りつぶす
「＋」マークで新規シートを追加→「シート名：祝日一覧」にする
→Googleで年間の祝日を調べる
→下の写真のように祝日一覧を作成する
続いて「シフト表」シートに戻り、E4セルをクリックし
「条件付き書式」→「数式を使用して、書式設定するセルを決定」
→下の写真のようにOR関数を使って複数の条件を設定する。
更に「書式」→「フォント：黒」→「塗りつぶし：灰色」
→「OK」→「適用先右の↑」をクリック→月末まで範囲選択
→「OK」で下の写真のように土日祝日が塗りつぶされる。
※エクセルは祝日を自動判別できないので、一覧を作って
COUNTIF関数を使って「日」と祝日一覧の日付が
合致していることを確認している事がポイントです！
次回はマスタシートでのシフト・氏名管理と
データの入力規則を使ったシフト・氏名の反映を実装します。
この記事がいいと思った方、読者登録とTwiitterでの拡散
宜しくお願いします。また、関数の使い方などご不明な点や
「こんな記事を書いて欲しい」などご要望がありましたら、
今後の励みになりますので、お気軽にtwitterのリプまたはDM、
noteのコメント欄までお問合せください。
一緒に一歩上の自分を目指して頑張りましょう！
noteにも同じ記事を書きました。
noteの方が見やすいので是非ご一読ください！
5.補足
上記（2-4）でIF関数とDAY関数を組合わせて翌月を反映させましたが
ここで本来の使い方を確認しておきましょう。
写真のように、DAY関数はシンプルに選択した年月日の「日付」を
返すものになっています。
※上記（2-4）で「DAY（F$3）=１」としているのは
「日付が翌月の１日なら」という条件を指定しています。
【補足-その②】COUNTIF関数の使い方
上記（2-5）で土・日・祝日を塗りつぶす際に条件付き書式で
COUNTIF関数を使って条件と合致する日付を返していましたが
ここで本来の使い方を確認しておきましょう。
写真のように、COUNTIF関数は指定したものの個数を
カウントする際に使うのが一般的です。
※上記（2-5）で「COUNTIF（祝日一覧!$B:$B, F$3）」としているのは
「祝日一覧の日付とF3セルの日付が一致している」という条件を
指定しています。
