EXCEL 選択したセルの位置を基準に数式を入力したい

質問

こんにちは
タイトルが意味不明かも知れません。説明いたします。
たとえば、あるセルを対象として（たとえば、D100とします）、「D100よりも上３０個の平均を計算し、その値をE100に表示」、ということはできますでしょうか。つまり、E100を選択し、その隣のD100から上３０個のセル、D71からD100までの平均を計算せよ、という数式となりますが、いかがでしょうか。手動で選択ではなく、数式で指令することなのですが、どうでしょうか。
宜しくお願いします。

回答No.4

imogasi · ベストアンサー

average関数はセル範囲を引数として要求します。
セル範囲は、エクセル関数では、ＯＦＦＳＥＴ関数しかありません（あまり選択の余地はない。普通は関数は、１つの値！を結果として返すのですが、この関数だけは異色と思います。）
例
Ａ１：Ａ１０
1
2
3
4
5
6
7
8
9
10
Ａ１０から上に５行の平均
=AVERAGE(OFFSET(A10,-4,0,5,1))
隔たりなので５－１の４、且つ上にさかのぼるときはマイナスをつけることが出来る、ので上記の式になる。
Ａ１０（第１引数）から上に（第２引数のマイナス）５行の１０－４（第２引数）＝第６行から５行（第４引数。１列＝第５引数）のセルの数値の平均と言う意味になる。

回答No.6

turuzou

こんな使用法は如何でしょうか？
例えばJ1へ E100 と入力
結果を表示したいセルへ　=AVERAGE(OFFSET(INDIRECT(J1),,-1,-30,1))

回答No.5

imogasi

#4です。小細工して（＃４のデータ例で）
=AVERAGE(INDIRECT("A10:A"&ROW(A10)-4))
デモできました。４は５－１から来ます。
１０番から５つ前は１０－５＋１（小学校で習う式）。

回答No.3

noname#204879

単に =AVERAGE(D71:D100) で駄目ならその理由が知りたいです。

回答No.2

turuzou

こんな感じでしょうか？
E列の何処かへ　=SUM(OFFSET(INDIRECT("E"&ROW()),,-1,-30,1))/30
例えば、E100へ上記の式を使用した場合、D71:D100の平均値を表示します。
Excelのヘルプで、OFFSET関数や、INDIRECT関数を確認して下さい。
使用例としてF列に1が入力されていれば、D列のその行を含む上へ30のセルの平均値を表示させ、F列に1が入力されていなければ、空を表示させるには。
=IF(INDIRECT("F"&ROW())=1,SUM(OFFSET(INDIRECT("E"&ROW()),,-1,-30,1))/30,"")

回答No.1

Cupper

OFFSET関数を使ってはいかがですか
ただし、
＞D100よりも上３０個
と表現していますが
＞D71からD100までの
と仰っていることから
「D100から上３０個」の誤りと勝手に判断させていただきます

　=OFFSET(基準,行数,列数 ,高さ,幅）
と指定します（"高さ"と"幅"は省略可能）
　OFFSET(D100,-(30-1),0,30,1)
など（応用が利くように "30" という数字を残しています）
これは高さと幅を指定しているため範囲を指定する表記になっていますので、
関数式にそのままイコールを付けるとエラーになります

平均であればこの関数式を AVERAGE関数に渡します
　=AVERAGE(OFFSET(D100,-(30-1),0,30,1))

また、INDIRECT関数を使っても同様なことが可能です
ヘルプなどを参照して自力で数式を作ってみてください