I thought over a lot for this case, I need to plot a series as percentage value of another series.
我对这种情况考虑了很多,我需要绘制一个系列作为另一个系列的百分比值。
e.g, series A is the capacity and B is the usage. I want a percentage plot of B on A. Initially I did this by creating a 100% stack chart with series B vs (A-B). I computed (A-B) on a separate column and used that as a series for this purpose. So far so good.
例如,系列A是容量,B是使用。我想在A上绘制B的百分比图。最初,我通过创建一个系列B vs(A-B)的100%叠加图来做到这一点。我在一个单独的列上计算了(A-B),并将其作为一个系列用于此目的。到现在为止还挺好。
But now I want to generate multiple charts like this (say 12 different graphs for 12 months' usage). I have 12 columns of usage data and one column of fixed capacity data.
但现在我想生成这样的多个图表(比如说12个月使用的12个不同的图表)。我有12列使用数据和一列固定容量数据。
Is there a way to auto-generate this chart without having to create 12 intermediate columns with formula. It seems logical that Excel should have a chart type for representing A on B, just as they have one for A,B on A+B.
有没有办法自动生成此图表,而无需使用公式创建12个中间列。似乎合乎逻辑的是Excel应该具有用于在B上表示A的图表类型,就像它们在A + B上具有A,B一样。
Sorry if I am too lengthy or confusing in my description. I did my best here to explain.
对不起,如果我的描述过于冗长或混乱。我在这里尽力解释。
3 个解决方案
#1
1
Actually I am trying to do the same thing I figured it out. If you have Excel 2007 (not sure if this feature is available in older versions). Select all 3 colums, 1 with the names of your variables, total count, and subtotal of what you are counting. Select column chart. Once the chart is created, right click to format the data series. Under series options there is a slidebar for series overlap, change this value to 100% and wahla, the the 2nd series becomes a percent of the total. Hope this helps!
实际上我正在尝试做同样的事情,我想出来了。如果您有Excel 2007(不确定此功能是否在旧版本中可用)。选择所有3个列,1个,包含变量名称,总计数和计数小计。选择柱形图。创建图表后,右键单击以格式化数据系列。在系列选项下,有一个用于系列重叠的滑动条,将此值更改为100%和wahla,第二个系列变为总数的百分比。希望这可以帮助!
#2
0
Excel has common chart types, but even Microsoft doesn't have the resources to provide every possible combination of charting styles. And users are confused enough with the currently available styles.
Excel具有常见的图表类型,但即使Microsoft也没有资源提供图表样式的所有可能组合。用户对当前可用的样式感到困惑。
Excel charts plot the data that they are given. If you need to plot A as a percentage of B, you will need to compute the percentage in a range, and plot this range.
Excel图表绘制了给出的数据。如果需要将A绘制为B的百分比,则需要计算范围内的百分比,并绘制此范围。
#3
-1
The way I would do this is not to modify the ranges on the worksheet at all. Write a new function that creates the modified range data you need so that you can return this and use it inside of ApplyTemplate method when creating the chart. The only downside to this is you would need to run a macro/vsto ribbon button to update the chart. as opposed to getting that behaviour for free with a worksheet bound approach.
我这样做的方法是根本不修改工作表上的范围。编写一个新函数,创建所需的修改范围数据,以便在创建图表时可以返回此函数并在ApplyTemplate方法中使用它。唯一的缺点是你需要运行一个宏/ vsto功能区按钮来更新图表。而不是通过工作表绑定方法免费获得该行为。
#1
1
Actually I am trying to do the same thing I figured it out. If you have Excel 2007 (not sure if this feature is available in older versions). Select all 3 colums, 1 with the names of your variables, total count, and subtotal of what you are counting. Select column chart. Once the chart is created, right click to format the data series. Under series options there is a slidebar for series overlap, change this value to 100% and wahla, the the 2nd series becomes a percent of the total. Hope this helps!
实际上我正在尝试做同样的事情,我想出来了。如果您有Excel 2007(不确定此功能是否在旧版本中可用)。选择所有3个列,1个,包含变量名称,总计数和计数小计。选择柱形图。创建图表后,右键单击以格式化数据系列。在系列选项下,有一个用于系列重叠的滑动条,将此值更改为100%和wahla,第二个系列变为总数的百分比。希望这可以帮助!
#2
0
Excel has common chart types, but even Microsoft doesn't have the resources to provide every possible combination of charting styles. And users are confused enough with the currently available styles.
Excel具有常见的图表类型,但即使Microsoft也没有资源提供图表样式的所有可能组合。用户对当前可用的样式感到困惑。
Excel charts plot the data that they are given. If you need to plot A as a percentage of B, you will need to compute the percentage in a range, and plot this range.
Excel图表绘制了给出的数据。如果需要将A绘制为B的百分比,则需要计算范围内的百分比,并绘制此范围。
#3
-1
The way I would do this is not to modify the ranges on the worksheet at all. Write a new function that creates the modified range data you need so that you can return this and use it inside of ApplyTemplate method when creating the chart. The only downside to this is you would need to run a macro/vsto ribbon button to update the chart. as opposed to getting that behaviour for free with a worksheet bound approach.
我这样做的方法是根本不修改工作表上的范围。编写一个新函数,创建所需的修改范围数据,以便在创建图表时可以返回此函数并在ApplyTemplate方法中使用它。唯一的缺点是你需要运行一个宏/ vsto功能区按钮来更新图表。而不是通过工作表绑定方法免费获得该行为。