Excel COUNTIF 高速化20分→5秒

ExcelVBA記事を掲載しているWEBサイトは「やさしいExcelVBA」をご覧ください。

高速化Excelアドインは「VLOOKUP関数」をこちらです。

今回はCOUNTIF関数の高速化について書きたいと思います。

1.COUNTIF関数とは

Excelの中でも、VLOOKUP関数同様に、使用される頻度の高い関数の一つかなと思います。
基本的な使い方は下の表の様にたくさんあるデータの中から、A列の検索したい値とD列から一致した個数を抽出します。

結果を出力したいセルに以下の様な数式をB1に入力します。
=COUNTIF($D$2:$D$300001,A2)
この数式をオートフィルでデータのある最後の行までコピーすると、データの価格が抽出出来ます。
ここまでは簡単で、使える人も多いと思います。

2.COUNTIF関数のデメリット

こちらもVLOOKUP関数同様に、非常に便利な関数なのですが、データ量が多いとどうしても処理時間や、再計算に時間がかかってしまいます。
データ量が10万件×10万件・・・、それ以上となると10分、20分固まるなんて事があります。

そんな処理時間を何度も1日しているとやってられません・・・。

膨大な時間がかかってしまいますよね。

3.自作で高速COUNTIF関数を作ってみた

自己紹介で書いていますが、僕はちょー面倒くさがり屋なので毎回数式を打ったり、数分、数十分待ったりが大嫌いなので、自作しみました。

そちらをご紹介いたします。

自分のExcelで常に使えるように「アドイン化」しているため、みなさんと画面が少し違うかもしれませんが、最後まで読んで頂けると同じ環境をご提供できます。

自分が働いているチームにも提供しています。

まず、実行結果も併せてご紹介するために、10万行×30万行のサンプルデータを用意しました。(VLOOKUPのパクリですみません・・・。)

↓の表の右下に表示されているように、A列に10万行の商品列があります。

↓の表の右下に表示されているように、D列に30万行の商品列があります。VLOOKUPの時は10万でしたがカウントさせるために30万にしてみました。

(※念のため乱数で並び替えしてD列はぐちゃぐちゃに並べています。)

普通にCOUNTIF関数を使用してB列にE列の価格を反映させようとした場合、自分の環境では20分以上かかります。

では早速、関数を実行します。

すぐ使えるようにクイックアクセスツールバーに登録しているので、○で囲んだアイコンをポチっと押すだけです。

(3つありますが、他はCOUNTIF以外の関数です。)

↓図の様に、フォームが立ち上がります。

立ち上がったら、検索範囲のテキストボックスがアクティブになっているので、検索したい範囲を選択します。

A2～A100001まで選択してOKですし、A:Aと列指定してもOKです。(列全体指定の場合は若干遅くなります。)

もちろんA1000～A10000と調べたい範囲のみ選択してもOKです。

一部範囲の場合でも出力結果も同じ行範囲に出力されます！
選択すると以下の図の様になります。

次に参照範囲を選択します。

参照範囲のテキストボックスをアクティブにしてから範囲を選択します。

普通のCountしたい列を選択しします。

参照列ももちろん列指定でもOKです。

また、参照範囲は別のExcelファイルでも大丈夫です。

ただし、検索範囲と出力列はフォームを開いたファイルに依存します。

最後に結果を出力したい列をアルファベットで指定します。

小文字でも、AAなどかなり離れた列でも問題ありません。

今回はB列にしました。

3つをそれぞれ指定したら集計ボタンを押します。

指定した出力列に結果が表示されます。

5秒です！

VLOOKUPの時の2秒より遅いですが「10万行×30万行」のためご了承ください。

普通に関数で処理すると20分以上かかりますので、性能としては一緒です。

一度も数式入力などせず、ポチ、ポチ、ポチで上記データ量でも5秒で計算結果が出ました。

ちなみに、もし間違えてデータの存在する列を指定してしまった場合・・・。

ちゃんと、アラートが表示されるので、間違えて消してしまう事もフォローしています。

なぜこの機能を付けたかというと、VBAで出力してしまった結果は「戻る」が出来ないんですよね・・・。(致命的・・・)

なので、間違えないようにアラートを出す必要がありました。

さらに重要なデータの場合は使用する前に上書き保存するとかの保険も推奨します。

今回は実行結果を表示させていますが、提供版は表示されません。

如何でしたでしょうか。

どうしてもExcelって手離れ出来ないソフトになっています。

そんな中、単純作業で高速に処理できる環境は非常に助かります。

さらには、関数を苦手と思っている人も関数という概念すら必要なく

使用できるようにしています。

工夫一つでこんな事も出来るんです。

面白いですよね。

コードも書かず、ファイルをダウンロードしていただき、設定するだけでご使用いただける環境を構築できます。

設定方法は記載致しますので、少しでも使ってみたい！効率化したい！と思って頂けましたら、ご購入して頂けると幸いです。