Fórmulas com matrizes no Excel: um guia completo e exemplos práticos

As Fórmulas com matrizes no Excel São ferramentas que parecem "mágica negra" à primeira vista, mas, quando dominadas, permitem realizar com uma única fórmula o que antes exigia dezenas ou centenas de células auxiliares.

Embora inicialmente possam inspirar um pouco de respeito, os matrizes e fórmulas matriciais São um dos recursos mais poderosos do Excel para análise de dados, modelagem, finanças, engenharia ou simplesmente para tornar suas planilhas muito mais organizadas e rápidas.

O que é uma matriz e o que é uma fórmula matricial no Excel?

No Excel, uma matriz é simplesmente uma coleção de valores que são tratados como um conjunto: podem estar em uma única linha, em uma única coluna ou formar um bloco de várias linhas e colunas.

Por exemplo, uma matriz muito típica pode conter o meses do ano Escritos um abaixo do outro ou um ao lado do outro, o Excel trabalharia com esse grupo como se fosse um único objeto de dados.

Uma fórmula matricial Trata-se de uma fórmula que, em vez de operar sobre um único valor, funciona simultaneamente com uma matriz completa de elementos: ela pode realizar vários cálculos ao mesmo tempo e retornar um único resultado ou uma matriz de resultados.

A ideia principal é que uma fórmula matricial torna o Excel mais eficiente. processar muitos itens em massaavaliando internamente todos os valores e, se necessário, retornando vários resultados simultaneamente em células diferentes.

Por exemplo, imagine que você tenha o número de unidades vendidas na coluna B e o preço de cada unidade na coluna C. Com uma fórmula matricial como esta: =SOMA(B2:B11*C2:C11)O Excel multiplica cada linha (unidades por preço) e depois soma todos esses produtos, sem a necessidade de colunas intermediárias.

Como inserir e reconhecer uma fórmula matricial clássica

As fórmulas matriciais tradicionais no Excel são inseridas usando uma combinação de teclas especial: Ctrl + Shift + Enter (Ctrl + Shift + Enter). Simplesmente pressionar Enter não é suficiente.

Ao escrever uma fórmula matricial e confirmá-la com essa combinação, o Excel exibe a fórmula na barra de fórmulas, cercada por um símbolo de interrogação. chaves { }Essas chaves não são digitadas manualmente: o Excel as adiciona automaticamente quando detecta que se trata de uma fórmula matricial.

Se você tentar digitar as chaves manualmente, o Excel não as reconhecerá como tal. fórmula de matrizMas, como se trata de uma fórmula normal, é obrigatório usar a combinação de teclas para que funcione.

Cada vez que você edita uma fórmula matricial, as chaves desaparecem temporariamente: você terá que pressionar [a tecla apropriada] novamente. Ctrl + Shift + Enter Após a edição, a fórmula deixará de ser baseada em matriz e será calculada apenas no primeiro elemento do intervalo.

E um detalhe importante: se você se esquecer de usar a combinação e apenas pressionar Enter, a fórmula se comportará como uma fórmula padrãoSelecionar apenas o primeiro valor de cada intervalo pode levar a resultados errôneos sem que você perceba.

Tipos de fórmulas matriciais: com um resultado ou com múltiplos resultados.

Podemos distinguir dois tipos principais de Fórmulas com matrizes no Excel: aquelas que retornam um único valor e aquelas que retornam um conjunto de resultados distribuídos por várias células.

No primeiro caso, a fórmula recebe uma matriz de dados, realiza os cálculos e retorna um resultado. apenas um resultado em uma célula (por exemplo, uma soma, uma média, uma contagem, um mínimo ou um máximo).

No segundo tipo, a própria fórmula gera um matriz de saída que ocupa duas ou mais células. Nesse caso, todos os resultados fazem parte de uma única fórmula matricial que "existe" em várias células ao mesmo tempo.

Recursos como SUMA, MÉDIA, MAX o MIN (e suas variantes, MINIFS e MAXIFS) pode funcionar com matrizes se elas forem inseridas como fórmulas matriciais em uma única célula, enquanto outras, como TRANSPONADOR, TREND o FREQUÊNCIAEles são projetados para retornar matrizes de várias células.

O mais impressionante em tudo isso é que uma única fórmula pode substituir muitas colunas auxiliares, mantendo sua lâmina mais limpa e leve e com menor risco de erros de cópia ou atualização.

Exemplos avançados de fórmulas matriciais clássicas

As fórmulas matriciais permitem resolver situações que seriam complicadas ou simplesmente impossíveis com fórmulas padrão. Vários exemplos são listados abaixo. exemplos Existem opções típicas e avançadas baseadas em intervalos, com nomes como Dados, Vendas, MeusDados ou SeusDados.

Soma dos intervalos que contêm erros

Quando um intervalo inclui erros como #N / DUma soma normal usando a função SOMA falhará. Com uma fórmula matricial, você pode ignorar esses erros e somar apenas os valores válidos:

=SOMA(SE(ÉERRO(Dados),"",Dados))

A função EMISSOR ERRO Detecta células com erros dentro do intervalo de dados e da função. SI Crie um novo array onde, em vez de erros, você coloque strings vazias. ""e onde as células sem erros mantêm seu valor original.

Nessa matriz limpa, a função SUMA Ele calcula o total ignorando elementos vazios, então você pode obter a soma mesmo que o intervalo original tenha células defeituosas.

Conte quantos erros existem em um intervalo.

Se o que você precisa é conte os erros Em vez de somá-los, você pode usar uma variação semelhante:

=SOMA(SE(ÉERRO(Dados),1,0))

Esta fórmula constrói uma matriz na qual cada célula com um erro é transformada em uma 1 e cada célula sem erro em um 0de forma que a soma de todos esses 1s e 0s dê o número total de erros.

A fórmula pode simplificar Removendo o terceiro argumento de IF, já que quando a condição é falsa ela retorna FALSE, e SUM interpreta FALSE como 0:

=SOMA(SE(ÉERRO(Dados),1))

E ainda é possível encurtá-lo ainda mais multiplicando diretamente o resultado booleano por 1, aproveitando o fato de que VERDADEIRO*1=1 y FALSO*1=0:

=SOMA(SE(ÉERRO(Dados)*1))

Adicionando valores que atendem às condições (AND e OR “manualmente”)

Com fórmulas matriciais, você pode somar apenas os valores que atendem a determinadas condições sem precisar usar a função SOMASE o tempo todo. Por exemplo, para somar apenas os valores positivo da gama de vendas:

=SOMA(SE(Vendas>0;Vendas))

Aqui está a função SI Ela gera uma matriz onde as células com valor maior que 0 mantêm seu valor e as demais se tornam FALSE; a função SUMA Ignore os valores FALSOS e some apenas os números positivos.

Você também pode combinar várias condições usando multiplicação (equivalente a um E lógico) ou somas (equivalentes a um Ou lógicoPor exemplo, para somar valores maiores que 0 e menores ou iguais a 5:

=SOMA((Vendas>0)*(Vendas<=5)*(Vendas))

Neste caso, as expressões lógicas retornam matrizes VERDADEIRO/FALSO, que, quando multiplicadas, tornam-se 1 ou 0 e atuam como filtros com base nos valores de vendas.

Se o que você precisa é de um comportamento do tipo O, você pode usar o soma de condições lógicas dentro do SI, como nesta fórmula que soma valores menores que 5 ou maiores que 15:

=SOMA(SE((Vendas<5)+(Vendas>15);Vendas))

As funções Y y O Elas retornam um único valor VERDADEIRO ou FALSO, portanto não são usadas diretamente com múltiplos arrays; a solução é emulá-las com multiplicações e adições, como nos exemplos anteriores.

Calcule a média excluindo os zeros.

Se você deseja obter um média sem considerar os zerosVocê pode combinar a função MÉDIA com uma condição de matriz no intervalo de Vendas:

=MÉDIA(SE(Vendas<>0;Vendas))

A matriz SI resultante contém apenas os valores diferentes de 0, que são os que serão utilizados na análise final. MÉDIA Para calcular a média.

Contando as diferenças entre dois intervalos

Suponha que você tenha dois intervalos de mesmo tamanho e formato, chamados MyData e YourData, e queira saber Em quantas células eles diferem?Uma fórmula matricial resolve isso da seguinte maneira:

=SOMA(SE(MeusDados=SeusDados,0,1))

A função SE gera uma matriz onde cada correspondência se torna 0 e cada não correspondência se torna 1. Somando a matriz, você obtém o resultado. contagem de células diferentes.

Existe também uma versão mais compacta aqui que usa diretamente a comparação desigual (<>) multiplicado por 1:

=SOMA(1*(MeusDados<>SeusDados))

Mais uma vez, o truque que VERDADEIRO é igual a 1 e FALSO é igual a 0 quando multiplicado por um número.

Localize o valor máximo e sua posição dentro de um intervalo.

Com fórmulas matriciais, você pode não apenas descobrir o que... valor máximo de um conjunto, mas também sua posição exata na folha; você também pode usar o Função CLASSIFICAÇÃO Para solicitar e localizar posições.

Para encontrar o número da linha Para encontrar o valor máximo dentro de um intervalo de uma coluna chamada "Dados", você pode usar:

=MIN(SE(Dados=MÁXIMO(Dados),LIN(Dados),»»))

Esta fórmula cria uma matriz onde as células que contêm o valor máximo armazenam o número da linha correspondente, e as restantes ficam vazias. A função MIN Encontre o menor número nessa matriz que coincide com a primeira linha onde aparece o valor máximo.

Se o que você quer é obter o referência da célula Para obter o valor máximo, você pode envolver o cálculo anterior com ENDEREÇO ​​e COLUNA:

=ENDEREÇO(MÍNIMO(SE(Dados=MÁXIMO(Dados),LIN(Dados),"")),COLUNA(Dados))

Crie fórmulas matriciais com várias células passo a passo.

Em um livro de amostras com um mesa dos vendedoresOs tipos de veículos, as unidades vendidas e os preços podem ilustrar como funciona uma fórmula matricial que ocupa várias células simultaneamente.

Imagine que você copiou uma tabela a partir da célula A1 com estes campos: Vendedor, Tipo de Veículo, Quantidade Vendida, Preço Unitário e Total de VendasE na coluna E você deseja calcular as vendas de cada linha usando uma fórmula matricial.

No intervalo C2:C11 você tem as quantidades vendidas e em D2:D11 os preços; o objetivo é preencher E2:E11 com os valores. produto de cada linha Sem precisar escrever as fórmulas uma a uma.

Para fazer isso como uma fórmula matricial em várias células, primeiro selecione todo o intervalo. E2: E11Digite na barra de fórmulas:

=C2:C11*D2:D11

e confirme com Ctrl + Shift + EnterO Excel preencherá todas as células de E2 a E11 de uma só vez com o resultado correspondente a cada linha.

Fórmula de matriz de célula única no mesmo exemplo

Partindo dessa mesma tabela, é possível obter o vendas totais usando uma única fórmula matricial em uma única célula, por exemplo B13.

Em vez de escrever fórmulas linha por linha, basta inserir o seguinte em B13:

=SOMA(C2:C11*D2:D11)

e confirme com Ctrl + Shift + EnterO Excel realiza internamente a multiplicação de cada par de células Cx*Dx e, em seguida, soma todos esses produtos para obter o total.

Como depurar e entender uma fórmula matricial complexa

Quando uma fórmula é longa e um tanto "enigmática", é essencial ser capaz de veja o que cada parte está calculandoO Excel permite avaliar partes de uma fórmula usando a tecla F9.

O truque é selecionar uma parte específica dentro da barra de fórmulas (por exemplo, apenas B2:B11*C2:C11), e então pressione F9 para que o Excel substitua temporariamente esse fragmento pelo resultado intermediário.

Ao fazer isso em uma fórmula matricial, você verá o matriz resultante, com todos os seus elementos, o que ajuda muito a compreender o comportamento da fórmula e a localizar possíveis erros.

Após inspecionar a peça escolhida, você pode pressionar Esc Para sair sem salvar as alterações, ou Ctrl + Z se você confirmou acidentalmente e deseja desfazer a avaliação.

Matrizes constantes no Excel: como criá-las e usá-las

Além de usar intervalos de células, o Excel permite que você trabalhe com constantes da matriz, que são conjuntos de valores fixos escritos diretamente dentro de uma fórmula e que não se alteram quando a fórmula é copiada ou movida.

Uma constante de matriz pode conter números, textos, valores lógicos (VERDADEIRO/FALSO) ou errosmas não pode incluir referências de células, nomes definidos, datas, funções ou outras matrizes.

Existem constantes unidimensionais horizontais (uma única linha), constantes unidimensionais verticais (uma única coluna e bidimensional (um bloco de linhas e colunas), e distinguem-se pelos separadores utilizados entre os elementos.

Em contextos regionais espanhóis, as matrizes verticais são geralmente separadas por ponto e vírgula (;), enquanto que em matrizes horizontais outro separador pode ser usado (em algumas configurações, a barra invertida) ou a vírgula, dependendo da configuração do sistema.

Por exemplo, um matriz vertical Considerando os meses do ano, isso poderia ser expresso da seguinte forma:

={"Janeiro";"Fevereiro";"Março";"Abril";"Maio";"Junho";"Julho";"Agosto";"Setembro";"Outubro";"Novembro";"Dezembro"}

Atribuir um nome a uma constante de matriz

Para tornar o uso de uma constante grande mais gerenciável, você pode dê-lhe um nome Com o Gerenciador de Nomes do Excel, você pode reutilizá-lo sem precisar digitá-lo novamente.

O processo é simples: você vai até a aba Fórmulas e usa a opção para Nome definido ou Nome atribuídoVocê escreve o nome desejado e, na caixa "Refere-se a", insere diretamente a constante da matriz.

Por exemplo, você poderia criar um nome chamado Meses que apontar para a constante:

={"Janeiro"\"Fevereiro"\"Março"\"Abril"\"Maio"\"Junho"\"Julho"\"Agosto"\"Setembro"\"Outubro"\"Novembro"\"Dezembro"}

Em seguida, basta selecionar tantas células quantos forem os elementos da matriz e digitar o nome. =Meses e confirme como uma fórmula matricial para que todos os valores distribuídos pela planilha apareçam.

Se a constante estiver causando problemas, é uma boa ideia verificar. os separadores usados e que um intervalo apropriado ao tamanho da matriz tenha sido selecionado antes de inserir a fórmula com Ctrl + Shift + Enter.

Exemplos de utilização de constantes matriciais

As constantes permitem criar fórmulas poderosas em um espaço muito pequeno. Por exemplo, para adicionar o três valores mais altos em uma faixa Você pode usar uma combinação com LARGE e uma constante que define as ordens desejadas.

Da mesma forma, é possível somar os N menores valores com K.ESIMO.MENORBasta alterar a função, mas manter a matriz de posições que você deseja somar.

Outro caso típico é contar quantas vezes um avaliador (por exemplo, Pedro) atribuiu uma pontuação com vários valores específicos sem ter que repetir os critérios da função CONT.SES várias vezes.

Num gama de avaliações Você pode usar uma fórmula como esta:

=SUMA(CONTAR.SI.CONJUNTO(A2:A28;»Pedro»;C2:C28;{3\4\5}))

Aqui, a constante {3\4\5} Ela reúne as pontuações aceitas (3, 4 e 5) e torna a fórmula mais compacta e fácil de manter, embora você possa expandi-la com mais valores, se necessário, sempre respeitando o limite máximo de caracteres de uma fórmula.

Fórmulas matriciais dinâmicas no Excel 365 e 2021

Com as versões modernas do Excel (Microsoft 365 e Excel 2021) uma mudança muito importante foi introduzida: o fórmulas matriciais dinâmicaso que elimina a necessidade de usar Ctrl + Shift + Enter na maioria dos casos.

Essas novas fórmulas funcionam nativamente com intervalos e matrizes e possuem a capacidade de "transbordar" automaticamente para as células adjacentes, ocupando quantas linhas e colunas forem necessárias para exibir todos os resultados.

A principal diferença é que, enquanto no Excel você digita a fórmula em uma célula e pressiona Enter normalmente, ele preenche o intervalo de saída necessário e o marca com uma borda especial indicando que se trata de uma célula. intervalo de transbordamento.

Além disso, surgiram novas funções especificamente projetadas para trabalhar com matrizes dinâmicas, como FILTRO, PEDIDO, SÓ, SEQUÊNCIA, ORDENAR POR o MATRIZ ALEATÓRIA, Entre outros.

Essas funções permitem filtrar, classificar, gerar listas sequenciais ou números aleatórios, e retorna conjuntos de resultados sem a necessidade de definir o tamanho do intervalo de destino antecipadamente.

Principais diferenças entre fórmulas matriciais clássicas e dinâmicas

As fórmulas matriciais clássicas exigem Ctrl + Shift + EnterPodem ser um pouco mais difíceis de ler e, em muitos casos, têm capacidade limitada para produzir resultados automaticamente, ao contrário de recursos modernos como... PROCV e PROCVX.

As fórmulas matriciais dinâmicas são escritas como fórmulas normaisA confirmação é feita simplesmente pressionando Enter, e os resultados são exibidos automaticamente, sem a necessidade de selecionar intervalos anteriores ou usar fórmulas matriciais herdadas.

Outra diferença importante é que as funções dinâmicas retornar matrizes explicitamente e são esperadas como tal; se um livro antigo usasse uma função que retornasse uma matriz para várias células, o Excel poderia aplicar uma interseção implícita silenciosa.

Com matrizes dinâmicas, o Excel marca esses casos antigos com o operador. @, o que indica onde essa intersecção implícita estava ocorrendo, a fim de preservar o comportamento anterior e evitar resultados inesperados.

Vale ressaltar também que as fórmulas matriciais dinâmicas estão disponíveis apenas em Excel 365 e Excel 2021Em versões anteriores, elas não funcionam e podem aparecer como fórmulas matriciais legadas se essas pastas de trabalho forem abertas em computadores sem suporte para matrizes dinâmicas.

Configurando e utilizando fórmulas matriciais dinâmicas

Para usar uma fórmula dinâmica, basta escolher a célula inicialDigite a fórmula com uma função como FILTRAR ou CLASSIFICAR e pressione Enter. O Excel irá automaticamente distribuir os resultados para baixo e para a direita.

É importante garantir que haja espaço livre ao redor a partir da célula inicial, porque se as células onde a matriz deve ser inserida já contiverem dados, o Excel exibirá um erro de estouro (#OVERFLOW ou similar).

Após a criação da fórmula, o intervalo de estouro entra em vigor. como um blocoSe você modificar a fórmula na célula principal, todos os resultados serão atualizados; se quiser apagar tudo, basta remover a fórmula dessa célula.

Você também pode consultar o intervalo de transbordamento de outras fórmulas que usam o operador de estouro (por exemplo, =SOMA(F2#)), de forma que, se aumentar ou diminuir de tamanho, as fórmulas que o utilizam se adaptarão automaticamente.

Em ambientes de programaçãobibliotecas como Aspose.Cells Elas permitem definir e recalcular fórmulas matriciais dinâmicas por meio de código, usando métodos específicos para atribuí-las a uma célula e atualizá-las antes de executar o cálculo geral da fórmula.

Aplicações avançadas de matrizes: álgebra linear e finanças

Trabalhar com matrizes no Excel não se limita a somar intervalos ou filtrar valores: também pode ser usado para problemas que envolvem... álgebra Linear e otimização, como inversão de matrizes, resolução de sistemas de equações ou construção de carteiras de investimento.

Uma matriz quadrada A pode ser invertida no Excel usando a função MINVERSA, o que requer uma fórmula matricial (ou dinâmica, dependendo da versão) em um intervalo do mesmo tamanho que a matriz original.

Para obter a inversa de uma matriz 3×3 localizada em B3:D5, você selecionaria um bloco 3×3 vazio e escreveria =MINVERSO(B3:D5) e você a confirmaria como uma fórmula matricial, obtendo assim a matriz A.^-1.

Se você multiplicar a matriz original por sua inversa usando MMULT em uma faixa adequada você obterá um matriz de identidade, análogo a multiplicar um número pelo seu inverso, o que sempre resulta em 1.

De forma semelhante, você pode configurar um sistema de equações lineares na forma matricial, com A como a matriz de coeficientes, K como o vetor de incógnitas e P como o vetor de termos independentes, e resolvê-lo usando a relação. K = A^-1 ·P usando MINVERSA e MMULT.

Em finanças, essa abordagem matricial é aplicada, por exemplo, a problemas de otimização de portfólio, onde a matriz de covariância σ é usada_ij entre títulos, os retornos esperados μ_j e restrições de rentabilidade para encontrar a combinação de ativos de menor risco para uma meta de rentabilidade específica.

Partindo da função de Lagrange e da derivação das condições de primeira ordem, chegamos novamente a um sistema matricial do tipo A·X = P, cuja solução X = A^-1·P nos dá o pesos ótimos de cada ativo na carteira.

Em um exemplo com três ações A, B e C, com covariâncias e retornos esperados dados, um vetor de investimento pode ser determinado de forma que a carteira obtenha um retorno esperado de 4% com o variação mínimaresultando em uma combinação que aproveita a diversificação para reduzir o risco em comparação com o investimento em um único título.

Tudo isso é implementado usando as mesmas ferramentas básicas: MINVERSA, MMULT e fórmulas matriciais, reforçando a ideia de que o Excel pode ser uma plataforma muito competente para análise numérica quando se domina o uso de matrizes.

Após abordar tudo, desde os fundamentos das fórmulas matriciais clássicas, passando por constantes matriciais e matrizes dinâmicas, até usos avançados em álgebra linear e finanças, fica bastante claro que Aprenda a manipular matrizes corretamente no Excel. É um investimento que permite trabalhar de forma mais limpa, rápida e com soluções que muitas vezes estão além do alcance das fórmulas convencionais.