Um Excel array Fórmula é uma fórmula que realiza cálculos sobre os valores em uma ou mais matrizes, em vez de um único valor de dados. Em programas de planilha, um array é um intervalo ou série de valores de dados relacionados que são normalmente armazenados em células adjacentes em uma planilha. Este guia explica como usar fórmulas de matriz em todas as versões modernas do Excel.
Este tutorial é compatível com a maioria das versões do Excel, incluindo 2007, 2010, 2013, 2016 e Office 365.
O que são fórmulas de matriz?
As fórmulas de matriz são semelhantes às fórmulas regulares:
- Eles começam com um sinal de igual ( = )
- Use a mesma sintaxe das fórmulas regulares
- Use os mesmos operadores matemáticos
- Siga a mesma ordem de operação
No Excel, as fórmulas de matriz são cercadas por chaves { } - estes aparelhos não podem ser simplesmente digitados; eles devem ser adicionados a uma fórmula pressionando o Ctrl , Mudança , e Entrar chaves depois de digitar a fórmula em uma célula ou células. Por esse motivo, uma fórmula de matriz é às vezes chamada de CSE Fórmula no Excel.
Uma exceção a essa regra é quando chaves são usadas para inserir uma matriz como um argumento para uma função que normalmente contém apenas um único valor ou referência de célula. Por exemplo, no tutorial abaixo, que usa VLOOKUP e a ESCOLHER função para criar uma fórmula de pesquisa à esquerda, uma matriz é criada para o ESCOLHER funções Index_num argumento, digitando as chaves em torno da matriz inserida.
Passos para criar uma fórmula de matriz
- Digite a fórmula.
- Mantenha o botão Ctrl e Mudança teclas no teclado.
- Pressione e solte o Entrar chave para criar a fórmula de matriz.
- Liberte oCtrl e Mudança chaves.
Se feito corretamente, a fórmula será cercada por chaves e cada célula que contém a fórmula conterá um resultado diferente.
Editando uma fórmula de matriz
Sempre que uma fórmula de matriz é editada, as chaves desaparecem da fórmula da matriz. Para recuperá-los, a fórmula de matriz deve ser inserida pressionando o botão Ctrl, Mudançae Entrar chaves novamente, assim como quando a fórmula de matriz foi criada pela primeira vez.
Tipos de fórmulas de matriz
Existem dois tipos principais de fórmulas de matriz:
- Fórmulas de matriz de célula única que realizam vários cálculos em uma única célula da planilha.
- Fórmulas de matriz de células múltiplas que estão localizadas em mais de uma célula da planilha.
Fórmulas de Matriz de Célula Única
Esse tipo de fórmula de matriz usa uma função, como SOMA, MÉDIAou CONTAGEM, para combinar a saída de uma fórmula de matriz de células múltiplas em um único valor em uma única célula. Um exemplo de uma fórmula de matriz de célula única seria:
{= SUM (A1: A2 * B1: B2)}
Esta fórmula adiciona o produto de A1 * B1 e A2 * B2e, em seguida, retorna um único resultado em uma única célula na planilha. Outra maneira de escrever a fórmula acima seria:
Fórmulas de Matriz Multi-Célula
Como o próprio nome sugere, essas fórmulas de matriz estão localizadas em várias células da planilha e retornam uma matriz como uma resposta. Em outras palavras, a mesma fórmula está localizada em duas ou mais células e retorna respostas diferentes em cada célula.
Cada cópia ou instância, da fórmula de matriz, executa o mesmo cálculo em cada célula que ela habita, mas cada instância da fórmula usa dados diferentes em seus cálculos. Portanto, cada instância produz resultados diferentes. Um exemplo de uma fórmula de matriz de múltiplas células seria semelhante a:
{= A1: A2 * B1: B2}
Se o exemplo acima estiver localizado em células C1 e C2 em uma planilha, os resultados seriam os seguintes:
- Os dados em A1 é multiplicado pelos dados em B1, e os resultados são armazenados em célula C1.
- Os dados em A2 é multiplicado pelos dados em B2, e os resultados são armazenados em célula C2.
Usando matrizes para transpor linhas e colunas
o TRANSPOR A função é usada para copiar dados de uma linha para uma coluna ou vice-versa. Essa função é uma das poucas no Excel que sempre deve ser usada como uma fórmula de matriz.
Fórmulas de matriz e funções do Excel
Muitas das funções internas do Excel, como SOMA, MÉDIAe CONTAGEM, também pode ser usado em uma fórmula de matriz. Existem também algumas funções, como a TRANSPOR função, que sempre deve ser inserida como uma matriz para que funcione corretamente. A utilidade de muitas funções, como ÍNDICE e PARTIDA ou MAX e E SE pode ser estendido usando-os juntos em uma fórmula de matriz.
Exemplos de fórmulas de matriz no Excel:
01 de 08Criar uma fórmula simples de matriz de célula única
Normalmente, as fórmulas de matriz de célula única executam primeiro um cálculo multicelular e depois usam uma função como MÉDIA ou SUM para combinar a saída da matriz em um único resultado.
02 de 08Ignorar valores de erro ao encontrar dados
Esta fórmula de matriz usa o MÉDIA, E SEe ISNUMBER funções para encontrar o valor médio para os dados existentes, ignorando os valores de erro, como # DIV / 0! e #NOME?
03 de 08Contagem de Células de Dados
Use o SOMA eE SE funções em uma fórmula de matriz para contar células de dados que atendem a uma das várias condições; esta técnica é diferente de usar o Excel COUNTIFS função, que requer que todas as condições sejam atendidas antes que a célula seja contada.
04 de 08Encontre o maior número positivo ou negativo
Este exemplo combina o MAX função eE SE função em uma fórmula de matriz que encontrará o valor maior ou máximo para um intervalo de dados quando critérios específicos forem atendidos.
05 de 08Encontre o menor número positivo ou negativo
Semelhante ao exemplo acima, este combina o MIN função e se função em uma fórmula de matriz para encontrar o menor ou mínimo valor para um intervalo de dados quando critérios específicos são atendidos.
06 de 08Encontre o valor médio ou mediano
o MEDIANA função no Excel encontra o valor do meio para uma lista de dados. Ao combiná-lo com o E SE função em uma fórmula de matriz, o valor do meio para diferentes grupos de dados relacionados pode ser encontrado.
07 de 08Faça uma fórmula de pesquisa com vários critérios
Essa fórmula de matriz envolve aninhar o PARTIDA e ÍNDICE funções para encontrar informações específicas em um banco de dados.
08 de 08Faça uma fórmula de pesquisa à esquerda
o VLOOKUP função normalmente só procura por dados localizados em colunas à direita, mas combinando-o com o ESCOLHER função, você pode criar uma fórmula de pesquisa à esquerda que pesquisará colunas de dados à esquerda da Lookup_value argumento.
