数据提取+扣减=想要的剩余数据

zhiwen20190717 · 发表于 2024-6-13 15:16

如图所示，H列提取规则：依据A表中的单据编号查找B表单据编号对应的规格,并且从A表规格中减去B表对应规格，返回剩余规格，

本人使用的是WPS，数据约有几千行。

part98 · 发表于 2024-6-13 15:22

本帖最后由 part98 于 2024-6-13 15:26 编辑

=TEXTJOIN(",",,TEXTSPLIT(B3,TEXTSPLIT(VLOOKUP(A3,D:E,2,0),","),",",1))

hugeinfo · 发表于 2024-6-13 15:26

=TEXTJOIN(",",,FILTER(TEXTSPLIT(INDEX(B:B,MATCH(G3,A:A,)),,","),NOT(BYROW(N(TEXTSPLIT(INDEX(B:B,MATCH(G3,A:A,)),,",")=TRANSPOSE(TEXTSPLIT(INDEX(E:E,MATCH(G3,D:D,)),,","))),LAMBDA(x,SUM(x))))))

我走我流 · 发表于 2024-6-13 15:32

=REGEXP(TRIM(REGEXP(REGEXP(ASC(B3),"\,",2," "),SUBSTITUTE(ASC(VLOOKUP(A3,D:E,2,)),",","|"),2,"")),"[\s]+",2,",")

wbjjzhu · 发表于 2024-6-13 17:24

=TEXTJOIN(",",,REGEXP(REGEXP(VLOOKUP(A3,A表!$A$2:$B$5,2,FALSE),VLOOKUP(A3,B表!$A$2:$B$5,2,FALSE),2),"[\w]+"))

复制代码

cnmlgb9998 · 发表于 2024-6-13 20:12

PQ 难道就不香么？？？？？？？？？？？？？？？

mykitami · 发表于 2024-6-13 20:44

=TEXTJOIN(",",,TEXTSPLIT(B3,,VSTACK(",",VLOOKUP(A3,D:E,2,)),1))

inrever · 发表于 2024-6-13 22:29

=TEXTJOIN(",",,REGEXP(CONCAT(TEXTSPLIT(VLOOKUP(G3,A:B,2,),VLOOKUP(G3,D:E,2,))),"\w+\d+"))

复制代码

沈默00 · 发表于 2024-6-14 08:37

H3=TEXTJOIN(",",,UNIQUE(TEXTSPLIT(TEXTJOIN(",",,FILTER(B:B,A:A=G3),FILTER(E:E,D:D=G3)),","),1,1)) 下拉

我的城市你来过 · 发表于 2024-6-15 08:19

=ARRAYTOTEXT(REGEXP(SUBSTITUTE(VLOOKUP($G3,$A:$B,2,),VLOOKUP($G3,$D:$E,2,),""),"[^,]+"))

		自动登录	找回密码
密码			免费注册

[求助] 数据提取+扣减=想要的剩余数据

评分

评分