転職して時間ができたからブログでも書こう

SEから製造業に転職して通勤時間が1/5になり、勤務時間が3/5になり、そして給料は1/2になった"きゅぅ"の人生を謳歌するブログです。

パソコン関連

エクセルで勤怠表のテンプレートを作成。残業時間や深夜時間も計算してます

投稿日:2018-03-19 更新日:

以前フリーランスのSE時代に、ある企業に入り込んで作業してました。その時に使ってたエクセルの勤怠表をテンプレートとして公開します。よかったら使ってください。
なお、この記事でテンプレートの説明を行っている箇所がありますが、私のパソコンにエクセルはインストールされておらず、KingsoftのSpreadsheets2010がインストールされているので、コチラのソフトを使っての説明になります。

スポンサーリンク

エクセルで勤怠表のテンプレートを作ってみました。

先ずはココからダウンロードしてください。

このexcelには特にロック等をかけていません。なのでレイアウト変更などはご自由にどうぞ。

ダウンロードしたZIPファイルを解凍して開いてみるとわかりますが、中身はとても簡単なものです。入力するのは薄緑色のセルのみ。

では、簡単な説明を以下に書きまのでEXCELファイルを開いた状態で見てください。

日付

【A3】セルにその月の勤怠開始日を入力してください。すると、【A33】まで日付が展開されます。

A4~A33 までのセル内には 【 一つ上のセル+1】の数式が入っています。

例)【A4】セル  【=A3+1】

曜日

B列は曜日を表示していますが、数式はA列をそのまま参照してるだけです。
なので【B3】のセルには 【=A3】と書かれています

ではなぜA列は日付(月日)で表示され、B列は曜日を表示しているのでしょう?

これはセルの【書式設定】で表示方法を指定しているからなんです。

まず対象のセルをすべて選択後、右クリックし、【セルの書式設定】を選択します。するとセルの書式設定の小さなウィンドが開くので、【表示形式】タブの【分類】で【ユーザー定義】を選択し、その右側の【種類】のところに【aaa】と入力します。

これだけでそのセルの日付データは曜日表示に変わります。


余談ですが【(aaa)】というように括弧をつけると、表示内容は【(月)】のようにカッコつきで表示されます。

あと、日曜日を赤色で表示させるには、【条件付書式】で設定します。
設定方法は【書式】-【条件付書式】で

【数式が】 【=WEEKDAY(B3)=1】のときに文字の色を赤にするよう【書式】を設定しているだけです。

この WEEKDAY 関数は
1 = 日
2 = 月
3 = 火
4 = 水
5 = 木
6 = 金
7 = 土
となってますので、土曜日の色も変えたければ【追加】ボタンを押して条件を追加してあげれば良いです。

スポンサーリンク

有休、欠勤

【有休休暇】と【欠勤】の列は単純に日数を入れるだけです。そして合計欄に一ヶ月の合計を出しているだけですので大して難しいことはしていません。

【C34】や【D34】の合計欄も【=SUM(C3:C33】っていう感じで簡単な式になってます。。

出勤、退勤、休憩

ここには出退勤時間、休憩時間を60進法で記入します。

例)9時ちょうどの出勤の場合 【9:00】

例)17時30分 に退勤の場合 【17:30】

例)お昼休憩が 45分の場合 【0:45】

実働

この【実働】列を簡単に説明しますと

退勤時間 - 出勤時間 - 休憩時間 で計算され、10進数で表示します。

J3セルの数式を見てみましょう。

=IF(E3=””,””,HOUR(H3)+MINUTE(H3)/60)
①————— ②——————————–

①もしE3セルが空白なら空白を
②   空白じゃなければH3セルの 【時 + 分/60】をセット

■IF文

=IF(セルA=値1,値2,値3)
IF文は条件文 (ここでは セルA=値1 )を書いてカンマの後に条件を満たした時にセットする値、そしてもう一つのカンマの後に条件を満たしていないときにセットする値を書きます。実働時間【J3】のセルの中の式に
=IF(セルA=”星”,”★”,”●”)
と書かれていた場合、セルAの中身をみて、【星】という文字が書かれていればJ3には【★】を表示して、【星】意外の文字(空白も含む)であればJ3には【●】と表示することになります。

文章だけだとほんと難しいですね。

ここでの H3(H列) は実働時間を計算するためのセルにしています。なので普段は非表示にしています。
そしてこのH列は 退勤時間 - 出勤時間 - 休憩時間 で計算してあり、60進で表示しています。

J列の実働時間はH列の60進の実働時間を上記の式により、10進に変換しているのです。

例をあげてみましょう

出勤 9:00
退勤 18:00
休憩 45分
の場合、実働時間(60進)は 8:15(8時間15分)です。

これを上記の式 【HOUR(H3)+MINUTE(H3)/60) 】 に当てはめると

8 + 15 ÷ 60 = 8.25

となって 8時間15分 は 8.25時間 となるわけです。

なんだか文字ばかりで判りにくいですね。すみません。

エクセル勤怠で残業時間や深夜時間の計算式サンプル

時間外

時間外とは1日の規定時間を越えて働く場合に時間外手当てとして支払われる所謂【残業時間】のことです。
一般的には8時間超過分がそれにあたりますが、契約時間を任意に設定できるように【H1】に登録してある時間を規定時間としてみなすように扱っています。なのでとりあえずH1には【8】を設定しています。(必要に応じて変更してください)

時間外の計算はシンプルです。

=IF(J3=””,””,IF(J3<=$H$1,””,J3-$H$1))
①————— ②—————– ③———-

①もしJ3セルが空白なら空白を
②   空白じゃなく、H1の値以下なら空白を
③   そうじゃなければ【実働時間 – 規定時間】の値をセット

深夜

深夜は22時以降の作業時間を計算します。注意してほしいのは、時間外にも深夜が含まれることがあるということです。
時間外は8時間超の勤務時間を計算しており、深夜は22時以降の勤務時間です。

そしてこの【深夜】も【I1】セルに登録してある時間を基準にしており、それ以降の時間を計算しているので深夜時間の開始時間を任意に変更することが可能です。

深夜の計算もシンプルです。

まず、I列に22時(I1で指定した時間)以降の勤務時間を60進で計算し、その後10進に変換しています。

=IF(I3=””,””,HOUR(I3)+MINUTE(I3)/60)
①————— ②—————————–

①もしI3セルが空白なら空白を
②   そうじゃなければ I3セルの 【時 + 分/60】をセット

-パソコン関連

執筆者:


comment

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

関連記事

OPTIPLEX755が普通に起動しない。原因は電池切れなので交換してみた。

我が家ではまだまだ現役で活躍しているDELLの小型デスクトップパソコンOPTIPLEX755ですが、電源を入れた際に普通に起動しなくなりました。 BIOSが起動する以前に下記のエラーメッセージがでたん …

フィッシング詐欺

年次訪問者調査というタイトルでGoogleChromeのアンケートに答えてしまった

去年の1月の事ですが、久しぶり々フィッシングサイトに出くわしました。 スポンサーリンク 『おめでとうございます! あなたは、2017年 年次訪問者調査の参加者に特別選ばれました!是非、Chromeにつ …

写真の位置情報を変更するアプリを使ってみた。

デジカメではまだ少ないですが、スマートフォンなら殆どの機種でGPS機能が搭載されているので簡単に位置情報付きの写真を撮ることができますね。でも、実際にその機能をつかってみると案外位置がずれていることが …

パソコンのアップデートが始まらない

Windows7のOS再インストール後Windowsupdateが始まらない時の対処法

以前、Windows7(64bit)のOSを再インストール後、WindowsUpdateを行うと何分待っても処理が始まらないという現象に遭いました。 スポンサーリンク 実は最近、パソコン(Lenovo …

画像へのモザイクのかけ方はWindows標準アプリで簡単にできる

ブログに写真を載せる際、自分や家族、知人だけしか写ってなくて、全員がその写真をブログに載せる事を了承していれば問題ないですが、赤の他人が写っていたりすれば勝手に写真を載せることはできません。肖像権やプ …