|
与 30万 读者一起学Excel 卢子：Text一个很神奇的函数，可以将数据变化成你想看到的任何形式，有万能函数之称。 网友：万能？这么牛逼，真想好好见识下。 卢子：N年前在日企工作，经常会写一些日语格式的星期几、数字、日期，你们觉得输入这些是不是很麻烦？ 网友：看都看不懂，别说输入了，那你日语一定很厉害吧，经常输入这些。 卢子：其实我有一个秘密一直没跟外人说，我压根儿不会日语，也很少用有道词典翻译。 网友：那你怎么输入这些？ 卢子：我是借助自定义单元格格式跟Text函数而搞定这些，下面开始了解一些Text的基础，最后我再将我的绝招说出来。 万能当然是夸张的说法，但确实很强大。其实，Text的宗旨就是将自定义格式体现在最终结果里。Text函数主要是将数字转换为文本。当然，也可以对文本进行一定的处理。 TEXT函数的语法：TEXT(值,要显示的文本格式) Text返回的一律都是文本形式的数据。如果需要计算，可以先将文本转换为数值，然后再计算。文本型数值遇到四则运算会自动转为数值，比如+0。但文本会不参与Sum之类的函数运算。 例子1 Text函数基本的数字处理方式。 =TEXT(12.34,"0") =TEXT(12.34,0) 含义是将数字12.34四舍五入到个位，然后以文本方式输出结果，当只有一个0的时候，引号可以不加。 =TEXT (12.34,"0.0") 得到12.3，可以看到效果是保留一位小数。小数点后写几个0，就是设置多少位小数。 "0"：数字占位符。如果单元格的内容大于占位符，则显示实际数字，如果小于占位符的数量，则用0补足。 =TEXT (12.34,"00000") 就显示为：00012。 "#"：数字占位符。只显有意义的零而不显示无意义的零。小数点后数字如大于"#"的数量，则按"#"的位数四舍五入。 =TEXT(12.34,"#####") 就显示为：12。如果设置为"###.##"，12.1显示为12.10；12.1263显示为：12.13。 "0,0"：里面的逗号是千分符。 =TEXT(123456,"0,0") 就显示成123,456。 "G/通用格式"：以常规的数字显示。例如：10显示为10；10.1显示为10.1。 另外，前导0的效果，想显示几位就写几个0，也可以配合Rept函数来写，REPT(字符,N)，重复N次显示字符，比如REPT(6,3)就是666。就是一个为数据前面加0的效果。 =TEXT(D2,REPT(0,D2)) 例子2 Text在日期时间处理方面的应用。 先说说日期这种特殊的数据类型，日期2010/5/25，其实是数字40323。 =TEXT(40323,"yyyy/m/d") 可以显示2010/5/25。Text是把日期所代表的真正的数字，来转成所需要的日期格式的文本。而20100525，要显示2010/05/25的话，要用上面介绍的0的方法： =TEXT(20100525,"0!/00!/00") 如果分隔符号用-就不需要加!。!就是强制显示某字符，后面的案例会说。 =TEXT(20100525,"0-00-00") 公式中：yyyy可以用e来代替。mm，表示显示两位月份，m显示一位。中间的连接号，还可以换成其他。 text的结果是文本，如果text返回"2010/05/25"的话，再去设置格式就改变不了的。 例子3 Text表示四种数据类型。 =text(数据,"正;负;零;文本") text里面可以表示四种数据类型。正数、负数、零与文本，用分号隔开。根据数据的类型，返回对应位置里的格式。 没有分号，代表一种格式。 2个分号：表示单元格为两种格式：分号前面为正数和0；分号后面为负数。 3个分号：表示单元格为三种格式：第1部分用于正数，第2部分用于负数，第3部分用于0值。比如"0;-0;"，将只显示正数和负数，但不显示0；最后一个分号不能省略，如果写成"0；-0"表示的是不一样的含义。 =text(数据,"1;2;3;@") @是文本的通配符，相当于数值中的0。 =text(数据,"1;2;3;@") =if(数据>0,1,if(数据<0,2,3)) 这两种是一样的。 当数据大于0，返回1；小于0，返回2；等于0，返回3，是文本的话，返回其本身。根据分号内的格式自动分配。 =text(数据,"1;;;") 这种，分号内没有要显示的格式了，结果就显示空。也就是，当数据大于0时，显示1，其余显示为空。3分号，四类型。 例子4 强制符号方面的应用。 =TEXT(A2,"0;!0;0;!0") 强制符号！有了它就可以强制显示0了。大于0，显示本身，其他显示0。 例5 条件判断方面的应用。 效果1 =TEXT(A2,"[>10]0;1") =IF(A2>10,A2,1) 效果2 =TEXT(B2,"[>50]a;[>10]b;c") =IF(A2>50,"a",IF(A2>10,"b","c")) text很经典的用法就是在条件判断方面。因为可以省字符，数组公式中常用。条件需要用中括号括起来。这时，分号的作用就不是隔开正数、负数、零了。条件判断的顺序，是先左后右，如同if函数一样。 例6 中文数字中的应用。 效果1 =TEXT(A2,"[dbnum1]") 效果2 =TEXT(A2,"[dbnum2]") 效果3 =TEXT(A2,"[dbnum3]") 网友：头大了，这么多，那里记得住。 卢子：你会自定义单元格格式吗？ 网友：这个会。 卢子：前面说了那么多，只是让大家有一个初步的了解，知道Text函数可以做什么。这么多用法其实我也记不住，也无需记忆。 输入任意一个数字，设置单元格格式为货币格式，然后查看自定义格式，复制自定义格式，输入 =TEXT(23,”¥#,##0.00;¥-#,##0.00”) 利用同样的方法，哪一种格式不会就设置单元格格式，再查看自定义格式代码，这样可以减轻我们的记忆负担。 现在到了应该解开最开始留下的那个日文输入法的时候了。 默认情况下，在特殊这个格式对应的区域设置，中文（中国），但实际上这里是允许选择任意国家的语言的。如果你选择日语，在类型这里就出现很多跟日语有关的数字格式，你只要选择这些就可以尝试一些设置。
善于借助一切可以为我们减去记忆负担的方法，这样学习起来就变得更加轻松。 [DBNum2][$-411]aaaa [DBNum2][$-411]G/通用格式 [DBNum2][$-411]yyyy/m/d 网友：以前以为看到这些都要记住，原来很多都藏在自定义里，还有这个区域设置还第一次知道，以前从没注意过这个问题，长见识了。 卢子：学习这些要有好奇心，有空点开一些你从没点击过的功能来看看，也许会发现很多你意想不到的功能。惊喜就由此产生。 作者：卢子，清华畅销书作者，《Excel效率手册 早做完，不加班》系列丛书创始人，个人公众号：Excel不加班（ID：Excelbujiaban） |
|
|
