エクセル関数を活用したシフト表の作成方法
シフト表（勤怠管理表）は会社などのシフト管理で1つは作成すると思います。シフト表には日付計算、日数計算、書式設定など見た目とは裏腹に複雑なことを行います。
様々なエクセル関数を駆使してシフト表を作成することで、思い通りのエクセルシフト表を作成することができるようになるでしょう。
目次
完成するエクセルのシフト表
当ページで紹介している手順に沿って作成することで完成するシフト表のイメージです。
使用するエクセル関数
| 関数名 | 説明 |
|---|---|
| COUNTA | 範囲に含まれる空白ではないセルの個数を返す。 |
| COUNTIF | 1つの検索条件に一致するセルの個数を返す。 |
| DATE | シリアル値で表された日付の情報を返す。 |
| DAY | シリアル値を日付に変換する。 |
| IF | 条件によって処理をわける。 |
| INDIRECT | 文字列への参照を返す。 |
| MATCH | 範囲内で値を検索して相対的な位置を返す。 |
| MAX | 最大の数値を返す。 |
| MIN | 最小の数値を返す。 |
| TEXT | 表示形式コードを使用して数値に書式設定を適用する。 |
項目を入力する
まずは名前などの項目を入力します。
※名前は例です。実在する人物ではありません。
次に日付を入力します。C3のセルに1、D3のセルに2を入力し、D3のセルを選択した状態で右下をAD3のセルまでドラッグします。
※解説の都合上、列幅を狭めています。
次にAE3のセルに「=IF(DAY(DATE($A$3,$A$4,29))=29,29,””)」を入力します。これで設定している年月に29日が存在する場合だけ29日が表示されます。
次にAF3のセルに「=IF(DAY(DATE($A$3,$A$4,30))=30,30,””)」を入力します。これで設定している年月に30日が存在する場合だけ30日が表示されます。
次にAG3のセルに「=IF(DAY(DATE($A$3,$A$4,31))=31,31,””)」を入力します。これで設定している年月に31日が存在する場合だけ31日が表示されます。
次に曜日を入力します。C4のセルに「=TEXT(DATE($A$3,$A$4,C3),”aaa”)」を入力します。これで設定している年月と上記で入力した日付に対応する曜日を省略形式で表示します。
そして、C4のセルを選択した状態で右下をAD4のセルまでドラッグします。
最後にAE4のセルに「=IF(AE3=””,””,TEXT(DATE($A$3,$A$4,AE3),”aaa”))」を入力し、右下をAG4のセルまでドラッグします。これで29日～31日がそれぞれ存在している場合に曜日が表示されます。
このようになれば成功です。
出勤日数を計算する準備
次に出勤日数を計算する準備をします。AH3のセルに「出勤日数」と入力します。そしてAH5のセルに「=COUNTA(C5:AG5)」を入力します。
入力したAH5のセルを選択した状態で右下をAH14のセルまでドラッグします。
このようになれば成功です。
出勤人数を計算する準備
次に出勤人数を計算する準備をします。C15のセルに「=COUNTIF(C5:C14,”A”)」を入力します。これでシフトが入力されている場合に、勤務体系Aを選択している人数が表示されます。
続けてC16のセルに「=COUNTIF(C5:C14,”B”)」を入力します。これでシフトが入力されている場合に、勤務体系Bを選択している人数が表示されます。
続けてC17のセルに「=COUNTIF(C5:C14,”C”)」を入力します。これでシフトが入力されている場合に、勤務体系Cを選択している人数が表示されます。
最後に入力したC15～C17のセルを選択した状態で、右下をAG列までドラッグして完了です。
シフトの入力
いよいよシフトを入力するのですが、単純にA、B、Cを入力してしまうと表記ゆれが発生する上に入力の手間がかかります。そのためここではリスト機能を使用します。B5のセルを選択して「データの入力規則」ボタンをクリックし、入力値の種類から「リスト」を選択します。
次に「元の値」の右側にあるボタンを押します。押すとセルを選択できる状態になるので、A20～A22のセルを選択してEnterキーを押して確定します。
確定させたらOKボタンを押します。
するとC5のセルに三角ボタンが表示されてリストからA～Cを選択できるようになります。これを他のセルにも適用させるために、C5のセルを選択してCtrl+Cでコピーします。コピーしたらShiftを押しながらAG15のセルを選択してCtrl+Vでペーストします。
全てのセルがリストからA～Cを選択できるようになるので、それぞれのシフトを選択して完了です。
抜粋の表示
なくてもシフト表として機能しますが、練習のためにどの人が一番出勤日数が多いか、または少ないかを抜粋として表示します。
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日で最小です」と表示されます。
INDIRECT関数の意味
なぜ上記のようになるか順番に説明します。まず「=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が返されます。
つまり
- 「=INDIRECT(“A”&MATCH(MAX(AH5:AH14),AH1:AH14,0))」
- 「=INDIRECT(“A”&MATCH(18,AH1:AH14,0))」
- 「=INDIRECT(“A”&14)」
- 「=INDIRECT(“A14”)」
- 「=A14」
- 「浜崎 正征」
となります。
最小出勤日がMAX()がMIN()になるだけでやり方は同じです。
※同じ日数の人が複数いる場合は、1人目しか表示されません。
装飾
ここからは線や色を付けて見栄えを調整します。まずA1～AH1のセルを選択して「セルを結合して中央揃え」のボタン、「左揃え」のボタンの順番にクリックします。
※解説のために文字サイズとセルの幅を変更しているため、若干表示が異なります。
次に黄色で塗りつぶした上で太字にします。
同様に日付や名前の項目を太字にします。
次にA1～A17のセル以外を中央揃えにします。
次にAH3～AH4、B19～E19、B20～E20、B21～E21、B22～E22のセルをそれぞれ結合します。
※「出勤日数」は勤日の間でAlt+Enterを押して改行します。
次に罫線を付けます。A3～AH13のセルを選択した状態でCtrlを押しながらA19～E22のセルを選択し、罫線のボタンから「格子」を選択します。
続けて不要な罫線を削除します。罫線のボタンから「罫線の削除」を選択します。
そして5行目～17行目のAとBをドラッグして罫線を削除します。削除できたらEscキーを押して削除機能を解除します。
罫線を引けたら次はセルに色を付けていきます。C3～AH4のセルを選択し、Ctrlを押しながらA19～B19のセルを選択します。そして灰色に塗りつぶします。
次にA15～B15のセルを選択してオレンジ色、A16～B16のセルを選択して青色、A17～B17のセルを選択して緑色に塗りつぶします。
次は条件付き書式を使ってセルを塗りつぶします。C5～AG14のセルを選択して「条件付き書式」のボタンを押して「新しいルール」を選択します。
ダイアログボックスが表示されるので「指定の値を含むセルだけを書式設定」「セルの値」「次の値に等しい」を選択して「A」を入力します。入力したら「書式」をクリックします。
さらにダイアログボックスが表示されるので「塗りつぶし」タブでオレンジ色を選択してOKを押し、前のダイアログボックスもOKを押して確定します。
正しく設定できていればこのようになります。
「A」を「B」「C」にすれば、Aと同様に色が付きます。
次に出勤の人数が0の場合にセルが赤色になるように設定します。C15～AG17のセルを選択して「条件付き書式」のボタンを押して「新しいルール」を選択します。ダイアログボックスが表示されるので「指定の値を含むセルだけを書式設定」「セルの値」「次の値に等しい」を選択して「0」を入力します。入力したら「書式」をクリックします。
さらにダイアログボックスが表示されるので「塗りつぶし」タブで赤色を選択してOKを押し、前のダイアログボックスもOKを押して確定します。
このようになれば成功です。最後にA2～AH2のセルを結合させればエクセル関数を活用したシフト表の作成は終了です。
お疲れ様でした。
この記事を書いた人
