Funções da matriz do Excel - como contar células não-brancas em várias planilhas?

Question

Eu tenho uma pasta de trabalho do Excel com várias planilhas, algumas delas contendo um valor numérico na célula A1 :

  'DATA_1'        'DATA_2'        'DATA_3'  
    A               A               A       
1    1.6        1   -0.8        1           

Desejo determinar o número de A1 células em uma única fórmula em toda a pasta de trabalho que contém dados, portanto, o resultado do exemplo acima deve ser 2 porque a célula na última planilha está em branco.

Como o número de planilhas pode variar, estou usando uma função definida pelo usuário (UDF) que retorna uma matriz contendo os nomes de todas as planilhas:

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

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

Esta UDF está funcionando bem, a fórmula {=SHEET_NAMES()} retorna a matriz esperada ( {"DATA_1","DATA_2","DATA_3"} ). (Observe que a UDF precisa ser considerada não editável).

Minha idéia agora é usar o UDF junto com as funções ADDRESS() e INDIRECT() para fazer referência às células A1 de todas as planilhas disponíveis dinamicamente, para usar ISNUMBER() para verificar se um número válido está presente, convertendo o booleano FALSE / TRUE a 0 / 1 por N() e, finalmente, usar SUM() para somar todos os 0 / 1 , assim:

{=SUM(N(ISNUMBER(INDIRECT(ADDRESS(ROW(A1);COLUMN(A1);;;SHEET_NAMES())))))}

No entanto, o resultado é sempre 0 , mesmo que nenhuma planilha contenha uma célula vazia A1 .

Descobri que a função ADDRESS() retorna a matriz de cadeias correta, representando as referências a todas as células A1 , que é {"DATA_1!$A$1","DATA_2!$A$1","DATA_3!$A$1"} .
INDIRECT() retorna {#VALUE!,#VALUE!,#VALUE!} , pois não suporte matrizes aparentemente, no entanto, a função de contêiner ISNUMBER() suporta matrizes, portanto, isso parece fazer a iteração sobre os elementos da matriz corretamente e resulta em {TRUE,TRUE,FALSE} .
A função N() faz a conversão da maneira pretendida, portanto resulta em {1,1,0} .
Mas o SUM() final sempre resulta em 0 , não importa quantas folhas existem e se algumas ou todas elas contêm números válidos na célula A1 . (Apenas como um sidenote: se eu inserir a fórmula como uma função não matriz, o resultado dependerá apenas da primeira planilha.)

Eu tentei usar NOT(ISBLANK()) e NOT(ISERROR()) em vez de ISNUMBER() , e tentei alterar SUM(N(ISNUMBER())) para SUM(COUNT()) , mas sem sucesso (todos resultam em 0 , exceto NOT(ISBLANK()) que fornece 3 ).
(Também tentei substituir ROW(A1) e COLUMN(A1) por ROWS($A$1:A1) e COLUMNS($A$1:A1) , respectivamente, pois isso deve superar um problema relacionado à matriz de INDIRECT() , de acordo com algumas páginas da Web, mas sem alterações bem.)

Então, você poderia me dizer o que estou fazendo de errado aqui e como superar o fracasso sem perder a flexibilidade?

(Se possível, eu preferiria uma solução sem usar COUNTIF() ou SUMIF() nem qualquer outra função que requeira que uma determinada condição seja fornecida como string, para manter a portabilidade.)

    

Answer 1

Você não precisa de todos os nomes das planilhas, apenas o primeiro e o último:

=COUNT(Sheet1:Sheet3!A1)

    

Answer 2

Você pode tentar com este código:

=SUMPRODUCT(SUBTOTAL(2;INDIRECT(ADDRESS(ROW();COLUMN();;;SHEET_NAMES))))