データテーブルから、番号を指定してデータを参照する


データが規則正しく並んだ表(テーブル)から、座標を指定してデータをピックアップします。LOOKUP関数で検索するのでなく、座標が分かっていればINDEX関数を使います。

=INDEX(範囲,行,列)

上の例は、B2からJ7までの9列×6行の表から、列座標と行座標を指定してデータを参照する例です。テーブルの9列・3行には「A」が入っています。

何番目か分かっている(計算できる)場合は、INDEX関数の方が便利です。

使い方の例 自動車税を求める

J11には、1本にまとめた式を入れてありますが、B12とE12で座標を計算し、これを使って式を作れば短くなります。B12・E12は見るものではないので、関係ない場所に置いておきます。
J12の式は、=INDEX(範囲,E12,B12)と簡潔になります。無論、テーブルは、式を置くシートが異なるシートに置くこともできます。

エクセルファイルの例  ここをクリックすると、実際のエクセルファイルが開きます。(サイズ14KB)
  (ファイルはエクセル2000形式なので、97以降のエクセルがないと開けません)

MATCH関数 + INDIRECT関数 + ADDRESS関数で、表データを検索する

源泉税の税率表のように、式に組みにくい数値表は、そのままMATCH関数で検索できます。下の表は、賞与の税率表ですが、前月の所得と扶養家族数の2つの条件から税率が決まります。扶養家族数によって検索する列が決まり、その列の中でMATCH関数で検索オプションを1にして、超えない最大値の位置をみつけ、INDEX関数でA列の税率を読み取ります。
C23に扶養家族数があり、この数字+2列を3行から20行まで範囲指定します。
これを実現する関数が、=ADDRESS(3,huyou+2)&":"&ADDRESS(20,huyou+2) です。huyouは、C23のセルの名前です。A25にこの式を入れました。kyuuyoは、B23のセルの名前です。
この範囲を、MATCH関数で検索します。=MATCH(kyuuyo,INDIRECT(A25),1) これをA26に入れました。その結果をINDEX関数で、税率の数値のある範囲(A3:A20ですが、zeirituという名前を付けました)から読み取ります。それが、=INDEX(zeiritu,A26,1) です。
A25 A26 A27と分けて式を入れましたが、一つにまとめれば
=INDEX(zeiritu,MATCH(kyuuyo,INDIRECT(ADDRESS(3,huyou+2)&":"&ADDRESS(20,huyou+2)),1),1) となります。

実際のエクセルファイルは、ここをクリックしてダウンロードして下さい。

表紙へ