エクセルで重複分を除いて関数でカウントする簡単な方法

  > > 重複データを除いた件数をカウント

ここでは、重複を除いてカウントする方法を解説します。

 

例えば値が何種類あるかその数を数えたい場合には、紹介する方法でカウントすることができます。

 

覚えておくときっと便利ですよ。



1.まずはやりたいことを確認

まずは、やりたいことを例を含めて確認します。

 

1-1.やりたいことは?

やりたいことは、「範囲内の重複を除いた数をカウントする」ことです。

 

例えば、「範囲内で何種類あるのか知りたい」といった時に使います。

 

1-2.例で確認

ひとつ例を見てみましょう。

上の表では品名として「レモン」、「りんご」、「ねぎ」が全部で7つ表示されています。しかし、中身はダブっています。

 

このようなダブりのある範囲の中で、重複分を除いてカウントすることができます。

 

ちなみに、例では「3」と返ってきます。



2.数式の形と作り方

次に、数式の形と作り方を見てみます。

 

2-1.数式の形は

数式の形は、次の通りです。

=SUMPRODUCT(1/COUNTIF(範囲,範囲))

「範囲」に「重複を除いてカウントしたい範囲」を指定し、「2ヶ所とも同じ」にします。

 

なお、範囲は「複数列指定」しても構いません。

 

2-2.数式の作り方

先ほどの数式の形を使って、実際に数式を作ってみます。

 

以下、手順です。

 

(1)以下の数式をコピーし、セルに貼り付ける

【コピーする数式】

=SUMPRODUCT(1/COUNTIF(範囲,範囲))

 

(2)「範囲」の文字を消し、カウントしたい範囲を入力する

 

(3)Enterキーを押せば完了

例の数式は、次のようになります。

=SUMPRODUCT(1/COUNTIF(B3:B9,B3:B9))

 

これで、「重複分を除いたカウント」ができます。



3.数式の意味を解説

続いて、数算式の意味を解説します。

 

2-1.1/COUNTIF(範囲,範囲)の部分

まず、「1/COUNTIF(範囲,範囲)」だけでどんな結果が返ってくるかを見てみましょう。

返ってくるのは「分母を『種類ごとの数』にした値」です。

 

まず、SUMPRODUCT関数で「配列数式」になるので、「セルごと検索条件は変わる」のがポイントです。例えば、「セルC3の検索条件値は『レモン』」、「セルC4の検索条件値は『りんご』」となります。

 

そして、「カウントする範囲」も「B3:B9」なので、結果「種類ごとの数」が返り、その値で1を割っているので「分母を『種類ごとの数』にした値」になるのです。

 

例えばセルC3で見ると、B3~B9には「レモン」が2つあるので、

=COUNTIF(B3:B9(="レモン"),B3:B9)

「2」です。

 

このことから、

=1/COUNTIF(B3:B9,B3:B9)

「1/2」となります。

 

2-2.SUMPRODUCT( )の部分

この数式の書き方の場合、SUMPRODUCT関数は「カッコ内の値を合計」します。

 

例だとSUMPRODUCT関数のカッコ内は、「レモンは「1/2」が2つ」、「りんごは「1/3」が3つ」、「ねぎは「1/2」が2つ」、という形になります。

 

そして、レモンは足すと「1/2+1/2=1」、りんごも足すと「1/3+1/3+1/3=1」、そしてねぎも足すと「1/2+1/2=1」です。

 

つまり、「『重複分だけ分母が増える』→『足される』で種類ごとの計はすべて『1』になる」のです。

 

 

ちょっと難しいかもしれませんが、仕組みを理解しなくても使うのは簡単ですので、よかったら使ってみてください。


口コミ・評判のいいExcel関数本の紹介】

 Excel関数逆引き辞典パーフェクト

4.他ブックにある値は別の関数で

以上、重複分を除いてカウントする方法を解説しました。

 

ただし、カウントしたい範囲が「他のブック(ファイル)」にある場合には、紹介した数式ではうまく判定できません。

 

なぜならCOUNTIF関数やCOUNTIFS関数は、開いていない他のファイルの中身を見ることができないからです。

 

つまり、COUNTIF関数ではない別の関数を使う必要があるということです。

 

この方法は次のページで紹介します。

⇒「別ブック(ファイル)にある値を重複は除外してカウントする方法」へはこちら

 

以上、参考になれば幸いです。

 

また、関数に関連したページもたくさんありますので、以下のリンクから参考にしてください。