Fórmulas de Matriz no Excel – Guia Completo

Se você quer aprender o que é Fórmulas de Matriz no Excel e como usá-las corretamente em suas planilhas, então você está no lugar certo!

As fórmulas de matriz são recursos muito interessantes e, provavelmente, um dos conceitos que mais causam dúvidas por parte dos usuários de Excel. Por meio desse tipo de fórmula, é possível realizar diversos cálculos de modo a substituir muitas fórmulas comuns.

Através deste tutorial você irá aprender a dominar o conceito de fórmulas de matriz no Excel para começar a aplicar em suas planilhas hoje mesmo. Confira!

Afinal, o que são fórmulas de matriz?

Para aprender o que as fórmulas de matriz no Excel, primeiramente é necessário aprender o conceito de “matriz”.

No Excel, o termo “matriz” representa uma coleção de itens. Esses itens podem ser textos ou números que podem estar em uma única linha ou coluna ou simplesmente em várias linhas e colunas. Veja a imagem abaixo, onde cada célula ou intervalo de células pintados são considerados exemplos de matrizes no Excel:

Agora que você já sabe o que é uma matriz no Excel, a pergunta que fica a pergunta: O que é uma fórmula de matriz?

Basicamente, a diferença entre uma fórmula de matriz e as fórmulas comuns do Excel é que uma fórmula de matriz é capaz de processar vários valores em vez de apenas um. Ou seja, uma fórmula de matriz no Excel avalia todos os valores individuais em uma matriz e executa vários cálculos em um ou vários itens de acordo com as condições expressadas na fórmula.

Além de conseguir lidar com vários valores simultaneamente, as fórmulas de matriz também são capazes de retornar vários valores por vez. Isso significa que os resultados retornados por uma fórmula de matriz do Excel também são uma matriz.

Como criar uma fórmula de matriz

Para que você possa dar os primeiros passos com fórmulas de matriz no Excel, vamos começar com um exemplo simples e fácil de entender.

Suponha que você tenha uma lista de produtos com quantidades vendidas e preços de venda e queira obter o total em vendas.

Na maneira tradicional, você pode fazer isso você isso calculando subtotais em cada linha primeiro e, posteriormente, somando o resultados de cada subtotal conforme mostrado abaixo:

Contudo, uma fórmula de matriz pode lhe poupar tempo, pois permite que o Excel armazene resultados intermediários na memória, em vez de em uma coluna adicional. Para isso, basta seguir as seguintes etapas:

1. Selecione uma célula vazia e insira a seguinte fórmula: =SOMA(B2:B6*C2:C6)

2. Pressione o atalho de teclado CTRL + SHIFT + ENTER para concluir a fórmula da matriz. Após fazer isso, o Excel irá envolver a fórmula com {chaves}, que é uma indicação visual de uma fórmula de matriz.

Basicamente o que a fórmula faz é multiplicar os valores em cada linha individual da matriz especificada (células B2 a C6), adicionar os subtotais e gerar o total geral.

Este simples exemplo mostra o quão poderosa pode ser uma fórmula de matriz do Excel. Isso significa que ao trabalhar com centenas e milhares de linhas de dados, você pode economizar muito tempo inserindo uma fórmula de matriz em uma única célula.

Fórmulas de matriz em uma única célula e em múltiplas células

As fórmulas de matriz do Excel podem retornar um resultado em uma única célula ou em várias células.

Uma fórmula de matriz inserida em uma única célula é conhecida como fórmula de célula única. Por outro lado, uma fórmula de matriz em um intervalo de células é chamada de fórmula de várias células. Os exemplos a seguir demonstram como usar uma fórmula de matriz em uma única célula ou em várias células.

Exemplo 1: Fórmula de Matriz em uma única célula

Suponha que você tenha uma planilha com o registro de vendas de produtos de dois meses conforme mostrado abaixo. Neste caso você precisa encontrar o maior aumento de vendas.

No modo tradicional, você poderia fazer isso adicionando uma coluna extra à planilha (digamos, coluna D) e calculando as vendas de cada produtos usando =C2-B2 e em seguida usando a fórmula MÁXIMO(D:D) para encontrar o maior valor.

Por outro lado, ao usar uma fórmula de matriz, você descarta o uso de uma coluna adicional, já que a fórmula será capaz de armazenar os resultados intermediários em sua memória.

Isso pode ser feito com a seguinte fórmula de matriz. =MÁXIMO(C2:C6-B2:B6)

Importante: Ao término na fórmula, não se esqueça de usar Ctrl + Shift + Enter para que as chaves {} sejam aplicadas na fórmula.

Exemplo 2: Fórmula de Matriz em várias células

Utilizando o mesmo exemplo citado anteriormente, agora vamos supor que você tenha que pagar 20% de imposto por cada venda. Neste caso, você precisará calcular o valor do imposto por cada produto por meio de uma fórmula.

Para isso, selecione o intervalo de células em alguma coluna em branco (por exemplo, D2:D6) e digite a seguinte fórmula na barra de fórmulas:

{=(B2:B6*C2:C6)*0,2}

Exemplo 3: Usando uma função de matriz

No Excel, existem algumas funções de matriz projetadas para retornar matrizes de várias células, por exemplo, TRANSPOR, TENDÊNCIA, FREQUÊNCIA, entre outras.

Neste exemplo, iremos usar a função TRANSPOR como fórmula de matriz para converter uma tabela de linhas em colunas.

Para isso, basta selecionar um intervalo de células vazio no qual você deseja transportar a tabela. Como estamos convertendo linhas em colunas, basta selecionar o mesmo número de linhas e colunas da tabela original.

Em seguida, insira função da matriz =TRANSPOR(matriz) e pressione Ctrl + Shift + Enter. Veja o exemplo abaixo:

Dicas importantes sobre fórmulas de matriz com várias células

Se você estiver trabalhando com fórmulas de matriz com várias células, atente-se as seguintes regras:

1. Antes de inserir a sua fórmula de matriz, selecione o intervalo de células desejado.

2. Para excluir uma fórmula de matriz com várias células, é necessário selecionar todas as células e apertar Delete ou selecionar a fórmula inteira na barra de fórmulas e pressionar Delete e Ctrl + Shift + Enter em seguida.

3. Em fórmulas de matriz, você não pode editar, mover ou inserir novas células ou excluir células existente. Sempre que você tentar fazer isso, receberá a seguinte mensagem: “Você não pode alterar parte de uma matriz“.

4. Se você precisar reduzir uma fórmula de matriz, ou seja, aplicá-la a menos células, será necessário excluir a fórmula e depois inserir uma nova.

5. Se você precisar aumentar a sua fórmula de matriz, ou seja, expandir para outras células, basta selecionar todas as células que contenham a fórmula atual e as demais células que deseja, pressionar F2, inserir as referências novas na fórmula e pressionar Ctrl + Shift + Enter para finalizar.

6. Fórmulas de matriz não podem ser utilizadas em várias células em tabelas no Excel.

7. As fórmulas de matriz com várias células devem ser inseridas com a mesma quantidade de linhas e colunas da matriz resultante. Células extras resultarão no erro #N/D.

Tecla F9 – Analisando partes da fórmula

Quando se trabalha com fórmulas de matriz, é possível analisar como elas calculam e organizam seus dados para mostrar o resultado final.

Para fazer esse tipo de análise, basta selecionar um ou vários argumentos entre parênteses de uma função e em seguida, pressionar a tecla F9. Para sair do modo de análise, basta pressionar a tecla Esc.

No exemplo abaixo podemos ver como a fórmula calcula os subtotais e depois realiza a soma deles para exibir o total.

Dicas extras sobre fórmulas de matriz

• As fórmulas de matriz costumam lentas em termos de desempenho de cálculo. Em muitos casos, você não notará a diferença, mas se você usar fórmulas de matriz em grande escala, poderá diminuir significativamente o desempenho da sua planilha.
• As fórmulas de matriz estão disponíveis em todas as versões do Excel 2013, Excel 2010, Excel 2007 e versões inferiores.
• No geral, as fórmulas de matriz são recomendadas para usuários de nível intermediário a avançado em Excel, pois são um pouco mais difíceis de serem compreendidas, criadas e editadas.
• Uma única fórmula de matriz pode substituir literalmente centenas de fórmulas comuns. Portanto, em alguns casos, vale a pena avaliar a possibilidade de usá-las.
• Toda fórmula de matriz deve obrigatoriamente finalizar com as teclas Ctrl + Shift + Enter para que os símbolos de chaves {} sejam aplicados à fórmula.

Sinta-se à vontade para fazer download dos exemplos deste artigo em uma pasta de trabalho do Excel. Basta clicar aqui para iniciar o download.

Confira também as seguintes dicas de Excel:

• Como Multiplicar no Excel – Passo a Passo
• Função PROCV com Duas Condições (ou mais)
• Função SOMARPRODUTO no Excel: Como usar?

E então, o que achou da dica sobre fórmulas de matriz no Excel? Deixe o seu comentário e continue acompanhando mais dicas de Excel em nosso site!