求助各位大神

raisons[蛋糕]

qwer401245 发表于 2024-6-28 14:22
瞎写一个

如果要求,A类一共多少台呢

hcm19522

B2=IF(D2="","",SUBSTITUTE(IF(ROW(A1)=1,SUBSTITUTE("*"&A2,",",",*"),B1),"*"&D2&"*",E2&"*"))

qwer401245

raisons[蛋糕] 发表于 2024-6-28 15:02
如果要求,A类一共多少台呢

raisons[蛋糕]

qwer401245 发表于 2024-6-28 15:15

怎么另外一列,例如我只要求A类

zxy041

=LET(rank1,RANK(D2:D5,D2:D5,0),num,LAMBDA(x,INDEX(D2:D5,x)),REDUCE(A2,rank1,LAMBDA(x,y,SUBSTITUTE(x,num(y),OFFSET(num(y),,1)))))

我这是先把数据表排个序，然后从多位数的开始替换

raisons[蛋糕]

raisons[蛋糕] 发表于 2024-6-28 15:18
怎么另外一列,例如我只要求A类

就是我能求出来A类多少台

夜雨无声1949

替换

1. =TEXTJOIN("*1,",,IFERROR(XLOOKUP(--TRIM(MID(SUBSTITUTE(SUBSTITUTE(A2,"*1",REPT(" ",99)),",",),ROW($1:$99)*99-98,99)),D2:D5,E2:E5),""))&"*1"

复制代码

计算

1. =SUM((IFERROR(--TRIM(MID(SUBSTITUTE(SUBSTITUTE(A2,"*1",REPT(" ",99)),",",),ROW($1:$99)*99-98,99)),0)=D2)*1)

复制代码

我走我流

仅供  CAN ka0  

问题1：=SUBSTITUTES(A2,SORT(D2:D5,1,-1),SORTBY(E2:E5,D2:D5,-1))
问题2：=LOOKUP(1,-SEARCH(REPT("*A类*",ROW(1:99)),B2),ROW(1:99))

华尔街操盘手

b2：

1. =LET(a,TEXTSPLIT(A2,"*",","),TEXTJOIN(",",TRUE,DROP(REDUCE("",TAKE(a,,1),LAMBDA(x,y,VSTACK(x,XLOOKUP(--y,D:D,E:E)&"*"))),1)&TAKE(a,,-1)))

复制代码

华尔街操盘手

c2=

1. =TEXTJOIN(",",TRUE,REDUCE("",E2:E5,LAMBDA(x,y,VSTACK(x,y&"一共"&SUM(IFERROR(--TEXTSPLIT(B2,y&"*",","),0))&"台"))))

复制代码