gooドクター有料プランが1ヶ月間無料!

Excelで時間の引き算が負になる場合"#########"となってしまうので、
以下の様な対応をしていますが、
=TEXT(ABS(C2-B2),IF(C2>B2,"-hh:mm:ss","hh:mm:ss"))

ソートした時、正しい並びにならなくなってしまいます。
良い方法は無いでしょうか?
----------
例えば、
9:06-9:05 は、0:01
9:05-9:06 は、-0:01
9:05-9:08 は、-0:03
9:08-9:05 は、0:03

結果を昇順にソートした場合、
以下の様になって欲しいのですが、
9:05-9:08 は、-0:03
9:05-9:06 は、-0:01
9:06-9:05 は、0:01
9:08-9:05 は、0:03

実際にソートすると以下の様になってしまいます。
9:06-9:05 は、0:01
9:05-9:06 は、-0:01
9:08-9:05 は、0:03
9:05-9:08 は、-0:03
(↑「-」を無視してソートされてしまうため)

良い方法は無いでしょうか?

gooドクター

A 回答 (5件)

No.3、No.4です。


大変失礼しました。回答の一部に誤りがありました。
「1904年から計算する」という設定の場合、
起点は1904年1月0日で、これがシリアル値「0」と書きましたが、
これは誤りでした。
正しくは
起点は1904年1月1日で、これがシリアル値「0」
です。
お詫びして訂正いたします。
    • good
    • 0

>1900年1月0日になっているのを


>1904年1月0日にすると都合良くなる理屈がイマイチ理解できませんでした。

とのことですが、これは、No.2さんの回答にある
[ファイル]⇒[オプション]で設定を変更するという意味で、EXCELの仕様の話です。
No.2さんの説明どおり変更すると1904年1月1日がシリアル値で「1」となります。つまり1904年1月0日が「0」でここが起点です。
EXCELの仕様では、この状態だと時間のマイナス計算の表示が可能になるという話です。
※通常は1900年1月1日がシリアル値で「1」です。つまり、1900年1月0日が「0」でここが起点です。
    • good
    • 0
この回答へのお礼

ありがとうございます。
私が言いたかったのは、
・1900年1月0日を起点「0」とするとマイナス計算が出来ない
・1904年1月0日を起点「0」とするとマイナス計算が出来る
どちらも「0」より前の計算ができなんじゃないか?と思ったので、
1904年の時だけ何故できる?と理屈に合わない疑問がありました。

これに対して論理的な理由があるわけではなく、
「これがExcelの仕様」という事であれば素直に納得です(笑

お礼日時:2021/05/28 17:54

No.2さんのご指摘のとおり、時間計算でマイナスの場合の表示に不都合が起こるのは日付計算の起点が1900年1月0日になっているからです。


これを1904年1月0日にすれば、マイナス値も表示可能であり、
=TEXT(ABS(C2-B2),IF(C2>B2,"-hh:mm:ss","hh:mm:ss"))
という数式も不要になります。
また、No.2さんの示されたオプションの変更はブック毎に設定できますので、必要なブックにのみ「1904年から計算する」という設定を行い、通常は1900年から計算するというスタンダードな設定で使うという手もあります。
しかし、No2さんも「ただ・・・」と書きかけていますが、EXCELのブックは実務の世界では、複数のブック間でデータをコピペしたり、VLOOKUP関数等で参照したりすることも結構あります。
この場合、「1904年・・・」の設定の有無により日付のシリアル値が異なることになるため、予期しない結果になる危険性があります。
このようなリスクをどう判断するのかという問題になります。↓
https://docs.microsoft.com/ja-jp/office/troubles …
一方、No.1さんの方法は、作業列が増えることになりますが、「予期しない結果」になることはないと考えられます。
引き算の答えがD列に表示されているなら作業列2行目に
=IF(LEN(D2)<>LEN(SUBSTITUTE(D2,"-","")),SUBSTITUTE(D2,"-","")*(-1),D2*1)
を記述し、表示形式が標準のままだとすれば、負の場合も表示され、可視性もあります。逆に作業列を見せたくないなら非表示にするとか文字色を白にする手もあります。これを下方向にコピーし、作業列を基準として昇順にソートすればご希望どおりの結果が得られると思います。
或いは、一層のこと、No.1さんの方法を応用して、2~300行にデータがあるとして作業列2行目に、
{=SUMPRODUCT((IF(($B$2:$B$300="")*($C$2:$C$300=""),10^15,($B$2:$B$300-$C$2:$C$300))<(B2-C2))*1)+1}
という配列数式({}以外を入力してCtrl+Shift+Enterで確定)を記述し、下方向にコピーして、引き算の答えに昇順の順位をつけてしまう方法もあります。この順番にソートすればよいだけです。
現状の私の知識では、No.1さんNo.2さん以外の方法を思いつかないので、あとはご質問者が判断する問題だということになります。
    • good
    • 0
この回答へのお礼

1900年1月0日になっているのを
1904年1月0日にすると都合良くなる理屈がイマイチ理解できませんでした。

お礼日時:2021/05/28 16:50

"良い方法"かどうか、添付図参照(バージョンは問いません!)


[ファイル]⇒[オプション]で設定を変更(私はしないけど)すれば好いンです!
知らなかったでしょうッ!(^_^)

並べ替え問題も解消するかと。ただ…
「Excelで時間の引き算が負になる場合に」の回答画像2
    • good
    • 0
この回答へのお礼

ありがとうございます。単独ブックで完結する仕様なら良いけど連携などで問題が起きる可能性がありますね。でも参考になりました。

お礼日時:2021/05/28 16:45

こんにちは



ご提示の、TEXT関数の結果は文字列ですが、ご希望の並べ替えは実質の差分で並び替えたいということと解釈しました。

ですので、作業列にでも =C2-B2 の式を入れて、表示を「標準」にしておきます。
※ 時刻表示だと負数は表示されませんが、標準なら負数も表示されます。
(気にしないのなら、時刻表示で「######」表示のままでもかまいません。)
この列をキーにして並べ替えを行えば、ご希望の結果になると思います。
    • good
    • 0
この回答へのお礼

実際に見るための列と、都合よくソートさせるための列を分けるって事ですね。
ありがとうございました。

お礼日時:2021/05/28 16:47

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

gooドクター

このQ&Aを見た人がよく見るQ&A

このカテゴリの人気Q&Aランキング