Excelで大量のデータを扱う際、重複を除いた値や種類をカウントするにはどうすれば良いか困ったことはありませんか？
本記事では重複を除いた値のカウント方法について関数を使った二つの方法を解説します。
この方法を用いる事で簡単に重複を除いた形で値のカウントが出来るようになります。
指定したデータ範囲の中から重複を除いた値のカウントが出来るようになる。
またExcel VBAで重複削除しデータ整理、カウントする方法について解説した記事もありますので、宜しければこちらも参考にしてください！
重複を除いた値のカウント方法
次のデータを例にそれぞれの関数を用いた方法で解説していきます。
①SUMPRODUCT(1/COUNTIF(範囲,範囲)
この方法はCOUNTIF関数とSUMPRODUCT関数を組合わせた方法で、重複するデータを1つのデータとしてカウントし合計値を算出してくれます。
使用関数
使用する関数の定義は次の様になります。
定義：SUMPRODUCT(配列1,配列2,配列3,・・・)
- 複数配列の要素同士を掛け合わせ、その結果の総和を算出する。
- 今回は配列が1つの為、掛け合わせは行われず配列の値の総和を算出する役割を持つ。
定義：COUNTIF(範囲,検索条件)
- 指定した範囲において検索条件に一致するデータの個数をカウントして算出する。
- 今回は「指定した範囲」=「検索条件」なので、範囲内の各データに対し個別に条件が適応され計算結果を配列の構造で算出する。
次の項目からデータを用いた説明を行いますが、長くなりますのでポイントだけ押さえたい場合は次の3点だけ覚えてください。
- COUNTIF(範囲,検索条件)の引数「範囲」と「検索条件」には同じデータ範囲を設定する。
- COUNTIF関数の逆数の総和を求める事で重複を除いた値のカウントが出来る。
- データ範囲に空白がある場合は数式をSUMPRODUCT((範囲<>””)/COUNTIF(範囲,範囲&””))にし、重複と空白を除いた結果を出せるようにする。
実例
実際に数式をセルF4に入力して商品種類をカウントしてみます。
今回カウントする範囲はセルのB4:B10の範囲なのでこれを数式の範囲に代入し、
と入力すると、計算結果は「3」と表示され商品が3種類あることが分かります。
実際に商品データが同じ物同士に色を付けてみるとA,B,Cの3種類になり算出した値と同様の結果が得られます。
次に計算の途中過程について確認するため、セルC4:C10の範囲に
を入力してみます。
この結果はセルの範囲B4:B10の中にそれぞれAが2個、Bが２個、Ｃが3個ある事を表しています。
同様にD4:D10の範囲に数式で使用したCOUNTIF関数の逆数の形である
を入力すると次の様になります。
この時商品Aの値に着目してみるとそれぞれが「0.5」と出力されており、その合計は0.5+0.5=1になります。
同様の結果が商品B,Cにもいえて、それぞれの逆数の合計が1となるのでこれらを全て足すと合計値が「3」となります。
この様にCOUNTIFの結果を逆数にし、それらの総和をとる事で重複を除いた形で商品種類をカウントする事が出来ます。
※1/COUNTIF(B4:B10,B4:B10)を分数書式で表記した例
悠逆数の算出結果が小数の書式で分かりにくい時は、分数に書式を
変更すると理解し易くなります。
商品Aの値に注目してみると算出結果はそれぞれ「1/2」となっており、これは「データ範囲全体で商品Aが2個あり、その中の1個」を各計算結果が示しています。
この結果よりデータ範囲内の同じ種類のデータを全て足すと1(1/2+1/2=2/2=1)となる為商品Aを1つのデータとしてカウントする事が出来ます。
補足：範囲内に空白セルがある場合の注意点
この関数の組合せは便利でありますがデータ範囲に空白が存在するとエラーが生じ、正常に個数をカウント出来なくなります。
空白でエラーとなる原因
エラー文の説明より「0または空のセルで除算された事によるエラー」が原因で生じていることが分かります。
これについて実際に数式を見ていきます。
セルF4に入力した数式内のCOUNTIF(B4:B10,B4:B10)を選択し、F9キーを押して計算結果を表示すると次の配列の形で値が算出されます。
悠数式中で計算項目を選択した状態でF9キーを押すと、その選択した項目の計算結果を算出してくれるのでおススメです！
COUNTIF(B4:B10,B4:B10)={2;2;2;2;0;2;2}
この配列は指定した範囲B4~B10が検索条件となったCOUNTIF(B4:B10,B4:B10)の計算結果をそれぞれ表しており、セルB8の空白に該当する結果が0になっていることが分かります。
次に数式内の1/{2;2;2;2;0;2;2}を選択しF9キーで計算結果を表示すると
1/{2;2;2;2;0;2;2}={0.5;0.5;0.5;0.5;#DIV/0!;0.5;0.5}
となり１を0で割った為「#DIV/0!」のエラーが発生していることが分かり、項目の冒頭であった「0または空のセルで除算された事によるエラー」が生じている事が分かります。
空白のエラー対策
対策としては次のポイントを踏まえて、数式を変更する事で空白と重複を除いた値のカウントを行うことが出来ます。
- COUNTIF関数での計算結果に0を生じさせない(空白時のカウントも行える様にする)
- 空白セルの場合の計算結果を除外する様に条件を加える
変更後の数式
SUMPRODUCT((範囲<>””)/COUNTIF(範囲,範囲&””))
・(範囲<>””)
条件式を表し、これを入れる事で空白の時の計算結果を除外する様になる
・COUNTIF(範囲,範囲&””)
&””を検索条件の引数に追加する事により、空白もカウント出来るようにする
(0を生じないようにする)
実際にセルF4に範囲をB4:B10とし
を入力すると空白を除いた算出結果「3」が出力されます。
なお計算過程は次の通りになります。
②COUNTA(UNIQUE(範囲))
こちらはCONTA関数とOffice365かOffice2021で使えるUNIQUE関数を用いた方法で先程の方法よりも手軽に計算することが出来ます。
ポチップ
使用関数
それぞれの使用関数の定義は次の様になります。
定義：COUNTA(範囲)
- 指定範囲の空白ではないセルの個数を求める関数
定義：UNIQUE(範囲)
- 指定範囲に含まれるデータから重複を除いたものを取り出す関数
実例
実際に数式をセルF4に入力して商品種類をカウントしてみます。
今回カウントする範囲はセルのB4:B10の範囲なのでこれを数式の範囲に代入し、
と入力します。
数式中のUNIQUE(B4:B10)を選択し、F9キーを押すと次の算出結果になります。
この{“A”,”B”,”C”]をCOUNTA関数がカウントした結果「3」と出力され、重複を除いた形で商品種類をカウントすることが出来ます。
以上で解説は終了になります。
件数が多くなるほど重複する値のカウントは難しくなりますが、こうした関数を用いる事で手軽にカウントする事が出来るので積極的に使っていきましょう。
また計算の数式は最初はただ使用するだけでも良いですが、時間がある時にでも良いので
- 「何故こうなるのか?」
- 「途中計算で何を行っているのか?」
など疑問を持ち内容を調べていくとより理解が深まるので、分からない内容が出てきた時はぜひ調べてみて下さい。
最後までお読み頂きありがとうございました。
