【SUMPRODUCT関数】空白セルを無視して計算したい - 統計を学ぶ化学系技術者の記録

回帰分析を自力でやる計算するときに偏差積和を求める必要がある。

$\begin{align*} S_{xx} = {\displaystyle \sum_{i=1}^n(x_i-\bar{x})^2 } \end{align*}$

エクセルでSUMPRODUCT関数を使って計算しようと思ったのだが、地味に苦戦したので解説記事を書く。

実際のエクセルが気になる方ははこちらのGoogle スプレッドシートをご参照ください。

例えばこんな感じの計算をしたいとする。

$\sum_{i=1}^n{x_iy_i}$

エクセルでやるならば①B列×C列をD列に表示②SUM(D列)とすればよい。

この一連の操作をまとめてやるのがSUMPRODUCT関数で

SUMPRODUCT（C列,D列）

とすると一発で計算してくれる。

では続いて下記の式の偏差平方和をSUMPRODUCT関数を使って求めたいとする。

$\sum_{i=1}^n({x_i-\bar{x})^2}$

SUMPRODUCT(C列 - 平均値, C列 - 平均値)と入力すればいい気がするのだが、空白セルがあると少し話がややこしくなる。

D列のように空白セルが0 - 平均値として処理されてしまい、加算されてしまう。

じゃあどうすればよいかというと

SUMPRODUCT(G列<>"". G列-平均値, G列-平均値)

とするとよい。

「G列＜＞""」という数式は、G列が空白でなければTRUE(=1)、空白であればFALSE(=0)が出力される。

結果下記のように正しく偏差平方和を計算することが出来た。