ExcelでSUMPRODUCT関数を用いたデータ比較

　ExcelでIF関数を用いて複数のデータ比較をしようとした場合、AND関数、IF関数で入れ子にしていく方法などがあります。
しかし、いずれの場合も条件式が長くなり読みづらくなります。
　複数のデータを比較したいとき、「SUMPRODUCT」を用いることで簡単にデータ比較を行うことができます。

SUMPRODUCT関数

　SUMPRODUCTは対応する範囲または配列の四則演算（defaultは”乗算”）の合計を返すExcel関数です。

引数：SUMPRODUCT( array1, [array2], [array3], ・・・)

第１引数のみを指定するとSUM関数と同等の結果となります。
第２引数以降を指定すると、それぞれの引数に指定した配列の対応する要素同士を四則演算し、その合計の値が返されます。
　※SUMPRODUCT(A1:A3, B1:B3)の時、( A1 * B1 ) + ( A2 * B2 ) + ( A3 * B3 )の結果が返されます。

例：成績の比較

　前期成績と後期成績が各教科全て等しいかどうかを比較します。
この例ではAさんのデータ比較を行います。

・AND関数を利用するとき

　AND関数を利用して比較すると、次のような式になります。

=IF(AND(C4=J4, D4=K4, E4=L4, F4=M4, G4=N4),
　　　”完全一致”,”一致していない”)

　AND関数を利用して複数のデータを同時に比較することができますが、比較データの個数が増えたときに一つ一つ記述するのには時間がかかってしまいます。

・SUMPRODUCT関数を利用するとき

　SUMPRODUCT関数を利用して比較すると、次のような式になります。

=IF(SUMPRODUCT(( C4:G4 <> J4:N4 ) *1 ) > 0,
　　　”一致していない”, ” 完全一致 “)

　AND関数を利用したときとは異なり、比較データの個数が増加しても記述量を減らすことができます。
次の段落でこの使用例について解説していきたいと思います。

解説

　ここでは、先ほど使用したSUMPRODUCTの式について、内側から詳しく解説していきたいと思います。

① ( C4:G4 <> J4:N4 )

　①の式ではそれぞれの要素同士を比較している論理式になっています。
　例のAさんの場合、{FALSE, TRUE, FALSE, FALSE, FALSE}という結果が返ります。

② ( C4:G4 <> J4:N4 ) *1

　②の式では、① * 1されています。この[* 1]は{TRUE, FALSE}の値を{1, 0}に変換するために使用しています。
　 Aさんの場合、{0, 1, 0, 0, 0}という結果に変換されます。

③ SUMPRODUCT(( C4:G4 <> J4:N4 ) *1 )

　③の式では②の結果で返ってきた値をSUMしています。
　③の結果が0より大きいときは、いずれかの値が異なり、0の時は全て一致しています。
　 Aさんの場合 、0 + 1 + 0 + 0 + 0 = 1で1が返されます。よってAさんは前期と後期の成績が同じではないことが分かります。

同じようにB,C,Dさんの比較を同じように行うと、Dさんのみが完全一致していることが分かりました。

まとめ

　今回は、Excelで複数のデータ比較をするときに便利なSUMPRODUCT関数について解説しました。SUMPRODUCT関数は今回紹介した使い方以外にも幅広い場面で使うことができる関数なので、時間があれば調べてみるのもいいと思います。