小数点切捨ての数列から対数近似式を求めたい

質問

数列　　A　　　B　　　C　　 D
1　　　5508　2924　2771　1428
2　　　6550　3475　3294　1692
3　　　7160　3797　3600　1847
4　　　7592　4026　3817　1957
5　　　7928　4204　3985　2042
6　　　8202　4349　4123　2112
7　　　8434　4471　4239　2170
8　　　8635　4577　4340　2221
9　　　8812　4671　4429　2266

上記4パターンの数列があり、いずれも1列目の無作為な数字に対し2列目以降が対数近似になっていると思われます。
1列目の数字を1とした場合の式をExcelで計算したところy-0.273ln(x)+0.999966までは出せたのですが、小数点以下の端数が切り捨てられているため正確な式が出せていません。
上記情報から、
(1)正確な対数近似式
(2)無作為な数字"A"の"B"列目をExcelで求める数式
の２つを教えていただけないでしょうか。
よろしくお願い申し上げます。

回答No.6

m_and_dmp · ベストアンサー

A１が初期値 5508、以下、A2～A9がある法則に従って決まるということですか？
近似式は y = 0.273ln(x) + 1.0
と貴計算と一致しました。

R2も1で、相関が良いとの結果です。

任意のxの値（1～9の範囲を超えても可、整数でなくても可）を求めるには近似式にxの値を代入し、初期値をかけます。

お礼

何度もお付き合いいただきありがとうございました。
質問の仕方が悪くお手数をおかけしましたが、内容理解できました。
どうやら４グループとも微妙に違うようなので、自分で考察進めてみます。

回答No.5

keithin

どうやら結局あなたが言いたかったことは、「１行目」に対しては論外で、「１列目」に対して対数になってるのでもありません。
添付図のように数字の１，２，３…を並べ、それに対してＡＢＣＤの４つともが対数になっている、というお話のようです。

近似値の計算方法は既に２回お話しした通りで、
Ｆ２：
=FORECAST(LN($A2),B$2:B$10,LN($A$2:$A$10))
右にコピー、下にコピーします。

なお、Ａ列からＢ列やＣ列を計算する方法は、最初の回答１でお話しした通りなので、まだ手を動かしていないなら実際にやってください。

お礼

色々と教えて頂きありがとうございました。
上記のやり方で今後考察できそうです。

回答No.4

m_and_dmp

貴殿が求めた近似式(初期値=1)は次の式なので、

y = -0.273ln(x)+0.999966

初期値を 6550 としたときの、2番目の予測値は

y = (-0.273 * ln(2) + 0.999966) * 6550 = 5310.326169

となります。実際は、貴質問の中で、6550 の次は 3475 になっていますので実際の値との差が大きいです。

先ほどの私の回答の、グラフの近似曲線の近似式から計算しますと、
y = (-0.4998 * ln(2) + 0.9704) * 6550 = 4086.971006
となり、まだまだ差が大きいです。

初期値が任意の値でももちろんかまいません。6550　をその数字にしてください。
3番目の値の予測ならxの値を3に、サンプルでは4番目の数までしかありませんが、それを超える値(x ≧ 4)でもよろしいわけで、それが予測です。

サンプル点を通る近似式なら多項式近似でかなり良く一致する式ができますが、サンプル数が４しかないデータでそれに何の意味もないと思います。直線近似で十分です。

補足

何度も見て頂きありがとうございます。
４グループ並べて書いたので分かりにくくなってしまい申し訳ないです。

数字は縦に見てください。（5508,6550,7160...)
5508から8812まで9つの数字を散布図に落とし近似式確認。
同じ作業を他３グループでもやったのですが、同じ傾向が見れるものの最初の数字がそれぞれ違うため近似式も違うものになってしまいます。
このため、４グループそれぞれで最初の数字を100%とした場合の増加率で比較しようと思い、A列でを変換したものが前回コメントさせて頂いたA'列です。

y = (0.273 * ln(2) + 0.999966) * 6550 = 5310.326169

ご教授いただいた上記式が理解できたので、後は正確なln値（と呼んだらいいのでしょうか？）が分かれば何とかなりそうなのですが…

回答No.3

keithin

縦だろうが横だろうが、やることは一緒です。どうして同じことを２回も回答しなきゃなりませんか。
それにデータの様子まで一緒です。人に書いてもらったことをつらつら流し読みで、判った気になって終わりじゃなく自分の手を動かしてください。

＞＞1行目の無作為な数字に対し2行目以降が対数近似になっていると思われます

ウソです。
実際のデータに当たってみる限り、２行目以降はいずれも１行目に対して線形になっています。

添付図：
判りやすいように８行目だけに操作をしていますが、２行目以下も全く同じです。

手順：
散布図を描く
系列１（２列目）のプロットに対して右クリックして「近似曲線の追加」を行う
オプションタブで「数式」と「Ｒ２値」を表示させる

添付図では黒が線形近似の結果、赤が対数近似の結果です。近似曲線の様子を見ても黒の線がきっちりプロットを通っているのに対して、対数近似の赤の線は微妙にずれています。
Ｒ２値をみても、線形近似で１００％説明が付くことが示されています。

＞正確な対数近似式

近似曲線の追加によりグラフ上に近似式を示すことができます。が、この式を計算には「使ってはいけません」ので、十分注意してください。
表示された数式は精度が足りていない（＝正確ではない）ので、計算に使うと正しくない結果になります。

グラフで示したように線形近似を採用するなら、FORECAST関数で近似値を計算します。
=FORECAST(A1,$A$2:$D$2,$A$1:$D$1)
右にコピー

もしどうしても対数近似の計算がしたいなら、
=FORECAST(LN(A1),$A$2:$D$2,LN($A$1:$D$9))
のようにすることもできます。

なお近似値ではなく近似係数（近似式の係数）が欲しいなら、LINEST関数や、SLOPE関数とINTERCEPT関数で計算します。
関数のヘルプをよく読んで、計算してください。

＃＃
今度こそ、ご自分でエクセルをちゃんと動かして、自分の手を動かして確認してからレスポンスを記入してください。

回答No.2

m_and_dmp

対数近似と仮定して近似式を求めたらy=-0.273ln(x)+0.999966(オリジナル: y-0.273ln(x)+0.999966)であったと、していますが、
x軸の数値が示されていないのでA-B, B-C, C-D の間隔が等間隔※と仮定して、
1行目は、
ｘ = 1, 2, 3, 4 に対して yの値が、5508, 2924, 2771, 1428 となるものとします。
5508を1として書き直すと、
1, 0.530864198, 0.50308642, 0.259259259 - - - - - - - - - - (1)
となります。
近似式 y = -0.273ln(x)+0.999966 にｘ = 1, 2, 3, 4 を代入して計算すると
y = 0.999966, 0.81073682, 0.700044845, 0.621507639 - - - -(2)
となり、(2)と比べてかなりの違いがあり、近似が良いとは言えません。

一列目と、全列の算術平均をグラフで表現してみました。全列の平均はyの値を*10してあります。（そうしないと、重なって判別できないため）
そのグラフに対数近似曲線を表示させ、近似式と、決定数R2を表示させてあります。
一列目と全列平均の傾向はほとんど差はなく、R2の値も小差です。
グラフ上で見る近似式は貴殿がお示しの近似式とはかなり違います。
（グラフ上の近似式の方が近似が良いと思います。）

無作為の数字AのB列目を求める式は、近似式のxに2を代入すればいいのではありませんか？

※A-B, B-C, C-D の間隔が等間隔であれば、xの値は1, 2, 3, 4 でも 100, 101, 102, 103でも、10, 20, 30, 40でも本質的な違いはありません。
等間隔でないならば、xの値が示されていなければ近似式を作ることはできません。

補足

考察ありがとうございました。
質問を捕捉させてください。
数列Aの場合、初期値5508に対し2番目が6550になっており、初期値を1とした場合1.189179...となります。
3番目以降も初期値5508で割戻してA'列を算出したところ対数近似かなと考えた次第です。
初期値がランダムに変わった場合でもn番目を計算したく、質問させて頂きました。
ご確認お願いできますでしょうか。

　　A　　　　A'
1　5,508　1
2　6,550　1.189179
3　7,160　1.299927
4　7,592　1.378359
5　7,928　1.439361
6　8,202　1.489107
7　8,434　1.531227
8　8,635　1.567720
9　8,812　1.599855

回答No.1

keithin

＞1列目の無作為な数字に対し2列目以降が対数近似になっていると思われます

実際のデータに当たってみる限り、２列目以降はいずれも１列目に対して線形になっています。

添付図：
判りやすいように２列目だけに操作をしていますが、３列目４列目も全く同じです。

手順：
散布図を描く
系列１（２列目）のプロットに対して右クリックして「近似曲線の追加」を行う
オプションタブで「数式」と「Ｒ２値」を表示させる

添付図では黒が線形近似の結果、赤が対数近似の結果です。近似曲線の様子を見ても黒の線がきっちりプロットを通っているのに対して、対数近似の赤の線は微妙にずれています。
Ｒ２値をみても、線形近似で１００％説明が付くことが示されています。

＞正確な対数近似式

近似曲線の追加によりグラフ上に近似式を示すことができます。が、この式を計算には「使ってはいけません」ので、十分注意してください。
表示された数式は精度が足りていない（＝正確ではない）ので、計算に使うと正しくない結果になります。

グラフで示したように線形近似を採用するなら、FORECAST関数で近似値を計算します。
=FORECAST(A1,$B$1:$B$9,$A$1:$A$9)
以下コピー

もしどうしても対数近似の計算がしたいなら、
=FORECAST(LN(A1),$B$1:$B$9,LN($A$1:$A$9))
のようにすることもできます。

なお近似値ではなく近似係数（近似式の係数）が欲しいなら、LINEST関数や、SLOPE関数とINTERCEPT関数で計算します。
関数のヘルプをよく読んで、計算してください。

補足

ご回答ありがとうございました。
そして申し訳ありません。質問文が間違っていました。
＞1列目の無作為な数字に対し2列目以降が対数近似になっていると思われます
　↓
＞1行目の無作為な数字に対し2行目以降が対数近似になっていると思われます

縦横を勘違いして書いていました…
お手数ですが、ご確認お願いできますでしょうか。