見出し画像

【COUNTIF関数こうも使える!】シフト表作り方①

皆さんこんにちは。今回は、Excelを使ったシフト表づくりを
行っていきたいと思います。

シフト管理は専用ツールを使っている会社も多いですが
今回も条件付き書式などを使いシフトによる色変更、
出勤日の集計、労務アラートの実装まで行っていきます!

この記事で学べること!

  • IF関数を使った月の切り替えの仕方がわかる

  • 条件付き書式を使った土・日・祝日の塗りつぶし

  • COUNTIF関数の意外な使い方

それでは、始めていきましょう!


1.<完成物>のプレビュー

完成品_画像

それでは、次の項から手順を追ってシフト表を作っていきましょう!

2.年月日の入力とセルの書式設定を使った表記変更

2-1.セルに1月単位で曜日を入力

まず、写真のようにE2セルに日付を入力(手入力でOK)

E2セル_日付入力

2-2.E3セルを日付に、E4セルを曜日に表記変更

まず、E3セルに写真のように年月日を反映する。

E3セル_日付参照

次にE3セルで右クリック→「セルの書式設定」→「ユーザー設定」
→入力欄に「d」と入力→E3セルが「日」に変換される。

E3セル_年月日→曜日への表示変更

更にE4セルに写真のように日付を反映する。

E4セル_日付反映

そしてE4セルで右クリック→「セルの書式設定」→「ユーザー設定」
→入力欄に「aaa」と入力→E4セルが「曜日」に変換される。

E4セル_日付→曜日表記変更

「OK」を押して表示変更すると

E4セル_曜日への表記変更後

2-3.1月分の日付、曜日をオートフィルで反映

F3セルをクリックし、写真のように翌日の日付を反映する。

F3セル_翌日の日付反映方法

次にF3セルを右クリックしオートフィルで月末まで日付を反映する。

月末まで日付反映

続いて曜日を月末まで反映する。
E4セルを右クリックし、下の写真のようにオートフィルで
曜日を月末まで反映する。

オートフィル_月末まで曜日反映

3.IF関数とDAY関数を使った月変更

3-1.月の切り替え関数で、翌月を表示する

F2セルを左クリックし、下の写真のようにIF関数で
月が変わるタイミングで翌月を表示するようにする。

F2セル_月切り替え関数の実装

更に下の写真のように、オートフィルで月末まで関数を反映させる。

月末まで_月の切り替え関数反映

加えてAJセルを右クリック→セルの書式設定→ユーザー定義
→「m」→「OK」でシリアル値を「月」の表記に変える。

AJセル_シリアル値→月へ表記変更

同様のやり方でE2セルを「8」と表記変更してしまいましょう。

4.土・日・祝日の塗りつぶし

4-1.条件付き書式で土・日・祝日を塗りつぶす

「+」マークで新規シートを追加→「シート名:祝日一覧」にする
→Googleで年間の祝日を調べる
→下の写真のように祝日一覧を作成する

新規シート_祝日一覧作成

続いて「シフト表」シートに戻り、E4セルをクリックし
「条件付き書式」→「数式を使用して、書式設定するセルを決定」
→下の写真のようにOR関数を使って複数の条件を設定する。

条件付き書式_土日祝日を塗りつぶす

更に「書式」→「フォント:黒」→「塗りつぶし:灰色」
→「OK」→「適用先右の↑」をクリック→月末まで範囲選択
→「OK」で下の写真のように土日祝日が塗りつぶされる。

土日祝日_塗りつぶし

※エクセルは祝日を自動判別できないので、一覧を作って
 COUNTIF関数を使って「日」と祝日一覧の日付が
 合致していることを確認している事がポイントです!

次回はマスタシートでのシフト・氏名管理と
データの入力規則を使ったシフト・氏名の反映を実装します。

この記事がいいと思った方、読者登録とTwiitterでの拡散
宜しくお願いします。また、関数の使い方などご不明な点や
「こんな記事を書いて欲しい」などご要望がありましたら、
今後の励みになりますので、お気軽にtwitterのリプまたはDM、
noteのコメント欄までお問合せください。

一緒に一歩上の自分を目指して頑張りましょう!

【こちらの記事もいかが?】
続きの記事を書きました!ぜひチェックしてみて下さい!

5.補足

【補足-その①】DAY関数の使い方

上記(3-1)でIF関数とDAY関数を組合わせて翌月を反映させましたが
ここで本来の使い方を確認しておきましょう。

DAY関数_使い方

写真のように、DAY関数はシンプルに選択した年月日の「日付」を
返すものになっています。
※上記(3-1)で「DAY(F$3)=1」としているのは
 「日付が翌月の1日なら」という条件を指定しています。

【補足-その②】COUNTIF関数の使い方

上記(4-1)で土・日・祝日を塗りつぶす際に条件付き書式で
COUNTIF関数を使って条件と合致する日付を返していましたが
ここで本来の使い方を確認しておきましょう。

COUNTIF関数_使い方

写真のように、COUNTIF関数は指定したものの個数を
カウントする際に使うのが一般的です。
※上記(4-1)で「COUNTIF(祝日一覧!$B:$B, F$3)」としているのは
  「祝日一覧の日付とF3セルの日付が一致している」という条件を
 指定しています。

この記事が気に入ったらサポートをしてみませんか?