エクセルの関数の使い方

質問

エクセルで2つの連続する数値の相関関係を知りたいためにCORREL関数を使いたいです。

しかし、CORREL（A1：A100,B1:B100)と範囲指定した時に、たまに参照している計算式の関係で95列目以降、エラー表示#N/Aが出てしまいます。エラー表示が出るとCORREL関数が使えません。
さらにデータの切り替えがあるので、CORREL（A1：A95,B1:B95)と範囲を狭めたくありません。
またA1：A100とB1:B100で折れ線グラフを作成しており、IFERROR等でエラー表示を消してしまうと、折れ線グラフが0の値を指し示してしまうのでエラー表示を消したくありません。

このようにエラー表示を消さず、CORREL関数を使うにはどのような方法が考えられますか？

回答No.4

SI299792 · ベストアンサー

ワークエリアを設けて、グラフを出す領域と、計算する領域を別々にすればいいです。
具体的には。
C1: =IFERROR(A1,"")
C1:D100 へコピペ
=CORREL(C1:C100,D1:D100)

お礼

ご回答ありがとうございます。
折れ線グラフで参照する行とCORREL関数で参照する行を分けてしまうしかないんですね。

表示範囲に同じ値が出るのが気に入らないので、行を非表示にして隠すことにしました。
ありがとうございます。

回答No.5

masnoske

No.3 です．
CORRELを計算しようとしている A列に日付が入っていて，そこにエラーが発生しているのなら，当然ですが CORRELの結果もエラーになります．その場合は，エラーが発生している行を削除するしかありません．
あるいは，日付との相関ではなく日との相関にし，A列には 1～31の数値を入力する．
2月や 4月のように 31日までない場合は，ダミーデータとしてその月の平均値を入力すれば，相関に影響しません．

お礼

厳密には日付に基づいたデータAとデータBの相関性を知りたかったのですが、おっしゃるように折れ線に使う＃N/Aを使うものと、CORRELように＃N/Aを表示させないダミーを使うことにしました。

回答No.3

masnoske

問題は CORREL関数ではなく，以下の部分にあるように参照している計算式がエラー値を出しているからではありませんか？

CORREL（A1：A100,B1:B100)と範囲指定した時に、たまに参照している計算式の関係で95列目以降、エラー表示#N/Aが出てしまいます。エラー表示が出るとCORREL関数が使えません。

お礼

単純に言えば、日付の関係です。
時系列の数式で31日もあれば2月のように28日のときもあるため31日未満の月は、必然的に数値がないので＃N/Aが発生してしまします。

回答No.2

HohoPapa

CORREL関数を呼び出して使う、
オリジナルな関数をVBAで作成し対応する
という解はいかがでしょうか？

つまり、オリジナルな関数が
（A1：A100,B1:B100)　を受け取り
エラーセルを動的に見つけ
（A1：A95,B1:B95)を組み立て
これをCORREL関数に渡します。

言い換えれば、後記のコードを標準モジュールに配置し

=CORREL（A1：A100,B1:B100)
と書くのではなく
=MyCORREL（A1：A100,B1:B100)
と書きます。

Function MyCorrel(Rng1 As Range, Rng2 As Range) As Double

　Dim Rng1x As Range
　Dim Rng2x As Range
　Dim SRow1 As Long
　Dim SRow2 As Long
　Dim ERow1 As Long
　Dim ERow2 As Long
　Dim ERow As Long
　Dim SCol1 As Long
　Dim SCol2 As Long
　Dim RowCnt As Long

　SRow1 = Rng1.Row
　SRow2 = Rng2.Row
　SCol1 = Rng1.Column
　SCol2 = Rng2.Column

　RowCnt = SRow1
　Do
　　If IsError(Cells(RowCnt, SCol1).Value) = True Then Exit Do
　　If Cells(RowCnt, SCol1).Value = "" Then Exit Do
　　RowCnt = RowCnt + 1
　Loop
　ERow1 = RowCnt - 1

　RowCnt = SRow2
　Do
　　If IsError(Cells(RowCnt, SCol2).Value) = True Then Exit Do
　　If Cells(RowCnt, SCol2).Value = "" Then Exit Do
　　RowCnt = RowCnt + 1
　Loop
　ERow2 = RowCnt - 1
　ERow = Application.Min(ERow1, ERow2)

　Set Rng1x = Range(Cells(SRow1, SCol1), Cells(ERow, SCol1))
　Set Rng2x = Range(Cells(SRow2, SCol2), Cells(ERow, SCol2))

　MyCorrel = Application.Correl(Rng1x, Rng2x)

End Function

回答No.1

msMike

「たまに参照している計算式の関係で」とは具体的にだう云ふ事ですか？

お礼

単純に言えば、日付の関係です。
時系列の数式で31日もあれば2月のように28日のときもあるため31日未満の月は、必然的に数値がないので＃N/Aが発生してしまします。