|
在获取业务数据时，由于数据来自多个来源，通常不能直接进行计算和可视化展示分析。在这种情况下，我们需要使用BI工具对现有的数据进行数据处理，以提高数据质量。
今天小九给大家介绍一些用于数据清理的文本函数，消除数据中的错误、缺失值、重复值和异常值，保障数据的可复用性，从而提高数据的质量和可信度。
在处理数据时，用户常常需要将英文字母进行大小写转换。在九数云中，有三个函数处理大小写十分好用：
目的：将文本数据中的首字母和所有非字母字符后的第一个字母转化为大写，其他字母变为小写。
示例：PROPER("SpreaDSheEt")=Spreadsheet
目的：将文本数据中的大写字母全部转换成大写
示例：UPPER("SpreaDSheEt")="SPREADSHEET"
目的：将文本数据中的大写字母全部转换成小写
示例：LOWER("SpreaDSheEt")=spreadsheet
目的：清除文本首尾所有的空格
示例：TRIM(" Monthly Report ")=Monthly Report
用户有时需清洗掉数据中的一些不需要的字符。例如，去掉表中的“【】”及其中间的内容，如下图所示：
可以结合使用两个函数，先使用 FIND 函数找到“【】”在字符串中的位置，以及【XX】所占的字符数，再使用 REPLACE 函数，根据指定的字符数，用空值替换【XX】这部分的内容。
目的：返回字符所在位置
示例：FIND("i","Information")=9
参数说明：FIND(find_text,within_text,start_num)
find_text：需要查找的文本
within_text：包含需要查找文本
start_num：非必填项，从字符串哪个位置开始查找，within_text 里的索引从 1 开始，如果省略 start_num，则假设值为 1
目的：根据指定的字符数，用其他文本串来替换某个文本串中的部分内容
示例：REPLACE("0123456789",5,4,"*")=0123*89
参数说明：REPLACE(old_text,start_num,num_chars,new_text)
old_text：需要被替换部分字符的文本或单元格引用
start_num：需要用 new_text 来替换 old_text 中字符的起始位置
num_chars：需要用 new_text 来替换 old_text 中字符的个数
new_text：需要替换部分旧文本的文本
一些订单信息表中，对于销售额、利润等字段会包含货币符号「¥」，想要将字符清除掉进行一些其他的运算。
Excel首尾字符智能清理
用户在使用、更新Excel或是本地数据源的过程中，经常会遇到Excel「文本」的头尾包含「空格或换行符号」等，九数云系统会自动清理单元格字符前后的空格等符号，更加高效便捷。
除了自动清理的字符外，还有一些其他特殊的字符，我们可以结合使用函数来清理：
-
先用 LEFT 函数截取第一位返回值，与（ 符号进行比较。
-
如果字符中的第一个字符为（，那么就需要使用 SUBSTITUTE 函数替换字段中的 （、）、￥内容，并使用 CONCATENATE函数拼接「-」负号字符；
-
如果字符中的第一个字符不是（，那么就使用 SUBSTITUTE 函数替换字段中的 ￥内容；
-
最后使用 TONUMBER 函数将结果转换成数值类型。
目的：截取前几个字符串
示例：LEFT("Fine software",8)=Fine sof
目的： 截取后几个字符串
示例：RIGHT("Itisinteresting",6)=esting
目的：返回指定位置字符串
示例：MID("Finemoresoftware",9,8)=software
参数说明：MID(text,start_num,num_chars)
text：包含要提取字符的文本串
start_num：文本中需要提取字符的起始位置，文本中第一个字符的start_num为1，以此类推
num_chars：返回字符的长度
目的：替换指定字符
示例：将database中的base替换成model
SUBSTITUTE("database","base","model")=datamodel
参数说明：SUBSTITUTE(text,old_text,new_text,instance_num)
text：原始字符串。
old_text：需要被替换的部分字符。
new_text：用于替换old_text的字符。
instance_num：指定用new_text替换第几次出现的old_text。可以不指定，若未指定，则替换所有出现的old_text。
目的： 将多个字符串合并成一个字符串
示例：CONCATENATE("Average","Price")=AveragePrice
目的：文本转为数字
示例：TONUMBER("123")=123
目的：文本转为整数
示例：TOINTEGER("123.56")=123
例如用户有字段长度不同的字符，需要截取最后一个「_」后的字符，如下图所示：
可以使用INDEXOF、FIND、MID、SUBSTITUTE、RIGHT、LEN等函数组合实现截取字段。
公式示例：
INDEXOF(SPLIT(字段,"_"),LEN(SPLIT(字段,"_"))-1)
目的：返回指定位置的字符
示例：INDEXOF("FineReport",2)=n
目的：分割字段
示例：SPLIT("this is very good"," ")=[this,is,very,good]
目的：返回字符长度
示例：LEN("Evermoresoftware")=16
通过使用九数云中的不同函数组合计算，我们可以解决大部分常用数据清洗需求。此外，我们还优化了函数框的提示功能，即使在多个函数嵌套的情况下，也不用担心出错无法定位了！
优化前：在公式写错的情况下，仅仅有一些宽泛的提示，例如【语法错误】、【不符合参数要求】等，不容易定位问题
优化后：对公式合法性判断进行优化，细化报错提示，更准确的指出不合法的地方。且增加波浪线标识，帮助用户更快地定位公式出错位置。
更详细的函数释义及应用可以扫描下方二维码添加小九获取。
/END/
| 
