請AI幫忙寫EXCEL VBA，並用內建繪圖元件幫忙畫 迴歸直線圖 ,及其找出其線性函數式

 情境:因EXCEL圖表製圖不熟悉，可透過AI工具，協助幫忙產出EXCEL VBA進行繪製

迴歸直線圖 ,及其找出其線性函數式。y = 4.341x + 43.017

 

EXCEL表格，如下:    

      A               B                  C
1 年度        行銷費X        銷售額Y
2  109               2                50
3  110               3                66
4  111               6                54
5  112               9                88
6  113               12              96

💚EXCEL預先準備環境
檔案 | 選項 |增益集 | 設定
增益集| 勾選 分析工具箱 | 確定


💚EXCEL分析操作設定， 資料 | 資料分析 | 迴歸 |確定
輸入Y範圍(Y): 工作表1!$C$2:$C$6
輸入X範圍(X): 工作表1!$B$2:$B$6
 

EXCEL迴歸分析產出結果，如下:
摘要輸出                               
                               
迴歸統計                               
R 的倍數    0.883984674                           
R 平方    0.781428904                           
調整的 R 平方    0.708571872                           
標準誤    11.02650511                           
觀察值個數    5                           
                               
ANOVA                               
    自由度    SS    MS    F    顯著值           
迴歸    1    1304.048555    1304.048555    10.72551108    0.046601631           
殘差    3    364.7514451    121.583815                   
總和    4    1668.8                       
                               
    係數    標準誤    t 統計    P-值    下限 95%    上限 95%    下限 95.0%    上限 95.0%
截距    43.01734104    9.812397095    4.38397882    0.021976561    11.78991416    74.24476792    11.78991416    74.24476792
X 變數 1    4.341040462    1.325515578    3.274982607    0.046601631    0.12265831    8.559422615    0.12265831    8.559422615



💚AI Prompt提示詞，下法，如藍色部分
有一個EXCEL表,第1列為標題列,A欄為年度,B欄為行銷費X,C欄為銷售額Y,
請用EXCEL VBA,利用其  資料分析  迴歸分析 ,產出迴歸直線圖  ,
X軸為 B欄 ，Y軸為C欄,處理圖表直到最後1筆 ,
請畫出迴歸直線  ,並且將其迴歸直線函數,表示出來

 

ＡＩ產出之巨集程式  ，執行CreateRegressionChart巨集，即可產製EXCEL圖表

Sub CreateRegressionChart()

    Dim ws As Worksheet
    Dim LastRow As Long
    Dim DataRange As Range
    Dim XRange As Range
    Dim YRange As Range
    Dim Regression As Object
    Dim RegressionEquation As String
    
    ' 指定ＥＸＣＥＬ表數據，放置之⌈工作表⌋名稱
    Set ws = ThisWorkbook.Sheets("工作表1") '  
    

    ' 找出資料筆數之總數
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' 設定X和Y的範圍
    Set XRange = ws.Range("B2:B" & LastRow)        ' B欄為X軸

    Set YRange = ws.Range("C2:C" & LastRow)       ' C欄為Y軸
    
    ' 使用LINEST函數，進行迴歸分析
'    RegressionData = WorksheetFunction.LinEst(YRange, XRange, True, True)
    RegressionData = WorksheetFunction.LinEst(YRange, XRange, True, True)

    ' 提取斜率和截距
    Slope = RegressionData(1, 1)
    Intercept = RegressionData(1, 2)
    
    ' 在工作表中，顯示迴歸方程式

    ws.Range("E2").Value = "Regression Equation:"
    ws.Range("F2").Value = "Y = " & Slope & " * X + " & Intercept
    
    ' 繪製線性圖表
    Dim ChartObj As ChartObject
    Dim TrendlineChart As Chart
    
    ' 刪除現有的圖表
    For Each ChartObj In ws.ChartObjects
        ChartObj.Delete
    Next ChartObj
    
    ' 新增趨勢線圖表
    Set ChartObj = ws.ChartObjects.Add(Left:=100, Width:=375, Top:=75, Height:=225)
    Set TrendlineChart = ChartObj.Chart
    
    ' 添加散點圖
    With TrendlineChart
        .ChartType = xlXYScatter
        .SetSourceData Source:=ws.Range("B2:C" & LastRow)
        .HasTitle = True
        .ChartTitle.Text = "Regression Analysis"
        
        ' 添加趨勢線
        With .SeriesCollection.NewSeries
            .XValues = XRange
            .Values = YRange
            .Trendlines.Add(Type:=xlLinear).Select
            .Trendlines(1).DisplayEquation = True
            .Trendlines(1).DisplayRSquared = False
        End With
        
        ' 設定軸標籤
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Text = "X行銷費"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Text = "Y銷售額"
    End With 
End Sub