スプレッドシート セルやテキストを検索する関数 2つのカテゴリに分けて解説

kabu

やってきました。
みんな大好き「検索関数」の頂上決戦です。

帰家

何の話ですか？？

kabu

あ、はい。
スプレッドシートって「検索する関数」って多くの種類がありますが、なかなか全部を有効活用できている人はいないんじゃないかと思いまして。
そこで今回は色々な検索関数を使用イメージとともに紹介したいと思います。

帰家

私、VLOOKUPくらいしか使っていないので・・・
自分の知らない関数で業務に使えるものがあるのか、掘り出しのヒントになりそうですね！

kabu

ということで
① 範囲内から検索する関数
② 文字列の中から検索する関数
に分けて解説していこうと思います。

できるYoutuber式 Googleスプレッドシート 現場の教科書 できるYouTuber式シリーズ

ブラウザだけで学べる Googleスプレッドシート プログラミング入門

図解！ Googleスプレッドシートのツボとコツがゼッタイにわかる本

頭がよくなるスプレッドシート問題集 入門編

範囲内から検索する関数

kabu

まずは範囲内・・・要は「複数セル」を対象に検索をおこなう関数群です。

VLOOKUP関数

通常の使い方

VLOOKUP関数の内容は次の通りです。

=VLOOKUP(検索キー, 範囲, 指数, [並べ替え済み])

検索キーは範囲の先頭列で、検索をおこなう文字列やセルを指定します。

範囲は検索をおこなう列を先頭に、出力をおこなう列までをおおって指定します。

指数には範囲の中の何列目を出力するかを数値で指定します。

[並べ替え済み]はFALSEを指定すると検索キーに対して完全一致、TRUEを指定すると検索キーに対して最も近い値が出力されます。

使い方のイメージはこんな感じです。
範囲の中で、検索キーと一致した指数列目の値を出力してくれます。

あいまい・ワイルドカード・〜を含む検索

VLOOKUP関数では、あいまい検索（ワイルドカード検索・〜を含む検索）もできます。
例えば、範囲の中で「会社」と入っているものを探す、などですね。

こんな感じに使います。
検索キーに「*」をつけると、ワイルドカードの意味になります。
「検索キー*」なら「〜から始まる」ですし、
「*検索キー」なら「〜で終わる」、
「*検索キー*」なら「〜を含む」と指定できます。

”左”側検索　検索をする列が出力列よりも右にある場合

VLOOKUP関数、長年の欠点であると思われていた、範囲の一番左での検索しかできない問題の解決策がこれです。
範囲の一番左列を検索し、以降右にある指定列を出力するのがVLOOKUP関数で、検索列の左側列を出力させるためには、後に紹介するXLOOKUP関数が使われることがほとんどでした。
しかし、次のやり方では検索列の左側列を出力結果として表示させることが可能です。

通常、範囲は「B3:C」のように指定し、この範囲の一番左にある「B」列が検索キーの検索対象となっていました。
しかしこれを「{C3:C,B3:B}」という方法で記載すると、関数内で列が入れ替わり、「C」列が検索キーの対象となり、「B」列を出力させることができます。

このスクリーンショットでも、上に書いたのと同様の方法で「C」列を検索し、「B」列、要は左側列を出力しています。

kabu

関数の使い勝手を僕の主観で評価してみます。
★5つが最高評価ですね。

「使える場面」はこの関数が活用できるシーンがどれだけ多いか、活躍できる頻度が高いと思われるものは高評価としています。
「扱いやすさ」は関数の数式の読みやすさ、分かりやすさ、簡単さなどです。
「関数の自由度」は関数の機能内で、どのくらい色々なことができるか、ですね。
自由度が高いものは、その関数のみで様々なケースに対応ができる、というイメージです。

AVLOOKUP関数（名前付き関数）

kabu

こちらは僕が作成した「名前付き関数」です。
仕様はVLOOKUP関数とほぼ一緒ですが、検索は一つのセルや文字列ではなく、列範囲が対象になっています。

=AVLOOKUP(キーワード列, 検索範囲, 出力列)

キーワード列には、VLOOKUP関数の検索キーの入力されている列を範囲で指定します。
すると、指定した範囲で検索キーが入っている行に対しては、自動でVLOOKUP関数を反映してくれる、という関数です。

kabu

この関数はデフォルトで入っているものではなく、新しく作成した「名前付き関数」になりますので、もし気になる方はこちらの記事を見てみてください。

スプレッドシート VLOOKUPを列全体に自動で反映する方法

数百、数千行が並ぶリスト全部にVLOOKUPを反映するのが面倒。「関数一つで、VLOOKUPを列範囲に反映してくれる」ことを目的に、ARRAYFORMULA、IFEEROR、VLOOKUPを掛け合わせた「名前付き関数」の作成方法を解説します。

kabu-noma.com

2024.03.18

帰家

「名前付き関数」についてはこちらの記事をどうぞ！！

スプレッドシート 名前付き関数とは？作成方法・実用例の紹介

「名前付き関数」とは？画面キャプチャを中心に分かりやすく解説していきます。実務に使える「名前付き関数」の作成例も紹介しますので、参考にしていただければ嬉しいです。また、登録した名前付き関数を他のスプレッドシートで使う方法についても紹介しています。

kabu-noma.com

2024.03.28

LOOKUP関数

kabu

LOOKUP関数は、昇順で並べられた一列（または一行）の検索範囲に対して検索キーで検索をおこない、同じ位置にある結果の範囲内のセルの値を出力する関数です。

LOOKUP(search_key, search_range|search_result_array, [result_range])

日本語に直すと・・・

LOOKUP(検索キー, 検索範囲, [結果の範囲])

使用イメージはこんな感じです。

数式の構造としては、後に話すXLOOKUP関数と似ていますが、この検索範囲、”昇順”で並べられている必要があります。

”昇順”必須仕様を改善し、列内検索に特化したのがVLOOKUP関数で、行内検索に特化したのがHLOOKUP関数と思っていただいて結構です。
このLOOKUP関数でできることは、VLOOKUP関数、HLOOKUP関数、ひいてはXLOOKUP関数に引き継がれているので、もうなかなか出番はないかと思います。

HLOOKUP関数

kabu

VLOOKUP関数が列内検索（縦検索）に特化しているのに対して、
HLOOKUP関数は行内検索（横検索）に特化した関数です。
VLOOKUP関数の横Verって感じですね。

HLOOKUP(検索キー, 範囲, 指数, [並べ替え済み])

検索キーは範囲の先頭”行”で、検索をおこなう文字列やセルを指定します。

範囲は検索をおこなう行を先頭に、出力をおこなう行までをおおって指定します。

指数には範囲の中の何行目を出力するかを数値で指定します。

[並べ替え済み]はFALSEを指定すると検索キーに対して完全一致、TRUEを指定すると検索キーに対して最も近い値が出力されます。

使い方としては、行の中で検索したい値を検索キーに指定します。
その検索キーと一致した列の、範囲内で指定した行指数の値を出力します。

検索キーを変えれば、その検索キーと同じ列にある値に置き換わります。

XLOOKUP関数

kabu

XLOOKUP関数はVLOOKUP関数やHLOOKUP関数の上位互換の感がある関数です。
自由度は高いですが、数式がやや見づらく、一目で何の引数なのか分かりづらいという欠点があります。

以下ではVLOOKUP関数のように列内検索（縦検索）で説明していますが、範囲を行に変えれば行内検索（横検索）も可能です。

XLOOKUP(検索キー, 検索範囲, 結果の範囲, [見つからない場合の値], [一致モード], [検索モード])

検索キーは検索範囲の中で検索をおこなう文字列やセルを指定します。

検索範囲は検索をおこなう列範囲を指定します。
一列のみを指定します。

結果の範囲では検索範囲と同じ行数の一列を範囲として指定します。
検索キーが検索範囲の中で一致した行数に対応する行を結果の範囲内から出力します。

[見つからない場合の値]はエラーがでた際の出力結果ですね。
VLOOKUP関数を使う際に「=IFERROR(VLOOKUP(…」と書いている方も多いと思いますが、これを一つの関数で実現した親切設計です。

[一致モード]はVLOOKUP関数の[並べ替え済み]に相当する引数です。
これを「0」（VLOOKUP関数の「FALSE」）を指定すると検索キーに対して完全一致、「1」（VLOOKUP関数の「TRUE」）を指定すると検索キーに対して最も近い値が出力されます。

[検索モード]は・・・
「1」：先頭から末尾に向かって検索
「-1」：末尾から先頭に向かって検索
「2」：検索範囲が昇順で並べ替えられていると想定してバイナリ検索
「-2」：検索範囲が降順で並べ替えられていると想定してバイナリ検索
という指定ができます。

使い方のイメージはこんな感じです。
しかし、VLOOKUP関数の範囲列入れ替えのテクニックを覚えてしまえば出番は少なくなる関数かもしれませんね。

MATCH関数

kabu

MATCH関数は、範囲の中で検索キーが一致した「位置」を返す関数です。
これだけで使うというより、OFFSET関数やINDEX関数と併用することが多いと思います。

MATCH(検索キー, 範囲, [検索の種類])

検索キーは範囲の中で検索をおこなう文字列やセルを指定します。

範囲は検索をおこなう”一列”の範囲や”一行”範囲を指定します。

[検索の種類]は・・・
「1」：範囲が昇順で並べ替えられている場合に検索キー以下の最も大きい値を返します。
「0」：範囲が並べ替えられていない場合に完全一致を返します。
「-1」：範囲が降順で並べ替えられている場合に検索キー以上の最も小さい値を返します。

帰家

ふんふん。
私はきっと「0」しか使わない！！

使用イメージはこんな感じです。
範囲は”一つの列や行”ということに注意して使いましょう。

FILTER関数

kabu

FILTER関数は、範囲に対して条件に合った範囲＝複数行・複数列を出力する関数です。

FILTER(範囲, 条件1, [条件2, …])

この範囲は検索をおこなう範囲ではなく、出力をする対象範囲です。
1列のみでもいいですし、複数列をまとめて指定することもできます。

条件1には範囲と同数の行範囲を指定し、結果がTRUEかFALSEになる条件式を指定します。
例えば、「A1:A>=1」、「B3:B<>””」などですね。

条件2以降にも同様の条件式を指定できます。
複数条件を指定すると、より絞ったフィルタリングをおこなえます。

使用イメージはこんな感じ。
VLOOKUP関数などと違うのが、一致した項目が複数ある場合、その全てを出力する点ですね。
例えば条件に「〜以上」といったような指定をした場合、該当する全ての行が出力されます。

VLOOKUP関数と同じような使い方をすることもできます。

QUERY関数

kabu

QUERY関数は、データ範囲の中の指定列に対してクエリを指示することで任意のデータを抽出する関数です。

数式の内容は次の通り。

QUERY(データ, クエリ, [見出し])

データには出力範囲とクエリの対象範囲を含めて指定します。

ではそのクエリについてですが、スプレッドシートの一般的な関数とは違う独自の書き方をします。
クエリは「””」（ダブルクオーテーション）で囲んで記載します。
「“Where B contains ‘ABC'”」こんな感じですね。
これがどういう意味かというと、「B」列で「ABC」が含まれる行を抜き出すよう指定しています。

他にも以下のような「句」があります。

QUERY関数の使用イメージはこんな感じ。
FILTER関数同様、条件に一致した行が全て出力されます。

また、「句」を複数組み合わせてクエリを組むこともできます。

クエリはセルで指定することも可能です。
「contains ‘”&B3&”‘”」等ですね。
文字列は「”」で囲うことをお忘れなく。

kabu

QUERY関数は非常に自由度が高く、幅広い使い方ができます。
この関数の機能によって、こんなシステムも作れるので興味がある方は見てみてください。

スプレッドシートでの検索・抽出システム 範囲全体からフリー入力で検索

スプレッドシートでセルに「任意のテキスト」を入力すると、全ての列に対して検索・抽出をおこなうシステムが作りたい。ということで、QUERY関数を使いフリー入力から検索・抽出をおこなえるシステムの作り方をご紹介します。

kabu-noma.com

2024.03.30

スプレッドシート QUERY ソート順を選べるシステムを作る

デフォルト機能の「フィルタ」ではなく、プルダウンリストからソート順を選び、それに準じた順番をQUERY関数でソートをかけるシステムを作成していきます。

kabu-noma.com

2024.04.02

文字列の中から検索する関数

kabu

続いて、「文字列」の中から指定文字や記号を検索する関数について紹介していきます。

FIND関数

FIND(検索文字列, 検索対象のテキスト, [開始位置])

kabu

FIND関数は、検索対象文字列の中で検索文字列が何文字目にあるかを割り出す関数です。

検索文字列は、「”あいう”」とテキストで指示しても「B3」とセルで指定しても構いません。
僕はよく混乱しちゃうのですが、この検索文字列が「検索キー」の方ですね。

対して検索対象のテキストは、検索文字列＝「検索キー」で検索をされる側の元テキストです。
この中から指定した検索文字列が何文字目にあるのか探します。

例えば「C」という文字列は「ABC株式会社」の中の何文字目にあるのか・・・
そんな使用イメージになります。

SUBSTITUTE関数

kabu

SUBSTITUTE関数は、検索対象のテキスト内から検索文字列＝検索キーを探し、別の文字列に置き換えることができる関数です。

SUBSTITUTE(検索対象のテキスト, 検索文字列, 置換文字列, [出現回数])

使い方としては、FIND関数に似ていますが、検索対象のテキストと検索文字列の順番がFIND関数とは違うので注意してください。

また、置き換えをする文字列の出現位置を指定することもできます。

何も指定しなければ、検索文字列と一致した文字全てを置き換え。
例えば「1」と指定した場合、1箇所目に出てきた一致文字と置き換え。
「2」なら2箇所目に出てきた一致文字と置き換え、となります。

REGEXEXTRACT関数

kabu

REGEXEXTRACT関数は、指定した正規表現を文字列から抽出できる関数です。

帰家

正規表現・・・ですか？？

正規表現（せいきひょうげん、英: regular expression）は、文字列の集合を一つの文字列で表現する方法の一つである。正則表現せいそくひょうげんとも呼ばれ、形式言語理論の分野では比較的こちらの訳語の方が使われる^[1]。まれに正則式せいそくしき^[2]あるいは正規式せいきしきと呼ばれることもある。

https://ja.wikipedia.org/wiki/%E6%AD%A3%E8%A6%8F%E8%A1%A8%E7%8F%BE

帰家

おおお・・・！！

kabu

はい。
要するに「半角アルファベット」とか、「全角数字」とか、そういう枠・グループのことですね。
この関数は、文字列ではなく、「半角アルファベット」とか、「全角数字」の文字グループ単位で文字を抜き出しますよって関数です。

REGEXEXTRACT(テキスト, 正規表現)

例えば「[A-Za-z]+」と指定すると、大文字小文字のアルファベットを指定することができます。

正規表現はこんな感じで指示することができます。

帰家

へぇ〜
文字列の中の「数字を抽出」とかができる訳ですね！

まとめ

kabu

今回は
① 範囲内から検索する関数
② 文字列の中から検索する関数
2つの検索方法で分け、それぞれに属する関数について解説しました。

kabu

急に関数の評価をつけだして失礼しました。

帰家

本文を読まない方（？？）でも
使う場面が多いのか、難しいかなどが視覚的に分かって良いかと思います！

帰家

検索・抽出する関数が、なんでこんなに種類があるのかと思ったら、少しずつ用途が違うからなんですね！

kabu

そうですね。
難しい関数はできることも多いですが、その分数式が煩雑になりがちです。
用途に一致した関数を適宜使用し、極力すっきりとした数式にするのがベストかと思います。