前言：
如今咱们对“excel如何在柱状图中添加横线”可能比较注意，小伙伴们都想要知道一些“excel如何在柱状图中添加横线”的相关文章。那么小编同时在网络上搜集了一些对于“excel如何在柱状图中添加横线””的相关资讯，希望我们能喜欢，同学们一起来学习一下吧！执行方框中的两个函数，可以根据左侧选中的单元格区域，绘制图表。执行后，大致的结果如下。
根据选中单元格区域按行绘制线性图
Sub 根据选中单元格区域按行绘制线性图() Dim rng As Range Dim ws As Worksheet Dim chartObj As ChartObject Dim series As series Dim xValues As Range Dim yValues As Range Dim row As Range Dim i As Integer Dim showDataLabels As VbMsgBoxResult Dim showLastPointLabels As VbMsgBoxResult Dim lastPointIndex As Integer Dim lastYValue As Variant Set rng = Selection Set ws = ActiveSheet If rng Is Nothing Then MsgBox "请选择一个有效的单元格区域", vbExclamation Exit Sub End If showDataLabels = MsgBox("是否显示数据标签？", vbYesNo + vbQuestion, "显示数据标签") Set xValues = rng.Rows(1).Offset(0, 1).SpecialCells(xlCellTypeVisible) Set chartObj = ws.ChartObjects.Add(Left:=100, Top:=50, Width:=500, Height:=300) With chartObj.Chart Dim msgResult msgResult = MsgBox("是否创建线形图Line?如否，则为柱形图Column。", vbInformation + vbYesNoCancel, "确认") If msgResult = vbYes Then .ChartType = xlLine ElseIf msgResult = vbNo Then .ChartType = xlColumnClustered Else Exit Sub End If Dim seriesCount As Integer seriesCount = 0 For i = 2 To rng.Rows.Count Set row = rng.Rows(i) If Not row.Hidden Then Set yValues = row.Offset(0, 1).SpecialCells(xlCellTypeVisible) Set series = .SeriesCollection.NewSeries seriesCount = seriesCount + 1 series.xValues = xValues series.Values = yValues series.Name = row.Cells(1, 1).Value If showDataLabels = vbYes Then series.HasDataLabels = True End If End If Next i If seriesCount > 1 Then .HasLegend = True .Legend.Position = xlLegendPositionRight Else .Legend.Delete End If With .Axes(xlCategory, xlPrimary) .HasTitle = False .TickLabels.NumberFormat = "General" End With ' 删除横线和竖线 .Axes(xlCategory, xlPrimary).MajorGridlines.Delete .Axes(xlCategory, xlPrimary).MinorGridlines.Delete .Axes(xlValue, xlPrimary).MajorGridlines.Delete .Axes(xlValue, xlPrimary).MinorGridlines.Delete End With End Sub根据选中单元格区域按列绘制线性图Sub 根据选中单元格区域按列绘制线性图() Dim rng As Range Dim ws As Worksheet Dim chartObj As ChartObject Dim series As series Dim xValues As Range Dim yValues As Range Dim col As Range Dim i As Integer Dim showDataLabels As VbMsgBoxResult Dim showLastPointLabels As VbMsgBoxResult Set rng = Selection Set ws = ActiveSheet If rng Is Nothing Then MsgBox "请选择一个有效的单元格区域", vbExclamation Exit Sub End If showDataLabels = MsgBox("是否显示数据标签？", vbYesNo + vbQuestion, "显示数据标签") Set xValues = rng.Columns(1).Offset(1, 0).SpecialCells(xlCellTypeVisible) Set chartObj = ws.ChartObjects.Add(Left:=100, Top:=50, Width:=500, Height:=300) With chartObj.Chart Dim msgResult msgResult = MsgBox("是否创建线形图Line?如否，则为柱形图Column。", vbInformation + vbYesNoCancel, "确认") If msgResult = vbYes Then .ChartType = xlLine ElseIf msgResult = vbNo Then .ChartType = xlColumnClustered Else Exit Sub End If Dim seriesCount As Integer seriesCount = 0 For i = 2 To rng.Columns.Count Set col = rng.Columns(i) If Not col.Hidden Then Set yValues = col.Offset(1, 0).SpecialCells(xlCellTypeVisible) Set series = .SeriesCollection.NewSeries seriesCount = seriesCount + 1 series.xValues = xValues series.Values = yValues series.Name = rng.Cells(1, i).Value If showDataLabels = vbYes Then series.HasDataLabels = True End If End If Next i If seriesCount > 1 Then .HasLegend = True .Legend.Position = xlLegendPositionRight Else .Legend.Delete End If With .Axes(xlCategory, xlPrimary) .HasTitle = False .TickLabels.NumberFormat = "General" End With ' 删除横线和竖线 .Axes(xlCategory, xlPrimary).MajorGridlines.Delete .Axes(xlCategory, xlPrimary).MinorGridlines.Delete .Axes(xlValue, xlPrimary).MajorGridlines.Delete .Axes(xlValue, xlPrimary).MinorGridlines.Delete End WithEnd Sub 版权声明：
本站文章均来自互联网搜集，如有侵犯您的权益，请联系我们删除，谢谢。
标签： #excel如何在柱状图中添加横线
