用Chat GPT 教寫excel vba 指令，快速製作分析圖表 make Chart quickly
用Chat GPT 教寫excel vba 指令，快速製作分析圖表 make Chart quickly
如果我想將一份問卷( 假設是長的)逐題製作分析圖表，需要不少時間。
今次我用ChatGPT幫忙快速製作分析圖表，嘗試省卻時間。
以下是一份模擬問卷，共有4題。
我的指令是:
Write Excel VBA command to make pie charts (3D) for different questions automatically
Chat GPT 的答案:
Sure, I'll provide you with VBA code to create a 3D pie chart that aggregates the answers for different questions. In this example, I'll assume the questions are listed in column A (starting from A2), and the corresponding answers are in columns B to F for each question. The data range for each question will be determined dynamically. Here's the code:
vbSub CreatePieCharts()
Dim ws As Worksheet
Dim chartDataRange As Range
Dim chartDestination As Range
Dim chartTitle As String
Dim lastRow As Long
Dim questionRange As Range
Dim question As Range
' Set the worksheet where the data is located
Set ws = ThisWorkbook.Worksheets("Sheet1") ' Replace "Sheet1" with the actual sheet name
' Find the last row of data in column A
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Set the range containing the questions (assumes each question is in a separate row)
Set questionRange = ws.Range("A2:A" & lastRow)
' Loop through each question and create a pie chart
For Each question In questionRange
chartTitle = question.Value
' Determine the data range for the current question
Dim firstDataRow As Long
firstDataRow = question.Row
Dim lastDataRow As Long
lastDataRow = ws.Cells(firstDataRow, ws.Columns.Count).End(xlToLeft).Column
' Set the data range for the pie chart
Set chartDataRange = ws.Range(ws.Cells(firstDataRow, "B"), ws.Cells(firstDataRow, lastDataRow))
' Set the destination range for the chart
Set chartDestination = ws.Cells(firstDataRow, lastDataRow + 2) ' Places the chart two columns to the right of the data
' Create the 3D pie chart
CreatePieChart3D ws, chartDataRange, chartDestination, chartTitle
Next question
End Sub
Sub CreatePieChart3D(ws As Worksheet, dataRange As Range, destination As Range, chartTitle As String)
Dim cht As ChartObject
Dim chartData As Range
' Define the chart data (including the category names and values)
Set chartData = Union(dataRange.Offset(0, -1), dataRange)
' Add a new chart to the worksheet
Set cht = ws.ChartObjects.Add(Left:=destination.Left, Width:=375, Top:=destination.Top, Height:=225)
' Set chart properties
With cht.Chart
.ChartType = xl3DPie
.SetSourceData Source:=chartData
.HasTitle = True
.ChartTitle.Text = chartTitle
End With
End Sub
Please make sure to replace "Sheet1" with the actual name of the sheet containing your data. Also, ensure that the data is arranged as described, with questions in column A and answers in columns B to F for each question. This code will loop through each question, create a 3D pie chart for its corresponding answers, and place the chart two columns to the right of the data for each question.
如要在excel 用VBA巨集功能: 先要在選項--＞自訂功能區-->剔選開發人員選項。
而後在開發人員的Tab裡按Visual
Basic
右Click 工作表1--> 插入--> 模組
然後貼上Chat GPT 的excel vba 指令。注意"Sheet1"是要改作工作表１
Sub CreatePieCharts()
Dim ws As Worksheet
Dim chartDataRange As Range
Dim chartDestination As Range
Dim chartTitle As String
Dim questionRange As Range
Dim question As Range
' Set the worksheet where the data is located
Set ws = ThisWorkbook.Worksheets("工作表1") ' Replace "Sheet1" with the actual sheet name
' Set the range containing the questions (assumes each question is in a separate row)
Set questionRange = ws.Range("B2:B5") ' Replace with the range containing your questions
' Loop through each question and create a pie chart
For Each question In questionRange
chartTitle = question.Value
' Set the data range for the pie chart (assuming the data is in the same row as the question)
Set chartDataRange = question.Offset(0, 1).Resize(1, 5) ' Assumes data is in columns B to F
' Set the destination range for the chart
Set chartDestination = ws.Cells(question.Row, questionRange.Columns.Count + 10) ' Places the chart two columns to the right of the question
' Create the 3D pie chart
CreatePieChart3D ws, chartDataRange, chartDestination, chartTitle
Next question
End Sub
Sub CreatePieChart3D(ws As Worksheet, dataRange As Range, destination As Range, chartTitle As String)
Dim cht As ChartObject
Dim chartData As Range
' Define the chart data (including the category names and values)
Set chartData = Union(dataRange.Offset(0, -1), dataRange)
' Add a new chart to the worksheet
Set cht = ws.ChartObjects.Add(Left:=destination.Left, Width:=375, Top:=destination.Top, Height:=225)
' Set chart properties
With cht.Chart
.ChartType = xl3DPie
.SetSourceData Source:=chartData
.HasTitle = True
.chartTitle.Text = chartTitle
.ApplyDataLabels Type:=xlDataLabelsShowLabelAndPercent ' Show custom text and percentages on the data labels
' Define custom color scheme for pie chart slices (modify as per your preference)
.SeriesCollection(1).Points(1).Interior.Color = RGB(255, 0, 0) ' First slice - Red
.SeriesCollection(1).Points(2).Interior.Color = RGB(0, 255, 0) ' Second slice - Green
.SeriesCollection(1).Points(3).Interior.Color = RGB(0, 0, 255) ' Third slice - Blue
.SeriesCollection(1).Points(4).Interior.Color = RGB(255, 165, 0) 'Fourth slice - Orange
.SeriesCollection(1).Points(5).Interior.Color = RGB(255, 255, 0) 'Fifth slice - Yellow
.SeriesCollection(1).Points(1).DataLabel.Text = "Totally Agree" & Format(.SeriesCollection(1).Points(1).DataLabel.Caption, "0.00%")
.SeriesCollection(1).Points(2).DataLabel.Text = "Agree" & Format(.SeriesCollection(1).Points(2).DataLabel.Caption, "0.00%")
.SeriesCollection(1).Points(3).DataLabel.Text = "Netural " & Format(.SeriesCollection(1).Points(3).DataLabel.Caption, "0.00%")
.SeriesCollection(1).Points(4).DataLabel.Text = "Disagree" & Format(.SeriesCollection(1).Points(4).DataLabel.Caption, "0.00%")
.SeriesCollection(1).Points(5).DataLabel.Text = "Totally Disagree " & Format(.SeriesCollection(1).Points(5).DataLabel.Caption, "0.00%")
End With
End Sub
注意 ：
Set questionRange = ws.Range("B2:B5") ' Replace with the range containing your questions 由原本Ａ２：Ａ１０改作B2:B5，根據問卷設計
.ApplyDataLabels ' Add data labels to the chart 這一句加上了圖表標籤資料，令圖表更清晰。
設定Pie Chart 顏色
由於有５個答案選項，我把它們設定了顏色。
.SeriesCollection(1).Points(1).DataLabel.Text = "Totally Agree" & Format(.SeriesCollection(1).Points(1).DataLabel.Caption, "0.00%")
.SeriesCollection(1).Points(2).DataLabel.Text = "Agree" & Format(.SeriesCollection(1).Points(2).DataLabel.Caption, "0.00%")
.SeriesCollection(1).Points(3).DataLabel.Text = "Netural " & Format(.SeriesCollection(1).Points(3).DataLabel.Caption, "0.00%")
.SeriesCollection(1).Points(4).DataLabel.Text = "Disagree" & Format(.SeriesCollection(1).Points(4).DataLabel.Caption, "0.00%")
.SeriesCollection(1).Points(5).DataLabel.Text = "Totally Disagree " & Format(.SeriesCollection(1).Points(5).DataLabel.Caption, "0.00%")
設定Pie Chart 標籤文字及百分比
然後按巨集，執行
這就成功快速製作分析圖表 了。
留言
張貼留言