Excel使いこなしの基礎と応用

行と列の交差位置を求めるINDEX関数

使用関数:INDEX

INDEX関数についての基礎知識をまとめています。配列形式での範囲指定、行(列)番号を省略(0指定)する使い方、セル参照形式で複数の対象範囲から参照する方法などについて詳しく図解。

目次


2つの形式から選択する

INDEX関数は範囲検索でVLOOKUP関数が使えない時の救世主的存在です。
検索値の位置を左端列に限定するというような縛りがなく、クロス集計表のような2つの条件を持つデータも参照できます。
指定した行と列の交差セルを引き出すシンプルな仕組みですが、他の関数と組み合わせることで格段に汎用性が高まります。

スポンサーリンク

数式ライブラリの検索/行列からINDEXを選択すると、
数式ライブラリの検索/行列からINDEXを選択
2つの選択肢から引数の形式を選ぶボックスが現れます。
引数の選択ダイアログ1
デフォルトの選択肢「配列、行番号、列番号」は「配列形式」、もう一つの「参照、行番号、列番号、領域番号」の方は「セル参照形式」といいます。

まずは、配列形式で第1~3引数を指定してみましょう

配列形式で数式を組み立てる

「引数の選択」で配列形式を選択し、ダイアログを開きます。
詳細は後述しますが、「行番号」「列番号」のどちらかの引数を省略する使い方もあり、とてもコンパクトな構成です。
配列形式の引数

引数1「配列」

最初の引数「配列」では検索する対象の範囲を指定します。
(1)別表を用意して、そのデータ範囲を指定する
下図は出荷個数と配送区域を基にした送料の一覧表です。表はテーブル化してあり、「送料リスト」というテーブル名が付いています。この表からINDEX関数で値を取り出すために、第1引数「配列」に構造化参照でテーブル名を指定します。
▶データ表にはテーブルを使うべき!その理由と設定方法
※ テーブル化していない表では「$A$2:$C$4」と指定
引数「配列」にテーブル名を指定
(2)データ数が少なく、表がない場合、配列定数を直に指定する
データ数が多い時は現実的な方法とは言えませんが、3行3列の配列を{30,500,600;50,550,650;80,700,800}と直接指定できます。別表を用意しなくてもいいので、ケースによっては効率的。
配列全体を波括弧「{}」で囲み、1行目のデータをカンマ「,」で区切って入力、2行目に移る時はセミコロン「;」を打ちます。
配列定数を直に入力

引数2「行番号」と引数3「列番号」

行番号」と「列番号」は、それぞれ「配列」で指定した対象範囲の何行目・何列目を検索するか指定します。
行番号と列番号を指定
INDEX関数で送料が表示された

MATCH関数と組み合わせて使う時は、たいがい、この2つの引数に入れて数式を組み立てます。

「行番号」「列番号」のどちらかを0(または空白)指定する

「行番号」と「列番号」の、どちらか一方に「」を指定するか、省略(空白)すると、指定された行(または列)に属する全データ範囲を取得できます。

スポンサーリンク

「テスト成績表」というテーブル名の付いた表から、「氏名」と「判定」のデータだけ別表に転記したいと思います。
「テスト成績表」というテーブル名の付いた表から、「氏名」と「判定」のデータだけ別表に転記
転記する1行目のセルを選択してINDEX関数を呼び出し、「配列形式」を選択します。ダイアログの「配列」にテーブル名を指定し、「行番号」を「0」、「列番号」を「1」と入力します。「行番号」を空白のままにしてもOK。
行番号を0に指定
数式の入ったセルに最初の氏名が転記されました。
数式は「=INDEX(テスト成績表,0,1)」または、「=INDEX(テスト成績表,,1)
氏名のフィールドが転記された
このセルを人数分まで書式なしのオートフィルコピーします。
書式なしフィルコピーで全ての氏名が転記された
「判定」にも「=INDEX(テスト成績表,0,8)」または、「=INDEX(テスト成績表,,8)」と入れて、同様に転記。
判定のフィールドも転記

セル参照形式で複数の表から参照する方法

セル参照形式を使うのは、主に複数の対象範囲を指定する場合です。

下図は2つのテーブル化した料金表です。上がテーブル名「ピザ」、下がテーブル名「ドリンク」。この両方のテーブルを第1引数の「参照」に指定して、注文に応じた金額を取り出して表示させます。
2つの範囲(テーブル)から金額を求める
「引数の選択」でセル参照形式を選択します。
「引数の選択」でセル参照形式を選択

引数1「参照」に2つのテーブルを指定

「参照」に複数の範囲指定をする時は、それぞれの範囲をカンマ「,」で区切り、全体を括弧「()」でくくります。「(ピザ,ドリンク)」と入りました。

※ テーブル化していない表では ($B$2:$D$4,$B$7:$D$9)と指定

「参照」に複数の範囲指定

引数「行番号」と「列番号」は前述した配列形式と同じです。「照り焼きチキン」のある行番号「2」と「L」のある列番号「4」を指定すればいいわけですね。
行番号と列番号を指定

引数4「領域番号」

領域番号」は第1引数「参照」で指定した複数の範囲に付く番号です。
括弧で括った最初の指定範囲が「領域番号1」になり、後は順に「領域番号2」~と振られます。
最初の指定範囲が領域番号1、次が2
最終的なダイアログ。
セル参照形式の最終的なダイアログ
数式は「=INDEX((ピザ,ドリンク),2,4,1)」。
「行番号」「列番号」「領域番号」を注文に応じて変えていけば、その金額が表示されることになります。
「行番号」「列番号」「領域番号」を変えて金額の値を入れ替え

まとめ

  • INDEX関数は指定した行と列の交差位置を求める
  • INDEX関数は「配列形式」と「セル参照形式」を選択して数式を組み立てる
  • 配列形式では、対象範囲を、{1,2,3;4,5,6;7,8,9}のように、配列定数で指定できる。作表しなくても値が取り出せる。
  • 「行番号」と「列番号」の内、どちらかを省略することができる。その場合、空白のままにするか、「0」を指定する。番号を指定した行か列の全データ範囲が返される
  • 複数の範囲を参照する場合は「セル参照形式」を選択。


高機能の表計算ソフトExcelを活用すれば、仕事や私生活で、今あなたが必要とするいろいろなモノが作れます。その作成手順とデザイン、アイデアのヒントまでを可能な限り詳細にわかりやすくナビします。基本操作や関数、グラフに関する解説記事とともにご利用下さい。