Funzioni di arrays di Excel – come contare le celle non vuote su fogli di lavoro multipli?

Ho una cartella di lavoro di Excel con numerosi fogli di lavoro, alcuni dei quali contengono un valore numbersco nella cella A1 :

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

Voglio determinare il numero di celle A1 mediante una singola formula in tutta la cartella di lavoro che contengono dati, quindi il risultato per l'esempio precedente dovrebbe essere 2 perché la cella nell'ultimo foglio di lavoro è vuota.

Poiché il numero di fogli di lavoro può variare, sto usando una function definita dall'utente (UDF) che restituisce un arrays contenente i nomi di tutti i fogli:

 Function SHEET_NAMES() As Variant ' returns names of all sheets as an arrays 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 

Questo UDF funziona bene, la formula {=SHEET_NAMES()} restituisce l'arrays atteso ( {"DATA_1","DATA_2","DATA_3"} ). (Si noti che l'UDF deve essere considerato non modificabile.)

La mia idea è ora di utilizzare l'UDF insieme alle funzioni ADDRESS() e INDIRECT() alle celle di riferimento A1 di tutti i fogli di lavoro disponibili in modo dinamico, per utilizzare ISNUMBER() per verificare se è presente un numero valido, convertendo il FALSE / TRUE boolean 0 per N() e, infine, utilizzare SUM() per riassumere tutti gli 0 , come segue:

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

    Tuttavia, il risultato è sempre 0 , anche se nessun foglio di lavoro contiene una cella vuota A1 .

    Ho scoperto che la function ADDRESS() restituisce la matrix corretta di stringhe, che rappresenta i riferimenti a tutte le celle A1 , che è {"DATA_1!$A$1","DATA_2!$A$1","DATA_3!$A$1"} .
    INDIRECT() restituisce {#VALUE!,#VALUE!,#VALUE!} Poiché non support apparentemente gli arrays, tuttavia la function container ISNUMBER() support gli arrays, in modo che questo sembra rendere correttamente l'iterazione sugli elementi dell'arrays e risulta {TRUE,TRUE,FALSE} .
    La function N() effettua la conversione nel modo desiderato, per cui risulta in {1,1,0} .
    Ma il finale SUM() risulta sempre in 0 , non import quanti fogli ci sono e se alcuni o tutti contengono numbers validi nella cella A1 . (Proprio come un sidenote: se inserisco la formula come una function non arrays, il risultato dipende solo dal primo foglio di lavoro.)

    Ho cercato di utilizzare NOT(ISBLANK()) e NOT(ISERROR()) invece di ISNUMBER() e ho cercato di cambiare SUM(N(ISNUMBER())) a SUM(COUNT()) ma senza successo (tutti risulta in 0 , tranne NOT(ISBLANK()) che fornisce 3 ).
    (Anche io ho provato a sostituire ROW(A1) e COLUMN(A1) da ROWS($A$1:A1) e COLUMNS($A$1:A1) , poiché dovremmo superare un problema di INDIRECT() , secondo alcune pagine web, ma senza cambiamenti.)

    Potresti per favore dirmi cosa sto facendo qui, e come superare il fallimento senza perdere la flessibilità?

    (Se ansible, preferirei una soluzione senza utilizzare COUNTIF() o SUMIF() né altre funzioni che richiedono che una determinata condizione venga data come string per mantenere la portbilità.)

    Non è necessario tutti i nomi dei fogli, solo i primi e gli ultimi:

     =COUNT(Sheet1:Sheet3!A1) 

    Puoi provare con questo codice:

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