在Excel中找到重复次数最多的值，但仅在某些颜色的单元格中 [英] Find the most repeated value but only in cells of certain color in excel

问题描述

我在这里知道这个公式

  = MODE(！B:B) 

为您提供了B列中重复次数最多的值，但我只想计数彩色的单元格.那有可能吗?

谢谢.

这是我的模块的外观:

 函数GetInfo(作为变量的TopObj，作为变量的PropertySpec)作为变量对象树拆分返回的Dim PropArr As Variant'数组集合中的Dim ItemSpec作为变体'Dim Obj As Object'要持有的通用对象'顶级对象(ws，wb，范围或应用)Dim Ndx As Long'循环计数器Dim Pos1 As Integer'用于查找集合对象中指定的项目Dim Pos2 As Integer'用于查找集合对象中指定的项目昏暗的TempObj作为对象''拆分对象/属性规范'PropArr = Split(PropertySpec，.")''如果Rng是一个对象，则它必须是一个Range.那是唯一的'您从单元格传递的对象的类型.'如果IsObject(TopObj)然后设置对象= TopObj别的''否则，最好是以下字符串之一.别的，炸毁用户.'选择案例UCase(TopObj)情况"APP"，"APPLICATION"设置对象=应用情况"WB"，"TWB"，"THISWORKBOOK"，"WORKBOOK"设置对象= ThisWorkbook案例"WS"，"TWS"，"THISWORKSHEET"，"WORKSHEET"设置对象= Application.Caller.Parent其他情况GetInfo = CVErr(xlErrValue)结束选择万一对于Ndx = LBound(PropArr)到UBound(PropArr)-1''此代码块用于处理集合中的项目'Pos1 = InStr(1，PropArr(Ndx)，(")如果Pos1>0然后''如果我们找到了开放的家长，我们正在处理"的集合.现在，找到结束括号.'Pos2 = InStr(1，PropArr(Ndx)，)")ItemSpec = Mid(PropArr(Ndx)，Pos1 + 1，Pos2-Pos1-1)如果是IsNumeric(ItemSpec)，则'数字-按索引号ItemSpec = CLng(ItemSpec)别的'字符串-按键名进行操作，因此请删除所有引号.ItemSpec = Replace(ItemSpec，""，")万一''调用集合对象的Item方法.'设置Obj = CallByName(Obj，Mid(PropArr(Ndx)，1，Pos1-1)，_VbGet，ItemSpec)别的''我们不处理藏品.刚拿到物体.'设置Obj = CallByName(Obj，PropArr(Ndx)，VbGet)万一下一个Ndx''获得最终属性(通常是对象树的名称"或值")'如果IsObject(Obj)然后GetInfo = CallByName(Obj，PropArr(UBound(PropArr))，VbGet)万一结束功能公共函数getArrayInfo(rng作为范围，atr作为字符串)作为变量昏暗的温度作为Excel.Range变暗out()作为变体昏暗的我只要我= 1ReDim out(1到行数，1到1)设置温度=相交(rng，ActiveSheet.UsedRange)对于临时单元格中的每个项目out(i，1)= GetInfo(Item，atr)我=我+ 1下一个项目getArrayInfo = out结束功能 

导入

替代解决方案:

此解决方案假定您能够略微修改数据，特别是添加一个辅助列并将范围转换为表，但更简单，运行速度更快且不需要VBA.

1..转到公式>定义的名称>名称管理器

2..点击 New ，将其命名为您想要的任何名称，我选择了"bg"并在引用:"中类型:

= GET.CELL(63，INDIRECT("rc [-1]"，FALSE))

然后单击确定，然后关闭名称管理器.

3..选择数据表，然后转到 Insert>表格>表格，您将看到一个对话框，以确认您要选择的范围，检查表格中是否包含标题，然后单击确定，您的数据现在应具有表格格式.很容易识别，因为现在您应该在表格标题旁边有一个过滤器箭头.

4..在数据列的右侧添加一个新标题，然后在标题中键入 color .在此新列的第一个数据记录中，键入公式 = bg (或在步骤1中选择命名自定义命名范围的任何名称).单击Enter，它将在整个列上自动填充相同的公式:

5.现在，您终于有了一个帮助程序列，它为每个对应的记录读取colorIndex，因此您可以读取要分析的colorIndex，公式很简单:

{= MODE.SNGL(IF(Table1 [COLOR] = ColorIndex，Table1 [INPUT]))} 其中 colorIndex 是您想要的颜色编号分析，例如在我的表中，黄色是6，红色是3.请记住使用 Ctrl + Shift + Enter 将其作为数组公式输入

在这里您可以看到我已经为我的数据表中的所有颜色计算了相应的模式，另外一个好处是，由于数据被格式化为表格，因此我们的公式将在我们添加新记录时自动更新

Hi,

I know this formula here

=MODE(!B:B)

gives you the most repeated values in column B but I want to count only colored cells. Is that even possible?

Thank you.

EDIT:

this is how my module looks:

Function GetInfo(TopObj As Variant, PropertySpec As Variant) As Variant

Dim PropArr As Variant ' array returned by Split of object tree
Dim ItemSpec As Variant ' item in collection
Dim Obj As Object ' generic Object to hold
                  'the top-level object (ws,wb,range, or app)
Dim Ndx As Long ' loop counter
Dim Pos1 As Integer ' used to find the Item specified in collection objects
Dim Pos2 As Integer ' used to find the Item specified in collection objects
Dim TempObj As Object

'
' split the object/property spec
'
PropArr = Split(PropertySpec, ".")
'
' If Rng is an object, then it must be a Range. That's the only
' type of object you pass from a cell.
'
If IsObject(TopObj) Then
    Set Obj = TopObj
Else
    '
    ' Otherwise, it better be one of the following strings. Else,
    ' blow up the user.
    '
    Select Case UCase(TopObj)
        Case "APP", "APPLICATION"
            Set Obj = Application
        Case "WB", "TWB", "THISWORKBOOK", "WORKBOOK"
            Set Obj = ThisWorkbook
        Case "WS", "TWS", "THISWORKSHEET", "WORKSHEET"
            Set Obj = Application.Caller.Parent
        Case Else
            GetInfo = CVErr(xlErrValue)
    End Select
End If

For Ndx = LBound(PropArr) To UBound(PropArr) - 1
    '
    ' this block of code is for handling items of a collection
    '
    Pos1 = InStr(1, PropArr(Ndx), "(")
    If Pos1 > 0 Then
        '
        ' if we've found the open paren, we're dealing with an
        ' item of a collection. now, find the closing paren.
        '
        Pos2 = InStr(1, PropArr(Ndx), ")")
        ItemSpec = Mid(PropArr(Ndx), Pos1 + 1, Pos2 - Pos1 - 1)
        If IsNumeric(ItemSpec) Then
            ' numeric -- going by index number
            ItemSpec = CLng(ItemSpec)
        Else
            ' string -- going by key name, so get rid of any quotes.
            ItemSpec = Replace(ItemSpec, """", "")
        End If
        '
        ' call the Item method of the collection object.
        '
        Set Obj = CallByName(Obj, Mid(PropArr(Ndx), 1, Pos1 - 1), _
            VbGet, ItemSpec)
    Else
        '
        ' we're not dealing with collections. just get the object.
        '
        Set Obj = CallByName(Obj, PropArr(Ndx), VbGet)
    End If
Next Ndx
'
' get the final property (typically 'name' or 'value' of the object tree)
'
If IsObject(Obj) Then
    GetInfo = CallByName(Obj, PropArr(UBound(PropArr)), VbGet)
End If

End Function

Public Function getArrayInfo(rng As Range, atr As String) As Variant
Dim temp As Excel.Range
Dim out() As Variant
Dim i As Long
i = 1

ReDim out(1 To rng.Rows.Count, 1 To 1)
Set temp = Intersect(rng, ActiveSheet.UsedRange)

For Each Item In temp.Cells
    out(i, 1) = GetInfo(Item, atr)
    i = i + 1
Next Item

getArrayInfo = out

End Function

解决方案

after you have imported the getInfo function to your module, you now need to add an aditional function to the module to work with Array Formulas. Add this one after the getInfo function:

Public Function getArrayInfo(rng As Range, atr As String) As Variant
Dim temp As Excel.Range
Dim out() As Variant
Dim i As Long
i = 1

ReDim out(1 To rng.Rows.Count, 1 To 1)
Set temp = Intersect(rng, ActiveSheet.UsedRange)

For Each item In temp.Cells
    out(i, 1) = GetInfo(item, atr)
    i = i + 1
Next item

getArrayInfo = out

End Function

Then, in your worksheet you get the mode with:

=MODE(IF(getArrayInfo(data,"Interior.Color")=24,data))

where data is your data column. Remember to enter it as an array formula with Ctrl+Shift+Enter

Here I tested it with this data set:

ALTERNATE SOLUTION:

This solution assumes you are able to slightly modify your data, specifically adding a helper column and transforming the range to a table, but is much simpler, runs faster and doesn't require VBA.

1. Go to Formulas > Defined Names > Name Manager

2. Click on New, name it anything you want, I chose "bg" and in "Refers to:" type:

=GET.CELL(63,INDIRECT("rc[-1]",FALSE))

Then click ok and close the Name Manager.

3. Select your data table and go to Insert > Tables > Table, you'll see a dialog box to confirm the range you want to select, check if your table has headers and click ok, your data should now have table formatting. It's easy to recognize because now you should have a filter arrow next to your table header.

4. add a new header to the right of your data column then type color in the header. In the first data record on this new column, type the formula =bg (or whatever you chose to name your custom named range on step 1). Click enter and it should autofill with the same formula on the entire column:

5. Now finally, you have a helper column that reads the colorIndex for each corresponding record, so you can read which colorIndex you want to analize and your formula is simply:

{=MODE.SNGL(IF(Table1[COLOR]=ColorIndex,Table1[INPUT]))} where colorIndex is the number of the color you want to analyze, for example in my table yellow is 6 and red is 3. Remember to enter it as an array formula with Ctrl+Shift+Enter

Here you can see I have calculated the corresponding mode for all the colors in my data table, with the added benefit that since the data is formated as a table, our formulas will automatically update when we add a new record

这篇关于在Excel中找到重复次数最多的值，但仅在某些颜色的单元格中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！