Como contar o número de células em várias planilhas cujos valores estão entre um determinado intervalo?

Question

Eu tenho uma pasta de trabalho do Excel com várias planilhas contendo dados como:

    'DATA_1'                        'DATA_2'                        'DATA_3'        
    A       B                       A       B                       A       B       
1   value1     1.6              1   value1     0.8              1   value1     2.0  
2   value2     2.5              2   value2     2.2              2   value2     0.5  
3   value3     3.4              3   value3     3.1              3   value3     3.2  

Existe outra planilha chamada SUMMARY , na frente de todas as outras, que se parece com isso:

    'SUMMARY'                       
    A       B       C       D       
5           min     max     count   
6   value1     1.0     1.8          
7   value2     1.5     2.5          
8   value3     2.0     3.0          

As linhas 1 to 4 que não são mostradas estão vazias e ocultas.

Como você já deve ter adivinhado, agora estou tentando preencher a coluna count ( D ) corretamente. Ele deve conter a contagem de todos os valores value? em todas as folhas DATA_? que estão no respectivo intervalo de min a max limites (um valor igual a um dos limites é considerado no intervalo). Então, o que estou esperando para os dados de amostra fornecidos é:

    D       
5   count   
6        1  
7        2  
8        0  

O que eu tenho até agora é uma função definida pelo usuário para recuperar uma matriz de nomes de planilha, o que funciona bem:

Function SHEET_NAMES() As Variant
    ' returns names of all sheets as an array
    Dim index As Long, retArray() As String
    Application.Volatile True

    ReDim retArray(ThisWorkbook.Sheets.Count)
    For index = 1& To ThisWorkbook.Sheets.Count
        retArray(index) = ThisWorkbook.Sheets.Item(index).Name
    Next index
    SHEET_NAMES = retArray
End Function

Além disso, posso acessar com êxito o value1 data (representativamente) em todas as planilhas com {=N(INDIRECT(ADDRESS(ROW(B1);COLUMN(B1);;;SHEET_NAMES())))} .

Eu posso verificar os valores de value1 em relação aos seus limites de min com {=N(N(INDIRECT(ADDRESS(ROW(B1);COLUMN(B1);;;SHEET_NAMES())))>=B6)} . Quando essa função de matriz é dividida em 4 células, resulta em:

 0       1       0       1  

Eu posso verificar os valores de value1 em relação aos seus limites de max com {=N(N(INDIRECT(ADDRESS(ROW(B1);COLUMN(B1);;;SHEET_NAMES())))<=C6)} . Quando essa função de matriz é dividida em 4 células, resulta em:

 1       1       1       0  

Finalmente, também posso testar as células para valores numéricos com {=N(ISNUMBER(INDIRECT(ADDRESS(ROW(B1);COLUMN(B1);;;SHEET_NAMES()))))} . Quando essa função de matriz é dividida em 4 células, resulta em:

 0       1       1       1  

O 0 vem da planilha SUMMARY cuja célula B1 está vazia como já foi mencionado.

Minha idéia agora é usar a função SUMPRODUCT finally e assim obter as contagens individuais: {=SUMPRODUCT(N(N(INDIRECT(ADDRESS(ROW(B1);COLUMN(B1);;;SHEET_NAMES())))>=B6);N(N(INDIRECT(ADDRESS(ROW(B1);COLUMN(B1);;;SHEET_NAMES())))<=C6);N(ISNUMBER(INDIRECT(ADDRESS(ROW(B1);COLUMN(B1);;;SHEET_NAMES())))))} .
No entanto, embora eu espere 1 = (0 * 1 * 0) + (1 * 1 * 1) + (0 * 1 * 1) + (1 * 0 * 1) , isso sempre resulta em 0 estranhamente. Então você poderia por favor me dizer o que estou fazendo de errado aqui?

(Eu preferiria uma solução sem usar COUNTIF , COUNTIFS , SUMIF , SUMIFS e outras funções semelhantes que precisam da condição a ser fornecida como uma string, se possível.)

    

Answer 1

Altere seu UDF para ficar assim:

Function SHEET_NAMES() As Variant
    ' returns names of all sheets as an array
    Dim index As Long, retArray() As String, i As Long
    index = 1
    i = 1
    Application.Volatile True

    ReDim retArray(1 To ThisWorkbook.Sheets.Count - 1)
    Do While index <= ThisWorkbook.Sheets.Count
        If ThisWorkbook.Sheets(index).Name <> "SUMMARY" Then
            retArray(i) = ThisWorkbook.Worksheets(index).Name
            i = i + 1
        End If
        index = index + 1
    Loop
    SHEET_NAMES = retArray
End Function

A única mudança realmente significativa é que eu ignorei a planilha SUMMARY.

E, em seguida, use essa fórmula de matriz, adaptada de aqui (confirme com ctrl + shift + enter):

=SUM(IF(N(INDIRECT(SHEET_NAMES()&"!B"&ROW()-5))>=B6,IF(N(INDIRECT(SHEET_NAMES()&"!B"&ROW()-5))<=C6,1,0),0))

Observe que, para que isso funcione, seus dados na planilha SUMMARY devem começar na linha 6 (como sua amostra). Se isso não acontecer, você terá que ajustar o ROW()-5 bit para que seja igual a 1 na primeira linha. Ele tem que funcionar dessa forma porque é isso que incrementa a linha quando você faz o autofile da fórmula.

Editar: endereçando seus comentários, use esta fórmula. Pode ser usado com a sua UDF original e utiliza ADDRESS() para criar as referências.

=SUM(IFERROR(IF(N(INDIRECT(ADDRESS(ROW(B1),COLUMN(B1),,,SHEET_NAMES())))>=B6,IF(N(INDIRECT(ADDRESS(ROW(B1),COLUMN(B1),,,SHEET_NAMES())))<=C6,1,0),0),0))