为具有相同x值但不同y值的多个图形创建Excel宏 [英] Creating an Excel macro for multiple graphs with the same x-values but different y-values

问题描述

我试图通过宏自动化在Excel中创建图形。
基本上我有很多数据（从列C到列AQ开始的41列），每个数据包含另一个名称。在一列中有两个不同行部分的两个序列（一个从第3行到第8行，一个从第12行到第17行）。

I'm trying to automate the creation of graphs in Excel by means of a macro. Basically I've got a lot of data (41 columns, starting from column C to column AQ) each one containing another 'name'. In one column there are two series in two different row sections (one from row 3 to 8, one from row 12 to 17).

我每列需要一个图形，所以总共有41个图形。每个图包含两个系列。列B是X轴上的值，这对于每个图形都是一样的。

I want one graph per column, so in total I'll have 41 graphs. Each graph contains the two series. In column B are the values on the X axis, and this is for every graph the same.

目的是为y轴值创建一个下一个循环，并让它们全部创建。

The purpose is to create a for next loop with the y-axis values, and have them all created at once.

我已经尝试编码第一部分（只是在x轴上创建一个图形，从B3：B8和on y轴的值来自C3：C8，第二个系列只是几行。

I've tried coding the first part (just creating one graph with on x-axis the values from B3:B8 and on y-axis the values from C3:C8, and the second series just a few rows down.

我收到错误，我不明白为什么，我尝试了不同的方法，但它从不起作用。错误位于此行中：ActiveChart.SeriesCollection（1）.Values = Range（Cells（3，3），Cells（8,3））。Value

I get an error and I don't understand why, I've tried different approaches but it never works. The error situates in this line: ActiveChart.SeriesCollection(1).Values = Range(Cells(3, 3), Cells(8, 3)).Value

有没有人有任何想法可能是错的或如何解决这个问题？

Does anyone have any ideas what may be wrong or how to tackle this problem?

Sub Macro5()

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).Name = "='Blad1'!$A$1"
    ActiveChart.SeriesCollection(1).XValues = "='Blad1'!$B$3:$B$8"
    ActiveChart.SeriesCollection(1).Values = Range(Cells(3, 3), Cells(8, 3)).Value
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(2).Name = "='Blad1'!$A$10"
    ActiveChart.SeriesCollection(2).XValues = "='Blad1'!$B$12:$B$17"
    ActiveChart.SeriesCollection(2).Values = Range(Cells(12, 3), Cells(17, 3)).Value
    ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
    ActiveChart.Axes(xlValue).MajorGridlines.Select
    Selection.Delete
    ActiveChart.SeriesCollection(1).Trendlines.Add
    ActiveChart.SeriesCollection(1).Trendlines(1).Select
    Selection.DisplayEquation = True
    Selection.DisplayRSquared = True    
    ActiveChart.SeriesCollection(2).Trendlines.Add
    ActiveChart.SeriesCollection(2).Trendlines(1).Select
    Selection.DisplayEquation = True
    Selection.DisplayRSquared = True

End Sub

推荐答案

范围不合格，数组你将其转换为使用.Value可能不被Excel了解。

Range isn't qualified, and the array you convert it to using .Values is probably not understood by Excel.

更改

ActiveChart.SeriesCollection(1).Values = Range(Cells(3, 3), Cells(8, 3)).Value

to

ActiveChart.SeriesCollection(1).Values = Worksheets("Blad1").Range(Cells(3, 3), Cells(8, 3))

或

ActiveChart.SeriesCollection(1).Values = "='Blad1'!$C$3:$C$8"

这篇关于为具有相同x值但不同y值的多个图形创建Excel宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！