エクセルの便利な関数:INDEX関数の使い方

こんにちは、市民パソコン教室北九州小倉校の篠原です。


今回から、数回に分けて、連携して使うことで威力を発揮するEXCEL関数を紹介します。

便利な関数

以下の関数と機能を使いこなせれば、今までと違ったEXCELの世界が広がるのではないかと思います。
ちょっと複雑な入力表なんかがVBAを使わずに出来てしまいますよ。

  1. INDEX関数
  2. MATCH関数
  3. COUNTA関数
  4. INDIRECT関数
  5. OFFSET関数
  6. 名前定義 Part1
  7. 名前定義 Part2
  8. データの入力規則

INDEX関数

 INDEX関数は、行位置と列位置を指定することで、該当するセル位置の参照や配列を返す関数です。


 INDEX(セル範囲,行番号,列番号,領域番号)

 INDEX(配列定数またはセル範囲,行番号,列番号)

 セル範囲:A1:D10など、セル範囲を指定する
      複数個指定することも可能
 配列定数:{1,2,3,4,5;6,7,8,9,10}(この例は2行5列の配列定数)
 行番号 :セル範囲を指定した場合は、必須。
      配列の中にあり、値を返す行を数値で指定します。
      列の配列を受け取りたい場合は、「0」を指定する。
 列番号 :配列の中にあり、値を返す列を数値で指定します。
      行の配列を受け取りたい場合は、「0」を指定する。


 このINDEX関数は、引数の指定の仕方により、戻り値がセル参照になったり、配列なったりする優れものの関数です。



INDEX関数の使い方 その1(ピンポイントでセルを指定する)

  • 1-1 INDEX関数使い方の例1
    INDEX関数:ピンポイントでセルを指定する その1

  1. 計算式

     =INDEX(B2:F6,2,3)



    この場合 セル範囲内の2行3列目の値を取得できる(結果「8」となる)。



  • 1-2 INDEX関数使い方の例2
    INDEX関数:ピンポイントでセルを指定する その2

  1. 計算式

     =INDEX((B2:F2,C4:D5),2,2,2)
      セル範囲が複数ある場合は、その範囲の指定をカッコでくくります。



     この場合 セル範囲領域2(C4:D5)範囲内の2行2列目の値を取得する(結果「18」となる)。


    以上は、ピンポイントでセルを参照する計算式を見ました。


    次は、行や列単位(配列)で結果を取得する計算式の使い方です。



INDEX関数の使い方 その2(配列を取得する)

  • 2-1 INDEX関数使い方の例3
    INDEX関数:ピンポイントでセルを指定する その3


    下図は計算式の結果
    INDEX関数:ピンポイントでセルを指定する その3の結果

  1. 計算式

     =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列は指定したセル範囲内に入っていないため)ことを示しています。


  1. 計算式
  • 2-2 INDEX関数使い方の例4
    INDEX関数:ピンポイントでセルを指定する その4
    下図は計算式の結果
    INDEX関数:ピンポイントでセルを指定する その4の結果

     =INDEX((B2:F3,C4:E6),0,2,2) → 入力確定時に「Ctrl」+「Shift」+「ENTER」キーを押す



     この式では、複数の領域の内(赤枠、青枠)、指定した領域(2番目の領域:青枠)から、その2列目の配列(緑枠)を取得できます。



INDEX関数は参照を返す

  • 3-1 INDEX関数使い方の例5
    INDEX関数:ピンポイントでセルを指定する その5
    下図は計算式の結果
    INDEX関数:ピンポイントでセルを指定する その5の結果

  1. 計算式

     =SUM(B2:INDEX(B3:B6,4,1))



     このSUM関数の中にあるINDEX関数がセル参照を返していることは、結果「55」からわかると思います。
     合計されているのは、B2:B6までのセル範囲で、「=SUM(B2:B6)」という計算式と同じとなっている。



INDEX関数の利用方法(ちょっと複雑)

  • 4-1 INDEX関数使い方の例6
    INDEX関数:ピンポイントでセルを指定する その6
    下図は計算式の結果
    INDEX関数:ピンポイントでセルを指定する その6の結果

  1. 計算式

     =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関数 配列数式

      コメント


      認証コード4194

      コメントは管理者の承認後に表示されます。