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

問題 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個あるので、4が4回出てきます。
     「スイカ」は1個あるので、1が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($B3ドル:B3,B3) と入力して、同じ品名の個数を上のセルに何個あるのかをカウントします。
  求める個数(重複しない品名の個数)は作業列で 1 となっているセルの個数をカウントすればよいので =COUNTIF(D3:D15,1) で求めることができます。