excel - 查找存在空白单元格的第 2 和第 3 最常见文本字符串的函数
问题描述
我正在尝试使用公式来显示列中第一个、第二个和第三个最常见的文本字符串。这个公式有效,但前提是我指定了一个没有空白单元格的特定范围。这样做的问题是列表通常通过在底部添加一行来更新,因此范围需要是动态的(或者我正在尝试做的整个列)。
=IFERROR(INDEX(C:C,MODE(IF(COUNTIF(U$1:U1,C:C)=0,MATCH(C:C,C:C,0)+{0,0}))),"")
非常感谢任何见解。
解决方案
首先在标准模块中输入此 VBA 代码:
Public Function MostCommon(rng As Range) As Variant
Dim rng2 As Range, r As Range, C As Collection, arr(), arr2
Dim cKount As Long, i As Long, Kaller As Range, HowBig As Long
Set rng2 = Intersect(rng, rng.Parent.UsedRange)
Set C = New Collection
Set Kaller = Application.Caller
For Each r In rng2
If r.Value <> "" Then
On Error Resume Next
C.Add r.Value, CStr(r.Value)
On Error GoTo 0
End If
Next r
cKount = C.Count
ReDim arr(1 To cKount, 1 To 2)
For i = 1 To cKount
arr(i, 1) = C.Item(i)
arr(i, 2) = Application.WorksheetFunction.CountIf(rng2, arr(i, 1))
Next i
Call VBA_Sort(arr)
HowBig = Application.WorksheetFunction.Max(cKount, Kaller.Rows.Count)
ReDim arr2(1 To HowBig, 1 To 2)
For i = 1 To HowBig
arr2(i, 1) = ""
arr2(i, 2) = ""
Next i
For i = 1 To cKount
arr2(i, 1) = arr(i, 1)
arr2(i, 2) = arr(i, 2)
Next i
MostCommon = arr2
End Function
Public Sub VBA_Sort(InOut())
Dim i As Long, J As Long, Low As Long, _
Hi As Long, Temp As Variant
Low = LBound(InOut, 1)
Hi = UBound(InOut, 1)
J = (Hi - Low + 1) \ 2
Do While J > 0
For i = Low To Hi - J
If InOut(i, 2) < InOut(i + J, 2) Then
Temp = InOut(i, 2)
InOut(i, 2) = InOut(i + J, 2)
InOut(i + J, 2) = Temp
Temp = InOut(i, 1)
InOut(i, 1) = InOut(i + J, 1)
InOut(i + J, 1) = Temp
End If
Next i
For i = Hi - J To Low Step -1
If InOut(i, 2) < InOut(i + J, 2) Then
Temp = InOut(i, 2)
InOut(i, 2) = InOut(i + J, 2)
InOut(i + J, 2) = Temp
Temp = InOut(i, 1)
InOut(i, 1) = InOut(i + J, 1)
InOut(i + J, 1) = Temp
End If
Next i
J = J \ 2
Loop
End Sub
然后选择一个两列块(如E1到F50)并输入以下内容:
=MostCommon(C:C)
如您所见,该函数返回一个简短的频率表,最频繁的项目位于顶部。
数组公式必须用Ctrl++Shift输入,Enter而不仅仅是Enter键。如果正确执行此操作,公式将在公式栏中显示并带有花括号。
推荐阅读
- ios - Xcode 12.5.1 getting this error , any idea?
- node.js - Wait for all promises to finish
- python - Facebook 营销 API、获取见解字段和活动对象字段
- javascript - 可编辑的问题,单选答案
- python - 为什么我的`train_test_split()`返回相同的样本
- java - 无法在对话框片段中运行 Activity 方法
- javascript - 提交表单后使隐藏元素可见
- java - 使用 apache beam (GCP) 写入 Postgres
- python - 使用for循环构建文件名并保存
- python-3.x - 如何将条件分组到“其他”类别