Excel数据不规范处理 / 开普饭

今天看到这么一个案例，需要提取，或者说需要根据姓名查询！

问题虽然看上去有点麻烦，但是好在有规律，那么也就谢天谢地了！

Excel中没有文本分隔函数，所以处理类似的问题有其他的SUBSTITE+REPT套路！

我们先使用一些老套路处理！当然看到这里，已经有很多人蠢蠢欲动！这个我会，基础操作就可以！好吧，那我先基础操作吧！

思路1 | 基础操作

操作：

1、数据-分列【ALt+D+E】，使用的快捷键，分隔符选择（|），确定完成

2、数值复制，选择性粘贴-转置

3、复制数值左边的符号（〖），再次分列

4、替换（〗）为空【Ctrl+H】

小结：基础操作永远是亲民的，但是略显繁琐，胜在简单，但是不适合大量处理！

下面我们来说说，传统处理这类问题的，函数套路！

思路2 | 函数套路法

▼我是一条普通的公式

=TRIM(MID(SUBSTITUTE(TRIM(MID(SUBSTITUTE(LEFT($A$1,LEN($A$1)-1),"〗|",REPT(" ",99)),ROW(A1)*99-98,99)),"〖",REPT(" ",99)),COLUMN(A1)*99-98,99))

简单分析：SUBSTITUTE+REPT已经是处理这类问题的老套路！把特定的符号，替换成足够多的空格，然后再进行截取，这样就可以按照统一维度截取，对于多出的空格使用Trim去掉即可！只是在案例中有多个符号，所以我们需要替换两次，一次“|”整体分离，一次姓名、分数分离！

我们来看一下中间的核心过程，大家即可明白！

把"〗|"替换成99个空格，自动换行显示如下：

这样函数是最传统的套路，在2013版本之后，出现了FILTERXML函数，这个函数可以借助网页的xpath进行提取，对于这类问题，有了更多的更有意思的解法！

思路3 | 新的套路 - FILTERXML

我们使用FILTERXML可以轻松提取出全部内容，默认一列显示

▼我是一条普通的公式

=FILTERXML("<a>"&SUBSTITUTE(SUBSTITUTE(LEFT($A$1,LEN($A$1)-1),"〗|",""),"〖","")&"</a>","//b")

我们可以通过index函数轻松转成两列显示

先构建一样，结果需要的格式，然后放到INDEX第二参数即可！

=2*ROW(A1)-1+{0,1}

序列的构建是数组的基础功，大家后期慢慢总结，即可轻松掌握！

▼我是条区域数组公式（两列）

=INDEX(FILTERXML("<a>"&SUBSTITUTE(SUBSTITUTE(LEFT($A$1,LEN($A$1)-1),"〗|",""),"〖","")&"</a>","//b"),2*ROW(A1)-1+{0,1})

关于FILTERXML这个函数，小编已经写过专题文章，不了解基础的同学，

Excel数据不规范处理