Excel中級者

[徹底解説]Excelで月間シフト表やカレンダーを自動化する方法

しんたろ。

勤務表や実績表をExcelで作成しているんだけど

月が変わる毎に日付更新しないといけないし

どのシフトにどれだけ人が居るか数えないといけないし

作成途中で間違えると、最初に作っていたところを

全部修正しないといけないこともあるし

もう嫌だ!

実はExcelで管理できる方法があるよ♪

過重労働の防止や適正な給与計算には出勤・退勤をはじめとした勤怠状況の把握と管理が欠かせません。

勤怠管理の方法は様々ですが「なるべくお金を掛けずに勤怠管理したい」ですよね。実績表についてもです。

今回はExcel初心者でも簡単にカレンダーを作成する方法を紹介!

◎前編は「勤怠管理表

◎後編は「加工実績表

に分けて紹介します。

見たい項目は目次の対象項目をクリックして飛んでいってください♪

[前編]勤怠管理表を作成

まずは完成形から

勤務完成

対象者の勤務を入力すると16行〜20行に各日シフトの合計が自動で更新されます。

また、「開始日」に月の1日目を入力すると、日付と曜日が自動で更新されます。

余談ですが、私が最初に関数を覚えたきっかけは「勤務表」で試行錯誤しながら覚えました。

開始日を変更するだけでカレンダーの日付が変わった時は脳汁ドバドバでしたね・・・。

カレンダー自動再生

罫線で骨組み作成

罫線を作る際を必要な項目を確認しましょう。

  1. 勤務開始日・終了日を入力(EOMONTH関数)
  2. 月タイトル
  3. 勤務時間の項目
  4. 名簿・日付・曜日
  5. 勤務帯人員集計

は、どのセルにするか特に気にせずOKで。

②〜⑤については図を参考に配置をおねがいします。

後々セル番地を指定しながら説明をおこないますので伝えやすくなります。ご協力をおねがいいたします。

①勤務開始日・終了日を入力(EOMONTH関数)

開始日と終了日の説明をします。

◎開始日

7月だったら「7/1」と手入力。

◎終了日

EOMONTH関数を使用します。EOMONTH関数をしようすると対象の月末を出力してくれます。

今回は開始日がセルK2にありますので

=EOMONTH(K2,0)

セルK3に入力ください。

②月タイトル

こちらはどの月のシフトなのかを、わかりやすくするためのアイキャッチ情報です。

いちいち手打ちで更新するのは面倒くさいので関数を入れましょう。

①で作成した「開始日」の月を使用します。

=TEXT(K2,”M”)&”月度シフト表”

TEXT関数を使って表示形式を「月」だけにして”&”で「月度シフト表」と繋げます。

[Excel]別々のセルを結合して表示する方法
[Excel]別々のセルを結合して表示する方法

③勤務時間の項目

勤務時間
ここの時間は手打ちで入力してください。

④名簿・日付・曜日

ここからが本番です。

初心者の方はここを乗り越えると一気に楽しくなります

名簿

まずは対象者の名前を入力してください。ドロップダウンリストを作成し、入力ミスを防ぐなど方法はあります。

[Excel]入力を《リスト化で選択式へ》。複数連動と自動追加の方法
[Excel]入力を《リスト化で選択式へ》。複数連動と自動追加の方法

日付

勤務開始日1

セルD6には②で入力した「開始日」を選択します。

ですので、セルD6には「=K2」が入ります。

勤務開始日2

開始日以降のセルを入力していきましょう。

セルE6には先程入力したセルD6の値にプラス1して右端までコピペすれば良いです。

勤務表2

ですがここでポイント。

月間シフト表を作りたいので、他の月日はいらないですよね。このままプラス1を引っ張ってきてしまうと・・・

勤務表3

「6月のシフト表なのに7月1日まで出現しちゃった!」

となります。これを回避するために

IF関数を使用しましょう。

[Excel]結果によって表示を変更する方法
[Excel]結果によって表示を変更する方法
カレンダー説明3

IF関数を追加して、もし当月ではなかった場合は空白という条件分岐にしましょう。

=IF(D6+1>$K$3,””,D6+1)

これで表示されなくなりましたね。

カレンダー説明2

曜日

カレンダー説明

TEXT関数を使って6行目の日付に連動した曜日を出力できるようにしましょう!セルD7に

=TEXT(D6,”aaa”)

と入力すると、上の図でいくと「」と結果がでますね。ちなみに“aaaa”と打つと「水曜日」と結果がでます。

”a”を一文字追加するだけで「曜日」が出てくるなんて・・・

ルールを覚えれば簡単!覚えておこうね♪

「曜日」を入力し終わったら後はコピペしましょう。これで曜日設定は終了♪

休みの日を「条件付き書式」でハッチング

土日&祝日の対象日を色付けするために

条件付き書式で自動色付をするようにします。

[Excel]セルに色付を自動で10倍早くする方法
[Excel]セルに色付を自動で10倍早くする方法

まずは「ホーム → 『条件付き書式』」をクリック。

条件付き書式説明

次に、どのような条件の際に色を塗るか定義します。

=OR(WEEKDAY(D$6)=1,WEEKDAY(D$6)=7)

OR関数(〜または)にネストで「WEEKDAY関数」を使います。

WEEKDAY関数は、「1〜7」の数字に応じて「日〜土」までを出力してくれます。

[後編]加工実績表の作成

完成イメージをまずはチェック

カレンダー完成1

上の図を目標に作成を進めます。「月」を変更すると・・・

カレンダー完成2

連動してカレンダーが自動で更新されます♪

日付を更新するだけでカレンダーの日が変わるのはとても楽ですね!!

日付設定方法の確認

まずはカレンダーのベース作成

完成イメージのようにまずは下記①〜③まで作成してください。

  1. 年、月を指定するセル枠を作成
  2. カレンダー自体のフォーマットを作成
  3. 曜日を日曜日〜土曜日まで入力
カレンダー設定4

年、月の情報設定

次に、年月に入力された値通りにデータが変わる関数を入力していきます。

  1. セルG2に「年」、セルH3に「月」を入力
  2. date関数を利用してカレンダーの開始日を取得
  3. weekday関数で開始日の曜日番号を取得

②にdate関数についてはセルB3に「=date(G2,H3,1)」を入力

③についてはセルC3に「weekday(B3)」を入力してください。

補足ですが、weekday関数の引数は「日曜=1、土曜=2、・・・」と続いていきます。

下の画像は「2022/04/01=金曜日=6」となります。

カレンダー設定3

カレンダーの始まりを取得する方法

先ほど入力をした関数が反映されるように今度はカレンダーの中に関数を入れていきます。

セルB6には「B3-(C3-1)」と入力をします。いまいち分かりにくいと思いますので説明すると月初日から何日前かを考えます。

  1. 月初日がもし日曜日だった場合は、セルB6は月初日から0日前 ⇨「2022/4/1」
  2. 月初日がもし月曜日だった場合は、セルB6は月初日から1日前 ⇨「2022/3/31」

セルB6には月初日の曜日数値に対して-1日遡った日付が入力されるので、自然と該当日が日曜日に設定されるということになります。

カレンダー設定

カレンダーの始まり以外を決める方法

下の図は、分かりやすいようにあえて数式表示にしています。

  1. セルB6が決まったらセルC6には「=B6+1」を入力
  2. セルC6にカーソルを合わせて「Ctrlキー+C」を押してコピー
  3. セルD6にカーソルを合わせて「Shiftキー+カーソル⇨でH6」まで選択
  4. 「Ctrlキー+V」を押してペースト
カレンダー範囲選択5

6行目が一旦完成です。

次はセルB7にまた数式を入れていくのですが今度は「=H6+1」を入力することで連続した日にちのデータを入力します。

セルC7からはセルC6と同じように前の日に対して+1日を入力してあげてください。

カレンダー範囲選択4

ここまで入力をし終えたら後は簡単です。

B7からH7の範囲をコピーし、8行目から11行目までコピーをしていただくと一旦完成です。

カレンダー範囲選択3
カレンダー範囲選択2

ただこのままの日付だと、ちょっと見にくいですので「表示形式」で見た目を変えていきます。

表示形式の変更

まずはセルB6からH11まで範囲選択をします。

※セルB6にカーソルを合わせて

「shift キー+Ctrlキー+⇨矢印」

「shift キー+Ctrlキー+⇩矢印」

で簡単に範囲選択できます^^

「ctrl + A」でもできますよ♪

カレンダー範囲選択

表示自体は日にちだけ表示すれば良いので

現在の表示形式[yyyy/m/d]を[d]のみに変更します。

  1. 「セルの書式設定」を開く
  2. 「表示形式」タブを開く 一番左にあります。
  3. 「ユーザー定義」を選択
  4. 「d」を選択
カレンダー設定

入力が完了すると・・・。

カレンダー自動化

日にちのみとなりました!だいぶ完成形に近づいてきましたね^^

ですが、当月の日付を表示する必要って正直無いですよね。

「いらない日付の消し方」についてになります。

条件付き書式設定の確認

対象ではない日付を消す方法

当月の日にちではない日を消す方法を伝授いたします。

  1. セルB6にカーソルを合わせ「ホーム⇨ 条件付き書式⇨ 新しいルール」の順に押す。
  2. 一番下にある「数式を使用して、書式設定をするセルを決定を選択。
  3. 「=MONTH(B6)<>$H$3」を入力。$H$3は当月以外を指します。
  4. 下にあるプレビューの「書式(F)」を選択し、フォントタブにある「色(C):」を好きな色に変更
カレンダー自動化2

すると、セルB6の文字が白くなり消えて見えます。

B6をコピーして

「shift キー+Ctrlキー+⇨矢印」

「shift キー+Ctrlキー+⇩矢印」

で範囲選択をした後に、右クリックを押して

「形状を選択して貼り付け ⇨ 書式 ⇨ ok」

を押すと全体に条件付き書式が反映されます。

カレンダー自動化3

カレンダー全体を整える

後は、見た目を整えて終わりです。

日付の下に挿入で追加して余裕を持たせて見るとかなり見栄えが良くなります。

カレンダー自動化4

動作確認

カレンダー自動化4

「H3」に日付を入れて無事に動いているか動作確認してみてください。

まだ作り込みたい人は、曜日や日のカラーを変えたり年月をプルダウンメニュー化したりオリジナルをぜひ作ってみてください♪

カレンダー自動化5

こういう感じで実績管理をするのもよし!実績の集計にはSUMIFS関数がおすすめ♪

さいごに

 今回作成したようなカレンダーは、様々なアプリやツールがありますが、やはり自分で作成すると融通が効くので特にオススメです。

全会社員を残業地獄から救いたい
本ブログの有効活用方法
1
Concept
初心者に分かりやすい解説

しんたろ。ブログでは「少しでも残業を減らしたい」「時間効率を上げて新しいことにチャレンジしたい」「仕事で評価を上げて年収を上げたい」そんなあなたに向けて私が学んできたノウハウを最短ルートで教えるExcelブログとなっています。

覚えておくべき関数や操作方法を徹底解説!

2
Concept
Excelを最短で
習得する方法を解説

「独学で習得できる気がしない」そんな方に効率よく習得できる記事を書きました。

本や動画を紹介していますが、やはり一番効率の良い習得方法は、講師にわからないことを質問できる『オンラインスクール』です。

3
Concept
Excelが大活躍!
会社員でも不就労所得を作れる。

「会社の収入だけでは不安」そう思い出会った副業がブログでした。書いた記事たちが、あなたが寝ている間もずっと働いてくれます。『不就労』の完成です。

記事を書く際の様々な分析にExcelがとても役立ちます。私は2023年末までにブログで脱サラする予定です!

あなたもブログで脱サラしましょう。

お問い合わせ

Contact

問い合わせより直接相談していたくと
無料で相談にのりますよ♪

お気軽にご相談ください♪


ABOUT ME
しんたろ。
しんたろ。
脱サラ目指す会社員
デスクワークに追われ、仕事が極端に遅かった私がExcelを学んだら会社から評価され残業も大幅削減を実現!早期FIREするための副業もしています。 あなたの人生も好転させたい!ぜひ本ブログをご活用ください。
記事URLをコピーしました