求出指定差值对应的减数与被减数

zhangstone

各位高手：

   我想求出指定差值对应的减数与被减数，详见附件。

theking_wsz

=LET(a,1-ISERR(FIND(TEXTSPLIT(B2,,","),C2)),b,VSTACK(0,a)+VSTACK(a,0),c,FILTER(TEXTSPLIT(A2,,","),b),TEXTJOIN(",",,c)   wps       excel老版本太难了

hugeinfo

b2=TEXTJOIN(",",,DROP(--TEXTSPLIT(A2,,","),-1,)-DROP(--TEXTSPLIT(A2,,","),1,))

c2=TEXTJOIN(",",,UNIQUE(TEXTSPLIT(TEXTSPLIT(B2,,","),UNIQUE(TEXTSPLIT(B2,,","),0,1),)))

d2=TEXTJOIN(",",,UNIQUE(TOCOL(REDUCE("",--TEXTSPLIT(  C2,,","),LAMBDA(x,y,VSTACK(x,FILTER(HSTACK(DROP(--TEXTSPLIT(A2,,","),-1,),DROP(--TEXTSPLIT(A2,,","),1,)),N(DROP(--TEXTSPLIT(A2,,","),-1,)-DROP(--TEXTSPLIT(A2,,","),1,)=y))))),3)))

mykitami

=LET(_a,TEXTSPLIT(A2,,",",1),_z,--TEXTSPLIT(C2,,"，",1),TEXTJOIN(",",,UNIQUE(REDUCE("",_z,LAMBDA(m,n,VSTACK(m,REDUCE("",_a,LAMBDA(x,y,LET(_b,FILTER(_a,y-_a=n,""),VSTACK(x,IF(_b="","",VSTACK(y,_b))))))))))))

jisijie630

B2:

=TEXTJOIN(",",1,TEXT(MMULT(TEXT(TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",99)),99*(ROW($1:$15)+{0,1})-98,99)),"0;;;!"&{0,999})/1,{1;-1}),"0;;"))

D2:

=TEXTJOIN(",",1,TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",99)),99*IF(FREQUENCY(IF(ISERR(FIND(TEXT(TRIM(MID(SUBSTITUTE(B2,",",REPT(" ",99)),99*ROW($1:$10)-98,99)),"，0，;;;|"),"，"&C2&"，")),11,ROW($1:$10)+{0,1}),ROW($1:$20)),ROW($1:$21),21)-98,99)))

zhangstone

非常感谢，几位高手的鼎力相助。