我的兄弟请我帮忙格式化工作表。他希望成千上万的内容以 K格式表示,数百万以 M格式,万亿十亿以‘T’‘B’表示。
My brother asked my help formatting a Worksheet. He wants thousands to be formatted with 'K', millions with 'M' and trillions billions with 'T' 'B'.
- €1.000->€1,0 K
- €1.000.000->€1 ,0 M
- €1.000.000.000->€1,0 B
我发现了互联网上的以下自定义格式(此处)
I found the following custom format on the Internet (here)
[>=1000000] $#,##0.0,,"M";[<1000000] $#,##0.0,"K";General我对此进行了修改
[>999999] $#,##0.0,,"M";[>999] $#,##0.0,"K";General添加更多条件将导致错误。
Adding more conditions will result in an error.
[>999999999] $#,##0.0,,,"B";[>999999] $#,##0.0,,"M";[>999] $#,##0.0,"K";General最后,当我忽略小于一千的数字时,这似乎可行。
Finally, this seems to work when I ignore the numbers under one thousand.
[>999999999] $#,##0.0,,,"B";[>999999] $#,##0.0,,"M";$#,##0.0,"K"最后,我只是制作了一个公式来进行格式化,除以数字并连接€uro符号和K,M或B。
In the end, I just made a formula that does the formatting, dividing the number and concatenating the €uro sign and the K, M or B.
问题是,是否有一种方法可以添加两个以上条件。
The question is, whether there is a way to add more than two conditions.
推荐答案我认为解决方案,例如,取决于您选择的标记:条件格式。您引用的自定义格式设置规则显示了研究结果,但似乎不符合您兄弟的要求(例如 $ cf €),因此我不确定要格式化的内容是什么,并假设这些是数字值。
I think the solution, such as it is, lies in your choice of tag: Conditional Formatting. The custom formatting rules you refer to show research but seem not to meet your brother's requirement (eg $ cf €) so I am uncertain what exactly the content to be formatted is and am assuming these are number values.
此外,您将能够转换为适合您的语言环境,要求是Windows,而不是OSX,不需要考虑负数,并且与欧分无关。
Also that you will be able to convert for a locale that suits you, the requirement is for Windows, not OSX, negative numbers need not be considered and Euro cents are irrelevant.
首页>样式-条件格式,新规则...,使用公式确定要格式化的单元格和设置值的格式公式正确的地方::
HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format and Format values where this formula is true::
=AND(A1>0,A1<1000000)格式... ,依次选择数字标签,类别:自定义和:
Format..., select Number tab, Category: Custom and:
"€ "#.0, " K"好的,好的。然后是另一个公式规则:
OK, OK. Then another formula rule:
=AND(A1>999999,A1<1000000000),格式为:
"€ "#.0,, " M"和第三个公式规则:
=A1>999999999格式:
"€ "#.0,,, " T"应用规则的顺序可能很重要。
The order in which the rules are applied may be significant.
更多推荐
如何在自定义数字格式Excel中使用多个条件运算符?
发布评论