如何在Excel中创建百分比条形图，类似于“100%堆积柱形图”但只有一个变量相对于总价值
对于这种情况，我想了很多，我需要将一个系列绘制为另一个系列的百分比值。
例如，A系列是容量，B系列是用途。我想要 B 对 A 的百分比图。最初，我通过创建 B 系列与 (AB) 的 100% 堆栈图来实现此目的。我在一个单独的列上计算了 (AB)，并将其作为一个系列用于此目的。到目前为止，一切都很好。
但现在我想生成多个这样的图表（比如 12 个月的使用情况的 12 个不同的图表）。我有 12 列使用数据和一列固定容量数据。
有没有办法自动生成此图表，而无需使用公式创建 12 个中间列。 Excel 应该有一种图表类型来表示 B 上的 A，这似乎是合乎逻辑的，就像他们有一种用于表示 A+B 上的 A、B 的图表类型一样。
抱歉，如果我的描述太长或令人困惑。我在这里尽力解释一下。
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.
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.
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.
Sorry if I am too lengthy or confusing in my description. I did my best here to explain.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
事实上，我正在尝试做我想出的同样的事情。如果您有 Excel 2007（不确定此功能在旧版本中是否可用）。选择所有 3 列，其中 1 列包含变量名称、总计数以及所计数内容的小计。选择柱形图。创建图表后，右键单击以格式化数据系列。在系列选项下，有一个用于系列重叠的滑动条，将此值更改为 100%，哇啦，第二个系列将成为总数的百分比。希望这有帮助！
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 具有常见的图表类型，但即使 Microsoft 也没有资源来提供所有可能的图表样式组合。用户对当前可用的样式感到非常困惑。
Excel 图表绘制给定的数据。如果需要将 A 绘制为 B 的百分比，则需要计算某个范围内的百分比，并绘制该范围。
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 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.
我这样做的方法是根本不修改工作表上的范围。
编写一个新函数来创建您需要的修改后的范围数据，以便您可以返回该数据并在创建图表时在 ApplyTemplate 方法内部使用它。唯一的缺点是您需要运行宏/vsto 功能区按钮来更新图表。而不是通过工作表绑定方法免费获得该行为。
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.