エクセル 二列のデータの相関係数を取る際に、データ範囲をずらしながら相関係数を取る方法

エクセルの質問です。

10　20
12　18
11　16
25　15
21　32
10　26
……

という二列のデータがあるとします。
このデータを、

a1,b1
a2,b2
a3,b3
……

というようにセル番号で置き換えます。

単純に上記のデータの二列のデータの相関係数を
とるのであれば、
CORREL(a1:an,b1:bn)
で、求める値が算出できます。

次に、上記のデータをひとつずらして、

a1,b2
a2,b3
a3,b4
……

とした場合の相関係数を求めます。
CORREL(a1:an-1,b2:bn)
で求まると思います。

これを繰り返していって、
「最も相関が強くなるのは、データをいくつずらしたときなのか？」
を知りたいのですが、
データ数が多くなるといちいち式を書き換えていくのは少し面倒です。

オートフィルや関数など使いながら、
うまくやる方法をどなたかご存じでないでしょうか？
お教え頂けると有りがたいです。
よろしくお願いいたします。

No.2 ベストアンサー 回答者： CoalTar 回答日時： 2016/09/01 15:40

もう少し簡単にできました

=CORREL(A$1:INDEX(A:A,COUNT(A:A)-ROW(A1)+1),B1:INDEX(B:B,COUNT(A:A)))
下へオートフィル
お分かりのこととは思いますが、行番号n-1まで相関関係を出せますが信頼性は薄れます

この回答へのお礼

ありがとうございます！
統計用のアドインもあるようですが、
なるべく関数で済ませるやり方も考えておきたいと思っていましたので……

助かりました

お礼日時：2016/09/01 17:34

No.1 回答者： CoalTar 回答日時： 2016/09/01 15:36

OFFSET関数でもよいのかもしれませんが、揮発性が気になるので

=CORREL(A$1:INDEX(A:A,COUNTA(A:A)-ROW(A1)+1),INDEX(B:B,ROW(B1)):INDEX(B:B,COUNTA(A:A)))

http://detail.chiebukuro.yahoo.co.jp/qa/question …