Question

excel求助公式

A B C D E F G H I
05 14 15 16 24 29 05 15
02 07 09 25 31 32 09 07
06 14 19 23 25 32 12 23
07 16 17 20 25 26 04 26
07 16 18 24 28 29 06 07
09 10 12 16 18 32 15 32
03 06 11 21 24 31 10 03
06 07 09 12 17 24 09 12
02 03 04 05 18 28 01 05
03 09 17 20 21 29 04 20
假设
A、B、C、D为一组
E、F、G、H为另一组
在 I 列通过公式统计相同的数
例如：
$A2:$D2与$E2:$H2中相同的数字为
“05”和“15”
那么就在$I2中显示“05,15”

Answer 1

【方括号】回答的相似，
但是，若当E1=5时，使用【方括号】提供的公式，得到的结果是500，5，15

本人作了一个公式，但是，也存在缺陷，但是，可以找到重复的数字，就是多了一些字符
在I列输入：
=CONCATENATE(IF(ISERROR(HLOOKUP($E1,$A1:$D1,1,FALSE))=TRUE,"",HLOOKUP($E1,$A1:$D1,1,FALSE)),"、",IF(ISERROR(HLOOKUP($F1,$A1:$D1,1,FALSE))=TRUE,"",HLOOKUP($F1,$A1:$D1,1,FALSE)),"、",IF(ISERROR(HLOOKUP($G1,$A1:$D1,1,FALSE))=TRUE,"",HLOOKUP($G1,$A1:$D1,1,FALSE)),"、",IF(ISERROR(HLOOKUP($H1,$A1:$D1,1,FALSE))=TRUE,"",HLOOKUP($H1,$A1:$D1,1,FALSE)))

Answer 2

I1=SUBSTITUTE(SUBSTITUTE(TEXT(SUMPRODUCT(TEXT((COUNTIF(A1:D1,E1:H1)>0)*E1:H1,"00")*10^{6,4,2,0}),REPT("-00",SUMPRODUCT(COUNTIF(A1:D1,E1:H1)))),"-",,1),"-",",")
向下复制公式。

追问

你好，要是数值格式的呢，该怎么改公式。以上不是文本格式的

追答

因为结果带逗号，且放在一个单元格里，只能是文本格式，不可能是数值格式的。
如果只取出一个数，即结果无逗号的，可以用加0，减0，乘1，除1，--等方法转换为数值。

追问

这个公式还有点小问题，麻烦大师改一下。
$e:$h里有相同的数字时,“I列”结果就出现了如下问题
A B C D E F G H I
01 09 17 24 26 31 05 09 09
12 12 13 17 12 12 15 31 12,12,00,00
17 18 23 24 17 26 04 17 170000,17
01 04 05 09 01 01 13 15 10100,00
01 12 18 19 21 24 10 18 18

追答

是的，公式没有考虑重复问题。
不过有重复的是只要一个还是重复的都要？
今天太晚了，来不及考虑了，不好意思！

追问

好的，明天再帮我看看吧，重复的只要一个

追答

不好意思，只做出这个，即重复数都显示。
=SUBSTITUTE(SUBSTITUTE(TEXT(SUBSTITUTE(SUMPRODUCT(TEXT((COUNTIF(A1:D1,E1:H1)>0)*E1:H1,"00")*10^{6,4,2,0}),"00",),REPT("-00",COUNT(0/COUNTIF(A1:D1,E1:H1)))),"-",,1),"-",",")
还是数组公式，按Ctrl+Shift+Enter结束公式输入。

Answer 3

1.G2输入:
=SUMPRODUCT((COUNTIF(A2:F2,ROW($1:$10)-1)>0)/1)
2.H2输入:
=IF(COUNTIF(A2:G2,"0"),"",0)&SUBSTITUTE(SUMPRODUCT((COUNTIF(A2:F2,ROW($1:$10)-1)=0)*(ROW($1:$10)-1)*10^(10-ROW($1:$10))),"0",)
如果非要用逗号分隔,那么H2公式如下:
=SUBSTITUTE(MID(TEXT(IF(COUNTIF(A2:G2,"0"),"",0)&SUBSTITUTE(SUMPRODUCT((COUNTIF(A2:F2,ROW($1:$10)-1)=0)*(ROW($1:$10)-1)*10^(10-ROW($1:$10))),"0",),REPT("-0",10-SUMPRODUCT((COUNTIF(A2:F2,ROW($1:$10)-1)>0)/1))),2,100),"-",",")

Answer 4

用宏程序简单，公式不会