Excel 怎么合并多列数据
姓名和电话分两列、地址拆成三段,要拼成一列才好导入系统。这篇讲几种拼法各适合什么情况、哪几处最容易出错,以及拼完怎么核对。
姓名和电话分在相邻两列、地址拆成省市区三段,这类表格要拼成一列才好导入系统或者发给别人。手工敲几行还行,几百行就得靠公式。这篇讲入口在哪、几种拼法各适合什么情况、哪几处最容易出错,以及拼完之后怎么核对。
入口在哪
- 公式:直接在新列里写,用连接符把几个单元格接起来。所有版本都有,最通用。
- 函数:CONCATENATE 是老函数,CONCAT 和 TEXTJOIN 只有较新版本才有,老版本输入后会提示名称无效。
- 快速填充:在目标列手打两三个示例,按 Ctrl 加 E,让 Excel 照着规律自己填。适合格式整齐、又不想写公式的场合。
- Power Query:在数据选项卡里从表格或区域导入,进编辑器后选合并列。适合以后还要反复刷新的数据。
具体步骤
- 先在原表右侧插一列空的辅助列,别直接盖掉原数据,拼错了还能重来。
- 在第一行写公式。要拼 A 列和 B 列,就写
=A2&B2,数字和文字会直接粘在一起,中间不留空格。 - 需要分隔符的,在表格空白处放一个单元格,里面只填分隔符,比如一个逗号,然后把它引用进来并锁定,写成
=A2&$D$1&B2。这样做的好处是公式里不用出现引号,以后换分隔符也只改一个格子。 - 回车确认结果,再双击单元格右下角的小方块,公式会自动填到最后一行。
- 确认无误后选中这一列,复制,再原地选择性粘贴成数值,把公式去掉,避免以后删列导致错位。
- 原列不再需要的话,先把拼好的列复制到别的工作表留底,再去删原列。
只是把几段文字接起来、不介意空单元格,用 CONCAT 更直观;想让空单元格不产生多余分隔符,用 TEXTJOIN,它的第一个参数填分隔符,第二个参数控制是否忽略空白。这两个函数对版本有要求,换台电脑打不开多半就是这个原因。
几个坑
- 拼接结果是文本,不是数字。拿它去求和会得到 0,要参与计算得先转回数值。
- 日期和金额会走样。日期拼进文本时可能变成一串五位数字,小数位也可能被截断,要先用 TEXT 函数规定格式再拼。
- 前导 0 会丢。编号里 0 开头的,拼完变短,导入系统时对不上,得把源列按文本格式处理。
- 相对引用和绝对引用弄反了,往下拖之后分隔符所在单元格也跟着动,结果整列串位。
- 原列还有别的地方在引用,直接删会让那些公式集体报错,动手前先查一遍。
- 快速填充是猜的,不是算的。样例给得少、格式不统一,它会自作主张,必须整列看一遍。
- 合并后的列里可能带进不可见字符,粘贴到系统里既不报错也不匹配,用清空空格一类的处理过一遍更稳。
怎么验证
- 抽三行回原表逐字比对,重点看有没有多出或者少了分隔符。
- 用长度函数量一遍,比如
=LEN(D2),同一格式的行长度应该一致,突然短一截的往往是掉了内容。 - 筛一下空值,拼出来是空的行,说明源数据这一行本身有问题。
- 另存一份,用文本编辑器或者导入预览看一眼,确认编码和分隔符都对,再拿去用。
常见问题
合并后日期变成一串数字
日期在 Excel 里本质是序列号,拼进文本就露出原形。用 TEXT 函数把日期那一格先转成想要的年月日格式,再参与拼接,结果就正常了。
合并后想求和,结果是 0
拼接产出的是文本,求和函数当成 0 处理。选中这一列用分列或者乘 1 的办法转成数值;也可以只把数字那几段单独求和,不必拼起来再算。
几十列要拼,一列列写公式太慢
列多行多的时候改用 Power Query 的合并列,选好要拼的列和分隔符,一次生成,以后源数据变了刷新一下就行,也省得手工维护几十个公式。
一句话总结:先插辅助列、用连接符把单元格接起来、分隔符放单独单元格再引用,最后粘贴成数值;拼完务必数长度、抽三行回原表核对。