Excel で複数の基準に基づいてテーブルからデータを抽出する方法

必要なときにデータを抽出することは、スプレッドシートを使用する主な要因であり、Excel も例外ではありません。データを Excel に表形式で保存して、それらを抽出します。今日は、複数の基準に基づいてテーブルからデータを抽出する方法を紹介します。

まず最初に、例のベースとなるデータセットについて知りましょう。

ここには、映画のリストと、ジャンルと主演俳優、およびリリース年を含むテーブルがあります。このデータセットを使用して、複数の基準に基づいてデータを取得します。

これは、物事を単純にするための基本的なテーブルであることに注意してください。実際のシナリオでは、はるかに大規模で複雑なデータセットに遭遇する可能性があります。

練習用ワークブック

次のリンクから練習用ワークブックをダウンロードしてください。

複数の基準に基づいてテーブルからデータを抽出

ここでは、たとえば、ジャンルと俳優名を基準として提供し、これらの基準に基づいて映画名を抽出します。

1.単一の値を返す

このセクションでは、単一の値を返します。基準に基づいて、1 つの値のみがフェッチされます。調べてみましょう。

I. INDEX-MATCH 配列数式

INDEX の組み合わせを使用できます そしてマッチ 機能。 インデックス 範囲内の特定の位置の値を返します。 マッチ 範囲内のルックアップ値の位置を特定します。

これらの機能については、INDEX、MATCH の記事をご覧ください。

基準値を設定しましょう。当分の間、ジャンルとしてスリラー 俳優のヒュー・ジャックマン フィールド。

式は次のようになります

=INDEX($B$4:$B$19,MATCH(1,($H$4=$C$4:$C$19)*($H$5=$D$4:$D$19),0))

B4:B19 値を抽出する配列です。そしてマッチ 関数は、フェッチされる行番号を設定します。

lookup_value として 1 が指定されていることがわかります MATCH 内 .そして lookup_array 条件一致ロジックの乗算によって生成されます。

$H$4=$C$4:$C$19まで ジャンルと $H$5=$D$4:$D$19 を確認しました 俳優の名前です。

乗算結果の配列の中から 1 を見つけます。行番号と INDEX を返します 関数は映画名を返します。

これは配列数式なので、CTRL + SHIFT + ENTER を使用する必要があります

基準値を変更すると、更新された値が表示されます。

続きを読む: Excel のセルから特定のデータを抽出する方法 (3 つの例)

II. INDEX-MATCH 非配列数式

INDEX を組み合わせて非配列数式を作成できます そしてマッチ .

まず式を見てみましょう

=INDEX($B$4:$B$19,MATCH(1,INDEX(($H$4=$C$4:$C$19)*($H$5=$D$4:$D$19),0,1),0))

ご覧のとおり、いくつかの INDEX を使用しています。 そこで機能します。外側の INDEX 関数は、インサイダーが行番号を検出するのに役立つ抽出ジョブを実行します。

内側の INDEX 内で、 基準値を確認しました。 INDEX 内で乗算された 2 つの論理演算 配列参照として機能しました。

入力するだけ 式を実行します。

基準値を自由に変更してください。更新された値が表示されます。

続きを読む: 基準に基づいて Excel からデータを抽出する方法 (5 つの方法)

III. INDEX-MATCH-IF の組み合わせ

前のセクションでは、条件を確認し、それらを乗算して強制的に一緒に動作させました。 IF を使用して乗算を終了できます 関数。

IF 論理テストを実行し、ブール値 (TRUE) を返します または FALSE ) 結果として。関数について知るには、この IF 記事にアクセスしてください。

式は次のようになります

=INDEX($B$4:$B$16,MATCH(1,IF($C$4:$C$16=$H$4,IF($D$4:$D$16=$H$5,1)),0))

一致する条件が 2 つあるため、IF が 2 つ .ネストされた IF として機能しています （別の中に1つ）。外側の IF 関数は、最初に (シーケンスに関係なく) 条件をチェックし、2 番目の条件 (内部 IF) をチェックします。 if_true_value です 最初の IF .

CTRL+SHIFT+ENTER を使用する必要があります 数式を実行します。

基準値を変更すると、更新された値が表示されます。

続きを読む: Excel シートからデータを抽出する方法 (6 つの効果的な方法)

IV. LOOKUP関数

LOOKUP を使用できます 基準に基づいてデータを抽出するタスクを実行する関数。

ルックアップ 関数は、範囲内で一致するルックアップを実行し、対応する値を返します。詳細については、Microsoft サポートをご覧ください。

式を見てみましょう

=LOOKUP(2,1/($C$4:$C$19=$H$4)/($D$4:$D$19=$H$5),($B$4:$B$19))

ここでは、lookup_value として 2 を設定しています。 .そして、1 をそれらで割る形式の 2 つの論理演算は、lookup_vector です。 .

ここでは、1 を TRUE/FALSE の配列で割ります 値 ($C$4:$C$19=$H$4 )、次に TRUE/FALSE の別の配列によって 値 ($D$4:$D$19=$H$5 ）。これは 1 または #DIV/0! を返します。 エラーです。

lookup_value 範囲内の数値と一致するように数式に指示します。一致が見つかると、値は配列 B4:B19 から取得されます .

CTRL + SHIFT + ENTER を押す必要はありません 実行します。

基準値を変更して、式が他の値に対して完全に機能しているかどうかを確認してください。

lookup_value として 2 を使用していることに注意してください .これは 1 から始まる任意の数です。

続きを読む: 画像から Excel にデータを抽出する方法 (簡単な手順で)

類似の読み物

• テキスト ファイルを Excel に変換する VBA コード (7 つのメソッド)
• Excel VBA:Web サイトから自動的にデータを取得する (2 つの方法)
• 安全な Web サイトから Excel にデータをインポートする方法 (クイック手順付き)
• VLOOKUP を使用して、ある Excel ワークシートから別の Excel ワークシートにデータを自動的に転送する
• パイプ区切り文字を使用して Excel をテキスト ファイルに変換する方法 (2 つの方法)

2.複数の値を返す

I. INDEX-SMALL の組み合わせ

基準に基づいて複数のデータを抽出するために、さまざまな関数の組み合わせを使用できます。組み合わせの 1 つは INDEX です – 小

小さい 関数は、値でランク付けされたリスト内の位置に基づいて値を返します。詳細については、この SMALL を確認してください

これら 2 つに加えて、いくつかのヘルパー関数 IF が必要になります。 、行 と IFERROR .詳細については、IF、ROW、IFERROR の記事を確認してください。

式は次のようになります

=IFERROR(INDEX($B$2:$B$17,SMALL(IF(($C$2:$C$17=$H$2)*($D$2:$D$17=$H$3), ROW($B$2:$B$17)),ROW(1:1))-1,1),"")

ここでは、すべての機能に目的があります。 インデックス 関数は配列 B2:B17 から値を返します 大きな小さな 部分は、フェッチされる行番号を提供します。

もし、 SMALL 内で、 基準が一致するかどうかをチェックします。一致する条件が 2 つあるため、条件を確認するために両方の論理演算を乗算しました。そして ROW 関数は列のセルを反復します。

次に、外側の ROW SMALL の k 番目の値を示します 関数。これらの関数を組み合わせると、行番号と INDEX が返されます 結果を返します。

IFERROR 数式から発生する可能性のあるエラーに対処します。エラーが発生した場合に空のセルが提供されるように設定しました。

下にドラッグすると、条件に一致するすべての値が取得されます。

続きを読む: Excel 数式を使用してリストからデータを抽出する方法 (5 つの方法)

II. INDEX-AGGREGATE の組み合わせ

集計 関数を使用すると、さまざまなタスクを実行できます。複数の操作に 1 つの機能。この関数を使用して、複数の基準に基づいて複数の値を返すことができます。

関数 AGGREGATE について少し知りましょう。 関数は、AVERAGE、COUNT、MAX などの集計計算を返します。

AGGREGATE の構文 関数は次のとおりです:

AGGREGATE(function_number,behavior_options, range)

機能番号: この数値は、どの計算を行うべきかを指定します。

動作オプション: これを数値で設定します。この数値は、関数がどのように動作するかを示します。

範囲: 集計したい範囲。

集計 function はいくつかのタスクを実行するため、多数の関数が定義済みです。使用頻度の高い機能番号をいくつか記載しています

関数	Function_number
平均	1
COUNT	2
COUNTA	3
MAX	4
MIN	5
PRODUCT	6
SUM	9
LARGE	14
小	15

この機能の詳細については、Microsoft サポートをご覧ください。

式を見てみましょう。これは INDEX の組み合わせになります と集約 .

=IFERROR(INDEX($B$2:$B$17,AGGREGATE(15,6,IF(($C$2:$C$17=$H$2)*($D$2:$D$17=$H$3), ROW($B$2:$B$17)),ROW(1:1))-1,1),"")

ここでは 15 を使用しました function_number として 集約で .上の表から、15 を確認できます。 SMALL の呼び出し 機能操作。 AGGREGATE の使用とは別に見ることができます (および関数番号と動作オプション番号) 式は以前の INDEX とまったく同じです –小さい

仕組みは同じ、INDEX AGGREGATE で見つかった一致に基づいて値を返す配列を保持します 式の一部。

6 エラー値を無視することを示す動作オプション .

下にドラッグすると、条件に一致するすべての値が取得されます。

CTRL+SHIFT+ENTER を忘れずに使用してください 数式を実行します。

続きを読む: 単一の基準に基づいて Excel で複数の値を返す (3 つのオプション)

III. INDEX-MATCH-COUNTIF の組み合わせ

複数の基準に基づいて複数の値を返すには、INDEX の組み合わせを使用できます 、マッチ 、および COUNTIF .

COUNTIF 1 つの条件を満たす範囲内のセルをカウントします。この関数の詳細については、次の記事をご覧ください:COUNTIF .

Our formula will be the following one

=IFERROR(INDEX($B$4:$B$19,MATCH(0,COUNTIF(H5:$H$5,$B$4:$B$19)+IF($C$4:$C$19<>$H$4,1,0)+IF($D$4:$D$19<>$H$5,1,0),0)),"")

Within the MATCH function, we provided 0 as the lookup_array, and for lookup_range we have used the IF portion containing COUNTIF .

Here, the COUNTIF function excludes any value that has already been fetched. And two IF functions check two conditions. We have added these functions so that they together form the lookup_range .

The MATCH portion returns the value as long as 0 is found. The value here works as the row number for INDEX .

Drag it down, you will get all the values that match the criteria.

Read More: How to Extract Data from Cell in Excel (5 Methods)

IV. FILTER Function

If you are using Excel 365, then you can perform the task with a single built-in function called FILTER .

The FILTER function filters a range of data based on given criteria and extracts matching records. To know about the function, visit this article:FILTER.

Let’s explore the formula

=FILTER(B4:B19,(H4=C4:C19)*(H5=D4:D19))

B4:B19 is the array that is to be filtered. Then we have provided the condition, based on what we will extract values. Since we need to check two criteria, we have multiplied them.

Here you will not need to drag down the formula, at one go this will provide all the values and fulfill the list.

Read More: Extract Filtered Data in Excel to Another Sheet (4 Methods)

結論

That’s all for today. We have listed several methods to extract data from table based on multiple criteria. Hope you will find this helpful. Feel free to comment if anything seems difficult to comprehend. Let us know any other approaches that we have missed here.

Further Readings

• How to Extract Data from Excel to Word (4 Ways)
• Extract Data from One Sheet to Another Using VBA in Excel (3 Methods)
• How to Pull Data from Multiple Worksheets in Excel VBA
• Transfer Data from One Excel Worksheet to Another Automatically
• How to Pull Data From Another Sheet Based on Criteria in Excel
• Excel Macro:Extract Data from Multiple Excel Files (4 Methods)
• How to Extract Year from Date in Excel (3 Ways)