Notice:做完下面的处理后要记得将处理结果粘贴为文本形式。
1 统计单元格中某个字符出现的次数,可用len()和substitute()函数。
业务背景:如果某个字符在一条文本中出现了N次以上,我们想把这条文本剔除。
若统计A2单元格中字符"#"出现的次数,则 = len(A2) - len(subtitute(A2,"#", ""))。也就是用单元格中总字符长度减去某字符替换为空后的字符长度。
2 随机抽取一定数量的样本或者将样本顺序打乱,可用randbetween()函数。
业务背景:针对一批大量的数据,想随机抽取一些数据进行校核。
解决方案:如果总共3000样本,我们要随机选取100样本进行质检,可以怎么做呢?其实就是 =randbetween(1,30),也就是将样本随机分成30份,那么每一个便能得到100左右数量的样本。因为是随机数,所以不能精确到100,自己处理后可以进行微调。如果是将3000样本的样本顺序打乱,则直接为 =randbetween(1,3000),再下拉,便能得到3000个随机数,然后升序或降序排列便是打乱后的样本。
3 文本去重。
业务背景:表里有很多字段,其他字段跟文本是否重复没关系。
解决方案:先将文本升序或降序排列。在新的一列,第一条文本为0,后面每条文本用if()函数,即 = if(A3 = A2, 1,0),再筛选出标号为1的数据即为重复数据,可进行剔除。虽然python的duplicate函数也可以快速去除,但是想想对于小数据量,开下软件,跑下脚本这个时间可能没有直接在excel中操作几下来得快,不过python的duplicate函数不会打乱样本的原有顺序。
4 将前后空格去除,转换英文大小写。
业务背景:有时针对不同的sheet表要做一些关联匹配,文本内容是对的,但是严格匹配后会因为前后有空格,大小写不一导致匹配不上。
解决方案:将文本转换为大写用 =upper(),转换为小写用lower(),去前后空格用trim()。比如将A2单元格中的英文文本转换为小写,并去除前后空格,则=trim(lower(A2))。
5 提取文本中的薪资信息。
业务背景:想从文本中将所有的薪资信息单独提取出来。比如7k-12k,8k-11k,11k-15k,20k-25k等。
解决方案:用find函数定位到k的位置,再用left函数提取。比如薪资信息是J列,则使用下式再下拉即可提取薪资下限(减1是因为想把k直接去掉)。同理,可提取薪资上限。
6 将不同列的薪水进行拼接。可以有两种解决方案,一种是用"&",一种是用concatenate函数。如下图(注意双引号为英文状态下)。
7 替换。这里讲用replace函数替换。比如将上图中的"到"替换为" and "。当然,一般简单的场景是直接用替换功能。
8 文本作减法。也就是A列中把包含B列的信息去掉。可直接用substitute函数。
9 检测是否为纯英文。业务场景:一列数据,包含纯中文,纯英文和中英文混合内容,需要将里面的纯英文内容筛选出来。将纯英文内容筛选:=IF(LEN(A1) = LENB(A1),1,0),则标记为1的内容即为纯英文内容。
10 将EXCEL表中同一ID的不同内容合并。先对ID进行排序,然后按以下列举内容函数表示为:=IF(A1=A2,B1&"|"&C2,B1)。再以A1去重,即保留每个ID第一个最长内容项。