Fórmulas de matrizes dinâmicas e comportamento de matrizes transpostas

As fórmulas do Excel que devolvem um conjunto de valores, também conhecidas como matriz, devolvem estes valores às células vizinhas. Este comportamento chama-se transposição de limites.

As fórmulas que podem devolver matrizes de tamanhos variáveis são denominadas fórmulas de matriz dinâmica. As fórmulas que estão a devolver matrizes que são transpostas com êxito podem ser referidas como fórmulas de matriz transposta.

Seguem-se algumas notas para o ajudar a compreender e a utilizar estes tipos de fórmulas.

O que significa a "transposição de limites"?

Nota: As fórmulas de matriz mais antigas, conhecidas como fórmulas de matriz legadas, devolvem sempre um resultado de tamanho fixo, ou seja, são sempre transpostas para o mesmo número de células. O comportamento de transposição de limites descrito neste tópico não se aplica a fórmulas de matriz legadas.

A "transposição de limites" significa que uma fórmula resultou em múltiplos valores e esses valores foram colocados nas células vizinhas. Por exemplo, =ORDENAR(D2:D11;1;-1), que ordena uma matriz por ordem descendente, devolveria uma matriz correspondente com 10 linhas de altura. No entanto, só precisa de inserir a fórmula na célula superior esquerda ou F2, neste caso, e será automaticamente transposta para a célula F11.

Ordenar os valores nas células D2:D11 com =ORDENAR(D2:D11,1,-1)

Pontos principais

Quando pressiona Enter para confirmar a sua fórmula, o Excel irá dimensionar dinamicamente o intervalo de saída e colocar os resultados em cada célula dentro desse intervalo.
Se estiver a escrever uma fórmula de matriz dinâmica para operar numa lista de dados, pode ser útil colocar numa tabela do Excel e usar as referências estruturadas para se referir aos dados. Isto porque as referências estruturadas se ajustam automaticamente à medida que as linhas são adicionadas ou removidas da tabela.
As fórmulas de matriz transposta não são suportadas nas próprias tabelas do Excel, por isso deve colocá-las na grelha fora da Tabela. As tabelas são mais indicadas para manter linhas e colunas de dados independentes.
Depois de inserir uma fórmula de matriz transposta, quando selecionar uma célula dentro da área de transposição de limites, o Excel irá colocar um limite destacado à volta do intervalo. O limite desaparecerá quando selecionar uma célula fora da área.
Apenas a primeira célula na área do derrame é editável. Se selecionar outra célula na área de transposição de limites, a fórmula será visível na barra de fórmulas, mas o texto se torna um "fantasma" e não pode ser alterado. Se precisar de atualizar a fórmula, deve selecionar a célula no canto superior esquerdo do intervalo de matriz, alterá-la conforme necessário e, em seguida, o Excel atualizará automaticamente o resto da área de transposta quando premir Enter.
Sobreposição da fórmula – As fórmulas de matriz não podem ser inseridas se existir algo a bloquear o intervalo de saída. e se isto acontecer, o Excel devolverá um erro de #TRANSPOSIÇÃO DE LIMITES!, a indicar que existe um bloqueio. Se remover o bloqueio, a fórmula será transposta conforme esperado. No exemplo abaixo, o intervalo de saída da fórmula sobrepõe-se a outro intervalo com dados e é apresentado com um limite pontilhado sobreposto a células com valores que indicam que não pode ser transposto. Remova os dados que estão bloqueando ou copie-os para outro local e a fórmula será transposta conforme esperado.
As fórmulas de matriz legadas inseridas através de CTRL+SHIFT+ENTER (CSE) ainda são suportadas por motivos de retrocompatibilidade, mas não devem continuar a ser utilizadas. Se quiser, pode converter as fórmulas de matriz legadas em fórmulas de matriz dinâmica ao localizar a primeira célula no intervalo de matrizes, copiar o texto da fórmula, eliminar todo o intervalo da matriz legada e, em seguida, introduzir novamente a fórmula na célula superior esquerda. Antes de atualizar as fórmulas de matriz legadas para fórmulas de matriz dinâmica, deve estar ciente de algumas diferenças de cálculo entre as duas.
O Excel tem suporte limitado para matrizes dinâmicas entre livros e este cenário é suportado apenas quando ambos os livros estão abertos. Se fechar o livro de origem, as fórmulas de matriz dinâmica ligadas irão devolver um erro #REF! quando forem atualizadas.