エクセルで文字列や数値を検索する関数｜複数条件や部分一致など

エクセルで文字列や数値を検索する関数はたくさんありますが、この記事ではビジネスの場でも特によく使われる6つの関数（VLOOKUP関数、FIND関数、MACTH関数、COUNTBLANK関数、XLOOKUP関数、SUMIF関数）を紹介します。

汎用性が高く、工夫次第で膨大なデータを効率よく処理したり分析したりできる関数ばかりですので、ぜひ各関数の例題を見ながらご自身の職場の具体事例に当てはめてみてください。

エクセルで文字列を検索するVLOOKUP関数
エクセルで特定の文字が文字列の何番目か検索するFIND関数
エクセルで文字列がある列／行を検索するMACTH関数
エクセルで空白のセルをカウントするCOUNTBLANK関数
エクセルで複数条件を設定して検索するXLOOKUP関数
エクセルで特定の条件に一致したデータを検索・合計するSUMIF関数
エクセルで文字列や数値を検索する関数を改めておさらい

エクセルで文字列を検索するVLOOKUP関数

VLOOKUP関数は用意されたデータ群から文字列を検索し、セットとなる値を抽出する時に使用される関数で、構文は「=VLOOKUP（検索値, 範囲, 列番号, 検索の型）」です。

以下では具体的な使い方の手順を紹介します。例では商品ID、商品名、価格がセットになったデータベースがあり、任意の商品IDを入力すると商品名と価格が表示されるようにします。

STEP.1

商品名の欄に「=VLOOKUP（B2, B7:D17, 2, FALSE）」と入力しましょう。検索値は商品ID欄に入力された値、範囲はデータベースのデータ範囲です。ここではデータベースの2列目にあたる「商品名」を取り出したいので「2」と入力します。

検索の型は値が見つからなかった時の処理を指定するもので、TRUEかFALSEを入力します。完全に一致するもののみ検索の対象にしたい場合は、完全一致する値が見つからない時にエラーを表示してくれる「FALSE」を使用するとよいでしょう。

STEP.2

価格の欄に「=VLOOKUP（B2, B7:D17, 3, FALSE）」と入力しましょう。価格の欄から値を取得したいので、列番号が「3」となっています。

STEP.3

商品IDの欄に任意の商品IDを入力してみましょう。データベースにある正しい値が取得できていればOKです。

エクセルで特定の文字が文字列の何番目か検索するFIND関数

FIND関数とは、対象の文字列の中で探したい文字が最初から何文字目に現れるのかを検索できる関数で、他の関数と組み合わせて使用することが多い関数です。構文は「=FIND(検索文字列,対象,開始位置)」です。

以下具体的な使い方の手順を紹介します。例では、カッコを含む文字列の中からカッコがその文字列の中で何番目になるのか、その値を取得します。カッコを含む文字列と、その隣にカッコの位置を表示させる欄を用意しましょう。

STEP.1

カッコの位置を表示させたいセルに「=FIND(“(“,B2,1)」と入力しましょう。「（」の位置を検索したいので、第一引数にはダブルクオテーション「”」で「（」を囲い指定します。半角、全角を間違えないようにしましょう。

第二引数には検索対象のB2セルを、第三引数には検索を開始する文字の位置を指定します。ここでは文字列の最初から検索したいので、「1」を入力しています。

STEP.2

入力できたら、オートフィル機能を使用して全てのセルに関数を入力します。

STEP.3

次に文字を取り出す関数LEFT関数と組み合わせてカッコ以外の文字列を抽出してみましょう。隣のセルに「=LEFT（B3,C3-1)」と入力します。第一引数には検索する文字列を、第二引数には抽出したい文字数を指定します。

例では「C3-1」として「（」前までの文字数を指定しています。

STEP.4

オートフィル機能を使用して、残りのセルも入力しましょう。これでFIND関数と他の関数を組み合わせてカッコ内の前の文字のみ抽出できました。

エクセルで文字列がある列／行を検索するMACTH関数

MACTH関数は、大量の表形式データがある場合に任意のデータが表の中でどの位置にあるのかを列・行番号で返す関数で、構文は「=MATCH(検索値, 検索範囲, 照合の種類)」です。

以下具体的な使い方の手順を紹介します。例では、商品データベースから「写真用紙」がある行と列番号を調べます。画像のようなデータを用意しましょう。

STEP.1

列番号を表示する欄に「=MATCH(A14,A4:C4,0 )」と入力しましょう。第一引数には検索したい文字列があるセル参照を、第二引数には検索範囲として「写真用紙」を含む行を、第三引数には「0」を入力します。第三引数の照合の種類は、完全一致する時のみ値を返す「０」としています。

STEP.2

行番号を表示する欄に「=MATCH(A14,B2:B12,0 )」と入力しましょう。第一引数には検索したい文字列があるセル参照を、第二引数には検索範囲として「写真用紙」を含む列を、第三引数には「0」を入力します。第三引数の照合の種類は、完全一致する時のみ値を返す「０」としています。

STEP.3

これで商品データベース内で「写真用紙」がある行と列番号が表示されました。こちらの関数も他の関数と組み合わせて使用すると便利なシステムを構築することが可能です。

エクセルで空白のセルをカウントするCOUNTBLANK関数

空白のセルの数をカウントする時に使用するのがCOUNTBLANK関数で、構文は「=COUNTBLANK（範囲）」と比較的シンプルです。以下具体的な使い方の手順を紹介します。例では、アンケートの自由回答が無回答の人数を数えますので画像のようなデータを用意しましょう。

STEP.1

空欄の数を表示したいセルに「=COUNTBLANK（E3;E11）」と入力しましょう。引数は1つで、空欄の数を数えたいセル範囲を指定します。

例では自由回答が空欄の人数を数えたいので「E3;E11」と入力しています。

STEP.2

これでアンケートの自由回答欄を無回答とした人数が表示されます。

平均値や分散など様々な統計量を求める際にもCOUNTBLANK関数を活用できます。例えば値が０ではなくデータが存在しない場合、単純にデータ数で割ると正確な値が求められませんが、COUNTBLANK関数を用いてデータなし（空欄）のセル数を排除すれば正確な値を求めることができます。

エクセルで複数条件を設定して検索するXLOOKUP関数

XLOOKUP関数は縦方向にデータを検索するVLOOKUP関数と、横方向にデータを検索するHLOOKUP関数の後継に位置づけられる関数で、縦横のデータ検索が1つで行うことができる関数です。複数条件の検索も簡単に設定することができるのが特徴です。

構文は「＝XLOOKUP（検索値,検索範囲,戻り範囲,見つからない場合,一致モード,検索モード）」となります。

以下具体的な使い方の手順を紹介します。例では、商品名を入力すると商品データベースから商品IDを抽出して表示させるようにしますので画像のようなデータを用意しましょう。

STEP.1

商品IDを表示させたいセルに「=XLOOKUP(B14,B2:B12,A2:A12)」と入力しましょう。第一引数は検索したい値を、第二引数には検索範囲として商品IDのあるB2:B12を、第三引数には戻り範囲として抽出したい商品名のあるA2:A12を指定しています。

第四～第六引数では、検索値が見つからない場合や一致モードの細かな設定が可能ですが、省略しても問題ありません。

STEP.2

商品名の欄に、商品IDを検索したい商品名を入力しましょう。正しい商品IDが表示されていればOKです。

STEP.3

複数条件を指定したい場合は検索値と検索範囲にアンド「＆」を用いて指定します。

例えば商品名(ファイル）と価格（400円）の2つの条件を同時に満たしたものの商品IDを検索するには「=XLOOKUP(B13&B14,B2:B11&C2:C11,A2:A11)」と入力すれば400円のファイルの商品ID「C-9102」が検索されます。

エクセルで特定の条件に一致したデータを検索・合計するSUMIF関数

特定の条件に一致したデータを検索したり、同じ行にある値を合計したりするにはSUMIF関数を使用します。構文は「=SUMIF(範囲,検索条件,合計範囲)」です。

以下具体的な使い方の手順を紹介します。例では気温とその日のかき氷の販売数のデータを使って、ある気温以上の日に売れたかき氷の数のみを合計した値を求めます。画像のようなデータを用意しましょう。

STEP.1

合計を表示させたいセルに「=SUMIF(B3:B11,”>=”&B14,C3:C11)と入力しましょう。第一引数には検索の対象とするセル範囲である「B3:B11」を、第二引数にはある温度以上の時という条件を指定するために「”>=”&B14」を、第三引数には条件を満たす時のみ合計する販売数の範囲として「C3:C11」を入力しています。