是否有可能获得作为过滤器结果的列的最通用值(字符串)?VBA [英] is it possible to get the most common value (String) of a column that is the result of a filter? VBA
问题描述
我有点卡在这里.我在类似这样的工作表中应用了一个过滤器,在应用了过滤器后,它看起来像这样(示例项目999)
I'm a bit stuck here. I'm applying a filter in a sheet that's something like this, after applying the filter it looks like this (Example Item 999)
物品发票数量Avi.数量标志框
Item Inv. Qty Avi. Qty Flag BOX
999 12 9 N X1
999 23 17 Y X2
999 1 1 N X14
999 21 3 N X113
我试图获取标志"列的值,(在过滤器内部,它崩溃了)代码:
I was trying to get the "Flag" Column value, (inside the filter and it crashed) Code:
With InventorySheet
.AutoFilterMode = False
LRowOnQ = .Cells(Rows.Count, "Q").End(xlUp).Row
.Range("B1").AutoFilter Field:=2, Criteria1:=Project
.Range("D1").AutoFilter Field:=4, Criteria1:=ContractNumber
.Range("N1").AutoFilter Field:=14, Criteria1:=Code
.Range("Q1").AutoFilter Field:=17, Criteria1:=">0"
Set rangeFilteredInventory = .Range("Q1:Q" & LRowOnQ)
ControlFlag = .Range("L2").Value
End With
在过滤器之后,它始终取相同的值.代码:
After the filter, it always take the same value. Code:
With InventorySheet
.AutoFilterMode = False
LRowOnQ = .Cells(Rows.Count, "Q").End(xlUp).Row
.Range("B1").AutoFilter Field:=2, Criteria1:=Project
.Range("D1").AutoFilter Field:=4, Criteria1:=ContractNumber
.Range("N1").AutoFilter Field:=14, Criteria1:=Code
.Range("Q1").AutoFilter Field:=17, Criteria1:=">0"
Set rangeFilteredInventory = .Range("Q1:Q" & LRowOnQ)
End With
ControlFlag = InventorySheet.Range("L2").Value
有什么方法可以获取结果范围中最常见的字母?我知道小计中有一个平均选项,但我认为它仅适用于数字.
Is there any way to take the most common letter of the result range? I'm aware there's a average option in subtotal, but I think it's just for numbers.
编辑:试过了代码:
With InventorySheet
.AutoFilterMode = False
LRowOnQ = .Cells(Rows.Count, "Q").End(xlUp).Row
.Range("B1").AutoFilter Field:=2, Criteria1:=Project
.Range("D1").AutoFilter Field:=4, Criteria1:=ContractNumber
.Range("N1").AutoFilter Field:=14, Criteria1:=Code
.Range("Q1").AutoFilter Field:=17, Criteria1:=">0"
Set rangeFilteredInventory = .Range("Q1:Q" & LRowOnQ)
Set ControlRange = .Range("L1:L" & LRowOnQ)
End With
ControlFlag = WorksheetFunction.Subtotal(1, ControlRange.SpecialCells(xlCellTypeVisible))
它甚至都没有循环.
推荐答案
这可以使用 MODE
函数来实现.因此不需要VBA.
This can be achieved using the MODE
function. So no need for VBA.
数字示例
=MODE(IF(SUBTOTAL(3,OFFSET(Range,ROW(Range)-MIN(ROW(Range)),0,1)),Range*{1,1}))
文字示例
=INDEX(A3:A21,MODE(IF(SUBTOTAL(3,OFFSET(A3,ROW(A3:A21)-ROW(A3),0)),MATCH(A3:A21,A3:A21,0)*{1,1})))
ctrl + shift + enter(因为它是一个数组公式)
ctrl+shift+enter (as it's an array formula)
您可以阅读有关此 HERE
这是使用纯VBA的另一种解决方案:
Here is another solution using purely VBA:
Public Function ModeSubTotal(rng As Range) As String
Dim Dn As Range
Dim oMax As Double
Dim K As Variant
Dim val As String
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
For Each Dn In rng
If Dn.Rows.Hidden = False Then
If Not .Exists(Dn.Value) Then
.Add Dn.Value, 1
Else
.Item(Dn.Value) = .Item(Dn.Value) + 1
End If
End If
Next
oMax = Application.Max(Application.Transpose(.Items))
For Each K In .keys
If .Item(K) = oMax Then
val = val & K & ","
End If
Next K
ModeSubTotal = Left(val, Len(val) - 1)
End With
End Function
由于它是UDF,因此您可以像正常功能一样在工作表中使用:
As it's a UDF you can use in the worksheet like a normal function:
或者您可以轻松地在代码中调用它:
Or you could easily call it in code:
Public Sub test()
Sheet1.Cells(1, 1).Value = ModeSubTotal(Range("C1:C20"))
End Sub
该函数将空白值作为当前值进行计数,因此,如果空白值是将要返回的最频繁出现的值,则可以很容易地对其进行更改.
The function counts blanks as values at the moment, so if blank is the most occuring value that will be returned, this could be changed quite easily.
这篇关于是否有可能获得作为过滤器结果的列的最通用值(字符串)?VBA的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!