エクセルの便利な関数:INDEX関数の使い方
こんにちは、 篠原です。
今回から、数回に分けて、連携して使うことで威力を発揮するEXCEL関数を紹介します。
便利な関数
以下の関数と機能を使いこなせれば、今までと違ったEXCELの世界が広がるのではないかと思います。
ちょっと複雑な入力表なんかがVBAを使わずに出来てしまいますよ。
INDEX関数
INDEX関数は、行位置と列位置を指定することで、該当するセル位置の参照や配列を返す関数です。
INDEX(セル範囲,行番号,列番号,領域番号)
INDEX(配列定数またはセル範囲,行番号,列番号)
セル範囲:A1:D10など、セル範囲を指定する
複数個指定することも可能
配列定数:{1,2,3,4,5;6,7,8,9,10}(この例は2行5列の配列定数)
行番号 :セル範囲を指定した場合は、必須。
配列の中にあり、値を返す行を数値で指定します。
列の配列を受け取りたい場合は、「0」を指定する。
列番号 :配列の中にあり、値を返す列を数値で指定します。
行の配列を受け取りたい場合は、「0」を指定する。
このINDEX関数は、引数の指定の仕方により、戻り値がセル参照になったり、配列なったりする優れものの関数です。
INDEX関数の使い方 その1(ピンポイントでセルを指定する)
- 計算式
=INDEX(B2:F6,2,3)
この場合 セル範囲内の2行3列目の値を取得できる(結果「8」となる)。
- 計算式
=INDEX((B2:F2,C4:D5),2,2,2)
セル範囲が複数ある場合は、その範囲の指定をカッコでくくります。
この場合 セル範囲領域2(C4:D5)範囲内の2行2列目の値を取得する(結果「18」となる)。
以上は、ピンポイントでセルを参照する計算式を見ました。
次は、行や列単位(配列)で結果を取得する計算式の使い方です。
INDEX関数の使い方 その2(配列を取得する)
- 計算式
=INDEX(B2:F6,2,) → 入力確定時に「Ctrl」+「Shift」+「ENTER」キーを押す
数式の入力時には、複数のセル範囲を選択しておきます(ここ重要です)。今回の例では、B9:G9までを選択している。
数式バーの表示は、{=INDEX(B2:F6,2,)}のようになっています。これは配列数式が入力されていることを示しています。
列番号を省略または「0」を指定することで、セル範囲(B2:F6)内の2行目の配列を取得できる(結果「6,7,8,9,10,#NA」となる)(この数式例で、列の配列を取得したい場合は、行番号を省略または「0」を指定すればよい)。
ここで#NAとなっている部分は、セル範囲内に該当するデータがなかった(G列は指定したセル範囲内に入っていないため)ことを示しています。
- 計算式
- 2-2 INDEX関数使い方の例4
下図は計算式の結果
=INDEX((B2:F3,C4:E6),0,2,2) → 入力確定時に「Ctrl」+「Shift」+「ENTER」キーを押す
この式では、複数の領域の内(赤枠、青枠)、指定した領域(2番目の領域:青枠)から、その2列目の配列(緑枠)を取得できます。
INDEX関数は参照を返す
- 計算式
=SUM(B2:INDEX(B3:B6,4,1))
このSUM関数の中にあるINDEX関数がセル参照を返していることは、結果「55」からわかると思います。
合計されているのは、B2:B6までのセル範囲で、「=SUM(B2:B6)」という計算式と同じとなっている。
INDEX関数の利用方法(ちょっと複雑)
- 計算式
図の関数の意味を解説しておきます。
=INDEX*1,0,2,A9) → 入力確定時に「Ctrl」+「Shift」+「ENTER」キーを押す
領域1→B2:F3
領域2→C4:E6
領域3→INDIRECT関数で文字列からその参照範囲を返す(今回は、B2:F6(名前:配列の範囲)というセル範囲の参照が戻り値になる)
INDIRECT(F9) → 「配列の範囲」という名前のセル範囲「B2:F9」を戻り値とする関数
「0」 → 列番号
「2」 → 行番号
「A9」 → 領域番号が指定されているセル(今回は「3)」
ということで、この関数の結果は、領域3(B2:F6)の範囲の2列目の配列が戻り値となり「2,7,12」となる(配列式として列方向に3個のセルが選択されているため)。
VLOOKUP関数のように、テーブルの中から一致するキーを検索することはできないけれど、あらかじめデータの並びに意味のあるテーブルを作っておけば、位置はピンポイントで指定できるし、リスト機能を使えばn行目のn列目なんていうことも簡単に指定できてしまうので、利用価値は十分にある。
また、参照範囲には、複数の範囲を式の中に指定することが出来るため、条件によって、一つの関数内で、参照先を変えてテーブルの中を参照する事も出来る。その参照範囲でさえ、「範囲の名前」機能を使えば、条件によっても変更可能です。これってとても柔軟性がありますよね。
図のようにINDEX関数を使えば、参照先を条件により柔軟に変更することが出来ます。これは、EXCELで入力フォームを作る際に強力なツールになりそうです。
以上、INDEX関数の使い方でした。
?
Tag: エクセル EXCEL INDEX関数 配列数式コメント