Windows 8 & Office 2013 Excel 2013

行と列が交差するセルの値を求める(INDEX関数とMATCH・XMATCH関数)

投稿日:2017年1月9日 更新日:

Excelで下のようなマトリックス表があって、このマトリックス表の行と列が交差する値を、関数を使って自動的に求める方法です。

早見表などでも使用できると思います。

下の表は、1行目に店名、A列に食材名が表記されています。

セル[C13]に食材名を、セル[C14]には店名を入力すると、セル[C15]には、価格が自動的に表示できるようにしたいと思います。

Excelのマトリックス表(クロス集計表)

入力規則の設定

まず、セル[C13]とセル[C14]には、入力規則を設定していてドロップダウンリストから選択できるようにします。

セル[C3]を選択して、[データ]タブの[データの入力規則]をクリックします。

[データ]タブの[データの入力規則]

[データの入力規則]ダイアログボックスで[設定]タブにある[入力値の種類]のプルダウンメニューから[リスト]を選択します。

[データの入力規則]ダイアログボックス

そして、[元の値]の[ダイアログボックス拡大/縮小]ボタンをクリックします。

[データの入力規則]ダイアログボックスの[元の値]の[ダイアログボックス拡大/縮小]ボタン

食材名が表記されているセル[A2]からセル[A11]までをドラッグします。

そして、再度 [ダイアログボックス拡大/縮小]ボタンをクリックします。

データの入力規則の[元の値]を範囲選択

[元の値]に食材のリスト範囲が指定できたことを確認して、[OK]ボタンをクリックします。

[元の値]に入力された[データの入力規則]ダイアログボックス

セル[C13]をクリックすると、ドロップダウンリストが表示されリストから食材を選択できるようになります。

同じようにしてセル[C14]にも入力規則を設定して、リストから店名を選択できるようにします。

ドロップダウンリストから入力

参考入力規則については、以下の記事で解説しています。

データの入力規則のリストの参照範囲の設定と入力規則の設定範囲の確認

Excel2010では、[データの入力規則]の設定で、[元の値]を参照する場合、別シートの範囲も設定できるようになりました。 参考Excel ...

続きを見る

INDEX関数

セル[C15]に関数を入力します。

INDEX関数とMATCH関数を組み合わせますが、まずINDEX関数を解説します。

INDEX 関数 - Microsoft サポート(Microsoft)

=INDEX(参照,行番号,列番号)

上記の表では、参照範囲はセル[B2]からセル[H11]、行番号は7(食材Gは参照範囲内で7番目)、列番号は4(D店は参照範囲内で4番目)となります。

INDEX関数の関数の引数ダイアログボックスです。

INDEX関数の関数の引数ダイアログボックス

セル[C15]の数式は以下のようになります。

=INDEX(B2:H11,7,4)

セル[C15]の数式

行番号は参照範囲内で何番目、列番号は何番目といちいち数えるのは面倒ですね。

そこで、MATCH関数を使うと便利ですよということです。

MATCH関数

=MATCH(検査値,検査範囲,照合の種類)

上記の表で、セル[C13]に入力した食材が範囲内で何番目になるかを求めるには

検査値はセル[C13]、検査範囲はセル[A2~A11]、照合の型は検査値と完全一致を求めるので0となります。

照合の種類
0 [検索値]と一致、[検索値]が文字列の場合は、ワイルドカードの使用ができます。
-1 [検索値]以上の最小値。[検索範囲]を降順に並べておく必要があります。
1または省略 [検索値]以下の最大値(既定値)。[検索範囲]を昇順に並べておく必要があります。

検索時に大文字と小文字は区別されませんが、全角と半角は区別されます。

MATCH 関数 - Microsoft サポート(Microsoft)

数式は以下のようになります。

=MATCH(C13,A2:A11,0)

同じようにセル[C14]に入力した店名が範囲内で何番目になるかを求めます。

=MATCH(C14,B1:H1,0)

INDEX関数にMATCH関数をネストします。

INDEX関数の関数の引数ダイアログボックス-MATCH関数をネスト

セル[C15]に以下の数式を入力します。

=INDEX(B2:H11,MATCH(C13,A2:A11,0),MATCH(C14,B1:H1,0))

INDEX関数にMATCH関数をネストしたのがわかりますね。

数式バーの関数-INDEX関数とMATCH関数のネスト

セル[C15]の数式

XMATCH関数を使用する場合

Excel2021と、Microsoft 365のExcelでは、新関数[XMATCH]関数を使用できます。

XMATCH関数を使用するのであれば、以下のようになります。[検索値]と[検索範囲]を指定するだけでOKです。

以下の画像は、Excel2021です。

INDEX関数の関数の引数ダイアログボックス-XMATCH関数をネスト

=INDEX(B2:H11,XMATCH(C13,A2:A11),XMATCH(C14,B1:H1))

数式バーの関数-INDEX関数とXMATCH関数のネスト

XMATCH関数の数式は、以下のようになっています。

[一致モード]の既定が完全一致なので、省略できます。また、[検索モード]も既定値が先頭から末尾への検索なので、これも省略できます。

=XMATCH(検査値,検査範囲,一致モード,検索モード)

一致モード
0または省略 完全一致(既定値)
-1 完全一致 または 次に小さい項目
1 完全一致 または 次に大きい項目
2 ワイルドカード文字との一致
検索モード
1または省略 先頭から末尾へ検索(既定値)
-1 末尾から先頭へ検索
2 バイナリ検索([検索範囲]を昇順に並べておく必要があります。)
-2 バイナリ検索([検索範囲]を降順に並べておく必要があります。)

XMATCH 関数 - Microsoft サポート(Microsoft)

関連以下の記事も参考になると思います。

3つの条件を満たす値を求める(MATCH関数とINDEX関数を組み合わせて)

Excelでリストの中から、3つの条件を満たす値を求める方法です。 先日、職場でちょっと難しい課題にぶつかってしまいました。 リストの中から ...

続きを見る

INDEX関数とMATCH関数

この記事で使用した表は、マトリックス表、または、クロス集計表と呼ばれるものです。マトリックス表は、旧のピボットテーブルウィザードや、Power Query(パワークエリ)エディターを使用してテーブルに変換できます。

テーブルへ変換したほうが、便利な場合もあります。変換方法は、以下の記事で解説しています。

マトリックス表(クロス集計)をピボットテーブルウィザードでテーブルに変換

マトリックス表(クロス集計)は、ピボットテーブルウィザードの[複数のワークシート範囲]を選択して、リスト形式に変換することができます。 Ex ...

続きを見る

Power Queryでデータベースに整形(マトリックス表をテーブルに)

Power Query(パワークエリ)は、Excel2016とExcel2019には、標準でインストールされています。Excel2010とE ...

続きを見る

以下は入力規則に関する記事です。

ドロップダウンリストから選択して自動的にセルに塗りつぶしを設定

Excelで表を作成してセルに入力する文字列が決まっている場合は、データの入力規則を使ってドロップダウンリストから選択して入力できるようにす ...

続きを見る

入力規則のリストが可変(増減)するならテーブルに変換して自動修正

Excelの入力規則でドロップダウンリストから選択できるように設定することは多いですね。 でも、そのリストが増減する場合、その度に入力規則の ...

続きを見る

ドロップダウンリストを2段階選択で絞り込むには(データの入力規則)

Excelでは入力規則と関数を使って、リストの中から1つ選択すると、次のリストは前のリストに該当する内容の中からしか選択できないようにするこ ...

続きを見る

入力規則のエラーメッセージと無効データのマーク

Excelの[データ]タブの[データの入力規則]のメニューの中に[無効データのマーク]というのがあります。 これは入力規則に設定した以外の値 ...

続きを見る

重複データが入力されたらメッセージを表示して規制するには

Excelで1つの列に重複した値を入力できないように規制する方法を紹介します。 Excel2013で解説しますが、他のバージョンも同じ方法で ...

続きを見る

半角・ひらがな入力などを自動で切り替えるには[データの入力規則]で

Excelでは、[入力規則]を使って入力時の日本語入力をオンにしたり、オフにしたりすることができます。 この設定を行っておくと、入力時にIM ...

続きを見る

検索

ほかの記事も検索してみませんか?

Access / Excel / PowerPoint / Word

関連記事30件

Profile

-Windows 8 & Office 2013 Excel 2013

Copyright © 2001-2024 初心者のためのOffice講座 All Rights Reserved.

Copyright© 初心者のためのOffice講座 , 2024 AllRights Reserved Powered by AFFINGER4.