快速拆字段（一拆多）

数据剖析

1.固定宽度数据剖析

2.分隔符号数据剖析

3.不太规则的情况

4.拆成多列

5.换行符号数据剖析

归并字段（多并一）

1.单纯归并多栏

2.取部分字符串

3.归并时在单元格内换行

同场加映：怎么在 Ragic 拆字段或归并字段？

经手大量数据的人有时会遇到的问题：从其他地方下载 / 汇入 / 拷贝到 Excel 的一批数据，格式和需要的不一样，得自己整理。可能是一坨数据都挤在同一格里面，需要把同一个字段里的不同信息分拆到不同单元格，或是反过来，需要把一批数据的多个字段值，全都一起归并到同一个字段。

这时候，不用一一笔数据拷贝贴上、归并单元格。很多人知道可以利用现成的功能：数据剖析（一拆多）和公式（多并一），来快速处理多笔数据，但有时没办法快速判断“怎么用”，特别是遇到换行等特殊状况时，会需要用点小技巧。

这里特别整理出各种情境的对应解法，可以参阅下方表格，点击对应的超链接。

（注：如果你是 Ragic 的订户，要将 Excel 汇入 Ragic 前有时也需要这样的数据整理技巧。如果是已经存放在 Ragic 的数据，想知道怎么在 Ragic 做到这些事的话，可以查看这个部分的说明。）

例如，原本一批数据的某个字段值是“姓名”像“王大明”，现在需要把它分拆成一栏“姓”一栏“名”变成“王”和“大明”：

又例如拿到一组全都挤在一栏的“地址”信息，希望依照邮政编码、县市乡镇等分成一栏一栏的：

这在 Excel 最简便的方法就是利用数据页面签下的数据剖析来处理。

只要你的一批数据可以整理出一定的“分栏规则”，就可以用数据剖析把一个单元格一刀切成好几块。

最简单可以直接适用的规则是：① 要切成固定宽度（不同数据每一段要切的都一样长，字符数一样），或者 ② 字段值有以空格或特定符号来分隔。所以先来说这部分。

如果你想要“切”的数据，切分点右方每一段的字数都是相同的（例如把地址切成邮政编码、县市乡镇、其余地址，其中邮政编码固定是 3 码、县市乡镇固定是三个国字），那它就是固定宽度/固定字符的情况，可以直接使用“数据剖析”来切数据。

方法为：框选要拆分的字段后，到数据 > 数据剖析 > 选“固定宽度” > 依系统指示在画面上切出分割线，完成步骤即可。如果希望指定把切好的字段放在其他地方，就点“目的地”来另外框选位置。

“106 台北市 大安区 南昌路 二段 222号 ”、“106,台北市,大安区,南昌路,二段,222号 ”“106台北市大安区南昌路二段_222号 ”“106/台北市/大安区/南昌路/二段/222号 ”......这些都可以切成“106”“台北市”“大安区”“南昌路”“二段”“222号 ”没问题。

不过不限于这些符号，其实只要是一个特定的字符都可以，例如“106✦台北市✦大安区✦南昌路✦二段 ✦222号”只要有指定用✦来区隔，或是“106隔台北市隔大安区隔南昌路隔二段隔222号”指定用“隔”来区分，就都可以得出“106”“台北市”“大安区”“南昌路”“二段”“222号 ”。

方法为：框选要拆分的字段后，到数据 > 数据剖析 > 选“分隔符号” > 选择你的分隔符号并完成步骤即可。（可参阅上图。如果希望指定把切好的字段放在其他地方，就点“目的地”来另外框选位置。)

选分隔符号时，如果是上面提到的特殊分隔符号，就勾“其他”并填入该字符即可：

如果同时有两种分隔符号（例如空格与逗号），只要两个都一起勾选就可以了喔！

这里还有两个“特例”可以讲一下，以下例子虽然分隔符号不是那么“规则”或直观，但还是可以用的：

首先，假如我手上有一批地址数据要切得很细，每段宽度不一（例如前面例子的邮政编码有的三码有的五码；或更常见的路名长度不一、“区”有的一个字有的两个字），不能用固定宽度来切，但也没有空格、逗号等区隔符号，该怎么办？

用“查找与替换”制造区隔字符

此时仔细研究一下，会发现我想要分段的东西还是有规则可循的，例如邮政编码跟地址之间一定一边是数字一边是字、其他的区隔多半可以从“县”“市”“乡”“镇”“区”关键字看出。

利用这些规则，搭配 Excel 的“查找与替换”功能，把我们想要切割的地方制造出“,”之类的区隔字符，之后就可以快速利用前面的数据剖析功能了。例如下图：你可以用“县,”替换“县”，“市,”替换“市”，“乡,”替换“乡”，“镇,”替换“镇”... 如果邮政编码跟地址中间没空格，一样可以用“0,”替换“0”，以此类推入。

连续分隔符号视为单一处理

另外一个比较单纯的情形：例如假设我的数据每一段都是用双引号" "括起来，要做数据剖析时，会发现两段之间因为有两个分隔符号，会被额外切出一个空栏。此时只要在设置画面勾选“连续分隔符号视为单一处理”即可：

例如你手上有的是一串名字，它们有用空格区分，但都挤在同一个字段，你最终希望一个名字放在一个单元格，且每一个名字是垂直往下一列一列排的像这样：

那首先还是可以先用数据剖析把它拆成多字段。接着，利用 Excel 的转置功能从栏转置（旋转）到列就可以了，方法为：先选取这些单元格之后右键拷贝，接着到要贴上的地方单击“选择性贴上”并选“转置”。

其实在 Excel，“换行”这个动作本身是可以被识别或转换成某个区隔符号的，把它识别出来、或转换出来，就可以当成一个“分隔符号”来做数据剖析了。（解决这点，接下来有需要的话可以用跟上面一样的方法，用“转置”来将栏换成列）

在数据剖析向导里直接填入快速键“Ctrl+j”

很常见的方法是利用代表“换行符号”的快速键 Ctrl+j ，在数据剖析向导里直接打 Ctrl+j：

很多情况下画面上虽然仍然是空白（肉眼看不到你打的 Ctrl+j ），但你可以看到已经把不同行的数据区隔开了。

利用“查找与替换”将“Ctrl+j”替换成其他分隔符号例如“,”

一样就是在“查找与替换”时，查找“Ctrl+j”，用“,”替换。替换完之后就可以用这个肉眼可见的分隔符号来做操作了，这是很多人用的方法。

其他方式：利用 CHAR(10) 公式

但如果你跟网编一样，不确定是因为 Excel 版本还是 Mac 计算机的关系，用了“Ctrl+j”或是其他任何江湖传言的换行符号，都没有任何改变，可以来试试另一个方法： 用 CHAR(10) 公式来找。

CHAR 这个公式的意思是“获取计算机字符编码代表的值”，而 10 是在 Mac 跟 Windows 计算机里都可以代表“换行”的编码，所以 CHAR(10) 就是换行符号的意思。

可以利用一个效果跟“查找与替换”类似的公式： SUBSTITUTE 公式，来做到把换行符号代换成其他肉眼可见符号的方法。

SUBSTITUTE 在 Excel 的用法是这样：

=SUBSTITUTE(参照字段值,参照字段中要被代换的值,要把被代换的值换成什么值)，例如“一百三十元”要换成“一百三十圆”，那就是SUBSTITUTE(参照字段值,元,圆)。

因此如果要以“,”替换换行符号，公式就是这样写：

用公式获取有区隔符号的值后再做一次数据剖析就可以了。

计算机、自动化处理批次数据这种事情就是需要有个规则，所以真的理不出一个规则的话就真的就没办法了，可能还是得从源头数据着手，让数据从源头就用比较好处理的方式呈现、汇出。

有时候你可能有跟“分拆字段”相反的需求：要把多个字段归并成一个，例如手上有“姓”和“名”要合成“姓名”，手上有邮政编码、县市乡镇等，要组合成一个完整的地址字段，或是要把姓名、地址等字段集结成单一个出货备注字段。

这些在 Excel 大致上可以用字符串公式搭配一些变化版达成，如下。

如果我手上拿到的数据是这样：“106”“台北市”“大安区”“南昌路二段222号 ”，想要集成成单一单元格的完整地址“106 台北市大安区南昌路 二段222号 ”，这是最单纯的需求，可以用 Excel 的字符串公式 & 或是归并多个字符串的公式 CONCAT 或 CONCATENATE 来做。

举例来说，“106”“"台北市"”“"大安区"”“"南昌路二段222号" ”，想要集成成没有双引号区隔的单一单元格完整地址“106 台北市大安区南昌路二段222号 ”，那么可以利用“抽取部分字符串”的公式 MID 抽取去掉头尾的字符串，再利用前面字符串相加的公式串起来即可。

＊要抽取的字符串有固定字符长度的话，MID 公式里填入所需的字符长度即可；没有固定字符长度的话可以找出规律，例如下图“其余地址”这个字段要取的字符数会随着地址长短而有所不同，但确定的是都要取"之后到“号”这个字之间的字符数，因此搭配 SEARCH 公式 “SEARCH("号",D2)-1”获取所要的数字填入。

跟“单纯多并一”的情况相比，只需要在字符串公式中插入换行符号。

前面有讲到，在 Excel 公式里要系统性的使用换行符号可以用 CHAR(10) 来做，因此只要在 =(单元格1&单元格2&单元格3)这样的公式中，插入 CHAR(10) 像这样： =(单元格1&CHAR(10)&单元格2&CHAR(10)&单元格3)，另外记得在该单元格上的“常用”页面签勾选“自动换行”图符，就可以了。

如果你是 Ragic 订户，偶尔会拿到一些不太符合原本 Ragic 表单格式的数据，你可以考虑利用上面讲的 Excel 功能拆分或归并单元格，清整数据之后再汇入 Ragic，就不用在 Ragic 为了整顿数据创建多余字段，可以省很多力。

不过如果你是固定会需要这样拆字段/归并字段之后汇入 Ragic ，评估有需要的话，以上需求也可以在 Ragic 用公式来做。 Ragic 的公式好处是：逻辑类似 Excel 、好上手，但一批数据只要套用一次公式在字段首部即可，省力且不易在编辑数据/拷贝字段值时被意外错改。

Ragic 目前没有 Excel 的“数据剖析”功能，因此一次性的字段拆分其实还是比较建议在 Excel 整理。不过如果需要系统性在 Ragic 做这种事的话，Ragic 也支持各种字符串抽取的公式： MID, LEFT, RIGHT 等取出部分字符串的公式，以及“找出某个字符在字符串中位置”的公式：FIND（无论该字符是中文或英文，在 Ragic 都可以使用 FIND，不需要特别使用 SEARCH，例如FIND('吴',A1) 就是找出 A1 字段中“吴”是第几个字）。

因此，再以前面的地址为例，假如手上有的是完整地址，要拆分成不同字段的话可以像这样：

Ragic 也支持“字符串相加”的公式，但逻辑比 Excel 更简单一些，不用 & 而是用加号“+”。只要是非数字字段使用“+”，例如“A1+A2”，就等同于 Excel “=A1&A2”的意思。邮政编码、县市等组成完整地址字段的例子，公式就会像这样：

需要抽取部分字符串相加时，由于如同上面所说，Ragic 也支持各种抽取字符串的公式，直接搭配抽取字符串与 + 一起使用即可。

至于“搭配换行”的部分，Ragic 采用的是换行符号 "\r\n"，可以直接参阅这篇文章的说明来轻松达成订单内备注换行等功能。

标签: Excel

归类: 云工作术, 表格技巧