エクセル関数を活用したシフト表の作成方法

シフト表(勤怠管理表)は会社などのシフト管理で1つは作成すると思います。シフト表には日付計算、日数計算、書式設定など見た目とは裏腹に複雑なことを行います。様々なエクセル関数を駆使してシフト表を作成することで、エクセル関数を組み合わせて思い通りの結果を表示させることができるようになるでしょう。

完成するシフト表

当ページで紹介している手順に沿って作成することで完成するシフト表のイメージです。

エクセルシフト表

目次

  1. 使用するエクセル関数
  2. 項目を入力する
  3. 出勤日数を計算する準備
  4. 出勤人数を計算する準備
  5. シフトの入力
  6. 抜粋の表示
  7. 装飾

使用するエクセル関数

関数名 説明
COUNTA 範囲に含まれる空白ではないセルの個数を返す。
COUNTIF 1つの検索条件に一致するセルの個数を返す。
DATE シリアル値で表された日付の情報を返す。
DAY シリアル値を日付に変換する。
IF 条件によって処理をわける。
INDIRECT 文字列への参照を返す。
MATCH 範囲内で値を検索して相対的な位置を返す。
MAX 最大の数値を返す。
MIN 最小の数値を返す。
TEXT 表示形式コードを使用して数値に書式設定を適用する。

項目を入力する

まずは名前などの項目を入力します。
※名前は例です。実在する人物ではありません。

項目を入力する

次に日付を入力します。C3のセルに1、D3のセルに2を入力し、D3のセルを選択した状態で右下をAD3のセルまでドラッグします。
※解説の都合上、列幅を狭めています。

項目を入力する2

次にAE3のセルに「=IF(DAY(DATE($A$3,$A$4,29))=29,29,"")」を入力します。これで設定している年月に29日が存在する場合だけ29日が表示されます。

項目を入力する3

次にAF3のセルに「=IF(DAY(DATE($A$3,$A$4,30))=30,30,"")」を入力します。これで設定している年月に30日が存在する場合だけ30日が表示されます。

項目を入力する4

次にAG3のセルに「=IF(DAY(DATE($A$3,$A$4,31))=31,31,"")」を入力します。これで設定している年月に31日が存在する場合だけ31日が表示されます。

項目を入力する5

次に曜日を入力します。C4のセルに「=TEXT(DATE($A$3,$A$4,C3),"aaa")」を入力します。これで設定している年月と上記で入力した日付に対応する曜日を省略形式で表示します。

項目を入力する6

そして、C4のセルを選択した状態で右下をAD4のセルまでドラッグします。

項目を入力する7

最後にAE4のセルに「=IF(AE3="","",TEXT(DATE($A$3,$A$4,AE3),"aaa"))」を入力し、右下をAG4のセルまでドラッグします。これで29日~31日がそれぞれ存在している場合に曜日が表示されます。

項目を入力する8

このようになれば成功です。

項目を入力する9

出勤日数を計算する準備

次に出勤日数を計算する準備をします。AH3のセルに「出勤日数」と入力します。そしてAH5のセルに「=COUNTA(C5:AG5)」を入力します。

出勤日数を計算する準備

入力したAH5のセルを選択した状態で右下をAH14のセルまでドラッグします。
このようになれば成功です。

出勤日数を計算する準備3

出勤人数を計算する準備

次に出勤人数を計算する準備をします。C15のセルに「=COUNTIF(C5:C14,"A")」を入力します。これでシフトが入力されている場合に、勤務体系Aを選択している人数が表示されます。

出勤人数を計算する準備

続けてC16のセルに「=COUNTIF(C5:C14,"B")」を入力します。これでシフトが入力されている場合に、勤務体系Bを選択している人数が表示されます。

出勤人数を計算する準備2

続けてC17のセルに「=COUNTIF(C5:C14,"C")」を入力します。これでシフトが入力されている場合に、勤務体系Cを選択している人数が表示されます。

出勤人数を計算する準備3

最後に入力したC15~C17のセルを選択した状態で、右下をAG列までドラッグして完了です。

出勤人数を計算する準備4

シフトの入力

いよいよシフトを入力するのですが、単純にA、B、Cを入力してしまうと表記ゆれが発生する上に入力の手間がかかります。そのためここではリスト機能を使用します。B5のセルを選択して「データの入力規則」ボタンをクリックし、入力値の種類から「リスト」を選択します。

シフトの入力

次に「元の値」の右側にあるボタンを押します。押すとセルを選択できる状態になるので、A20~A22のセルを選択してEnterキーを押して確定します。

シフトの入力2

シフトの入力2

確定させたらOKボタンを押します。

シフトの入力3

するとC5のセルに三角ボタンが表示されてリストからA~Cを選択できるようになります。これを他のセルにも適用させるために、C5のセルを選択してCtrl+Cでコピーします。コピーしたらShiftを押しながらAG15のセルを選択してCtrl+Vでペーストします。

シフトの入力4

全てのセルがリストからA~Cを選択できるようになるので、それぞれのシフトを選択して完了です。

シフトの入力5

抜粋の表示

なくてもシフト表として機能しますが、練習のためにどの人が一番出勤日数が多いか、または少ないかを抜粋として表示します。
A2のセルに「=INDIRECT("A"&MATCH(MAX(AH5:AH14),AH1:AH14,0))&"さんが出勤日数"&MAX(AH5:AH14)&"日で最多、"&INDIRECT("A"&MATCH(MIN(AH5:AH14),AH1:AH14,0))&"さんが出勤日数"&MIN(AH5:AH14)&"日で最小です。"」を入力します。

抜粋の表示

すると例では「浜崎 正征さんが出勤日数17日で最多、長野 政年さんが出勤日数8日で最小です」と表示されます。

抜粋の表示2

なぜ上記のようになるか順番に説明します。まず「=INDIRECT("A"&MATCH(MAX(AH5:AH14),AH1:AH14,0))」の「INDIRECT()」は文字列からセル参照を返す関数で、例えばA1に「あ」と入力されている状態でB1のセルに「=INDIRECT("A"&1)」と入力すると、B1のセルにも「あ」と表示されます。このように文字列でセルを参照させる際に使用する関数です。

次に「"A"&」は、Aとその後に続くものを連結するという意味です。例えば後に続くものが1の場合はA1となります。

次に「MATCH()」は範囲内で項目を選択して相対的な位置を返す関数です。「MATCH(検索値,検索範囲,照合の型)」という指定方法で、例えば「MATCH("apple",A1:A3,0)」と入力するとA1~A3の中でappleが入力されているセルのA1から数えた番号が返されます。最後に0を指定した場合、検索値と等しい最初の値を返します。

次に「MAX()」は範囲内の最大の数値を返す関数で、指定されているAH5~AH14のセルの最大値である18が返されます。
つまり

  1. 「=INDIRECT("A"&MATCH(MAX(AH5:AH14),AH1:AH14,0))」
  2. 「=INDIRECT("A"&MATCH(18,AH1:AH14,0))」
  3. 「=INDIRECT("A"&14)」
  4. 「=INDIRECT("A14")」
  5. 「=A14」
  6. 「浜崎 正征」

となります。
最小出勤日がMAX()がMIN()になるだけでやり方は同じです。
※同じ日数の人が複数いる場合は、1人目しか表示されません。

装飾

ここからは線や色を付けて見栄えを調整します。まずA1~AH1のセルを選択して「セルを結合して中央揃え」のボタン、「左揃え」のボタンの順番にクリックします。
※解説のために文字サイズがセルの幅を変更しているため、若干表示が異なります。

装飾

次に黄色で塗りつぶした上で太字にします。

装飾2

同様に日付や名前の項目を太字にします。

装飾3

次にA1~A17のセル以外を中央揃えにします。

装飾4

次にAH3~AH4、B19~E19、B20~E20、B21~E21、B22~E22のセルをそれぞれ結合します。
※「出勤日数」は勤日の間でAlt+Enterを押して改行します。

装飾5

次に罫線を付けます。A3~AH13のセルを選択した状態でCtrlを押しながらA19~E22のセルを選択し、罫線のボタンから「格子」を選択します。

装飾6

続けて不要な罫線を削除します。罫線のボタンから「罫線の削除」を選択します。

装飾7

そして5行目~17行目のAとBをドラッグして罫線を削除します。削除できたらEscキーを押して削除機能を解除します。

装飾8

罫線を引けたら次はセルに色を付けていきます。C3~AH4のセルを選択し、Ctrlを押しながらA19~B19のセルを選択します。そして灰色に塗りつぶします。

装飾9

次にA15~B15のセルを選択してオレンジ色、A16~B16のセルを選択して青色、A17~B17のセルを選択して緑色に塗りつぶします。

装飾10

装飾11

装飾12

次は条件付き書式を使ってセルを塗りつぶします。C5~AG14のセルを選択して「条件付き書式」のボタンを押して「新しいルール」を選択します。

装飾13

ダイアログボックスが表示されるので「指定の値を含むセルだけを書式設定」「セルの値」「次の値に等しい」を選択して「A」を入力します。入力したら「書式」をクリックします。

装飾14

さらにダイアログボックスが表示されるので「塗りつぶし」タブでオレンジ色を選択してOKを押し、前のダイアログボックスもOKを押して確定します。

装飾15

正しく設定できていればこのようになります。

装飾16

「A」を「B」「C」にすれば、Aと同様に色が付きます。

装飾17

次に出勤の人数が0の場合にセルが赤色になるように設定します。C15~AG17のセルを選択して「条件付き書式」のボタンを押して「新しいルール」を選択します。ダイアログボックスが表示されるので「指定の値を含むセルだけを書式設定」「セルの値」「次の値に等しい」を選択して「0」を入力します。入力したら「書式」をクリックします。

装飾18

さらにダイアログボックスが表示されるので「塗りつぶし」タブで赤色を選択してOKを押し、前のダイアログボックスもOKを押して確定します。

装飾19

このようになれば成功です。最後にA2~AH2のセルを結合させればエクセル関数を活用したシフト表の作成は終了です。
お疲れ様でした。

エクセルシフト表

出張/持込/宅配でパソコン修理・設定 24時間365日対応
消費税計算

税率を設定して税込/税抜金額の消費税計算ができます。

文字数カウント

文字数をカウントできます。

和暦西暦変換

和暦と西暦を相互変換できます。

年齢計算

和暦または西暦から年齢を計算できます。

入学年・卒業年計算

履歴書に必要な学校の入学年・卒業年を生年月日から計算できます。

単位変換(換算)

キロ、マイル、グラム、華氏などの様々な単位を相互変換(換算)できます。

カラーコード変換

カラーコード(16進数)とRGB値(10進数)を相互変換できます。

Webタイマー(カウントダウン)

Webタイマー(カウントダウン)です。ストップウォッチ機能もあります。

電話番号一覧

警察や消防などの緊急連絡先や電話番号案内などの電話番号を確認できます。

タスク管理(ToDo)

自分のWebブラウザだけでタスク管理(ToDo)ができます。

エクセル関数

エクセル関数を検索できます。

麻雀の点数計算

麻雀の和了時の点数(符数/翻数/役)を計算することができます。

便利なショートカット一覧

Windows 10やExcelなどで使用できる便利なショートカットを確認できます。

電気料金計算

消費電力、使用時間、使用日数、1kWh単価から電気料金を計算できます。

パスワード生成(作成)

大文字・小文字・数字・記号を含むランダムなパスワードを生成できます。

出張/持込/宅配でパソコン修理・設定 24時間365日対応
きょうみくん
このサイトの管理者
名前 きょうみくん
身長 181.1cm
誕生日 1月21日
所属 日本PCサービス
コメント パソコン、インターネット、サーモン、ミルクティーが好きです。
猫ではありません。