セル B5 には次の数式があります:
=SUMPRODUCT((A2:A10<>"")/COUNTIF(A2:A10,A2:A10&"")-(COUNTIF(A2:A10,A2:A10&"")=1))
列 A に以下に示すデータがある場合、複数回出現する値が 3 つあるため (14、16、17)、数式は正しい答え 3 を返します。これはまさに私が知りたいこと、つまり、複数回出現する値が 3 つあるということです。値が何であるか、またその値がどこにあるかを知る必要はありません (ただし、最後の部分については便利な情報を持っています)。
Column A:
12
13
14
14
14
15
16
16
17
17
ただし、A2:A10 の範囲内のどこかに空白セルがある場合、重複値の数は 1 減ります (上記の例では、数式は 3 を返すべきところを 2 を返します)。以下のサンプルでは、空白セルは "B" で表されます。
Column A:
12
13
14
B
14
15
16
16
17
17
この例では、14、16、17 はそれぞれ複数回出現するため、数式は 3 を返す必要がありますが、2 が返されます。
これは、基本的に空でないセルをカウントしている最初の部分によるものだと確信しています=SUMPRODUCT((A2:A10<>"")。もちろん、2 つ (またはそれ以上) の空白セルがある場合は、再度増分されますが、これも実際には正しくありません。なぜなら、空白でない重複をまだ省略しているからです (それが意味をなすかどうかは別として)。
答え1
注: あなたの質問は、9セル範囲が表示されていますが10例の値は一致しないので、問題は理解しているのですが。
数式の最初の部分は、異なる非空白値の数を数える標準的な方法なので問題ありません......しかし、2番目の部分を減算するときにはCOUNTIF空白も除外する必要があるため、2番目の部分の&""部分を削除する必要がありますCOUNTIF。つまり、このバージョンです。
=SUMPRODUCT((A2:A10<>"")/COUNTIF(A2:A10,A2:A10&"")-(COUNTIF(A2:A10,A2:A10)=1))
....でもこのバージョンの方が良い
=SUMPRODUCT((COUNTIF(A2:A10,A2:A10)>1)/COUNTIF(A2:A10,A2:A10&""))
これらの数式は両方とも、A2:A10のテキストまたは数値データ（またはその両方の混合）に機能しますが、数値のみの場合（例のように）FREQUENCY次のような関数を使用することもできます。
=SUMPRODUCT((FREQUENCY(A2:A10,A2:A10)>1)+0)