请教老师用公式，找出多行中的相同数，谢谢！

cartel

请教老师用公式，找出多行中的相同数，谢谢！.zip (8.92 KB, 下载次数: 41)

2024-3-19 09:10 上传

点击文件名下载附件

xlllw203

1. =TEXTJOIN(",",,SORT(IF(MMULT(TRANSPOSE(ROW(INDIRECT("1:"&COUNTA(M2:M9))))^0,COUNTIF(OFFSET(M1,ROW(INDIRECT("1:"&COUNTA(M2:M9))),),"*"&TEXTSPLIT(M1,",")&"*"))=COUNTA(M2:M9),TEXTSPLIT(M1,","),""),,1,1))

复制代码

hugeinfo

=TEXTJOIN(",",,SORT(TRANSPOSE(FILTER(TEXTSPLIT(M1,","),COUNTIF(M2:M9,"*"&TEXTSPLIT(M1,",")&"*")=COUNTA(M2:M9))),,1))

jivy1212

1. =TEXTJOIN(",",1,MODE.MULT(IFERROR(--MID(M1:M3,COLUMN(A:Z)*3-2,2),0=1)))

复制代码

2019以上支持，排序就多加个

1. =TEXTJOIN(",",1,SORT(MODE.MULT(IFERROR(--MID(M1:M3,COLUMN(A:Z)*3-2,2),0=1))))

复制代码

严谨点就这样

1. =TEXTJOIN(",",1,IF(MMULT({1,1,1},1-ISERR(FIND(TEXT(COLUMN(A:DD),",00!,"),","&M1:M3&",")))=3,TEXT(COLUMN(A:DD),"00"),""))

复制代码

丢丢表格

jivy1212 发表于 2024-3-19 10:06
2019以上支持，排序就多加个严谨点就这样

我看这题 就是一个快乐八 。。。。

hcm19522

N2{=IFERROR(SMALL(IF(MMULT(COLUMN(A:I)^0,1-ISERR(FIND(","&COLUMN(A:CU)&",",","&M$1:M$9&",")))=COUNTA(M$1:M$9),COLUMN(A:CU)),ROW(A2)),"")

cartel

感谢老师们的回复！
想请老师看下新附件，谢谢！ 请教老师用公式，找出多行中的相同数（新附件），谢谢！.zip (9.5 KB, 下载次数: 6)

2024-3-19 12:34 上传

点击文件名下载附件

hugeinfo

cartel 发表于 2024-3-19 12:35
感谢老师们的回复！
想请老师看下新附件，谢谢！

=TEXTJOIN(",",,SORT(INDEX(TEXTSPLIT(M1,","),,MODE.MULT(TOCOL(MATCH(TEXTSPLIT(TEXTJOIN("/",,$M$2:$M$9),",","/"),TEXTSPLIT(M1,","),),3))),,1))   ？？？？