在条形图中绘制线条？ [英] Draw lines in otherwise bar chart?

问题描述

VBA受害者！

I目前我正在为不同类别（AD）生成开放缺陷图表。现在，每个类别都有一个阈值（A类和B类为零）。

I currently am generating a graph of open defects og for different categories (A-D). Now, there is a threshold for each category (being zero for category A and B).

请提出一种方法，我可以将这些阈值作为图表中的一条线绘制？请参阅附图（我在Powerpoint中所做的）。我目前的代码是：

Please propose a way I can draw these thresholds as a line in the chart? See attaches illustration (which I did in Powerpoint). My current code is:

   公共功能popup_graph（urliurli As String）

    Dim cht As Chart

   

       设置url_defect_array = defects_of_category（urliurli）

        Dim ser As系列

       设置cht =表格（"新报告"）。ChartObjects.Add（0,0,300,300）.Chart

       使用cht

            .ChartType = xlColumnStacked

           设置ser = .SeriesCollection.NewSeries

           使用ser

               .Name ="打开< b $ b               .XValues =阵列（" A"，" B"，" C"，" d"）

&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; .Values =阵列（url_defect_array（QUOT; ARED"），url_defect_array（QUOT;育成"），url_defect_array（QUOT; CRed工作"），url_defect_array（QUOT; DRED"））

&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; .Format.Fill.ForeColor.RGB = RGB（255，0，0）

&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;结束与$
          设置ser = .SeriesCollection.NewSeries

           使用ser

               .Name =" ASK"

               .XValues =阵列（" A"，" B"，" C"，" d"）

&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; .Values =阵列（url_defect_array（QUOT; ABlue"），url_defect_array（QUOT; BBlue"），url_defect_array（QUOT; CBlue"），url_defect_array（QUOT; DBLUE"））

&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; .Format.Fill.ForeColor.RGB = RGB（0，0，255）

&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;结束与$
          设置ser = .SeriesCollection.NewSeries

           使用ser

               .Name =" FIX"

               .XValues =阵列（" A"，" B"，" C"，" d"）

&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; .Values =阵列（url_defect_array（QUOT; AAmber"），url_defect_array（QUOT;班伯"），url_defect_array（QUOT;外倾角"），url_defect_array（QUOT; DAmber"））

&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; .Format.Fill.ForeColor.RGB = RGB（255，215，0）

&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;结束与$
           charto = .Name

       结束与$
      设置Shp = ActiveSheet.Shapes（cht.Parent.Name）

        SetChartPosition

   结束功能

    Public Sub SetChartPosition（）

       

        Dim intLeft As Integer

        Dim intTop As Integer

        intLeft = Application.Selection.Offset（0,10）.Left

        'intLeft = Range（" e2"）。Offset（0,10）.Left

        intTop = Application.Selection.Offset（0,30）.Top

        'intTop = Range（" e2"）。Offset（0,30）.Top

        Shp.Left = intLeft

        Shp.Top = intTop

        Shp.IncrementTop 22

       

   结束Sub

    Public Function popup_graph(urliurli As String)
    Dim cht As Chart
   
        Set url_defect_array = defects_of_category(urliurli)
        Dim ser As Series
        Set cht = Sheets("New Report").ChartObjects.Add(0, 0, 300, 300).Chart
        With cht
            .ChartType = xlColumnStacked
            Set ser = .SeriesCollection.NewSeries
            With ser
               .Name = "Open"
               .XValues = Array("A", "B", "C", "D")
               .Values = Array(url_defect_array("ARed"), url_defect_array("BRed"), url_defect_array("CRed"), url_defect_array("DRed"))
               .Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
            End With
            Set ser = .SeriesCollection.NewSeries
            With ser
               .Name = "ASK"
               .XValues = Array("A", "B", "C", "D")
               .Values = Array(url_defect_array("ABlue"), url_defect_array("BBlue"), url_defect_array("CBlue"), url_defect_array("DBlue"))
               .Format.Fill.ForeColor.RGB = RGB(0, 0, 255)
            End With
            Set ser = .SeriesCollection.NewSeries
            With ser
               .Name = "FIX"
               .XValues = Array("A", "B", "C", "D")
               .Values = Array(url_defect_array("AAmber"), url_defect_array("BAmber"), url_defect_array("CAmber"), url_defect_array("DAmber"))
               .Format.Fill.ForeColor.RGB = RGB(255, 215, 0)
            End With
            charto = .Name
        End With
        Set Shp = ActiveSheet.Shapes(cht.Parent.Name)
        SetChartPosition
    End Function
    Public Sub SetChartPosition()
       
        Dim intLeft As Integer
        Dim intTop As Integer
        intLeft = Application.Selection.Offset(0, 10).Left
        'intLeft = Range("e2").Offset(0, 10).Left
        intTop = Application.Selection.Offset(0, 30).Top
        'intTop = Range("e2").Offset(0, 30).Top
        Shp.Left = intLeft
        Shp.Top = intTop
        Shp.IncrementTop 22
       
    End Sub

BW Peter B Hansen（通过REST API / JSON从不同的敏捷开发工具和测试工具工具中提取数据并在Excel中显示给我的同事敬畏）

BW Peter B Hansen (extracting data via REST API/JSON from different agile dev tools and test tools tools and displaying it in Excel to the awe of my colleagues)

推荐答案

您好，

您是否尝试通过添加NewSeries或其他方法手动在Excel中绘制线条而不是在图表中插入线形？

Do you try to draw the line in Excel manually by adding NewSeries or some other methods instead of inserting line shapes in the chart?

我认为获得所需的唯一方法是在图表中插入线条形状。但是，如果更改了系列值，则会更改轴，但线条形状是固定的，因此解决方案没有意义。

I think the only way to get your want is to insert line shapes in the chart. However, if the series value are changed, the axis would be changed but the line shapes are fixed, so the solution makes no sense.

我们无法获得标记的点在下面的图片中。 Excel对象模型不提供任何方法或属性。所以在我看来，我们无法以编程方式获得您期望的结果。

We are unable to get the point I marked in the following picture. Excel Object Model doesn't provide any methods or properties for that. So in my opinion, we could not get your expected result programmatically.

问候，

Celeste

这篇关于在条形图中绘制线条？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！