表格中同一个人的多项列备注变成横向行标记

LCWEM123

表格中同一个人的多项列备注变成横向行标记，求公式

你是那块小饼干

1. =IF(ISNUMBER(MATCH($J2&$K2&L$1,$B$2:$B$11&$C$2:$C$11&$D$2:$D$11,0)),"√","")

复制代码

又拉下啦

lachenfei

1. =IF(XLOOKUP($J7&$L$1:$R$1,$B:$B&$D:$D,$D:$D,"",0)<>"","√","")

复制代码

mykitami

=LET(s,PIVOTBY(HSTACK(MATCH(B2:B21,B2:B21,),B2:C21),HSTACK(MATCH(D2:D21,D2:D21,),D2:D21),D2:D21,LAMBDA(x,CONCAT(IF(x<>"","√",""))),0,0,,0,,B2:B21<>""),a,DROP(TAKE(s,,3),2,1),b,DROP(s,1,3),HSTACK(VSTACK({"编号","姓名","性别"},HSTACK(SEQUENCE(ROWS(a)),a)),b))

richardzhu15

=DROP(PIVOTBY(B2:C11,D2:D11,D2:D11,LAMBDA(x,IF(x>0,"√","")),0,0),,-1)

zpy2

create temp table aa as
select row_number() over () 序,姓名 from Sheet1 group by 姓名;
create temp table bb as
select 序 编号,姓名,性别,类别,勾选 from aa join Sheet1 using(姓名);
cli_create_two_dim~bb~类别~勾选;
select * from bb_two_dim;

mjm04

参与下，高版本可用，一步到位的

=PIVOTBY(B2:C21,D2:D21,D2:D21,LAMBDA(a,IF(COUNTA(a)=1,"√","")),,0,-1,0,,D2:D21<>"")

忙到飞起

=LET(a,COUNTIFS($B$2:$B$11,$J2,$C$2:$C$11,$K2,$D$2:$D$11,L$1),IF(a,SUBSTITUTE(a,1,"√"),""))
写的不好，填充

solextrade

=REPT("√",COUNTIFS($B:$B,$J2,$C:$C,$K2,$D:$D,L$1))