RESUMO
Alguém pode explicar por que o Excel - versões atuais no Mac e no Windows - às vezes se recusa a aceitar, na fórmula de uma célula, isto:
CHOOSECOLS(SomeArray,ColNum)
mas aceita isso:
INDEX(SomeArray,0,ColNum)
Dois exemplos específicos são quando esses formulários são usados em uma função SUMIF() ou COUNTIF(). E quando digo que o Excel se recusa a aceitar o primeiro, quero dizer que nem consigo aceitá-lo como uma fórmula sintaticamente correta. Observo que versões não condicionais dessas funções não mostram o problema, nem funções como MAX(), MIN(), AVERAGE(), etc.
DETALHES
Suponha que eu tenha um array em B5:C10 (suponha que seja dinâmico, então posso me referir a ele como B5# nos exemplos a seguir; mas o problema também existe mesmo com intervalos não dinâmicos), e quero somar todos os itens maior que 0 na coluna 2 (ou seja, em C5:C10). Embora o Excel aceite o seguinte:
=SUMIF(INDEX(B5#,0,2),">0")
recusa-se a aceitar:
=SUMIF(CHOOSECOLS(B5#,2),">0")
No entanto, se eu primeiro extrair a coluna desejada em uma matriz auxiliar enraizada em C16 (digamos), da seguinte maneira:
=CHOOSECOLS(B5#,2)
então o seguinte é aceito como fórmula correta (e fornece o resultado correto):
=SUMIF(C16#,">0")
Tenho uma planilha Excel que mostra isso em ação, mas não tenho certeza da melhor maneira de anexá-la. Se for útil e alguém puder explicar como posso anexar um arquivo .xlsx, eu o fornecerei. Enquanto isso, aqui está uma captura de tela:
SUMIFe outras funções listadas são chamadas de funções RACON, que requerem uma entrada de intervalo. (você pode ler mais sobre isso aqui )O fato de funcionar com INDEX é uma pequena surpresa para mim, para ser honesto, mas acho que o Excel ainda interpreta isso como uma entrada de intervalo. Se você o tivesse usado,
=SUMIF(INDEX(B5#,{1;2},2),">0")não seria mais aceito como um intervalo, mas será reconhecido como um array, que não é uma entrada aceita.Podemos contornar esse problema examinando outras funções do Excel que não estão limitadas à entrada de intervalo, como MMULT. Embora seja um pouco menos simples que SUMIFS, deixe-me tentar explicar a lógica:
MMULTusa multiplicação de matrizes . se você tiver 2 matrizes:MMULT multiplicará a primeira linha da matriz1 pelos valores transpostos da primeira coluna da matriz 2:
={1,2,3}*{10;20;30}a soma desses produtos é o primeiro resultado do MMULT:10+40+90= 140em seguida, multiplicará os valores da primeira linha da matriz1 para a próxima coluna transposta da matriz2:
={1,2,3}*{4;5;6}=4+10+18= 32a seguir, a segunda linha da matriz1 contra a primeira coluna transposta da matriz2:
={4,5,6}*{10;20;30}=40+100+180= 320e finalmente a segunda linha da matriz1 contra a segunda coluna transposta da matriz2:
={4,5,6}*{4;5;6}=16+25+36= 77Isto resulta em:
então mais visual:
Por que estou contando isso?
SUMIFSnão podemos usar arrays como argumento de intervalo, mas MMULT pode e podemos usar uma lógica semelhante a SUMIFS dentro de MMULT, só precisamos multiplicá-lo por um array com a mesma altura (nº de linhas) que a largura (nº de colunas) de a matriz de entrada.Então, voltando
B5#e traduzindo os argumentos SUMIFS para MMULT seria:=MMULT(TOROW(CHOOSECOLS(B5#,2)),N(CHOOSECOLS(B5#,2)>0))ou=LET(b,CHOOSECOLS(B5#,2),MMULT(TOROW(b),N(b>0)))Ele transpõe os valores de
CHOOSECOLS(B5#,2)para uma linha e os multiplica para uma matriz vertical de booleanos (CHOOSECOLS(B5#,2)valores maiores que 0 VERDADEIRO ou FALSO), onde VERDADEIRO e FALSO são convertidos em sua representação numérica1e0respectivamente. Isso é então SOMADO.mas neste caso também poderíamos usar o booleano dentro de SUM:
=LET(b,CHOOSECOLS(B5#,2),SUM(b*(b>0)))Então por que se preocupar com a explicação do MMULT?
Como o MMULT também tem a capacidade de distribuir uma matriz de resultados:
Portanto, no seu caso, se somarmos os valores maiores que 0 em linha, teríamos que arrastar manualmente a versão SUM ou usar uma versão BYROW LAMBDA, mas MMULT também pode fazer isso perfeitamente de uma só vez, sem arrastando manualmente:
=MMULT((B5#>0)*B5#,{1;1})derramaria:ou um equivalente em CONT.SE:
=MMULT(N(B5#>0),{1;1})