Contando as células em um intervalo nomeado sem VBA

Question

Eu tenho um conjunto disjunto de 9 células: A1, B3, C5, D7, E11, F13, G17, H19, I23 . Eu atribuí um Nome a estas células: MyPicks

Eu tenho uma fórmula simples para escolher o valor em uma dessas células aleatoriamente:

=SMALL(MyPicks,RANDBETWEEN(1,9))

a fórmula funciona:

Noentanto,semprequealteraronúmerodecélulasnoIntervaloNomeado,tenhodevoltarealteraro9nafórmula!Entãodecidi"consertar" a fórmula:

Primeira tentativa:

=SMALL(MyPicks,RANDBETWEEN(1,COUNTA(MyPicks)))

Isso pareceu funcionar. No entanto, COUNTA () conta apenas células com valores ou valores nulos. Ignora células totalmente vazias. Então ...........

Segunda tentativa:

=SMALL(MyPicks,RANDBETWEEN(1,COUNTA(MyPicks)+COUNTBLANK(MyPicks)))

Infelizmente, isso produz um valor de #VALUE! erro porque COUNTBLANK () não funciona em intervalos disjuntos. Então ............

Terceira tentativa:

Eu criei um pequeno UDF VBA:

Public Function nCount(r As Range) As Long
    nCount = r.Count
End Function

O uso desta UDF resolveu o problema. Descobri então que meu cliente era macrofóbico e a solução foi imediatamente rejeitada.

É possível contar o número total de células em um Intervalo Nomeado sem o VBA ??

    

Answer 1

Que tal esta pequena beleza:

=SUM(FREQUENCY(MyPicks,MyPicks))

Portanto, toda a sua fórmula seria:

=SMALL(MyPicks,RANDBETWEEN(1,SUM(FREQUENCY(MyPicks,MyPicks))))

EDITAR: Como apontado por barry houdini, a função SMALL retornará apenas valores numéricos. Nesse caso, a solução SUM(FREQUENCY()) é exagerada. Em vez disso, simplesmente use

=SMALL(MyPicks,RANDBETWEEN(1,COUNT(MyPicks)))

    

Answer 2

Se cada um dos intervalos separados for composto por uma única célula, a fórmula a seguir retornará o número de células no intervalo, independentemente do conteúdo, ou mesmo se estiverem em branco, o que eu acho que é o que você deseja:

=LEN(CELL("address",MyPicks))-LEN(SUBSTITUTE(CELL("address",MyPicks),",",""))+1