- Home »
- エクセル練習問題:目次 »
- 重複するデータをカウントする/集計をする
2021/5/4
- 重複しているデータから重複していない品名の数をカウントしたり、重複していない品名を書き出して、それらの出現回数を求めます。
また、各品名の数量の合計数を計算します。
- 各例題ではExcel for Microsoft365 の最新の関数や機能で求める方法や、従来の関数で求める方法を書いています。
お使いのExcelのバージョンで利用できる方法をお選びください。
- 問題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:重複しない品名の個数を求めなさい
- 問題1の解答 4
- Microsoft365ではUNIQUE関数が使えます。
UNIQUE関数の詳細な使い方は UNIQUE関数で重複しない値を取り出す をご覧ください。
- =UNIQUE(B3:B15) で重複しない品名を取り出すことができます。
- UNIQUE関数で取り出した品名は文字列なのでCOUNT関数ではなく、COUNTA関数で数えます。
E3セルに =COUNTA(UNIQUE(B3:B15)) と入力します。
- 重複しない品名の個数は、数式 =SUMPRODUCT(1/COUNTIF(B3:B15,B3:B15)) で求めることができます。
-
重複しているデータを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]キーを押します。
- この、{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)で求めている配列の値が同じであるのがわかります。
- 例えば、「りんご」なら「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:重複しない品名を書き出して各品名の個数をカウントしなさい
- 解答:下図のように、りんご 5、バナナ 3、みかん 4、スイカ 1 となります。
- 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:重複しない品名を書き出して各品名の個数を合計しなさい
- 解答
品名 |
個数 |
りんご |
253 |
バナナ |
147 |
みかん |
172 |
スイカ |
16 |
- 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]ボタンをクリックします。
- 計算結果が表示されました。
スポンサードリンク
Home|エクセル練習問題:目次|重複するデータをカウントする/集計をする
PageViewCounter
Since2006/2/27