A comparação de um intervalo com um valor não resulta em um array quando usado dentro de SUM ()

Question

Há algo sobre a função SUM() no Excel que não entendo.

Eu posso somar um intervalo, como este: =SUM(A2:A4) .

Eu posso somar uma lista de intervalos: =SUM(A2, A3:A4) .

Eu posso somar uma matriz: =SUM({1, 2, 3}) (retorna 6 ).

Eu posso somar um array de booleanos não numéricos coagindo os elementos em números da seguinte forma:

=SUM(-{TRUE,TRUE,TRUE}) (retorna -3 ).

MAS , o seguinte não funciona e não sei porquê:

=SUM(--(A2:A7>0.5))

Por não funciona , o resultado é uma mensagem de erro, 0 ou 1 . Eu esperava que o resultado funcionasse como uma função COUNTIF() , em que o resultado informaria o número de instâncias em A2:A7 cujo valor > 0,5.

Meu entendimento era que --(A2:A7>0.5) deveria retornar uma matriz, ou assim pensei.

Por exemplo, usando SUMPRODUCT() , que aceita intervalos ou matrizes, posso fazer =SUMPRODUCT(A2:A4, B2:B4, --(A2:A4>0.5)) e o resultado será o sumproduct das colunas em que o valor na coluna A > 0,5.

Estou ciente de SUMIFS() e não estou procurando uma solução. Estou apenas curioso e procurando alguma explicação sobre o que algo como --(a2:a7>0.5) realmente retorna, e porque a função SUM() não pode lidar com isso.

    

Answer 1

Isso tudo deriva do que acontece quando você usa um intervalo de várias células em uma fórmula em que uma única célula / valor é esperado. Quando você faz isso, o Excel usa a célula única no intervalo multi-cell correspondente à linha da célula que a fórmula é inserida, se o intervalo for uma coluna (ou parte dela) ou a célula correspondente à coluna de a célula da fórmula se o intervalo for uma linha (ou parte dela). Não não retorna uma matriz.

Se não houver linha / coluna correspondente ou se um intervalo bidimensional for fornecido, então #VALUE! será usado.

Portanto, por exemplo, se você inserir a fórmula =--(A2:A7>0.5) em C5 , o Excel a avaliará como =--(A5>0.5) , pois as comparações normalmente recebem valores únicos e você forneceu um intervalo de várias células. Se você inserir a fórmula em A9 , ela será avaliada como =--(#VALUE!>0.5) .

Agora, como SUM() pode ter intervalos / matrizes de várias células ou células / valores únicos como argumentos, juntamente com o fato de que as comparações são normalmente entre único valores retornam um único valor, o Excel avalia =SUM(--(A2:A7>0.5)) como =SUM(--(A5>0.5)) (quando inserido em qualquer lugar em $5:$5 , é claro).

É por isso que você receberá um 0 ou 1 (ou o erro se a célula correspondente em $A:$A contiver um erro) se a fórmula for inserida em qualquer lugar em $2:$7 e #VALUE! erro caso contrário (desde =SUM(--(#VALUE!>0.5)) avalia para #VALUE! ).

O motivo pelo qual =SUMPRODUCT(A2:A4, B2:B4, --(A2:A4>0.5)) funciona é porque SUMPRODUCT() foi projetado para funcionar com argumentos de intervalo / matriz de várias células. (Ele irá ainda funcionar com células / valores únicos / matrizes de valor único, embora.) Como tal, ele trata as comparações como multivalorizadas com um resultado de matriz.

Existem dois métodos para fazer com que SUM() funcione da maneira desejada:

1. O método INDEX()

Esse método usa o fato de que INDEX() espera um intervalo de várias células como o primeiro argumento, e que INDEX(array,0) (ou meu INDEX(array,) preferencial) retorna a matriz inteira. Assim, a comparação em INDEX(--(A2:A7>0.5),) tem vários valores e =SUM(INDEX(--(A2:A7>0.5),)) funciona conforme desejado.

2. O método de fórmula de matriz

A comparação e outros operadores podem ser forçados a serem tratados como valores múltiplos, inserindo a fórmula =SUM(--(A2:A7>0.5)) como uma fórmula de matriz. Isto é feito usando Ctrl + Deslocar + Enter ao invés de apenas Enter ao inserir a fórmula.

    

Answer 2

De fato, =(a2:a7>0.5) retornará uma matriz de valores.

Os elementos da matriz serão TRUE ou FALSE ou um erro se a célula contiver um erro.

--(a2:a7>0.5) retornará uma matriz de 1 , 0 ou o erro, se aplicável.

sum(--(a2:a7>0.5) , quando inserido normalmente, retornará valores diferentes dependendo da localização da fórmula em relação a a2: a7. Esse valor será 1 , 0 , a mensagem de erro na matriz ou o erro #VALUE! .

sum(--(a2:a7>0.5) quando inserido como uma fórmula de matriz, mantendo pressionado ctrl + deslocamento enquanto pressiona enter , exibirá a soma do array. No entanto, se a matriz contiver um erro, a fórmula retornará o erro.