SQLのような「NOT IN」条件を期待してQUERY関数を使おうとすると、GoogleスプレッドシートのQUERY関数単体では別範囲の参照条件を直接書けないため、エラーやつまずきの原因になりがちです。
本記事では、QUERY関数で仮想配列を作って除外を実現するテクニックと、実務で最もシンプルかつ高速に抽出できる「FILTER関数」を用いたおすすめの抽出方法を詳しく解説します。
除外リスト照合の全体フローと仕組み
まずは、スプレッドシート内で「元リスト」と「除外リスト」を突き合わせ、どのように除外データをフィルタリングするのか、全体構造を確認しておきましょう。
元データと除外リストをCOUNTIFで照合し、一致件数が0件のものだけを抽出する仕組み
ポイントは「COUNTIF関数で除外リスト内に何件ヒットするか」を判定基準にすることです。
除外リストに電話番号が存在していれば「1以上」が返り、存在していなければ「0」が返ります。
この「判定結果が 0」の行だけを拾い上げれば、除外リストに載っている連絡先を確実にカットした安全なリストが完成します。
前提となるシート構成の準備
ここでは、以下のような3つのシート構成を例にして数式を解説します。
- シート名「元リスト」: A列に電話番号、B列に顧客名（2行目以降がデータ部）
- シート名「除外リスト」: A列に除外したい電話番号（2行目以降がデータ部）
- シート名「抽出結果」: 別シート。先頭セルに数式を入力して自動反映させる
※シート名が日本語の場合は、数式内で「元リスト!A2:B」のように感嘆符（!）を付けてシートを指定します。
よりシンプルでおすすめなFILTER関数を使う方法
実務で「除外リストにないデータを取り出したい」という場合、実はQUERY関数よりも「FILTER関数」を使うほうが数式が短く、直感的でトラブルが起きにくいため強く推奨されます。
電話番号と顧客名をまとめて抽出する数式
抽出先シートのセル（例: A2）に以下の数式を1つ入力するだけで、条件に一致する全行が自動展開（スピル）されます。
=FILTER(
元リスト!A2:B,
元リスト!A2:A <> "",
COUNTIF(除外リスト!A2:A, 元リスト!A2:A) = 0
)電話番号列（A列のみ）を抽出する場合
=FILTER(
元リスト!A2:A,
元リスト!A2:A <> "",
COUNTIF(除外リスト!A2:A, 元リスト!A2:A) = 0
)数式のポイント解説
- 元リスト!A2:A <> “”: 空白行が抽出結果に混ざらないように除外しています。
- COUNTIF(除外リスト!A2:A, 元リスト!A2:A) = 0: 元リストの電話番号が除外リストの中に「0件（含まれていない）」行だけを通過させます。
元データ側に判定用の作業列を増やす必要が一切なく、数式1つで完結するのが大きな強みです。
QUERY関数を使って仮想列と結合して抽出する方法
GoogleスプレッドシートのQUERY関数では、SQLの「WHERE Col1 NOT IN (SELECT …)」のような副問合せ（サブクエリ）が使えません。
そのため、QUERY関数で除外を実現するには、波括弧 { } を使って「元データ」の後ろに「COUNTIFの結果列」を仮想的に合体させた配列をデータソースとして渡すテクニックを使います。
電話番号と顧客名をまとめて抽出する数式
=QUERY(
{元リスト!A2:B, ARRAYFORMULA(COUNTIF(除外リスト!A2:A, 元リスト!A2:A))},
"SELECT Col1, Col2 WHERE Col1 IS NOT NULL AND Col3 = 0"
)電話番号列（A列のみ）を抽出する場合
=QUERY(
{元リスト!A2:A, ARRAYFORMULA(COUNTIF(除外リスト!A2:A, 元リスト!A2:A))},
"SELECT Col1 WHERE Col1 IS NOT NULL AND Col2 = 0"
)QUERY数式の構造と仕組み
- 波括弧 {元リスト!A2:B, ARRAYFORMULA(…)}: カンマで横方向に結合し、3列目（Col3）として「除外判定カウント」を一時的に作っています。
- ARRAYFORMULA: 複数行に対して一括でCOUNTIFを計算するために必要となります。
- WHERE Col3 = 0: 仮想的に追加した3列目が 0（除外リストに存在しない）行のみを指定しています。
- Col1 IS NOT NULL: 空白行が拾われるのを防止します。
FILTER関数とQUERY関数の比較と使い分け
どちらの関数を採用すべきか迷ったときは、以下の比較表を参考にしてください。
要件や処理の複雑さに応じて適切な関数を選択するのがコツ
| 比較項目 | FILTER関数 | QUERY関数 |
|---|---|---|
| 数式の書きやすさ | ◎ 非常に直感的でシンプル | △ 仮想配列やCol指定が必要 |
| 計算負荷・軽さ | ◎ 動作が軽く安定 | ○ 大規模データではやや重い |
| 特定列の抽出・並べ替え | △ SORTやCHOOSECOLSとの併用が必要 | ◎ SELECT句やORDER BYで自在に制御可能 |
| 推奨利用シーン | 通常の除外抽出全般 | 除外と同時に並び替えや集計もまとめたい場合 |
基本的には「FILTER関数」を第一選択にし、「除外と同時に一部の列だけを抜き出して昇順に並べ替えたい」といった高度な要求がある場合に「QUERY関数」を採用するのが最もスマートです。
電話番号照合で「除外されない・一致しない」ときの原因と対策
スプレッドシートで電話番号を突き合わせる際、「同じ番号に見えるのに正しく除外されない」というトラブルが頻発します。よくある原因と解決策を押さえておきましょう。
先頭の「0」が消えて数値扱いになっている
スプレッドシートに「09012345678」と入力した際、セルの書式が自動で数値と判定されると「9012345678」のように先頭のゼロが消えてしまいます。
文字列の「09012345678」と数値の「9012345678」は別物と判定され、一致しません。
対策: 元リストと除外リストの両方の電話番号列を選択し、上部メニューの「表示形式」→「数字」→「書式なしテキスト」に設定してください。
ハイフンの有無や全角半角の不一致
片方が「090-1234-5678」、もう片方が「09012345678」のようにハイフンの有無が異なると一致しません。
また、全角ハイフン（ーや―）と半角マイナス（-）の違いも不一致の原因になります。
対策: 事前にSUBSTITUTE関数を用いてハイフンを除去した統一列を作成するか、以下のように数式内でハイフンを除去して比較します。
=SUBSTITUTE(SUBSTITUTE(A2, "-", ""), "ー", "")前後に不要な半角スペースが入っている
CSVインポートやコピー＆ペーストの際、電話番号の前後や末尾に見えないスペースが含まれていると照合が失敗します。
TRIM関数を使って前後の空白を除去しておくと安心です。
まとめ
- スプレッドシートで除外リストを照合して取り除くには「COUNTIF(除外範囲, 元範囲) = 0」の判定が基本。
- 最もシンプルかつ実務でおすすめなのは「FILTER関数」。作業列なしで一発抽出できる。
- QUERY関数を使う場合は、波括弧 { } を使ってCOUNTIF結果を仮想列として横結合し、「WHERE ColX = 0」で絞り込む。
- 照合トラブルを防ぐため、電話番号は「書式なしテキスト」に設定し、ハイフンや空白の有無を事前に統一しておく。
日々のリストクレンジングや営業アプローチ前の除外作業は、手動で行うとミスや重複が発生しやすくなります。
今回ご紹介したFILTER関数やQUERY関数を活用して、安全かつスピーディーにクリーンなデータ抽出を実現してください。
