Excel。表内で重複しないデータ数を一発で算出する簡単な方法はないのかな。
＜SUM＋COUNTIF関数＞
帳票や表のデータが重複していない件数を算出するには、なかなか大変なケースもあります。
例えば、次の表。
B2:D4に入力されている地域名のうち、重複を除いた件数を、D6に算出したいわけです。
データタブの「重複削除」をつかって、残った件数を数えるというのもアリですが、今回のように、ひとつの列内にデータがあるわけではないので、「重複削除」をつかうことはできません。
また、別のところで、コピーして、ひとつの列をつくってから、「重複削除」を行う必要があります。
こうなると、作業は簡単でも、面倒になってきます。
また、Excel VBAでプログラム文をつくるというのも、ちょっと面倒です。
そこで、SUM＋COUNTIF関数を組み合わせた数式で、算出することができます。
D6に設定する数式は、
=SUM(1/COUNTIF(B2:D4,B2:D4))
この数式だけで、重複を除いた件数を算出することができます。
別のセルをつかって説明します。
COUNTIF関数で範囲の中に、その文字が何件あるかを算出させています。
B9に、
=COUNTIF($B$2:$D$4,B2)
という数式を設定して、オートフィルで数式をコピーしています。
すると、ハワイは表内で3件あることが算出されるわけです。
ただ算出しただけでは、1より大きい値のデータが重複していることがわかるだけです。
そこで、この算出した値を、「1」で除算してみましょう。
B9の数式は、
=1/COUNTIF($B$2:$D$4,B2)
と修正して、オートフィルで数式をコピーしています。
この値全部を合算すれば、重複を除いた件数になります。
なぜ、「1」で除算したのかというと、例えば、2件あった場合、1÷2とすれば、「0.5」になるわけです。
2件ということは、別のセルで2と算出されたところも「0.5」となります。
0.5+0.5なので、1とすることができます。
4件あれば0.25+0.25+0.25+0.25 で、1とすることができる。
あとは、合算させればいいわけですね。
このような方法を使えば、数式だけで重複を除いた件数を算出することもできます。
