sumproductとcountifの使用例

質問

A　B　C　D　E
1　あ あ あ が い
2　い う か ぎ え
3　う い さ　ぐ　ん
4　え え た げ　あ
5　お お な ご　げ

=SUMPRODUCT((COUNTIF(A1:D5,E1:E5)>0)*1)

上記は重複している個数が何個あるか（同じデータが複数回でてきても1個とカウントする）を出す関数なのですが、

sumproductとcountifの部分は大体理解できるのですが、

「＞０」の部分と、「*１」の部分がどうしても理解できず、悩んでいます。

内部ではどういう計算が行われているのでしょうか？
どなたかお知恵をお貸しください。
よろしくお願いいたします。

回答No.1

keithin · ベストアンサー

その数式は，A1:D1のセル範囲の中に，E1:E5のどれが幾つあるか無いかを計算しているだけで，「重複を数える」というのとはちょっと違っているような気がします。

しかし今ご質問はその式の正誤はとりあえず置いて計算のシクミについてですから，そういうのを確認し勉強する便利な道具として，「数式の検証」というのを利用してみます。

Excel2002,2003ではツールメニューのワークシート分析の中に，Excel2007,2010では数式タブにありますので，問題の式を入れたセルを選んで早速実行してみます。

まず一段階「検証」してみると，
　COUNTIF(範囲,い)が２個，
　COUNTIF(範囲,え)が２個，
　COUNTIF(範囲,ん)が０個，
　COUNTIF(範囲,あ)が３個，
　COUNTIF(範囲,げ)が１個，
それぞれ見つかったようです。

次のステップは
　２＞０　ですから　TRUEです
　２＞０　ですから　TRUEです
　０＞０　ではないので　FALSEです
　３＞０　ですから　TRUEです
　１＞０　ですから　TRUEです
次のステップは
　True * 1　は　１です
　True * 1　は　１です
　FALSE * 1　は　０です
　True * 1　は　１です
　True * 1　は　１です
最後に合計して４が得られました。

お礼

「＞０」をつける事により戻り値がtrue、falseになるんですね。
そこの概念が理解できなかったので迷走しておりました。

ご回答ありがとうございました。

回答No.2

imogasi

COUNTIF関数はＣＯＵＮＴＩＦ（範囲,条件）で、範囲は複数セル（の範囲を指定するのは）質問の式もＡ１：Ｄ５で普通の使い方です。
条件は比較演算子＋リテラル値（”＞７０”）や１つのセル（１つの値）（Ａ１など）を指定するのが普通の使い方です。
普通１つのセル番地しか指定しない関数式の引数部分に複数セルの番地を指定できるのは
配列数式とＳＵＭＰＲＯＤＵＣＴ関数です。（他にもあるかもしれないが代表的なものはこれです。外にリテラル値の配列を指定（｛　｝内に）できる関数がありますが少し事情が違う）。私が配列数式とＳＵＭＰＲＯＤＵＣＴ関数の「双対性」（もともとは数学の言葉）を想像するのはこのためである。
この質問のケースはこの場合で
---
この場合Ｅ１からＥ５までのそれぞれの
COUNTIF(A1:D5,E1)
ＣOUNTIF(A1:D5,E2)
COUNTIF(A1:D5,E3)
COUNTIF(A1:D5,E4):
(COUNTIF(A1:D5,E5)
の５回の処理をそれぞれ別に（内部で）やっていて、またＳＵＭＰＲＯＤＵＣＴなので５式の結果のＳＵＭ（和）を取っていると考えられる。
その前に言うべきことは、それぞれの式には＞０がついているので「存在する」（かどうか）の条件を見ている。
と言うことはＴＲＵＥかＦＡＬＳＥを出している。
例えばセルにＡ１に３を入れて＝Ａ１＞４と入れるとＦＡＬＳＥになる（これは実用では使わない場合なので、したことがない人も多いだろうが）これと同じ理屈。
もうひとつ*1をしているのは、ＴＲＵＥやＦＡＬＳＥを数値に直すためにやっている。この場合では0か1
ですが。
其れで５回の関数のそれぞれは、Ｅ１の値がＡ１：Ｄ５に１つでも存在すれば1、存在しなければ0を返している。それをＥ１：Ｅ５までくり返し内部でおこなって、結果を足しているものを結果に出している。
ーー
質問の式の意味を文章で表現すれば、Ｅ１：Ｅ５の５文字（列）の中でＡ１：Ｄ５の文字列に存在するのはセル数で言うと、４セル（個）ですということになる。
この質問の回答の関数式を、質問者は質問の趣旨に当てはまるとして、ベストアンサーに選んだのかもしれないが、
実際の場面でこの４つと言う結果を得て、それが当てはまる（使える）ケースは想像しにくい。
質問者が質問文章に、はっきり文章で何を出したいかを明記しなかった（表現できなかって）ために、回答の式で迷路に迷い込んでいるのではないかとおもうがどうだろう。

お礼

重複している一意のデータの個数を求めることができたので満足しています。
迅速な回答ありがとうございました。