linest関数とseriessum関数の使い方(邪道か？)

旧ブログ https://blog.goo.ne.jp/yuuhae612go

 

３点を通る２次関数・２次曲線・２次の多項式

４点を通る３次関数・３次曲線・３次の多項式

５点を通る４次関数・４次曲線・４次の多項式

６点を通る５次関数・５次曲線・５次の多項式

７点を通る６次関数・６次曲線・６次の多項式

８点を通る７次関数・７次曲線・７次の多項式

９点を通る８次関数・８次曲線・８次の多項式

１０点を通る９次関数・９次曲線・９次の多項式

　　　　…

ｎ+1点を通るn次関数・n次曲線・n次の多項式

linest関数で多項式の係数を求めてseriessum関数でその係数とxからyを求める。

任意の点10個(p1～p10)を用意してD4:D13にそのｙ座標を、F4:F13にそのx座標を入力して、E列の定数(x^0)からN列のx^9までを配置する。(linest関数では定数の列は不使用)

{G4:G13} =ArrayFormula($F4:$F13^G2)

H～N列に{G4:G13}をコピペ

または

{G4:N13} =ArrayFormula($F4:$F13^{2,3,4,5,6,7,8,9})

「0^0=1」は必ずしも正しい訳ではないが表計算ソフトがそれを正しいとして出力するので利用している。「x=0」を場合分けで別処理すれば正当性は高くなる。

	B	C	D	E	F	G	H	I	J	K	L	M	N
2				0	1	2	3	4	5	6	7	8	9
3		0^0=1として計算	y	x^0	x^1	x^2	x^3	x^4	x^5	x^6	x^7	x^8	x^9
4	1	p1	4	1	0	0	0	0	0	0	0	0	0
5	2	p2	1	1	2	4	8	16	32	64	128	256	512
6	3	p3	0	1	3	9	27	81	243	729	2187	6561	19683
7	4	p4	2	1	4	16	64	256	1024	4096	16384	65536	262144
8	5	p5	4	1	5	25	125	625	3125	15625	78125	390625	1953125
9	6	p6	5	1	6	36	216	1296	7776	46656	279936	1679616	10077696
10	7	p7	7	1	7	49	343	2401	16807	117649	823543	5764801	40353607
11	8	p8	8	1	8	64	512	4096	32768	262144	2097152	16777216	134217728
12	9	p9	6	1	9	81	729	6561	59049	531441	4782969	43046721	387420489
13	10	p10	0	1	10	100	1000	10000	100000	1000000	10000000	100000000	1000000000

{E33:N33} =ArrayFormula(linest(D4:D13,F4:N13))

{F34:N34} =ArrayFormula(linest(D4:D12,F4:M12))

{G35:N35} =ArrayFormula(linest(D4:D11,F4:L11))　以下略

	B	C	D	E	F	G	H	I	J	K	L	M	N
32		linest関数を使用		x^9の係数	x^8の係数	x^7の係数	x^6の係数	x^5の係数	x^4の係数	x^3の係数	x^2の係数	x^1の係数	x^0の係数
33	10	p1～p10を通る9次関数		0.00	0.01	-0.13	1.42	-9.38	37.63	-88.74	112.24	-59.95	4.00
34	9	p1～p9を通る8次関数			0.00	-0.02	0.28	-2.14	9.18	-21.27	24.27	-11.85	4.00
35	8	p1～p8を通る7次関数				0.00	0.02	-0.10	-0.17	3.50	-10.85	8.60	4.00
36	7	p1～p7を通る6次関数					0.00	0.14	-1.49	7.55	-17.22	12.60	4.00
37	6	p1～p6を通る5次関数						0.04	-0.75	4.79	-12.25	9.17	4.00
38	5	p1～p5を通る4次関数							-0.17	1.83	-5.83	4.17	4.00
39	4	p1～p4を通る3次関数								0.33	-1.50	0.17	4.00
40	3	p1～p3を通る2次関数									0.17	-1.83	4.00
41	2	p1～p2を通る1次関数										-1.50	4.00

46～55行は10個の点の座標、56行以下はn次曲線の散布図用データ

E56 =if($D56=0,$N$33,seriessum($D56,E$44-1,-1,E$33:$N$33))

F56 =if($D56=0,$N$34,seriessum($D56,F$44-1,-1,F$34:$N$34))

 (中略)

M56 =if($D56=0,$N$41,seriessum($D56,M$44-1,-1,M$41:$N$41))

E56:M56をE56:E266にコピペ。

	B	C	D	E	F	G	H	I	J	K	L	M	N
43			seriessum関数使用(linest関数の結果から)
44		グラフ用		10	9	8	7	6	5	4	3	2
45	-1	←10点のxの最小値	x	y(10点)	y(9点)	y(8点)	y(7点)	y(6点)	y(5点)	y(4点)	y(3点)	y(2点)	y(p1-p10)
46	10	←10点のxの最大値	-1										4
47	1.1	←xの範囲の１割	2										1
48	20	←分割	3										0
49			4										2
50			5										4
51			6										5
52			7										7
53			8										8
54			9										6
55			10										0
56			-1.28	119.861	34.283	0.907	-6.154	-3.533	0.412	3.120	4.275	4.275
57			-1.22	90.001	26.470	1.559	-3.743	-1.762	1.243	3.323	4.220	4.220
58			-1.17	63.795	19.614	2.197	-1.532	-0.129	2.015	3.512	4.165	4.165
59			-1.11	40.922	13.635	2.819	0.489	1.371	2.730	3.688	4.110	4.110
60			-1.06	21.083	8.454	3.421	2.330	2.746	3.391	3.851	4.055	4.055
61			-1.00	4.000	4.000	4.000	4.000	4.000	4.000	4.000	4.000	4.000
62			-0.95	-10.587	0.206	4.554	5.508	5.139	4.558	4.137	3.945	3.945
63			-0.89	-22.917	-2.992	5.081	6.864	6.170	5.067	4.261	3.890	3.890
64			-0.84	-33.216	-5.651	5.580	8.074	7.096	5.530	4.373	3.835	3.835
65			-0.78	-41.688	-7.827	6.047	9.148	7.924	5.947	4.474	3.780	3.780
66			-0.73	-48.527	-9.570	6.483	10.094	8.658	6.321	4.562	3.725	3.725
67			-0.67	-53.910	-10.926	6.887	10.919	9.304	6.654	4.640	3.670	3.670
68			-0.62	-57.998	-11.939	7.257	11.629	9.865	6.946	4.707	3.615	3.615
69			-0.56	-60.944	-12.650	7.593	12.234	10.348	7.201	4.763	3.560	3.560
70			-0.51	-62.884	-13.096	7.895	12.738	10.756	7.419	4.809	3.505	3.505
71			-0.45	-63.945	-13.310	8.162	13.149	11.093	7.603	4.845	3.450	3.450
72			-0.40	-64.244	-13.324	8.396	13.473	11.364	7.753	4.871	3.395	3.395
73			-0.34	-63.887	-13.166	8.595	13.716	11.573	7.872	4.887	3.340	3.340
74			-0.29	-62.968	-12.863	8.761	13.883	11.723	7.961	4.895	3.285	3.285
75			-0.23	-61.577	-12.439	8.894	13.981	11.820	8.022	4.894	3.230	3.230
76			-0.18	-59.792	-11.916	8.995	14.015	11.865	8.056	4.884	3.175	3.175
77			-0.12	-57.685	-11.312	9.064	13.989	11.864	8.064	4.866	3.120	3.120
78			-0.06	-55.321	-10.646	9.103	13.909	11.819	8.048	4.840	3.065	3.065
79			-0.01	-52.756	-9.933	9.112	13.779	11.734	8.009	4.807	3.010	3.010
80			0.05	-50.043	-9.189	9.093	13.603	11.611	7.950	4.766	2.955	2.955
81			0.10	-47.228	-8.424	9.047	13.387	11.455	7.870	4.718	2.900	2.900

(グラフ元データは以下略)

因みに

E56 =if($D56=0,$N$33,seriessum($D56,E$44-1,-1,E$33:$N$33))

の代わりに

E56 =if($D56=0,$N$33,seriessum($D56,E$44-1,-1,linest($D$4:$D$13,$F$4:＄N$13)))

も可能。

できた散布図グラフを調整して完成。

ちゃんと10点を通ってて凄いと思う。でも表計算ソフト等は精密な計算には向いてないから、どんな場合でも通るかと言われればその保証はない。

linest関数は近似曲線(直線)を求めるためのものだと思うからこの使い方は邪道かも。n個の点を通る曲線を求めるのに便利そうだったので使ってみた。

seriessum関数は「べき級数」を求めるためのものだと思うからこの使い方は邪道かも。多項式用に便利そうだったので使ってみた。ただseriessum関数でべき級数の求め方を教えてくれるサイトは見つからなかったので自分でいろいろ試してみた。

ついで　linest関数もseriessum関数も使わずに逆行列でやってみた。

{E19:N19} =ArrayFormula(transpose(mmult(minverse(E4:N13),D4:D13)))

{E20:M20} =ArrayFormula(transpose(mmult(minverse(E4:M12),D4:D12)))

{E21:L21} =ArrayFormula(transpose(mmult(minverse(E4:L11),D4:D11)))　以下略

	B	C	D	E	F	G	H	I	J	K	L	M	N
18		逆行列を使用		x^0の係数	x^1の係数	x^2の係数	x^3の係数	x^4の係数	x^5の係数	x^6の係数	x^7の係数	x^8の係数	x^9の係数
19	10	p1～p10を通る9次関数		4.00	-59.95	112.24	-88.74	37.63	-9.38	1.42	-0.13	0.01	0.00
20	9	p1～p9を通る8次関数		4.00	-11.85	24.27	-21.27	9.18	-2.14	0.28	-0.02	0.00
21	8	p1～p8を通る7次関数		4.00	8.60	-10.85	3.50	-0.17	-0.10	0.02	0.00
22	7	p1～p7を通る6次関数		4.00	12.60	-17.22	7.55	-1.49	0.14	0.00
23	6	p1～p6を通る5次関数		4.00	9.17	-12.25	4.79	-0.75	0.04
24	5	p1～p5を通る4次関数		4.00	4.17	-5.83	1.83	-0.17
25	4	p1～p4を通る3次関数		4.00	0.17	-1.50	0.33
26	3	p1～p3を通る2次関数		4.00	-1.83	0.17
27	2	p1～p2を通る1次関数		4.00	-1.50

272～281行は10個の点の座標、282行以下はn次曲線の散布図用データ

F282 =if($D282=0,$E$27,SUMPRODUCT($D282^(column($E$27:F$27)-5),$E$27:F$27))

G282 =if($D282=0,$E$26,SUMPRODUCT($D282^(column($E$26:G$26)-5),$E$26:G$26))

 (中略)

N282 =if($D282=0,$E$19,SUMPRODUCT($D282^(column($E$19:N$19)-5),$E$19:N$19))

F282:N282をF282:F492にコピペ。

	B	C	D	E	F	G	H	I	J	K	L	M	N
269			seriessum関数不使用(逆行列の結果から)
270		グラフ用			2	3	4	5	6	7	8	9	10
271	0	10点のxの最小値	x	y(p1-p10)	y(2点)	y(3点)	y(4点)	y(5点)	y(6点)	y(7点)	y(8点)	y(9点)	y(10点)
272	10	10点のxの最大値	0	4
273	1	xの範囲の１割	2	1
274	20	分割	3	0
275			4	2
276			5	4
277			6	5
278			7	7
279			8	8
280			9	6
281			10	0
282			-0.25		4.375	4.469	3.859	2.564	0.865	-0.349	1.118	8.850	27.546
283			-0.20		4.300	4.373	3.904	2.918	1.637	0.729	1.819	7.526	21.253
284			-0.15		4.225	4.279	3.940	3.237	2.333	1.697	2.455	6.400	15.837
285			-0.10		4.150	4.185	3.968	3.523	2.956	2.561	3.028	5.450	11.210
286			-0.05		4.075	4.092	3.988	3.777	3.510	3.326	3.543	4.656	7.289
287			0.00		0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000
288			0.05		3.925	3.909	4.005	4.194	4.428	4.588	4.403	3.466	1.272
289			0.10		3.850	3.818	4.002	4.360	4.799	5.095	4.755	3.037	-0.958
290			0.15		3.775	3.729	3.992	4.500	5.115	5.527	5.057	2.701	-2.748
291			0.20		3.700	3.640	3.976	4.614	5.380	5.888	5.313	2.445	-4.153
292			0.25		3.625	3.552	3.953	4.705	5.598	6.185	5.525	2.256	-5.220
293			0.30		3.550	3.465	3.924	4.773	5.771	6.421	5.695	2.125	-5.996
294			0.35		3.475	3.379	3.889	4.820	5.902	6.601	5.826	2.041	-6.519
295			0.40		3.400	3.293	3.848	4.846	5.995	6.729	5.921	1.996	-6.827
296			0.45		3.325	3.209	3.802	4.854	6.051	6.810	5.981	1.983	-6.953
297			0.50		3.250	3.125	3.750	4.844	6.074	6.848	6.010	1.995	-6.926
298			0.55		3.175	3.042	3.693	4.817	6.067	6.845	6.008	2.025	-6.774
299			0.60		3.100	2.960	3.632	4.774	6.031	6.807	5.979	2.068	-6.520
300			0.65		3.025	2.879	3.566	4.717	5.970	6.735	5.925	2.120	-6.185
301			0.70		2.950	2.798	3.496	4.647	5.885	6.634	5.847	2.176	-5.788
302			0.75		2.875	2.719	3.422	4.564	5.778	6.507	5.748	2.234	-5.346
303			0.80		2.800	2.640	3.344	4.470	5.653	6.356	5.630	2.289	-4.873
304			0.85		2.725	2.562	3.263	4.366	5.511	6.184	5.494	2.339	-4.382
305			0.90		2.650	2.485	3.178	4.252	5.353	5.995	5.342	2.383	-3.884
306			0.95		2.575	2.409	3.090	4.130	5.182	5.790	5.177	2.419	-3.387
307			1.00		2.500	2.333	3.000	4.000	5.000	5.571	5.000	2.444	-2.900

(グラフ元データは以下略)

できた散布図グラフを調整して完成。

2023.04.18 20:25公開　単純ミスなどは適宜修正済＆修正予定

余談１

「表は貼り付け時の状態、グラフはドライブの元データを即反映」らしい。修正状態によっては齟齬が生じる恐れあり。

以下テスト用