数据整理与清洗

适用情景：

需要经常使用表格进行询报价以及数据分析。由于数据来源不一（客户习惯，系统格式，互联网等等）拿到的数据大多数时候未必是可以马上使用的，这时就需要事先对数据进行清理，把它整理成复合我们操作需要的格式。大数据时代，我们把这一过程称为数据清洗。

函数：

len()

left()

right()

mid()

find()

& 或 concatenate()

trim()

clean()

substitute()

replace()

upper()

lower()

proper()

value() 或 -- 或 *1

Text()

len(text) 函数

返回文本中字符的个数（含空格）

例如：len("Good morning") = 12

left(text,[num_chars])

从文本中第1个字符开始返回指定个数[num_chars]的字符，num_chars省略时，默认为1。

例如：left("Good morning") = G

left("Good morning",1) = G

left("Good morning",4) = Good

Right(text,[num_chars])

从文本中最后一个字符开始返回指定个数[num_chars]的字符，num_chars省略时，默认为1。

例如：right("Good morning") = g

right("Good morning",1) = g

right("Good morning",7) = morning

MID(text,Start_num,num_chars)

在文本中，从指定的开始位置返回特定长度的字符。

例如：mid("Good morning",2,3) = ood

Find(find_text, within_text, [start_num])

查找一个字符串在另一个字符串中开始出现的位置（区分大小写）, start_num省略时，默认从第1位开始查找。

find("i","China") = 3

find("I","China,1) = #VALUE! (大小写不同i、I)

字符串连接

-CONCATENATE(text1,[text2],[text3],…)将多个字符串合并成一个。

-&连接字符串，更加简便。

例如：

A1=12,A2=34 - A1 & A2=1234（文本格式的12）

”A"&"B"=AB

trim(text)

去除字符串前后的空格，但会保留1个空格作为字词之间的分隔符。

clean(text)

去除非打印字符。比如网页中复制下来的不可见字符或者换行符，回车符等。

substitute(text,old_text,new_text,[instance_num])

替换原文本中指定的字符(区分大小写），instance_num省略时，查找到的复合条件的字符全部替换。如果指定1,2,3...，则只替换对应位置的字符，如超过出现的次数，则不替换。

例如：

单元格 A1 = bookkeeper，e出现了3次。

substitute(A1,"e","E") = bookkEEpEr

substitute(A1,"e","E",1) = bookkEeper

substitute(A1,"e","E",2) = bookkeEper

substitute(A1,"e","E",3) = bookkeepEr

substitute(A1,"e","E",4) = bookkeeper

replace(old_text, Start_num, num_chars, new_text)

在文本中（整体）替换指定区域的任意文本

例如：REPLACE("bookstore",5,5,"keeper !") = bookkeeper !

bookstore中,从第5个位置s开始，取长度为5的区域（e),即"store"，替换为"keeper !"

当然，特定情况下，这个用CTRL+H（查找）替换也是极为方便的。

我们看几个实际案例：

例1：

单元格A3：

LEFT(A2,5)&"."&MID(A2,6,4)&"."&RIGHT(A2,3) = 12345.6789.ABC

客户发现小数点分隔不美观，希望可以用-分隔。

单元格A4：

SUBSTITUTE(A3,".","-") = 12345-6789-ABC

客户希望用XI-OTC代替ABC。

单元格A5：

REPLACE(A4,12,3,"XI-OTC") = 12345-6789-XI-OTC

此处也可以CTRL+H查找ABV，用XI-OTC替换。

现在客户决定把产品开始前5位随机的编码，按公司编码体系统一更改为98001,98002,98003....

例2：

接到一份询价，尺寸描述混杂在一起，不能有效的筛选相同规格进行计算，而且有不规则的空格掺杂其中。如果将我们所需要的数据提取为如下格式呢？

观察一下三部分的规律，标准都在M之前，尺寸是M和-之间的部分，表面是-之后的部分。

标准 - 公式

为了方便计算空格数，首先对A2单元格进行去空格操作。

Trim(A2) = DIN 912 M5x25 - A2K

然后用left()函数，取M5之前的部分。那么我们需要知道M的位置号。

而M的位置恰好可以通过Find()函数得到。

FIND("M",TRIM(A2)) = 9, 即M位置为9，那么我们只需用Left()取到8就可以了。

LEFT(TRIM(A2),FIND("M",TRIM(A2))-1) = "DIN 912 "

注意此处第8位有一个空格，再次去空格操作

TRIM(LEFT(TRIM(A2),FIND("M",TRIM(A2))-1)) = DIN 912

表面 - 公式

为了方便计算空格数，首先对A2单元格进行去空格操作。

Trim(A2) = DIN 912 M5x25 - A2K

然后用right()函数，取 - 之后的部分。那么我们需要知道 - 的位置号。

FIND("-",TRIM(A2)) = 15

还需要知道15至随后一位一共有多少个字符，需要总长度-15

总长度 = LEN(TRIM(A2)) = 19

所以right(A2, 19-15) 取4位，随后再做去除多余空格操作。

TRIM(RIGHT(TRIM(A2),LEN(TRIM(A2))-FIND("-",TRIM(A2)))) = A2K

尺寸 - 公式

为了方便计算空格数，首先对A2单元格进行去空格操作。

用Mid()函数截取M和 - 之间的部分，最后做去除多余空格操作。

起始位置：M的位置号 = FIND("M",TRIM(A2)) = 9

结束位置：- 的位置号 = FIND("-",TRIM(A2)) =15

截取长度：FIND("-",TRIM(A2)) - FIND("M",TRIM(A2)) = 6

Mid()出场

MID(TRIM(A2),FIND("M",TRIM(A2)),FIND("-",TRIM(A2))-FIND("M",TRIM(A2))) = "M5x25 "

注意此处第6位有一个空格，再次去空格操作。

TRIM(MID(TRIM(A2),FIND("M",TRIM(A2)),FIND("-",TRIM(A2))-FIND("M",TRIM(A2)))) = M5x25

同样的操作也适用于，分数段的统计[60 - 80]，检测结果的提取[4.5-5.21]，不一而足。

UPPER(text)

所有字母变为大写

LOWER(text)

所有字母变为小写

PROPER(text)

每个单词首字母变为大写，其余小写。