请教老师：如何统计一户家庭的子女属性，谢谢！

hugeinfo

=IF(C2="户主",CONCAT(TEXT(BYCOL(N(ISNUMBER(FIND({"子","女"},FILTER($C$2:$C$23,COUNTIF(OFFSET($C$1,1,,ROW($1:$22)),"户主")=COUNTIF($C$2:C2,"户主"))))),LAMBDA(a,SUM(a))),{"0子;;","0女;;"})),"")

袁勇

假设子女个数都是个位数。

=IF(A2=A1,"",SUBSTITUTE(SUBSTITUTE(TEXT(MMULT(COUNTIFS(A:A,A2,C:C,{"*子","*女"}),{10;1}),"0子0女"),"0女",""),"0子",""))

你是那块小饼干

1. =IF(C2="户主",LET(T,FILTER($C$2:$C$23,$A$2:$A$23=A2),SUM(N(ISNUMBER(FIND("子",T))))&"子"&SUM(N(ISNUMBER(FIND("女",T))))&"女"),"")

复制代码

你是那块小饼干

1. =CONCAT(TOCOL(VSTACK(BYCOL(N(ISNUMBER(FIND({"子","女"},FILTER($C$2:$C$23,$A$2:$A$23=A4)))),SUM),{"子","女"}),,TRUE))

复制代码

丢丢表格

=IF(C2="户主",CONCAT(TEXT(COUNTIFS(A:A,A2,C:C,{"*子","*女"})*{1,-1},"0子;0女;")),"")

不能装孙子，装了就玩P .........

我要成为专家

看户号

1. =LET(s,CONCAT(DROP(FILTER(C:C,A:A=A2),2)),k,LEN(s)-LEN(SUBSTITUTE(s,{"子","女"},"")),IF(C2="户主",CONCAT(FILTER(k&{"子","女"},k<>0)),""))

复制代码

更一般的情况，只看前后户主确定中间子女数量

1. =LET(s,CONCAT(OFFSET(C2,2,,MATCH("户主",VSTACK(C3:$C$23,"户主"),)-2,)),k,LEN(s)-LEN(SUBSTITUTE(s,{"子","女"},"")),IF(C2="户主",CONCAT(FILTER(k&{"子","女"},k<>0)),""))

复制代码

我要成为专家

1. =LET(s,COUNTIFS(A$2:A$23,A2,C$2:C$23,"*"&{"子","女"}),IF(C2="户主",CONCAT(FILTER(s&{"子","女"},s<>0)),""))

复制代码

再来一个更精简的

太迷糊

非常感谢各位老师，大开眼界啊！慢慢研究下看那个更容易理解。

太迷糊

leepoison 发表于 2024-12-20 12:25
=DROP(REDUCE("",A2:A23,LAMBDA(x,y,VSTACK(x,VSTACK(IF(OFFSET(y,,2)="户主",LET(a,CONCAT(FILTER($C$ ...

没有新函数，不过感谢老师！

太迷糊

leepoison 发表于 2024-12-20 12:25
=DROP(REDUCE("",A2:A23,LAMBDA(x,y,VSTACK(x,VSTACK(IF(OFFSET(y,,2)="户主",LET(a,CONCAT(FILTER($C$ ...

版本较低，没那函数，不过感谢老师!