vba Excel如何显示范围内重复次数最多的名称？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16949318/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel how to show most repeated name from a range?
提问by netmaster
My Column Ahas
我的Column A有
Apple
Orange
Apple
Aple
Mango
Column B
Apple
Mango
Apple
Mango
Mango
Column C
Apple
Apple
Apple
Apple
Apple
to show most repeated names on a single column (Like Column A) i use this code-
要在单个列（如 A 列）上显示最重复的名称，我使用此代码-
=INDEX(A1:A5,MODE(IF(A1:A5<>0,MATCH(A1:A5,A1:A5,0))))
Code returns most repeated name of col.A: Apple
代码返回 col.A 中重复次数最多的名称： Apple
But that code works fine for single column only. While working with three column its not working. What should I do? I've tried to change A1: C5but its not working.
但该代码仅适用于单列。在使用三列时它不起作用。我该怎么办？我试图改变，A1: C5但它不起作用。
回答by barry houdini
This "array formula" will get the most repeated text
这个“数组公式”将获得最多重复的文本
=INDIRECT("R"&TEXT(MIN(IF(COUNTIF(A1:C5,A1:C5)=MAX(COUNTIF(A1:C5,A1:C5)),ROW(A1:C5)*1000+COLUMN(A1:C5))),"0\C000"),0)
=INDIRECT("R"&TEXT(MIN(IF(COUNTIF(A1:C5,A1:C5)=MAX(COUNTIF(A1:C5,A1:C5)),ROW(A1:C5)*1000+COLUMN(A1:C5))),"0\C000"),0)
confirmed with CTRL+SHIFT+ENTER
用CTRL+ SHIFT+确认ENTER
That will work for ranges up to 999 columns - if there are ties it will only give the first one, working across each row first
这适用于最多 999 列的范围 - 如果有关系，它只会给出第一个，首先在每一行中工作
回答by E.V.I.L.
Here's and idea.
这是和想法。
You find the most repeated word of each column and put the result into a results column. So the most repeated word result of column Awould be in column D row 1, the most repeated word result of column Bwould be in column D row 2, and the most repeated word result of column Cwould be in column D row 3. Then you find the most repeated word in column D, hence giving you the most repeated word from all the columns.
您找到每列中重复次数最多的单词并将结果放入结果列中。所以最重复的词结果column A是in column D row 1，最重复的词结果column B是in column D row 2，最重复的词结果column C是in column D row 3。然后您在 中找到重复次数最多的单词column D，从而为您提供所有列中重复次数最多的单词。
Or you could use a PowerShell script.
或者您可以使用PowerShell 脚本。
回答by Jon Crowell
Try this:
尝试这个：
Sub MostDupesInRange()
Dim ws As Worksheet
Dim rng As Range
Dim cell As Range
Dim dupes As Long
Dim maxDupes As Long
Dim dupeWord As String
Dim dupeTie As Boolean
Set ws = ThisWorkbook.Sheets("Sheet1")
Set rng = ws.Range("A1:D8")
For Each cell In rng
dupes = Application.WorksheetFunction.CountIf(rng, cell)
If dupes > maxDupes Then
maxDupes = dupes
dupeWord = cell.Value
dupeTie = False
End If
If dupes = maxDupes And InStr(1, dupeWord, cell.Value) = False Then
dupeWord = dupeWord & ", " & cell.Value
dupeTie = True
End If
Next cell
If dupeTie = False Then MsgBox dupeWord & "" _
& "appears in the range " & maxDupes & " times."
If dupeTie = True Then MsgBox "The values (" & _
dupeWord & ") appear in the range " & maxDupes & " times."
End Sub
NOTE:This will also detect if there is a tie for values with the most duplicates. My initial answer didn't include this, but it irritated me to leave it incomplete.
注意：这还将检测重复次数最多的值是否存在平局。我最初的回答没有包括这个，但是让它不完整让我很恼火。
