excel数字格式 - 不同的十进制数字 [英] excel number format - varying decimal digits

问题描述

我试图用这些规则设置一个特殊的单元格数字格式：
$ b $ ul

显示百分比

显示最多3位数（十进制+整数部分）

$ b $所以我可以显示100％或99.3％或1.27％
但不是100.9％或100.27％或99.27％。

当然，我可以有负数（-27.3％），这并不影响我的规则。

我已经尝试使用单元格格式化选项，但没有成功：

  [<1] 0.00％; [<10] 0.0％; 0％
  

似乎excel（2010）不支持超过2个单元格格式的条件（所以我不能扩展它来管理负数...）

无论如何要做我想要的东西？

谢谢

解决方案

功能区中的主页选项卡选择条件格式： >

然后选择新建规则或管理规则（后者可以选择新建规则，但也可以概览所有当前规则）

然后选择使用公式确定要格式化的单元格，并参照单元格本身输入公式（在我的情况下，选中单元格 A1 $的迹象，以便它也可以应用到其他单元格本身！）：

现在点击格式...并根据您的需要选择所需的数字格式

对所有需要区分的情况重复此操作。

ABS（）总是测试单元格的绝对值的规则。

请注意，您可以使所有规则适用于2方的有限值范围（在我例如我有最小的0.1和最大值1（分别为10％和100％）。或者，您只能确定最小或最大值，并在所有您的规则涉及的右端勾选Stof If True。

I'm trying to set a special cell number format with theses rules:

• display percentage
• display at max 3 digits (decimal + integer part)

So I can display 100% or 99.3% or 1.27% but not 100.9% or 100.27% or 99.27%.

Of course, I can have negative number (-27.3%) and it does not affect my rules.

I've try with the cell formating option without success:

[<1]0.00%;[<10]0.0%;0%

Because it seemed that excel (2010) does not support more than 2 conditions in cell formating (and so I can't expand it to manage negative number...)

It there anyway to do what I want?

Thanks

解决方案

Under the Home tab in the Ribbon select Conditional Formatting:

and then select either New Rule or Manage Rules (with the latter you can then select New Rule, but also have an overview of all current rules)

Then select Use a formula to determine which cells to format and enter the formula with reference to the cell itself (in my case cell A1 was selected, take the $ signs out to allow it to be applied to other cells themselves as well!):

Now Click Format... and select the required Number format as percentage with the number of decimal places as you want it.

Repeat this for all the cases you want to distinguish.

As values can be negative I use ABS() to always test for the rule on the absolute value of the cell.

Note you can either make all rules apply for a 2 side limited value range (in my example I have the minimum of 0.1 and the maximum of 1 (10% and 100% respectively). Alternatively you can only determine the minimum OR maximum and tick the box for Stof If True at the right end for all your rules involved.

这篇关于excel数字格式 - 不同的十进制数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！