No Excel, exatamente por que o EOMONTH () (e outros) não funciona em fórmulas de matriz?

Question

Se uma expressão do Excel pode ser convertida para o formulário de matriz, às vezes é difícil prever. Por exemplo, a captura de tela a seguir mostra uma tabela com uma linha de datas na parte superior, seguida por quatro linhas que fazem referência à primeira. As três primeiras (isto é, as linhas 3, 4 e 5 do Excel) contêm, cada uma, uma fórmula de matriz. Os dois primeiros são bons, mas o terceiro, usando EOMONTH (), quebra. Em seguida, a linha 6 do Excel mostra a mesma fórmula EOMONTH (), mas em forma sem matriz. Funciona bem. Em B3: B5 e C7: I7 eu usei FORMULATEXT () para exibir as funções nas várias linhas e células.

Nãovejonadadeóbviona documentação para EOMONTH () que prevê a falha.

Existe uma maneira de descobrir quais funções têm esse tipo de limitação? ( This é ostensivamente a forma geral da minha pergunta, mas realmente não ajuda muito).

    

Answer 1

A Microsoft simplesmente decidiu não estender todas as fórmulas para trabalhar com matrizes.

Alguns podem ter sido difíceis, alguns podem ter sido ilógicos ou talvez tenham ficado sem tempo, quem sabe. Como resultado, há uma lista de funções que podem manipular arrays, e o restante não pode. Eu não tenho um link agora (vou procurar), mas é um fato que existe uma lista definida.

Para muitas funções, você pode usar o Google como solução alternativa, por exemplo, em vez de usar EOMONTH(range,0) , você pode usar DATE(YEAR(range),MONTH(range)+1,0)} (o primeiro dia do mês seguinte é simplesmente um dia antes do primeiro dia do próximo mês ...) , em vez de OR(cond1,cond2,...) , você pode usar IF(cond1+cond2+...>0,.. e, para AND(cond1,cond2,...) , pode substituir IF(cond1*cond2*...,... , porque o Excel trata TRUE como 1 e FALSE como 0.

Algumas fórmulas (as que estavam no Analysis ToolPak no Excel 2003) também podem ser feitas para trabalhar com arrays , mas não com intervalos - diferença sutil! Adicione - - (menos menos, mas não em branco) antes do intervalo para torná-lo uma matriz: EOMONTH(- -range,0)

    

Answer 2

Não funciona. Mas você pode fazer:

{=DATE(YEAR(C$2:i$2),MONTH(C$2:I$2)+1,0)}

    

Answer 3

Além da solução observada por outras pessoas aqui

{=DATE(YEAR(C$2:i$2),MONTH(C$2:I$2)+1,0)}

você pode criar uma UDF baseada em array. Em outras palavras, fale!

A pasta de trabalho do Excel deve estar habilitada para Macro para que o UDF funcione (o que pode ou não ser um problema para você). Alguns recursos em UDFs baseados em array:

• myonlinetraininghub (achei a linguagem desta maneira fácil de entender).
• Here no SE
• Outro SE - discute a entrada como um intervalo ou uma matriz. O exemplo na resposta usa duas UDFs, mas pelo bom uso de TypeName , você pode combiná-las em uma única UDF que sabe se um intervalo ou uma matriz foi passada.