値の重複を調べる[Excel]
値の重複の有無をチェックする（マクロ無し縛り）
久しぶりにマクロ無し縛りでExcelのシートを作ることになり、半分忘れかけていたことがあったので、備忘録的に記しておく。
１億2000万余名マイナス１億2000万名の非常に数少ないこのブログの読者の方ならおよそ見当がついていると思うが、私はExcel本体の機能に精通する前にVBAにハマってしまい、畸形的な発達を遂げてしまっているので、ツッコミどころがたくさんあると思う。遠慮無くツッコミを入れてくださるとありがたい。
範囲内の値の延べ個数を求める
COUNTA関数を使う
まあ、これは初歩中の初歩でしょう。
こんな表があるとする。範囲はD2:D15。
んで、テキトーなセルに
=COUNTA(D2:D15)
と入力するだけ。
ご覧のように、引数で指定した範囲（D2:D15）の値を返してくれる。
ちなみに、私は「COUNTA」の「A」ってどういう意味なのか知らないｗ
範囲内の値の実個数を求める
SUMPRODUCT関数とCOUNTIF関数を使う
最初に言っておく。
最初にこれ考えたやつ、天才!!!!!!!!
そのぐらい、感動しましたよ、私は。
同じようにD2：D15に値が入っているとして、テキトーなセルに
=SUMPRODUCT(1/COUNTIF(D2：D15,D2：D15))
これで、重複したやつを「1」と勘定して実個数を求めることができる。
最初これ見たときは何のことやらサッパリだったんですが、ちょっと意味が分かったときにマジで感動しましたよ。
SUMPRODUCTというのは、ざっくり言うと１行ごとの計算結果を合計する、というもの（雑過ぎる説明でスマン）。
んで、引数の中のCOUNTIFの第２引数に注目。ここは普通、「検索値」１つが入るはず。ここがセル範囲になっているので、「COUNTIF（D2：D15,D2：D15）」だけだと、
COUNTIF(D2：D15,D2), COUNTIF(D2：D15,D3), COUNTIF(D2：D15,D3), ･･･COUNTIF(D2：D15,D15)
みたいになっているとイメージしたら良いかと。
すげえのはここから。
COUNTIFの返り値を逆数にしている
ってところですよ。
たとえば、D2:D15に同じ値が３回出てくるとすれば、当然その値を検索値としたCOUNTIFの返り値は「3」。
で、それが「3」回出てくることになる。
単純にそれらをSUMPRODUCTしたら「9」になるところ、COUNTIFの返り値を逆数にしていることにより、
1/3 + 1/3 + 1/3 で「1」になる
んですよ!!!!!!!!!! ちょっとこれ、凄くないですか？
この状態だと、D2:D15の値に重複はないので、「14」が返っている。値の延べ個数と同じ。
んで、このように、「呼延灼」を無駄に４つにしてみると、ちゃんと「11」になっている。
要するに、「1/COUNTIF（D2：D15,D4）」、「1/COUNTIF（D2：D15,D7）」、「1/COUNTIF（D2：D15,D9）」、「1/COUNTIF（D2：D15,D12）」の計算結果が全て「1/4」になるために、最終的にSUMPRODUCTした結果が「呼延灼」については「1」になるというわけだ。
TRUEかFALSEを返すようにする
ワークシート関数オンリーで行く場合は、むやみに複数の関数をネストすると可読性が嫌がらせレベルにまで下がるので、なるべく複数のセルに分けるようにしている。
この場合も、重複のあるなしを１つの数式で書こうと思えば書けるが、複数セルに分ける。
これは、セルの中に
=(F2=F3)
と書いただけ。
延べ個数と実個数が同じ、すなわち重複なしならTRUE、異なる、すなわち重複があるならFALSEを返す。
今回は、「重複があったら困る」という想定なのでこうしたが、逆に「重複がないと困る」という場面なら
=(F2<>F3)
にすればよかろう。
おわりに
SUMPRODUCTとCOUNTIFという基本的な関数を組み合わせて、「重複を除いた実個数を数える」という数式を発明した人への感謝と感動を伝えたくて書きました。
