(Eu originalmente perguntei isso no stackoverflow. No entanto, as fórmulas do Excel definitivamente ficam na linha entre "programação" e, um, "superutilização", então eu acredito que este cross-post é apropriado.)
Muitas das funções internas do Excel podem receber argumentos de matriz. Às vezes o resultado é documentado na ajuda e às vezes não. Então:
=IF({1,0,1}, 42, 99)
retornará {42, 99, 42}. A ajuda para 'IF' abrange argumentos de matriz. Mas:
=INDEX({2,3,5,7,11}, {2,4})
retornará {3, 7}. Isso é intuitivo, mas não consigo encontrar uma fonte da Microsoft que o documente. E:
=INDEX({1,2,3,4;5,6,7,8;9,10,11,12;13,14,15,16}, {1,3},{2,4})
retorna {2, 12}, o que não é intuitivo.
Existe alguma fonte que cubra esses usos menos comuns da matriz? Parece que tem que haver um, mas eu não estou achando isso em uma pesquisa na web porque ele requer o uso de "Excel", "function" e "array" ...
(Além disso, alguém pode marcar essa pergunta com a 'fórmula da matriz', já que não tenho o poder de criar tags?)
Estou confortável em usar fórmulas de array e usá-las o tempo todo, mas odeio ter que descobrir o que acontecerá por tentativa e erro. Essa questão em particular surgiu quando eu estava fazendo o (equivalente simplificado de):
=IF(ISNA(udf()), {1,2,3,4}, {5,6,7,8})
onde 'udf' estava retornando, por exemplo, {1,2,3}, e a fórmula estava avaliando para {5,6,7, # N / A}. Isso me surpreendeu, embora eu possa descobrir o que está acontecendo e contornar isso. É só me incomodar que eu não posso encontrar uma fonte autoritária que define tudo.
Vincular à pergunta no stackoverflow (há várias respostas e alguns comentários, mas nada que satisfaça a pergunta agora) aqui: link
EDIT: Eu fiz uma grande edição na versão stackoverflow desta questão, e incorporou parte da resposta de Neal lá. A adição é muito do lado "programação" das fórmulas do Excel, por isso não vou repetir aqui.
Charles Williams postou um link para o site da Decision Models que tem a informação que eu estava procurando em sua resposta no stackoverflow:
link
O link em si é:
(Todo o site é um excelente recurso do Excel, e seu produto FastExcel é muito bom, BTW.)
Eu não sei de nada de útil para acompanhar o que você está fazendo: não me lembro de ter visto um exemplo de uso de fórmulas de array (pelo menos com um array onde se esperaria ver uma única coisa. veja arrays usados em exemplos da função INDEX).
Eu acho que é relativamente simples se você pensar no que aconteceria se você expandisse o array (existe uma terminologia melhor para isso? Por favor edite se você puder pensar em um). Onde você usa um argumento de matriz onde a função esperaria um argumento normal, o resultado é uma matriz com o mesmo número de elementos que o argumento de matriz, sendo cada elemento da matriz resultante o que o resultado seria com o único argumento.
Então, no seu primeiro exemplo
=IF({1,0,1}, 42, 99)
você recebe uma matriz
{ IF(1, 42, 99), IF(0, 42, 99), IF(1, 42, 99)}
que sai como
{ 42, 99, 42 }
No seu último exemplo
=IF(ISNA({1,2,3}), {1,2,3,4}, {5,6,7,8})
o resultado é
{ IF(ISNA(1),1,5) , IF(ISNA(2),2,6) , IF(ISNA(3),3,7) ,IF(ISNA(),4,8) }
Os primeiros três ISNAs retornam falso e o quarto falha, então o resultado é o que você obteve. Eu acho que a única coisa confusa aqui poderia ser onde o valor do erro é.
Em princípio, achei que o resultado de um erro seria mais lógico do que uma matriz de três elementos apropriados e um elemento que é um erro, mas, pensando nisso, você pode inserir uma fórmula de matriz em um intervalo de planilha para corresponder o número de elementos na matriz. Se você pensar em fazer isso, o elemento de erro faz todo o sentido e pode ajudá-lo a encontrar o erro.