エクセル、放物線的なデータを関数で

質問

乗り物の速度におけるある効果（"A値"とします）を、速度を入力すると自動的にその効果（A値）を計算できる表を作ろうとしています。
（※画像の図の数値は手入力です。A列に速度を入れたら、B列のセルに自動で数値を反映したい）

ただ図のように、A値は速度に対して正比例ではなく、放物線を描く推移になります。
ですので単純に「=セル番地*掛け率」では計算できず、速度の数値に応じて掛け率を変動させないといけません。
図のような数行なら手入力で計算すればよいのですが、実際は数百行もあるため、ひとつの関数式を作り、オートフィルで複製することが前提です。
また実際は、速度記入は図のように直線的推移ではなく、比較的ランダムな数字になるので、それに対応できないといけません。

このような場合、どのような関数を使えば良いでしょうか？
アドバイスをいただけますでしょうか。よろしくお願いします。

回答No.3

keithin · ベストアンサー

何やらミスリードされているように見えますが、

＞A値は速度に対して正比例ではなく、放物線を描く推移になります

Ａ値 = 基本的に速度から直接計算して求まる
のでしょうか。

それとも
Ａ値 = 速度から別に求めた「ファクタ」を使って何か別の計算をする
のですか？

添付図：
Ａ列とＢ列に元データとしての時速とＡ値があります。

＞速度記入は…比較的ランダムな数字になる

こういう時は、グラフの種類としては「散布図」を使用します。横軸に時速、縦軸にＡ値です。
散布図のプロット（添付図では青いマーカ）を右クリック、「近似曲線の追加」を行い「多項式近似　２次」を選択すると、指定のマーカをもっともよく通る２次曲線が描けます。基本的にはこの数式を用いて、速度から直接Ａ値を計算する事を考えます。

なお、近似曲線の追加を行うと「近似方程式」をグラフに描かせる事ができます。ただし、この数式を直接目視して計算に利用しては「絶対にいけません」。正しい計算結果が得られません。

手順：
Ａ１１セルに「Ａ値を計算したい時速」を記入する
Ｂ１１セルに
=TREND(B2:B6,A2:A6^{1,2},A11^{1,2})
と記入し、二次の近似値を求める。

お礼

ありがとうございます。
図のような放物カーブの変化度合を持つデータなので、
「Ａ値 = 速度から別に求めた「ファクタ」を使って何か別の計算をする」
になりますね。
提示していただいた手順をコピーさせていただき（既知のY、Xは$をつけて）オートフィルし、完璧に実現することができました！
TREND関数というのは今まで使ったことがなく、各エレメントの機能をよく調べて、応用していきたいと思います。
本当に助かりました、ありがとうございました。

回答No.4

MackyNo1

ご希望の処理をするには散布図グラフの近似曲線の機能を利用します。

エクセルのバージョンが記載されていないので、2007の例で説明すると、データ範囲を選択して挿入タブからグラフの「散布図」をクリックして散布図を作成し、グラフ上のプロットの上で右クリックし「近似曲線の追加」から「多項式近似」を選択して次数を「2」にし「グラフに数式を表示する」にチェックを入れます。
今度は近似式の上で右クリックし「近似曲線ラベルの書式設定」で表示形式を「数値」にして小数点以下の桁数を「１０」桁にします。
例示のデータだとこの数式は「y = 0.0004071429 x2 - 0.0463571429 x + 1.9542857143
」のようになっていますので、これを以下の数式にすれば、添付画像のようにA2セルに「速度」を入力すればC2セルに「掛け率」を計算することができます（添付画像は、見やすくするために軸の最小値を0.5に変更したり、近似曲線の前方および後方補外を入れています）。

=0.0004071429*A2^2-0.0463571429*A2+1.9542857143

回答No.2

shintaro-2

＞このような場合、どのような関数を使えば良いでしょうか？

速度をＶとすると
Ａ＝ｋＶと表せますから（ｋは定数）

ＶをＡ列、Ｖに対応する速度をＢ列に入力して

普通の人は、
　Ａを計算するために、Ｃ列に　=a2*b2　という式を入力しておくと思います。

お礼

ありがとうございます。
なるほど、C列も使って計算させるのですね。
ただ、そこからどうしたらよいかわかりませんでした。

回答No.1

中京区 桑原町（@l4330）

電卓で計算するとしてその効果「Ａ」はどのように計算するのですか？
掛け率をどのように変動するのか書いてもらえれば、関数を作れると思います

お礼

すみません、No.3の方の回答で問題解決することができましたので、締め切らせていただきました。
最初の書き込みをしていただき、ありがとうございました。

>MackyNo1さん
締め切りがNo.4のMackyNo1さんの書き込みとすれ違いになってしまいました。
お礼を書き込もうとしましたがなぜか書き込めなかったので、申し訳ないのですがl4330様のお礼欄をお借りしてお礼申し上げます。
無事解決いたしましたので、ご報告申し上げます。

補足

ありがとうござます。
掛け率は図のとおりで、以下のようになります。

速度　掛け率
40　　0.75
50　　0.66
60　　0.63
70　　0.71
80　　0.85

ただし実際は速度1キロごとに間の掛け率が存在します。
おおむね上記のような変動カーブになりますので、だいたいこのような変動率になればOKだす。
よろしくお願いします。