【高速】COUNTIFを一瞬でExcel VBAを使わずに終わらせる関数

1.　COUNTIFは重くて時間がかかる

Excelで重複データを探したり、データの個数を調べるときは「COUNTIF」を使われると思います。
しかし、以前私が働いていた職場では20万行を超えることが普通で、COUNTIFを使うと非常に重いもので3時間もかかっていました。

どうにか高速化できないかとインターネットを検索すると同じ悩みを抱えた方がたくさん質問していましたが、VBAマクロを使って難しいプログラムを書いたり、重複を調べるだけで個数までわからないものしか出てきませんでした。
VBAマクロも書ければ簡単なのですが、メンテナンスする人が限られるうえ、別のファイルや別の列等でも使いたい場合に移植するのも一苦労です。

そこで試行錯誤して編み出したのがご紹介する関数です。作業列は2つ必要ですが、Excelの関数だけで一瞬で終わります。
また、VBAの知識がなくても列の指定を変えるだけで色々なファイルで使用できますので、並べ替えと作業列は必須ですがVBAより気軽に使えるかと思います。

Excelファイルが数十万行ともなると関数を貼るという作業だけで数秒かかりますが、その関数を貼る時間の方が長いです。

2.　なぜそんなに高速化できるのか？

COUNTIF関数はデータが10万行ある中から、1つのデータが何個存在するか調べるのに10万行すべてのデータを比較します。
もし1000個のデータがそれぞれ何個存在するか調べるとなると、
100,000×1000個=1億回　の比較をします。
むしろそういったデータベースにCOUNTIFを使用するときは全体に張り付けることが多いので、10万×10万で100億回になるのではないでしょうか。

しかし頭を使うことによって比較回数を大幅に減らすことができるので、単純に考えれば使用したい行数分の１の時間で計算が可能です。
1万行のデータに使用するなら、1万分の1の時間で終わります。1万分の1て意味わからないですよね。どれくらいでしょうか。

10000行のデータにCOUNTIF関数を使用して、多めに見て10分かかっていたとすると、600秒です。
それの10000分の1だと0.06秒です。一瞬です。瞬きする間に終わります。たとえスペック低いPCで100万行あろうが10秒かからないと思います。
そんなPCだと先にメモリが枯渇してフリーズしますが…笑

ではその高速COUNTIFとは、、、