ExcelでINDEXとMATCHを一緒に使用する方法
Excelテーブルを操作するとき、値を検索する必要がある状況が常に見つかることがあります。 このチュートリアルでは、INDEX関数とMATCH関数の組み合わせを適用して、水平ルックアップと垂直ルックアップ、双方向ルックアップ、大文字と小文字を区別するルックアップ、および複数の条件を満たすルックアップを作成する方法を示します。
- INDEXとMATCHを組み合わせる例
- 左ルックアップを適用するためのINDEXとMATCH
- 双方向ルックアップを適用するためのINDEXとMATCH
- 大文字と小文字を区別するルックアップを適用するためのINDEXとMATCH
- 複数の基準でルックアップを適用するためのINDEXとMATCH
- 複数の列にルックアップを適用するためのINDEXとMATCH
INDEX関数とMATCH関数はExcelで何をしますか
INDEX関数とMATCH関数を使用する前に、INDEX関数とMATCHが最初に値を検索するのにどのように役立つかを確認しましょう。
ExcelでのINDEX関数の使用
また INDEX Excelの関数は、特定の範囲内の特定の場所の値を返します。 INDEX関数の構文は次のとおりです。
- 配列 （必須）は、値を返す範囲を指します。
- 行番号 （column_numが存在しない場合は必須）は、配列の行番号を参照します。
- column_num （オプションですが、row_numが省略されている場合は必須です）は、配列の列番号を参照します。
たとえば、知るために ジェフの最終試験のスコア、リストの6番目の学生の場合、次のようにINDEX関数を使用できます。
= INDEX（E2：E11、6） >>>戻ります 60
√注：範囲 E2：E11 番号がリストされている間、最終試験がリストされている場所です 6 の試験スコアを見つけます 6学生。
ここで少しテストしてみましょう。 式について = INDEX（B2：E2,3）、どのような値を返しますか？ ---はい、戻ります 中国 3 指定された範囲の値。
ここで、INDEX関数が水平範囲または垂直範囲で完全に機能することを知っておく必要があります。 しかし、複数の行と列を持つより広い範囲の値を返す必要がある場合はどうなりますか？ この場合、行番号と列番号の両方を適用する必要があります。 たとえば、調べるために エミリーの出身国 INDEXを使用すると、次のように、B8からE3までのセルで行番号が2、列番号が11の値を見つけることができます。
= INDEX（B2：E11,8,3） >>>戻ります 中国
上記の例によると、 ExcelのINDEX関数については、次のことを知っておく必要があります。
- INDEX関数は、垂直範囲と水平範囲で機能します。
- INDEX関数では大文字と小文字は区別されません。
- 行番号は、INDEX式の列番号（両方の番号が必要な場合）よりも先になります。
ただし、複数の行と列を持つ非常に大きなデータベースの場合、正確な行番号と列番号を使用して数式を適用することは確かに不便です。 そしてこれは、MATCH関数の使用を組み合わせる必要がある場合です。
それでは、まずMATCH関数の基本について学びましょう。
ExcelでのMATCH関数の使用
ExcelのMATCH関数は、指定された範囲内の特定のアイテムの場所である数値を返します。 MATCH関数の構文は次のとおりです。
- ルックアップ配列 （必須）は、MATCHで検索するセルの範囲を指します。
- マッチタイプ （オプション）、 1, 0 or -1:
- 1（デフォルト）、MATCHは以下の最大値を検索します 参照値。 の値 ルックアップ配列 昇順で配置する必要があります。
- 0、MATCHは、 参照値。 の値 ルックアップ配列 任意の順序にすることができます。 （一致タイプが0に設定されている場合は、ワイルドカード文字を使用できます。）
- -1、MATCHは、以上の最小値を見つけます。 参照値。 の値 ルックアップ配列 降順で配置する必要があります。
たとえば、知るために 名前のリストでのVeraの位置、次のようなMATCH式を使用できます。
= MATCH（ "vera"、C2：C11,0） >>>戻ります 4
√注：MATCH関数では大文字と小文字は区別されません。 結果「4」は、名前「Vera」がリストの4番目の位置にあることを示します。 数式の「0」は、ルックアップ値「Vera」と正確に等しいルックアップ配列の最初の値を見つける一致タイプです。
知るために B96からE2までの行のスコア「2」の位置、次のようにMATCHを使用できます。
= MATCH（96、B2：E2,0） >>>戻ります 4
☞ExcelのMATCH関数について知っておくべきこと：
- MATCH関数は、値自体ではなく、ルックアップ配列内のルックアップ値の位置を返します。
- MATCH関数は、重複する場合に最初の一致を返します。
- INDEX関数と同様に、MATCH関数は垂直範囲と水平範囲でも機能します。
- MATCHでも大文字と小文字は区別されません。
- MATCH式のルックアップ値がテキスト形式の場合は、引用符で囲みます。
ExcelでのINDEX関数とMATCH関数の基本的な使用法について理解したところで、袖をまくり上げてXNUMXつの関数を組み合わせる準備をしましょう。
INDEX関数とMATCH関数を一緒に使用する方法
このパートでは、さまざまなニーズを満たすためにINDEX関数とMATCH関数を使用するさまざまな状況について説明します。
INDEXとMATCHを組み合わせる例
以下の例を参照して、INDEX関数とMATCH関数を組み合わせる方法を理解してください。
たとえば、知るために Evelynの最終試験のスコア、次の式を使用する必要があります。
=INDEX(A2:D11,MATCH("evelyn",B2:B11,0),MATCH("final exam",A1:D1,0)) >>>戻ります 90
さて、式は複雑に見えるかもしれないので、それの各部分を見ていきましょう。
あなたが上で見ることができるように、大きな INDEX 式にはXNUMXつの引数が含まれています。
- 配列: A2：D11 セルから一致する値を返すようにINDEXに指示します A2からD11.
- 行番号: MATCH（ "evelyn"、B2：B11,0） INDEXに値の正確な行を通知します。
- MATCH数式については、次のように説明できます。B2からB11までのセルの「evelyn」と正確に等しい最初の値の位置を数値で返す。 5.
- column_num: MATCH（ "最終試験"、A1：D1,0） INDEXに値の正確な列を通知します。
- MATCH数式については、次のように説明できます。A1からD1までのセルの「最終試験」に正確に等しい最初の値の位置を数値で返す。 4.
したがって、以下に示すものと同じくらい単純な大きな式を見ることができます。
= INDEX（A2：D11,5,4)
この例では、ハードコードされた値「evelyn」と「finalexam」を使用しました。 ただし、このような大きな式では、新しいものを検索するたびに値を変更する必要があるため、ハードコードされた値は必要ありません。 このような状況では、セル参照を使用して、次のように数式を動的にすることができます。
= INDEX（A2：D11,マッチ（G2、B2：B11,0）,マッチ（F3、A1：D1,0））
左ルックアップを適用するためのINDEXとMATCH
ここで、Evelynのクラスを知る必要があるとしましょう。どうすれば、INDEXとMATCHを使用して答えを知ることができますか？ 注意を払った場合、クラス列が名前列の左側にあり、別のExcelの強力なルックアップ関数であるVLOOKUPの機能を超えていることに注意してください。
実際、左ルックアップ機能は、INDEXとMATCHの組み合わせがVLOOKUPよりも優れている側面のXNUMXつです。
知るために Evelynのクラス、セルF3の値を「クラス」に変更し、上記と同じ数式を使用するだけで、INDEX関数とMATCH関数がすぐに答えを教えてくれます。
=INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F3,A1:D1,0)) >>>戻ります A
あなたがインストールを持っている場合LED Kutools for Excel, 私たちのチームによって開発されたプロのExcelアドイン、指定された値の左ルックアップを適用することもできます 右から左へのルックアップ 数回クリックするだけの機能。 この機能を実装するには、にアクセスしてください クツール あなたのエクセルのタブ、見つけてください 式 グループ化して、 右から左へのルックアップ のドロップダウンリスト スーパールックアップ。 次のようなポップアップダイアログボックスが表示されます。
Kutools for Excelで左ルックアップ機能を適用する具体的な手順については、ここをクリックしてください。
双方向ルックアップを適用するためのINDEXとMATCH
これで、動的ルックアップ値を使用してINDEXとMATCHの組み合わせ式を作成し、双方向ルックアップを適用できますか？ 以下に示すように、セルG3、G4、およびG5で数式を作成する練習をしてみましょう。
答えは次のとおりです。
セルG3： =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F3,A1:D1,0))
セルG4： =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F4,A1:D1,0))
セルG5： =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F5,A1:D1,0))
√注：数式を適用した後、セルG2の名前を変更することで、生徒の情報を簡単に取得できます。
大文字と小文字を区別するルックアップを適用するためのINDEXとMATCH
上記の例から、INDEX関数とMATCH関数では大文字と小文字が区別されないことがわかります。 ただし、大文字と小文字を区別する数式が必要な場合は、次のように追加できます。 EXACT このようにあなたの式に機能します：
√注：これは、次のように入力する必要がある配列数式です。 Ctrl + Shift + Enter。 中括弧のペアが数式バーに表示されます。
たとえば、知るために ジミーの試験のスコア、次のような関数を使用します。
=INDEX(A2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0),MATCH("final exam",A1:C1,0)) >>>戻ります 86
または、セル参照を使用できます。
=INDEX(A2:C11,MATCH(TRUE,EXACT(F2,A2:A11),0),MATCH(E3,A1:C1,0)) >>>戻ります 86
√ 注：で入力することを忘れないでください Ctrl + Shift + Enter.
複数の基準でルックアップを適用するためのINDEXとMATCH
複数の列と行のキャプションを持つ大きなデータベースを扱う場合、複数の条件を満たすものを見つけるのは常に注意が必要です。 この場合、複数の基準を検索するには、以下の式を参照してください。
√ 注：これは、次のように入力する必要がある配列数式です。 Ctrl + Shift + Enter。 中括弧のペアが数式バーに表示されます。
たとえば、 インド出身のクラスAのココの最終試験スコア、式は次のとおりです。
=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0)) >>>戻ります 88
√注：で入力することを忘れないでください Ctrl + Shift + Enter.
さて、あなたが常に使用するのを忘れたらどうしますか Ctrl + Shift + Enter 数式が正しくない結果を返すように数式を完成させるには？ ここに、より複雑な式があります。これを使用すると、XNUMXつの単純な式で完了することができます。 入力します キー：
上記の同じ例で インド出身のクラスAのココの最終試験スコア、通常だけ必要な式 入力します ヒットは次のとおりです。
=INDEX(D2:D11,MATCH(1,INDEX((G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0,1),0)) >>>戻ります 88
ここでは、複数の基準がある場合にユニバーサル式が必要になるため、ハードコードされた値は使用しません。 この方法でのみ、上記の例のセルG2、G3、G4の値を変更することで、必要な結果を簡単に得ることができます。
Kutools for Excel'■マルチコンディションルックアップ機能。数回クリックするだけで、複数の基準で特定の値をルックアップできます。 この機能を実装するには、にアクセスしてください クツール あなたのエクセルのタブ、見つけてください 式 グループ化して、 マルチコンディションルックアップ のドロップダウンリスト スーパールックアップ。 次に、次のようなポップアップダイアログボックスが表示されます。
Kutools for Excelでマルチ条件ルックアップ機能を適用する具体的な手順については、ここをクリックしてください。
複数の列にルックアップを適用するためのINDEXとMATCH
以下に示すように、XNUMXつのキャプションを共有する異なる列を持つExcelスプレッドシートがある場合、各学生の名前をINDEXおよびMATCHを使用してクラスと一致させるにはどうすればよいですか？
ここでは、私たちのプロのツールでタスクを完了する方法を紹介しましょう Kutools for Excel。 その フォーミュラヘルパー、 以下に示す手順で、生徒とクラスをすばやく一致させることができます。
1.機能を適用する宛先セルを選択します。
2。 下 クツール タブ、へ フォーミュラヘルパー、クリック フォーミュラヘルパー ドロップダウンリストで。
3。 選択する 数式タイプからのルックアップ、次にクリックしてください 複数の列にインデックスを付けて一致させる.
4.a。 1番目をクリックします 
の右側にあるボタン Lookup_col 値を返すセル、つまりクラス名を選択します。 （ここでは、XNUMXつの列または行のみを選択できます。）
NS。 2番目をクリックします の右側にあるボタン Table_rng 選択したセルの値と一致するセルを選択します Lookup_colつまり、学生の名前。
NS。 3番目をクリックします の右側にあるボタン 参照値 検索するセル、つまりクラスと一致させたい生徒の名前を選択します。
5. [OK]をクリックすると、宛先セルにジミーのクラス名が表示されます。
6.これで、塗りつぶしハンドルを下にドラッグして、他の生徒のクラスに入力できます。
最高のオフィス生産性ツール
Kutools for Excelはほとんどの問題を解決し、生産性を80％向上させます
- スーパーフォーミュラバー （複数行のテキストと数式を簡単に編集できます）; 読書レイアウト （多数のセルを簡単に読み取って編集する）; フィルター範囲に貼り付け...
- セル/行/列をマージする およびデータの保持。 分割セルコンテンツ; 重複する行と合計/平均を組み合わせる...重複セルを防止します。 範囲を比較する...
- [複製]または[一意]を選択します 行; 空白行を選択 （すべてのセルは空です）; スーパーファインドとファジーファインド 多くのワークブックで; ランダム選択...
- 正確なコピー 数式参照を変更せずに複数のセル。 参照の自動作成 複数のシートに; 箇条書きを挿入、チェックボックスなど...
- お気に入りの数式をすばやく挿入する、範囲、チャート、写真; セルを暗号化する パスワード付き。 メーリングリストを作成する そしてメールを送る...
- テキストを抽出、テキストの追加、位置による削除、 スペースを削除する; ページング小計の作成と印刷。 セルの内容とコメントを変換する...
- スーパーフィルター （フィルタースキームを保存して他のシートに適用します）; 高度な並べ替え 月/週/日、頻度など。 特殊フィルター 太字、斜体...
- ワークブックとワークシートを組み合わせる; キー列に基づいてテーブルをマージします。 データを複数のシートに分割; xls、xlsx、PDFをバッチ変換...
- ピボットテーブルのグループ化 週番号、曜日など... ロック解除された、ロックされたセルを表示する さまざまな色で; 式/名前を持つセルを強調表示する...
- Word、Excel、PowerPointでタブ付きの編集と読み取りを有効にする、パブリッシャー、アクセス、Visioおよびプロジェクト。
- 新しいウィンドウではなく、同じウィンドウの新しいタブで複数のドキュメントを開いて作成します。
- 生産性を 50% 向上させ、毎日何百回もマウス クリックを減らすことができます!
