创建没有数据源的多个Excel图表的平均值 [英] Create an average of multiple excel chart without the data source

问题描述

思想刺激的问题（至少对我来说）。通常在创建图表时，您拥有您的数据，然后使用它来创建图表。如果您将图表复制到另一个工作簿，图表上的值保持不变，但新工作簿中存在无可用数据源。我想创建一个新的图表，它是多个复制图表的平均值。这是可能在excel / vba？

Thought provoking problem (for me at least). Normally when creating a chart you have your data and then use it to create the chart. If you then copy the chart to another workbook, the values on the chart stay the same but there is "no available" data source in the new workbook. I want to create a new chart which is the average of multiple copied charts. Is this possible in excel/vba?

我甚至不能尝试录制一个宏并从那里去，因为我不知道是否可以平均多个图表。

I can't even try recording a macro and going from there as I don't know if its possible to "average" multiple charts.

编辑：做了一些更多的思考，我想如果是可能的，而不是提取数据到每张图表的新工作表，是可能平均数据萃取。如果在图表上，您可以右键 - >选择数据，查看对原始工作表中数据的引用。是否有可能平均这和打印只是结果，而不必存储所有的数据？如果可能，仍然更容易直接平均图表！

EDIT : Been doing some more thinking and am thinking if it is possible to instead of extract data into a new sheet for each chart, is it possible to average data upon extraction. If on the chart you Right click -> select data, you can see the reference to the data in the original worksheet. Is it possible to average this and print just the outcome without having to store all the data? Would still be easier to directly average charts if possible!

编辑2：我重新制作了我的数据模板，以便匹配的时间序列数据范围不再是一个问题。同样根据平均值的平均值的评论，数据都是相等的权重和数量，所以这不应该是一个问题。它只是来自：是有办法采取多个图表（或图形）的面值，并平均他们形成一个新的图表（或图形），而没有海量数据操作在原始（或新）工作簿？

EDIT 2: I have reworked my data template so that matching time series data ranges is no longer an issue. Also as per the comment on averages-of-averages, the data is all of equal weight and quantity so this should not be a problem. It literally just comes down to: is there a way to take the face values of multiple charts (or graphs), and average them to form a new chart (or graph) without massive data manipulation in the original (or new) workbook?

赏金摘要（带有圆号）：在VBA中寻找一种快速的方式来创建一个图表， 。我有10种类型的图表在50个单独的工作表。我想要创建一个总结表有10个图表，平均来自相同的图表在其他50张表的数据。关键困难在于，这是一个所有图表都复制到其中的演示工作簿，每个图表的所有数据都在不同的工作簿中。

Bounty Summary (with round numbers): Looking for a quick'ish way in VBA to create a chart which is the average of multiple charts. I have 10 types of chart on 50 separate worksheets. I'm looking to create a summary sheet with 10 charts that average the data from the same respective chart on the other 50 sheets. The key difficulty is that this is a 'presentation Workbook that all charts are copied into, all the data for each chart is in a different workbook.

EDIT 4 ：数据存储在主数据表中并排的多个时间序列表中。它似乎是在目前（根据​​斯科特的评论），没有办法直接操纵，最可能的解决方案将是数据提取/操纵。搜索仍然继续：）

EDIT 4: Data is stored in multiple time series tables that are all side by side in a main data sheet. It appears to be at the moment (as per Scott's comment) that there is no way to directly manipulate and the most likely solution will be that of data extraction/manipulation. Search still continues though :)

推荐答案

我想创建一个新的图表，多个复制的图表。这是可能在excel / vba吗？

I want to create a new chart which is the average of multiple copied charts. Is this possible in excel/vba?

这是可能的，但是这个任务没有魔术公式。

It is possible but there is no magic formula for this task.

我将首先迭代每个工作簿，每个工作表，每个形状并聚合数组中的值，每个类型的图表有一个数组。
为了避免存储所有的数据，平均值必须在每次提取时计算，如下所示：

I would first iterate each workbook, each worksheet, each shape and aggregate the values in an array, with one array for each type of chart. To avoid storing all the data, the averages will have to be computed upon each extraction like this:

Average = ((PreviousAverage * N) + Value) / (N + 1)

在仪表板中，我将从聚合工作簿中复制缺少的图表，并重用已存在的图表。
这样，如果所有图表都已存在，那么仪表板的自定义将保持不变。

Next, to expose the data in your dashboard, I would duplicate the missing charts from the aggregated workbooks and reuse the one already present. This way, the customisation of the dashboard will remain untouched if all the charts are already there.

最后，我将直接在图表中插入聚合值

Finally, I would directly insert the aggregated values in the charts without storing them in a sheet.

我组装了一个工作示例，聚合当前工作簿中的所有图表，并在Dashboard工作表中显示结果： / p>

I've assemble a working example that aggregates all the charts from the current workbook and displays the results in the sheet "Dashboard":

Sub AgregateCharts()

  Dim ws As Worksheet, wsDashboard As Worksheet, sh As Shape, ch As chart
  Dim xValues(), yValues(), yAverages(), weight&, key
  Dim items As Scripting.dictionary, item As Scripting.dictionary
  Set items = CreateObject("Scripting.Dictionary")

  ' define the dashboard sheet
  Set wsDashboard = ThisWorkbook.sheets("Dashboard")

  ' disable events
  Application.ScreenUpdating = False
  Application.EnableEvents = False

  ' iterate worksheets  '
  For Each ws In ThisWorkbook.Worksheets
    ' if not dashboard  '
    If Not ws Is wsDashboard Then
      ' iterate shapes      '
      For Each sh In ws.Shapes
        If sh.type = msoChart Then ' if type is chart    '

          Debug.Print "Agregate " & ws.name & "!" & sh.name

          ' check if that type of chart was previously handled
          If Not items.Exists(sh.chart.chartType) Then

            ' extract the values from the first serie
            xValues = sh.chart.SeriesCollection(1).xValues
            yValues = sh.chart.SeriesCollection(1).values

            ' duplicate the chart if it doesn't exists in the dashboard
            Set ch = FindChart(wsDashboard, sh.chart.chartType)
            If ch Is Nothing Then
              Set ch = DuplicateChart(sh.chart, wsDashboard)
            End If

            ' store the data in a new item   '
            Set item = New Scripting.dictionary
            item.Add "Chart", ch
            item.Add "Weight", 1   ' number of charts used to compute the averages
            item.Add "XValues", xValues
            item.Add "YAverages", yValues
            items.Add ch.chartType, item  ' add the item to the collection  '

          Else

            ' retreive the item for the type of chart  '
            Set item = items(sh.chart.chartType)
            weight = item("Weight")
            yAverages = item("YAverages")

            ' update the averages : ((previous * count) + value) / (count + 1)  '
            yValues = sh.chart.SeriesCollection(1).values
            UpdateAverages yAverages, weight, yValues

            ' save the results  '
            item("YAverages") = yAverages
            item("Weight") = weight + 1

          End If

        End If
      Next
    End If
  Next

  ' Fill the data for each chart in the dashboard
  For Each key In items
    Set item = items(key)
    Set ch = item("Chart")

    ' Add the computed averages to the chart
    ch.SeriesCollection(1).xValues = "={" & Join(item("XValues"), ";") & "}"
    ch.SeriesCollection(1).values = "={" & Join(item("YAverages"), ";") & "}"
  Next

  ' restore events
  Application.EnableEvents = True
  Application.ScreenUpdating = True

End Sub

Private Sub UpdateAverages(averages(), weight&, values())
  Dim i&
  For i = LBound(averages) To UBound(averages)
    averages(i) = (averages(i) * weight + values(i)) / (weight + 1)
  Next
End Sub

Private Function DuplicateChart(ByVal source As chart, target As Worksheet) As chart

  ' clone the chart to the target
  source.Parent.Copy
  target.Paste
  Application.CutCopyMode = 0

  ' clear the data '
  With target.Shapes(target.Shapes.count).chart.SeriesCollection(1)
    Set DuplicateChart = .Parent.Parent
    .name = CStr(.name)
    .xValues = "={0}"
    .values = "={0}"
  End With

End Function

Private Function FindChart(source As Worksheet, chartType As XlChartType) As chart

  ' iterate each shape in the worksheet to fin the corresponding type
  Dim sh As Shape
  For Each sh In source.Shapes
    If sh.type = msoChart Then
      If sh.chart.chartType = chartType Then
        Set FindChart = sh.chart
        Exit Function
      End If
    End If
  Next

End Function

这篇关于创建没有数据源的多个Excel图表的平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！