使用大语言模型对excel表格进行统计分析和可视化操作

下面操作都是在国内万知大模型平台中执行：

万知 I 问答、阅读、创作的一站式AI工作平台

自动化使用excel表格例子

1.功能描述：计算需要列的平均值

2 提示语：生成VBA代码示例，它将分别计算excel表格中DEF列中每一列的平均值，并将结果记录在各自列的最后一行的后面一行

自动统计结果如下，具体操作看后面详细步骤：

3.生成下面内容：

Sub CalculateAveragesForDEF()

    Dim ws As Worksheet

    Set ws = ActiveSheet

   

    Dim lastRow As Long

    Dim i As Integer

    Dim averageRange As Range

    Dim averageValue As Double

   

    For i = 4 To 6 ' Loop through columns D (4), E (5), and F (6)

        lastRow = ws.Cells(ws.Rows.Count, i).End(xlUp).Row ' Find the last row with data in the column

       

        Set averageRange = ws.Range(ws.Cells(1, i), ws.Cells(lastRow, i)) ' Set the range for averaging

        averageValue = WorksheetFunction.Average(averageRange) ' Calculate the average

       

        ws.Cells(lastRow + 1, i).Value = averageValue ' Place the average in the cell below the last row

    Next i

End Sub

4.运行结果：

Alt+F11打开excel文档vba编辑器，同时双击当前worksheet，右边显示编辑框。

代码拷贝进去点击运行

看到统计结果

自动化使用excel表格例子

1.功能描述：根据D列前十行生成柱状图

2．提示语：生成VBA代码示例，根据D列前十行生成柱状图，数据是从第三行开始

3.生成下面内容：

4.运行结果：

自动化使用excel表格例子

1.功能描述：计算每行DEF列的平均值，命名为“三周平均分”

2．提示语：生成一个VBA代码示例，根据计算每行DEF列的平均值，并将结果放在G列，G列第二行的名为“三周平均分”

3.生成下面内容：

Sub CalculateAverageAndSetHeader()

    Dim ws As Worksheet

    Dim lastRow As Long

    Dim rng As Range

    Dim cell As Range

   

    Set ws = ActiveSheet  ' 使用活动工作表

    lastRow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row  ' 找到D列的最后一行

   

    ' 设置G列第二行的标题为“三周平均分”

    ws.Cells(2, "G").Value = "三周平均分"

   

    ' 设置计算平均值的范围并遍历每行

    Set rng = ws.Range("D3:D" & lastRow)

    For Each cell In rng

        ' 计算每行的D、E、F列的平均值，并将其放在G列对应行

        cell.Offset(0, 3).Value = AverageIfNotError(cell.Value, cell.Offset(0, 1).Value, cell.Offset(0, 2).Value)

    Next cell

End Sub

Function AverageIfNotError(param1 As Variant, param2 As Variant, param3 As Variant) As Double

    Dim sum As Double

    Dim count As Integer

   

    sum = 0

    count = 0

   

    ' 检查每个参数是否为错误值，如果不是，则计入总和和计数

    If Not IsError(param1) Then

        sum = sum + param1

        count = count + 1

    End If

    If Not IsError(param2) Then

        sum = sum + param2

        count = count + 1

    End If

    If Not IsError(param3) Then

        sum = sum + param3

        count = count + 1

    End If

   

    ' 如果计数大于0，则计算平均值，否则返回0

    If count > 0 Then

        AverageIfNotError = sum / count

    Else

        AverageIfNotError = 0

    End If

End Function

4.运行结果：

自动化使用excel表格例子

1.功能描述：根据分别计算DEF列的总值，并用生成的三个总值生成饼状图

2．提示语：生成VBA代码示例，分别计算DEF列的总值，并用生成的三个总值生成饼状图

3.生成下面内容：

Sub CreatePieChartFromDEFColumns()

    Dim ws As Worksheet

    Dim chartObj As ChartObject

    Dim rng As Range

    Dim dataRng As Range

    Dim i As Integer

    Dim totalD As Double

    Dim totalE As Double

    Dim totalF As Double

    Dim labels As String

   

    Set ws = ActiveSheet  ' 使用活动工作表

   

    ' 计算D、E、F列的总值

    totalD = Application.WorksheetFunction.Sum(ws.Range("D:D"))

    totalE = Application.WorksheetFunction.Sum(ws.Range("E:E"))

    totalF = Application.WorksheetFunction.Sum(ws.Range("F:F"))

   

    ' 设置数据区域和标签

    Set dataRng = ws.Range("H1:H3")

    dataRng.Cells(1, 1).Value = totalD

    dataRng.Cells(2, 1).Value = totalE

    dataRng.Cells(3, 1).Value = totalF

    labels = "={" & Chr(34) & "D列总值" & Chr(34) & "," & Chr(34) & "E列总值" & Chr(34) & "," & Chr(34) & "F列总值" & Chr(34) & "}"

   

    ' 添加一个新的饼状图

    Set chartObj = ws.ChartObjects.Add(Left:=ws.Range("H1").Left, Width:=300, Top:=ws.Range("H1").Top, Height:=200)

   

    With chartObj.Chart

        .ChartType = xlPie  ' 设置图表类型为饼状图

        .SetSourceData Source:=dataRng, PlotBy:=xlColumns  ' 设置图表的数据源

        .SeriesCollection(1).XValues = labels  ' 设置系列的类别标签

        .HasTitle = True  ' 图表有标题

        .ChartTitle.Text = "DEF列总值分布"  ' 设置图表标题

    End With

   

    chartObj.Chart.PlotArea.Interior.ColorIndex = xlNone  ' 设置图表区域背景为无色

    chartObj.Chart.PlotArea.Border.LineStyle = xlLineStyleNone  ' 设置图表区域无边框

End Sub

4.运行结果：

自动化使用excel表格例子

1.功能描述：得出各专业平均分，最后把结果生成柱状图

2．提示语：生成VBA代码示例，分别计算C列中文字带有“会计”“机制”“信科”对应G列平均分的总和，再除去他们的数量得出各专业平均分，最后把结果生成柱状图

3.生成下面内容：

Sub CalculateAveragesAndCreateChart()

    Dim ws As Worksheet

    Set ws = ActiveSheet

   

    Dim lastRow As Long

    lastRow = ws.Cells(ws.Rows.count, "C").End(xlUp).Row

   

    Dim keyWords As Variant

    keyWords = Array("会计", "机制", "信科")

   

    Dim i As Long

    Dim j As Long

    Dim sumScores As Double

    Dim count As Long

    Dim averages() As Double

    ReDim averages(LBound(keyWords) To UBound(keyWords))

   

    ' Calculate sums and counts for each keyword

    For i = LBound(keyWords) To UBound(keyWords)

        sumScores = 0

        count = 0

        For j = 2 To lastRow ' Assuming row 1 contains headers

            If InStr(1, ws.Cells(j, "C").Value, keyWords(i), vbTextCompare) > 0 Then

                sumScores = sumScores + ws.Cells(j, "G").Value

                count = count + 1

            End If

        Next j

        If count > 0 Then

            averages(i) = sumScores / count

        Else

            averages(i) = 0

        End If

    Next i

   

    ' Output the averages to the worksheet

    Dim outputRow As Long

    outputRow = lastRow + 2 ' Adjust based on where you want the output

    For i = LBound(keyWords) To UBound(keyWords)

        ws.Cells(outputRow, "C").Value = keyWords(i)

        ws.Cells(outputRow, "G").Value = averages(i)

        outputRow = outputRow + 1

    Next i

   

    ' Create a bar chart

    Dim chartData As Range

    Set chartData = ws.Range(ws.Cells(outputRow - UBound(keyWords) - 1, "C"), ws.Cells(outputRow - 1, "G"))

   

    Dim chartObj As ChartObject

    Set chartObj = ws.ChartObjects.Add(Left:=ws.Cells(outputRow, "C").Left, Width:=300, Top:=ws.Cells(outputRow, "C").Top, Height:=200)

   

    With chartObj.Chart

        .ChartType = xlColumnClustered

        .SetSourceData Source:=chartData

        .HasTitle = True

        .ChartTitle.Text = "Average Scores by Category"

        .Axes(xlCategory).HasTitle = True

        .Axes(xlCategory).AxisTitle.Text = "Category"

        .Axes(xlValue).HasTitle = True

        .Axes(xlValue).AxisTitle.Text = "Average Score"

    End With

End Sub

4.运行结果：

自动化使用excel表格例子

1.功能描述：根据DEF列的分数生成折线图来表示趋势

2．提示语：生成VBA代码示例，根据DEF列的分数生成折线图

3.生成下面内容：

Sub CreateLineChartFromDEFScores()

    Dim ws As Worksheet

    Set ws = ActiveSheet

   

    Dim lastRow As Long

    lastRow = ws.Cells(ws.Rows.count, "D").End(xlUp).Row

   

    Dim chartData As Range

    Set chartData = ws.Range(ws.Cells(1, "D"), ws.Cells(lastRow, "F")) ' Assuming row 1 contains headers

   

    Dim chartObj As ChartObject

    Set chartObj = ws.ChartObjects.Add(Left:=ws.Cells(1, "D").Left, Width:=300, Top:=ws.Cells(1, "D").Top, Height:=200)

   

    With chartObj.Chart

        .ChartType = xlLine

        .SetSourceData Source:=chartData

        .HasTitle = True

        .ChartTitle.Text = "Scores Trend"

        .Axes(xlCategory).HasTitle = True

        .Axes(xlCategory).AxisTitle.Text = "Category"

        .Axes(xlValue).HasTitle = True

        .Axes(xlValue).AxisTitle.Text = "Score"

    End With

End Sub

4.运行结果：