求大神指点，此公式如何优化自动匹配指定列，不受列位置变动

Lu991

如何自动匹配指定列，不需要固定列表位置限制

solextrade

=SUMIF(INDEX(INDIRECT(A$1&B$1&"!A:Z"),,MATCH("业务员",INDIRECT(A$1&B$1&"!1:1"),)),$A3,INDEX(INDIRECT(A$1&B$1&"!1:1"),MATCH(B$2,INDIRECT(A$1&B$1&"!1:1"),)))

无关岁月

1. =SUMIF(INDIRECT(LOOKUP("座",$B$1:B$1)&"!K:K"),$A3,OFFSET(INDIRECT(LOOKUP("座",$B$1:B$1)&"!a:a"),,MATCH(B$2,'3月'!$B$1:$J$1,)))

复制代码

quqiyuan

1. =SUMPRODUCT(IFERROR(INDIRECT(LOOKUP("座",$B$1:B$1)&"!a:z")*(INDEX(INDIRECT(LOOKUP("座",$B$1:B$1)&"!a:z"),,MATCH("业务员",INDIRECT(LOOKUP("座",$B$1:B$1)&"!1:1"),))=$A3)*(INDIRECT(LOOKUP("座",$B$1:B$1)&"!a1:z1")=B$2),0))

复制代码

Lu991

solextrade 发表于 2024-9-11 12:27
=SUMIF(INDEX(INDIRECT(A$1&B$1&"!A:Z"),,MATCH("业务员",INDIRECT(A$1&B$1&"!1:1"),)),$A3,INDEX(INDIRECT ...

完美解决

你是那块小饼干

=LET(_Y,DROP(REDUCE("",F12:F14,LAMBDA(X,Y,LET(T,INDIRECT("'"&Y&"'!B1:L1"),_T,INDIRECT("'"&Y&"'!B2:L"&COUNTA(INDIRECT("'"&Y&"'!B:B"))),_X,CHOOSECOLS(_T,MATCH({"业务员","销售额","销售毛利"},T,0)),VSTACK(X,GROUPBY(CHOOSECOLS(_X,1),CHOOSECOLS(_X,2,3),SUM,0,0,))))),1),GROUPBY(CHOOSECOLS(_Y,1),CHOOSECOLS(_Y,2,3),SUM,0,0))

Lu991

solextrade 发表于 2024-9-11 12:27
=SUMIF(INDEX(INDIRECT(A$1&B$1&"!A:Z"),,MATCH("业务员",INDIRECT(A$1&B$1&"!1:1"),)),$A3,INDEX(INDIRECT ...

SUMPRODUCT(SUMIF(INDIRECT(ROW($1:$6)&"月!K:K"),$B$1,INDIRECT(ROW($1:$6)&"月!H:H")))
您好，请教下同样的这个公式该怎么写呢，谢谢