是否可以在excel图轴上获得点的位置

I'm not sure this is possible but thought this was the best place to ask.

我不确定这是可能的,但认为这是最好的地方。

Is it posible to get the position of a series value on a graph in excel?

在excel的图表上获取系列值的位置是否可行?

For example, if I have a line graph in excel that has time along the x axis, is it possible to (using VBA) get the position of a specific point on that axis.

例如,如果我在excel中有一个沿x轴有时间的折线图,是否可以(使用VBA)获取该轴上特定点的位置。

What I am trying to do is have a vertical line that is can be positioned based on a date entered by the user.

我想要做的是有一条垂直线,可以根据用户输入的日期进行定位。

like this

Where the green line could be positioned by entering in a date (rather than just being manually moved) (or also it could be set to automatically move to the current date etc).

可以通过输入日期(而不仅仅是手动移动)来定位绿线(或者也可以将其设置为自动移动到当前日期等)。

I was then thinking that if the position is on the graph is queryable, then I can just access the line object and move it to any position I wanted through VBA.

我当时正在考虑如果图表上的位置是可查询的,那么我可以访问线对象并将其移动到我想通过VBA的任何位置。

Any Ideas? or is this just not possible?

有任何想法吗?或者这是不可能的?

1 个解决方案

#1

The "cleanest" way to do this is to add the line to the chart as a new series. In that way, Excel handles all of the positioning and your work is simplified. To get a vertical line on a chart, there are a number of options. I prefer this route:

“最干净”的方法是将该行添加到图表中作为新系列。通过这种方式,Excel可以处理所有定位,简化了您的工作。要在图表上获得垂直线,有许多选项。我更喜欢这条路线:

1. Create a small 2x2 area with two dates and two values

创建一个包含两个日期和两个值的小型2x2区域

2. Add in the date or x-axis value you want the line at (E3 in image). You can use =TODAY() here or some manually entered value.

在行所在的日期或x轴值中添加(图像中的E3)。您可以在此处使用= TODAY()或手动输入的值。

3. Set the second x-axis value equal to the first

将第二个x轴值设置为等于第一个

4. Use MAX and MIN on the data to get the values for each date. You can also use 0 and 1 and a secondary axis, but I think MAX/MIN is easier.

在数据上使用MAX和MIN来获取每个日期的值。您也可以使用0和1以及辅助轴,但我认为MAX / MIN更容易。

5. Add the data to the chart and format as a marker with straight line.

将数据添加到图表并格式化为带有直线的标记。

Formulas

• E3: =TODAY()
• E4: =E3
• F3: =MIN(C3:C27)
• F4: =MAX(C3:C27)

Result and chart data series for vertical line

垂直线的结果和图表数据系列