1.6 数据格式转换

1.6.1 数值取整的9种方法

【问题】

数据取整是Excel数据处理最常用的方式。可能大家最经常用的是INT函数，但INT函数并不能满足所有的取整要求。本节将总结各种取整函数的方法，基本能满足不同的取整要求。

【实现方法】

1）INT函数取整

特征：

（1）当数值为正数时，直接截掉数值的小数部分。

（2）当数值为负数时，截掉数值的小数部分再-1。

INT函数取整举例如图1-193所示。

2）TRUNC函数取整

特征：不管数值是正数还是负数，都直接截掉数值的小数。

TRUNC函数取整举例如图1-194所示。

3）ROUND函数小数取整

特征：当ROUND函数的第2个参数为0时，对数值采取四舍五入方式取整。

ROUND函数对小数取整举例如图1-195所示。

4）ROUND函数整数取整

特征：当ROUND函数的第2个参数为负数时，将数值四舍五入到其小数点左边的相应位数取整。

ROUND函数整数取整举例如图1-196所示，ROUND（A2，-3）是指将数值12345四舍五入到千位数。

5）ROUNDUP（向上舍入）函数

特征：

（1）朝着远离0（零）的方向将数字进行向上舍入。

（2）如果第2个参数为0，则将数字向上舍入到最接近的整数。

（3）如果第2个参数小于0，则将数字向上舍入到小数点左边的相应位数。

ROUNDUP函数取整举例如图1-197所示。

6）ROUNDDOWN（向下舍入）函数

特征：

（1）朝着零的方向将数字进行向下舍入。

（2）如果第2个参数为0，则将数字向下舍入到最接近的整数。

（3）如果第2个参数小于0，则将数字向下舍入到小数点左边的相应位数。

ROUNDDOWN函数取整举例如图1-198所示。

7）MROUND函数

特征：

（1）返回参数按指定基数舍入后的数值。

（2）采取四舍五入的方式。

（3）数值和基数参数的符号必须相同。如果不相同，将返回错误值“＃NUM！”。

MROUND函数取整举例如图1-199所示。

8）CEILING函数

特征：

（1）向上舍入（沿绝对值增大的方向）为最接近指定基数的倍数。

（2）如果数值为正值，基数为负值，则返回错误值“＃NUM！”。

（3）如果数值为负，基数为正，则对值按朝向0的方向进行向上舍入。

（4）如果数值和基数都为负，则对值按远离0的方向进行向下舍入。

CEILING函数取整举例如图1-200所示。

9）FLOOR函数

特征：

（1）将数值向下舍入（沿绝对值减小的方向）为最接近的指定基数的倍数。

（2）如果数值为正值，基数为负值，则返回错误值“＃NUM！”。

（3）如果数值为负，基数为正，则对值按远离0的方向进行向下舍入。

（4）如果数值和基数都为负，则对值按朝向0的方向进行向上舍入。

FLOOR函数取整举例如图1-201所示。

1.6.2 数值的特殊舍入方式

【问题】

舍入到偶数或奇数，在很多特殊数据处理场合下使用。

【实现方法】

1）舍入到偶数

（1）MROUND函数四舍五入到偶数的用法举例如图1-202所示。

注意：

•偶数是指能被2整除的数，所以，MROUND函数的第2个参数，即基数为2或者为负2。

•如果第2个参数是2，结果为正偶数。

•如果第2个参数是负2，结果为负偶数。

•数值和基数参数的符号必须相同。如果不相同，结果将返回＃NUM！错误。

（2）CEILING函数的用法举例如图1-203所示。

用CEILING函数舍入到偶数时应注意：

•基数，即第2个参数为2或者为负2。

•如果数值为正值，基数为2，则向上舍入。

•如果数值为正值，基数为负2，则返回错误值“＃NUM！”。

•如果数值为负，基数为2，则对值按朝向0的方向进行向上舍入。

•如果数值和基数都为负2，则对值按远离0的方向进行向下舍入。

（3）FLOOR函数的用法举例如图1-204所示。

用FLOOR函数舍入到偶数时应注意：

•基数，即第2个参数为2或者为负2。

•如果数值为正值，基数为2，则向下舍入。

•如果数值为正值，基数为负2，则返回错误值“＃NUM！”。

•如果数值为负，基数为2，则对值按远离0的方向进行向下舍入。

•如果数值和基数都为负2，则对值按朝向0的方向进行向上舍入。

（4）EVEN函数。EVEN函数是为舍入到偶数量身定制的函数。EVEN函数的用法举例如图1-205所示。

EVEN函数的特征：

•参数只有一个，即要舍入的数值。

•舍入方式为沿绝对值增大的方向返回最接近的偶数。

2）舍入到奇数

ODD函数为舍入到奇数量身定制的函数。

ODD函数的用法举例如图1-206所示。

特征：

（1）参数只有一个，即要舍入的数值。

（2）舍入方式为沿绝对值增大的方向返回最接近的奇数。

1.6.3 NUMBERSTRING函数和TEXT函数

【问题】

在进行数据处理时，经常会遇到阿拉伯数字与中文数字之间的转换（尤其遇到“钱”的问题时），而Excel提供的设置单元格格式功能，根本满足不了这种需求。

本节讲述利用NUMBERSTRING函数和TEXT函数实现在阿拉伯数字与中文数字之间的转换。

【实现方法】

1）阿拉伯数字转中文数字

阿拉伯数字转中文数字常用的两种函数是：NUMBERSTRING和TEXT。

（1）NUMBERSTRING函数。它是指数字到文本的转换。该函数在Excel里是隐藏的，输入时，须要输入函数名，而且不会提示参数。

NUMBERSTRING函数的参数有两个，其语法为：

其中，格式参数可以有1、2、3这3个值。

•格式参数为1：返回值采用普通的大写格式，如“七百八十九”。

•格式参数为2：返回值采用财务专用大写格式，如“柒佰捌拾玖”。

•格式参数为3：返回值采用仅数字大写格式，如“七八九”。

以“123456789”为例，不同的格式参数，转换成为的中文数字格式也不同，结果如图1-207所示。

NUMBERSTRING函数的局限是：仅能计算整数。

（2）TEXT函数。它用来将数字转成中文大写格式，其语法为：

•格式参数为"[dbnum1]"：返回值采用普通的大写格式，如“七百八十九”。

•格式参数为"[dbnum2]"：返回值采用财务专用大写格式，如“柒佰捌拾玖”。

•格式参数为"[dbnum3]"：返回值采用阿拉伯数字之间加单位格式，如“7百8十9”。

以“123456789”为例，不同的格式参数，转换成为的中文数字格式也不同，结果如图1-208所示。

2）中文数字转为阿拉伯数字

不同形式的中文数字转为阿拉伯数字的公式参数也不同，如图1-209所示。

公式为“{=MAX（（TEXT（ROW（$1：$99999），"[dbnum1]"）=A2）*ROW（$1：$99999））}”，其解释如下。

•计算ROW（$1：$99999），此步的结果是返回1～99999之间的整数。因为本示例要转换的数字有5位，所以用1～99999，如果有3位，用1～999；如果有六位，用1～999999。

•计算TEXT（ROW（$1：$99999），"[dbnum1]"），将1～99999之间的整数转换为“一万二千三百四十五”格式的中文数字。

•计算TEXT（ROW（$1：$99999），"[dbnum1]"）=A2，将1～99999之间格式为“一万二千三百四十五”的中文数字与A2单元格的中文数字做比较。如果相等，则返回TRUE；如果不相等，则返回FALSE。所以，此步返回的是由一个TRUE和99998个FALSE组成的数组。

•计算（TEXT（ROW（$1：$99999），"[dbnum1]"）=A2）*ROW（$1：$99999），由一个TRUE和99998个FALSE组成的数组，分别与对应的1～99999相乘，TRUE相当于1，FALSE相当于0，所以，此步的结果是返回1个阿拉伯数字与99998个0组成的数组，而该阿拉伯数字就是与A2单元格相对应的数字。

•计算{=MAX（（TEXT（ROW（$1：$99999），"[dbnum1]"）=A2）*ROW（$1：$99999））}，在1个阿拉伯数字与99998个0组成的数组中取最大值，也就是与A2单元格相对应的数字。

因为这里进行的计算是数组计算，所以，按Ctrl+Shift+Enter组合键执行计算公式输入。由于数组中的数据有99999个，所以公式运行稍有点慢。

1.6.4 怎么计算长短不一的文本算式结果

【问题】

如图1-210所示，这样的交易记录怎么计算存货量？

【实现方法】

（1）选项设置。单击“文件”→“选项”→“高级”，勾选“转换Lotus 1-2-3公式”项，如图1-211所示。

（2）数据分列。复制B2：B8区域到C2：C8区域，如图1-212所示。

选中C2：C8区域，单击“数据”→“分列”按钮，在打开的“文本分列向导-第1步”对话框中不做特殊修改，直接单击“完成”按钮，如图1-213所示。

完成以后的结果，如图1-214所示。

（3）选项设置。单击“文件”→“选项”→“高级”→“转换Lotus 1-2-3公式”，将其前面的钩去除，如图1-215所示。

去除这个选项的目的是：防止影响日期等类型数据的正常输入。

特别提醒：这样计算出来的结果，不会随着源数据的修改而改变！要想真正利用Excel记账，一定要预先设计好表结构哦！

1.6.5 阿拉伯数字（小写）转为中文数字（大写）来表示人民币的金额

【问题】

1．6.3节讲述了利用NUMBERSTRING和TEXT函数实现阿拉伯数字和中文数字的转换，并提到使用NUMBERSTRING函数，将阿拉伯数字（小写）转为中文数字（大写）来表示人民币的金额，但NUMBERSTRING函数的局限是：仅能计算整数，小数部分则要四舍五入，如图1-216所示。

还有一种方法：设置单元格格式，也可以将阿拉伯数字（小写）转为中文数字（大写）。如图1-217所示，在“设置单元格格式”对话框的“分类”栏中选择“特殊”，在“类型”中选择“中文大写数字”。但这种方法也有局限：小数部分只能“逐字直译”成大写，不能写成“几角几分”。

以上两种方法都不完美，只能求助函数了。

【实现方法】

如图1-218所示，在B2单元格中输入公式“=SUBSTITUTE（SUBSTITUTE（IF（-RMB（A2，2），TEXT（A2，"；负"）&TEXT（INT（ABS（A2）+0.5%），"[dbnum2]G/通用格式元；；"）&TEXT（RIGHT（RMB（A2，2），2），"[dbnum2]0角0分；；整"），"零元整"），"零角"，IF（A2^2<1，，"零"）），"零分"，"整"）”，按Enter键执行计算，再将公式向下填充，可实现完美转换。

【公式解析】

•-RMB（A2，2）：按人民币格式将数值四舍五入到两位数并转换成文本。

•TEXT（A2，"；负"）：如果A2的金额小于0，则返回字符“负”。

•TEXT（INT（ABS（A2）+0.5%），"[dbnum2]G/通用格式元；；"）：金额取绝对值，整数部分转换为大写格式，参数+0.5%用于避免0.999元等的情况下计算出现错误。

•TEXT（RIGHT（RMB（A2，2），2），"[dbnum2]0角0分；；整"）：金额小数部分转换为大写。

•IF（-RMB（A2，2），TEXT（A2，"；负"）&TEXT（INT（ABS（A2）+0.5%），"[dbnum2]G/通用格式元；；"）&TEXT（RIGHT（RMB（A2，2），2），"[dbnum2]0角0分；；整"），"零元整"）：IF函数用于判断，如果金额不是0分，则返回大写格式的结果，否则返回零元整。

•用两个SUBSTITUTE函数替换“零角”为“零”，“零分”为“整”。