Aさん VLOOKUPを覚えてデータ転記は便利になりましたが、同じ主キーで複数列を転記したい場合に、いちいち列番号を手修正するのが面倒です。。
こんな場合、もっと楽にできる方法はありませんかね？
森田 その場合は、関数の「MATCH」を活用すると良いですよ！
では、MATCHの使い方について解説していきますね。
はじめに
この記事は関数の概要とVLOOKUPの詳細を把握していることが前提です。
関数の概要とVLOOKUPの使い方の詳細は以下の記事をご参照ください。
はじめに この記事は数式の概要を把握していることが前提です。 参考記事 数式の概要の詳細は以下の記事をご参照ください。 関数とは 関数とは、数式の構成要素の1つであり、固有の計算/処理の機能がセットされた数式のことです。 …
はじめに この記事は関数の概要を把握していることが前提です。 参考記事 関数の概要の詳細は以下の記事をご参照ください。 別表からのデータ転記を自動化したい場合は「VLOOKUP」が有効 実務でデータ集計/分析を行う際、1 …
表の行番号/列番号を自動計算したい場合は「MATCH」が有効
データ転記作業において、表の行番号や列番号を自動計算できると、より便利になるケースがあります。
たとえば、VLOOKUPを使い、同じ主キーで複数列を転記する場合です。
VLOOKUPの引数「列番号」を定数で指定していると、列ごとにVLOOKUPの数式の列番号を手修正する必要があり、列数が多いほど手間がかかってしまいます。
こんな場合、関数の「MATCH」を使い、列番号を自動計算しましょう（行番号も計算可能）。
MATCHは「マッチ」と読む。
MATCHを使うことで、指定した値が対象範囲の何行/何列目に位置するかを数値で返すことが可能です。
VLOOKUP+MATCHの組み合わせテクニックの詳細は以下の記事をご参照ください。
はじめに この記事はVLOOKUPとMATCHの詳細を把握していることが前提です。 参考記事 VLOOKUPとMATCHの使い方の詳細は以下の記事をご参照ください。 VLOOKUPでの複数列の転記は引数「列番号」の手修正 …
MATCHの構文
MATCHの構文は以下の通りです。
=MATCH(検査値,検査範囲,[照合の種類])
指定された照合の種類に従って検査範囲内を検索し、検査値と一致する要素の、配列内での相対的な位置を表す数値を返します。
| 引数名 | 必須 | データ型 | 説明 |
|---|---|---|---|
| 検査値 | ○ | すべて | 「検査範囲」の範囲内で検索したい値を示す単一セルを指定します。 |
| 検査範囲 | ○ | 数値 | 検索する対象のセル範囲（1行または1列）を指定します。 |
| 照合の種類 | － | 数値 | 2種類の「近似一致」と「完全一致」のいずれで検索するか指定します。 ※1：近似一致（「検査値」以下の最大値）、 0：完全一致（「検査値」と等しい最初の値）、 -1：近似一致（「検査値」以上の最小値） |
MATCHの各引数はVLOOKUPの引数と類似（以下、左がMATCH、右がVLOOKUP）。
※検査値=検索値、検査範囲=範囲、照合の種類=検索方法
引数「照合の種類」を省略した場合、「1」と同じ近似一致（「検査値」以下の最大値）で検索される。
MATCHでは英字の大文字と小文字で区別されない。
※引数「検査値」と引数「検査範囲」で大文字↔小文字の表記ゆれがあっても同一文字扱いとなる。
引数「検査値」に指定した値が引数「検査範囲」にない場合、エラー値「#N/A」が表示。
引数「検査範囲」に指定したセル範囲が2行以上または2列以上の場合、エラー値「#N/A」が表示。
引数「照合の種類」が「0」で、かつ引数「検査値」に指定する値が文字列の場合、引数「検査値」にワイルドカード文字（*、?）を使用し、あいまい検索が可能。
※ワイルドカード文字（*、?）を通常の文字として検索したい場合は、その文字の前に半角のチルダ（~）を付けること（例：~*）。
【参考】引数「検索方法」は「完全一致」が基本
前述の通り、MATCHの引数「照合の種類」は省略できますが、実務では「完全一致」で検索することが大原則です。
よって、この引数は省略せず、「0」を指定すると覚えておきましょう。
なお、実務では近似一致で検索するケースはほぼありませんが、どんな動作になるかは以下の表をご覧ください。
| 照合の種類 | 検索方法 | 動作 |
|---|---|---|
| 1または省略 | 近似一致 | 引数「検査値」の値以下のものから最大の値を検索します。 引数「検査範囲」に指定した行・列の中の値は昇順である必要があります。 例）1→9、A→Z、あ→ん、FALSE→TRUE |
| 0 | 完全一致 | 引数「検査値」の値と等しい最初の値を検索します。 引数「検査範囲」に指定した行・列の中の値は任意の並びで問題ありません。 |
| -1 | 近似一致 | 引数「検査値」の値以上のものから最小の値を検索します。 引数「検査範囲」に指定した行・列の中の値は降順である必要があります。 例）9→1、Z→A、ん→あ、TRUE→FALSE |
いずれのケースも、引数「検査範囲」が1行の場合は左から、1列の場合は上から検索される。
【参考】MATCHは「検索/行列関数」
あくまで参考情報となりますが、MATCHはリボン「数式」タブの関数ライブラリの「検索/行列」に分類されています。
実際にMATCHを活用する際は、以下で解説しているように直接入力で挿入していきましょう。
MATCHの使用結果イメージ
MATCHを使い、列番号を返すイメージは以下の通りです。
今回は「商品名」・「単価」列が「商品マスタ」シート上で何列目にあるか、計算結果を注文テーブルのD1・E1セルへ表示しました。
MATCHを複数セルへ使う場合、1セルにつき1つの関数を使います。
ベースの数式をセットしたら、他のセルへペーストしましょう。
後は、MATCHの戻り値をVLOOKUPの引数「列番号」で参照すれば、同じVLOOKUPの数式で複数列の転記が可能となります。
なお、今回はD1セルの数式をD2セルへコピペで使いまわせるように引数「検査値」は複合参照（行のみ絶対参照）にしています。
また、引数「検査範囲」はどのMATCHの数式でも同じセル範囲（A1~C1セル）で参照を固定するため、絶対参照にしています。
絶対参照/相対参照の詳細については以下の記事をご参照ください。
関数の数式を直接入力できるようになってきました！ もっと関数を実務で役立てていくために、他に覚えておいた方が良いことはありますか？？ …
【参考】「行番号」は主キーを基準にすることが一般的
先述の通り、列番号を計算したい場合はMATCHの引数「検査値」にフィールド名を指定します。
一方、行番号を計算したい場合は、主キーを指定することが一般的です。
MATCHで行番号を計算するケースは、INDEXとの組み合わせ時が多いです。
INDEXの詳細については以下の記事をご参照ください。
はじめに この記事はVLOOKUPとMATCHの詳細を把握していることが前提です。 参考記事 VLOOKUPとMATCHの使い方の詳細は以下の記事をご参照ください。 検索対象の表の左端に主キーの列がない場合は「INDEX …
MATCHの数式の挿入手順
上記の結果を得るための手順は以下の通りです。
- 関数を挿入するセルを選択
※今回はD1セル - 「=mat」等と入力
- サジェストから「MATCH」を選択し、「Tab」キーで確定
- 検査値にしたいセルを選択
※今回はD2セル（行のみ絶対参照） - コンマ（,）を入力
- 検索対象のシートへ移動
※今回は「商品マスタ」シート - 検索対象のセル範囲を選択
※今回はA1~C1セル（絶対参照） - コンマ（,）を入力
- 「0」を選択 or 入力
- 「Enter」キーで確定
- ベースの数式をコピーし、以降のセルへペースト
※今回はE1セルへペースト
手順②の際にIMEを半角英数モードにすること。
手順④は、行番号を計算したい場合は主キー、列番号を計算したい場合はフィールド名を指定するケースが一般的。
手順④で選択したセルがテーブルで構造化参照になってしまう場合、複合参照（D$2等）で直接入力すること（手順⑪でコピペしたセルの引数「検査値」がスライドしないため）。
サンプルファイルで練習しよう！
可能であれば、以下のサンプルファイルをダウンロードして、実際に操作練習をしてみてください。
※サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります。
（上記リンクから登録フォームへ遷移します）
ブックを開いたら、次の手順を実施してください。（今までの解説のまとめです）
- 関数を挿入するセルを選択
※今回はD1セル - 「=mat」等と入力
- サジェストから「MATCH」を選択し、「Tab」キーで確定
- 検査値にしたいセルを選択
※今回はD2セル（行のみ絶対参照） - コンマ（,）を入力
- 検索対象のシートへ移動
※今回は「商品マスタ」シート - 検索対象のセル範囲を選択
※今回はA1~C1セル（絶対参照） - コンマ（,）を入力
- 「0」を選択 or 入力
- 「Enter」キーで確定
- ベースの数式をコピーし、以降のセルへペースト
※今回はE1セルへペースト
本記事の解説と同じ結果になればOKです！
さいごに
いかがでしたでしょうか？
MATCHは表の行番号/列番号を自動計算する際に役立つ関数です。
VLOOKUPやINDEX等、他の検索/行列関数と組み合わせ、より高度なデータ転記作業を行うことが可能になるため、ぜひ覚えていきましょう！
なお、MATCH以外にもExcel関数の各種テクニックを拙著で解説していますので、こちらも参考にしてみてください。
また、よりコアな情報を発信していくために「公式LINE」を開始しましたので、ご興味があれば以下バナーから友だち追加をお願いします！
特典として、Excelワークシート関数一覧（計22スライド）やExcelショートカットキー一覧（計25スライド）等をプレゼント！
ご参考になれば幸いですm(_ _)m
森田 MATCHとセットで覚えた方が良い関数は、行番号/列番号を基準にデータ転記を行う「VLOOKUP」と「INDEX」です。
これらとMATCHを組み合わせることで、MATCHが自動計算した行番号/列番号を使って、より柔軟なデータ転記を実現できますよ！
