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

问题描述

我有一系列数据，我想将它们绘制成几个图表.数据按日期排序，提取的数据根据​​先前条件在旁边标有 TRUE 条件.

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.

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

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

例如:

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

推荐答案

这是可行的.动态创建图表是您应该放在库中以备将来参考的例程之一.我已经这样做了，代码如下.该代码将根据 x/y 范围和位置创建图表.该位置允许图表在创建时排列在网格中.您将不得不调整您的范围，以便您可以在其需要的输入下方提供 sub.这应该只是迭代和跟踪要创建的图表的问题.

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.

唯一的关键步骤是使用 ChartObjects.Add 创建一个新图表(带有定位数据)，然后使用 SeriesCollection.NewSeries 向图表添加一个系列.

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.

您可以多次调用此代码，location 递增以创建您想要的图表并将它们放入网格中.

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屋！