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

問題1の解答例　　　　 topへ

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

UNIQUE関数を使う方法	数式(計算式)で求める方法
作業列を使う方法

UNIQUE関数を使う方法

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

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

重複しない品名の個数は、数式　=SUMPRODUCT(1/COUNTIF(B3:B15,B3:B15)) で求めることができます。
重複しているデータを1件とカウントする数式の解説
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}　となるのがわかります。
  - 数式バーの数式の COUNTIF(B3:B15,B3:B15) 部分を数式バーで選択して、[F9]キーを押すと下図のようになります。
    元に戻すには [Esc]キーを押します。
2. この、{5;3;4;5;3;5;4;1;4;5;3;5;4}　は　=COUNTIF($B$3:$B$15,B3)　で求められる値の配列であるのがわかります。
  - D3セルに=COUNTIF($B$3:$B$15,B3) と入力して、下方向へコピーすると下図のようになります。
    D3:D15セルの値とCOUNTIF(B3:B15,B3:B15)で求めている配列の値が同じであるのがわかります。
3. 例えば、「りんご」なら「5」個あるので、5が5回出てきます。「バナナ」は3が3回出てきます。
  りんごなら、1/5　という逆数にして5を掛ければ1、　同様に、バナナなら　1/3*3=1　ということになり、1種類を1とカウントすることができます。
  数式 =SUMPRODUCT(1/COUNTIF(B3:B15,B3:B15))　の 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　となります。

作業列を使う方法

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

問題2の解答例　　　　 topへ

問題2：重複しない品名を書き出して各品名の個数をカウントしなさい
解答：下図のように、りんご 5、バナナ 3、みかん 4、スイカ 1 となります。

UNIQUE関数を使う方法	重複の削除と数式を使う方法
ピボットテーブルを利用する	統合を利用する

UNIQUE関数を使う方法

Microsoft365ではUNIQUE関数やSpill(スピル)機能が使えます。
UNIQUE関数の詳細な使い方は　UNIQUE関数で重複しない値を取り出す　をご覧ください。

E3セルに　=UNIQUE(B3:B15)　と入力して、重複のない品名を取り出します。
F3セルに　=COUNTIF(B3:B15,E3#)　と入力します。
E3#　の　#はスピル範囲演算子と呼ばれるものです。スピル範囲の全体を参照することができます。

重複の削除と数式を使う方法

重複しない品名を書き出すには、重複データの削除を利用すると簡単です。
品名をコピーして、E列に貼り付けます。
B3:B15セルを選択して、[Ctrl]+[C]でコピーし、E3セルを選択して[Ctrl]+[V]で貼り付けます。
もしくは、B3:B15セルを選択して、[Ctrl]キーを押した状態で、E3セルの位置へドラッグしてコピーします。
[データ]タブの[重複の削除]を実行します。
重複の削除で[OK]ボタンをクリックします。
この時、「先頭行をデータの見出しとして使用する」のチェックは外した状態にします。
重複した品名が削除され、一意の値が残っています。[OK]ボタンをクリックします。
各品名の個数はF3セルに　=COUNTIF($B$3:$B$15,E3)　と入力して、数式を下方向へコピーします。

ピボットテーブルを利用することもできます

データリスト内のセルを選択します。
[挿入]タブの[ピボットテーブル]を実行します。
テーブル/範囲がデータリストの範囲と一致しているのを確認します。
ここでは、「既存のワークシート」にチェックを入れ、場所に　E3セルを指定しました。
[OK]ボタンをクリックします。
ピボットテーブルのフィールドリストで、フィールドの「品名」を行ラベルと値にドラッグして配置します。
ピボットテーブルで各品名の個数が計算されました。

統合を利用することもできます

計算結果を表示するセル　G2セルを選択します。
[データ]タブのテータツールグループにある[統合]をクリックします。
集計の方法を「個数」にします。
統合元範囲内をクリックして、シートの　B2:C15 を選択します。
[追加]ボタンをクリックします。
すると、統合元に　Sheet1!$B$2:$C$15 が登録されます。
統合の基準で　「上端行」「左端列」にチェックを入れます。
[OK]ボタンをクリックします。
計算結果が表示されました。

問題3の解答例　　　　 topへ

問題3：重複しない品名を書き出して各品名の個数を合計しなさい
解答

品名個数

りんご 253

バナナ 147

みかん 172

スイカ 16

UNIQUE関数を使う方法	重複の削除と数式を使う方法
ピボットテーブルを利用する	統合を利用する

UNIQUE関数を使う方法

Microsoft365ではUNIQUE関数やSpill(スピル)機能が使えます。
UNIQUE関数の詳細な使い方は　UNIQUE関数で重複しない値を取り出す　をご覧ください。

E3セルに　=UNIQUE(B3:B15)　と入力して、重複のない品名を取り出します。
F3セルに　=SUMIF(B3:B15,E3#,C3:C15)　と入力します。
E3#　の　#はスピル範囲演算子と呼ばれるものです。スピル範囲の全体を参照することができます。

重複の削除と数式を使う方法

重複しない品名名を書き出すのは上記の問題2と同様に「重複の削除」を利用します。
重複しない各品名の個数の合計は、F3セルに =SUMIF($B$3:$B$15,E3,$C$3:$C$15) と入力して、下方向へ数式をコピーします。

ピボットテーブルを利用することもできます

行ラベルにフィールドの「品名」、値にフィールドの「個数」を配置します。
- 個数の計算方法は「合計/個数」となっていることに注意してください。
  計算方法は、配置したフィールドをクリックして、「値フィールドの設定」を選択します。
- 集計方法タブで「合計」を選択します。

統合を利用することもできます

計算結果を表示するセル　H2セルを選択します。
[データ]タブのテータツールグループにある[統合]をクリックします。
集計の方法を「合計」にします。
統合元範囲内をクリックして、シートの　B2:C15 を選択します。
[追加]ボタンをクリックします。
すると、統合元に　Sheet1!$B$2:$C$15 が登録されます。
統合の基準で　「上端行」「左端列」にチェックを入れます。
[OK]ボタンをクリックします。
計算結果が表示されました。

	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

スポンサードリンク

問題 topへ

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

問題　　　　topへ