如何以编程方式在Excel中使用图表中的第n个单元格 [英] How to Use Every nth Cell in a Chart in Excel Programmatically

问题描述

一个输入文件大约有500,000行.我要尝试做的主要事情是在整个单元范围内仅绘制出500,000个单元中的10,000个.有没有办法以编程方式使用图表中的第50个单元格?如果不是，最好的方法是将第50个单元格复制到一个新位置，然后绘图吗?

An input file has about 500,000 rows. The main thing I am trying to do is to plot only 10,000 of the 500,000 cells over the whole range of cells. Is there a way to use every 50th cell in the chart programmatically? If not, is the best approach to copy every 50th cell to a new location, and then plot?

推荐答案

对于相对少量的数据，可以将x和y-数据设置为单独的单元格列表.

For a relatively small amout of data, it is possible to set the x and y - Data to a list of separate cells.

最好在VBA中以编程方式完成此操作.电子表格已重命名为"A"，以使数据字符串尽可能的小.循环中to和step的值可以更改:

This is best done programatically in VBA. The Spreadsheet was renamed to 'A' to keep the Data-String as small as possible. The values for to and step in the loop may be varied:

[...]
Dim xS As String
Dim yS As String
xS = "="
yS = "="

For i = 1 To 23000 step 50
    If i > 1 Then
        xS = xS & ","
        yS = yS & ","
    End If
    xS = xS & "A!$A$" & CStr(i)
    yS = yS & "A!$B$" & CStr(i)
Next

ActiveChart.FullSeriesCollection(1).XValues = xS
ActiveChart.FullSeriesCollection(1).Values = yS
[...]

但是，我不确定XValues String的最大长度是多少.最初的测试显示至少4032个字符.这将使您每个Chart-SeriesCollection约有350至450个值.如果添加约30个系列，这可能是容纳10000个值对的解决方案. 如果您更改日期，这会很麻烦并且值得.

I am not sure, however, what the maximul length of the XValues String is. First tests showed at least 4032 characters. This would bring you to an amout of about 350 to 450 values per Chart-SeriesCollection. If You add about 30 series-collection, this might be a solution to hold your 10000 value-pairs. This is a bit cumbersome and only worth it, if your date changes.

如果您打算绘制静态数字列表，则最好以编程方式将单元格复制到第二个电子表格，然后对其进行绘制:

If You aim to plot a static list of numbers it is best to copy cells programmatically to a second spreadsheet and then plot them:

[...]
for i = 1 to 500000 step 50
   destinationSheet.range(1,i/1000).value = sourceSheet.range(1,i).value
next
[...]

这篇关于如何以编程方式在Excel中使用图表中的第n个单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！