Excelでエラーが出ない方法

質問

SUM(A1,A4,A7,A10,A13)
こういうような数式をA20に入れたとします。
そのときにA10のセルを削除すると「#REF!」とひょうじされますが、これを表示させずにA10をなしにして計算してくれる方法を教えてください。

よろしくお願いします。

回答No.8

nishi6 · ベストアンサー

質問にあることを実現するには、

=SUM(A1,A4,A7,A10,A13)　の代わりに、

=SUM(IF(ISERROR(A1),0,A1),IF(ISERROR(A4),0,A4),IF(ISERROR(A7),0,A7),
　　　IF(ISERROR(A10),0,A10),IF(ISERROR(A13),0,A13))
　　　　　　　（１行です。長くなるので２行で書いています）
とすれば、計算可能です。

この算式でA10のセルを削除しても、A13セルはA12に自動的に変更され、正しい値がでます。
しかし、A10セルが削除されたのは事実で、回答の算式内のA10部分は『#REF!』になります。
式の一部は『#REF!』になるが、式全体では計算できるということです。
計算は可能ですが、この状態のままにしておくのは何か安心できませんが・・・

しかし、上の式は入力が面倒なので、同じ機能のユーザファンクションを作ってみました。

標準モジュールに貼り付け、
　=Sum_NotRef(A1,A4,A7,A10,A13)
　=Sum_NotRef(A17,A10,A1,B1:C3)
のようにして使います。
引数のセルを削除すると、そのセルが『#REF!』になりますが他のセルを使っての計算は行います。

Function Sum_NotRef(ParamArray rg() As Variant)
　　Dim ct As Integer　　'// カウンタ
　　Dim v As Double　　　'// セルの値
　　Dim TTL As Double　　'// 有効なセルのみの合計値

　　If UBound(rg) = -1 Then
　　　　'// セルを指定していなかったら答えはゼロ
　　Else
　　　　'// 最低、1個のセルを指定していた場合
　　　　On Error Resume Next

　　　　'// 関数の引数がエラーでないセルを合計する
　　　　For ct = LBound(rg) To UBound(rg)
　　　　　　v = Application.Sum(rg(ct))
　　　　　　TTL = TTL + v: v = 0
　　　　Next
　　End If

　　Sum_NotRef = TTL
End Function

回答No.7

imogasi

可能回答が出ると無知を曝しますが、本件不可能でしょう。
A10を参照とか関数式に記述している場合、A10が削除されると、殆どの場合、加工参照対象を失って＃REFになります。
唯一（？）の例外が=SUM(A1:A10)等の場合A10を削除しても＝SUM(A1:A9）になってくれます。
＝SUM(A9:A11）などの場合はA10は途中であって、記述されてない時も削除しても＝SUM(A9:A10）になってくれます。
＝SUM(A1,A4,A7,A10,A13)をその形に持って行けないか
考えましたが、不可能なようです。
A10が削除される（A10しか削除されない）のがわかっているなら
＝A1+A4＋A7＋SUM(A9:A10)-A9＋A13
とかすれば、１回の削除には耐えられそうです。

回答No.6

noname#9284

再々失礼いたします。

> 例のやつは２つおきになってますが実際のほ不規則になってます。

そういうご事情でしたら私もANo.#3さんの方法しか思いつきません・・・。

ここでご質問を切り分けたいのですが、
１．
削除しても常にA1、A4、A7・・・といった「位置が固定の」セルを足し算されたいのでしたらINDIRECRが有効です。
A10を削除されればA13はA12になり、計算の範囲から外れます。

２．
しかし、例えばA10を削除しますとA13がA12となるわけで、計算式は自動的にSUM(A1,A4,A7,A12)になります。
結果的にSUM(A1,A4,A7,A12) をされたいのでしたら、最初はSUM(A1,A4,A7,A10,A13) このように入力されるのが一番無難と思います。

この場合の入力方法ですが、
数式バーに「＝SUM()」と入力するかΣボタンを押されるかし、
Ctrlキーを押しながら必要なセルをクリックして行って、
最後にEnterキーを押します。
この方法ですと、手で入力される時間の1/10で済みそうです。

回答No.5

noname#9284

ANo.#1とANo.#２です。
先ほどは失礼いたしました。

=SUMPRODUCT((MOD(ROW(INDIRECT("A1:A13")),3)=1)*1,INDIRECT("A1:A13"))

この数式ですと、セルを削除してもエラーにはなりません。
ただしどんどん削除してA20に入力したこの数式が上に上にと上がって行くとしたら
A14までにして下さい。

補足

質問の仕方が悪かったみたいです。
例のやつは２つおきになってますが実際のほ不規則になってます。

よろしくお願いします。

回答No.4

12m24

質問のままでは相対参照の状態になっているので、A20からA10にコピーさせると、もとのA1,A4,A7の行番号がマイナスになってしまうので、＃REFエラーを吐き出すことになります。
　これを解決するには、絶対参照にします。
　絶対参照にするには、変化させたくない要素の頭に「＄」をつけます。こうすれば、セルをコピーしても、参照先が変わることがありません。
　ただし、この場合A10にコピーすると、循環参照となってしまうので、セルがA10の時には0を足すようにする必要があります。

回答No.3

neKo_deux

> A10のセルを削除すると

[編集]-[削除]した場合に、式自体が
=SUM(A1,A4,A7,#REF,A12)
となる状況ですね。

=SUM(INDIRECT("A1"),INDIRECT("A4"),INDIRECT("A7"),INDIRECT("A10"),INDIRECT("A13"))

の式ではどうでしょう？

＃別の良い方法がある気もしますので、締め切りは少し待った方が良いかも。

回答No.2

noname#9284

すみません、
=SUMPRODUCT((MOD(ROW(A1:A13),3)=1)*1,A1:A13)
これは間違いでした。

回答No.1

noname#9284

=IF(ISERROR(SUM(A1,A4,A7,A10,A13)),"",SUM(A1,A4,A7,A10,A13))
でいいと思います。

ただ、下記のようにしますと、どんなにセルを削除しても常にA1:A13の範囲で2行おきに足し算をしてくれます。
エラーにはなりませんので、ISERROR関数は必要ではありません。
=SUMPRODUCT((MOD(ROW(A1:A13),3)=1)*1,A1:A13)