- Home »
- エクセル練習問題：目次 »
- 重複するデータをカウントする/集計をする
更新:2024/5/7;作成:2015/4/24
- 重複しているデータから重複していない品名の数をカウントしたり、重複していない品名を書き出して、それらの出現回数を求めます。
また、各品名の数量の合計数を計算します。
- 各例題では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
- 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)) で求めることができます。
- 実は、=SUM(1/COUNTIF(B3:B15,B3:B15)) という配列数式で求めることができます。
動的配列が使える、Excel for Microsoft365、Excel2021以外では[Ctrl]+[Shift]+[Enter]で入力する必要がありますので、SUMPRODUCT関数を使う方が一般的かと思います。
なお、Microsoftは「動的配列数式とスピル配列の動作」で以下のようなコメントを出しています。
「CTRL+SHIFT+ENTER (CSE) を介して入力されたレガシ配列数式は、下位互換の理由で引き続きサポートされていますが、今後は使用しないでください。」
-
重複しているデータを1件とカウントする数式の解説
- ここではスピル(Spill,動的配列)が利用できる環境(Excel for Microsoft365)で説明しています。
- 数式 =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} となるのがわかります。
- 各品名の出現回数を計算しているのがわかります。
「りんご」は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とカウントすることができます。
-
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} といった逆数の配列になっています。
- SUMPRODUCT関数でこれらの和を計算しています。
結果は 4 となります。
- D列を作業列として利用します。
D3セルに =COUNTIF($B$3:B3,B3) と入力して、同じ品名の個数を上のセルに何個あるのかをカウントします。
求める個数(重複しない品名の個数)は作業列で 1 となっているセルの個数をカウントすればよいので =COUNTIF(D3:D15,1) で求めることができます。
- 問題2：重複しない品名を書き出して各品名の個数を合計しなさい
- 解答
| 品名 |
個数 |
| りんご |
253 |
| バナナ |
147 |
| みかん |
172 |
| スイカ |
16 |
UNIQUE関数を使う方法
- E3セルに =UNIQUE(B3:B15) と入力して、重複のない品名を取り出します。
- F3セルに =SUMIF(B3:B15,E3#,C3:C15) と入力します。
E3# の #はスピル範囲演算子と呼ばれるものです。スピル範囲の全体を参照することができます。
- 問題3：重複しない品名を書き出して各品名の個数をカウントしなさい
- 解答：下図のように、りんご 5、バナナ 3、みかん 4、スイカ 1 となります。
- Excel for Microsoft365、Excel2021では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セルの位置へドラッグしてコピーします。
- [データ]タブの[重複の削除]を実行します。
- 重複の削除に関する警告が表示されました。
選択しているセル範囲E3:E15の上のセルE2が入力済みなので、隣接するセルを含むように拡張しますか？という問い合わせになります。
ここでは、必要ないので、「現在選択されている範囲を並べ替える」にチェックを入れ、[重複の削除]ボタンをクリックします。
- 重複の削除ダイアログボックスで[OK]ボタンをクリックします。
- 重複した品名が削除され、一意の値が残っています。[OK]ボタンをクリックします。
- 各品名の個数はF3セルに =COUNTIF($B$3:$B$15,E3) と入力して、数式を下方向へコピーします。
- データリスト内のセルを選択します。
[挿入]タブの[ピボットテーブル]を実行します。
- テーブル/範囲がデータリストの範囲と一致しているのを確認します。
- ピボットテーブルのフィールドリストで、フィールドの「品名」を行ラベルと値にドラッグして配置します。
ピボットテーブルで各品名の個数が計算されました。
- 計算結果を表示するセル F2セルを選択します。
[データ]タブのテータツールグループにある[統合]をクリックします。
- 集計の方法を「個数」にします。
統合元範囲内をクリックして、シートの B2:C15 を選択します。
[追加]ボタンをクリックします。
すると、統合元に Sheet1!$B$2:$C$15 が登録されます。
統合の基準で 「上端行」「左端列」にチェックを入れます。
[OK]ボタンをクリックします。
- 計算結果が表示されました。
スポンサードリンク
Home｜エクセル練習問題：目次｜重複するデータをカウントする/集計をする
PageViewCounter
Since2006/2/27