Aさん VLOOKUPを覚えてデータ転記は便利になりましたが、検索対象の表の右端に主キーの列があると困ってしまいます。。
こんな場合、どうすればデータ転記できますかね？
森田 その場合は、関数の「INDEX」を活用すると良いですよ！
では、INDEXの使い方について解説していきますね。
はじめに
この記事はVLOOKUPとMATCHの詳細を把握していることが前提です。
VLOOKUPとMATCHの使い方の詳細は以下の記事をご参照ください。
はじめに この記事は関数の概要を把握していることが前提です。 参考記事 関数の概要の詳細は以下の記事をご参照ください。 別表からのデータ転記を自動化したい場合は「VLOOKUP」が有効 実務でデータ集計/分析を行う際、1 …
はじめに この記事は関数の概要とVLOOKUPの詳細を把握していることが前提です。 参考記事 関数の概要とVLOOKUPの使い方の詳細は以下の記事をご参照ください。 表の行番号/列番号を自動計算したい場合は「MATCH」 …
検索対象の表の左端に主キーの列がない場合は「INDEX」が有効
VLOOKUPを使う前提条件の1つは、検索対象の表の左端が主キーの列であることです。
しかし、実務では主キーが左端にない表からデータ転記したい場合もあります。
こうした表だと、主キーの列を切り取り→表の左端へ挿入するか、主キーの列をコピー→表の左端へリンク貼り付けするといった事前準備をしないとVLOOKUPが使えません。
こんな場合、関数の「INDEX」を使えば、事前準備不要でデータ転記が可能となります。
INDEXは「インデックス」と読む。
INDEXを使うことで、指定の表を行番号と列番号で検索し、交点となるセルの値を転記することが可能です。
INDEXの構文
INDEXの構文は以下の通りです。
=INDEX(参照,行番号,[列番号],[領域番号])
指定された行と列が交差する位置にある値またはセルの参照を返します。
| 引数名 | 必須 | データ型 | 説明 |
|---|---|---|---|
| 参照 | ○ | 参照 | 検索する対象のセル範囲を指定します。 ※複数のセル範囲を指定する場合、「(A1:B10,D1:E10)」のようにカッコで囲み、セル範囲の間はコンマ（,）で区切ります。 |
| 行番号 | ○ | 数値 | 「参照」の上から何行目を戻り値にしたいか、整数を指定します。 ※上から1行目は「1」 |
| 列番号 | － | 数値 | 「参照」の左端から何列目を戻り値にしたいか、整数を指定します。 ※左端は「1」 |
| 領域番号 | － | 数値 | 「参照」の何番目のセル範囲を検索したいか、整数を指定します。 ※「参照」が複数のセル範囲の場合に指定し、「(A1:B10,D1:E10)」なら「A1:B10」が「1」、「D1:E10」が「2」となります。 |
引数「参照」に指定したセル範囲が1行または1列の場合、引数「行番号」・「列番号」を省略可能。
※1行なら「行番号」、1列なら「列番号」をそれぞれ省略可能。
引数「行番号」・「列番号」に「0」を指定した場合、引数「参照」のセル範囲の行全体または列全体がINDEXの戻り値となる（両方の場合は引数「参照」のセル範囲全体）。
※ExcelのバージョンがExcel2021またはMicrosoft365ならスピル、それ以外は配列数式（出力範囲を選択→数式入力→「Ctrl」+「Shift」+「Enter」で確定）。
引数「行番号」・「列番号」・「領域番号」に指定した値が引数「参照」の範囲外の場合、エラー値「#REF!」が表示。
引数「参照」に指定した複数のセル範囲が同一シートにない場合、エラー値「#VALUE!」が表示。
【参考】INDEXは2種類の形式がある
INDEXは「配列形式」と「セル範囲形式」の2つの構文が用意されています。
先述の構文はセル範囲形式のもので、配列形式の場合の構文は以下の通りです。
=INDEX(配列,行番号,[列番号])
実務において、この2つの形式の使い分けは特に意識しなくともOKです。
【参考】INDEXは「検索/行列関数」
あくまで参考情報となりますが、INDEXはリボン「数式」タブの関数ライブラリの「検索/行列」に分類されています。
実際にINDEXを活用する際は、以下で解説しているように直接入力で挿入していきましょう。
INDEXの使用結果イメージ
INDEXを使い、データ転記を行うイメージは以下の通りです。
今回は商品マスタのA2~C10セルを対象に、行番号はF1セルの値、列番号はF2セルの値を指定し、その交点となるセルの値を転記しました。
結果、INDEXの戻り値は行番号と列番号の交点に該当するB6セルの値が転記されました。
今回は単一セルでINDEXを使用するため、すべて相対参照にしていますが、ベースの数式を複数セルへコピペで使い回す場合、引数「参照」は絶対参照にしましょう。
絶対参照/相対参照の詳細については以下の記事をご参照ください。
関数の数式を直接入力できるようになってきました！ もっと関数を実務で役立てていくために、他に覚えておいた方が良いことはありますか？？ …
【参考】引数「行番号」・「列番号」はMATCHと組み合わせることが一般的
上記の使用結果イメージは解説をシンプルにするため、INDEX単独での数式にしていましたが、実務では引数「行番号」・「列番号」はMATCHと組み合わせることが一般的です。
理由として、INDEX単独では、主キーやフィールド名の位置を特定できないためです。
よって、MATCHを使い、指定した値が対象範囲の何行/何列目に位置するか自動計算した結果を、INDEXの行番号/列番号として利用します。
これで、主キーの列の位置に関係なく、複数列のデータ転記にも対応できるようになります。
なお、単一列の転記でよければ、引数「列番号」のMATCHは不要です（定数を指定）。
INDEXの数式の挿入手順
上記の結果を得るための手順は以下の通りです。
- 関数を挿入するセルを選択
※今回はF3セル - 「=in」等と入力
- サジェストから「INDEX」を選択し、「Tab」キーで確定
- 転記したいテーブル/セル範囲を選択
※今回はA2~C10セル - コンマ（,）を入力
- 転記したい行番号を入力 or セルを選択
※今回はF1セル - コンマ（,）を入力
- 転記したい列番号を入力 or セルを選択
※今回はF2セル - 「Enter」キーで確定
手順②の際にIMEを半角英数モードにすること。
【応用】条件により検索対象の表を切り替えたい場合は引数「領域番号」を指定する
INDEXの応用的なテクニックとして、引数「領域番号」を指定することで、条件に応じて検索対象の表を切り替えることも可能です。
一例として、2022年度と2023年度の商品マスタを切り替える場合、INDEXの数式は以下の通りです。
今回の領域番号が「2」なので、引数「参照」の2つ目のセル範囲が検索対象となっています。
なお、今回は引数「領域番号」を数値で直接指定していますが、「2022」や「2023」といった値を条件に領域を切り替えたい場合、IF等で任意の値を領域番号に変換すると良いです（例：「2022」の場合は「1」、「2023」の場合は「2」にする等）。
IFの詳細については以下の記事をご参照ください。
はじめに この記事は関数の概要を把握していることが前提です。 参考記事 関数の概要については以下の記事をご参照ください。 条件判定の結果を値で表示したい場合は「IF」が有効 実務では、既存データに対して基準値を条件に評価 …
サンプルファイルで練習しよう！
可能であれば、以下のサンプルファイルをダウンロードして、実際に操作練習をしてみてください。
※サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります。
（上記リンクから登録フォームへ遷移します）
ブックを開いたら、次の手順を実施してください。（今までの解説のまとめです）
- 関数を挿入するセルを選択
※今回はF3セル - 「=in」等と入力
- サジェストから「INDEX」を選択し、「Tab」キーで確定
- 転記したいテーブル/セル範囲を選択
※今回はA2~C10セル - コンマ（,）を入力
- 転記したい行番号を入力 or セルを選択
※今回はF1セル - コンマ（,）を入力
- 転記したい列番号を入力 or セルを選択
※今回はF2セル - 「Enter」キーで確定
本記事の解説と同じ結果になればOKです！
さいごに
いかがでしたでしょうか？
INDEXは別表からのデータ転記の自動化に役立つ関数の一つです。
VLOOKUP以上に柔軟で高度なデータ転記やレイアウト変更が可能になるため、VLOOKUPとMATCHの次のステップとして、ぜひ覚えていきましょう！
なお、INDEX以外にもExcelでのデータ整形の各種テクニックを拙著で解説していますので、こちらも参考にしてみてください。
ご参考になれば幸いですm(_ _)m
森田 INDEX+MATCHを使えるようになると、ほとんどのデータ転記に対応できるようになって便利です！
また、この組み合わせテクニックを使えると、他の関数も問題なく使えるレベルとも言えますね。
ぜひ、この機会に学習し、実務へ活用できるようになってください。
