EXCELで作成した予定表で１．予定と実績の差を計算し、２．その後の各作業の終了見込みを知りたい。

EXCELのA列に作業終了予定時刻、B列に作業終了実績時刻、C列に作業終了時刻の実績と予定の差、D列に作業名、G列に予定作業所要時間、H列に実績作業所要時間を記載している。I列に各作業の終了見込みを表示させたい。
なお、
　A列の作業終了予定時刻は最初の作業を除き直前の作業終了時刻+予定作業所要時間として計算(即ち、iを行番号、作業の数をn、2=＜i＜=nとするとき,　A(i) = A(i-1)+G(i))。
　B列の作業終了実績時刻は1作業終了ごとに手入力。
　G列の予定所要時間は最初の作業開始前に各作業の所要時間の見込み値すべてを手入力。
　H列の(各作業の)実績所要時間はB列の作業終了実績時刻の差として計算
(即ち、H (i) = B (i)- B (i-1))。

教えていただきたいことは
１． 各作業ごとに、C列の作業終了時刻の実績と予定の差を計算したい。（C(i)を計算したい。2=＜i＜=n）　　（計算値が負になる場合、24時をまたぐ場合など計算してくれません。）
２． 各作業終了ごとにそれ以降の各作業の終了予定時刻を計算し,I列に表示させたい。例えば5番目の作業終了時には5番目以降のすべての作業の終了予定時刻とその前に計算した2～4番目の作業終了予定時刻も残しておきたい。
I(i) =A(i)+ C(i)とすればいいのかと思いますが、C(i)が計算できないので…
また、I(i)を1列に表示する方法がわかりません（2=＜i＜=n）。
　EXCELは2016年版（多分）
　　
パソコン初心者です。よろしくお願いします。

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

• ご指導に従いやってみました。
  ABC列はうまくいきました。
  ところがHIJ列にうまくいかないところがあります。
  IJ列が小数になります。例えばI15は0.013888888889 、 I16は0.986111111　、
  J15は0.972222222　
  H16が＃＃＃＃＃となりました。
  お手数をおかけしますので恐縮ですが、教えていただければとてもありがたく思います。

  No.1の回答に寄せられた補足コメントです。 補足日時：2018/05/18 23:50

• 補足の補足です。(補足の情報不足と思いまして…)
  各列に入力したのは下記のとおりです。
  J15には下の式をカットアンドコピーしました。
  =IF(G15="","",IF(B15="",J14+G15,IF(ABS(A15-B15)>0.5,B15+1,B15)))
  そしてJ16行目以下はJ15をコピードラッグしました。IHCA列も要領は同じです。
  
  I15は、次の式です
  =IF(B15="",J15,J14+G15)
  H15は、次の式です
  =IF(B15="","",J15-J14)
  C15は、次の式です
  =IF(B15="","",IF(A15=B15,"±00:00",TEXT(ABS(J15-A15),IF(A15>J15,"-hh:mm","+hh:mm"))))
  A15は、次の式です
  =IF(OR(A14="",G15=""),"",A14+G15)

    補足日時：2018/05/19 00:18

• 補足3　ひょっとして、下のことが影響しているのでしょうか？H16が####となりましたので。
  少数の作業間の時間を知る必要があり、F列（の少数の行）をこれに使っています。
  F16にはつぎの式をいれています。=K16-A15+A16 　
  K16は「 0:00」と表示されており、開いてみると“1900/1/1 0:00:00”となっています。
  ひょっとしてこれが悪さをしているのでしょうか？

    補足日時：2018/05/19 01:07

No.1 回答者： moto_koukousei 回答日時： 2018/05/12 15:06

１)　作業終了時刻の実績Ｂ列と作業終了時刻の予定Ａの差をC列に計算したい。

　　（計算値が負になる場合、24時をまたぐ場合など計算してくれません）

☆　エクセルで時刻のデータには、(マイナスの時刻)はないのでそのままは無理です。
　　時刻データとしてではなく、文字として表示させるのが一般的だと思います。
　　時刻差や時間差は、(＋)(-)(±)をつけて、文字として表示させるという方法です。
　　この文字としての時間差を、他の計算に使用はできないので、
　　差を計算に使う場合は、通常の計算方法で出した結果を使います。
☆　(24時をまたぐ場合など)　手入力した[00:23]と、[23:45+00:48]のように計算させた結果で表示が[00:23]となっているのとは、同じ値としては扱われません。
　　手入力した[00:23]を([23:59]よりも大きな値)として扱いたい場合は、[00:23]と入力されているところの値に(何かを加算した結果)を利用することにするのが良いです。
　～～～　　～～～　　～～～　　～～～
２）I列に表示させるものを、（条件によってふるい分ける　⇒　過去の欄には(その行のの終了時刻実績を入力する直前に表示されていた内容を残す)　：　終了時刻実績をまだ入力してない行には(所用作業予定時間から計算した終了予定時刻を表示する)

☆　やりたいことの条件が複雑ですので、そうした場合は、作業領域をつくって、ある程度のところまで作業させて必要なデータを出しておいて、そのデータを利用するのが、楽です。
☆　下図では、Ｊ列に作業領域をつくって、ダミー(特殊な)の終了時刻を表示させています。
J15の場合は次の式です。
=IF(G15="","",IF(B15="",J14+G15,IF(ABS(A15-B15)>0.5,B15+1,B15)))
ここで　[ B15+1 ]は、Ｂ列に入力した時刻を翌日の時刻にするための加算です
ここで　[ ABS(A15-B15)>0.5 ]は、A列とＢ列との時刻差が12時間以上あるか否かを判別しています
ここで　（Ｂ列に実績が入力されているときに）[ (B15+1) あるいは(B15))]で、B列に入力された終了時刻の本来の意図？時刻を、J15に表示
ここで　（Ｂ列に実績が入力されてないときに）[ J14+G15 ]で、Ｊ列の前の行の終了時刻にこの行の所要時間を加算して、J15に表示
☆
I15は、次の式です
=IF(B15="",J15,J14+G15)
ここで　（Ｂ列に実績が入力されているときに）[ J14+G15 ]を、I15に表示
ここで　（Ｂ列に実績が入力されてないときに）[ J15 ]を、I15に表示
☆
H15は、次の式です
=IF(B15="","",J15-J14)
ここで、[ J15-J14 ]とせずに、[ B15-B14 ]としてしまうと、00:00を超えたところで計算が期待のものではなくなってしまいます
☆
C15は、次の式です
=IF(B15="","",IF(A15=B15,"±00:00",TEXT(ABS(J15-A15),IF(A15>J15,"-hh:mm","+hh:mm"))))
このやり方が良いのか、他にも良い方法があるか、よく考えてはいません。
時間差を計算して、マイナスになった場合、それをそのまま表示しようとするとエラーになります。で、結果を文字列にしてしまうという方法です。
☆
A15は、次の式です
=IF(OR(A14="",G15=""),"",A14+G15)

この回答へのお礼

早速にご回答ありがとうございます。
どこまで理解できるか、じっくり考えて、やってみて改めてご報告します。
素人ではありますが、ここまでご回答いただくには大変なお手数をおかけしたのではとありがたく思っています。ありがとうございました。

お礼日時：2018/05/12 16:03