Fórmulas de Arranjo

Fórmulas de Arranjo são uma ferramenta poderosa no Excel. Enquanto uma fórmula comum faz um cálculo e retorna um valor como resultado, uma fórmula de arranjo pode fazer vários cálculos em um ou mais intervalos e retornar um ou múltiplos valores.

Este artigo explica como usar Fórmulas de arranjo.

Introdução

Antes de continuar a leitura desse texto, é recomendável saber como Analisar Fórmulas.

Um arranjo (do inglês array) é uma estrutura de dados que armazena uma coleção de elementos. No Excel, pode ser de uma ou duas dimensões. Um arranjo de uma dimensão (ou vetor) pode ser entendido como uma seleção contígua de células de uma linha (horizontal) ou de uma coluna (vertical) em que cada célula corresponde a um elemento de um arranjo:

Um arranjo de duas dimensões (ou matriz) pode ser representado como um intervalo retangular de mais de duas linhas e duas colunas de células no Excel:

Estou sendo ousado neste artigo ao mudar a nomenclatura oficial da Microsoft ao chamar esse tipo de fórmula de Fórmulas de Arranjo. Elas são popularmente conhecidas como Fórmulas Matriciais ou Fórmulas de Matriz, mas tecnicamente esses nomes estão errados: Uma matriz é um arranjo de duas dimensões e um vetor é um arranjo de uma dimensão. Logo, arranjo engloba tanto matrizes como vetores.

No Excel, arranjos podem ficar armazenados em células ou na memória. Fórmulas que conseguem lidar com arranjos possibilitam soluções incríveis!

Constantes de Arranjo

Abaixo são mostrados três intervalos que fazem analogias a arranjos:

A forma mais fácil de criar um arranjo é clicar em uma célula qualquer, pressionar =, selecionar o intervalo (com o mouse ou não) ao qual deseja converter em arranjo e pressionar a tecla F9:

Obtemos para esse intervalo vertical:

{"B";10;#DIV/0!;8;0;0;11;"Felipe";VERDADEIRO}

E para o caso do intervalo horizontal:

{82\#REF!\"CD"\13\0\FALSO\0\"D"\"c"}

Essas duas expressões possuem constantes de arranjo. Você pode escrevê-las manualmente ao invés de usar essa dica, mas acho mais demorado e complicado.

Algumas coisas interessantes podem ser percebidas nessas expressões:

  • Uma constante de arranjo começa com o símbolo { e termina com }.
  • ; é o separador de linha em arranjos.
  • \ é o separador de coluna em arranjos (em versões antigas do Excel, o símbolo do separador de colunas é o .).
  • Cada constante de arranjo possui uma quantidade de elementos igual ao número de células da seleção efetuada.
  • As regras de tipos de dados são respeitadas: textos aparecem entre aspas duplas, números, erros e valores lógicos livres (sem aspas duplas).
  • Células em branco são preenchidas com 0 em um arranjo.

No caso bidimensional, considere o intervalo abaixo:

Usando a técnica descrita anteriormente, ao transformar o intervalo D9:G12 num arranjo constante, obtemos:

{1\2\3\4;5\6\7\8;9\10\11\12;13\14\15\16}

Observe a notação de um arranjo bidimensional: é listado cada elemento horizontal, separado por \ e cada quebra de linha é representado por um ;.

Fórmulas com Arranjos de Constantes

O Excel é capaz de processar fórmulas que possuem arranjos em seus argumentos como:

=SOMA({5;8;4;3})

Ou também:

=SOMA({5;8;4;3};{-5;0;12;215;8})

É importante ressaltar que a fórmula acima possui apenas dois argumentos, ambos arranjos. Relembrando a definição de arranjo: é uma estrutura de dados que armazena uma coleção de elementos.

Quando trabalhamos com referências em fórmulas ao invés de arranjos de constantes e queremos transformar as referências em nesses arranjos o resultado, devemos usar uma fórmula de arranjo.

Existem dois tipos de Fórmulas de arranjo: as que retornam o resultado numa única célula e as que retornam o resultado num intervalo de células. A seguir, veremos os dois tipos.

Fórmula de Arranjo numa Única Célula

Vamos analisar a fórmula abaixo:

=SOMA({52;22;18;9;7;30;11;16}*{1,1;0,7;2,6;5;7,5;5,5;6,5;10})

Observe que há uma multiplicação entre os dois arranjos. O Excel irá multiplicar o primeiro elemento do vetor azul (52) com o primeiro elemento do vetor vermelho (1,1), resultando 57,2 nesse elemento. Isso é feito para todos elementos, até a fórmula ser simplificada para:

=SOMA({57,2;15,4;46,8;45;52,5;165;71,5;160})

Por fim, essa fórmula é avaliada no valor 613,40.

Podemos fazer uma analogia da multiplicação desses dois arranjos com o cenário a seguir:

Pelo que sabemos entre o relacionamento de arranjos e referências, podemos afirmar que:

  • O arranjo C3:C10 pode ser transformado em {52;22;18;9;7;30;11;16}.
  • O arranjo D3:D10 pode ser transformado em {1,1;0,7;2,6;5;7,5;5,5;6,5;10}.

A fórmula original – isto é, expressa pelos arranjos de constantes – funciona normalmente. Será que se substituirmos os arranjos de constantes da fórmula original por referências da tabela continuará funcionando? Escrevendo em D12 a fórmula:

=SOMA(C3:C10*D3:D10)

Você perceberá que essa fórmula não funciona e irá retornar o erro #VALOR!. Isso acontece porque temos que avisar ao Excel que estamos querendo transformar as referências em arranjos de constantes. A forma que temos para sinalizar isso é pressionar Ctrl+Shift+Enter ao invés de Enter ao entrar a fórmula:

Veja como a fórmula está sendo exibida na barra de fórmulas:

{=SOMA(C3:C10*D3:D10)}

O fato da fórmula estar sendo exibida com os símbolos { e } é apenas um efeito visual para mostrar ao usuário que o Excel está avaliando a fórmula como uma fórmula de arranjo. Você nunca deverá escrever os símbolos { e } explicitamente ao entrar uma fórmula de arranjo.

Ora, o que fazemos agora tem bastante utilidade: descobrimos o valor total de uma lista, efetuando multiplicações e uma soma. Outra forma de resolver isso seria criar uma coluna adicional chamada, por exemplo, Preço Total (R$), multiplicar o valor unitário de cada célula pela quantidade vendida numa coluna auxiliar e, ao final, somar tudo e obter o resultado. No entanto, apenas uma fórmula de arranjo poupou você de todo esse trabalho, retornando o resultado correto.

Vale relembrar: pressione Ctrl+Shift+Enter para entrar uma fórmula de arranjo. Fórmulas comuns são entradas com Enter, mas o Excel só irá converter referências em arranjos de constantes se você entrar a fórmula com Ctrl+Shift+Enter. Do contrário, você poderá obter um erro ou resultado incorreto.

Algumas fórmulas entendem arranjos naturalmente e não precisam de Ctrl+Shift+Enter. Por exemplo, a fórmula desse exemplo poderia ser substituída sem problemas por =SOMARPRODUTO(C3:C10;D3:D10).

Resultado em um Intervalo de Células

Considere o exemplo abaixo:

Para calcular o preço total de cada produto, basta escrever a fórmula na célula E3:

=C3*D3

Em seguida, copiar essa fórmula e colar abaixo. Mas não vamos fazer isso agora.

Vamos criar uma fórmula de arranjo. Primeiro, selecione o intervalo E3:E10 de forma que a célula ativa seja a E3:

Em seguida, digite a expressão abaixo da forma que achar mais fácil e pressione Ctrl+Shift+Enter:

=C3:C10*D3:D10

Veja que os resultados foram preenchidos corretamente e que a fórmula está sendo representada como {=C3:C10*D3:D10}, por ser uma fórmula de arranjo:

Note também que apesar de todas as fórmulas da região da fórmula de arranjo conter a mesma fórmula, retornam resultados diferentes.

Se você selecionar a expressão =C3:C10*D3:D10 da fórmula e pressionar F9, será mostrado:

={57,2;15,4;46,8;45;52,5;165;71,5;160}

O Excel atribui o primeiro elemento do arranjo (57,2) à primeira célula do intervalo, 15,4 à segunda célula e assim por diante. Logo, primeiro o Excel primeiro processa internamente as operações do arranjo e depois preenche os valores das células.

Descobrir qual é a Região de uma Fórmula de Arranjo

Muitas vezes trabalhamos em uma planilha que possui uma fórmula de arranjo de intervalo e não sabemos qual é a região de uma determinada fórmula de arranjo.

Para descobrir a região de fórmula de arranjo de uma célula, clique sobre ela, em seguida vá na guia Página Inicial >> botão Localizar e Selecionar >> Ir para Especial:

Na janela abaixo, selecione Matriz atual e clique em OK:

Use um dos atalhos a seguir para ir direto a essa tela: F5, Alt+E ou então Ctrl+G, Alt+E.

O resultado será como mostrado:

Se você quiser, pode apagar a fórmula de arranjo pressionando Delete e então recriá-la.

Vantagens e Desvantagens

Fórmulas de arranjo oferecem segurança. Se você tentar apagar uma linha onde corta uma fórmula de arranjo ou mesmo alterá-la, receberá a mensagem de erro abaixo:

Dessa forma, você protege a integridade da sua fórmula e garante que todas as células de uma região de arranjo possuem a mesma fórmula.

No entanto, essa característica também pode ser uma desvantagem. Se você quiser acrescentar uma linha na tabela acima, terá que apagar e reescrever a fórmula de arranjo para que ela englobe todas as células da coluna Preço Total.

Download

Para fazer download da pasta de trabalho usada neste artigo, clique aqui.

Sobre Felipe Gualberto

Microsoft Most Valuable Professional (MVP) de Excel.
Esta entrada foi publicada em Tutoriais e marcada com a tag , , , . Adicione o link permanente aos seus favoritos.