1.6 数据格式转换
1.6.1 数值取整的9种方法
【问题】
数据取整是Excel数据处理最常用的方式。可能大家最经常用的是INT函数，但INT函数并不能满足所有的取整要求。本节将总结各种取整函数的方法，基本能满足不同的取整要求。
【实现方法】
1）INT函数取整
特征：
（1）当数值为正数时，直接截掉数值的小数部分。
（2）当数值为负数时，截掉数值的小数部分再-1。
INT函数取整举例如图1-193所示。
图1-193 INT函数取整举例
2）TRUNC函数取整
特征：不管数值是正数还是负数，都直接截掉数值的小数。
TRUNC函数取整举例如图1-194所示。
图1-194 TRUNC函数取整举例
3）ROUND函数小数取整
特征：当ROUND函数的第2个参数为0时，对数值采取四舍五入方式取整。
ROUND函数对小数取整举例如图1-195所示。
图1-195 ROUND函数对小数取整举例
4）ROUND函数整数取整
特征：当ROUND函数的第2个参数为负数时，将数值四舍五入到其小数点左边的相应位数取整。
ROUND函数整数取整举例如图1-196所示，ROUND（A2，-3）是指将数值12345四舍五入到千位数。
图1-196 ROUND函数整数取整举例
5）ROUNDUP（向上舍入）函数
特征：
（1）朝着远离0（零）的方向将数字进行向上舍入。
（2）如果第2个参数为0，则将数字向上舍入到最接近的整数。
（3）如果第2个参数小于0，则将数字向上舍入到小数点左边的相应位数。
ROUNDUP函数取整举例如图1-197所示。
图1-197 ROUNDUP函数取整举例
6）ROUNDDOWN（向下舍入）函数
特征：
（1）朝着零的方向将数字进行向下舍入。
（2）如果第2个参数为0，则将数字向下舍入到最接近的整数。
（3）如果第2个参数小于0，则将数字向下舍入到小数点左边的相应位数。
ROUNDDOWN函数取整举例如图1-198所示。
图1-198 ROUNDDOWN函数取整举例
7）MROUND函数
特征：
（1）返回参数按指定基数舍入后的数值。
（2）采取四舍五入的方式。
（3）数值和基数参数的符号必须相同。如果不相同，将返回错误值“＃NUM！”。
MROUND函数取整举例如图1-199所示。
图1-199 MROUND函数取整举例
8）CEILING函数
特征：
（1）向上舍入（沿绝对值增大的方向）为最接近指定基数的倍数。
（2）如果数值为正值，基数为负值，则返回错误值“＃NUM！”。
（3）如果数值为负，基数为正，则对值按朝向0的方向进行向上舍入。
（4）如果数值和基数都为负，则对值按远离0的方向进行向下舍入。
CEILING函数取整举例如图1-200所示。
图1-200 CEILING函数取整举例
9）FLOOR函数
特征：
（1）将数值向下舍入（沿绝对值减小的方向）为最接近的指定基数的倍数。
（2）如果数值为正值，基数为负值，则返回错误值“＃NUM！”。
（3）如果数值为负，基数为正，则对值按远离0的方向进行向下舍入。
（4）如果数值和基数都为负，则对值按朝向0的方向进行向上舍入。
FLOOR函数取整举例如图1-201所示。
图1-201 FLOOR函数取整举例
1.6.2 数值的特殊舍入方式
【问题】
舍入到偶数或奇数，在很多特殊数据处理场合下使用。
【实现方法】
1）舍入到偶数
（1）MROUND函数四舍五入到偶数的用法举例如图1-202所示。
图1-202 MROUND函数四舍五入到偶数的用法举例
注意：
•偶数是指能被2整除的数，所以，MROUND函数的第2个参数，即基数为2或者为负2。
•如果第2个参数是2，结果为正偶数。
•如果第2个参数是负2，结果为负偶数。
•数值和基数参数的符号必须相同。如果不相同，结果将返回＃NUM！错误。
（2）CEILING函数的用法举例如图1-203所示。
图1-203 CEILING函数的用法举例
用CEILING函数舍入到偶数时应注意：
•基数，即第2个参数为2或者为负2。
•如果数值为正值，基数为2，则向上舍入。
•如果数值为正值，基数为负2，则返回错误值“＃NUM！”。
•如果数值为负，基数为2，则对值按朝向0的方向进行向上舍入。
•如果数值和基数都为负2，则对值按远离0的方向进行向下舍入。
（3）FLOOR函数的用法举例如图1-204所示。
图1-204 FLOOR函数的用法举例
用FLOOR函数舍入到偶数时应注意：
•基数，即第2个参数为2或者为负2。
•如果数值为正值，基数为2，则向下舍入。
•如果数值为正值，基数为负2，则返回错误值“＃NUM！”。
•如果数值为负，基数为2，则对值按远离0的方向进行向下舍入。
•如果数值和基数都为负2，则对值按朝向0的方向进行向上舍入。
（4）EVEN函数。EVEN函数是为舍入到偶数量身定制的函数。EVEN函数的用法举例如图1-205所示。
图1-205 EVEN函数的用法举例
EVEN函数的特征：
•参数只有一个，即要舍入的数值。
•舍入方式为沿绝对值增大的方向返回最接近的偶数。
2）舍入到奇数
ODD函数为舍入到奇数量身定制的函数。
ODD函数的用法举例如图1-206所示。
图1-206 ODD函数的用法举例
特征：
（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所示。
图1-207 NUMBERSTRING不同格式参数的返回值
NUMBERSTRING函数的局限是：仅能计算整数。
（2）TEXT函数。它用来将数字转成中文大写格式，其语法为：
•格式参数为"[dbnum1]"：返回值采用普通的大写格式，如“七百八十九”。
•格式参数为"[dbnum2]"：返回值采用财务专用大写格式，如“柒佰捌拾玖”。
•格式参数为"[dbnum3]"：返回值采用阿拉伯数字之间加单位格式，如“7百8十9”。
以“123456789”为例，不同的格式参数，转换成为的中文数字格式也不同，结果如图1-208所示。
图1-208 TEXT函数不同格式参数的返回值
2）中文数字转为阿拉伯数字
不同形式的中文数字转为阿拉伯数字的公式参数也不同，如图1-209所示。
图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-210 交易记录
【实现方法】
（1）选项设置。单击“文件”→“选项”→“高级”，勾选“转换Lotus 1-2-3公式”项，如图1-211所示。
图1-211 勾选“转换Lotus 1-2-3公式”项
（2）数据分列。复制B2：B8区域到C2：C8区域，如图1-212所示。
图1-212 复制算式到结果区
选中C2：C8区域，单击“数据”→“分列”按钮，在打开的“文本分列向导-第1步”对话框中不做特殊修改，直接单击“完成”按钮，如图1-213所示。
图1-213 分列步骤
完成以后的结果，如图1-214所示。
图1-214 分列结果
（3）选项设置。单击“文件”→“选项”→“高级”→“转换Lotus 1-2-3公式”，将其前面的钩去除，如图1-215所示。
图1-215 “转换Lotus 1-2-3公式”去除勾选
去除这个选项的目的是：防止影响日期等类型数据的正常输入。
特别提醒：这样计算出来的结果，不会随着源数据的修改而改变！要想真正利用Excel记账，一定要预先设计好表结构哦！
1.6.5 阿拉伯数字（小写）转为中文数字（大写）来表示人民币的金额
【问题】
1．6.3节讲述了利用NUMBERSTRING和TEXT函数实现阿拉伯数字和中文数字的转换，并提到使用NUMBERSTRING函数，将阿拉伯数字（小写）转为中文数字（大写）来表示人民币的金额，但NUMBERSTRING函数的局限是：仅能计算整数，小数部分则要四舍五入，如图1-216所示。
图1-216 NUMBERSTRING函数的缺陷
还有一种方法：设置单元格格式，也可以将阿拉伯数字（小写）转为中文数字（大写）。如图1-217所示，在“设置单元格格式”对话框的“分类”栏中选择“特殊”，在“类型”中选择“中文大写数字”。但这种方法也有局限：小数部分只能“逐字直译”成大写，不能写成“几角几分”。
图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键执行计算，再将公式向下填充，可实现完美转换。
图1-218 “方法实现”人民币数字转成中文大写
【公式解析】
•-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函数替换“零角”为“零”，“零分”为“整”。