エクセルの質問です。 拘束時間の管理表作成しているのですが、当日の始業時間と翌日の始業時間に差がある

エクセルの質問です。

拘束時間の管理表作成しているのですが、当日の始業時間と翌日の始業時間に差がある場合、例えば８時出勤で翌日９時ならば問題ないのですが、９時から翌日８時の場合、他加算として1時間入力しなければなりません。

Aセルに始業時間、Bセルに終業時間、Cセルに他加算だとしたら計算はA1から、A2を引くのですが、休みの日もあり空白セルがあります。

その場合、空白セルより翌日の始業時間をひくのでマイナス8とでてしまいます！

セルの列計算(引き算)で空白セルをとばす計算方法がわかる方、お願いします！教えて下さい。

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

• 皆さま、回答頂いて何のお礼もできず申し訳なく思っております！
  まだ仕事が終わらず、作成している表を貼り付けたいのですが、仕事が終わり次第添付させて下さい。
  本当にありがとうございます。

    補足日時：2016/12/16 21:05

• ＝IF(V12-V11<＝0,””,V12-V11)
  で、作成したのですが他加算のN13に本来は1時間、空白のセル飛ばしてN15は本来何ものらず、N16も本来は何ものらずでいきたいのですが、、、。

  
    補足日時：2016/12/19 12:40

• 私の最初の説明が間違っていました、すみません!
  当日始業から翌日の始業時間に遅く出勤した分は無視して、早めに出勤した分を他加算にカウントしなければなりません。
  しかし、日曜日休みで空白のセルはとばし、また月曜日から同じ事をするのですが、日曜日が絶対のお休みではなく、隔週の休みの社員も居ます。
  
  こんな計算式を出すのは無理でしょうか？

    補足日時：2016/12/19 18:38

• 何度も何度もすみません、頂いた関数を入れたらこのようになりました。

  
    補足日時：2016/12/20 13:49

• こんにちは、毎回お手を煩わし、申し訳ないです。
  先日頂いた式を入れてみました。
  この様になりましたがどうですか？

  
  No.12の回答に寄せられた補足コメントです。 補足日時：2016/12/21 13:45

• できました！！！！有難うございます。
  できた表を添付致しました。
  因みになんですが、N19ででた1：00なんですが本来は休日がN20でとっているのでV21からV19を引かない事ってできるのですか？
  これ以上お手を煩わせたくないので今回はできるか？できないか？だけで教えて頂ければもう十分です！！
  図々しいことだらけで本当にすみませんでした。
  本当に、本当に言葉で表せないくらいの感謝しております。
  ありがとうございました。

  
  No.13の回答に寄せられた補足コメントです。 補足日時：2016/12/21 16:03

No.13 ベストアンサー 回答者： tatsu99 回答日時： 2016/12/21 14:37

=IF(V14="",TRUE)・・・・・・・FALSE

=IF(ISBLANK(V14),TRUE)・・・FALSE
=IF(ISNUMBER(V14),TRUE)・・・TRUE
=IF(V14=0,TRUE)・・・・・・・TRUE
上記から判断できることは、V14には数値として０が格納されていて、表示上、０の場合、空白を表示している
ということです。一例として、添付の図のように、セルの書式設定で、ユーザー定義[=0]"";hh:mmを設定し、
00:00を入力したとき、空白が表示されます。
対策としては、
1案）何とか空白自体を入力するようにする。
2案）今のままで、空白のセルは０であることを前提として、関数式をつくる。
但し、その場合は、空白のセルは全て０であることが、前提です。（０と空白が混在してはいけません）
全ての空白のセルを対象として、上記の４式を適用し、FALSE,FALSE,TRUE,TRUEが成立すれば、
空白のセルは全て０であることが確認できます。

1案については、私のほうでは、解決できませんので、2案の場合について、式を提供します。
V14へ
=IFERROR(IF(V11=0,"",IF(INDIRECT(SUBSTITUTE(SUBSTITUTE(ADDRESS(1,COLUMN(V1)),"$",""),"1","")&SMALL(INDEX((V12:V$41<>0)*ROW(V12:V$41),0),COUNTIF(V12:V$41,0)+1))-V11>=0,"",V11-INDIRECT(SUBSTITUTE(SUBSTITUTE(ADDRESS(1,COLUMN(V1)),"$",""),"1","")&SMALL(INDEX((V12:V$41<>0)*ROW(V12:V$41),0),COUNTIF(V12:V$41,0)+1)))),"")

を入力し、オートフィルフィルで末日までコピーしてください。
これで、確認してください。これでOKなら、勤務表全体を右へコピーして翌月以降分を作っても
対応できるはずです。


＞質問を返して頂いて気づいたのですが、やはり月末、月初は手入力になりますね、私の頭ではどうも構成が浮かばず、、、となると計算式は他加算Nの１２からスタート下にオートフィルとなりますよね？？

月初は手入力は不要です。
手入力が必要なのは、月末日（正確には、当月勤務を行った最も月末に近い日付）です。
12月３０，３１日が休みの場合は、12月２９日が手入力が必要な日です。
（他加算を行う日付の後に実際に勤務を行った日付が１日存在すれば構いません）

この回答へのお礼

とても感謝しております。
有難うございました。

お礼日時：2016/12/21 16:06

No.12 回答者： tatsu99 回答日時： 2016/12/21 00:37

＞後、この表全体に1904のチェックボックスにチェックしてあると思います。

始業時刻のセル（V列）にTRUEが設定されていないことを確認してください。

>月初と月末はこれから右にコピーして、来年の1月分を入れるつもりです。
2016年の最後の勤務日～末日分を2017年1月1日のセルの上の行に入れるという意味ですか？
例えば、12月29日勤務、30日、31日が休みの場合、
12月29，30，31日の行を2017年1月1日のセルの上の行に入れるという意味ですか？
（そうすれば手作業はなくなりますが・・・・）

>2月はその隣です。その時にイコールセル番号でやろうかなぁと、思ってました！
来年分は、右にコピーしたものを追加して行くと理解しました。
又、2016年12月分（今回作成した分）を加算の計算式も含めてコピーするということと理解しました。
現在の式は、INDIRECT関数でV列の参照を直接"V"で記述しているので、右にコピーしたとき、
この"V"の部分は変化しません。従って、このままでは、右にコピーしても使えませんので
INDIRECT関数で使用する"V"が右にコピーされたとき、その月の始業時間の列を示すように
変更します。

この回答へのお礼

いつも有難うございます。
質問を返して頂いて気づいたのですが、やはり月末、月初は手入力になりますね、私の頭ではどうも構成が浮かばず、、、となると計算式は他加算Nの１２からスタート下にオートフィルとなりますよね？？

お礼日時：2016/12/21 13:52

No.11 回答者： tatsu99 回答日時： 2016/12/20 23:22

No9です。

こちらの図を添付するのを忘れました。添付します。

No.10 回答者： tatsu99 回答日時： 2016/12/20 22:55

No9です。

V14が本当に空白かを確認したいので、
適当な空いているセルへ（例えばZ列など）
Z1へ
=IF(V14="",TRUE)
Z2へ
=IF(ISBLANK(V14),TRUE)
Z3へ
=IF(ISNUMBER(V14),TRUE)
Z4へ
=IF(V14=0,TRUE)
と入力したとき、何が表示されるか、確認していただけませんでしょうか。

この回答へのお礼

わかりました！現在自宅で表が手元にないので明日すぐに確認します。
一応、セルはユーザー定義で時間にしてあるのと、始業の列は43番にオートサムを入れてあるだけです。
後、この表全体に1904のチェックボックスにチェックしてあると思います。
月初と月末はこれから右にコピーして、来年の1月分を入れるつもりです。
2月はその隣です。その時にイコールセル番号でやろうかなぁと、思ってました！
明日になりますが、また教えて頂いた式を入力してから返信致します。ご面倒おかけして、申し訳ございません！
お付き合い頂きありがとうございます。

お礼日時：2016/12/20 23:52

No.9 回答者： tatsu99 回答日時： 2016/12/20 19:45

No8です。

１）3日が7:00で表示される件ですが、こちらでは空白で表示されます。（添付の図参照）
4日の始業時間が空白の場合、5日の始業時間を見るはずでが、4日の時間を空白と
認識していないようにみえます。
V１４をクリックして、本当にスペースになっているか確認していただけませんでしょうか。
なにか、数式が入っているように思われます。
本当に空白か、もしくは数式などあるかによって、対応がかわるかと思いますので、
まずは、返信をお願いします。
２）6日が＃NUMで表示される件は、7日以降に始業時間がないです。
これは、仕様です。もし、6日を空白で表示したいなら、
=IFERROR(現在の式,"")
とすれば、＃NUMが空白に変わるはずです。
もし、6日を空白で表示することを望まれる場合は、その旨、補足ください。
=IFERROR(現在の式,"")・・・の形で再度提供します。
蛇足ですが、月末日が勤務日だとして、
月末日の他加算は、この式では対応できないはずです。（次の月の1日の始業時間がない為）
それゆえ、月末日の他加算は手入力になると理解していましたが、如何でしょうか。
又、来月になった場合、次の月の勤務表は、どこに作られる予定ですか？

No.8 回答者： tatsu99 回答日時： 2016/12/20 00:31

N11が1日の加算になります。

N11へ
=IF(V11="","",IF(INDIRECT("V"&SMALL(INDEX((V12:V$41<>"")*ROW(V12:V$41),0),COUNTIF(V12:V$41,"")+1))-V11>=0,"",V11-INDIRECT("V"&SMALL(INDEX((V12:V$41<>"")*ROW(V12:V$41),0),COUNTIF(V12:V$41,"")+1))))

と入力し、オートフィルで下へコピーしてください。
尚、excelの画像を取得するには、snipping toolがアクセサリの中にありますので、それを使用するのが便利です。（範囲を指定して画像が切り取りできます）

この回答へのお礼

本当にありがとうございます！！
私の拙い説明に付き合って頂いて恐縮です。
関数をのせてみたのですが、やはりまだ行かない状態です。
補足に画像添付してみました。

お礼日時：2016/12/20 13:57

No.7 回答者： tatsu99 回答日時： 2016/12/19 20:10

No6です。

式が長くなった場合、作業用の列をどこか使用しても良いですか。
（AA列とかAB列とか使用してよいなら、式がコンパクトになります）

No.6 回答者： tatsu99 回答日時： 2016/12/19 20:06

>当日始業から翌日の始業時間に遅く出勤した分は無視して、早めに出勤した分を他加算にカウントしなければなりません。

>しかし、日曜日休みで空白のセルはとばし、また月曜日から同じ事をするのですが、日曜日が絶対のお休みではなく、隔週の休みの社員も居ます。

以下の解釈で良いですか。
当日を１日とすると、
翌日の始業時刻-1日の始業時刻<0なら、1日の加算へ「1日の始業時刻-翌日の始業時刻」を設定する。
上記以外は、空白を設定する。（加算の設定箇所は当日（＝1日）です）
但し、翌日の始業時刻とは、翌日以降の空白でない始業時刻をもつ、最も小さい日付の始業時刻である。

この回答へのお礼

そうです！その通りの解釈です。
たた、列はこの表をコピーしていくので使用できるのは表の下部分のみです。
すみません、列を使用できないのは私の頭のなさなんですが、、。
その解釈で何とかなりますか？
もし列を使用した方が良いならばその通りに使ってみます！
宜しくお願いします。

お礼日時：2016/12/19 21:14

No.5 回答者： tatsu99 回答日時： 2016/12/19 18:13

N1２を当日とすると、N12へ

当日始業時間ー前日始業時間<0の時、前日始業時間ー当日始業時間を設定したい。
当日始業時間ー前日始業時間>=0の時、空白を設定したい。
但し、前日始業時間とは、当日より前で、空白でない始業時間をもつ最大の日付の始業時間である。
という前提で良いでしょうか。
N12へ
=IF(V12="","",IF(V12-INDIRECT("V"&MAX(INDEX((V$11:V11<>"")*ROW(V$11:V11),0)))>=0,"",INDIRECT("V"&MAX(INDEX((V$11:V11<>"")*ROW(V$11:V11),0)))-V12))

と入力し、オートフィルで下へコピーしてください。
空白の時間がなければ、
=(IF(V12-V11>=0,"",V11-V12))
の前提で作っています。

この回答へのお礼

ありがとうございます！

今コピーを貼り付けてみたのですがやはり12月4日、日曜日に休日だと4日に他加算は7時間とでてしまいます(汗)
どうすれば良いでしょう？

お礼日時：2016/12/19 18:31

No.4 回答者： yokomaya 回答日時： 2016/12/16 11:18

=IF(OR(A1="",A2=""),"",MAX(0,A1-A2))

これで如何でしょうか？

でもやはり疑問も残ります。
１行目に見出しを入れないで始業時間とか就業時間が入力すべきセルが
判るでしょうか？
A列に日付を入れないで何日の分を入力しているか判りにくく
ありませんか？

この回答へのお礼

お礼が遅くなりましてすみませんでした！
例題に乗せたセル番号は文面から説明するための仮説でした。すみません！

写真で載せて補足いたしましたが、頂いた計算式を入力しましたが、やはり空白の翌日他加算列に7：00とのってしまいます！

どうしたら良いですか？

お手数ですが、宜しくお願いします！

お礼日時：2016/12/19 12:52