|
小伙伴们，大家好。今天讲解一个按条件合并名单的问题，也可以看做表格转换的问题。 -01- 按户主合并成员名单 1.按户主合并家庭成员名单(不用辅助列) 首先看一下源数据，是社区人员信息表，如下图左表所示。现在要按户主将每户的成员名单合并在一起，效果如右表所示。 这里户主和家庭成员那2列分别用两个不同的公式。户主那一列的公式比较简单，就是用我们之前说过的筛选公式或一对多查询公式。在D2单元格输入公式=INDEX(A:A,SMALL(IF(B$2:B$19="户主",ROW($2:$19),4^8),ROW(A1)))&""，按三键结束，向下填充。 家庭成员这一列的公式就比较复杂了，在E2单元格输入公式=IFNA(TEXTJOIN("、",,OFFSET(INDEX(A:A,MATCH(D2,A:A,)),,,SUM(SMALL(IF(B$2:B$19="户主",ROW($2:$19),20),ROW(1:2))*{-1;1}))),"")，按三键结束，向下填充。 说一下思路吧，首先肯定要按户主姓名将每户的所有成员名单找到，然后用textjoin将其合并起来。户主的姓名可以引用D列的单元格，比如第一个户主是D2单元格的周世华，他所对应的所有成员名单是红色框标记的区域。第二个户主张云生对应的成员名单是蓝框标记的区域。现在的问题是如何利用D列的单元格来找到对应的区域名单。 要找对应的区域，这里考虑用offset，因为offset可以扩展区域。比如第一个户主周世华对应的区域，可以从A2单元格扩展到A6单元格，扩展的行高是5，正好就是第二个户主的行号减去第一个户主的行号，也就是7-2。第二个户主的区域是从A7单元格扩展到A8单元格，扩展的行高是2，同样是第三个户主的行号减去第二个户主的行号，也就是9-7。这样行高的问题就解决了。 SUM(SMALL(IF(B$2:B$19="户主",ROW($2:$19),20),ROW(1:2))*{-1;1})这部分就是计算要扩展的行高。首先判断B2:B19是否等于"户主"，如果等于返回对应的行号，否则返回20，也就是最后一行的下一行，这个还是比较重要的。然后用small提取第1，第2个最小的行号，也就是第一户和第二户的行号；要用第二户的行号减去第一户的行号，后面乘以{-1;1}，第一户的行号变成负数，第二户的行号还是正数，用sum求和就相当于相减。 虽然offset扩展的高度确定了，但扩展的起始位置还没有确定。我们知道第一户是从A2单元格开始扩展，第二户是从A7单元格开始扩展，但是我们只能引用D列的单元格，怎么从D2变成A2，D3变成A7呢？就用到INDEX(A:A,MATCH(D2,A:A,))这部分。用match查找D2在A列中的位置，再用index返回对应的单元格引用。因为index在这里返回的是引用，所以可以作为offset的第一参数。 offset的起始位置知道了，行不偏移，列也不偏移，扩展的高度知道了，那么扩展的区域就找到了。OFFSET(INDEX(A:A,MATCH(D2,A:A,)),,,SUM(SMALL(IF(B$2:B$19="户主",ROW($2:$19),20),ROW(1:2))*{-1;1}))这部分就是扩展的区域。 接下来用textjoin将其连接起来就可以了，由于下拉太多会出错，最外层用ifna处理错误值。总体来说，看公式还是比较复杂，如果理解了思路就不会很难了，可以分步理解。如果不理解，没关系，下面借用辅助列来完成一下，会相对简单很多。 2.按户主合并家庭成员名单(借用辅助列) 首先在C列添加辅助列，在C2单元格输入公式=IF(B2="户主",A2,C1)，向下填充，得到了下图的结果。可以看到每户成员对应的户主是相同的，如红色框标记所示。 户主那一列的公式还是不变，在E2单元格输入公式=INDEX(A:A,SMALL(IF(B$2:B$19="户主",ROW($2:$19),4^8),ROW(A1)))&""，按三键结束，向下填充。 家庭成员那一列的公式为，在F2单元格输入公式=TEXTJOIN("、",,IF(C$2:C$19=E2,A$2:A$19,""))，按三键结束，向下填充。也就是用辅助列C$2:C$19和E2单元格的户主比较，看是否相等。如果相等返回对应的A列的成员姓名，否则返回空文本""。这样刚好找到E2单元格的户主所对应的成员名单，最后用textjoin连接起来就ok了。 如果你感兴趣的话，可以练习起来了。 练习文件在此：https://pan.baidu.com/s/1zvkEW4m53Br_3mTnkZ3b5Q 提取码：0vtk |
|
|
