• ベストアンサー

エクセル　時間計算合計（マイナス含む）

マイナス時間のデータを扱う為、各セルではＴＥＸＴして時間を 扱っております。 マイナス表示もできております。 各セルの合計をＳＵＭを使用して計算したいのですが ＴＥＸＴデータのＳＵＭ関数で＝SUM(VALUE(W13:W43))を計算式として入れましたがデータに「マイナス時間」が含まれると結果が ＃ＶＡＬＵＥ！となってしまいます。 「プラス時間」でけあればＴＥＸＴの合算が出来ております。 「マイナス時間」を含めたＴＥＸＴ合算が出来る方法をご教示願います。

質問者が選んだベストアンサー

  • ベストアンサー
  • SI299792
  • ベストアンサー率48% (714/1476)
回答No.5

その数式が使えるという事は、最新版又はオンライン版ですか？ 10:00 -10:00 の様な文字列で入っているとします。 =SUM(IF(W13:W43>"",SUBSTITUTE(W13:W43,"-","")*IF(LEFT(W13:W43,1)="-",-1,1)))

HKRKEN
質問者

お礼

ありがとうございます！！ 数式通りの関数で見事に解決しました。 助かりました。

その他の回答 (4)

  • HohoPapa
  • ベストアンサー率65% (454/691)
回答No.4

日付、時間、時刻を扱う「日付シリアル値」は、 マイナスの値を扱うことができませんので、 何か別なアイデアが必要となりましょう。 そこで、例えば、添付画像のようなアイデアはいかがでしょうか？ 以下、画像の見方です ・マイナスの時間は、マイナスの値を埋めるのではなく 　24時間を加えた時刻（翌日の時刻）を入力する ・これらに該当するセルは、条件付き書式で赤文字にする。 ・合計を求める計算式を工夫する。 セルに埋まっている計算式は以下です。 D7=COUNTIF(D2:D5,">1") D8=SUMIFS(D2:D5,D2:D5,">1") D9=SUMIFS(D2:D5,D2:D5,"<=1") D10=D9-D8+D7

画像を拡大する
  • Chiquilin
  • ベストアンサー率30% (94/306)
回答No.3

> データに「マイナス時間」が含まれると結果が＃ＶＡＬＵＥ！となって > しまいます。 VALUE関数を入れたからでしょう。TEXTで表現した「マイナス時刻」はいわ ばただの飾りですから計算には使えません。 どうしてもマイナスの時刻表記にこだわるというなら毎回元の値から 全部計算することになります。 無駄なことに時間をかけるのは止めて 時間単位表記に切り替えたほう がいいと思います。

  • msMike
  • ベストアンサー率20% (363/1775)
回答No.2

「マイナス時間のデータを扱」える簡単な方法が 、Excel に存在することをご存じないンですね？それを貴方が使いこなせるか否かは別にして。 Excel Win版の標準の基準日は 1900年1月1日で、基準時はその前日の午前零時ですが、[ファイル]⇒[その他…]⇒[オプション]⇒[詳細設定]⇒[次のブックで計算するとき]配下の“1904年から計算する”にチェックを入れるのです。 ただし、副作用もあるので慎重に検討する必要があります。

  • imogasi
  • ベストアンサー率27% (4737/17068)
回答No.1

文字列を「エクセルの日付・時刻シリアル値に」直して加算（マイナス加算＝減算を含む）しないとダメでしょう。 このための関数が、DATEVALUE、TIMEVALUE です。 小生も経験不足で、下記でボロが出るかも。その際はすみません。 ーー https://www.tipsfound.com/excel/04timevalue にある、 ・変換できる文字列は、セルの書式設定にある時刻の形式とほぼ同じです。 ・引数「文字列」に含まれている日付はすべて無視されます。 ・引数「文字列」には最低でも時と分が必要です。一部の形式で時だけでも変換できます。 ・引数「文字列」に変換できない文字列や時間や値などを指定したときは、エラー #VALUE! になります。 ・引数に時間を受け取る関数は、自動で「TIMEVALUE 関数」が使用され文字列を時間に変換します。そのため「TIMEVALUE 関数」が必要になる場面はほとんどありません に注意して各セルの、時間文字列データを（１日＝24時間を１とする）時刻シリアル値に（エクセルの力で）変換し、それ（セルやセル範囲）をSUM関数で合計しよう。 これをやって見て、うまく行かないケース（文字列や２４時間以上表記）があれば、 その点に絞って、質問をし直したらどうか。 また　https://ilzem.com/ex/1285/ TIMEVALUE関数の注意点 に沿った文字列であるかどうかが問題で、複雑で難しい、と思う。 13時10分 0.548611111＜＝＝TIMEVALUE(A7)　半角数字 １３時１１分 0.549305556　＜＝＝TIMEVALUE(A８)　全角数字 午後13時10分 #VALUE!　　　<==TIMEVALUEでエラー 26時11分　　　　　　<==TIMEVALUEでエラー など ＝＝＝ 時刻を文字列で持つなど、まずい話と思う。経験が少ないからそういう方に行ってしまうのだろうと推測するが。

  1. カテゴリ
  2. パソコン・スマートフォン
  3. ソフトウェア
  4. Excel（エクセル）

関連するQ&A

  • ＥＸＣＥＬＬマイナスを含む時間合計（２４Ｈ超え）

    先日　ＳＩ２９９７９２様に TEXT化された時間計算の合計方法を下記の如くご教示頂き 問題となっていたマイナス時間を含む合算は出来るようになりました。 =SUM(IF(W13:W43>"",SUBSTITUTE(W13:W43,"-","")*IF(LEFT(W13:W43,1)="-",-1,1))) しかし、合計時間が２４時間を超えた段階で正しい合計時間にならなくなりました。 もともとマイナス時間を扱う為にＴＥＸＴ化しているため セル表示を時間表示に出来ません。 現状のマイナス時間が扱えるまま２４時間越えの合計時間を計算することは出来ないでしょうか？

  • エクセルで時間の合計ができません

    セルの表示を0:00となる[h]:mmとしています。たとえば、SUM(A1+A2+A3+A4+A5)とすると5:00など正常に動きますがSUM(A1:A5)とすると0:00となります。時間形式以外の計算ではSUM(A1:A5)の関数も正常に動きます。TEXT関数を調べましたが理解できませんでした。とても困っています。よろしくお願いします。

  • エクセル2003　計算されないようにしたい

    例 Ａ列　　　　　Ｂ列 　1　　　　　＝A1+A2 　2　　　　　＝A2+A3 　3　　　　　＝A3+A4 （A4は空白です） となっている場合に B1＝3 B2＝5 B3＝3 になると思うんですが この時に空白のセルと計算結果を出すときは０もしくは 計算されないようにしたいです。 最終的にB列にSUM関数を使って合計を出したいのですが A列に求めたい数字が片方しか入ってない場合 （数字が入っているセル+空白セル）はSUM関数の合計に入れたくないです。 なので上記の例では B列にSUM関数をした場合11ではなく8にしたいです。 それには文字列を入力して#VALUE!にするしかないですか？ でもその状態にしてSUM関数で合計を出したときも#VALUE!になってしまいます。 よろしくお願いします。

  • Excelマクロ 計算式を足していく

    Excel2003を使用しています。 マクロで、伝票を追加して行き、追加する度に合計のセルにある計算式を 前の伝票の計算式と合算させたいのですが、うまく行きません。 1枚目の合計　=Sum(A4:A17) 2枚目の合計　=Sum(A22:A35)+1枚目の合計 3枚目の合計　=Sum(A40:A53)+1枚目の合計+2枚目の合計 ※合計の計算式は18行毎に入ります。 伝票の枚数は決まっていませんので、何枚追加しても合算出来る様にと考えています。 Formulaを使ってセルの計算式を取得し、2枚目の計算式と合算は出来るのですが、 3枚目4枚目となっていく時の記述がイマイチうまく出来ません。 sikiA = Range("A18").Formula　←1枚目の合計 siki1 = sikiA & "+" & Cells(18 * cnt, 1)　←2枚目の合計と1枚目の合算 ※「cnt」は伝票の枚数です。 sumの部分は、伝票の追加の時点で計算式が入ります。 なので、伝票をコピー＆貼り付けした時点で、合算している計算式を入れてしまった方が 良いのかなとは思いましたが、思うように行っていません。 よろしくご教授下さい。

  • エクセル2000で時間の計算（マイナスも含む）

    こんにちは、いつもお世話になっています。 あと一歩のところだと思うのですが、 マイナス時間の表示を教えてください。 エクセル2000を使っています。 入力しているデータは、3.45　0.3 -1.2 といったデータです。 これを、3時45分、0時30分、-1時間20分、2時55分　と表示させ、 最終行に合計値を求める方法を探しています。 現在の状態は、 (1)セルA1（3.45）をセルB1（3時45分）に表示 　B1 =TIME(INT(A1),100*(B1-INT(A1)),0)　と入力。 (2)表示形式を時刻表示に設定しする。 (3)負の時刻や日付を表示させる方法として、 「ツール」-「オプション」-「計算方法」タブで「1904年から計算する」にチェックをする。 ↑のところまで実施したのですが、 マイナス値（-1.2）の結果がエラー表示（#NUM!）になってしまいます。 他の方のエラーは####表示のようですが、 私の場合、元データと関数の組み合わせが間違っているのでしょうか？ よろしくお願いいたします。

  • Excelでの合計計算について

    初歩的な質問で申し訳ありません。 Excelでの数字の合計方法について教えてください。 Excelで以下の数を合計するとします。 10 10 -10 10 10 SUMで計算すると結果が「30」になるのですが、数学上間違いではないことは分かります。 ですが、うまく言えないのですが私が求める結果は「40」になってほしいのです。 合計からマイナスの値を引く？という方法になると思うのですが、これを実現する方法、または関数などを教えてください。 大変恐縮なのですが、大変な素人のため、多少詳しく教えていただけると幸甚です。

  • エクセルで合計値が計算されない

    横一列のセルを選択してその右隣のセルに合計値を表示させたいのですが、普段は出来るのに突然計算されずに0を表示する事があります。 セルの中はきちんとSUM(C4:H4)となっていますが、どうして計算されないのでしょう？

  • エクセル２００３の時間計算の合計

    エクセル２００３で時間計算のワークシートを作りました。 1日の労働時間が細切れで、4回くらいの小計を出すことはできたのですが、その合計がどうしても計算できません。 A列：月日　B列：1回目の開始時間　C列：1回目の終了時間・・・ 時間で入力しています。その横に1回目、2回目・・・という風に =TEXT(C7-B7,"h:mm")　という計算式を入れて計算させています。 その書式設定を、標準にしても、時間にしても、１，２，３，４回目の合計がｎ列に　=SUM(J7:M7)　と入れても、できません。”０”になってしまいます。とても困っています。どうかよろしくお願いします。 　　　　　　　　　

  • エクセルの計算式でオートサムで単純にセルの合計を計算表示したいのですが

    エクセルの計算式でオートサムで単純にセルの合計を計算表示したいのですが　マイナスの数字もプラスと認識して合計表示する方法教えて下さい

  • 同じFunctionで、同じ計算させたいのですが

    私は今、以下のようにここで教えていただいたプログラムを参考に組んでは見たものの、同じ行をいくつもコピペすると計算されなくなってしまいます。1行だけの計算でしようとするとうまく計算されるのですが・・・ <HTML> <HEAD> <TITLE> タイトルバーに表示されるテキスト </TITLE> <SCRIPT LANGUAGE="JavaScript"> <!--JavaScript function SUM(){ if(!isNaN(document.sum.text1.value) && !isNaN(document.sum.text2.value) && !isNaN(document.sum.text3.value) && document.sum.text1.value && document.sum.text2.value && document.sum.text3.value){ document.sum.text4.value = eval(document.sum.text1.value) + eval(document.sum.text2.value) - eval(document.sum.text3.value) } if( !isNaN(document.sum.text5.value) && !isNaN(document.sum.text6.value) && document.sum.text5.value && document.sum.text6.value){ document.sum.text7.value = eval(document.sum.text4.value) + eval(document.sum.text5.value) + eval(document.sum.tex6.value) } } //--> </SCRIPT> </HEAD> <BODY> <FORM NAME="sum" onSubmit="SUM();return false;"> A<INPUT TYPE="text" NAME="text1" onChange="SUM()" SIZE="10"><BR> B<INPUT TYPE="text" NAME="text2" onChange="SUM()" SIZE="10"><BR> C<INPUT TYPE="text" NAME="text3" onChange="SUM()" SIZE="10"><BR> D=A+B-C<INPUT TYPE="text" NAME="text4" onChange="SUM()" SIZE="10"><BR> E<INPUT TYPE="text" NAME="text5" onChange="SUM()" SIZE="10"><BR> F<INPUT TYPE="text" NAME="text6" onChange="SUM()" SIZE="10"><BR> G=D+E+F<INPUT TYPE="text" NAME="text7" onChange="SUM()" SIZE="10"><BR> </FORM> </BODY> </HTML>

注目のQ&A

カテゴリ

専門家に質問してみよう

職業から探して質問する

あなたにピッタリな商品が見つかる！ OKWAVEセレクト

質問する