求VBA編碼 點擊分數段統計 自動繪制出 壹個分數段統計表

Range("I1") = "分數段": Range("J1") = "人數"

Range("I2") = "40-50": Range("I3") = "50-60"

Range("I4") = "60-70": Range("I5") = "70-80"

Range("I6") = "80-90": Range("I7") = "90-100"

Range("J2").Formula = "=COUNTIFS(G:G," & """" & ">=40" & """" & ",G:G," & """" & "<50" & """" & ")"

Range("J3").Formula = "=COUNTIFS(G:G," & """" & ">=50" & """" & ",G:G," & """" & "<60" & """" & ")"

Range("J4").Formula = "=COUNTIFS(G:G," & """" & ">=60" & """" & ",G:G," & """" & "<70" & """" & ")"

Range("J5").Formula = "=COUNTIFS(G:G," & """" & ">=70" & """" & ",G:G," & """" & "<80" & """" & ")"

Range("J6").Formula = "=COUNTIFS(G:G," & """" & ">=80" & """" & ",G:G," & """" & "<90" & """" & ")"

Range("J7").Formula = "=COUNTIFS(G:G," & """" & ">=90" & """" & ",G:G," & """" & "<=100" & """" & ")"

ActiveSheet.Shapes.AddChart.Select

With ActiveChart

.ChartType = xlColumnClustered

.SeriesCollection.NewSeries

.SeriesCollection(1).Name = "分數段"

.SeriesCollection(1).Values = "=Sheet2!$J$2:$J$7"

.SeriesCollection(1).XValues = "=Sheet2!$I$2:$I$7"

.SeriesCollection(1).ApplyDataLabels

.ChartTitle.Format.TextFrame2.TextRange.Characters.Text = "統計表"

End With

End Sub