Excel一个单元格比对一列，统计相同项个数

以第二个图为准！G列100万行，速度很重要！

第1个回答 2016-08-23

B1 粘贴公式
=SUM(IF(IFERROR(MATCH(TRANSPOSE(MID(D2,1+LARGE(IFERROR(LARGE(IFERROR(IFERROR(IF(FIND(" ",$D2,ROW(INDIRECT("A1:A"&LEN($D2))))=FIND(" ",$D2,1+ROW(INDIRECT("A1:A"&LEN($D2)))),0,FIND(" ",$D2,ROW(INDIRECT("A1:A"&LEN($D2))))),FIND(" ",$D2,ROW(INDIRECT("A1:A"&LEN($D2))))),0),ROW(INDIRECT("A1:A"&1+LEN(D2)))-1),LEN(D2)+1),1+ROW(INDIRECT("A1:A"&LEN(D2)))),LARGE(IFERROR(LARGE(IFERROR(IFERROR(IF(FIND(" ",$D2,ROW(INDIRECT("A1:A"&LEN($D2))))=FIND(" ",$D2,1+ROW(INDIRECT("A1:A"&LEN($D2)))),0,FIND(" ",$D2,ROW(INDIRECT("A1:A"&LEN($D2))))),FIND(" ",$D2,ROW(INDIRECT("A1:A"&LEN($D2))))),0),ROW(INDIRECT("A1:A"&1+LEN(D2)))-1),LEN(D2)+1),ROW(INDIRECT("A1:A"&1+LEN(D2))))-LARGE(IFERROR(LARGE(IFERROR(IFERROR(IF(FIND(" ",$D2,ROW(INDIRECT("A1:A"&LEN($D2))))=FIND(" ",$D2,1+ROW(INDIRECT("A1:A"&LEN($D2)))),0,FIND(" ",$D2,ROW(INDIRECT("A1:A"&LEN($D2))))),FIND(" ",$D2,ROW(INDIRECT("A1:A"&LEN($D2))))),0),ROW(INDIRECT("A1:A"&1+LEN(D2)))-1),LEN(D2)+1),ROW(INDIRECT("A1:A"&LEN(D2)))+1)-1)),TEXT(A:A,0),0),0)>0,1,0))
Ctrl + Shift + Enter 下拉追问

追答

首先证明一下我的清白，我的公式没有错误。

如果效率过低，尝试以下方法：

按 Alt + F11 进入 VBE，在左侧工程资源管理器右键选择插入、模块，粘贴以下代码在右侧的代码编辑器，关闭 VBE：

Option Explicit
Function MatchCount(RndSour As Range) As Integer
    Dim StrArr() As String, i As Integer, StrCount As Integer, j As Integer
    StrArr = Split(RndSour.Value)
    StrCount = UBound(StrArr)
    MatchCount = 0
    For i = 0 To StrCount
        If StrArr(i) <> "" Then
            For j = 2 To 34
                If Range("A" & j).Value = StrArr(i) Then
                    MatchCount = MatchCount + 1
                    Exit For
                End If
            Next
        End If
    Next
End Function

B2 粘贴公式

=MatchCount(D2)

回车下拉。

追问

速度可以，暂未发现错误。我想取消B列公式，直接显示在E列，并且，小于6的不显示，是不是要快一些？可以再做一下吗？100财富值酬谢。

第2个回答 2016-08-25

上图，在B2单元格输入以下数组公式，按Ctrl+Shift+Enter组合键结束
=SUM(IFERROR(IF(FIND(IF($A$2:$A$34<>"","b"&$A$2:$A$34&"b","a"),"b"&SUBSTITUTE(D2," ","b")&"b")>0,1,0),0))

请尽量减少变换数据后的一题多问。
在补充提问的基础上，下图，在H2单元格输入以下数组公式，按Ctrl+Shift+Enter组合键结束，然后向下填充公式
=IF(SUM(IFERROR(IF(FIND(IF($E$2:$E$34<>"","b"&$E$2:$E$34&"b","a"),"b"&SUBSTITUTE(G2," ","b")&"b")>0,1,0),0))=6,6,"")本回答被提问者采纳

第3个回答 2016-08-23

如果能将只有个位的A列、D列原始数据，改为00格式，会简单很多。
如：1 改为 01
3 改为03追问

00格式，可以，请教我方法。A列全是公式，也能变成00格式？会影响运行速度吗？

追答

也就是文本格式。不会影响速度。
B2格在编辑栏写公式：
=COUNT(IF(FIND(A2,C:C),1,0))
按组合键Ctrl+Shift+Enter结束
下拉填充即可。
把你A列的公式发上来，给你改改

追问

A2 =IF(SUM(COUNTIF(Sheet2!$B$1:$G$3,Sheet2!K1))=1,ROW(A1),"")
有一点很重要：D列有100万行

追答

点中B2右下角句柄（黑小方块），双击就能整表填充。

追问

1、你说B2写入=COUNT(IF(FIND(A2,C:C),1,0)),我数据在D列啊（第一个图），我只需要B列数据，而且都必须正确。

2、00格式，怎么做？你没有教我呀

追答

更正：=COUNT(IF(FIND(A2,D:D),1,0))

A列文本：
A2 =IF(SUM(COUNTIF(Sheet2!$B$1:$G$3,Sheet2!K1))=1,TEXT(ROW(A1),"00"),"")

追问

A列变成文本，没有问题。D列，我手动变了几个00格式，B列输入=COUNT(IF(FIND(A2,D:D),1,0))下拉，显示结果好像都是错误的。况且，你还没有教我怎么变D列为00格式。

追答

如果数据是不断变化，今后经常重复使用，还是要从数据来源解决。如果就此一次过的，可以函数辅助手工解决

追问

D列数据确定以后，不变了。A列数据经常不断地变化。100万行，速度很重要。

追答

那就处理一下D列数据。
A列如果不大于2位数，用上述公式就行

追问

是的，请处理D列

追答

D列中一行最多是多少个数据（一个空格隔开的算一个）

追问

数据太多，速度太慢。10个。为加快速度，取消公式吧，5以下的不显示了吧，显示在E列吧，这样就高级了。

追答

做了一个模版（见附件），你可以参考：

请注意别忽略了这样的操作：

B2格在编辑栏写公式：
=COUNT(IF(FIND(A2,C:C),1,0))
按组合键Ctrl+Shift+Enter结束

追问

B列公式应改下吧，因为是D列每一个单元格都要比对A$2：A$34

追答

A2格输入 =IF(SUM(COUNTIF(Sheet2!$B$1:$G$3,Sheet2!K1))=1,TEXT(ROW(A1),"00"),"")
下拉填充

B2格在编辑栏写公式：
(注意，光标停在编辑栏)
=COUNT(IF(FIND(A2,D:D),1,0))
注意！注意！！注意：按组合键Ctrl+Shift+Enter结束
下拉填充。

D列参考附件处理后，将结果复制，选择性（数值）粘贴回来。

追问

问题依旧。看来，B列公式还是要改的！

追答

按组合链结束公式，做到没有？

是数组公式！

追问

是按那三个键结束的，是数组公式！

公式不行，VBA更好啊？！

追答

那私信聊一下吧

那你用VBA！

第4个回答 2016-08-23