使用 VBA 在 Excel 中绘制散点图

Scatter Plot in Excel using VBA

提问by vdhalla

I have one column that contains my x data and two columns that contain my y data. I would like to plot y against x, but the length of each column is dependent on a counter variable, i. I do not have experience with vba, but do with coding. Can someone outline the correct syntax to perform this task?

我有一列包含我的 x 数据和两列包含我的 y 数据。我想针对 x 绘制 y，但每列的长度取决于计数器变量 i。我没有 vba 的经验，但有编码经验。有人可以概述执行此任务的正确语法吗？

Please and Thank you!

谢谢，麻烦您了！

回答by vdhalla

You could goto your 'Developer' tab, and record a macro. While in record mode, create the graph using the data and specifications you desire.

您可以转到“开发人员”选项卡，然后录制一个宏。在记录模式下，使用您想要的数据和规格创建图表。

End recording of the macro, and you will then have a template of VBA code (ALT+F11) to work with as a foundation.

结束宏的录制，您将拥有一个 VBA 代码模板 (ALT+F11) 作为基础。

回答by Rick supports Monica

It sounds like the best solution is to simply make the X-Y scatter plot ranges longer than you ever expect the data to be (Excel will ignore the blank rows).

听起来最好的解决方案是简单地使 XY 散点图范围比您预期的数据更长（Excel 将忽略空白行）。

But if you want to get fancy, you can populate your scatter plot data with dynamic named ranges that automatically adjust to the length of your data without bothering with VBA:

但是，如果您想变得有趣，您可以使用动态命名范围填充散点图数据，这些范围会自动调整到您的数据长度，而无需使用 VBA：

1. Create a dynamic named range (AKA named formula) representing your X data column.

   Example (assuming column A, and data starting at A1):

   =$A:INDEX($A:$A00,MATCH(TRUE,ISBLANK(Sheet1!$A:$A00),0)-1)
   

   Change $A$1so that it corresponds to the first row of your data. Change $A$1000to a row number that is longer than you ever expect your data to be.

   Call the named formula above "XColumn" or something similar. To make a named range, do Formulas->Define Name.

2. Set the X range of your scatter plot equal to:

   =Sheet1!XColumn
   

   Note that Excel's plotting window will give an error (devoid of any helpful information regarding how to fix the problem!) if you try to input the named range without the sheet name, even if the named range is scoped to the entire workbook, and even if it's on the same worksheet! -- Annoying, right?

3. Do this for each column and scatter plot range (Y1 = Y1Column, Y2 = Y2Column, etc.).

1. 创建一个代表 X 数据列的动态命名范围（也称为命名公式）。

   示例（假设 columnA和数据从 开始A1）：

   =$A:INDEX($A:$A00,MATCH(TRUE,ISBLANK(Sheet1!$A:$A00),0)-1)
   

   更改$A$1以使其对应于数据的第一行。更改$A$1000为比您预期的数据更长的行号。

   调用上面的命名公式“ XColumn”或类似的东西。要创建命名范围，请执行Formulas-> Define Name。

2. 将散点图的 X 范围设置为等于：

   =Sheet1!XColumn
   

   请注意，如果您尝试输入没有工作表名称的命名范围，即使命名范围限定在整个工作簿中，Excel 的绘图窗口也会出现错误（没有关于如何解决问题的任何有用信息！）如果它在同一个工作表上！——烦人吧？

3. 对每一列和散点图范围（Y1 = Y1Column、Y2 = Y2Column 等）执行此操作。

Note that this will not work correctly if your columnar data contains text, blanks, errors, etc., but the method can be modified to handle these issues.

请注意，如果您的柱状数据包含文本、空白、错误等，这将无法正常工作，但可以修改该方法以处理这些问题。

To test and make sure your dynamic named ranges are being created as expected, in any cell you can enter:

要测试并确保按预期创建动态命名范围，您可以在任何单元格中输入：

=SUMPRODUCT(XColumn)

Then do Formulas-->Evaluate Formula-->Evaluateto make sure the XColumnarray contains the data you want it to contain.

然后执行Formulas--> Evaluate Formula-->Evaluate以确保XColumn数组包含您希望它包含的数据。

---

---

Optional tip: create another named range called FirstColumnset to the location of the first column, e.g. $A:$A. Make anothernamed range called FirstRowand set it to the location of the first row, e.g. $1:$1. Make yet anothernamed range called MaxRowand set it to the maximum length you ever expect your data to be, e.g. $1000:$1000. Finally:

可选提示：创建另一个名为FirstColumnset 的命名范围以设置第一列的位置，例如$A:$A. 让另一个称为命名范围FirstRow，并将其设置到第一排的位置，例如$1:$1。让另一个称为命名范围MaxRow，并将其设置为你指望你的数据是，例如最大长度$1000:$1000。最后：

• Replace all instances of $A$1above with:

  INDEX(FirstRow,0,COLUMN(FirstColumn)+<DATA TABLE COLUMN NUMBER - 1>)

• Replace all instances of $A$1000with:

  INDEX(MaxRow,0,COLUMN(FirstColumn)+<DATA TABLE COLUMN NUMBER - 1>)

• 将$A$1上述所有实例替换为：

  INDEX(FirstRow,0,COLUMN(FirstColumn)+<DATA TABLE COLUMN NUMBER - 1>)

• 将所有实例替换为$A$1000：

  INDEX(MaxRow,0,COLUMN(FirstColumn)+<DATA TABLE COLUMN NUMBER - 1>)

<DATA TABLE COLUMN NUMBER - 1>will be different for each named formula (e.g. 0for XColumn, 1for Y1Column, 2for Y2Column, etc etc).

<DATA TABLE COLUMN NUMBER - 1>每个命名公式都会有所不同（例如0for XColumn、1for Y1Column、2forY2Column等）。

Now if you ever want to create new data tables and scatter plots in different locations, instead of doing all of the work over again you can just copy and paste your named ranges and you only have to change one or two things instead of 15! Additionally, if the data ever gets longer than you expected, you only have to change one thing instead of 3.

现在，如果您想在不同的位置创建新的数据表和散点图，而不是重新做所有的工作，您只需复制和粘贴您的命名范围，您只需更改一两件事而不是 15！此外，如果数据变得比您预期的要长，您只需更改一件事而不是三件事。

回答by guitarthrower

Excel's built-in Tablefunctionality is a great way to automatically increase a range-size when new data is added. Simply select your data, then either press Ctrl + tor go to Insert > Table. Then when you reference your new Tablefrom the Chartit will increase as your data does.

Excel 的内置Table功能是在添加新数据时自动增加范围大小的好方法。只需选择您的数据，然后按Ctrl + t或转到Insert > Table。然后，当您Table从引用您的 new时，Chart它会随着您的数据而增加。