[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『一行おきに二次関数でフィッティングをしたい』（Anshin）
下記のように (B4:B45,C4:C45)=(x,y) に格納されているデータに対して、B列とC列をそれぞれ一行ずつ飛ばして（つまり奇数行と偶数行をそれぞれ別々に）、xの二次関数 (y=a+bx+cx^2)でフィッティングをしたいと思い、下記のように計算式を書いてみたのですが、一行飛ばしにならず、すべての行を含めて計算してしまいます．
A列 B列 C列
1 6453.79 90.00
2 6397.40 91.52
3 6349.76 90.27
4 6295.68 91.80
5 6242.68 90.59
6 6190.92 91.79
7 6138.57 90.46
8 6086.05 91.88
9 6029.91 90.21
10 5974.08 92.35
c1=INDEX(LINEST(IF(MOD(ROW(C4:C45),2)=0,C4:C45,),B4:B45^{1,2}),1,1)
b1=INDEX(LINEST(IF(MOD(ROW(C4:C45),2)=0,C4:C45,),B4:B45^{1,2}),1,2)
a1=INDEX(LINEST(IF(MOD(ROW(C4:C45),2)=0,C4:C45,),B4:B45^{1,2}),1,3)
c2=INDEX(LINEST(IF(MOD(ROW(C4:C45),2)=1,C4:C45,),B4:B45^{1,2}),1,1)
b2=INDEX(LINEST(IF(MOD(ROW(C4:C45),2)=1,C4:C45,),B4:B45^{1,2}),1,2)
a2=INDEX(LINEST(IF(MOD(ROW(C4:C45),2)=1,C4:C45,),B4:B45^{1,2}),1,3)
どのように対処したらよろしいか、ご教示いただけると幸甚です．
よろしくお願いします．
＜ 使用 Excel：Excel2019、使用 OS：Windows10 ＞
もう一点、質問がありまして、同じデータに対して、エクセルでフィッティングをした２次式とカレエダグラフでフィッティングをした２次式が大きく異なる係数（a,b,c）となっています．相関係数はカレエダグラフのほうが高い結果ですが、エクセルの近似式の精度が低いということはあるでしょうか？
（Anshin） 2020/08/10(月) 02:26
後半部分。
それだけの情報では、ああそうですか、そういうこともあるかもとしか言えませんね。
カレイダグラフというのがあるんですね？
・どんなデータを使い
・どんな形式のフィッティングなのか、
・それぞれで、どんな結果が得られたのか
具体的な数値例を示してください。
（γ） 2020/08/10(月) 06:21
＞c1=INDEX(LINEST(IF(MOD(ROW(C4:C45),2)=0,C4:C45,),B4:B45^{1,2}),1,1)
↑
カンマの後ろを省略すると0で埋められてしまう。
=INDEX(LINEST(IF(MOD(ROW(C4:C45),2)=0,C4:C45,C3:C44),IF(MOD(ROW(B4:B45),2)=0,B4:B45,B3:B44)^{1,2}),1,3)
~~~↑~~ ~~↑~~
１行上を指定する １行上を指定する
＞カレエダグラフでフィッティングをした２次式が大きく異なる係数（a,b,c）となっています
１．配列数式として入力していますね？(つまり、Ctrl+Shiftを押しながら、Enterキー押下） ２．カレイダグラフは、一行置きなんて芸当が出来るんですか？
（半平太） 2020/08/10(月) 10:19
カレエダグラフURL https://www.hulinks.co.jp/software/stat_graph/kaleida/section02
（u） 2020/08/10(月) 10:59
なるほど、そういう式になるんですか。勉強になります。
2つの系列に分けて散布図を書くコードのサンプルを書いてみました。 選択範囲（数値部分のみを対象）を元に描画します。 必要であれば、これを素材に、 複数のシートの繰り返し処理を書いて見て下さい。
Sub test()
Dim chart As chart
Dim rng As Range
Dim r As Range
Set rng = Selection 'Range("B4:C45")
Set chart = ActiveSheet.Shapes.AddChart.chart
With chart
.ChartType = xlXYScatter
'規定の系列をいったん削除
.SeriesCollection(1).Delete
.SeriesCollection(1).Delete
Set r = myRange(rng) '奇数行の第1列のみのセル範囲
'奇数行からなる系列を追加
.SeriesCollection.NewSeries
.SeriesCollection(1).Name = "=""ser1"""
.SeriesCollection(1).XValues = r
.SeriesCollection(1).Values = r.Offset(0, 1)
'偶数行のデータ系列を追加
.SeriesCollection.NewSeries
.SeriesCollection(2).Name = "=""ser2"""
.SeriesCollection(2).XValues = r.Offset(1)
.SeriesCollection(2).Values = r.Offset(1, 1)
'近似曲線の追加
.SeriesCollection(1).Trendlines.Add
With .SeriesCollection(1).Trendlines(1)
.Type = xlPolynomial
.Order = 2
.DisplayEquation = True
'.DataLabel.NumberFormat = "#,##0.0000000000_);[赤](#,##0.0000000000)"
End With
.SeriesCollection(2).Trendlines.Add
With .SeriesCollection(2).Trendlines(1)
.Type = xlPolynomial
.Order = 2
.DisplayEquation = True
' .DataLabel.NumberFormat = "#,##0.0000000000_);[赤](#,##0.0000000000)"
End With
End With
End Sub
Function myRange(rng As Range) As Range
Dim c As Long
c = rng.Rows.Count
Set myRange = rng.Cells(1, 1)
For k = 3 To c Step 2
Set myRange = Union(myRange, rng.Cells(k, 1))
Next
End Function
【補足】 1.手元のExcel2010で作りましたが、最近のですとFullSeriesCollectionというのを 使うようですね。たぶん後方互換性があるとみて、そのままにしています。
2.なお、近似式の桁数を増やすために、表示形式を変更してみましたが、 どうしてもエラーになってしまう。（上記2カ所のコメントアウト部分） マクロ記録から得られるコードを実行してもエラーになる。不思議だ。
（γ） 2020/08/10(月) 12:24
カレイダグラフとの差の話。 二次多項式近似でそうそう差が出るとは、私にも思えません。 差異が見られるに至った経緯とその詳細を示してもらうと、 コメントができると思います。
余談：（ここでの議論とは直接関係はしません） グラフに表示される近似式の係数と、LINESTで求めた係数が異なることはあります。 二次多項式ではたぶん発生しないと思いますが、 5次6次と高次の場合、LINESTで求めた特定の次数の係数がゼロに なってしまうことがあります。 これはいわゆるマルチコの関係と思われるが、LINESTの内部処理は 公表されていないため、どのようなメカニズムで変数の節約が起きるかは 一般ユーザーからは不明です。 (もっとも、高次になればなるほどオーバーフィットしやすくなるので、 適切な次数選択が必要になるわけではありますが。)
（γ） 2020/08/10(月) 12:27
質問者さん、ごめんなさい。 ちょとだけお邪魔します。
γさん、教えて下さい。
>.DataLabel.NumberFormat = "#,##0.0000000000_);[赤](#,##0.0000000000)"
これは、どんな意味なのでしょうか。 係数が負の数のものは、赤色表示にできるのかと考え 手作業で、実行してみたのですが、 色に関しては何も変化なしです。
なお、マクロでは、以下でエラーなく設定はできました。 ですが、手作業と同じ結果です。
.DataLabel.NumberFormatLocal = "#,##0.0000000000_);[赤](#,##0.0000000000)"
とか
.DataLabel.NumberFormat = "#,##0.0000000000_);[Red](#,##0.0000000000)"
（マナ） 2020/08/10(月) 16:44
（γ） 2020/08/10(月) 17:24
焦っているときは、どうしても、マクロ記録は正しいという前提で出発してしまいます。
ありがとうございました。
[追記]
というか、デフォルトの赤字の表示を通常の黒字のマイナスにして
記録すべきだったわけで、そうすれば記録も↓となって、悩むこともなかったわけでした。
.DataLabel.NumberFormat = "#,##0.0000000000" どうも失礼しました。
（γ） 2020/08/10(月) 20:49
>意図は表示桁数を増やすことだけです。 >数値 を選んで 10桁 に設定したものの記録でした。
確かに、仰る通り記録されました。納得です。ありがとうございました。 Anshinさん、お邪魔しました。 （マナ） 2020/08/10(月) 22:31
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.