根据户主或身份信息把家庭成员按行排列

hcm19522

H2:M6{=IFERROR(IF(COLUMN(A1)>INDEX(FREQUENCY(ROW($2:$10),IF(RIGHT($D$2:$D$10,2)="户主",ROW($1:$9))),ROW(A2))*2,"",OFFSET($B$1,SMALL(IF(RIGHT($D$2:$D$10,2)="户主",ROW($1:$9)),ROW(A1))+INT((COLUMN(A1)-1)/2),MOD(COLUMN(B1),2))),"")

erdgh12

=LET(a,SCAN(0,D2:D10="本人/户主",SUM),IFNA(DROP(REDUCE("",UNIQUE(a),LAMBDA(x,y,(VSTACK(x,TOROW(FILTER(B2:C10,a=y)))))),1),""))

mengrs

=IFNA(REDUCE(TOROW({"成员","身份"}&SEQUENCE(5)),FILTER(B2:B10,D2:D10="本人/户主"),LAMBDA(x,y,VSTACK(x,TOROW(IF(LOOKUP(ROW(1:9),ROW(1:9)/(D2:D10="本人/户主"),B2:B10)=y,B2:C10,0/0),3)))),"")

秃头发师

=IFERROR(DROP(REDUCE("",SEQUENCE(4),LAMBDA(arr,brr,VSTACK(arr,TOROW(FILTER(B2:C10,SCAN(0,D2:D10,LAMBDA(x,y,IF(ISNUMBER(FIND("户主",y)),x+1,x)))=brr))))),1),"")

mengrs

=IFNA(LET(a,COUNTIF(OFFSET(D2,,,ROW(1:9)),"本*"),REDUCE(TOROW({"成员","身份"}&SEQUENCE(5)),UNIQUE(a),LAMBDA(x,y,VSTACK(x,TOROW(FILTER(B2:C10,a=y)))))),"")