ChatGPT和VBA的強(qiáng)強(qiáng)聯(lián)合，改變你看待Excel的方式!
完整代碼如下：
Sub CreateGroupedBarChart()
??' 檢查 "分析" 工作表是否已存在，如果存在，則刪除
??Dim ws As Worksheet
??For Each ws In ThisWorkbook.Sheets
????If ws.Name = "分析" Then
??????Application.DisplayAlerts = False
??????ws.Delete
??????Application.DisplayAlerts = True
??????Exit For
????End If
??Next ws
???
??' 創(chuàng)建一個(gè)新的工作表"分析"
??Sheets.Add(After:=Sheets(Sheets.Count)).Name = "分析"
??' 獲取銷售數(shù)據(jù)工作表
??Set ws = ThisWorkbook.Sheets("銷售數(shù)據(jù)")
??' 刪除空白行
??Dim rng As Range
??Dim cell As Range
??Dim del As Range
??Set rng = Intersect(ws.Range("A1:A37"), ws.Range("A:A"))
??For Each cell In rng
????If (Trim(cell.Value) = "") Then
??????If del Is Nothing Then
????????Set del = cell
??????Else
????????Set del = Union(del, cell)
??????End If
????End If
??Next cell
??On Error Resume Next
??del.EntireRow.Delete
??' 將銷售數(shù)據(jù)工作表中的數(shù)據(jù)復(fù)制到分析工作表
??ws.Range("A1:D37").Copy
??Sheets("分析").Range("A1").PasteSpecial Paste:=xlPasteValues
??Application.CutCopyMode = False
??' 在分析工作表中插入數(shù)據(jù)透視表
??Dim pivotTable As pivotTable
??Dim PivotRange As Range
??Set PivotRange = Sheets("分析").Range("A1:D" & Sheets("分析").Cells(Rows.Count, 1).End(xlUp).Row)
??Sheets("分析").PivotTableWizard SourceType:=xlDatabase, SourceData:=PivotRange, _
????TableDestination:=Sheets("分析").Range("F5"), TableName:="PivotTable1"
???
??Set pivotTable = Sheets("分析").PivotTables("PivotTable1")
??pivotTable.PivotFields("電腦品牌").Orientation = xlRowField
??pivotTable.PivotFields("銷售額").Orientation = xlDataField
??' 刷新數(shù)據(jù)透視表的緩存
??pivotTable.PivotCache.Refresh
??' 插入條形圖
??Dim chartObj As ChartObject
??Set chartObj = Sheets("分析").ChartObjects.Add(Left:=200, Width:=375, Top:=50, Height:=225)
??chartObj.Chart.SetSourceData Source:=pivotTable.TableRange1
??chartObj.Chart.ChartType = xlBarClustered
??' 格式化圖表
??With chartObj.Chart
????.HasTitle = True
????.ChartTitle.Text = "電腦品牌銷售額分組條形圖"
????.Axes(xlCategory, xlPrimary).HasTitle = True
????.Axes(xlCategory, xlPrimary).AxisTitle.Text = "電腦品牌"
????.Axes(xlValue, xlPrimary).HasTitle = True
????.Axes(xlValue, xlPrimary).AxisTitle.Text = "銷售額"
??End With
End Sub
