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