条件格式化到图表轴 [英] Conditional formatting to chart axis

问题描述

我有一张基于表格的图表。

表中的数据有条件地被格式化为数字或货币。

I have a chart based on a table.
The data in the table is conditionally formatted to be either a number or currency.

格式化为纯数字时，Y轴如下所示：

When formatted as a plain number the Y axis looks like the following:

格式化为货币时，Y轴不采用相同的格式。这是Excel中的故意还是错误？有解决方法吗？我不想在解决方案中使用VBA，但是任何类型的公式当然可以。

When formatted as currency the Y axis does not adopt the same formatting. Is this deliberate or a bug in Excel? Is there a workaround? I don't want to use VBA in the solution but any sort of formula is of course fine.

推荐答案

编辑 - 实际解决方案：

Edit - Actual Solution:

这个棘手的事情的解决方法是使用命名范围！

The solution to this tricky thing was to use named ranges!

命名范围可以切换他的内容，从而重定向图表动态地基于条件！这是相当强大的我在其他场合使用过它，但是我不得不首先对这个问题感到痴迷，所以我的记忆被触发。

A named range can switch his content, thereby redirecting a chart dynamically based on a condition! This is quite powerful. I used it on other occasions, but I had to first get really obsessed with this question, so that my memory was triggered.

在我的屏幕截图中，你会看到所有的内容来解决这个。只需设置这两个名称，就可以使用您喜欢的条件。

In my screenshot you see all contents to solve this. Just set up those two names with a condition you like.

不幸的是，我的解决方案需要公开您的数据列，以反映不同的格式。但是至少解决了你的问题。

Unfortunately my solution requires to dublicate your data-columns in order to reflect the different formattings. But at least, it solves your problem.

编辑：

这个问题比较详细，我必须在这里标注，我的答案不能解决这个特殊的问题。虽然这可以与给定问题的VBA解决方案结合使用，但是如果没有VBA，则无法应用静态格式数据。

After looking into this problem more detailed, I must mark here, that my answer does not solves this particular problem. While this can be used in combination with a VBA-Solution for the given problem, it cannot be applied without VBA, except of course for static formatted data.

可以使用您的轴的正确设置：

This is possible with the right settings for your axis:

当您链接到源时，数据的格式将反映在您的图表中;）

When you link to source, the formatting of your data will be reflected in your diagram ;)

否则，您的图表将使用您为此轴设置的任何格式。

Otherwise your chart will use whatever format you have set for this axis.

这篇关于条件格式化到图表轴的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！