请教一个模糊匹配的问题

℡﹎.乱﹌

请教一个模糊匹配的问题，如何在f3单元格根据组员信息 ，查找对应的金额

1220360472

试试这个怎么样，也许能解决问题

loirol

=SUMPRODUCT(SUMIF($A$2:$A$7,"*"&TRIM(MID(SUBSTITUTE(F2,"、",REPT(" ",100)),ROW(INDIRECT("a1:a"&(LEN(F2)-LEN(SUBSTITUTE(F2,"、",""))+1)))*100-99,99))&"*",$B$2:$B$7))

hcm19522

F2:G2 {=SUM(MMULT(N(ISNUMBER(0/(FIND(TRIM(MID(SUBSTITUTE(F2,"、",REPT(" ",99)),COLUMN($A:$I)*99-98,99)),"@"&$A2:$A7)>1))),ROW(1:9)^0)*$B2:$B7)

另解=SUMPRODUCT(SUMIF($A2:$A7,"*~"&TRIM(MID(SUBSTITUTE(F2,"、",REPT(" ",99)),COLUMN($A:$I)*99-98,99))&"*",$B2:$B7))


F11:G11{=SUM(MMULT(N(ISNUMBER(0/(FIND(TRIM(MID(SUBSTITUTE(F10,"、",REPT(" ",99)),COLUMN($A:$I)*99-98,99)),"@"&$A10:$A15)>1))),ROW(1:9)^0)*$B10:$B15/(LEN($A10:$A15)-LEN(SUBSTITUTE($A10:$A15,"、",))+1))

℡﹎.乱﹌

loirol 发表于 2022-3-7 09:40
=SUMPRODUCT(SUMIF($A$2:$A$7,"*"&TRIM(MID(SUBSTITUTE(F2,"、",REPT(" ",100)),ROW(INDIRECT("a1:a"&(LEN( ...

能否求得项目里面的报销人，如属于组内人员，那么返回相应的金额，否则返回空

℡﹎.乱﹌

hcm19522 发表于 2022-3-7 11:17
F2:G2 {=SUM(MMULT(N(ISNUMBER(0/(FIND(TRIM(MID(SUBSTITUTE(F2,"、",REPT(" ",99)),COLUMN($A:$I)*99-98,9 ...

能否返回对应的金额值，不用求和可以吗

℡﹎.乱﹌

这个位置不对的 谢谢你

zpy2

℡﹎.乱﹌ 发表于 2022-3-7 17:09
能否返回对应的金额值，不用求和可以吗

create temp table aa as select 项目,金额 from 模糊匹配金额; create temp table bb as select rowid as old_rowid,`1组`,`2组` from 模糊匹配金额 where `1组`!=''; select * from aa limit 15; select * from bb; cli_one_dim~temp.bb~1; cli_split_data~bbunion~、~数量; select 属性 组,group_concat(数量) 姓名,group_concat(金额) 金额,sum(金额) 合计,group_concat(项目) 项目 from bbunionsplit join aa on aa.项目 like '%'||bbunionsplit.数量||'%' group by 属性;

loirol

℡﹎.乱﹌ 发表于 2022-3-7 15:30
能否求得项目里面的报销人，如属于组内人员，那么返回相应的金额，否则返回空

把两个金额合并显示在单元格里？

loirol

332 超级火车  数组

=textjoin("、",,IF(ISNUMBER(FIND(TRIM(MID(SUBSTITUTE(F2,"、",REPT(" ",100)),ROW(INDIRECT("a1:a"&(LEN(F2)-LEN(SUBSTITUTE(F2,"、",""))+1)))*100-99,99)),TRANSPOSE($A$2:$A$7))),TRANSPOSE(MMULT($B$2:$B$7,COLUMN(INDIRECT("a1:a"&(LEN(F2)-LEN(SUBSTITUTE(F2,"、","")))+1))/COLUMN(INDIRECT("a1:a"&(LEN(F2)-LEN(SUBSTITUTE(F2,"、","")))+1)))),""))