blocks|key|1601271|text|下面的VBA函数是一个UDF，这意味着它类似于一个普通的Excel工作表函数，但它的执行完全是为了做你想做的事情。将其安装在标准代码模块中。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1601272|Function+CountDuplicates(Rng+As+Range)+As+Integer
'+set+a+Reference+to+"Microsoft+Scripting+Runtime"

++++Dim+Fun+As+Integer++++++++++++++++++++++'+function+return+value
++++Dim+Uniques+As+Scripting.Dictionary+++++'+list+of+occurrences
++++Dim+Arr+As+Variant++++++++++++++++++++++'+array+of+all+values
++++Dim+C+As+Long

++++Set+Uniques+=+CreateObject("Scripting.Dictionary")
++++Arr+=+Rng.Value
++++With+Uniques
++++++++For+C+=+1+To+UBound(Arr,+2)
++++++++++++If+Not+IsEmpty(Arr(1,+C))+Then
++++++++++++++++If+.Exists(Arr(1,+C))+Then
++++++++++++++++++++.Item(Arr(1,+C))+=+.Item(Arr(1,+C))+%2B+1
++++++++++++++++Else
++++++++++++++++++++.Add+Arr(1,+C),+0
++++++++++++++++End+If
++++++++++++End+If
++++++++Next+C

++++++++For+C+=+0+To+.Count+-+1
++++++++++++Fun+=+Fun+%2B+.Items(C)
++++++++Next+C
++++End+With

++++CountDuplicates+=+Fun
End+Function|code-block|syntax|javascript|1601273|标准代码模块是必须添加到项目中的模块。它的默认名称将类似于Module1，但您可以将其更改为任何您喜欢的名称(错误的语法名称将被拒绝)。例如，通过在任意单元格中输入函数调用来调用工作表中的函数。|1601274|=+CountDuplicates(A2:DD2)|1601275|此函数将返回在定义的范围内计数的所有重复数，不包括唯一值。看看代码。当第一次找到一个项目时，会对其记录值为0。此后，每次再次找到它时，都会将已找到的递归次数加1。最后，将所有值相加，以返回总计数。此方法确保所有第一次出现的次数都将计入0(表示不计入)。返回的合计中只包含重复次数。|1601276|与其他Excel函数一样，结果将出现在包含公式的单元格中。您可以像复制其他公式一样复制该公式，这意味着上面的原始公式必须位于第2行。如果您将其粘贴到其他位置，请考虑使用绝对寻址来定义操作范围。|1601277|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

The VBA function below is a UDF, meaning it's like a normal Excel worksheet function but doing designed to do precisely what you want. Install it in a standard code module.

<pre><code>Function CountDuplicates(Rng As Range) As Integer
' set a Reference to "Microsoft Scripting Runtime"

 Dim Fun As Integer ' function return value
 Dim Uniques As Scripting.Dictionary ' list of occurrences
 Dim Arr As Variant ' array of all values
 Dim C As Long

 Set Uniques = CreateObject("Scripting.Dictionary")
 Arr = Rng.Value
 With Uniques
 For C = 1 To UBound(Arr, 2)
 If Not IsEmpty(Arr(1, C)) Then
 If .Exists(Arr(1, C)) Then
 .Item(Arr(1, C)) = .Item(Arr(1, C)) + 1
 Else
 .Add Arr(1, C), 0
 End If
 End If
 Next C

 For C = 0 To .Count - 1
 Fun = Fun + .Items(C)
 Next C
 End With

 CountDuplicates = Fun
End Function
</code></pre>

A standard code module is one that you must add to your project. Its default name will be like Module1 but you can change it to anything you like (wrong syntax names will be rejected). Call the function from the worksheet by entering its call in any cell, for example.

<pre><code>= CountDuplicates(A2:DD2)
</code></pre>

This function will return the number of all duplicates counted in the defined range, excluding unique values. Look at the code. When an item is found for the first time a value of 0 is recorded against it. Thereafter, each time it is found again 1 is added to the number of recurrences already found. In the end all values will be added up to return the total count. This method ensures that all first occurrences will be counted as 0 (meaning not counted). Only repeats are included in the returned total.

As with other Excel functions, the result will appear in the cell containing the formula. You can copy that formula down as you do with any other, meaning the original above must be in row 2. If you paste it elsewhere consider the use of absolute addressing to define the action range.

blocks|key|361892|text|如果您将O365与UNIQUE函数一起使用，则可以使用：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|361893|=COUNT(A1:J1)-COUNT(UNIQUE(A1:J1,TRUE,TRUE))|code-block|syntax|javascript|361894|​|361895|📷|atomic|361896|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/f3b67b4b29dc1b7681f8d935f5518544.png|imageAlt^0|9|6|0|0|0|0|1|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@$9|Z|A|10|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|11|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|12|8|@]|D|@]|E|$]]|$1|M|3|N|5|O|7|13|8|@]|D|@$9|14|A|15|1|16]]|E|$]]|$1|P|3|-4|5|6|7|17|8|@]|D|@]|E|$]]]|Q|$R|$5|S|T|U|E|$V|W|X|-4]]]]

If you have O365 with the <code>UNIQUE</code> function, you can use:

<pre><code>=COUNT(A1:J1)-COUNT(UNIQUE(A1:J1,TRUE,TRUE))
</code></pre>

<a href="https://i.stack.imgur.com/QaciV.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/QaciV.png" alt="enter image description here"></a>

blocks|key|1601325|text|另一种方式|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1601326|=COUNT(A1:J1)-SUMPRODUCT(--(FREQUENCY(A1:J1,A1:J1)=1))|code-block|syntax|javascript|1601327|或|1601328|=SUMPRODUCT(--(COUNTIF(A1:J1,A1:J1)>1))|1601329|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Another way

<pre><code>=COUNT(A1:J1)-SUMPRODUCT(--(FREQUENCY(A1:J1,A1:J1)=1))
</code></pre>

or

<pre><code>=SUMPRODUCT(--(COUNTIF(A1:J1,A1:J1)&gt;1))
</code></pre>

I seem to be having issues finding a solution, 

I want to count duplications in a row, the row has 100 columns. I Just want to count many how duplications across the row. 

For example, 

<pre><code>1,2,3,1,4,9,2,9,1,4
</code></pre>

I just want to see how many times the same set of numbers show up. 

<pre><code>1 = 3
2 = 2 
3 = 0
4 = 2
9 = 2
</code></pre>

For example, <code>3 + 2 + 0 + 2 + 2 = 9</code>

This row has 9 duplications. ie the same value is being displayed more than once. However the value is dynamic.

Excel How To Count Dynamic values for Duplicates

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我似乎在寻找解决方案上有问题，我想计算一行中的重复项，该行有100列。我只想计算一下这一行有多少重复项。例如,1,2,3,1,4,9,2,9,1,4我只想看看同一组数字出现了多少次。1 = 32 = 2 3 = 04 = 29 = 2例如，3 + 2 + 0 + 2 + 2 = 9此行有9个重复项。也就是说，相同的值被...

问Excel如何计算重复项的动态值
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Excel如何计算重复项的动态值EN