O que são as fórmulas com chavetas no Excel (Array Formulas)

Resumo

As fórmulas que vemos no Excel com chavetas são array formulas, ou fórmulas matriz. Este tipo de fórmula é capaz de executar vários cálculos em um ou mais itens de uma matriz (array) e o seu resultado pode ser um resultado único ou vários resultados.

Para as array formulas funcionarem correctamente é necessário introduzi-las clicando em CTRL + SHIFT + ENTER (também referidas como fórmulas CSE).

{=formula}

A fórmula fica dentro de chavetas {} que não podem ser introduzidas manualmente, caso contrário a fórmula não funciona. Excepto, no Excel 365, onde as array formulas são nativas, o que significa que não é necessário usar CTRL + SHIFT + ENTER para funcionarem.

O que é uma Array (Matriz)

É útil entender o que é uma array, ou matriz, para perceber a utilização das fórmulas matriz. Uma array é uma estrutura que guarda uma lista de itens. No Excel, uma array ser vista como uma linha com vários valores, uma coluna com vários valores ou uma combinação de linhas e colunas. Um conjunto de referências de células, por exemplo A1:C4, corresponderia a uma estrutura bidimensional composta pelos valores em 4 linhas por 3 colunas:

{A1,B1,C1;A2,B2,C2;A3,B3,C3;A4,B4,C4}

Matriz vertical (coluna)

Se separarmos os itens da lista com ponto e vírgula (;) estamos a criar uma matriz vertical (uma coluna).

{D6;D7;D8}
{Portugal;Brazil;Argentina}

Matriz horizontal (linha)

Se usarmos uma vírgula (,) estamos a criar uma matriz horizontal (uma linha).

{B6,C6,D6}
{1,Portugal,Cristiano Ronaldo}

Matriz bidimensional (linhas e colunas)

Podemos ainda criar matrizes bidimensionais delimitando cada item da linha com uma vírgula e cada linha com um ponto e vírgula.

{B6,C6,D6;B7,C7,D7}
{1,Portugal,Cristiano Ronaldo;2,Brazil,Pelé (Ret)}

Exemplos de utilizção

Nem todas as array formulas necessitam do CSE para ser introduzidas. Algumas fórmulas, como por exemplo SUMPRODUCT (SOMARPRODUTO), estão preparadas para lidar com operações com matrizes de dados e por isso podem ser introduzidas normalmente. No exemplo abaixo, para o mesmo cálculo, se usarmos a função SUM (SOMA) precisamos introduzir a fórmula com CTRL + SHIFT + ENTER.

{=SUM(LEN(F6:F8))}
{=51;49;70}
{=2+2+2}
=6

Usando SUMPRODUCT não é necessário usar CSE.

=SUMPRODUCT(LEN(F6:F8))
=51;49;70
=2+2+2
=6

Outro exemplo de aplicação de uma array formula é procurar um determinado valor numa matriz. A fórmula percorre todos os valores da matriz e se o valor for encontrado o resultado devolvido é verdadeiro (TRUE).

{=OR(D6:D9="Lionel Messi")}
{=FALSE;FALSE;TRUE;FALSE}
=TRUE

Salvo algumas excepções, a maioria dos cálculos onde são usadas array formulas podem também ser feitos com fórmulas regulares.

Usando uma fórmula regular onde vamos multiplicar linha a linha e somamos os resultados de cada linha.

=SUM(E23*E24;F23*F24;G23*G24;H23*H24;I23*I24)

Introduzimos manualmente ambas as arrays (hard coded) e multiplicamos.

=SUM({2;4;6;8;10}*{1;3;5;7;9})

Ou usando uma array formula em que introduzimos as referências de célula das arrays (o número de linhas tem que ser exactamente o mesmo em ambas as arrays).

{=SUM(E23:I23*E24:I24)}

Vantagens e desvantagens das fórmulas matriz

Com as fórmulas matriz as fórmulas são iguais garantindo a consistência de fórmulas na worksheet.
Arrays armazenadas na memória podem acelerar o tempo de avaliação das fórmulas (mas não do recálculo).
São aplicadas em blocos de arrays e a fórmula tem que ser editada ou apagada em bloco evitando que se altere alguma linha por engano.
Muitas fórmulas matriz podem afectar a performance do ficheiro e tornar mais lento os recálculos (não é recomendável o uso excessivo destas fórmulas).
São mais difíceis de entender que fórmulas regulares e não permitem usar referências de colunas (por exemplo, A:A ou B:B).
Têm que ser sempre introduzidas com CTRL + SHIFT + ENTER (excepto no Excel 365).

Descargas

Descarrega aqui a folha de cálculo com os exemplos do artigo sobre a utilização de array formulas