EXCEL AVEREGE関数について

EXCEL　表の上部に行を追加すると、AVEREGE関数の範囲がずれて正確な値が求められない。

No.4 ベストアンサー 回答者： enunokokoro 回答日時： 2023/08/13 13:46

No.2の回答者です。

回答したものは行追加した状態の計算式でした。
正確には
　=AVERAGE(OFFSET(B2,1,0,B12))
ですね。

No.9 回答者： chonami 回答日時： 2023/08/14 10:20

範囲を下に多めにとっておいて、新しい日付は一旦一番下に入れて日付を降順で並べ替えてはどうでしょうか？

No.8 回答者： goomania 回答日時： 2023/08/14 01:04

ご質問のようなケースでは、AVERAGEIF関数を使う方法で解決できます。

ご質問者は3行目に新しい行を追加して、そこに最新日付のデータを入力していくという方法でデータを更新し、その状態で最新の平均値を求めたいというご希望だと思います。
掲出された画像のように、平均値を表示するのがB2セルでA2セルは空白だとすると、B2セルに

=AVERAGEIF(A1:A12,"<>",B1:B12)

という数式を記述することで、A3の上に行を追加すると自動的に計算範囲が拡大されてご希望の平均値が算出されます。

また、日付範囲を区切って計算したいとすると、AVERAGEIFS関数を用いてB2セルに、

=AVERAGEIFS($B:$B,$A:$A,">=2023/8/1",$A:$A,"<=2023/8/31")

とすれば、月を跨いでどんどんデータを蓄積していく場合でも、8月中だけの平均を求めることができます。

No.7 回答者： ggogooID 回答日時： 2023/08/13 15:20

「＄」を入れておけば、

行や列を挿入・削除しても、
値は変わらない。

No.6 回答者： fujillin 回答日時： 2023/08/13 14:46

こんにちは

＞AVEREGE関数の範囲がずれて正確な値が求められない。
挿入・削除を行った場合に式の参照セルを調整してくれるのは、エクセルの仕様ですね。
便利な場合と、変わってくれない方が良い場合がありますが、ご質問の場合は変わらないままで、
　＞=AVERAGE(B3:B12)
となっていても困るのではないでしょうか？
B12だけB13になって欲しいのではないかと想像します。

セル範囲を指定するのに、スタート位置はアドレス固定で、エンドの位置は操作に応じて変化して欲しいということではないかと解釈しました。
範囲の終わりは、ご提示の状態でB12で良いものとするなら・・
　=AVERAGE(OFFSET($B$1,2,0,ROW(B12)-2))
のような方法でも可能と思います。

※ 開始位置は固定のB$1を基準にして、範囲は可変になるような設定をしています。

No.5 回答者： 銀鱗 回答日時： 2023/08/13 14:17

まあ、仕様だから仕方ないですよ。

どうしてもそこに「行挿入」するのであれば、
範囲を文字列で指定するようにしましょう。
　＝AVERAGE(B3:B12)
　↓
　＝AVERAGE(INDIRECT("B3:B12"))
ただし追加された行により下のデータは押し出されて集計外になる。
ですので、
　＝AVERAGE(INDIRECT("B3:B1048576"))
これでOK。

　※ B1048576 はシート最下のセル

・・・

同様のことがA11セルに
　＝AVERAGE(A1:A10)
と入力されているところに、
11行目を挿入すると起きる。
（AVERAGE(A1:A10)のままで11行目を集計の範囲に含まない）

No.3 回答者： ssawatake 回答日時： 2023/08/13 13:44

一体どういう操作してます？？？

普通に3行目にカーソル当てて、右クリックで挿入。

これで自動的に範囲はB3:B13になるけど・・・・。

No.2 回答者： enunokokoro 回答日時： 2023/08/13 13:39

質問にある平均を求める一番下のセルが12行目で開始するのなら、

以下のようにOFFSET関数で処理できます。
　=AVERAGE(OFFSET(B2,1,0,B13))
https://www.tipsfound.com/excel/04offset-excel

No.1 回答者： mimazoku_3 回答日時： 2023/08/13 13:30

３の行をコピーして、４の行に挿入する。

次に、３の行を修正すればいいと思います。