[[20230307185821]] 『linest関数とグラフに描画した近似曲線から得られ』(カズタカ) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『linest関数とグラフに描画した近似曲線から得られる傾きの違いについて』(カズタカ)

一次式で減衰する物質の減衰速度定数を反応開始時点を含む3時点のデータから求めています。
(例 0分の値; 100, 60分の値; 80, 120分の値; 60)

エクセル公式HPによれば、linest関数は最小二乗法を基に重回帰分析可能なため、傾きを以下の式で求めました。
slope = -linest(ln(0分の値から120分の値までのセル), (0-120分までのセル))
一方、60分の値を40や120など、明らかに近似直線の傾きが変わる場合でも、計算される傾きが変わらないのですが、どのように修正すればいいでしょうか?

< 使用 Excel:Microsoft365、使用 OS:Windows10 >


 起こってる現象としては、
 3点の真ん中の値を変えてるだけだと切片しか変化しない。って事ですよね。
 (私には「そういうもんなんじゃないの?」程度の事しか理解出来ないので、それ以上何とも言えませんけど ^^;)

    Sub test()
        [A1:B5] = [{"分","値";0,100;60,80;120,60;180,40}]
        [B3].Borders.LineStyle = xlContinuous
        [A7:B7].FormulaR1C1 = "=AVERAGE(R2C:R4C)"
        [A8].Formula = "=VAR.P(A2:A4)"
        [C8].Formula = "=COVARIANCE.P(A2:A4,B2:B4)"
        [D7:E7] = [{"a","b"}]
        [D8].Formula = "=C8/A8"
        [E8].Formula = "=B7-D8*A7"
        [D9].Formula = "=SLOPE(B2:B4,A2:A4)"
        [E9].Formula = "=INTERCEPT(B2:B4,A2:A4)"
        [D10:E10].FormulaArray = "=LINEST(B2:B4,A2:A4)"
        [A12:B12].FormulaR1C1 = "=AVERAGE(R2C:R5C)"
        [A13].Formula = "=VAR.P(A2:A5)"
        [C13].Formula = "=COVARIANCE.P(A2:A5,B2:B5)"
        [D13].Formula = "=C13/A13"
        [E13].Formula = "=B12-D13*A12"
        [D14].Formula = "=SLOPE(B2:B5,A2:A5)"
        [E14].Formula = "=INTERCEPT(B2:B5,A2:A5)"
        [D15:E15].FormulaArray = "=LINEST(B2:B5,A2:A5)"
    End Sub

 (参考にしたサイト)

【6つもあった!】Excelで単回帰分析の最小二乗法を解く方法をすべて実演 | 物流業界の歩き方
https://rikei-logistics.com/least-square2

(白茶) 2023/03/07(火) 21:25:50


 COVARIANCEの計算上、
 x1,x2,x3の平均がx2に等しいので、y2の値は結果に影響を及ぼさないということでしょう。
  
(γ) 2023/03/07(火) 22:05:29

 グラフにしてみたのであります

    Sub test2()
        [A1:D5] = [{"分","系列1","系列2","系列3";0,100,100,100;60,80,120,40;120,60,60,60;180,40,40,40}]
        [A8:D12] = [{"分2","系列4","系列5","系列6";0,100,100,100;40,80,120,40;120,60,60,60;180,40,40,40}]
        [A15:D19] = [{"分3","系列7","系列8","系列9";0,100,100,100;80,80,120,40;120,60,60,60;180,40,40,40}]
        [A3,A10,A17].Borders.LineStyle = XlLineStyle.xlContinuous
        With ActiveSheet.Shapes.AddChart.Chart
            .ChartType = xlXYScatterSmooth
            .SetSourceData [A1:D4], xlColumns
            .SeriesCollection(1).Trendlines.Add Type:=xlLinear, Name:="線形1"
            .SeriesCollection(2).Trendlines.Add Type:=xlLinear, Name:="線形2"
            .SeriesCollection(3).Trendlines.Add Type:=xlLinear, Name:="線形3"
            .Parent.Top = [G2].Top
            .Parent.Left = [G2].Left
        End With
        With ActiveSheet.Shapes.AddChart.Chart
            .ChartType = xlXYScatterSmooth
            .SetSourceData [A8:D11], xlColumns
            .SeriesCollection(1).Trendlines.Add Type:=xlLinear, Name:="線形4"
            .SeriesCollection(2).Trendlines.Add Type:=xlLinear, Name:="線形5"
            .SeriesCollection(3).Trendlines.Add Type:=xlLinear, Name:="線形6"
            .Parent.Top = [H14].Top
            .Parent.Left = [H14].Left
        End With
        With ActiveSheet.Shapes.AddChart.Chart
            .ChartType = xlXYScatterSmooth
            .SetSourceData [A15:D18], xlColumns
            .SeriesCollection(1).Trendlines.Add Type:=xlLinear, Name:="線形7"
            .SeriesCollection(2).Trendlines.Add Type:=xlLinear, Name:="線形8"
            .SeriesCollection(3).Trendlines.Add Type:=xlLinear, Name:="線形9"
            .Parent.Top = [I26].Top
            .Parent.Left = [I26].Left
        End With
    End Sub

(白茶) 2023/03/08(水) 09:06:44


ありがとうございます。y切片を0にしたlinest関数で、各値を対数化後、変化量を線形回帰することで、解決しました。
(カズタカ) 2023/03/08(水) 20:00:46

質問の前提として、0分における値(100)は固定したいという点を述べるべきでした。
申し訳ないです。
(カズタカ) 2023/03/08(水) 20:04:05

コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.