Excel | NUMBERSTRING和TEXT函数:阿拉伯数字和中文数字转换

韩老师讲Office2018-12-11 09:08:03

点击上方「韩老师讲office」可快速关注
敬请阅读与分享,让更多学习者一起进步!



问题来源

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

今天韩老师跟大家讲利用NUMBERSTRING和TEXT函数实现数字在阿拉伯与中文格式之间的转变。


阿拉伯转中文数字

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

NUMBERSTRING函数

NUMBERSTRING函数,顾名思义,是数字到文本的转换。

该函数,在EXCEL里是隐藏的,输入的时候,需要我们全部输入函数名,而且,参数也不会提示。

那韩老师就把该函数的用法与参数解释一下:

NUMBERSTRING函数的参数有两个所以,语法我们可以简单的写成:

NUMBERSTRING(要转换成中文字符串的数值,格式参数

其中:

  • 格式参数为1:普通的大写,如“七百八”;

  • 格式参数为2:财务专用大写,如“柒佰捌拾玖”;

  • 格式参数为3:仅数字大写,如“八九”;

以“123456789”为例,不同的格式参数,转换成为的中文数字格式不同,结果如下图:

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

TEXT函数:

TEXT函数用来数字转中文大写时的语法:
TEXT(要转换成中文字符串的数值,格式参数

其中:

  • 格式参数为"[dbnum1]":普通的大写,如“七百八十九”;

  • 格式参数为"[dbnum2]":财务专用大写,如“柒佰捌拾玖”;

  • 格式参数为"[dbnum3]":阿拉伯数字之间加单位,如“7百8十9”;

以“123456789”为例,不同的格式参数,转换成为的中文数字格式不同,结果如下图:

中文转阿拉伯数字

不同形式的中文数字转换成阿拉伯数字的公式不同,如下图


韩老师以第一个公式:

“{=MAX((TEXT(ROW($1:$99999),"[dbnum1]")=A2)*ROW($1:$99999))}”为例,来讲述实现方法。

本公式:

第一步计算ROW($1:$99999),此步的结果是返回1~99999之间的整数;因为本示例要转换的数字有五位,所以,用1~99999,如果有三位,可以用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个,所以公式运行稍有点慢。

Copyright © 丰城计算器学习组@2017