Excel: fórmula de array dentro de outra fórmula

Question

A coluna

B copia valores da coluna A e, se não for o valor mínimo no intervalo determinado, adiciona 1, senão fica com: =IF(NOT($A2=MIN($A$2:$A$7)),$A2+1,$A2)

C2 localiza a célula com o valor mínimo diferente de zero do intervalo A2:A7 com =MIN(IF(A2:A7>0,A2:A7)) (com CTRL SHIFT ENTER ).

É possível usar essa fórmula de matriz como uma subexpressão em outra fórmula? Digamos que eu queira ADICIONAR 1 se o valor em A2:A7 NÃO for o valor mínimo de zero?

Portanto, no exemplo acima, nada será adicionado a A6 . E D2:D7 valores serão:

6    
5    
4    
3    
1    
1

    

Answer 1

Não tenho certeza se é possível usar essa fórmula de matriz específica como uma subexpressão em outra fórmula.

No entanto, há uma solução alternativa. Você só precisa de uma fórmula non -array que calcule o valor mínimo diferente de zero.

Essa fórmula pode ser criada com a função SMALL() :

=SMALL($A$2:$A$7,COUNTIF($A$2:$A$7,0)+1)

Os leva à solução de trabalho

emqueafórmulaemD2éapenasafórmulaemB2comafunçãoMIN()substituídapelafunçãoSMALL():

=IF(NOT($A2=SMALL($A$2:$A$7,COUNTIF($A$2:$A$7,0)+1)),$A2+1,$A2)

Note que esta fórmula é não matriz digitada!

Eu prefiro escrever esses tipos de fórmulas refatoradas para que eles não usem NOT() e a referência esteja fora de IF() :

=$A2+IF($A2=SMALL($A$2:$A$7,COUNTIF($A$2:$A$7,0)+1),0,1)

Minha fatoração favorita, no entanto, é esta:

=$A2+($A2<>SMALL($A$2:$A$7,COUNTIF($A$2:$A$7,0)+1))

Infelizmente, muitas pessoas acham que hacky e difícil de ler, então eu não tendem a usá-lo.

    

Answer 2

Resposta postada por @RobinCTS me inspirou a fazer as correções úteis em ambas as suas fórmulas excitantes.

Sua primeira fórmula deve ser escrita como mostrado abaixo na célula C2 para encontrar mínimo diferente de zero.

{=MIN(IF(A2:A7>0,A2:A7,A2+1))}

Na célula D2, escreva essa fórmula de matriz para adicionar 1, se não o mínimo diferente de zero & preencha-o.

{=IF(A2<>MIN(IF(A2:A7>0,A2:A7,A2+1)),A2+1,A2)}

N.B. As duas fórmulas acima descritas são uma fórmula de matriz, portanto, termine com Ctrl + Shift + Enter.