蒲公英联盟提供ghost win10,ghost win7,xp,装机纯净版,64位旗舰版,绿色软件,免费软件下载基地!

当前位置:主页 > 教程 > 软件教程 > Excel教程 >

相同条件单元格数据的合并

来源:蒲公英系统┆发布时间:2019-05-01 00:05┆点击:

  笔者所在单位今年实行了全国计算机应用等级考试,根据晋升职务级别的不同,考生可选择2至4个模块应试。每个考生具有唯一的档案号,同一次考试合格1个及以上模块者,取得1个合格证书,合格证标注了考生合格的模块。

  假如考生刘XX合格1科,有1科的合格证,考生王XX合格了3科,也同样取得了一个合格证书,合格证显示了合格的3个模块(图1)。为方便查询及便于考生领证签名,需要将图1中相同档案号考生的合格模块进行合并,模块之间用“/”分隔(图2),那么如何实现上述效果呢? 由于相同的档案号有2个、3个和4个三种情况,因此,要合并的模块数不是固定的,要解决此类问题需要用数组公式。

Excel表格中相同档案号的三个模块

图1 Excel表格中相同档案号的三个模块

合并Excle相同的档案

图2 合并Excle相同的档案

具体方法如下:

一、定义名称

  为简化公式,需要对引用单元格区域设置名称。

  执行“插入→名称→定义”命令,打开“定义名称”对话框,将名称定义为“Date”, 在“引用位置”文本框中输入公式:= Sheet1!$C:$C,按下“确定”按钮返回。接着用同样方法,再定义另一个名叫“Code”的名称,里面包括公式:= Sheet1!$A$2: $A$1200,即A列所在的数据区域。

二、输入公式

首先在E2单元各种输入公式:

  “=IF(ISERR(INDEX(Data,SMALL(IF(Code=$A2,ROW(code)),1))),"",INDEX($C:$C,SMALL(IF(code=$A2,ROW(code)),1))&"/") ”。该公式首先对A列进行判断,如果A2单元格内容存在于Code区域,则返回该单元格行号,如果存在重复,则取最小的行号。再用函数INDEX()选取该行对应的Data区域单元格内容,并用"/"分隔。为避免出现错误值,这里还使用了信息函数ISERR(),如果出现错误值,则为空白,否则为公式显示的内容。公式输入完成后,按“Ctrl+Shift+Enter”组合键即可。因为相同档案号最多为4个,因此,还需取第2、第3、第4小的行号。所以还需在F2、G2、H2单元格中依次输入数组公式:

  F2单元格:=IF(ISERR(INDEX($C:$C,SMALL(IF($A$2:$A$21=$A2,ROW($A$2:$A$21)),2))),"",INDEX($C:$C,SMALL(IF($A$2:$A$21=$A2,ROW($A$2:$A$21)),2))&"/") G2单元格: =IF(ISERR(INDEX($C:$C,SMALL(IF($A$2:$A$21=$A2,ROW($A$2:$A$21)),3))),"",INDEX($C:$C,SMALL(IF($A$2:$A$21=$A2,ROW($A$2:$A$21)),3))&"/")

  H2单元格: =IF(ISERR(INDEX($C:$C,SMALL(IF($A$2:$A$21=$A2,ROW($A$2:$A$21)),4))),"",INDEX($C:$C,SMALL(IF($A$2:$A$21=$A2,ROW($A$2:$A$21)),4)))

  以上公式输入完成后都要按“Ctrl+Shift+Enter”组合键结束公式输入。然后在D2单元格输入公式:“=E2&F2&G2&H2”。将E2、F2、G2、H2单元格内容连接起来。此时可以看到合格1至3个模块的考生,单元格内容合并后,结尾多了符号“/”,还须将此多余的符号删除。据此,在I2单元格输入公式:=IF(RIGHT(D2,1)="/",MID(D2,1,LEN(D2)-1),D2) 。

  所有公式输入完成后,选择D2:I2单元格区域采用拖动复制的办法,将公式复制到相应区域。这样就完成了相应数据的合并。最后还需要删除公式,具体方法是:复制所有含公式的区域,执行“编辑→选择性粘贴”命令,在“选择性粘贴”对话框中,选择“数值”选项即可。

  完成上述操作后还没达到图2的效果,还需使用公式将重复的数据行删除。因此,在J2单元格输入公式:=IF(A2<>A3,A2,"") 。并复制到相应数据区域,再使用排序或筛选的办法将J列空白区域删除,对数据区域作适当处理就完成了图2所示的效果。

尚未注册畅言帐号,请到后台注册