エクセルで作る見やすい出勤簿の作り方

「エクセルで見やすい出勤簿を自分で作成してみたい」と思う方は多いかと思います。

エクセルで見やすい出勤簿を作成してみましょう。

上記のような出勤簿の作り方を説明します。

• 土日が色分けされている
• 表の見出しは中央揃えになっている
• セル幅は必要最小限になっている
• 労働者のエリア間に境界線となる罫線が引かれている
• 目立たせたい箇所は太字になっている
• 1日/1ヶ月の実働時間（労働時間）が計算できる
• 1日/1ヶ月の残業時間が計算できる
• 1ヶ月の出勤/欠勤/有給/遅刻・早退日数がカウントできる
• 1ヶ月の遅刻/早退の累計時間が計算できる

作成の流れとしては、まず出勤簿のフォーマットを作成し、次に数式/関数を使って労働時間などを自動計算できるように設定します。

出勤簿のフォーマット作成方法

オリジナルの出勤簿を作成するには、以下の手順を踏むことをお勧めします。ここでは、3人分の勤怠内容を管理する出勤簿を作成する方法について説明します。

作業時間：20分

勤怠に関する項目を入力する

C2セルからT2セルまでのそれぞれのセルに、『勤怠内容』、『出勤』、『退勤』、『休憩』、『実働』、『時間外』をそれぞれ入力します。C3、I3、O3セルのそれぞれに『実働計』を入力します。

集計項目を入力する

V3からV8セルの列には、『出勤日数』、『欠勤日数』、『有給日数』、『遅刻/早退日数』、『遅刻/早退時間』、『残業時間』をそれぞれ入力します。

名前を入力する

C1、I1、O1セルに『それぞれ勤務者の名前』を入力します。W2、X2、Y2にも『同じ勤務者の名前』をそれぞれ入力します。

月初の日付と曜日を入力する

A4セルに『月の初めの日付（例：11月1日）』、B4セルに『曜日（例：月）』を入力します。

月末までオートフィルを行う

A4セル（11月1日）の右下にある四角にカーソルを合わせ、「11月30日」と表示されるまで【オートフィル】します。

曜日のオートフィルを行う

B4セル（月）の右下にある四角にカーソルを合わせ、11月30日の行まで【オートフィル】を行います。

実働計のセルを選択する

ここでは、「実働計」の色を目立たせたいので、【実働計のセル（C3からT3セル）】を選択します。

実働計の色を選択する

①【ホーム】タブ、②【「塗りつぶしの色」の「v」】、③【お好きな色（例：ゴールド）】の順に選択します。

土曜日の色を選択する

ここでは、土曜日の色を変更したいので、①土曜日の行にあたる【A9からT9のセル】、②【「塗りつぶしの色」の「v」】、③【お好きな色（例：水色）】の順に選択します。

Ctrl+Yで水色にする操作を繰り返す

他の土曜日も同じ色にしたいので、「11月13日の行」「11月20日の行」「11月27日の行」を選択し、繰り返しの操作を行う【Ctrl】+【Y】キーでそれぞれ水色にします。

日曜日の色を選択する

日曜日の色も変更したいので、①日曜日の行にあたる【A10からT10のセル】、②【「塗りつぶしの色」の「v」】、③【お好きな色（例：薄いオレンジ色）】の順に選択します。

Ctrl+Yでオレンジ色にする操作を繰り返す

他の日曜日/祝日も同じ色にしたいので、「11月3日」「11月14日」「11月23日」「11月28日」を選択し、繰り返しの操作を行う【Ctrl】+【Y】キーでそれぞれ薄いオレンジ色にします。

境界線を引く

ここでは、勤務する人のエリアに境界線となる罫線を引きます。①【Ctrl】キーを押しながら【ドラッグ】して境界線を引きたいセルを選択します。②【「罫線」の「v」】、③【左罫線】の順に選択します。

中央揃えにする

①見出しとなるセルは、【Ctrl】キーを押しながら【クリック】すると、離れているセルでも選択することができます。②【中央揃え】を選択します。

セルのスペースを調節する

文字列に対して、セルの隙間が開きすぎていたり、狭すぎたりすることがあります。列と列（例：B列とC列）の間で【マウスオーバー】し、【ダブルクリック】すると、文字に合わせてセル幅が調節できます。

目立たせたい文字を太字にする

今度は、目立たせたい文字（例：名前）を太字にします。①【Ctrl】キーを押しながら【クリック（ドラッグ）】して目立たせたい文字のセルを選択します。②【太字】を選択します。

出勤簿のフォーマットが完成した

出勤簿のフォーマットが完成しました。

数式/関数を使った勤務表の自動計算の入力

出勤簿のフォーマットが完成したら、エクセルの数式/関数を使って勤務表のデータを自動計算してみましょう。

今回は、労働時間が7時間30分を基本とする就業場所という前提で説明します。

1日の労働時間

出勤時間、退勤時間、休憩時間を計算して1日の労働時間を求める数式は「退勤時間-出勤時間-休憩時間」です。

数式の入力方法については、下記で説明します。

【1日の労働時間の計算結果を出したいセル（G4）】を選択します。

ここで、「退勤時間-出勤時間-休憩時間」の数式を使います。

数式バーに、『=E4-D4-F4』を入力し、Enterを押します。

G4セルに1日の労働時間（7:30）が表示されました。

1日の残業時間

8時間1分以上を残業時間とする場合、1日の残業時間を求める計算式は「労働時間-8時間」になります。

ただし、労働時間8時間を下回った場合は、残業時間はマイナスにならないような数式にしなければなりません。

以下のセクションでは、1日の残業時間を求める計算式の入力方法について説明します。

【残業時間を入力したいセル（H4）】を選択します。

ここで、1日の残業時間を求める計算式「労働時間-8時間」を入力します。

労働時間が8時間未満の場合は、残業時間がマイナスにならないような計算式にする必要があるので、ここではIF関数を使います。

数式バーに『=IF(G4-"8:00"<0,0,G4-"8:00")』を入力して、Enterを押します。

実働（9:30）-8:00で残業時間「1:30」が表示されました。

1ヶ月の労働時間

1ヶ月の労働時間を算出するには、SUM関数を数式バーに入力する他、ユーザー定義の表示形式を変更する必要があります。

詳しい手順は、下記をご覧ください。

【1ヶ月の労働時間を表示させたいセル（例：D3）】を選択します。

数式バーに『=SUM(G4:G33)』を入力し、Enterを押します。

1ヶ月間の合計時間数が表示されました。

1ヶ月の出勤/欠勤/有給/遅刻・早退日数

1ヶ月の出勤/欠勤/有給/遅刻・早退日数は、「勤怠内容」の列に記号をそれぞれ決めて計算するようにしましょう。

ここでは、1ヶ月の出勤/欠勤/有給/遅刻・早退日数を画面右上のエリア（例：織田の列）で表示させる関数を使ってみましょう。

1ヶ月の出勤/欠勤/有給/遅刻・早退日数を計算する鍵となるのがC列の記号です。

この記号を使って出勤/欠勤/有給/遅刻・早退日数の数を計算しますので、勤怠の入力の際は記号を入力するようにしましょう。

• 出勤：〇
• 欠勤：×
• 有給：有
• 遅刻：遅
• 早退：早

①【出勤日数を表示させたいセル（例：W3）】を選択し、②数式バーに『=COUNTIF(C4:C33,"〇")』を入力してEnterを押します。

①【欠勤日数を表示させたいセル（例：W4）】を選択し、②数式バーに『=COUNTIF(C4:C33,"×")』を入力してEnterを押します。

①【有給日数を表示させたいセル（例：W5）】を選択し、②数式バーに『=COUNTIF(C4:C33,"有")』を入力してEnterを押します。

①【遅刻・早退日数を表示させたいセル（例：W6）】を選択し、②数式バーに『=COUNTIF(C4:C33,"遅")+COUNTIF(C4:C33,"早")』を入力してEnterを押します。

1ヶ月の出勤/欠勤/有給/遅刻・早退日数の数値が一覧で表示されました。

1ヶ月の残業時間

1ヶ月の残業時間については、以下のとおりです。

【「残業時間」のセル（織田）】を選択します。

1ヶ月の残業時間を合計するには、時間外の列のH4セルからH33セルまで選択するようにします。

数式バーに『=SUM(H4:H33)』を入力し、Enterを押します。

1ヶ月の残業時間の合計「4:00」が表示されました。

1ヶ月の遅刻/早退の累計時間

1ヶ月の遅刻/早退の累計時間を算出する関数について説明します。

【遅刻/早退時間のセル（織田）】を選択します。

この関数は残業時間と遅刻・早退の時間が合計しないような関数を作る必要があります。

仕組みとしては、1ヶ月の出勤日数・有給日数・遅刻/早退日数に通常勤務時間の「7時間30分」をかけ、1ヶ月の残業時間を足して、1ヶ月の総労働時間を引きます。

関数は以下のとおりです。

="7:30"*(COUNTIF(C4:C33,"〇")+COUNTIF(C4:C33,"有")+COUNTIF(C4:C33,"遅")+COUNTIF(C4:C33,"早"))+W8-D3

『上記の関数』を数式バーに入力したら、Enterを押します。

遅刻/早退の合計時間数（2:30）が表示されました。

合計時間が正しく表示されない時は

合計時間が正しく表示されない時は、セルの書式設定で「[h]:mm」を入力する必要があります。

D3セルでは、その月の総労働時間数が「150:00」となるはずが、正しく表示されずなぜか「6:00」と表示されています。

こうしたトラブルに対処するには、①【ホーム】タブ、②【「数値の書式」の「v」】、③【その他の表示形式】の順に選択します。

「セルの書式設定」ダイアログボックスが表示されました。

①【表示形式】タブ、②【ユーザー定義】の順に選択します。

③「種類」のテキストボックスに『[h]:mm』を入力し、④【OK】ボタンを押します。

1ヶ月間の合計時間数が正しく表示されました。

勤務表の無料テンプレートをダウンロード

「勤務表を自分でエクセルで作成するのは大変。。」と思う方には、以下のセクションで本サイトオリジナルの無料テンプレートを用意しています。

末日締め

上記手順で作成した本サイトオリジナルの勤務表無料テンプレート（末日締め用）をダウンロードできます。

お好きなように編集してご愛用頂ければ幸いです。

このテンプレートは、多くの人が使いやすいように基本8時間労働に設定されており、残業時間や遅刻・早退の計算式も基本8時間労働をベースに作成しています。

勤務表末日締め用テンプレートをダウンロード

20日締め

上記手順で作成した本サイトオリジナルの勤務表無料テンプレート（20日締め用）をダウンロードできます。

お好きなように編集してご愛用頂ければ幸いです。

このテンプレートは、多くの人が使いやすいように基本8時間労働に設定されており、残業時間や遅刻・早退の計算式も基本8時間労働をベースに作成しています。

勤務表20日締め用テンプレートをダウンロード

このページを見ている人におすすめの商品

人事・労務担当者のためのExcel&Wordマニュアル

日本実業出版社
240ページ

87時間のビッグセール　タイムセール祭りAmazonで価格を見る

毎月5と0のつく日　楽天カード利用でポイント5倍楽天市場で価格を見る

対象ストア・商品限定クーポン　最大40％OFFYahoo!ショッピングで価格を見る