过滤范围内的条件计数 [英] Conditional count in filtered range

问题描述

我有一张大约一百万行的表格。在一个特定的列中，我的数字范围从 0 到 50,000 。

我试图在过滤范围内确定过滤范围中有多少个单元格在一定值内。

我可以轻松地执行一个 = COUNTIF（L：L，<5000）来查看少于5000个行，或 = COUNTFS（L：L，> 500，L：L，<5000）查看在两个数字之间的TOTAL范围内的数字，但是我无法弄清楚如何做

通常使用过滤后的数据，我使用 = SUBTOTAL 函数，但是我看不出在这个例子中，任何已建立的 = SUBTOTAL 函数是如何工作的。

任何想法？

解决方案

这是一个VBA解决方案。我已经评论过代码，所以你不应该有任何问题的理解，但如果你这样做简单的发布。

 子样本（）
 Dim ws As Worksheet 
 Dim lRow As Long，n As Long 
 Dim rng As Range，rngArea As Range 
 
'~~>更改为适用的
设置ws = ThisWorkbook.Sheets（Sheet1）
 
与ws 
'~~>在Col L 
 lRow = .Range（L& .Rows.Count）.End（xlUp）.Row  -  1 
 
'Debug.Print Intersect（_ 
 .Range（L2：L& lRow），_ 
 .Range（L1）。Offset（1，0）.SpecialCells（xlCellTypeVisible）_ 
）.Address 
 
'~~>这是所有可见单元格的范围，直到列L 
'~~>中的最后一行为止。除了标题：
设置rng =相交（_ 
 .Range（L2：L& lRow），_ 
 .Range（L1）。偏移量（1，0） .SpecialCells（xlCellTypeVisible）_ 
）
 
'~~>由于该区域可能不连续，我们使用每个区域的Countif并加起来
对于每个rngArea在rng 
n = n + Application.Evaluate（= COUNTIFS（& rngArea.Address& _ 
，> 500，& rngArea.Address&，<5000））
下一个
 
 Debug.Print n 
结束
结束子
  

I have a sheet with about a million rows. Within one particular column, I have numbers ranging from 0 to 50,000.

I am trying to determine, within a filtered range, how many cells in the filtered range fall within a certain value.

I can easily do a =COUNTIF(L:L, "<5000") to see how many rows are less than 5,000, or =COUNTIFS(L:L,">500",L:L,"<5000") to see numbers in the TOTAL range that fall between two numbers, but I cannot figure out how to do either of these in a filtered range.

Normally with filtered data I use the =SUBTOTAL function, but I cannot see how any of the established =SUBTOTAL functions would work in this example.

Any ideas?

解决方案

Here is a VBA solution. I have commented the code so you shouldn't have any problem understanding it but if you do then simply post back.

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long, n As Long
    Dim rng As Range, rngArea As Range

    '~~> Change this as applicable
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        '~~> Finding last row in Col L
        lRow = .Range("L" & .Rows.Count).End(xlUp).Row - 1

        'Debug.Print Intersect( _
                              .Range("L2:L" & lRow), _
                              .Range("L1").Offset(1, 0).SpecialCells(xlCellTypeVisible) _
                              ).Address

        '~~> This is your range of all visible cells till the last row in column L
        '~~> except the header:
        Set rng = Intersect( _
                              .Range("L2:L" & lRow), _
                              .Range("L1").Offset(1, 0).SpecialCells(xlCellTypeVisible) _
                              )

        '~~> Since the area could be non contiguous we use Countif per area and add up
        For Each rngArea In rng
            n = n + Application.Evaluate("=COUNTIFS(" & rngArea.Address & _
                                         ","">500""," & rngArea.Address & ",""<5000"")")
        Next

        Debug.Print n
    End With
End Sub

这篇关于过滤范围内的条件计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！