ExcelVBAで関数を作り、抽出条件外のデータを探すテクニック

SUMIFS関数は、ワイルドカード「*」を使用して部分一致によるグループ集計ができます。

しかし、SUMIFS関数による合計値と実際のデータの合計値が一致しない場合、どのデータが集計から漏れているのかを目視で確認するのは、非常に困難です。

Excelの既存のツールや関数では、この問題を解決する手段が存在しません。そこで、ChatGPTにお願いして、新しい関数を作ってもらいました。

こちらのキャプチャは、各データの合計値「523」と、文字列の一部をワイルドカード「*」として利用した抽出条件の計算した合計値「523」は一致しています。

問題は、Rawデータに抽出条件外のデータが混じっていた場合です。

キャプチャのケースでは「53」の値が抽出条件にないため、欠損してしまっています。もし、データが数千件のような場合では、どのデータが未取得なのかを探すのは困難を極めます。

そこで、ChatGPTに関数の生成をお願いします。

あっという間に完成ですね。これをVBAの標準モジュール内に作成します。

1. 「Alt」＋「F11」を押す（VBEの画面）
2. メニューバーの「挿入」から「標準モジュール」をクリック
3. コードを貼り付けて、画面を閉じる

※ VBEの細かい説明は割愛します<(_ _)>

これで準備完了です。

セルに「= IsTextMatched (テキスト,抽出条件の範囲)　」と作成します。VBAで作成した関数を使えるようになっているはずです。

SUMIFS関数で集計されていれば「１」と出力されます。

抽出条件範囲外のデータであれば「０」と出力され、どのデータが取れていないかを検証することができました。

COUNTIF関数を使えば、ワイルドカードを使った抽出条件に対して、該当データが何件あるかは調べられるのですが、その逆はできません。

ちなみに、灰色で示したVBAコードの青文字部分「IsTextMatched」は、自由に別の名前をつけることができますので、好きな関数名にすることができます。

IsTextMatched はChatGPTが命名したものです。

ワイルドカード「*」を利用してSUMIFS関数で行っている人は、このVBAで作成した関数、かなり役立つと思っています。ぜひ試してみてくださいね。

注意：

VBAコードつきのファイルを保存する場合、エクセルマクロブック（.xlsm）で保存する必要があります。エクセルブックのまま上書き保存すると、コードは保存されませんのでご注意ください。

マクロ有効ブックで保存する場合は、名前を付けて保存の際、以下のキャプチャの場所でファイル形式を変更して保存してください。

榊 裕次郎

Excel講師

1981年10月生まれのてんびん座、東京都出身。趣味は、旅行と料理とワイン。2024年は、佐賀県に行って「呼子のイカ」を思いっきり食べたいです。

仕事では2023年も引き続き、青森・秋田・岩手でのお仕事依頼、お待ちしております！