Windows8 & Office2013 Excel2013

Print Friendly, PDF & Email

行と列の交差するセルの値を求める

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

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

下の表は、1行目に店名、A列に食材名が表記されています。セルC13に食材名をセルC14には店名を入力すると、セルC15には、価格が自動的に表示できるようにしたいと思います。
エクセル表

まず、セルC13とセルC14には、入力規則を設定していてプルダウンから選択できるようにします。
セルC3を選択して、[データ]タブの[データの入力規則]をクリックします。
データの入力規則

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

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

食材名が表記されているセルA2からセルA11までをドラッグします。そして、再度 ダイアログボックス拡大/縮小ボタンをクリックします。
範囲選択

元の値に食材のリスト範囲が指定できたことを確認して、[OK]ボタンをクリックします。
[データの入力規則]ダイアログボックス

セルC13をクリックすると、ドロップダウンリストが表示されリストから食材を選択できるようになります。
同じようにしてセルC14にも入力規則を設定して、リストから店名を選択できるようにします。
リストから入力

セルC15に関数を入力します。
INDEX関数とMATCH関数を組み合わせますが、まずINDEX関数を解説します。

INDEX関数

=INDEX(参照,行番号,列番号)
上記の表では、参照範囲はセルB2からセルH11、行番号は7(食材Gは参照範囲内で7番目)、列番号は4(D店は参照範囲内で4番目)となります。セルC5の数式は以下のようになります。
=INDEX(B2:H11,7,4)
数式

行番号は参照範囲内で何番目、列番号は何番目といちいち数えるのは面倒ですね。そこで、MATCH関数を使うと便利です。

MATCH関数

=MATCH(検査値,検査範囲,照合の型)
上記の表で、セルC13に入力した食材が範囲内で何番目になるかを求めるには
検査値はセルC13、検査範囲はセルA2~A11、照合の型は検査値と完全一致を求めるので0となります。数式は以下のようになります。
=MATCH(C13,A2:A11,0)

同じようにセルC14に入力した店名が範囲寧で何番目になるかを求めます。
=MATCH(C14,B1:H1,0)

INDEX関数にMATCH関数をネストします。
セルC15に以下の数式を入力します。
=INDEX(B2:H11,MATCH(C13,A2:A11,0),MATCH(C14,B1:H1,0))

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

数式

関連以下の記事も参考になると思います。ぜひご参照ください。
3つの条件を満たす値を求めるには

3つの条件を満たす値を求めるには

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

続きを見る

INDEX関数とMATCH関数

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

絞り込みリストの作成

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

続きを見る

入力規則のコピーと貼り付け

Excelの入力規則は、便利ですね。ですが、この入力規則はコピー・貼り付けができるということは案外知られていないようです。 データの入力規則 ...

続きを見る

無効データのマークを使って入力規則以外のセルをチェック

[データの入力規則]の中に[無効データのマーク]というのがあります。 これは入力規則に設定した以外の値のセルに赤丸を付けてくれる便利な機能で ...

続きを見る

重複データの入力を禁止するには

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

続きを見る

行と列の交差するセルの値を求める

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

続きを見る

参考Officeのバージョンによって操作が異なる場合があります。以下の記事で、お使いのOfficeのバージョンを確認する方法を解説しています。

Office(Word、Excel、PowerPointなど)のバージョンを確認する方法

Microsoft Officeも時を得て、大きく様変わりしてきました。トラブルを解決しようとするとき、あるいは、何かの操作を探すときなど、 ...

続きを見る

あなたへのおすすめ

関連記事

トリミング後の画像は圧縮して保存したほうがいい

Office2013だけではないのですが、画像のトリミングをした後は、その画像を圧縮してトリミング部分を削除して保存することをお勧めします。 ...

ビューの切り替えができない

Access2013に限ったことではありませんが、ビューの切り替えができるフォームと出来ないフォームがあるんですが・・ との質問を受けること ...

削除できない名前がある

Excelのワークシートで使用していない名前があるので、削除したいと思い[数式]タブにある[名前の管理]をクリックして、[名前の管理]ダイア ...

コマンドボタンのスタイル変更

Access2013のフォーム(デザインビュー)でコマンドボタンを挿入すると、既定では下のようなデザインとなります。このコマンドボタンのスタ ...

タイムラインの挿入

Excel2013の新機能です。タイムラインを使うと、日付のフィルター処理を対話式に実行することができます。 まず、Excel2013でピボ ...

-Windows8 & Office2013 Excel2013

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

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