グラフ曲線で、y値を指定するとx値がわかる関数

質問

意味のわかりにくいタイトルで申し訳ありません。

例えば、
　　A　　B　　C
1　　x　　y1　　　y2　　　y3...
2　0.0　　0　　　0
3　0.2　　5　　　20
4　0.4　　20　　80
5　0.6　　80　　95
6　0.8　　95　　99
7　1.0　　100　　100
というグラフ曲線があり、B列 y1=50のxを知りたいとします。
そのとき、TREND($A4:$A5,B4:B5,50)とすれば良いです。(答えはx=0.5)
しかし、今度はC列 y2=50のxを知りたいとすると、
TREND($A4:$A5,B4:B5,50)をひとつ右のセルにコピーしてTREND($A4:$A5,C4:C5,50)、
行の範囲をシフトさせTREND($A3:$A4,C3:C4,50)とすればよいです(答えはx=0.3)が、
いちいち関数の範囲を行方向にシフトしなければならず、面倒です。
(TREND($A4:$A5,C4:C5,50)⇒TREND($A3:$A4,C3:C4,50)のように、
範囲を4-5行目⇒3-4行目に変える必要があります)
そこで、行方向の範囲を変えなくてもいいような方法はないでしょうか？
y曲線がy1,y2,y3,y4...とあり、y=50の時のx(x1,x2,x3,x4...)を知りたいです。
同じような操作を1000回くらいやりたいのですが、さすがに時間がかかってしまい、困っています。

申し訳ありませんが、どなたか教えていただければ幸いです。

※同様の質問を以前投稿したのですが、Okwaveのネチケットを失念してしまい、誤って回答を締め切ってしまいました。。大変申し訳ありませんがお願いしたいです。

回答No.2

ASIMOV

作業列を使います
仮にＥ列とします（変更可能です）
E2 =2　とし、下にCtrlキーを押しながらフィルします（E3=3,E4=4...）
B8 =50
B9 =VLOOKUP(B8,B2:$E7,6-COLUMN())
　：作業列がＥ列以外の場合
　：１．"$E7"を変更
　：２．"6"はＥ列が５列目＋１という事なのでＺ列なら２６＋１＝２７になります
B10 =CHAR(64+COLUMN())
B11 =B10&B9&":"&B10&B9+1
B12 ="A"&B9&":A"&B9+1
B13 =TREND(INDIRECT(B12),INDIRECT(B11),B8)
以上、B8～B13をＣ列以降にコピーします

ちょっと野暮ったいやり方ですが、一応出来ると思います

回答No.1

grumpy_the_dwarf

trend関数は通常、多数のxyペアを含む範囲を指定して最小二乗法で
算出した回帰直線を使うものなので、質問はかなり変則的なことをし
ています。データの点を直接結ぶ折れ線グラフで傾向を語ってはいけ
ないってのは、中学校の理科で教わるはずですが。

ところで質問のデータをみると、excelの回帰で扱える類の式では、
ちゃんとマッチしないことが予想できます。正規累積分布っぽい感じ
ですね。こういうパターンで確率0.5の点を求める話っていうと、
LD50などの推定ですか。マジメにやるならキーワードは「プロビット
法」になります。検索すればexcelでやるためのマクロやアドインが
見つかるでしょう。

ざっくりやるなら、プロビット変換した値に対して最小二乗法で回帰
直線を算出し、y=0の点を探します。ここでtrend関数が使えますね。
具体的には、D3: =normsinv(B3/100)と確率が0や1でない部分につい
てプロビット変換し、D8: =trend($A$3:$A$6,D3:D6,0)でオッケー。
そのままE列にコピーすれば、y2についても計算できてます。0.317。