如何在Excel中向单元格添加规则? [英] How do you add a rule to cells in excel?

问题描述

我有一组从Hyperion Essbase中提取的数据，我想在这些数据上添加一条规则.我已经尝试过条件格式设置，但没有得到想要的内容.

I have a set of data extracted from Hyperion Essbase in which I would like to add a rule over those cells. I've tried conditional formatting, but I'm not getting what I want.

例如，某些单元格包含0.000或13.5232，而我要对它们执行的规则是IF(ROUND(ABS(Cell Value),2)<=0.01,0,Cell Value)

For example some cells contain 0.000 or 13.5232 and the rule I want over them is IF(ROUND(ABS(Cell Value),2)<=0.01,0,Cell Value)

我不确定我的公式是没有意义的公式还是我没有正确使用条件格式.我不需要突出显示任何单元格，只要它们符合我的公式中的条件，我希望将单元格中的数据四舍五入.

I'm not sure if my formula is the one that doesn't make sense or I'm not using conditional formatting correctly. I don't need any cells highlighted, I would just like the data in the cells rounded if they fit the criteria in my formula.

推荐答案

从您的问题来看，您似乎想使用条件格式来更改数字的格式设置(对于绝对值四舍五入到小数点后两位的数字，是< = 0.01，则应使用小数点后0位(即显示为0)的格式设置.

From your question, it seems like you want to use conditional formatting to change how the numbers are formatted (for numbers whose absolute value, rounded to 2 decimal places, is <= 0.01, they should be formatted with 0 decimal places, i.e., show as 0).

这完全可以通过条件格式来实现(不需要VBA).

This can absolutely be achieved with conditional formatting (no VBA required).

1. 选择要应用条件格式的单元格.
2. 打开条件格式规则管理器，然后创建.
3. 选择使用公式来确定要格式化的单元格，然后输入用于标识要格式化的单元格的公式(确保避免使用绝对引用).
4. 点击格式按钮，然后选择数字> 0个小数位()
5. 单击确定" 3次以返回到电子表格，您应该看到所需格式的值:

1. Select the cells you wish to apply the conditional formatting to.
2. Open the Conditional Formatting Rules Manager, and create a .
3. Select Use a formula to determine which cells to format, enter the formula that identifies the cells you wish to format (make sure you eschew absolute references).
4. Click the Format button and select Number > 0 Decimal places ()
5. Click OK 3 times to return back to the spreadsheet, and you should see the values formatted as desired:

这篇关于如何在Excel中向单元格添加规则?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！