エクセルでの複雑なタイムカード計算を教えて下さい！

質問

毎月60名程のタイムカード集計をしております。
現在は、計算法が複雑の為、ほぼ手計算をしており、集計日には夜中までかかっており困っております。よろしければ、エクセルでの関数を使っての集計が出来れば教えて頂けないでしょうか？
お願い致します。

店舗のシフト
(1)（開店シフト）9：00～14：30
(2)（閉店シフト）14：30～20：00
(3)（中間シフト）13：00～18：00
が基本のパターンで　計算単位は10分単位です。

(3)（中間シフト）は10分単位で私でもエクセル関数を使って出来るのですが、

(1)（開店シフト）と(2)（閉店シフト）に複雑さがあり私のエクセル知識では解りません。

(1)（開店シフト）(2)（閉店シフト）の計算内容

(1)（開店シフト）は基本的な計算は開店時間から

例　A店の場合9：00開店　　　出社8：45　　　退社14：38ならば　　　　　　　5時間30分
　　　　　　　　　　　　　　　　　　　　　9：00　　　　14：30（10分単位）　　　

(2)（閉店シフト）の計算は、閉店処理として10分とつけ、その他、閉店時間後40分を超える場合
　は、すべて計算。　
例　　20：00閉店の場合　　　　出社14：25　　　　　　退社20：20　　　　　　　4時間40分
　　　　　　　　　　　　　　　　　　14：30（10分単位）　　閉店処理分10分
　閉店処理が40分を超える場合

例　　20：00閉店の場合　　　　出社14：25　　　　　　退社20：58　　　　　　　5時間20分
　　　　　　　　　　　　　　　　　　14：30（10分単位）　　20：50（10分単位）

その他補足
開店時間、閉店時間は店舗により異なります。
尚且つ、日曜。祝祭日でも異なります。　9：00開店20：00閉店が日・祝は10：00開店19：00閉店

以上がエクセルの関数で出来るでしょうか？
よろしくお願いいたします。

追伸
　各店舗、同じ様な売上でも、退社時間に個人差があまりにも格差が生じる為、このような計算法に
　行き着きました。ややこしくてすみません。

回答No.3

tom04

こんばんは！

具体的な表のレイアウトが判らないので、やり方だけ。
勝手に↓のような感じにしてみました。
一人に付き、6行使用しています。

＞(2)（閉店シフト）の計算は、閉店処理として10分とつけ・・・
とありますので、画像ではA2～E5の表で
平日・日祝日の閉店時刻に10分をプラスした表を作成しています。

まず準備として
（1）祝日は別Sheetにシリアル値で作成し、「祝日」という名前定義しておきます。
（2）A1セルに西暦の年の数値・C1セルに該当月の数値を入力します。

C7セル（セルの表示形式はユーザー定義から　d　としておく）に
=IF(MONTH(DATE($A1,$C1,COLUMN(A1)))=$C1,DATE($A1,$C1,COLUMN(A1)),"")
という数式を入れます。

C8セル（セルの表示形式はユーザー定義から　aaa　としておく）に
=IF(C7="","",C7)
として　C7・C8セルを範囲指定　→　C8セルのフィルハンドルで31日のAG列までオートフィルでコピーしておきます。
これで下準備は完了です。

次に入力する行は　9～11行のセル（9行目は入力規則のリスト設定をしておくと、いちいち入力しなくて済みます）

C12セルに
=IF(C10="","",IF(OR(WEEKDAY(C$7)=1,COUNTIF(祝日,C$7)),MAX(VLOOKUP(C9,$A$3:$E$5,4,0),CEILING(C10,"0:10")),MAX(VLOOKUP(C9,$A$3:$C$5,2,0),CEILING(C10,"0:10"))))

C13セルに
=IF(C11="","",IF(OR(WEEKDAY(C$7)=1,COUNTIF(祝日,C$7)),MAX(VLOOKUP(C9,$A$3:$E$5,5,0),FLOOR(C11,"0:10")),MAX(VLOOKUP(C9,$A$3:$C$5,3,0),FLOOR(C11,"0:10"))))

C14セルに
=IF(COUNTBLANK(C12:C13),"",C13-C12-"1:00")

C14セルはおそらく休憩時間が1時間あるものとして1時間分マイナスしています。
（そうでないと質問通りの時間にならないため）

最後にC12～C14セルを範囲指定　→　セルの表示形式はユーザー定義から　[h]:mm　とし
フィルハンドルでAG列までコピーすると
画像のような感じになります。

※　遅刻に関しては10分刻みで対応できると思いますが、早退については考慮していません。
それを考慮したい場合、C13セルの数式がもう少し長くなります。

※　画像では一人分ですが、複数名同じ表を作る場合、
画像の9行目～14行目の6行をすべて範囲指定　→　右クリック　→　コピー　→　15行目でも16行目でも好みの行のA列に貼り付け！
これで数式はそのまま使用可能です。
人数が多い場合は　9行目すべてを範囲指定　→　ウィンドウ枠の固定　を設定すれば
8行目までは常に表示されます。

※　一発で解決！とはいかないと思いますが、たたき台として・・・m(_ _)m

回答No.2

kagakusuki

不明な点が多過ぎて、回答に必要な情報不足が不足しておりますので、追加情報を御補足願います。

●そのタイムカードを作成するためのExcelbookは、一体どの様なレイアウトになっているのか？

●出社時刻と退社時刻は、それぞれ、何列の何行目から入力欄が始まっているのか？

●「開店シフト」、「中間シフト」、「閉店シフト」の区別を付けるためのデータは、どの列のセルにどの様な内容で入力されているのか？

●「閉店処理として10分とつけ」とは、「退社時間が閉店時間後40分を超えていない場合には、退社時間を一律で『閉店時間＋10分』と見做す」という意味なのか、それとも別の意味なのか？

●日付はどのセルにどのような形で入力されているのか？
　　（例えば、A列に「2014/1/7」の様な形式で入力されているとか、或いは、B1セルに「2014」、D2セルに「1」と入力されていて、A列には1～31の数字が並んでいるだけとか）

　又、「計算単位は10分単位」という条件に関しても、色々な異なるパターンが考えられます。
●単純に退社時刻から出社時刻を差し引いた時間を10分単位で切り捨てる
●単純に退社時刻から出社時刻を差し引いた時間を10分単位で切り上げる
●単純に退社時刻から出社時刻を差し引いた時間を10分単位で四捨五入する
●退社時刻を10分単位で切り捨てた値から出社時刻を10分単位で切り捨てた値を差し引く
●退社時刻を10分単位で切り捨てた値から出社時刻を10分単位で切り上げた値を差し引く
●退社時刻を10分単位で切り上げた値から出社時刻を10分単位で切り捨てた値を差し引く
等々、上記以外にも様々なパターンが考えられますが、どの様な方式の「10分単位」なのでしょうか？

回答No.1

gagagapipipi

たぶん、IF式と関数のTIME、CEILING、FLOORを使うことで可能です。

IF式の理屈は分かると思うので、他の関数の説明。

TIME（時間,分,秒）→時間を表示する関数、
TIME(9,0,0)→9:00:00

CEILING(数値,基準値）→指定した倍数のうち、最も近い値に数値を切り上げる、
CEILING(7,10)→10
CEILING(TIME(9,05,00),TIME(0,10,0))→9：10：00

FLOOR(数値、基準値）→指定した倍数のうち、最も近い値に数値を切り下げる、
FLOOR(15,10)→10
FLOOR(TIME(9,05,00),TIME(0,10,0))→9：00：00

秒はいらないので表示形式をh:mmにして時間と分だけの表示にします。

でまず出社時間（タイムカードを押した時間）とそれに対応した出社調整時間のセル、
退社時間とそれに対応した退社調整時間のセルを作ります。
出社時間A列、出社調整時間B列、退社時間C列、退社調整時間D列とします。

でAとCにタイムカードの時間を入力し、BとDに関数を入力します。

Bに入れる関数
どんなに早くきても始業時間は９時というのであればIF式で９時前に来た場合と９時以降に来た場合分けをします。
IF(A1<TIME(9,0,0),TIME(9,0,0),CEILING(A1,TIME(0,10,0)))
もしAが９時前であるなら9：００に、それ以外なら１０分ごとの切り上げ

Dに入れる関数
閉店時間前に帰る人＆閉店時間後４０分以上残っている人は１０分切り下げ、閉店してから４０分以内に帰る人は閉店時間＋１０分、ということであれば
IF(AND(C1>TIME(20,0,0),C1<TIME(20,40,0)),TIME(20,10,0),FLOOR(C1,TIME(0,10,0)))
もしC1が20:00より大きく20:40より小さいなら20:10、それ以外なら１０分切り下げ

で勤務時間は退社調整時間-出社調整時間→D-B、さらに休憩１時間ある人ならD-B-TIME(1,0,0)
これもIF式で休憩ある場合とない場合に分ければ大丈夫です。

数式が長いのが嫌ならF列に開店時間、G列に閉店時間を書いて

出社調整時間＝IF(A1<F1,F1,CEILING(A1,TIME(0,10,0)))
退社調整時間＝IF(AND(C1>G1,C1<G1+TIME(0,40,0)),G1+TIME(0,10,0),FLOOR(C1,TIME(0,10,0)))

F列とG列の行に違う開店時間、閉店時間を作っておけば応用できるかと思います。

もしかしたらもっと簡単な方法があるかもしれないですけど、今思いついたので書いてみました。