INDIRECT関数で簡単に参照範囲を換える

先に紹介したADDRESS関数とINDIRECT関数を組み合わせると、簡単に参照範囲を移動することができます。
下の表は、テストの成績表です。
現在11回目まで実施されたことになっていますが、4人のメンバーは参加したりしなかったりしています。
この表に基づいて、特定のスペースで選択された個人の成績を取り出す方法を紹介します。
●数式と構成 ●サンプルファイルと参考記事

送信者 OpenOffice

いずれの項目も、下の構成で数式を組み立てることができます。

MATCH関数で列を特定
ADDRESS関数に当てはめて文字列のセル番号を生成
INDIRECT関数にセル番号を当てはめて数式内で使用できるようする
上の処理を、起点と終点に対して行なう
目的の関数に当てはめる

テストに何回参加したかを表示する

●起点を設定
MATCH関数は、=MATCH(検索値;範囲;タイプ)で、検索値が範囲の中で何番目のセルにあるかを返すのでここではMATCH関数を利用して、表示したい名前が何列目にあるかを調べます。
上の例では、G2セルに井上の名前が表示されています。名前は、A1からE1のセルに表示されているので、
=MATCH(G2;A1:E1;0)　で、2が返されます。
ここで、範囲をB1:E1にすると、1が返され、ADDRESS関数、INDIRECT関数と組み合わせたときに正しく参照されないことになるのでA1:E1としなければいけません。

MATCH関数で返された値をADDRESS関数に当てはめてセル番号を生成します。
ADDRESS関数は、=ADDRESS(行番号;列番号;種類;A1;シート名)で、セル番号を生成します。ここでは、種類、A1,シート名を省略できるので、行番号と列番号だけを確定してADDRESS関数に当てはめます。
起点の行は、2行目から始まるので行番号は2とします。起点の列番号は、MATCH関数で得た値です。
=ADDRESS(2;MATCH(G2;A1:E1;0)) これで＄B$2が生成されます。

ADDRESS関数で得た文字列としてのセル番号ををそのままINDIRECT関数に当てはめると、数式で使用できるセル番号になるので、
=INDIRECT(ADDRESS(2;MATCH(G2;A1:E1;0)))　これで、G2セルの名前の人物の1回目のテストの点数が返されます。ここでは、空白なので0。

●終点を設定
基本的には、起点を設定するのと同じですが、何行目までを対象にするかについて、少し工夫が必要です。
ここでは、A列にテストが第何回まで実施されたかが表示されていますので、A列のなかで空白でないセルの数を数えることによって範囲の終点を設定します。
空白でないセルの数を数える関数は、COUNTA関数があります。
今後、テストが何回目まで実施されるか分かりませんが、1000回まで実施されても問題ないようにここでは=COUNTA(A1:A1000)とします。これで、12が返されます。

COUNTA関数で得られた値を、ADDRESS関数に当てはめます。
列番号は、起点の設定と同じ値を当てはめますので、
=ADDRESS(COUNTA(A1:A1000);MATCH(G2;A1:E1;0)) これで＄B$12が返されます。
ADDRESS関数で得られた値をそのままINDIRECT関数に当てはめると、=INDIRECT(ADDRESS(COUNTA(A1:A1000);MATCH(G2;A1:E1;0))) これでG2セルの名前の人物の11回目のテストの点数が返されます。ここでは、10。

これで起点と終点が設定できたので、それぞれの値をCOUNT関数に当てはめれば、範囲の中で、数字が入力されているセルの個数が返されます。それがそのまま参加回数になります。=COUNT(INDIRECT(ADDRESS(2;MATCH(G2;A1:E1;0))):INDIRECT(ADDRESS(COUNTA(A1:A1000);MATCH(G2;A1:E1;0)))) これでG2セルの人物のテスト参加回数が返されます。ここでは7。

今までの合計点を集計する

参加回数を数えるのとまったく同じ数式の構成で、変わるのはアタマをCOUNTからSUMに変えるだけです。

今までの最大得点を集計する

参加回数を数えるのとまったく同じ数式の構成で、変わるのはアタマをCOUNTからMAXに変えるだけです。

今までの最小得点を集計する

参加回数を数えるのとまったく同じ数式の構成で、変わるのはアタマをCOUNTからMINに変えるだけです。

如何にも長くて複雑な数式のようですが、その構成を一つ一つ分解して理解するようにすれば、案外シンプルにできていることがわかってもらえると思います。

INDIRECT関数の注意：
関数名：INDIRECT関数
関数名：=INDIRECT(参照範囲)
何ができる：文字列として入力されたセル番号や範囲を計算式で利用できるようにする
平たく書くと：=INDIRECT(A3)でA3セルを参照範囲として計算式の中で利用できるようにします。
INDIRECT関数では、空白のセルを参照したとき0を返してしまいます。これを回避したいときの方法をこちらに書きました。ご覧ください。

サンプルファイルと参考記事：
INDIRECT関数で簡単に参照範囲を換えるサンプルファイルは、SKYDRIVEからcalc084.odsをダウンロードしてください。
OpenOfficeで範囲をもらさずに集計するには
集計範囲をもらさない、もうひとつの方法：ADDRESS関数を使う