エクセルで勤怠表作成について教えてください

質問

エクセル２００７を使っています。

エクセルで勤怠表を作成するのですが、問題点があるので
お教え頂ければ幸いです。

１．交代勤務をしています。　早番・遅番・日勤・深夜勤があります。
　　この区別をしたいです。

２．遅刻、早退が多いので評価に反映させたいです。
　　極端ですが、１分の遅刻や早退でも、１５分単位として勤務時間を減らしたいのです。

３．時間の表現は、通常「８」時間、１５分単位としての遅刻、早退などがある場合は、
「７．７５」時間、「７．５」時間というように勤務時間を表示したいのです。

Ａ１列に出勤時間、Ｂ１列に退社時間というような標準的な入力としています。

よろしくお願いします。

回答No.8

KURUMITO

次の例を参考にしてはどうでしょう。
例えばシート1ではＡ1セルに氏名、Ｂ1セルに出勤時間、Ｃ1セルに退社時間、Ｄ1セルに勤務形態、Ｅ1セルに遅刻、Ｆ1セルに早退、Ｇ1セルには定刻の勤務時間が９時間としてそれを過ぎた超勤時間、Ｈ1セルに勤務時間の項目名がそれぞれ入力されているとします。Ａ、Ｂ，Ｃ列の2行目以降にはデータをそれぞれ入力するとします。
そこで初めに勤務形態を決めた時間表を用意することが必要になりますがそれらはデータベースとしてシート2に作成するとします。
シート2ではＡ2セルに早番、Ａ3セルに日勤、Ａ4セルに遅番、Ａ5セルに夜勤の項目名を入力します。
Ｂ1セルには出勤時間、Ｃ1セルには退社時間の文字を入力します。
そこで実際のデータを入力するわけですがここでは例えばＢ2セルから下方には6:00,8:00,10:00,22:00と入力し、Ｃ2セルから下方には15:00,17.:00,19:00,7:00と入力します。
これらの条件は勤務形態を決めるうえで非常に重要になりますね。
そこでシート1に戻ってＤ2セルには次の式を入力して下方にドラッグコピーします。

=IF(AND(B2>"5:30"*1,B2<"7:30"*1),"早番",IF(AND(B2>="7:30"*1,B2<"9:30"*1),"日勤",IF(AND(B2>="9:30"*1,B2<"12:00"*1),"遅番",IF(AND(B2>="21:30"*1,B2<="24:00"*1),"夜勤",""))))

この式では勤務形態が出勤時間を判断して決めるような形になっています。それができない場合には手入力であるいはリスト表示から入力する方法もありますね。
次にＥ2セルには次の式を入力して下方にドラッグコピーします。

=IF(D2="","",IF(B2<=INDEX(Sheet2!B:B,MATCH(D2,Sheet2!A:A,0)),0,CEILING(B2,"0:15")-INDEX(Sheet2!B:B,MATCH(D2,Sheet2!A:A,0))))

Ｆ2セルには次の式を入力して下方にドラッグコピーします。

=IF(D2="","",IF(C2>B2,IF(C2<INDEX(Sheet2!C:C,MATCH(D2,Sheet2!A:A,0)),INDEX(Sheet2!C:C,MATCH(D2,Sheet2!A:A,0))-FLOOR(C2,"0:15"),0),IF(C2>INDEX(Sheet2!C:C,MATCH(D2,Sheet2!A:A,0)),0,INDEX(Sheet2!C:C,MATCH(D2,Sheet2!A:A,0))-FLOOR(C2,"0:15"))))

Ｇ2セルには次の式を入力して下方にドラッグコピーします。

=IF(D2="","",IF(C2>B2,IF(C2>INDEX(Sheet2!C:C,MATCH(D2,Sheet2!A:A,0)),FLOOR(C2,"0:15")-INDEX(Sheet2!C:C,MATCH(D2,Sheet2!A:A,0)),""),IF(D2<>"夜勤",CEILING(C2,"0:15")+"24:00"-INDEX(Sheet2!C:C,MATCH(D2,Sheet2!A:A,0)),IF(C2>INDEX(Sheet2!C:C,MATCH(D2,Sheet2!A:A,0)),FLOOR(C2,"0:15")-INDEX(Sheet2!C:C,MATCH(D2,Sheet2!A:A,0)),""))))

Ｈ2セルには次の式を入力して下方にドラッグコピーします。

=IF(D2="","",("9:00"-E2-F2+G2)*24)

最後にＥ，Ｆ，Ｇ列についてはセルの表示形式は時刻から指定します。Ｈ列については標準にします。

お礼

ご回答いただきました皆様。
どうもありがとうございました。
今回は自分で解決となりました。

回答No.7

tom04

No.2・6です。

（1）遅刻と早退をしているところがあり、そこでは１５分と１５分で３０分を・・・
（2）一人、一月にばらばらのシフトで日勤、夜勤、休み、遅番、早番・・・

とありますので、前回の案に少し手を加えた感じで！

一人1Sheetになりますが↓の画像のようにA列に日付（単に数値のみ）を入れておきます。
Sheet2には前回同様表を作成し、Sheet2のA列部分をSheet1のプルダウンリスト表示できるようにしてみてはどうでしょう？

Sheet2のA2～A6セルを範囲指定　→　名前ボックス（画面左上のセル番地が表示されているところ）に
仮に　勤務形態　としてOK
（名前はどんな名前でも構いません。文字頭に数値・アルファベットは使用しない方が良いみたいです）

これでSheet2のA2～A6セルが「勤務形態」と名前定義されました。
Sheet1のB2セル以降を範囲指定　→　データ　→　入力規則　→　リスト　を選択　→　元の値の欄に
=勤務形態
としてOK
これでSheet1のB列セルがプルダウンでSheet2のA2～A6セルを選択できるようになります。

後は前回同様です
Sheet1のE2セルに
=IF(COUNTBLANK($B2:$D2),"",VLOOKUP($B2,Sheet2!$A$2:$C$6,COLUMN(B1),0))
という数式を入れ隣のF2セルまでオートフィルでコピー！

G2セルは
=IF(COUNTBLANK(C2:F2),"",SUM(IF(C2>E2,CEILING(C2-E2,"0:15"),0),IF(D2<F2,CEILING(F2-D2,"0:15"))))

H2セルは
=IF(COUNTBLANK(C2:G2),"","8:00"-G2)

として最後にE2～H2セルを範囲指定　→　H2セルのフィルハンドルで下へコピー！
これで何とかご希望の近い形にならないでしょうか？m(_ _)m

回答No.6

tom04

No.2です！
一つの案です。

↓の画像のようにSheet2に各勤務形態の定時出勤時刻・退勤時刻の表を作成してみてはどうでしょうか？

D2セルに
=IF($A2="","",VLOOKUP($A2,Sheet2!$A$2:$C$5,COLUMN(B1),0))
という数式を入れ、隣のE2セルまでオートフィルでコピー

F2セルに
=IF(COUNTBLANK(A2:E2),"",CEILING(IF(B2>D2,B2-D2,0)+IF(C2<E2,E2-C2,0),"0:15"))

G2セルに
=IF(F2="","",HOUR("8:00"-F2)+MINUTE("8:00"-F2)/60)
という数式を入れ、D2～G2セルを範囲指定　→　G2セルのフィルハンドルで
下へコピーすると、画像のような感じになります。

仮に数分だけ遅刻して、退勤時刻が定時より大幅に遅くても遅刻だけしか考慮していません。
（残業時間等の考慮なし）

※　D・E列が目障りであれば非表示にしても良いかと思います。

参考になりますかね？m(_ _)m

お礼

ご回答ありがとうございます。

画像付きでたいへん参考になりました。
ですが、遅刻と早退をしているところがあり、そこでは１５分と１５分で３０分を
カウントしなければなりませんが、１５分になっていますから、惜しいですね。
これをヒントに作成したいと思いますが、一人分で数日分しかありませんね。
一人、一月にばらばらのシフトで日勤、夜勤、休み、遅番、早番・・・と
続いていくので膨大になりそうです。

回答No.5

KURUMITO

遅刻や早退、早番、遅番などの複雑な計算については最近のＵＲＬを参考にしてください。

http://okwave.jp/qa/q7410058.html

お礼

ご回答ありがとうございます。

回答No.4

KURUMITO

回答Ｎｏ3です。
一部の表現が間違っていました。
また出勤時間については15分単位で処理をして余った時間は切り上げ処理をしています。

回答No.3

KURUMITO

15分単位で時間を処理する方法と7.5といったように勤務時間を表示させる方法について述べます。
例えばＡ2セルに出勤時間として9:05、Ｂ2セルには退社時間として17:40のように入力させているとしたらその勤務時間は

=(FLOOR(B2,"0:15")-CEILING(A2,"0:15"))*24

この式では退社時間については15分単位で処理するのですが余った時間は切り捨てをしています。また出勤時間については15分単位で処理をして余った時間は切り捨て処理をしています。
上記の場合には17:00から9:15を引いた時間となります。
7.5時間の表示にするためには時間の計算はシリアル値で行われ日にち単位の計算になっていますので24を掛けることで行っています。

お礼

ご回答ありがとうございます。

これだと深夜勤の場合は、マイナスになってしまいます。

回答No.2

tom04

こんにちは！
具体的なセル配置が判らないので・・・

（2）に関しては

=CEILING(遅刻時間+早退時間,"0:15")
（遅刻時間・早退時間はシリアル値です。0：31　のような感じ）

としてセルの表示形式は「時刻」でも構いませんが、
ユーザー定義から
[h]:mm　
としておいた方が良いかもしれません。

（3）については
仮にA1セルに「7:45」と表示されているとすると

=HOUR(A1)+MINUTE(A1)/60

として、セルの表示形式は「標準」にしておきます。

こんなんでどうでしょうか？m(_ _)m

お礼

ご回答ありがとうございます。

３については、同じ意見です。
２については、よくわかりません。

回答No.1

FEX2053

出勤時間をA1、退勤時間をA2にそれぞれ「日付シリアル」
（9:15 17:10 など、時刻表示のこと）で入力してあったとして。

=HOUR(A2-A1)+ROUNDDOWN(MINUTE(A2-A1)/15,0)/4

これで、セルの表示形式を数値、または標準にすれば、
「X.XX」時間として表示される筈です。

ただし、休憩時間のことは全く考えてませんので、その分を
どうやって控除するか、という問題はあります。

早番、遅番、日勤、深夜勤は、各々別の列に入力しておけば
いいだけなんですが、例えば出勤時間で判断できるなら、

=IF(A1>TIME(17,0,0),"深夜勤",IF(A1>TIME(12,0,0),"遅番","早番"))

などと設定すれば良いだけの話です。

お礼

ご回答ありがとうございました。

確かに出勤時間で○番なのか判断しますが、
早めに出てくるのはいいのですが、遅刻してくる人が多いので、
一概に断定できません。さらに、遅刻してその上早退する人もいますから、
困っています。