Office Hack

ExcelのINDEX関数とMATCH関数を組み合わせた使い方

  • Release
  • Update

INDEX関数とMATCH関数を組み合わせた使い方をご紹介します。INDEX関数は指定した行と列が交差する位置にあるセルの内容を返す関数ですが、MATCH関数を組み合わせることでより柔軟な検索が可能になります。

さらにSUMIF関数とINDEX関数、MATCH関数を組み合わせる方法もご紹介しています。関数の組み合わせ方を身につけましょう。

INDEX関数とは?

INDEX関数

INDEX関数は、指定された行と列が交差する位置にあるセルまたはセルの参照を返します。読み方は「インデックス」です。

書式は以下の2種類があります。

  • 「=INDEX(配列, 行番号, [列番号])」
  • 「=INDEX(参照, 行番号, [列番号], [領域番号])」

以下の記事ではINDEX関数の使い方を網羅していますのでご参考ください。

ExcelのINDEX関数の使い方|行と列が交差する位置にあるセルを返す

このページを見ている人におすすめの商品

Excel関数+組み合わせ術 [実践ビジネス入門講座]【完全版】 作業効率とクオリティがいっきに高まる、究極の使いこなしテクニック 【Excel 2019/2016/2013 & Office 365対応】

Excel関数+組み合わせ術 [実践ビジネス入門講座]【完全版】 作業効率とクオリティがいっきに高まる、究極の使いこなしテクニック 【Excel 2019/2016/2013 & Office 365対応】

SBクリエイティブ
355ページ

\Kindle本ビジネス書キャンペーン 最大70%OFF/Amazonで価格を見る

\毎月5と0のつく日 楽天カード利用でポイント5倍/楽天市場で価格を見る


    MATCH関数とは?

    MATCH関数の結果

    MATCH関数は、指定された照合の種類に従って検査範囲内を検索し、検索値と一致する要素の、配列内での相対的な位置を表す数値を返します。読み方は「マッチ」です。

    書式は「=MATCH(検索値, 検索範囲, [照合の種類])」のように記述します。

    以下の記事ではMATCH関数の使い方を網羅していますのでご参考ください。

    ExcelのMATCH関数の使い方|検索値の範囲内での相対的な位置を返す

    INDEX関数とMATCH関数の組み合わせ方

    INDEX関数とMATCH関数を組み合わせて、入力された検索値を参照するテーブルから検索し、対応するデータを表示する手順を説明していきます。

    作業時間:12分

    元となる表を準備する

    元となる表を準備する

    まず最初に検索するための表(テーブル)形式のデータを用意します。例では「商品コード」「商品名」「単価」が書かれた価格テーブルを用意しました。受注シートの商品名から商品コードを検索して表示させてみましょう。

    INDEX関数の参照を指定する

    INDEX関数の参照を指定する

    数式を入力したい【セル(例ではC10セル)】を選択し、『=INDEX(B4:D6』と入力します。セル範囲はドラッグして選択することもできます。

    参照先を絶対参照にする

    絶対参照にする

    このままだと数式をコピーした時に参照先がズレてしまうので絶対参照にします。【F4】キーを押します。「B4:D6」が「$B$4:$D$6」となり絶対参照になりました。

    INDEX関数の行番号を指定する

    INDEX関数の行番号を指定する

    参照先から「バナナ」の行がどこにあるかを指定します。「B4:D6」のセル範囲では2行目にバナナがあります。「=INDEX($B$4:$D$6」に続いて『,2,』と入力します。

    INDEX関数の列番号を指定する

    INDEX関数の列番号を指定する

    検索したい商品コードがどの列にあるかを指定します。「B4:D6」のセル範囲では1列目に商品コードがあります。「=INDEX($B$4:$D$6,2,」に続いて『1)』と入力し、【Enter】を押します。

    MATCH関数の検索値を指定する

    MATCH関数の検索値を指定する

    ここまででINDEX関数を用いて「バナナ」のみ固定で商品コードを検索できるようになりました。任意の商品名で検索できるようにするためにINDEX関数の行番号をMATCH関数に置き換えます。まずMATCH関数の検索値を指定します。「=INDEX($B$4:$D$6,」に続いて『MATCH(D10,』と入力します。

    MATCH関数の検索範囲を指定する

    MATCH関数の検索範囲を指定する

    MATCH関数の検索範囲として価格テーブルの商品名の列を指定します。「=INDEX($B$4:$D$6,MATCH(D10,」に続いて『$C$4:$C$6,』と入力します。

    MATCH関数の照合の種類を指定する

    MATCH関数の照合の種類を指定する

    MATCH関数の照合の種類として完全一致の「0」を指定します。「=INDEX($B$4:$D$6,MATCH(D10,$C$4:$C$6,」に続いて『0),』と入力します。

    INDEX関数の列番号を指定する

    INDEX関数の列番号を指定する

    最後にINDEX関数の列番号として価格テーブルの商品コードの列を指定します。「=INDEX($B$4:$D$6,MATCH(D10,$C$4:$C$6,0),」に続いて『1)』と入力し、【Enter】を押します。

    INDEX関数とMATCH関数を組み合わせた結果

    INDEX関数とMATCH関数を組み合わせた結果

    数式を入力したC10セルにD10で入力した商品名を価格テーブルから検索して対応する商品コード「1002」を表示することができました。

    商品名を変更した結果

    本当に任意の商品名の検索に対応しているか確認するため、D10セルの商品名を『みかん』とすると商品コードに対応する「1003」と表示されました。

    INDEX関数とMATCH関数を組み合わせることで自由度が高い検索をすることができます。

    VLOOKUP関数でも同じように検索することはできますが、VLOOKUP関数は一番左の列を検索範囲とし、2列目以降の値を取得することができます。今回のように検索範囲の左側を検索することはできません。

    VLOOKUP関数で左側(右から左に)の値を取得するのが難しいことを以下の記事で説明していますのでご参考ください。

    ExcelのVLOOKUP関数以外で左側(右から左に)の値を取得する

    INDEX関数とMATCH関数の処理の流れ

    INDEX関数とMATCH関数の処理の流れを確認すると以下のようになります。

    1. MATCH関数でD10セルに入力された商品名(バナナ)を価格テーブルから検索して位置を取得
    2. MATCH関数で取得した位置を行番号としてINDEX関数で1列目の商品コードを取得

    SUMIF、INDEX、MATCH関数を複合利用する

    SUMIF関数の合計範囲の自由度を高めるにはINDEX関数とMATCH関数を組み合わせます。固定の合計範囲ではなく任意の条件に当てはまったものだけ合計したい場合に活用できます。

    元となる表データ

    2つのシートがあります。Sheet1は参照する元の表データが入っています。「商品コード」「商品名」「単価」の他に10月~12月までの各月ごとの売上個数が入力されています。

    合計を出力するシート

    Sheet2には「商品コード」「対象月」を入力すると合計の売上個数を表示させるようにしていきます。

    SUMIF関数の入力

    まずはSUMIF関数を書いてみましょう。Sheet2のD3セルを選択し、『=SUMIF(Sheet1!B3:B6,』と入力します。

    SUMIF関数は指定された検索条件に一致するセルの値を合計する関数です。「=SUMIF(範囲,検索条件,[合計範囲])」のように記述します。

    SUMIF関数の基本的な使い方を以下の記事で説明しています。ご参考ください。

    ExcelのSUMIF関数の使い方|検索条件に一致するセルの値を合計する

    条件の指定

    SUMIF関数の検索条件としてSheet2の商品コードを指定します。「=SUMIF(Sheet1!B3:B6,」に続いて『B3,』と入力します。

    INDEX関数の入力

    SUMIF関数の合計範囲としてINDEX関数を指定します。「=SUMIF(Sheet1!B3:B6,B3,」に続いて『INDEX(Sheet1!B3:G6,』と入力します。

    列番号、行番号の指定

    INDEX関数の行番号には「0」を、列番号にはMATCH関数を指定します。「=SUMIF(Sheet1!B3:B6,B3,INDEX(Sheet1!B3:G6,」に続いて『0,MATCH(C3,』と入力します。

    MATCH関数の入力

    MATCH関数の検査範囲を指定します。「=SUMIF(Sheet1!B3:B6,B3,INDEX(Sheet1!B3:G6,0,MATCH(C3,」に続いて『Sheet1!B2:G2,』と入力します。

    照合の種類の指定

    MATCH関数の照合の種類を指定します。「=SUMIF(Sheet1!B3:B6,B3,INDEX(Sheet1!B3:G6,0,MATCH(C3,Sheet1!B2:G2,」に続いて『0)))』と入力し、Enterを入力します。

    複合利用の結果

    商品コード1002の10月の売上個数の合計がSheet2のD3セルに表示されました。

    数式が長くなりましたが、SUMIF関数の引数にINDEX関数、MATCH関数を入れ子にすることでより柔軟な条件で合計することが可能です。

    他の関数も合わせてチェック!

    Office Hackでは、ここでご紹介できなかった関数の一覧ページもご用意しております。ぜひ、参考にしてください。

    全488種のExcel関数一覧表!検索/並び替え/絞り込み機能付

    [Excel 関数 検索/行列]の関連記事

    ExcelのADDRESS関数の使い方|セル参照の文字列を求める

    ExcelのCHOOSE関数の使い方|インデックスに対応した値を取り出す

    ExcelのFILTER関数の使い方|必要なデータを抽出する

    ExcelのGETPIVOTDATA関数の使い方|ピボットテーブルのデータ取得

    ExcelのHLOOKUP関数の使い方|指定の行と同じ列にある値を返す

    ExcelのHYPERLINK関数の使い方|ハイパーリンクを作成する

    ExcelのINDIRECT関数の使い方|セルや他のシートを参照する

    ExcelのLOOKUP関数の使い方|1行または1列の範囲から検索する

    ExcelでOFFSET関数の使い方|指定した位置のセル範囲を参照する

    ExcelのROWS関数の使い方|参照、または配列に含まれる行数を返す

    ExcelのXLOOKUP関数の使い方|縦方向/横方向を両方検索する

    ExcelのXMATCH関数の使い方|範囲を検索し相対的な位置を返す

    コメント

    この記事へのコメントをお寄せ下さい。

    0 コメント
    すべてのコメントを表示