求大神帮助写一个公司，一列数据平均分配给textjoin几个人员使用

我要成为专家

区域2

1. =IFERROR(INDEX(TEXTSPLIT(TEXTJOIN(",",,SUBSTITUTE($B$2:$B$4&",",",",":"&$A$2:$A$4/(LEN($B$2:$B$4)-LEN(SUBSTITUTE($B$2:$B$4,",",""))+1)&",")),":",",",1),ROW(A1),COLUMN(A1)),"")

复制代码

我的城市你来过

=TEXTSPLIT(SUBSTITUTE(B2&",",",",","&A2/(LENB(B2)-LENB(SUBSTITUTE(B2,",",""))+1)&","),",")

=TEXTSPLIT(TEXTJOIN(",",,$B$2:$B$4),,",")
=FILTER($A$2:$A$4,LENB(SUBSTITUTE($B$2:$B$4,K2,""))-LENB($B$2:$B$4)<0)/COUNTA(TEXTSPLIT(FILTER($B$2:$B$4,LENB(SUBSTITUTE($B$2:$B$4,K2,""))-LENB($B$2:$B$4)<0),","))

mykitami

=TEXTSPLIT(TEXTJOIN(" ",,IF({1,0},TEXTSPLIT(B2,,",",1),A2/ROWS(TEXTSPLIT(B2,,",",1))))," ",,1)

=DROP(REDUCE("",B2:B4,LAMBDA(x,y,VSTACK(x,IF({1,0},TEXTSPLIT(y,,",",1),OFFSET(y,,-1)/ROWS(TEXTSPLIT(y,,",",1)))))),1)

swl4561255

C2数组公式，右拉下拉

1. =IF(B2<>"",IF(MOD(COLUMN(),2),MID(SUBSTITUTE($B2,",",),INT((COLUMN(A1)-1)+1),2),$A2/(LEN($B2)-LEN(SUBSTITUTE($B2,",",))+1)),"")

复制代码

K2公式，下拉

1. =MID(SUBSTITUTE(PHONETIC(B$2:B$4),",",),2*ROW(A1)-1,2)

复制代码

L2数组公式，下拉

1. =INDEX(A$2:A$4,MATCH("*"&K2&"*",B$2:B$4,))/SUM(COUNTIF(INDEX(B$2:B$4,MATCH("*"&K2&"*",B$2:B$4,)),"*"&K$2:K$7&"*"))

复制代码

hcm19522

C2:H4=IF(MOD(COLUMN(A1),2),INDEX(TRIM(MID(SUBSTITUTE($B2,",",REPT(" ",99)),COLUMN($A:$I)*99-98,99)),INT(COLUMN(B1)/2)),IF(B2="","",$A2/SUMPRODUCT(N(MID($B2&",",ROW($1:$99),1)=","))))

K2{=INDEX(TRIM(MID(SUBSTITUTE(B$2:B$4,",",REPT(" ",99)),COLUMN(A:I)*99-98,99)),LEFT(MIN(IF((COUNTIF(K$1:K1,TRIM(MID(SUBSTITUTE(B$2:B$4,",",REPT(" ",99)),COLUMN(A:I)*99-98,99)))=0)*(TRIM(MID(SUBSTITUTE(B$2:B$4,",",REPT(" ",99)),COLUMN(A:I)*99-98,99))<>""),{1;2;3}/1%+COLUMN(A:I),399))),RIGHT(MIN(IF((COUNTIF(K$1:K1,TRIM(MID(SUBSTITUTE(B$2:B$4,",",REPT(" ",99)),COLUMN(A:I)*99-98,99)))=0)*(TRIM(MID(SUBSTITUTE(B$2:B$4,",",REPT(" ",99)),COLUMN(A:I)*99-98,99))<>""),{1;2;3}/1%+COLUMN(A:I),399))))&""

L2{=IF(K2="","",SUM(IFERROR((C$2:G$4=K2)*D$2:H$4,)))

17601211544

无关岁月 发表于 2024-4-16 08:10
365参与一下

大神，路过来下帮写个简便好用的公式吧