EXCELで予測値を導き出す方法

質問

みなさま、よろしくお願いします。

A列1～9行に
50
70
30
60
10
90
50
60
20

という数字があるとします。
(列はアルファベット、行は数字)

現在A列10行は数字が出てないので空白です。

B列10行目に以下のような式を入れます。
=AVERAGE(A1:A9;C10)

次にC列10行目に48を入れますと、48.8になります。
次に48を49に変えますと、48.9となります。

つまり、48だと=AVERAGE(A1:A9;C10)を超えませんが、
49だと=AVERAGE(A1:A9;C10)をはじめて超えます。

現在のやり方だと、少しずつ数字を増減させて数字を導き出しているのですが、
このはじめて超える数字、この場合だと49を一発で出す関数式はどのようにすればいいのでしょうか？

現在、A列は9行目までですが、A列に数字が増えていけば、
=AVERAGE(A1:A10;C11)、=AVERAGE(A1:A11;C12)と増えていきますので、
OFFSETにできるのでしたら、どのようになるのかもあわせてお願いしたいと思います。
どうかよろしくお願いします。

回答No.2

zap35 · ベストアンサー

=AVERAGE(A1:A9;C10)
のように平均を求める範囲を「;（セミコロン）」で区切るとエラーになるはずです。「,（カンマ）」のことでしょうか

それはさておき、質問の趣旨は
・A1:A9の平均値である48.88889を越える整数値をC10に求めたい
・A列のデータが増えたら、求める数値の表示セルをA列の最終行の１行下にしたい
ということでしょうか。それであれば、AVERAGE関数でC10を計算対象にする必要はありません。

C10セルに以下の式を貼り付けて、下方向にコピーしてみてください
（C10には49が表示され、A列にデータを追加すると表示セルも変わります）
　=IF(AND(A10="",A9<>""),ROUNDUP(AVERAGE(A$1:A9),0),"")

お礼

端的でポイントをとらえた回答で非常にわかりやすかったです。
また、式の間違いを考慮しての回答していただき感謝します。
ご回答ありがとうございました。

回答No.3

Nouble

まず最初に
平均値の近似値の値を、平均をとる対象に加えても減らしても
平均値に大きくは変動を与えません

挙げられた例でA1:A9の平均は4.8888…ですが
c10に4.8888…を入れても平均は変わらないのと概ね同じことですね

C10に入力する最終的な値は
「A1:A9の平均を上回る最小の自然数」
だと思いますので
つまりは近似値
平均値に影響を落とさないと考えていいと思います。

ですのでこの際平均の対象からC10を外して
先ずA1:A9の平均を取ります。

しかしただ平均値を得ようとしただけでは
「上回った値」を得ることが出来ない場合があるので
「上回った値」を得るにはどうすればいいか
場合分けして考えてみたいと思います。

A1:A9の平均値が自然数の場合
A1:A9の平均値に最も近い自然数と
A1:A9の平均値が等しいので
C10の値はA1:A9の平均値に1を足さないと上回らない

A1:A9の平均値が自然数でない場合
端数が出ているので小数点以下を切り捨てた後
C10の値はA1:A9の平均値に1を足さないと上回らない
となりますね、
結局どちらにしろ１を足すこととなります。

ところで
自然数の小数点以下を切り捨てても
「元々ないものを便宜上だけ捨てる」
事となるので値は変わりません。

よって
=INT(AVERAGE(A1:A9))+1
とすると
A1:A9を上回る最小の自然数が得られます。

ところで、
お題では得られたこの値をも平均値に加味したいと言うことでしたが、
平均を取る以上は最低でも1つ
A列(？)に値があるものと思われます。

上記の式で得られたこの値を
平均値の対象内に加えたとしても
その変動は最大で０．５しか変わらないうえ
A列の平均値とC10の値との最大の格差は1未満で
例えば0．5加えると桁上がりして1増加するほど
A列の平均値とC10の値が近づいたとしても
両者の値が近づけば近づく程影響が減り、

結果として変動値が上記式中の付加値である１を超える事はないので、
この付加値の相殺範囲内だと言え、
「上記式に手を加える必要はない」
となると思います。

話は変わりますが、
Excelのセル指定には面白い指定法がありまして
通常はA1:A9とかいう風に指定するのですが、
Excelのバージョンによっては
A:Aと指定できます。

これはAの全てを指定したことになります、
加えてExcelの関数はこの表記に対応しており
A:Aの数値の平均を取る場合
A:Aに含まれる空白のセルは対象から自動的に省かれます。

便利で面白いですよね。

しかしこの方法はAの全てを対象にするため
少々計算が「重く」なってしまうかも知れません。

確かにOFFSET関数などを使用し
対象範囲のみを洗い出した方が良さそうに思えそうですが、

例えば、
MATCH関数でA列(行？)中の数値でない
最も行番号（列？）の若いセルを捜させ、
このセルの一個手前のセルとA1間との平均値を取ったとしても、
この時のMATCH関数の検索範囲がA:Aになってしまうので
本末転倒になると思われます。

素直にA:Aと記載した方が良いかもしれませんね
(;^_^A アセアセ…

如何でしょう？
参考になれば幸いです。

お礼

質問に対して丁寧に噛み砕いた内容で非常に勉強になりました。
長文で時間もかかったと思われますので、20ptをつけたかったですが、
お二方にということがルール上できませんので10ptとなってしまいました。
本当に申し訳ございません＾＾；
ご回答ありがとうございました。

回答No.1

shirayukix

=AVERAGE(A1:A9;C10)
まず、この数式が正しくありませんが？

お礼

ご指摘いただきありがとうございます。
誤)=AVERAGE(A1:A9;C10)
正)=AVERAGE(A1:A9,C10)
大変失礼しました。