データ検索・抽出に強いスプレッドシート関数一覧

その判断、ちょっと待ってください。

確かにIFS関数は万能ですが、無理にまとめようとすると、数式はどんどん巨大化します。

結果として、作った本人ですら、後から意味がわからなくて修正できないという事態になりかねません。

スプレッドシートには、シチュエーションごとに適した専門の関数があります。

これらを適材適所で使うだけで、苦労して作ったけど、後から修正不可能になる関数を作らずにすみます。

この記事では、実務でデータを探す・抽出する際に知っておくべき6つの関数と、その使い所を解説します。

データ検索・抽出に強いスプレッドシート関数6選

今回紹介する関数は、こちらです。

見慣れた関数もあれば、初めて見る関数もあるのではないでしょうか？

それでは、これらの関数をどんな場面で使い分けるのが正解か具体的に解説していきます。

XLOOKUP

一言説明

表からキーワードを検索し、同じ行にある指定した項目を抽出する関数

どんな時に使う

• 商品IDから「商品名」や「単価」を自動入力させたい時
• IDを入れるだけで「氏名・部署・住所」の様な複数列を一度に抽出したい時
• 履歴データから最新の情報（下の行から）を取りたい時

XLOOKUP関数の注意点

XLOOKUPの注意点は、条件に合う1行分のデータしか返さないことです。

例えば、条件に一致するデータが全て欲しいと思っても、XLOOKUPは上から（もしくは下から）探して一番最初に見つかったその一件だけで処理を終了させます。

FILTER

一言説明

特定の条件に合致するデータを、元の表からすべて抜き出して自動でリスト化する関数

どんな時に使う

• 「未対応」や「請求書未送付」が含まれる行だけを別シートに全て表示させたい時
• 今年度分だけのデータだけを抽出してレポートを作りたい時
• 「東京支店」かつ「売上100万以上」のように、複数の条件で絞り込みたい時

FILTER関数の注意点

最大の注意点は、抽出先に空きスペースがないとエラーになることです。

FILTER関数は条件にヒットした件数分だけ、下の行（または右の列）に向かってデータを自動展開します。

そのため、展開しようとする場所に文字や数式が一つでも入っていると、エラーになり、データが一切表示されません。

出力先は、常に空白にしてスペースを空けておく必要があります。

QUERY

一言説明

特定の条件に合致するデータを抽出・並べ替え・集計を1つの数式で完結させる万能関数

どんな時に使う

• 何十列列もある巨大な表から「A列（名前）とZ列（売上）だけ」を抜き出して表示したい時
• データを抽出すると同時に、日付順や金額順に「並べ替え」て表示したい時
• 担当者ごとに「売上の合計」や「案件数」を自動集計した表を作りたい時

QUERY関数の注意点

最大の注意点は、データがうまく取れなくても、見た目上は正常に動いているように見えてしまうという仕様です。

例えば、VLOOKUP関数やFILTER関数の場合は、探しているデータが見つからなかったりすると、#N/A や #VALUE! といったエラーがハッキリ表示されます。

ところが、QUERY関数の場合は、データをうまく読み込めなかったとしても、エラーを出さずに自分がわかる範囲のデータだけを抽出します。

特によくあるのが、同じ列の中に「数字」と「文字」が混ざってしまっているケースです。

例えば、「1000」「2000」といった数値が並んでいる列の中に一箇所だけ「1000円」と記載された行があるとします。

この時、エラーが出てくれれば、おかしな所があるとわかるのですが、QUERY関数は、エラーを出さず、おかしな所を空白にして出力します。

見た目上は綺麗にデータが抽出しているように見えるため、本当はあるはずのデータが、実はないという恐ろしい事態が起こり得ます。

エラーが出ないからこそ、計算結果が合わなくなるまでミスに気づけないのがQUERY関数の最大の落とし穴です。

UNIQUE

一言説明

選択した範囲内で重複したデータを1つにまとめて抽出する関数

どんな時に使う

• 大量の売上データから今日売れた商品はどれか一覧にして抽出したい時
• 顧客名簿から、どの地域の人がいるかというエリアリストを抽出したい時
• データの中に重複が混じっていないか、チェックしたい時

UNIQUE関数の注意点

最大の注意点は、人間から見たら同じで内容も、データとして少しでも違えば別物とみなされることです。

例えば、「株式会社 A」と「（株）A 」は、人間から見ると同じ会社だと分かりますが、UNIQUE関数はこれらは別物として扱います。

結果として、「重複を消したはずなのに、同じ項目がいくつも並んでいる」という、不完全なリストが出来上がってしまうリスクがあります。

SORT

一言説明

指定した範囲内で、指定した列を基準に昇順と降順で並べ替えて表示する関数

どんな時に使う

• 売上データから金額が高い順に並べ替えて、ランキング表を作りたい時
• UNIQUE関数で抜き出したバラバラな商品リストを、あいうえお順に整えたい時
• 日付が新しい順に並び替えて、最新の履歴を常に一番上に表示させたい時

SORT関数の注意点

最大の注意点は、並び替えられた順番を固定して保存できないことです。

SORT関数は、常に最新のデータを追いかけ続けます。

例えば、SORT関数を使って月ごとの売上順リストを作りたいとします。

「2月時点のランキングを残しておきたい」と思っても、3月のデータが追加されれば、SORT関数の結果もリアルタイムで最新の順位に書き換わってしまいます。

つまり、過去の順番や特定の時点のデータとして保存しておきたい時には注意が必要です。

IMPORTRANGE

一言説明

別のスプレッドシートにあるファイルデータを、今のシートにそのまま引っ張ってくる関数

どんな時に使う

• 「各支店の売上ファイル」を、1つの「本部まとめファイル」に自動集約したい時
• 見せたくない情報を隠して外部の人と情報を連携したい時
• 1つのファイルを入力用ファイルと閲覧用ファイルで分けたい時

IMPORTRANGEの注意点

最大の注意点は、便利だからといって使いすぎると、ファイルが重くなり、動かなくなることです。

IMPORTRANGE関数は、インターネットを通じて常に外部ファイルと通信をしています。

例えば、「10個のファイルから同時にデータを集める」「何万行ものデータを一気に引っ張る」といった使い方をすると、ファイルが重くなって動かなくなります。

結果として、いつまで経ってもデータが一切表示されないファイルになるという注意点があります。

まとめ

ここまでデータ検索・抽出に強いスプレッドシート関数について解説しました。

以下にそれぞれの使用用途についてまとめました。

• 1つの情報を探す → XLOOKUP関数
• 条件に合うデータを全部抽出 → FILTER関数
• 並べ変えも集計も一気に → QUERY関数
• 重複を取り除いてデータ抽出 → UNIQUE関数
• 順番を整えてデータ抽出 → SORT関数
• 別ファイルのデータをまとめる → IMPORTRANGE関数

もうこれからは、複雑なIFS関数を組み合わせて、無理やりデータを抜き出す必要はありません。

それぞれのシチュエーションに適したデータ抽出関数を使うことであなたの作業時間は劇的に改善されます。

ぜひともこの記事を参考にして、あなたの業務効率化に活用してください。