エクセルで、空白セルをグラフで非表示にし、かつ、AVERAGEなどの計算でエラーにならないようにするには？

質問

エクセルで、空白セルをグラフで非表示にし、かつ、AVERAGEなどの計算でエラーにならないようにするには、どのようにしたらよいのでしょうか。

過去の質問を拝見し、

　１）空白セルをグラフで非表示→NA()にする
　２）空白セルを非計算→""にする

は、理解できたのですが、同時に成立せず、困っております。

回答No.6

xls88 · ベストアンサー

>その抽出されたデータだけで、計算およびグラフ表示をしたいと考えています。
>マクロをつかえば、そのデータを値貼り付けしてしまうなど、対策ありそうなのですが、関数だけでいけないでしょうか。
無理だと思います。諦めてください。
私の現有能力範囲から導き出した結論です。

手作業を厭わないなら
編集メニューのジャンプからセル選択で、数式のエラー値、あるいは文字を選びOKでセル選択し、Deleteでセル内容を消去
といった方法もあります。
更新の度に、空白セルに数式のコピーをして、上記作業を行う必要があります。
自動的にというならマクロを組まなければなりません。
と思います。

お礼

このグラフの元データにもしたいが、数式も実施したいというのが、とてもよくあり、なにかもっと調べればよい方法はないかと悩んでいましたが、あきらめがつきました。
本当に質問してよかったです。

回答No.7

xls88

数式によって、"" 、#DIV/0!、#N/A、等が返されている場合
そのセルをクリアするサンプルマクロを組んでみました。
良ければ試してください。
対象セル範囲の番地を、実際に合わせて書き直してください。

Sub test折れ線()
　　Dim mycht As Chart
　　Dim erc As Range

　　'数式を再セット
　　For Each erc In Range("B2:C11")
　　　　If erc.HasFormula Then
　　　　　　Range("B2:C11").FormulaR1C1 = erc.FormulaR1C1
　　　　　　Exit For
　　　　End If
　　Next
　　'セルデータが、""とかエラー値の場合消去
　　For Each erc In Range("B2:C11")
　　　　If erc.Value = "" Or IsError(erc) Then
　　　　　　erc.ClearContents
　　　　End If
　　Next
End Sub

お礼

再度、ありがとうございます！！
マクロを組んでやってみたいと思います。

回答No.5

xls88

≪AVERAGE関数≫のみですが
Excel2007なら、AVERAGEIF関数があります。

旧Excelなら
平均＝SUMIF(データ範囲,">=0")/COUNTIF(データ範囲,">=0")
とか
平均＝SUMIF(データ範囲,"<>=#N/A")/COUNTIF(データ範囲,"<>=#N/A")
で、どうでしょうか。

お礼

Excel2003使用です。
平均＝SUMIF(データ範囲,"<>=#N/A")/COUNTIF(データ範囲,"<>=#N/A")
で、グラフかつ計算がいけそうです。
ただ、Median（中央値）と、stdiv（標準偏差）も計算したいので、困っています。
Median（中央値）と、stdiv（標準偏差）の計算方法があれば、教えてください！

回答No.4

xls88

リンクデータではなく、生数値データを計算に使えば良いと思うのですがだめなのでしょうか？

お礼

たびたび、ありがとうございます。
生数値データを計算に使うのは、ちょっと異なった発想で、そちら方面からもアプローチできるかもしれない？？と思いました。でも、今の状態ではできないです。

今のところ、生数値データのほうは、異常値なども含まれているので、それを除去するための判定をしていて、その判定に合格なら、リンクを表示するという式になっています。

その抽出されたデータだけで、計算およびグラフ表示をしたいと考えています。

マクロをつかえば、そのデータを値貼り付けしてしまうなど、対策ありそうなのですが、関数だけでいけないでしょうか。

回答No.3

xls88

質問の意図がイマイチ理解できていません。
(1)グラフは折れ線グラフでよかったでしょうか。
(2)データ表はどうなっているのでしょうか。
(3)実際の計算式を掲載できないでしょうか。

一例を挙げてみます。
=IF(COUNT(A1:E1)=0,NA(),AVERAGE(A1:E1))

取りあえず、参考になりそうなURLを貼っておきます。
[XL2002] 折れ線グラフでデータが 0 の要素は前後の要素を直線で結ぶ方法
http://support.microsoft.com/default.aspx?scid=kb%3bja%3b402639

お礼

補足要求ありがとうございます！！！

(1)グラフは折れ線グラフでよかったでしょうか。
　　　→グラフは散布図です。
(2)データ表はどうなっているのでしょうか。
　　　データ表は、元の生数値データから、=A1のようにリンクしています。
　　　ただし、元のデータが空欄でしたら、そのまま＝A1ですと
　　　0になってしまうので、
=IF(A1="","",A1)のようにしています。
　　　しかし、これですと、""セルが0と認識されてしまって、
　　　プロットされてしまうので、=IF(A1="",NA(),A1)とすると、
　　　グラフはうまくいくようになったのですが、
　　　計算ができなくなりました。

(3)実際の計算式を掲載できないでしょうか。
　　　グラフ表側（セルAA列）
　　　　=IF(A1="",NA(),A1)
　　　計算側
　　　　=AVERAGE(AA1:AA1000)
※A1は生数値データ、AAはある処理をしたあとの
　　　　　リンクデータで、グラフにしたい範囲

以上、よろしくお願い致します！！

回答No.2

n-jun

ANo.1です。

＞平均＝SUM関数/COUNT関数では？
平均＝SUMIF(データ範囲,">0")/COUNT(データ範囲)
でした。
範囲の有効数値に０がない場合が条件ですが。

お礼

早々ご回答ありがとうございます！！！
現在、データ範囲にNA()が含まれている状態で、
=SUMIF(データ範囲,">0")/COUNTIF(データ範囲,">0")
で、計算できました。
しかし、範囲の有効数値に0があるため、困っています。

「">0"」の代わりに、「NA()でない」という式を入れれると、できそうな気がするのですが。。

また、計算したいのは、AVERAGEと、MEDIANです。
なにかアドバイスがありましたら、また、お願い致します。

回答No.1

n-jun

平均＝SUM関数/COUNT関数では？
共に”数値”が対象です。