您的位置: 开心技术乐园 > 微技术 > 阅读文章

Excel 单元格合并高级技巧 – 各种复杂合并方法

 日常的工作中,我们经常需要对一些单元格进行合并。比如公式的产品编号,现在变更了配方,需要在原来的编号前添加N,也就是将一列N字符和原来的产品编号合并。对于类似的合并,手工操作显然费时费力,其实我们可以有更为高效的合并方法。

就地取材 用好Excel简单合并功能

  对于上述类似的合并需求,Excel本身已经自带很多合并功能,我们只要简单使用即可完成合并操作。以上述介绍的合并需求为例,假设原始数据在A列,选中B2,在其中输入“=”N”&A1”(&是Excel的连字符,因为这里连接的是“N”这个字符,因此注意N需要使用半角双引号标注),将这个公式下拉即可完成字符的合并了。

  使用“&”可以将任意字符(或指定列)连接起来,比如很多原来的淘宝地址是按照省市区(县)分列,现在需要将地址连接起來。同样在需要合并的单元格如G1输入“=C1&D1&E1&F1”,然后下拉即可完成地址合并。

  因为可以在连接时使用字符,因此我们可以根据自己的实际需要进行说明字符的添加。比如很多员工户籍和实际居住地址是不同的,现在需要在上述地址前添加“xx户籍地址是”这样的字符,那么将上述公式更改为“=A1&”户籍地址是”&B1&C1&D1&E1”即可。

  当然对于类似的简单合并,Excel还有很多组件可以直接使用。比如自动填充功能,对于上述“N”字符的添加,只要在C1、C2输入两个示例,然后向下自动填充即可。而且自动填充可以智能学习,所以对于一些特殊的填充,比如上述示例中是需要在GQ后面添加“N”,同上在C1、C2输入GQN4567、GQN4568,然后向下填充即可。

使用CHAR函数实现合并后换行

  如上述地址示例需要合并,并且合并完成后地址和姓名要分为两行,同时在名字后面添加“(收)”字,这样打印后可以直接用于信封地址上,对于这列合并可以借助CHAR()函数实现。

  在G1输入公式“=B1&C1&D1&E1&CHAR(10)&A1&”(收)””,这里Char函数用于转换ASCII码字符,其中“10”是指定ASCII代码(→),表示换行,公式的意思是将B1到E1的内容和A1连接(A1和“(收)”字符连接),同时从A1内容开始换行。这样下拉函数后上述地址信息会被自动合并到G列,右击G列任意单元格设置为“自动换行”,接着调整单元格大小后就可以实现指定的合并要求了。

使用VLOOKUP函数实现特定单元格合并

  在日常统计中,我们需要对一些指定单元格的数据进行合并。比如公司最近人员招聘不顺利,因此公司鼓励内部员工介绍他们的同学、老乡到公司来应聘,现在需要对内部员工成功介绍入职新员工进行奖励。由于每个员工介绍的新员工人数不同,月底需要对员工介绍的人数进行统计,即将B列对应A列介绍人进行合并在一起以方便统计。

  显然这里是需要对同一介绍人对应的新员工内容进行合并,此类合并可以借助VLOOKUP函数完成。由于VLOOKUP函数默认只能对单一条件进行查找和统计,但是这里每个介绍人对应多个员工,因此首先需要对其稍加改造。在A列前插入一列辅助列,接着在A2输入公式“=B2&COUNTIF($B$2:B2,B2)”并向下填充,这里使用COUNTIF函数对B列内容进行统计,这样一个介绍人就可以变为“张三1”、“张三2”形式,从而实现条件的单一。

  继续在G2输入公式“=IFERROR(VLOOKU P($F$2&ROW(A1),A:C,3,0),””)”。公式首先使用ROW(A1)函数与介绍员工所在单元格(B列)组成一个序列,这样通过VLOOKUP函数就可以实现一一对应的查询。VLOOKUP查找目标是F列单元格(即输入需要统计介绍员工的名称),查找范围则为A:C列内容,3是返回值,表示的是上述查找范围的第3列(即入职新员工),参数0表示精确查找。最外层嵌套的则是IFERROR函数,它对返回值进行判断,如果有则返回精确值,否则显示为””(即为空,这样在单元格不会出现类似A/N数值),公式向下填充即可。

  可以看到只要输入员工名称,函数就会自动列出其介绍的新员工。接下来将列出的员工使用上述介绍的&连接符,将其连接到一个单元格并使用顿号隔开,即可完成所需的合并操作了。

    • 本文标签:
    • 人气热度:2,587
    • 生产日期:2018年4月29日 - 15时23分50秒

    error: Content is protected !!