ｴｸｾﾙでﾀｲﾑｶｰﾄﾞの集計がしたいのですが

先任の退職につき､ﾀｲﾑｶｰﾄﾞの集計業務をすることになりました｡
私の会社は勤怠状況が複雑な零細企業ですが､従業員は26名ほどいます｡
遅刻･早退･中間外出､残業･深夜･休日出勤 等が頻繁にあります｡
ﾀｲﾑｶｰﾄﾞの計算程度に考えていましたが､複雑な出勤状況もあり､計算のミスが多く､何より集計時間に時間が掛りすぎてしまい､他の業務にも影響が出始めています｡ｴｸｾﾙにて集計表を作成しようとしましたが､1ヶ月経ってもうまく表示できません｡どうかお助けください｡

条件
始業8:00 終業17:00 休憩10:00~10:10 12:00~13:00 15:00~15:10 深夜22:00~29:00
打刻時間　出勤　8:00前 30分単位　所定時間内 5分単位　12:00~13:00 30分単位
　　　　　　　退勤　所定時間内 5分単位　12:00~13:00 30分単位　17:00以降 5分単位
所定時間 7時間40分　残業は30分から発生(17:29退社は残業0)　1日の区切り 5:00から24時間｡
法定内労働時間
　平日： 8:00~19:00勤務は 法定内 0:20・法定外 1:40
　半給：13:00~21:00勤務は 法定内 3:50・法定外 0:10
　遅刻：13:00~21:00勤務は 法定内 3:50・法定外 0:10・不就労 3:50）早退･外出も同じ考えです｡

A1 日付（事前入力）
B1 曜日（事前入力）
C1 当日の勤務種別（空白=平日・1＝所定休日・2＝法定休日・3＝半日有給・4＝1日有給）（入力）
D1 始業時刻（入力）
E1 途中出時刻（入力）
F1 途中入時刻（入力）
G1 終業時刻（入力）
H1 法定内労働時間（平日 所定労働時間を除く､実労働時間が8時間を超えない労働時間）
I 1 法定外労働時間（平日 5時から22時の間で所定時間を省いた労働）+（所定休日労働）
J1 深夜労働時間(22:00~29:00の間の労働)
K1 法定休日労働時間(法定休日は日曜なので､日曜日の労働時間)
L1 不就労時間（半日有給は含まない｡所定時間内での不就労時間）

まだ私も完全に把握していないほど複雑で､無理なのかもしれませんが､どうかよろしくお願いします｡

質問者からの補足コメント

• 

  ご教授にあたり製作における表示､情報､希望打込作業等を補足します｡
  最終表示項目
  ①従業員　氏　　名　横方向に25名ほど
  ②法定内　残業時間　実働の1日の法定労働時間時間(8時間)を超えない所定労働時間外労働
  ③法定外　残業時間　実働の1日の法定労働時間時間を超える労働時間 (昼休憩･深夜時間内作業 含)
  ④深　夜　残業時間　22:00~29:00(翌5:00)の間の労働時間
  ⑤法定休日労働時間　日曜日の労働時間(振替があるので日曜日とは限らない）
  ⑥不　就　労　時間　所定労働時間内の不就労（有給休暇は含まない）
  ⑦有給休暇取得日数　有給休暇 1日=1 半日=0.5
  ※ 所定労働時間(8:00~10:00 10:10~12:00 13:00~15:00 15:10~17:00 の間の労働)
  ※ 中間外出あり
  ※ ①②は実働なので半日有給･不就労は含まない｡平日残業発生時は常に20分

    補足日時：2017/02/24 16:52

No.24 回答者： yokomaya 回答日時： 2017/03/04 19:50

実行いい加減だった。

ごめんなさい。

ツール→マクロ→実行　　でもいいし
実行→SUB/ユーザーフォームの実行
どっちでも大丈夫です。
一分ほど実行中で操作できないと思う。

このマクロがどうこうじゃなくて、
動作結果を見てもらいたいなと
検証の難儀さを伝えたいだけだから。

＞当日8:00~29:00で翌日5:00~中出6:00中入8:00~17:00で処理してます。

だから尋ねたいのは$G14=""ってのが
打刻されてないにしろ29：00でいいんでしょう？
だったら入力しないって事は考える必要ないんじゃない？ということ

中出6:00中入8:00って何？
必ず帰るの？それとも居るのにカウントしないの？
どっちにしても意図が・・・・。

＞小さな会社なので顧客の要望は、ほぼ絶対です。

この思い込みを打ち破れる会社が多分伸びれるんだけどね。
人間関係でもそうだけど、言いなりの人を大事にするかと
いえば、そんなことないから。
肝心なところで信頼できるかどうかで、それは例えば
お客様は神様といったところで、モンスタークレーマーに
引きずりまわされてたら、ほかの大多数へのサポートが
おろそかになるし、どうやってより付加価値を上げるか、
相手にとってより不可欠な存在になるかが一番だと思うんだけどね。
あなたにこんなこと話しても始まらないのは判っているけど、
そういわれることを妄信する必要はないことは伝えたい。
が、この件とは何も関係しないので次へ。

とにかくね、長い数式の件だけど
コメントで説明できないようなら、もうそれだけで
不適切なんじゃないかなぐらいに、僕は思います。
例えば今の時点で悩んでたら、一年ぐらいして微妙に
ルール変更言われたりしたら、どこをどう触ったらいいのか
途方に暮れちゃうんじゃないかな？
如何でしょう？

この回答へのお礼

yokomaya様　お世話になります｡

すみません。　頭が許容オーバーしたらしく、熱をだしてしまいました。
お礼が遅くなり申し訳ございません。

深夜の計算式で就業時刻の空白確認は確かに深夜時間計算には必要ないです。
終業が22:00より前か後かの確認が適切でしょうか。

終業が22:00前 = 0:00
途入が22:00前 = 終業 - 22:00
途出が22:00前 = 終業 - 途入
始業が22:00前 = 終業 - 途入 + 途出 - 22:00
始業が22:00後 = 終業 - 途入 + 途出 - 始業
だと思うので

N14=IF(K14<"22:00",0,IF(J14<"22:00",K14-"22:00",IF(I14<"22:00",K14-J14,IF(H14<"22:00",K14-J14+I14-"22:00",K14-J14+I14-H14))))

なんか余計に長くなってしまいました。さらに希望の数値が出ない。
何が悪いのでしょう?

MAX(0,MIN("10:10",IF(I14="",H14,I14))-MAX("10:00",H14))
すみません。まだ把握できていません。どこかのサイトから引用しただけなので
式の意味までは理解していませんでした。　確かに後で変更等あればどうにも
ならなくなりそうですね。がんばります。

マクロ
インデックスが有効範囲にありません
のエラーメッセージが出てしまいます。何が悪いのでしょうか?

宜しくお願いします。

お礼日時：2017/03/05 20:04

No.23 回答者： yokomaya 回答日時： 2017/03/04 16:11

そういえば30年ほど前に、勤務的には似たようなこと

僕もしてたなぁ。
定時前後に帰っても。22時くらいにトラブルで呼び出されて
何時間か・・。当時はタイムカードなくて申告制だったから
気に留めなかったけど、そんなケースあるの？

会社により諸事情は色々かもな。

もしかしてｇ１４がないのに出勤みたいな
そのまま翌日につながるケースあるの？
ただそれは29：00手書きすればいいと思うな。

ってかタイムカード24：00以上は多分表示しないんじゃない？
もし、しないなら入力をHに変換する時処理するほうが合理的かも。

で、ずっと言ってた検証のVBAの話ね。
まだ、全然途中で申し訳ないんだけど。
Sheet3を検証用に準備してもらって
a1からに
　　　　╲ 時刻
始業時間 8:00
終了時間 29:00
ステップ 0:17
をセットしてもらい。（時間がB2:B4になるようにしてね）

今の勤怠管理(2)のシートかな
シート名を右クリック→コードの表示で表示された白いところに
以下を貼り付けて

Sub 検証()
Dim ws As Worksheet
Dim T1 As Double, T2 As Double, Dest As Long, st As Double, T3 As Double

Dest = 10
Me.Activate
Application.ScreenUpdating = False
With Worksheets("Sheet3")
st = CDbl(.Range("B4").Value) 'ステップ時間
T1 = .Range("B2").Value '開始時間
Do While T1 < CDbl(.Range("B3").Value - TimeSerial(2, 0, 0)) '終了時間の2時間前まで

If T1 < CDbl(TimeSerial(7, 0, 0)) Then
T2 = CDbl(TimeSerial(8, 0, 0))
Else
T2 = T1 + CDbl(TimeSerial(1, 0, 0))
End If
T3 = CDbl(.Range("B3").Value) '直接比較したらエラーになるので回避
While T2 < T3
With Worksheets("Sheet1")
.Range("D14").Formula = CDate(T1)
.Range("G14").Formula = CDate(T2)
.Range("D14:AZ14").Copy
End With
.Cells(Dest, 4).PasteSpecial xlPasteValues
T2 = T2 + st
Dest = Dest + 1
Wend
If Dest > 2000 Then Exit Do
T1 = T1 + st
Loop
End With
Application.ScreenUpdating = True
End Sub

貼り付けた画面の上にあるマクロの実行で実行する。

これで前のd14,g14を色々
変化させたとき後ろがどう変わるか連続的に見れるから。
（Sheet3の10行目以降）
数式の検証は大変だけど、なにがしかしないと
自分で考えで作った式は、自分の想定の範囲内の動作しか
しないから、考えの見落としを見つけるのはとても難しいです。

まあこのままじゃまだ多すぎて全部できないから
一応２千件で打ち切ってます。
見る元気もないしね。
なんか特徴的な時間テーブルを使って食わせるほうが
合理的かもな。そこはもうちょい検討ということで。

僕としては、長すぎる式が嫌なんです。
見直す気力がなくなるし。

で、S14僕はまだまとめてないって言ったけど
まとめないほうがいいと思うのね。
こうしてテストするとき値がおかしかったら
どこが間違っているのか探すのが困難だから。

この回答へのお礼

yokomaya様　お世話になります｡

＞・・・そんなケースあるの？
　あります。ほんとに小さな会社なので顧客の要望は、ほぼ絶対です。
　私たち事務は滅多に無いですが、現場作業員は一旦帰ってからの呼び出しや
　休日の夜に呼び出し等、基本何でもあり状態です。
　タイムカードは24:00以降も表示(0:00で)しますが、退勤が押してあると
　翌日の出勤になってしまいます。ただ、手書き記入は大丈夫なので入力時は
　問題ありません。
　※ 日をまたぐとき
　　当日8:00~30:00(6:00)で翌日8:00~17:00 は
　　当日8:00~29:00で翌日5:00~中出6:00中入8:00~17:00で処理してます。

＞貼り付けた画面の上にあるマクロの実行で実行する。
　シート1＝勤怠管理　シート2＝勤怠管理計算データ　シート3＝勤怠管理(2)なので、シート4に
　　╲ 時刻
　始業時間 8:00
　終了時間 29:00
　ステップ 0:17
　を入力します。　　・・・すみません。"マクロの実行" が何処にあるのか見つからないです。
　"ﾃﾞﾊﾞｯｸﾞ" と "ﾂｰﾙ" の間の "実行" で良いのでしょうか?
　マクロは未経験です。
　
宜しくお願いします。

お礼日時：2017/03/04 17:26

No.22 回答者： yokomaya 回答日時： 2017/03/04 11:54

N14も長いなぁ

IF($G14="",0,MAX(0,MIN("29:00",IF($I14="",$H14,$I14))-MAX("22:00",$H14))+MAX(0,MIN("29:00",$K14)-
　　MAX("22:00",IF($C14="",$H14,$J14))))
まずさぁ
退出が未入力の時計算されちゃう、そのこと自体が間違っていると
考えるべきじゃないかな？最初のG１４判定はいらないと思う。

基本、退出が22：00過ぎてたらその時間で何がいけないの？

途入、途出、関係ありそうに思えないし。
22：00過ぎて途入って、一般人としておかしくないですか？
泥棒じゃないんだから。

No.21 回答者： yokomaya 回答日時： 2017/03/04 10:38

〇IFERROR話が伝わらなくてすみません。

=MATCH(C5,種別,0)
がエラーで困るなら
=IFERROR(MATCH(C5,種別,0),0)
で如何ですかの意です。


〇それが仮設のz14ならP14が
=IF(OR(Z14={1,2,4}),0,MAX(IF(Z14=3,"3:50","7:40")-R14,0))
なとこかな。

〇でね、S14の長い式ね、僕とこではまだ6つに分けてるんだけど
二つづつ見て前の式は0以外にならないみたいだから

意図の確認をしたいんだけど
MAX(0,MIN("10:10",IF(I14="",H14,I14))-MAX("10:00",H14))
もし途入が""なら途出、でなければ途入みたいのがあるんだけど
これって""ならもひとつ絶対""じゃないのかな？

で仮にH14に時間あるとして
それが9：55としよう。
それと10：00のMAXは10：00だから
9：55から引くと負だよね。
逆に10：15としたら
と10：00のMAXは10：15になって
10：15から引いたら0
MINの答えは0か負となれば、それと0のMAXは0にしかならないのでは？

MAX(0,MIN("10:10",K14)-MAX("10:00",IF(J14="",H14,J14)))
こっちは基本0か0：10になってくれます。
ただもし途入が""なら途出、でなければ途入が
ひっかかるけど。

この回答へのお礼

yokomaya様　お世話になります｡

＞〇IFERROR話が伝わらなくてすみません。
　今使い方を理解しました。＞＿＜
　式に直接ではなく、ｚの列を使うって事ですね。納得です。

＞意図の確認をしたいんだけど
　すみません。　只今頭をフル回転で確認中です。
　確認取れ次第連絡入れます。

宜しくお願いします。

お礼日時：2017/03/04 17:46

No.20 回答者： yokomaya 回答日時： 2017/03/02 18:23

> 後から無駄な行を削除は可能ですか?

今のところは問題ないと思います。
下へのオートフィルが正常に作動していれば開始行以前の
行挿入、行削除は関係ありませんから。
注記はコメントを使うのも便利ですけどね。
普段は小さいマークだけでマウスを乗せた時だけ表示され、
位置も自在ですし。

MATCHの件はIFERRORですよね、エラーの場合は0でいいんじゃないでしょうか？
解決済みかもね。

この回答へのお礼

yokomaya様　お世話になります｡

>注記はコメントを使うのも便利ですけどね
　とても使いやすいです。　シートがすっきりしました。
　ただ､どうしても休憩時間のリストが11行使ってしまうので
　検証用シートは14行目入力でお願いします。

>エラーの場合は0でいいんじゃないでしょうか？
　例えば "不就労時間"
　P14=IF(OR($C14=1,$C14=2,$C14=4),0,MAX(IF($C14=3,"3:50","7:40")-$R14,0))
　の場合
　MATCH($C14,種別,0)をどのように組込めば良いのでしょう?
　P14=IF(MATCH($C14,種別,OR(1,2,4)),0,MAX(IF(MATCH($C14,種別,3),"3:50","7:40")-$R14,0))
　では駄目でした。
　種別が 所定休日･法定休日･1日有給 の時は 0:00
　種別が 半日有給 の時は 3:50 そうでない時(平日)は7:40から所定内労働時間(実働)をひく
　の計算式です

以下の計算式に修正をいれました
　深夜労働時間
　　N14=IF($G14="",0,MAX(0,MIN("29:00",IF($I14="",$H14,$I14))-MAX("22:00",$H14))+MAX(0,MIN("29:00",$K14)-
　　MAX("22:00",IF($C14="",$H14,$J14))))
　法定休日労働時間
　　O14=IF(AND($C14=2,$H14<>""),$Q14,0)
　所定内労働時間
　　R14=IF(OR(COUNT($H14:$K14)=0,$C14=1,$C14=2,$C14=4),0,MAX(N($I14)-MAX("8:00",$H14)+MIN("17:00",$K14)-
　　N($J14)-N($S1　4),0))

宜しくお願いします。

お礼日時：2017/03/02 23:16

No.19 回答者： yokomaya 回答日時： 2017/03/01 01:33

d列の件は僕が1列横に貼り間違えただけだった・・・。

アブない奴かも・・・。(-_-;)

しかしながら、行間を読むという言葉が日本語には
あるけど、あまり細々したことまで言うのが押しつけがましい
ような気がしてるのか、肝心なことが言えてないみたいで
伝わらない。

〇
＞そこの説明してくれないと数式がわかんないけど。
というのはここまで５行目に書いてた数式が４２行目で
届いたけど、これからは何行目で書くと統一したら
いいですか？
というのが主旨だったんだけど、そこを言ってないから
まさかの14行目で戻ってきた。
きっちり尋ねます。
14行目で統一でいいでしょうか？

〇0:00（整数？）という記述がありました。
（最初に謝ったように、この話は僕の勘違い発言からきてるんだけど）

そしてこれが実は稀な整数だから、説明がややこしくなるけど・・。
あえて説明しといたほうがいいと思うので。

日付を縦に並べる時に
=a5+1
を下にオートフィルしたのは覚えてるよね？
覚えてなくてもa15の数式見ればわかるけど。

これ漠然とそんなものかと思ってしまいがちですが、
実は、時間の長さというのは１日を1とするというEXCELの
基本ルールがあるので上の数式が成立しています。
つまり12：00なら0.5、18:00なら0.75ってこと
その結果0：00は0という整数になります。

因みに同じ考え方で日付も表していて
1を１９００年１月１日として、そこから数えて何日目かという
数字で、例えば今日２月２８日は42794らしい。
ここは表示形式を日付と整数で切り替えするとすぐ
確かめられるので試しに見ておいてください。
この日時を４万なにがしの数値で持つことを「シリアル値」って
言い方するから覚えておいてもいいかも。

2000年にはまだ36500ちょっとだったのに（約100倍だからね）
月日ってすんごい勢いで流れていく。

脱線してるので戻します。

〇a12の返事で
＞C列はリスト入力で行こうと思っています。
を否定されていないと思っていたので
=MATCH(C5,種別,0)
のくだりを書きました。

ところがc14を数値判定されている事から
あえてsamumachaさんの、言葉に出ていない行間を
想像すると

いつまでも古い話言ってんじゃないよ。
文字判定してたらめんどくさいからコード化に切り替えたんだ。
なにごちゃごちゃ言ってんの？

みたいな。(笑)

でもあえて、今一度ｃ１４でリスト入力試してくれません？
僕が書いたのは例えばｚ１４に
=MATCH(C14,種別,0)を書いておけば
ｃ１４でなくｚ１４を判定することでリスト入力でも数字で
IF判定の記述が出来る。そこまで見てもらってから
やっぱりコード化で行きますなら、やむを得ないけど、
スルーは、なんだかなぁ。と思っています。

同時に、ここはリスト入力を言ってくれた時に、
すぐ書いておくべきだったことも併せて謝ります。

〇休憩のsumって6マス使ってたでしょ。
まだ数式評価がちゃんと出来てないのに正確でなくて
あれだけど、休憩って3回しかないでしょ。
だから多分三つでいけるはずなんだ。
そこは、評価できたら説明します。

〇それとVBAで評価しているので、それについてもどんな感じか
次回伝えたいと思います。
今シート名はSheet1から名前を付け替えてます？

この回答へのお礼

yokomaya様　お世話になります｡

>14行目で統一でいいでしょうか？
　支障が無ければ14行目でお願いします。上の行に注記等書込んでおきたいので。
　注記とは計算の定義　 ”所定内労働時間＝7:00~1700の休憩を除いた実労働時間” みたいな...。
　作業中にどうしようとしていたのか解らなくなるのを軽減したいのと､数式がリストを読みに
　行っている時リストの場所と内容を把握したいのが理由です。
　※ 後から無駄な行を削除は可能ですか?

>下にオートフィルしたのは覚えてるよね？
　もちろん覚えています。　実行した時の爽快感は今の所この作業で一番でしたから。
　私も今回いろいろ調べている中で”シリアル値”はちょいちょい目にしました。
　正直なところまったく解りませんでしたが、なんとなく解ってきました。
　”だからこうすればいいんだ”　までは解りませんが...。

>今一度ｃ１４でリスト入力試してくれません？
　一応リスト入力はNo.7回答時に試みました。議論するタイミングが無かったので詳細は
　伝えられなかったのですが、ご想像の通りです。　　(あそこまでではないですが　笑）
　美観や作業性は申し分ないですし､今回の"MATCH( )"であれば逆に計算しやすそうですね。
　ご提案頂いたリスト入力で行きます。よろしくお願いします。
　
>休憩のsum・・・評価できたら説明します。
　すみません。よろしくお願いします。

>今シート名はSheet1から名前を付け替えてます？
　A:付け替えています。
　正式シート＝勤怠管理
　正式シート用テーブル等置場＝勤怠管理計算ﾃﾞｰﾀ
　検証用シート＝勤怠管理(2)
　です。今は検証用のシートで作業をしていますので、配置はNo.18のお礼・数式はNo.17のお礼
　に載せてみました。

※リスト入力ですが現在 平日を空白表示ですが"MATCH( )"セルが”#N/A”表示されます。
　平日は選択時間を減らすためにも空白で行きたいです。
　法定休日も日曜確定なのでリスト判断ではなく曜日から判断できれば選択時間を減らせるかも...。

宜しくお願いします。

お礼日時：2017/03/01 11:20

No.18 回答者： yokomaya 回答日時： 2017/02/28 16:04

42まで使うって事は、上にかなり行を取ったね。

そこの説明してくれないと数式がわかんないけど。

Ｓ5の超長い式ね、ちょっとまとめるの待ってくれない？
僕の検証じゃ、数字が0以外にならない部分がある気がして。

ｃ列が1とかって何？
リスト入力を数字に戻すのはワークセル（同一行）に
=MATCH(C5,種別,0)
とかしとくと、何番目が選ばれてるかを得られます。

Ｄ列も整数？

何故なの？教えてー。おじいさーん。　　（BY みくり　...のまね）

この回答へのお礼

yokomaya様　お世話になります｡

>42まで使うって事は、上にかなり行を取ったね。
　すみません。コピーしたシートの数式を載せちゃいました。
　上の行に色々情報とか書いておかないと何やってるか解らなくなってきちゃうんです。
　検証のさらに検証みたいなシートでした。

>Ｓ5の超長い式ね、ちょっとまとめるの待ってくれない？
　そうなんです。長いんです。どうにかなると嬉しいです。
　R5=IF(OR(COUNT(H5:K5)={2,4}),I5-MAX("8:00",H5)+MIN("17:00",K5)-J5-SUM(S5:X5),"")
　の SUM(S5:X5) 部分を取り分けただけです。当日の休憩時間だけ表示してみたかったので。

>ｃ列が1とかって何？
　C列はその日の勤務種別を数字で区別しています。（とりあえずで）
（空白=平日・1＝所定休日・2＝法定休日・3＝半日有給・4＝1日有給）としています。
　1の所定休日なら定時間はなくすべて法定外残業時間にしたいと思いました。

>Ｄ列も整数？
　D列はタイムカードの始業時刻打込みセルです。
　0:00（整数？）で打込んでいます。

今の検証用のシートは
日付 曜日 種別 出勤 外出 再入 退勤 始業 途出 途入 終業 法内 法外 深夜 休出 不労 総労 所内 休憩
の順番で A列からS列まで使い 13 行目にタイトルが来ています。打込みは14行目からです。
正式なシートは4行目です。（決定数式だけ置いてます）
休憩時間だけの数式を作る時､数式が休憩時間のリストのどこを探しに行っているのか別シートでは見づらかったので
A1:B11に置いています。正式シートは別のシート(勤怠管理計算ﾃﾞｰﾀ)に置いてます。
12行目に 年 と 月 を置いてます。できればこちらの形で進めたいのですがだめでしょうか?
宜しくお願いします。

※現在､出勤が17:00を過ぎると所定内がマイナス表示､不就労も最大7:40のはずが増えていってしまいます。
　17:00過ぎの出勤はまずないと思いますが･･･。

お礼日時：2017/02/28 22:50

No.17 回答者： yokomaya 回答日時： 2017/02/28 10:06

印刷の話を忘れてました。

一般的に印刷するということは１枚の紙に如何に読み手に取って
必要な情報を詰め込むかという話で、一方ｅｘｃｅｌはというと
縦横のマトリックスで構成されている表ですから、データ処理では
そのままの縦横それぞれに意味がある状態で使うのが最適で
本来、相容れないものなのです。

そこでデータ処理はあくまでも、ｅｘｃｅｌ本来の形で行い
印刷用シートにはそこから抜き出すという事をします。
例えば、縦に名前のリストが並んでいる表Ａから
ご指定の横に並んでいる印刷への転記を考えるなら実際に転記するのではなく
座標として横に一つ進むと=COLUMN()が一つ増えるので
Ｂの表で＝INDEX(A!A1:Z1000,COLUMN()+高さ定数＋ページあたり件数＊頁、列番号）として
横にオートフィルすれば縦の表を横に羅列できます。

実際このタイムカードのシートでも項目を横に並べましたが
それを横にしてもｅｘｃｅｌで使いやすくなるケースはまずありません。
それはＨLOOKUPとVLOOKUPの同じ機能が用意されていても
本や記事で見かける頻度が全く異なる点だけを見ても
明かと思うんです。

この件については、ですから実際の表を訊いてから具体的には進めたいと
思っています。

N()は寡聞にして知りませんでした。
従来僕はこんなケースでは””を0にして、オプションの詳細設定で
ゼロ値のセルにゼロを表示するのチェックを外していました。
事実上変わらないと思いますけど。

検証をしたいのでまたしばらくお待ちください。

この回答へのお礼

H=IFERROR(CEILING(D14,VLOOKUP(D14,$A$1:$B$11,2)),"")
I=IFERROR(FLOOR(E14,VLOOKUP(E14,$A$1:$B$11,2)),"")
J=IFERROR(CEILING(F14,VLOOKUP(F14,$A$1:$B$11,2)),"")
K=IFERROR(FLOOR(G14,VLOOKUP(G14,$A$1:$B$11,2)),"")
L=IF(OR(AND(N(H14)<>0,C14=""),C14=3),MAX(Q14-R14-M14,0),0)
M=IF(OR(N(H14)=0,C14=2,C14=4),0,IF(C14=1,Q14,MAX(Q14-"8:00",0)))
N=IF(N(K14)=0,0,MAX(K14-"22:00",0))
O=IF(AND(C14=2,H14<>""),Q14,0)
P=IF(OR(C14=1,C14=2,C14=4),0,MAX(IF(C14=3,"3:50","7:40")-R14,0))
Q=IF(OR(COUNT(H14:K14)={2,4}),N(I14)-N(H14)+N(K14)-N(J14)-N(S14),0)
R=IF(OR(COUNT(H14:K14)={2,4}),N(I14)-MAX("8:00",H14)+MIN("17:00",K14)-N(J14)-N(S14),0)
S=IF(OR(COUNT(H14:K14)={2,4}),(MAX(0,MIN("10:10",IF(I14="",H14,I14))-MAX("10:00",H14)))+(MAX(0,MIN("10:10",K14)-MAX("10:00",IF(J14="",H14,J14))))+(MAX(0,MIN("13:00",IF(I14="",H14,I14))-MAX("12:00",H14)))+(MAX(0,MIN("13:00",K14)-MAX("12:00",IF(J14="",H14,J14))))+(MAX(0,MIN("15:10",IF(I14="",H14,I14))-MAX("15:00",H14)))+(MAX(0,MIN("15:10",K14)-MAX("15:00",IF(J14="",H14,J14)))),0)

お礼日時：2017/02/28 23:06

No.16 回答者： yokomaya 回答日時： 2017/02/28 08:25

確認だけいくつか

OR(COUNT(H5:K5)={2,4})
の入る式、#VALUEが、たまにでたりしませんか？

それと多分他の数式もうできてると思うから披露していただけたらと思います。

この回答へのお礼

yokomaya様　お世話になります｡

#VALUE でました。
N( ) で今の所対応出来ていそうです。
他の数式ですが､何度か作ってはみたもののすぐに不具合が発生して先に進んでいません。
とりあえずその日の休憩時間だけ別セルで出してみました。

H5~K5=始業 途出 途入 終業

法内:L5=IF(OR(AND(N(I42)<>0,D42=""),D42=3),MAX(R42-S42-N42,0),0)
法外:M5=IF(OR(N(I42)=0,D42=2,D42=4),0,IF(D42=1,R42,MAX(R42-"8:00",0)))
深夜:N5=IF(N(K42)=0,0,MAX(K42-"22:00",0))
休出:O5=IF(AND(C42=2,H42<>""),Q42,0)
不労:P5=IF(OR(C42=1,C42=2,C42=4),0,MAX(IF(C42=3,"3:50","7:40")-R42,0))

総労:Q5=IF(OR(COUNT(H42:K42)={2,4}),N(I42)-N(H42)+N(K42)-N(J42)-N(S42),0)
所内:R5=IF(OR(COUNT(H42:K42)={2,4}),N(I42)-MAX("8:00",H42)+MIN("17:00",K42)-N(J42)-N(S42),0)
休憩:S5=IF(OR(COUNT(H42:K42)={2,4}),(MAX(0,MIN("10:10",IF(I42="",H42,I42))-MAX("10:00",H42)))+(MAX(0,MIN("10:10",K42)-MAX("10:00",IF(J42="",H42,J42))))+(MAX(0,MIN("13:00",IF(I42="",H42,I42))-MAX("12:00",H42)))+(MAX(0,MIN("13:00",K42)-MAX("12:00",IF(J42="",H42,J42))))+(MAX(0,MIN("15:10",IF(I42="",H42,I42))-MAX("15:00",H42)))+(MAX(0,MIN("15:10",K42)-MAX("15:00",IF(J42="",H42,J42)))),0)

どうでしょうか?
よろしくお願いします。

お礼日時：2017/02/28 09:19

No.15 回答者： yokomaya 回答日時： 2017/02/27 07:29

＞後から実働時間を分ける予定でしょうか?

そうです。
多分その条件が僕にとって後から刷り込まれたので
そう考えるのが自然だっただけで、どっちでも同じ
じゃないでしょうか。
僕の知っていた半日有給は、午後と午前を選択出来て
残業はそれとは全くの無関係で斬新すぎたので。

＞本来、丁度の時間は遅刻等の扱い
そんなことはないのではないでしょうか。
大人の常識的には不適切ですが、８時からと決められている
就業に８：00打刻で遅刻扱いは事実上困難だと思います。

むしろ、僕的には休憩始まりの10時出勤した人の
あるいは9：56からでもいいですが、出勤を10：00で
カウントする方が違和感ありましたけど、計算の方で
10：00から10分をカウントしなければ、事実上は
10：10始まりだから、それでいいんだと気づきました。
だから、間違いと言われたときにすぐに理解できなかったんです。

今日は昼間は実家のPCで打ってました。アプリでも読めるけど
返事はフリックが得意じゃなくて長文が打てません。

数式の方は、いけてそうな感触ですが、もうちょっと検証させてください。