スプレッドシートは、業務を進めていく上で活用されている機会が多くなってきました。スプレッドシートではグラフ作成や表計算を容易に行うことができ、複数人での共有も容易に行うことができる便利なツールです。
このようなスプレッドシートを活用するために、重要なのが関数です。その中でも検索関数は、業務において幅広い場面で活躍します。この記事では、スプレッドシートの検索関数について解説します。
チャット一括管理アプリ「OneChat」が今なら初月無料!!
OneChatのダウンロードはこちらから
スプレッドシート検索関数の基本
ここでは、Googleスプレッドシート検索関数の基本的な情報について紹介します。
検索関数VLOOKUP関数の使い方
検索関数であるVLOOKUP関数は、指定した範囲内から検索条件に当てはまるデータを抽出する関数です。
VLOOKUPのVは垂直を意味する英単語「Vertical」の頭文字、LOOKUPは探すを意味する英単語です。「垂直に該当する値を探す」と考えるとわかりやすいでしょう。
この関数を活用することにより、無数にあるデータの中から必要な特定データの抽出を素早く行うことができます。
検索関数VLOOKUPの構文は次の通りです。
=VLOOKUP（検索キー,範囲,番号,［並び替え済み］）
| 項目 | 内容 |
| 検索キー | 検索したい値を入力しましょう。文字列などの数字以外を指定したい場合は、「”〇〇”」といったようにダブルクォーテーションで囲った状態で入力する必要があります。 |
| 範囲 | 検索を行う範囲の指定を行います。この際、検索キーの項目が検索範囲の最も左の列に配置されている必要があります。そのため、列の2番目や3番目を検索することはできません。また、「列番号」より検索範囲の列数が少ない設定は行えません。 |
| 番号 | 検索範囲の中で、取得したい値が左から何番目の列の値なのかを指定します。列番号は数字で指定する必要があります。 |
| 並び替え済み | 「TRUE」または「FALSE」の値を指定します。「TRUE」では、近似照合が行われます。「並び替え済み」の項目を指定していない際は、「TRUE」として処理されます。「FALSE」では、完全一致で検索が行われます。基本的には「FALSE」を入力しておくと良いでしょう。 |
参考：VLOOKUP – Google ドキュメント エディタ ヘルプ
部分一致を活用したデータ検索
検索関数で判定を行う場合、判定条件は基本的に「完全一致する」か「完全一致しない」の二択です。しかし、検索条件の一部に該当する「部分一致」によるデータ検索ができないわけではありません。
部分一致によるデータ検索方法はいくつかありますが、ここでは「VLOOKUP関数」による部分一致検索方法について解説します。
VLOOKUP関数で部分一致検索を行う場合、「検索キー」の記述方法を工夫することにより実施できます。特定の文字列や数値などの値を検索キーとして含むことを指定するVLOOKUP関数の記述方法は、次の通りです。
=VLOOKUP（*検索キー*,範囲,番号,［並び替え済み］）
このように、検索キーを「*」で囲うことにより、入力した検索キーの部分一致検索が行えるようになります。数値ではなく文字列による検索を行いたい場合は、「”*〇〇*”」のように完全一致検索と同様に文字列をダブルクォーテーションで囲った内側を「*」で囲いましょう。
エラー対策と効率的な数式作成
検索関数VLOOKUPを利用している中で、「#N/A」などのエラー表示がされることがあるかと思います。
以下では、よくあるエラーやその原因について詳しく解説します。
- 完全一致指定において一致するものが存在しない
- 絶対参照としていないため、コピー時に範囲がズレる
- 空白を指定しているのに0が表示される
- 「TRUE」を指定した際に正しく表示されない
完全一致指定において一致するものが存在しない
完全一致で指定して数式を入力した際、一致するものがないと「#N/A」のエラー表示で返されます。そのため、完全一致で検索した際にエラーが表示された時は、指定したものと完全一致しているものがあるかどうかを確認しましょう。
絶対参照としていないため、コピー時に範囲がズレる
入力した関数を別にセルへとオートフィルを使いコピーする場合、絶対参照で入力していないと参照範囲にズレが生じてしまいます。
例えば、検索対象範囲が「B4:D8」の場合は、「$B$4:$D$8」とすることで絶対参照として入力することができます。
直接入力する方法だけでなく、ショートカットを用いて絶対参照にする方法もあります。
- 数式が入力されているセルを選択する
- 絶対参照にしたい値の後ろ（右）にカーソルをおく
- 「F4」キーまたは、「Fn」＋「F4」キーを押す
空白を指定しているのに0が表示される
空白部分を指定しているにもかかわらず、空白ではなく0が表示されてしまう場合です。
このような場合、数式の末尾に「&””」を入力することにより空白を表示することができます。
「TRUE」を指定した際に正しく表示されない
並び替え済みの指定を「TRUE」とした際に、結果が正しく表示されないことがあるかもしれません。これは、参照する範囲が「昇順」に並んでいないことが原因と考えられます。
「TRUE」として検索する場合、並び替えを「昇順」としていないとうまく結果が出ないことがあるため注意しましょう。
GoogleスプレッドシートとExcelの違い
Googleスプレッドシートは、Google社が提供している表計算ソフトのことです。インターネットを介して利用できるWebアプリケーションの一種のため、利用に際してダウンロードやインストールを行う必要がなく、「Googleアカウント」さえあれば誰でも無料で利用できます。
Googleスプレッドシートはクラウド型のため、作成したファイルはGoogleドライブに自動保存され、変更も自動的に保存される点が優れています。
Excelは、Microsoft社が提供している表計算ソフトのことです。パソコンにアプリをインストールして使うため、インターネット環境がなくても作業することができます。
Excelは有料ソフトなので、利用には費用がかかります。価格は契約プランや販売形式によりことなるため、Microsoft 365で確認してみてください。
PCのスペックにもよりますが、クラウド型であるGoogleスプレッドシートと比べ、オンプレミス型のExcelは処理速度が早い点は魅力です。
| Googleスプレッドシート | Excel | |
| 使用方法 | クラウド型 | インストール型 |
| 料金 | 無料 | 有料 |
| オフライン編集 | 不可 | 可 |
| 保存方法 | 自動保存 | 手動保存 |
| 同時編集 | 可 | 可（限定的） |
| デザイン性 | 罫線やグラフの種類、フォントの数は限られている | 罫線やグラフの種類、フォントの数が豊富 |
Excelと比べてGoogleスプレッドシートに向いている人の特徴は次の通りです。
- お金をかけずに使いたい方
- スマートフォンやタブレット端末からアクセスを行いたい方
- 複数人でデータの共有や同時編集を行いたい方
- 編集したデータをこまめに保存するのが面倒な方
- 急なデータ喪失が不安な方
- デザインがある程度シンプルでも問題ない方
関連記事：Googleスプレッドシート関数活用術！効率をアップしよう
他の便利な検索関数の活用法
VLOOKUP関数以外にも、スプレットシートには便利な検索関数があります。
XLOOKUP関数のマスター法
VLOOKUP関数は、検索可能範囲が左端のみという制約がありました。一方、XLOOKUP関数は、検索対象を検索範囲内に含まれる全ての列に指定することができます。
XLOOKUP関数の構文は、次の通りです。
=XLOOKUP（検索キー,検索範囲,結果の範囲,見つからない場合の値,一致モード、検索モード）
| 項目 | 内容 |
| 検索キー | 検索したい値を入力しましょう。文字列などの数字以外を指定したい場合は、「”〇〇”」といったようにダブルクォーテーションで囲った状態で入力する必要があります。これは、いずれの関数で入力する場合でも同様です。 |
| 検索範囲 | 検索を行う範囲の指定を行います。ここでは、単一の行か列の指定を行う必要があります。 |
| 結果の範囲 | 指定した範囲の内、どの行または列の値を取得したいのかを指定します。 |
| 見つからない場合の値 | 検索範囲内に指定した検索キーが見つからない場合に表示するものを指定します。省略した場合は「#N/A」が表示されます。 |
| 一致モード | 完全一致と近似値のどちらで検索するかを指定します。0：完全一致（省略した場合の設定）1：完全一致、または検索キーより大きい次の値を一致とする-1：完全一致、または検索キーより小さい次の値を一致とする2：ワイルドカードの文字列に一致する値を一致とする |
| 検索モード | 検索する順序（方法）を指定します。1：先頭から末尾（省略した場合の設定）-1：最後のエントリから最初のエントリ2：指定範囲をバイナリ検索（範囲内を昇順で並べておく）-2：指定範囲をバイナリ検索（範囲内を降順で並べておく） |
参考：XLOOKUP 関数 – Google ドキュメント エディタ ヘルプ
HLOOKUP関数で行方向のデータ操作
HLOOKUP関数は、VLOOKUP関数やXLOOKUP関数と同じように指定した範囲内から検索条件に当てはまるデータを抽出する関数です。
VLOOKUP関数が検索する値が垂直方向（列方向）に並んでいる際に利用するのに対し、HLOOKUP関数は水平方向（行方向）に並んでる際に用います。
HLOOKUPのHは水平を意味する英単語「Horizontal」の頭文字、LOOKUPは探すを意味する英単語です。「水平に該当する値を探す」と考えるとわかりやすいでしょう。
HLOOKUP関数の構文は次の通りです。
=HLOOKUP（検索キー,範囲,番号,［並び替え済み］）
| 項目 | 内容 |
| 検索キー | 検索したい値を入力しましょう。文字列などの数字以外を指定したい場合は、「”〇〇”」といったようにダブルクォーテーションで囲った状態で入力する必要があります。 |
| 範囲 | 検索を行う範囲の指定を行います。この際、検索キーの項目が検索範囲の上端の行に配置されている必要があります。そのため、行の2番目や3番目を検索することはできません。また、検索範囲内に検索する結果の値を含めておく必要があります。 |
| 番号 | 検索範囲の中で、取得したい値が上端の行から何番目の行の値なのかを指定します。行番号は数字で指定する必要があります。 |
| 並び替え済み | 「TRUE」または「FALSE」の値を指定します。「TRUE」では、検索キー以下で最も近い一致が返されます。該当データがない場合は「#N/A」が返されます。「FALSE」では、完全一致のみが返されます。一致する値が複数の場合は、最初に見つかった値に対応するセルの内容が返され、該当データがない場合は「#N/A」が返されます。 |
参考：HLOOKUP – Google ドキュメント エディタ ヘルプ
MATCH関数でセル位置を取得
大量にあるデータの中から、必要なデータの場所を調べる際に便利な関数がMATCH関数です。
MATCH関数は、指定した範囲内で文字列や値の検索を行い、それが「何行目」または「何列目」に出てくるのかを教えてくれます。
MATCH関数の構文は次の通りです。
=MATCH（検索キー,範囲,［検索の種類］）
| 項目 | 内容 |
| 検索キー | 検索する値を入力しましょう。文字列などの数字以外を指定したい場合は、「”〇〇”」といったようにダブルクォーテーションで囲った状態で入力する必要があります。 |
| 範囲 | 検索キーを検索する範囲を指定します。 |
| 検索の種類 | 検索範囲の中から、検索キーを検索する方法を指定します。1：検索キー以下の最大値で検索（範囲内を昇順で並べておく）0：完全一致-1：検索キー以上の最小値で検索（範囲内を降順で並べておく）省略した場合は「１の型」で検索を行います。 |
参考：MATCH – Google ドキュメント エディタ ヘルプ
連番を自動挿入するCOLUMN関数
引数に指定したセルの列番号を調べることができる関数が、COLUMN関数です。
COLUMN関数の構文は次の通りです。
=COLUMN（［セル参照］）
| 項目 | 内容 |
| セル参照 | 列番号を返すセルの指定を行います。省略した場合は数式を入力したセルとなります。 |
例えば、B2セルに「=COLUMN（）」と入力すると、B2セルは2列目にあるため、「2」と表示されます。
B2セルを起点として連番を表示したい場合は次の手順で可能です。
- B2セルに「=COLUMN（）-1」と入力する
- 数式を連番で表示したいセルにコピーする
こうするとB2セルに「1」と表示され、横のセルに連番が表示されます。
参考：COLUMN – Google ドキュメント エディタ ヘルプ
スプレッドシート検索機能の実用例
ここでは、スプレッドシートの検索関数機能のにおける実用例について紹介します。
複数条件でデータ管理を効率化
スプレッドシートの関数入力では、条件判定のためにさまざまな条件式の入力を必要とする場面も多く見られます。
シンプルな条件式であれば問題にはなりませんが、「AまたはBならばC」や「AかつBならばC」と言ったように、複数条件を組み合わせて利用することも多々あります。
そのような場合に便利な関数が、「AND関数」と「OR関数」です。
AND関数
指定した任意の条件の内、すべてに一致しているかどうかを検索する論理関数です。
AND関数の構文は次の通りです。
=AND（論理式1［,論理式2,論理式3,…］）
| 項目 | 内容 |
| 論理式 | TRUEまたはFALSEの結果を返す条件式の指定を行います。最大255件まで条件を追加できます。 |
AND関数では、指定された論理式とすべて一致している場合はTRUEとなります。1つでも異なる場合はFALSEが返されます。
参考：AND – Google ドキュメント エディタ ヘルプ
OR関数
指定した任意の条件の内、いずれかに一致しているかどうかを検索する論理関数です。
OR関数の構文は次の通りです。
=OR（論理式1［,論理式2,論理式3,…］）
| 項目 | 内容 |
| 論理式 | TRUEまたはFALSEの結果を返す条件式の指定を行います。最大255件まで条件を追加できます。 |
OR関数では、指定された論理式のいずれかと一致している場合はTRUEとなります。すべてと一致しない場合はFALSEが返されます。
参考：OR – Google ドキュメント エディタ ヘルプ
業務で役立つ関数の組み合わせ
今回紹介した、VLOOKUP関数とMATCH関数を組み合わせた使用方法について紹介します。
VLOOKUP関数とMATCH関数を組み合わせることにより、「特定の行と列の交点に存在する値について検索する」ことができます。
例えば、スプレッドシートに入力された以下のようなデータから、特定月と特定項目の値を検索したいとします。
| 行\列 | B | C | D |
| 2 | 月 | 収入（円） | 支出（円） |
| 3 | 1月 | 5000 | 2000 |
| 4 | 2月 | 10000 | 4000 |
| 5 | 3月 | 15000 | 6000 |
| 6 | 4月 | 20000 | 8000 |
| 7 | 5月 | 25000 | 10000 |
| 8 | 6月 | 30000 | 12000 |
このデータを元に、以下の関数を入力します。
=VLOOKUP(“3月”, B3:D7, MATCH(“支出”, B2:D2, 0), FALSE)
これは、「B3〜D7の範囲で”3月”を検索して、見つかった行のB2〜D2の範囲で”支出”の列に該当する値を返す」という意味です。
この数式では、3月の支出に合致する「6000」が表示されます。
IF関数と他の関数を組み合わせる
IF関数とは、指定した条件に一致するかどうかで結果をわけて表示させる関数です。
例えば、「ある条件に一致する場合はA、一致しない場合はB」といったように、IF関数では値を変更することができます。
IF関数は非常に便利な関数であるため、今回紹介したVLOOKUP関数やAND関数、OR関数などの複数の関数と組み合わせて使われる場面が多いです。
IF関数の構文は次の通りです。
=IF(論理式, TRUE値, FALSE値)
| 項目 | 内容 |
| 論理式 | 論理値（TRUEまたはFALSE）を表す式やそうした式を含むセルの参照のことを指します。論理式では、数値の比較のみではなく、日付の比較や文字列の比較を行うこともできます。また、条件の指定は四則計算などを用いて行うこともできます。 |
| TRUE値 | 論理式がTRUEである場合に返ってくる値のことです。数字ではなく文字列で返したい場合は、「”〇〇”」といったようにダブルクォーテーションで囲った状態か、適切なテキストを含んだセルを参照して入力する必要があります。 |
| FALSE値 | 論理式がFALSEである場合に返ってくる値のことです。TRUE値と同様に、数字ではなく文字列で返したい場合は、「”〇〇”」といったようにダブルクォーテーションで囲った状態か、適切なテキストを含んだセルを参照して入力する必要があります。省略をすることができますが、その場合はFALSEの際に空白となります。 |
例えば、スプレッドシートに入力された以下のようなデータを用いたIF関数による条件指定を見ていきましょう。
| 行\列 | B | C | D |
| 2 | 氏名 | 点数 | 評価 |
| 3 | A | 62 | |
| 4 | B | 85 | |
| 5 | C | 47 | |
| 6 | D | 72 | |
| 7 | E | 66 | |
| 8 | F | 55 |
これをもとに、点数の項目が80以上を「優」、65〜79を「良」、51〜64を「可」、50以下を「不可」を評価に入力する条件指定をしましょう。
この条件指定では、C3の数値が65以上である場合、65以上なら「優」または「良」、64以下なら「可」または「不可」となる条件付けをした数式を入力します。
まず、D3に次の数式の入力を行います。
=IF（C3 >=65,IF（C3>=80,”優”,”良”）,IF（C3>=51,”可”,”不可”））
これをD3からD8までオートフィルでコピーすることにより、すべての評価のセルに条件付けを行うことができます。
| 行\列 | B | C | D |
| 2 | 氏名 | 点数 | 評価 |
| 3 | A | 62 | 可 |
| 4 | B | 85 | 優 |
| 5 | C | 47 | 不可 |
| 6 | D | 72 | 良 |
| 7 | E | 66 | 良 |
| 8 | F | 55 | 可 |
続いて、IF関数とAND関数を組み合わせた使用方法例について紹介します。
| 行\列 | B | C | D | E |
| 2 | 氏名 | 前期点数 | 後期点数 | 結果 |
| 3 | A | 62 | 75 | |
| 4 | B | 85 | 92 | |
| 5 | C | 47 | 55 | |
| 6 | D | 72 | 64 | |
| 7 | E | 66 | 80 | |
| 8 | F | 55 | 60 | |
| 9 | 平均 | 64.5 | 71 |
上記のデータをもとに、前期と後期の点数がどちらとも平均点以上である場合を「◯」、そうでない場合を「追試」とします。まず、E3に次の数式の入力を行います。
=IF（AND（C3>=$C$9,D3>=$D$9）,”◯”,”追試”）
これをE3からE8までオートフィルでコピーすることにより、すべての結果のセルに条件付けを行うことができます。
| 行\列 | B | C | D | E |
| 2 | 氏名 | 前期点数 | 後期点数 | 結果 |
| 3 | A | 62 | 75 | 追試 |
| 4 | B | 85 | 92 | ◯ |
| 5 | C | 47 | 55 | 追試 |
| 6 | D | 72 | 64 | 追試 |
| 7 | E | 66 | 80 | ◯ |
| 8 | F | 55 | 60 | 追試 |
| 9 | 平均 | 64.5 | 71 | ◯ |
参考：IF – Google ドキュメント エディタ ヘルプ
サービス・Webサイトのデータ抽出
Googleスプレッドシートの関数を活用して、サービス・Webサイトのデータ抽出を行う場合は、IMPORTXML関数を活用しましょう。
IMPORTXML関数は、HTMLやXMLなどのWebページから特定データのインポートの際に使われる関数です。
MPORTXML関数の構文は次の通りです。
=IMPORTXML(url, xpath_query, locale)
| 項目 | 内容 |
| URL | インポートしたいWebページのURLです。入力するURLは（http://などの）プロトコルも含める必要があります。URLの値は、「”〇〇”」といったようにダブルクォーテーションで囲った状態か、適切なテキストを含んだセルを参照して入力しましょう。 |
| XPath クエリ | ページ内で特定のデータを指定するためのパスのようなものです。XPathの詳細については、「XPath Tutorial（英語）」を参照してください。 |
| locale | データ解析の際に使用する言語と言語/地域コードです。省略した場合は、ドキュメントの言語/地域が使用されます。 |
URLの取得方法
URLに関しては、ほとんどの方がお分かりかと思いますが、ブラウザの上部に表示されている記号や英文字が羅列されている場所です。その箇所をコピーすることで取得することができます。
XPathの取得方法
XPath（エックスパス）の取得方法は、URLの取得方法と比べて複雑です。
- XPathを取得したいページを右クリックする
- 「検証」をクリックする
- 右側にソースコードが羅列されたような画面が表示される
- 青く塗られている記述の部分にカーソルを合わせ右クリックする
- 「Copy>Copy XPath」をクリックする
参考：IMPORTXML – Google ドキュメント エディタ ヘルプ
よくある質問
スプレッドシートの関数利用に際して、よくある質問について紹介します。
スプレッドシートで関数を検索するにはどうすればいいですか？
スプレッドシートの関数は次の手順で見つけることができます。
- メニューバーの「挿入」タブをクリック
- 「関数」を選択する
- 関数が項目ごとに表示される
- 任意の関数を選択する
スプレッドシートの一覧から検索するには？
スプレッドシートには、利用可能な数式を一覧で表示する機能はありません。
ただし、Googleは公式サイトで「Google スプレッドシートの関数リスト」を掲載しているので、ここから数式検索を行うことができます。
利用可能な数式は膨大のため、探すのは容易ではないですがキーワードによる絞り込みも可能です。
スプレッドシートの数式はどうやって探すの？
スプレッドシート内に入力済みのすべての数式を表示する方法は次の通りです。
- メニューバーの「表示」タブをクリック
- 「数式のリストを表示」を選択
これにより、結果ではなく数式自体が表示されます。
また、ツールバーより「数式リスト」をクリックすることでも同様の結果を得ることができます。
まとめ
今回は、スプレッドシートの検索関数について解説しました。
基本的な関数やその使い方はスプレッドシートもExcelも大きな違いはありません。いざ必要となった時に活用できるように、どんな関数があるのかについて知っておくと良いでしょう。
スプレッドシート検索関数に関する重要用語
| 用語 | 説明 |
| セル | スプレッドシート上に表示されているマス目のことです。セル内に数値や文字といったデータの入力を行うことができます。 |
| 数式 | セル内に入力された「=（イコール）」から始まる式のことです。 |
| 絶対参照 | 参照するセルを固定化している状態のことを指します。 |
| 相対参照 | 参照するセルを固定化していない状態のことを指します。 |
| ワイルドカード | 任意の文字を指示する際に用いる特殊な文字記号のことです。スプレッドシートでは主に「?」や「*」が用いられます。「?」は任意の1文字、「*」は任意の0文字以上の文字列を表します。 |
