エクセルで#VALUE!の入ったセルの合計や平均を求めたい

計算表の集計対象列のいくつかのセルに計算結果が ＃VALUE!となるセルが必ず出るのですが、これを含めた対象のセル範囲の集計をしたいのです。
TEXT等試してみましたが思うようにいかず。

集計する値は時刻（残業時間）なので8:00（8時間）といった形式になっています。
終業時刻から始業時刻を引いて、そこから8時間を引いた値が残業時間と言うことになります。
時間に24をかけると数値に変換されるのですが、＃VALUE!のセルが入ると扱えなくなってしまいます。

HELPお願します。

No.1 ベストアンサー 回答者： trajaa 回答日時： 2020/10/19 16:54

＃VALUE!となる部分を、もし計算結果がエラー値なら結果をゼロにする

という感じにしておく

IFERROR関数なんかを活用しよう

この回答へのお礼

早々のご回答ありがとうございます。

そうでした! IFERRORがありました。
時間XX：XXを扱ったことが無かったので、表示形式を変える事ばかりに意識が行っていました。

お礼日時：2020/10/20 08:39

No.5 回答者： d-q-t-p 回答日時： 2020/10/20 04:53

あっとタイトルに「エクセル」って入ってましたね。

失礼しました。

No.4 回答者： d-q-t-p 回答日時： 2020/10/20 04:52

まず何の質問かくらいは書きましょう。

Excelと仮定しますけど バー
ジョンが分からないと適切な回答ができません。

> いくつかのセルに計算結果が ＃VALUE!となるセルが必ず出るのですが
そうならないように数式を作るべきかと思います。もっというなら最初か
ら数値と文字列が混在するようなデータの作り方をしない方がいいです。

ひとまず エラーを無視して集計をする場合 AGGREGATE関数が使えます。
ただし Excel2010以降でしか使えません。

もうひとつは SUMIFや AVERAGEIFを使う方法。時刻が対象なんだから
負の値はないんでしょうし

=SUMIF(範囲,">0")

みたいにすれば計算できるでしょう。

この回答へのお礼

ご指摘の通りで、時刻を扱ったことが無いので何とか数式というか、表示形式時刻表示（XX：XX）を数字化して後で時間に戻す、というような処理を試みたのですが諸事情で諦めて質問した次第です。

IFERROR関数（あまり使わない）で解決できることに気が回らず早急に質問してしまいました。

SUMIFでも出来ることを確認しました。
なるほどでした。
色んな関数を教えていただき感謝です。

お礼日時：2020/10/20 08:56

No.3 回答者： ママチャリ 回答日時： 2020/10/19 22:07

AGGREGATE 関数を使って、集計方法「SUM」、オプション「エラー値を無視」で行けませんかね？

この回答へのお礼

全く知らない関数でしたが解説見ながら試してみたら行けました!

非常に有用な関数ですが、その分オプションが多く、滅多に使わない当方には覚えきれないのでこの関数が必要な高度なケースがあるはずなのでその時に参考になります。

今回のケースは単純にIFERRORでも補助列の追加（不細工ですが）で確実に解決出そうです。

お礼日時：2020/10/20 08:47

No.2 回答者： angkor_h 回答日時： 2020/10/19 17:07

エラー表示セルを除外する関数を使えばよいでしょう。

しかし、元々、エラー発生するような使い方自体がおかしいので、
その原因を突き詰めるのが先なはずですが…

この回答へのお礼

表の各セルの参照セル（このセルも計算式）の参照セル･・・等の関係で一定の数だけエラーが出るのは仕方がない表です。（元データの入力セルが定位置のため）

お礼日時：2020/10/20 08:42