Excel多个动态图 [英] Excel multiple dynamic graphs

问题描述

我有一系列数据，我想画几张图。数据按日期排序，并根据先前的条件，以TRUE条件标记出提取的数据。

由于存在大数据集，是否可以生成一系列图表自动？我知道如何使用记录宏功能创建宏，只是我不确定如何过滤数据以制作图表。

在下面的示例数据中，我将有2个图表，日期范围为1-3月1日，另一个为1月6-7日。

例如：

  T / F日期数据
 True 1-Jan 0.1 
 True 2-Jan 0.2 
 True 3-Jan 0.4 
 False 4-Jan 0.2 
 False 5-Jan 0.1 
 True 6-Jan 0.3 
 True 7-Jan 0.4 
  

解决方案

这是可行的。动态创建图表是您应该将其放在库中以供将来参考的其中一个例程。我已经这样做了，代码如下。代码将基于x / y范围和位置创建一个图表。该位置允许在创建图表时排列图表。你将不得不摆动你的范围，以便你可以把子下面的输入需要。这只是一个迭代和跟踪哪些图表创建的问题。

唯一的关键步骤是使用 ChartObjects.Add 创建一个新的图表（带定位数据）然后 SeriesCollection.NewSeries 添加一个系列到图表。

您可以使用位置递增来调用此代码，以创建所需图表并将其放在网格中。

  Sub CreateChartFromRange（xval As Range，yval As Range，location As Integer）
 
 Dim height As Double，width As Double 
 height = 300 
 width = 300 
 
 Dim columns As Integer 
 columns = 3 
 
'假设活动工作表
 Dim cht_obj As ChartObject 
 Set cht_obj = ActiveSheet.ChartObjects.Add（_ 
（location Mod columns）* width，_ 
（location \ columns）* height，_ 
 width，_ 
 height）
 
 Dim ser A s系列
 Set ser = cht_obj.Chart.SeriesCollection.NewSeries 
 
 ser.Values = yval 
 ser.XValues = xval 
 
'假设XY散点键入
 ser.ChartType = xlXYScatter 
 
 End Sub 
  

I have a series of data which I'd like to plot into a few graphs. The data is ordered by date and extracted data tagged with a TRUE condition next to it based on prior conditions.

As there is a large data set, is it possible to generate a series of graphs automatically? I know how to create macros using the record macro function, just that I'm unsure how to filter the data to make the graphs.

In the sample data below, I would have 2 graphs with date ranges 1-3 Jan and another from 6-7 Jan.

E.g:

T/F   Date    Data
True  1-Jan   0.1
True  2-Jan   0.2
True  3-Jan   0.4
False 4-Jan   0.2
False 5-Jan   0.1
True  6-Jan   0.3
True  7-Jan   0.4

解决方案

This is doable. Creating charts dynamically is one of those routines that you should put away in a library for future reference. I have done so and the code is below. The code will create a chart based on x/y ranges and a location. The location allows the charts to be arranged in a grid as they are created. You will have to wrangle your ranges so that you can give the sub below the inputs it needs. This should just be a matter of iterating through and tracking which charts to create.

The only key steps to this are using ChartObjects.Add to create a new chart (with positioning data) and then SeriesCollection.NewSeries to add a series to the chart.

You can call this code several times with location incrementing to create the charts you want and put them in the grid.

Sub CreateChartFromRange(xval As Range, yval As Range, location As Integer)

    Dim height As Double, width As Double
    height = 300
    width = 300

    Dim columns As Integer
    columns = 3

    'assume active sheet
    Dim cht_obj As ChartObject
    Set cht_obj = ActiveSheet.ChartObjects.Add( _
        (location Mod columns) * width, _
        (location \ columns) * height, _
        width, _
        height)

    Dim ser As Series
    Set ser = cht_obj.Chart.SeriesCollection.NewSeries

    ser.Values = yval
    ser.XValues = xval

    'assume XY scatter type
    ser.ChartType = xlXYScatter

End Sub

这篇关于Excel多个动态图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！