本文介绍了过滤范围内的条件计数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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



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



我可以轻松地执行一个 = COUNTIF(L:L,来查看少于5000个行,或 = COUNTFS(L:L,> 500,L:L,查看在两个数字之间的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&,下一个

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

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

08-11 12:26