索引列标题，其中出现列中的最大值 [英] Index column header where max count of values in columns occurs

问题描述

我有一张桌子，桌子上有九列二十行.我正在尝试标识哪个列标题具有其下的字段的最大数量，其中值大于或等于指定的数字.表格范围是J4:R23.指定的号码是B6. J4:R4包含我要匹配的标头.对公式获取适当的列标题有任何想法吗?谢谢！

I have a table with nine columns and twenty rows. I am trying to id which column header has the max count of fields below it where the values are greater than or equal to a specified number. The table range is J4:R23. The specified number is B6. J4:R4 contains the header that i am trying to match. Any thoughts on formula to obtain the appropriate column header? Thanks!

推荐答案

我也做了一个VBA函数，它是多余的，依此类推，但这是:

I made a VBA function too, it's redundant and so on but here it is:

Public Function FINDHEADERWHEREMAXCOUNTIFS(Target As Range, Condition As Double)
Dim rng As Range
NumCols = Target.Columns.Count 'counts how many header values we can choose of
Dim Headers() 'defines separate arrays for headers and values (turned out to be obsolete, see variable x)
    ReDim Headers(1 To NumCols)
Dim ValuesArr()
    ReDim ValuesArr(1 To NumCols)

HeaderRow = Target.Row 'row in which headers are located
LastRow = HeaderRow + Target.Rows.Count - 1 'last row with values
FirstColumn = Target.Column 'first column with values
LastColumn = FirstColumn + Target.Columns.Count - 1 'last column with values

For k = FirstColumn To LastColumn 'for each column
    i = i + 1 'set array position
    Headers(i) = Cells(HeaderRow, k).Value2 'set the corresponding header
    For Each rng In Range(Cells(HeaderRow, k), Cells(LastRow, k)) 'for each value
        If rng.Row <> HeaderRow Then 'I mean value, not header
            If rng.Value2 > Condition Then ValuesArr(i) = ValuesArr(i) + 1 'if it's higher than the condition then count
        End If
    Next
Next

x = 1 'default is the first header
For j = 1 To NumCols 'for each header
 If ValuesArr(j) > ValuesArr(x) Then x = j 'if the corresponding value is larger then standard then change the current one to standard
Next
FINDHEADERWHEREMAXCOUNTIFS = Headers(x) 'the standard value's corresponding header is the output
End Function

这篇关于索引列标题，其中出现列中的最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！