重複するデータをカウントする/集計をする:エクセル練習問題

更新:2025/9/16;作成:2015/4/24

問題　　　　topへ

• 問題1：重複しない品名の個数を求めなさい　　問題1の解答
• 問題2：重複しない品名を書き出して各品名の個数を合計しなさい　　問題2の解答
• 問題3：重複しない品名を書き出して各品名の個数をカウントしなさい　　問題3の解答
  

  	B	C
  2	品名	個数
  3	りんご	44
  4	バナナ	75
  5	みかん	12
  6	りんご	89
  7	バナナ	53
  8	りんご	62
  9	みかん	50
  10	スイカ	16
  11	みかん	66
  12	りんご	44
  13	バナナ	19
  14	りんご	14
  15	みかん	44

問題1の解答例　　　　topへ

• 問題1：重複しない品名の個数を求めなさい
• 問題1の解答　4

UNIQUE関数を使う方法

• Excel for Microsoft365、Excel2021ではUNIQUE関数が使えます。
  UNIQUE関数の詳細な使い方は　UNIQUE関数で重複しない値を取り出す　をご覧ください。
• =UNIQUE(B3:B15)　で重複しない品名を取り出すことができます。
  
• UNIQUE関数で取り出した品名は文字列なのでCOUNT関数ではなく、COUNTA関数で数えます。
  E3セルに　=COUNTA(UNIQUE(B3:B15))　と入力します。
  

数式(計算式)で求める方法

• 重複しない品名の個数は、数式　=SUMPRODUCT(1/COUNTIF(B3:B15,B3:B15)) で求めることができます。
  
• 動的配列が使える、Excel for Microsoft365、Excel2021以降のバージョンでは
  =SUM(1/COUNTIF(B3:B15,B3:B15)) で求めることができます。
  Excel2019以前のバージョンではでは[Ctrl]+[Shift]+[Enter]で入力する必要があります。
  • なお、Microsoftは「動的配列数式とスピル配列の動作」で以下のようなコメントを出しています。
    「CTRL+SHIFT+ENTER (CSE) を介して入力されたレガシ配列数式は、下位互換の理由で引き続きサポートされていますが、今後は使用しないでください。」
  

• 重複しているデータを1件とカウントする数式の解説

  • ここではスピル(Spill,動的配列)が利用できる環境(Excel for Microsoft365)で説明しています。
  1. 数式 =SUMPRODUCT(1/COUNTIF(B3:B15,B3:B15))　の COUNTIF(B3:B15,B3:B15)部分の計算結果は
     下図のように、{5;3;4;5;3;5;4;1;4;5;3;5;4}　となるのがわかります。
     
  2. 各品名の出現回数を計算しているのがわかります。
     「りんご」は5個あるので、5が5回出てきます。
     「バナナ」は3個あるので、3が3回出てきます。
     「みかん」は４個あるので、4が４回出てきます。
     「スイカ」は１個あるので、1が１回出てきます。
     りんごなら、1/5　という逆数にして5回足せば1/5+1/5+1/5+/1/5+1/5=1、
     バナナなら　1/3+1/3+1/3=1　ということになり、1種類を1とカウントすることができます。
  3. 1/COUNTIF(B3:B15,B3:B15))　とすることで逆数が得られます。
     下図のように{1/5;1/3;1/4;1/5;1/3;1/5;1/4;1/1;1/4;1/5;1/3;1/5;1/4}　といった逆数の配列になっています。
     
  4. SUMPRODUCT関数でこれらの和を計算しています。
     結果は　4　となります。
     

作業列を使う方法

• D列を作業列として利用します。
  D3セルに　=COUNTIF($B$3:B3,B3)　と入力して、同じ品名の個数を上のセルに何個あるのかをカウントします。
  求める個数(重複しない品名の個数)は作業列で　1　となっているセルの個数をカウントすればよいので　=COUNTIF(D3:D15,1)　で求めることができます。