Excel で複数の値を検索する方法: ステップバイステップ

Microsoft Excel で大規模なデータセットを操作する場合、複数の条件に一致する複数の値を見つけるのが困難な場合があります。VLOOKUP などの一部の組み込み関数は、もともと単一の値を処理するように設計されていました。

複数のExcel 関数を組み合わせて、複数の値を検索できます。これらには、VLOOKUP、INDEX、MATCH、IF 関数が含まれます。Excel の現在のバージョンでは動的配列を使用できますが、古いバージョンでは配列数式が使用されます。

この記事では、データ内の複数の値を検索する数式でこれらの関数を使用する方法を正確に説明します。

さあ行こう！

目次

• 複数の値で VLOOKUP を使用する方法
  • 配列数式とは何ですか?
  • VLOOKUP の例
  • Excel の最新バージョン
  • 古いバージョン
  • 完全一致と近似一致
  • VLOOKUP の詳細
• INDEX と MATCH を使用して複数の値を検索する方法
  • Excel 365 で INDEX と MATCH を併用する方法
  • 古いバージョンの Excel での作業
• IF 関数を使用して複��の値を検索する方法
  • Excel の現在のバージョン
  • 古いバージョンの Excel
• 複数の基準に SUMPRODUCT を使用する方法
• ルックアップ関数に関する 3 つの一般的なエラー
  • 1. #N/A エラー
  • 2.#REF! エラー
  • 3. 循環エラー
• ルックアップ関数で IFERROR を使用する方法
• 7 高度な検索テクニック
  • 1. 相対位置ルックアップ
  • 2. SMALL機能
  • 3.ROW関数
  • 4.フィルター機能
  • 5. ヘルパー列
  • 6. 動的配列
  • 7. パワークエリ
• 最終的な考え

複数の値で VLOOKUP を使用する方法

VLOOKUP関数は、データ範囲内の単一の値を検索するためによく使用されます。ただし、この検索式を使用して Excel で複数の一致を検索することもできます。

デフォルトでは、最初に見つかった一致する値のみを返します。ただし、配列数式を使用して複数の値を返すように関数を変更できます。

配列数式とは何ですか?

配列数式は、データの配列に対して計算を実行できる数式です。単一の値ではなく結果の配列を返すことができるため、配列数式と呼ばれます。

配列数式を作成するには、いくつかの簡単な手順があります。

1. 検索するセルの範囲を選択します。

2. 数式バーに数式を入力します。

3. Ctrl + Shift + Enter を押して完了します。

配列数式の構文は通常の数式の構文と似ていますが、数式の前後に中括弧 {} が含まれます。中括弧は、数式が配列数式であり、値の配列を返すことを示します。

この記事の例では、配列数式を正しく使用する方法を示します。

VLOOKUP の例

この例では、ワークシートの列 A に 5 つの項目があります。

• りんご

• バナナ

• パン

• にんじん

• チェリー

このタスクは、リンゴ、バナナ、チェリーという 3 つの特定の果物がこのリストに含まれているかどうかを確認することです。

Excel の最新バージョン

VLOOKUP 構文は、使用している Excel のバージョンによって異なります。

最新バージョン ( Excel 365またはExcel 2021 ) は動的配列をサポートしています。この機能を使用すると、数式で隣接するセルに「こぼれる」複数の結果を返すことができます。

これは構文です (等号で数式が始まります)。

=VLOOKUP(ルックアップ値, テーブル配列, 列インデックス番号, [ルックアップ範囲])

• lookup_value: 検索する値。

• table_array: 検索するデータテーブル全体。

• Col_index_num: 返したいデータを含む table_array 内のテーブルの列番号。

• range_lookup: オプション。完全一致するか、近似一致するかを指定します。

私たちの具体的な例では、次の式を使用します。

=VLOOKUP({“りんご”,“バナナ”,“さくらんぼ”},A1:A5,1,FALSE)

セル B1 に数式を入力すると、結果がセル C1 と D1 に広がります。この図は、実際の例を示しています。

古いバージョン

動的配列をサポートしていない古いバージョンの Excel (Excel 2019 以前など) を使用している場合は、配列数式で少し異なるアプローチを使用する必要があります。

次の手順を実行します：

1. 最初の項目の結果を表示するセルをクリックします (列 B など)。

2. Enter キーをまだ押さずに次の数式を入力します: =VLOOKUP({“apple”,“banana”,”cherry”}, A1:A5, 1, FALSE)

3. Ctrl + Shift + Enter を押して、これを配列数式に変換します。

4. セル B1 をコピーし、下のセルに貼り付けます (または塗りつぶしハンドルを使用します)。

Ctrl + Shift + Enter を使用すると、Excel によって数式の周囲に中括弧が追加されます。これは、それが配列数式であることを示します。

完全一致と近似一致

デフォルトでは、VLOOKUP 関数は近似一致を使用します。これは、セル値が完全に一致しない場合でも、検出できる最も近い一致を返すことを意味します。

完全一致を実行したい場合は、range_lookup 引数を FALSE に設定します。

近似一致は、順序付けされた数値を使用する場合に最適に機能することに留意してください。通常、セル値がテキストの場合は適切ではありません。

VLOOKUP の詳細

この多用途な機能について詳しく知りたい場合は、次の記事を参照してください。

• VLOOKUPを使用してExcelの2つの列を比較する方法

• VLOOKUPで不一致を見つける方法

VLOOKUP 関数の設定が完了したので、別の方法で同じことを実行できる他の 2 つの関数、INDEX と MATCH を見てみましょう。

INDEX と MATCH を使用して複数の値を検索する方法

INDEX 関数と MATCH 関数を組み合わせて、複数の行で複数の値を検索できます。

Excel の INDEX 関数は、指定された範囲内のセルへの値または参照を返します。

=INDEX(配列, 行番号, [列番号])

• 配列: 値を検索するセルの範囲。

• row_num: 値を返す配列内の行番号。

• column_num: (オプション) 値を返す配列内の列番号。省略した場合、関数は行全体を返します。

Excel の MATCH 関数は、指定された範囲内の値の位置を返します。

=MATCH(lookup_value, lookup_array, [match_type])

• lookup_value: lookup_array 内で検索される値。

• lookup_array: lookup_value を検索するセルの範囲。

• match_type: (オプション) 実行される一致のタイプ。省略した場合、関数は完全一致を実行します。

Excel 365 で INDEX と MATCH を併用する方法

INDEX と MATCH を併用して Excel で複数の値を検索するには、配列数式を使用する必要があります。

以前のサンプル データを使用すると、Excel 365 の数式は次のようになります。

=INDEX(A1:A5, MATCH({“apple”,”banana”,”cherry”}, A1:A5, 0))

The above example breaks down as this:

• INDEX: this returns the value of a cell in a specified range based on a given row and column number. In this case, it will return the value from the range A1:A5.

• A1:A5: This is the defined table range where you’re searching for the value and from which the result will be returned.

• MATCH: this searches for a specified item in a range of cells and returns the relative position of that item in the range.

• {“apple”,”banana”,”cherry”}: this is the array constant containing the values you want to look up.

• A1:A5: this is the range where MATCH will search for the values from the array constant.

• 0: this is the match type for MATCH function. In this case, it’s 0, which means you’re looking for an exact match instead of a close match.

 This picture shows the formula in action:

Working with Older Versions of Excel

If you’re using an older Excel file that doesn’t support dynamic arrays (e.g., Excel 2019 or earlier), you’ll need to use a different approach.

Because older versions don’t support the formulas “spilling” into adjacent cells, you will need to break out the usage into three separate formulas.

Follow these steps:

1. Click on the cell where you want the result for the first item (e.g., cell B1)

2. Type the below formula:

3. =INDEX(A1:A5, MATCH(“apple”, A1:A5, 0))

4. Press Enter to execute the formula.

5. Type this formula in cell B2: =INDEX(A1:A5, MATCH(“banana”, A1:A5, 0))

6. Type this formula in cell B3: =INDEX(A1:A5, MATCH(“cherry”, A1:A5, 0))

This picture shows cell reference B3:

INDEX and MATCH functions aren’t the only ones that can enable you to find multiple values. In the next section, we look at how you can use the IF function as an alternative.

How to Use the IF Function to Find Multiple Values

Another way to lookup multiple cell values based on certain criteria is to use the IF function with other functions.

The IF function allows you to test multiple conditions and return different results depending on the outcome of those tests.

For example, let’s say you have a table of sales data with columns for Product and Sales. You want to lookup and total the sales amount for two of the three products.

Current Versions of Excel

To find the sum of the Sales column where the product is either “Apple” or “Banana” using the IF function, you can use an array formula with IF, SUM, and OR functions.

Assuming your data starts in cell A1, use the following formula:

=SUM(IF((A2:A4=”Apple”)+(A2:A4=”Banana”), B2:B4, 0))

The section (A2:A4=”Apple”)+(A2:A4=”Banana”) creates an array that has a value of 1 if the cell in range A2:A4 contains “Apple” or “Banana”, and 0 otherwise.

The IF statement checks each element of the array argument. If the value is 1 (i.e., the product is either “Apple” or “Banana”), it takes the corresponding value in the Sales column (range B2:B4); otherwise, it takes 0.

The SUM function adds up the values from the IF function, effectively summing the sales values for both “Apple” and “Banana”.

This picture shows the formula in action on the search range:

Older Versions of Excel

In Excel 2019 or earlier, you need to use an array formula. Follow these steps:

1. Type the formula but do not hit Enter.

2. Press Ctrl + Shift + Enter to make it an array formula.

Excel will add curly brackets {} around the formula, indicating it’s an array formula.

Next, we look at how you could use SUMPRODUCT to lookup several values based on your criteria. Let’s go!

How to Use SUMPRODUCT for Multiple Criteria

The SUMPRODUCT function also allows you to lookup multiple values based on multiple criteria.

Because it does not require the use of an array formula, the syntax is the same regardless of the version of Excel.

Using the same data as in the previous example, the formula looks like this:

=SUMPRODUCT((A2:A4=”Apple”)+(A2:A4=”Banana”), B2:B4)

The section (A2:A4=”Apple”)+(A2:A4=”Banana”) creates an array that has a value of 1 if the cell in range A2:A4 contains “Apple” or “Banana”, and 0 otherwise.

SUMPRODUCT 関数は、配列の要素と Sales 列の対応する要素 (範囲 B2:B4) を乗算します。次に、結果の値を合計し、「Apple」と「Banana」の両方の売上高を実質的に合計します。

以下の式は、実際の動作を示しています。

Excel 関数は、期待どおりに動作する場合には優れていますが、場合によってはエラーが発生することがあります。次のセクションでは、一般的なエラーのいくつかとその対処方法について説明します。

ルックアップ関数に関する 3 つの一般的なエラー

ルックアップ関数は、イライラさせられ、トラブルシューティングに時間がかかるエラーを返す場合があります。発生する最も一般的なエラーは次の 3 つです。

1. #N/A エラー

2. #REF! エラー

3. 循環エラー

1. #N/A エラー

#N/A エラーは、ルックアップ配列内にルックアップ値が見つからない場合に発生します。

このエラーが発生する理由としては、次のようなものが考えられます。

• ルックアップ値のスペルが間違っているか、正しくありません。

• ルックアップ配列は昇順に並べ替えられません。

• ルックアップ値がデータセットにありません。

ルックアップ値がデータ セットにない場合、これは有用な情報です。ただし、経験の浅い Excel ユーザーは、#N/A は数式に問題があることを意味すると考えるかもしれません。次のセクションでは、これをさらに使いやすくする方法を示します。

2.#REF! エラー

#REF! ルックアップ配列またはリターン配列が削除または移動されると、エラーが発生します。

このエラーは、ルックアップ関数のセル参照を更新することで修正できます。

3. 循環エラー

関数を組み合わせて複雑な数式を作成すると、Excel から循環参照があることが通知される場合があります。

Excel で循環参照を見つけるためのガイドを使用すると、これらを簡単に調査できることがわかります。

ルックアップ関数で IFERROR を使用する方法

IFERROR 関数は、検索関数のエラーを処理するための便利なツールです。ルックアップ関数がエラーを返した場合に返す値または式を指定できます。

IFERROR 関数の構文は次のとおりです。

=IFERROR(値, エラーの場合の値)

· value: 評価する値または式。

· Value_if_error: 最初の引数がエラーを返した場合に返される値または式。

たとえば、テーブル内の複数の値を検索する VLOOKUP 関数があるとします。下の図では、値の 1 つが検索されたデータ範囲に存在しません。

ご覧のとおり、#N/A エラーが表示されるため、経験の浅い Excel ユーザーは混乱する可能性があります。

代わりに、IFERROR を使用して、次の構文で空白のセルまたは「見つかりません」というメッセージを表示できます。

=IFERROR(VLOOKUP(ルックアップ値, テーブル配列, 列インデックス, FALSE), “見つかりません”)

この例では、VLOOKUP 関数がエラーを返した場合、IFERROR 関数は代わりに「見つかりません」というメッセージを返します。

この図は、実際の式を示しています。列 B には欠損値がありますが、列 C と列 D には一致が見つかりました。

これまでに多くの内容を説明してきました。いよいよ、ルックアップに関するより高度なテクニックを学ぶ準備が整いました。これについては、次のセクションのトピックです。

7 高度な検索テクニック

Excel で複数の値を検索することは、特に大規模なデータセットを扱う場合には困難な作業となることがあります。処理が遅いとパフォーマンスの問題が発生する可能性があります。

プロセスをより簡単かつ効率的に行うために使用できる 7 つの高度な検索テクニックがあります。

• 相対位置検索

• 小さな関数

• ROW関数

• FILTER関数

• ヘルパー列

• 動的配列

• パワークエリ

1. 相対位置ルックアップ

Excel で複数の値を検索する最も簡単な方法の 1 つは、相対位置検索を使用することです。これには、目的の値を見つけるために、特定のセルからの行と列のオフセットを指定することが含まれます。

たとえば、データのテーブルがあり、特定のセルの 2 行下、3 列右の値を検索する場合は、次の数式を使用できます。

=OFFSET(セル, 2, 3)

2. SMALL機能

複数値の検索に役立つもう 1 つのテクニックは、SMALL 関数を使用することです。この関数は、セル範囲内の n 番目に小さい値を返します。

この関数を INDEX や MATCH などの他の検索関数と組み合わせることで、特定の基準に基づいて複数の値を検索できます。

たとえば、次の数式は、特定の条件を満たすセル範囲内で 2 番目に小さい値を検索します。

=INDEX(データ, MATCH(SMALL(IF(基準, 範囲), 2), 範囲, 0))

3.ROW関数

ROW 関数は、Excel での複数の値の検索にも使用できます。この関数は、指定されたセルの行番号を返します。これは、データ表内のセルを参照するために使用できます。

たとえば、次の式は、一意の識別子に基づいてテーブル内の値を検索します。

=INDEX(データ, MATCH(固有識別子, データ[固有識別子], 0), 列インデックス番号)

4.フィルター機能

FILTER 関数は、古いバージョンの Excel では使用できません。

Excel 365 では、これを使用して特定の基準に基づいてセル範囲をフィルターし、それらの基準を満たす値のみを返すことができます。これは構文と 3 つの引数です。

=FILTER(配列、インクルード、[if_empty])

• 配列: フィルタリングする特定のデータ。

• include: include 引数は、配列に適用する基準または条件です。

• [if_empty] (オプション): include 引数で指定された基準を満たす行または列がない場合に返される値。

たとえば、次の式はサンプル データに対して機能し、最初の列の 3 つの項目のうち 2 つの一致を検索し、2 番目の列の対応する値を合計します。

=SUM(FILTER(B2:B4, (A2:A4=”アップル”)+(A2:A4=”バナナ”)))

この図は、一致する行の数と、さまざまな値の合計を示しています。

5. ヘルパー列

ヘルパー列を使用すると、VLOOKUP などの関数内で複数のフィールドを結合できます。

別のシートで姓と名を操作しているとします。最終的な式内で参照されるヘルパー列でそれらを連結できます。

6. 動的配列

前の例で学んだように、Microsoft 365 ユーザーは Excel での複数の値の検索に動的配列を利用できます。

動的配列を使用すると、単一の数式から複数の値を返すことができるため、大量のデータの検索が容易になります。

7. パワークエリ

Power Query は、複数の条件に基づいて値を返すために使用できる Excel の強力なツールです。

たとえば、このビデオではスプレッドシート内の乱雑なデータを見つけてクリーンアップします。

最終的な考え

これで、Excel で複数の値を検索する技術についての詳細な説明が終わりました。VLOOKUP、INDEX、MATCH、配列数式をマスターすると、熱いナイフでバターを切るかのように、複雑なデータ セットを素早く処理できるようになります。

重要なのは、各式の背後にある構文とロジックを理解することです。これらの数式は複雑になる可能性があるため、重要なデータ分析に数式を依存する前に、時間をかけて徹底的にテストすることが重要であることに留意してください。

Excel は強力なツールですが、その良さはユーザーによって決まります。したがって、これらのスキルを磨き続け、実験を続ければ、すぐに複数値検索の達人になるでしょう。次回まで、これらの数値を計算し続け、Excel に難しい作業を任せてください。