As fórmulas de matriz são uma parte crucial do kit de ferramentas que torna o Excel versátil. No entanto, essas expressões podem ser assustadoras para iniciantes.
Embora possam parecer complicados à primeira vista, seus conceitos básicos são fáceis de entender. Aprender a trabalhar com matrizes é útil, pois essas fórmulas simplificam o gerenciamento de dados complexos do Excel.
O que é Uma Fórmula de Matriz no Excel?
Uma matriz é qualquer coleção de células em uma coluna, linha ou combinação reunidas em um grupo.
No Microsoft Excel, o termo fórmula de matriz refere-se a uma família de fórmulas que executam uma ou mais operações em um determinado intervalo de células, todas de uma só vez, em vez de uma célula de cada vez.
As versões anteriores do Excel exigiam que os usuários pressionarem Ctrl + Shift + Enter para criar uma função de matriz, resultando no nome CSE (Ctrl, Shift, Escape), embora esse não seja mais o caso do Excel 365.
As fórmulas de matriz também podem ser a maneira perfeita de extrair amostras de grandes conjuntos de dados. Eles podem usar argumentos que incluem um intervalo de células de uma única coluna, um intervalo de células de uma única linha ou células que abrangem várias linhas e colunas.
Além disso, instruções condicionais podem ser aplicadas ao puxar dados para uma matriz de fórmulas, como apenas puxar números sobre ou abaixo de um valor específico ou puxar cada n-ésimo valor.
Esse controle granular permite que você garanta que está extraindo um subconjunto exato de seus dados, filtrando valores de células indesejados ou coletando uma amostra aleatória de itens para fins de teste.
Como Definir e Criar uma Matriz no Excel
As fórmulas de matriz são, em seu nível mais básico, fáceis de criar. Para um exemplo simples, podemos considerar uma fatura para um pedido de peças do cliente com as seguintes informações de item de linha: a SKU (Unidade de Manutenção de Estoque) do produto, a quantidade comprada, o peso cumulativo dos produtos comprados e o preço por unidade comprada.
Para concluir a fatura, três colunas devem ser adicionadas ao final da tabela inferior, uma para o subtotal das peças, uma para o custo de envio e a terceira para o preço total de cada item de linha.
Você pode usar uma fórmula simples para calcular cada item de linha de forma independente. Mas se a fatura fosse para muitos itens de linha, ela poderia rapidamente ficar muito mais complicada. Então, em vez disso, o "Subtotal" pode ser calculado com uma única fórmula colocada na célula E4:
=B4:B8 * D4:D8
Ambos os números que estamos multiplicando são intervalos de células em vez de células únicas. Em cada linha, a fórmula extrai os valores de célula apropriados das matrizes e coloca o resultado na célula correta sem mais informações do usuário.
Se assumirmos uma taxa de frete fixa de US$1,50 por libra, podemos calcular nossa coluna de envio com uma fórmula semelhante colocada na célula F4:
=C4:C8 * B11
Desta vez, usamos apenas uma matriz no lado esquerdo do operador de multiplicação. Os resultados ainda são inseridos automaticamente, mas cada um é multiplicado em relação a um valor estático desta vez.
Finalmente, podemos usar o mesmo tipo de fórmula que usamos para o Subtotal para obter um total adicionando matrizes de Subtotais e Custos de Envio na célula G4:
=E4:E8 + F4:F8
Mais uma vez, uma fórmula de matriz simples transformou o que de outra forma seriam cinco fórmulas em uma.
Gerenciando Condicionais em Matrizes do Excel
O exemplo anterior usa fórmulas aritméticas básicas em matrizes para produzir os resultados, mas a matemática simples não funcionará para situações mais complexas.
Por exemplo, suponha que a empresa que criou a fatura no exemplo anterior tenha mudado para uma transportadora de carga diferente. Nesse caso, o frete padrão pode cair de preço, mas uma taxa pode ser aplicada para objetos acima de um certo peso.
Para as novas taxas de envio, o frete padrão será de US$1,00 por libra, e o frete de maior peso será de US$1,75 por libra. O transporte de alto peso aplica-se a qualquer coisa acima de sete libras.
Em vez de voltar a calcular cada taxa de frete linha por linha, uma fórmula de matriz pode incluir uma instrução condicional SE para determinar o custo de envio apropriado para cada item de linha:
=SE (C4:C8 < 7, C4:C8*B11, C4:C8*B12)
Com uma mudança rápida para uma função em uma célula e a adição de uma nova taxa de envio em nossa tabela de taxas, agora podemos ver que não apenas toda a coluna de frete é calculada com base no peso, mas os totais também estão automaticamente pegando os novos preços.
A fatura é, portanto, preparada para o futuro, garantindo que qualquer alteração futura no cálculo de custo exigirá a alteração de apenas uma função.
A combinação de condicionais múltiplos ou diferentes pode nos permitir executar várias ações. Essa lógica pode ser usada de várias maneiras na mesma matriz de dados. Por exemplo,
- Uma função SE extra pode ser adicionada para ignorar o envio se o subtotal for superior a um determinado valor.
- Uma tabela de impostos poderia ser adicionada para tributar diferentes categorias de produtos a taxas diferentes.
Não importa o quão complexo seja a fatura, editar uma única célula será tudo o que é necessário para recalcular qualquer parte da conta.
Exemplo: Média de N-ésimos Números
Além de simplificar o cálculo e a atualização de grandes faixas de dados, as fórmulas de matriz podem extrair uma fatia de um conjunto de dados para testar os objetivos.
Como mostra o exemplo a seguir, extrair uma fatia de dados de um grande conjunto de dados para fins de validação é fácil usar fórmulas de matriz:
No exemplo acima, a célula D20 usa uma função simples para calcular a média de cada enésimo resultado do sensor 1. Neste caso, podemos definir a enésima usando o valor na célula D19, dando-nos controle sobre o tamanho da amostra que determina a média:
=MÉDIA(SE(MOD(LINHA(B2:B16)-2,D19)=0,B2:B16,"))
Isso permite que você segmente rápida e facilmente um grande conjunto de dados em subconjuntos.
Conclusão
Esses exemplos simples são um bom ponto de partida para entender a lógica por trás das fórmulas de matriz. As fórmulas de matriz são uma maneira leve e eficiente de fatiar e cortar dados. Domine-os no início de sua jornada de aprendizado do Excel para que você possa fazer cálculos estatísticos avançados com confiança.
Recomendamos que você experimente o PassFab for Excel caso você tenha esqucido a senha do Excel. É uma ferramenta que é confiável para desproteger uma planilha de Excel.