标签:Excel函数
这是一个关于数字取整的有趣问题,示例数据如下图1所示。
图1
在单元格B2中的公式为:
=ROUNDUP(A2,-INT(LOG10(A2)))
非常精彩的公式!它对数字的取整方式为:
¥12,036,219.22转换为¥20,000,000.00;¥437,379.70转换为¥500,000.00,等等。
然而,如果想将¥12,036,219.22转换为¥13,000,000.00,除此之外,其它数字仍按原方式转换,如何编写公式?
由于只有1个例外,很自然地就考虑到使用IF函数,于是在单元格D2中输入公式:
=ROUNDUP(A2,IF(-INT(LOG10(A2))=-7,-6,-INT(LOG10(A2))))
-INT(LOG10(A2))确定舍入级别。如果函数测试为指定值,则仅更改指定值的舍入级别。结果如下图2所示。
图2
更进一步,如果存在多个例外,怎么办?
首先,创建一个查询表,可以方便地指定例外情形,如下图3所示。
图3
这样,单元格E2中的公式为:
=ROUNDUP(A2,INDEX(list!B1:B8,MATCH(-INT(LOG10(A2)),list!A1:A
该公式在工作表list中查找列A中的值并获取列B中对应的舍入级别。这种方法很容易修改列B中的舍入级别。(注意到,单元格A8中的值-7对应的值是一个例外,舍入级别为-6而不是-7。)
也可以使用数组公式,直接将查询表的值放入公式中。单元格F2中的公式为:
=ROUNDUP(A2,INDEX({0;-1;-2;-3;-4;-5;-6;-6},MATCH(-INT(LOG10($A2)),{0;-1;-2;-3;-4;-5;-6;-7},0)))
这是一个数组公式,输入完后要按Ctrl+Shift+Enter组合键。
小结
通过本示例,应该更深入的理解了ROUNDUP函数,以及根据需要灵活编写公式的技巧。