Fórmulas Matriciais no Excel (Valores Únicos em uma Tabela)
Nessa publicação vamos mostrar como utilizar Fórmulas Matriciais! Sei que muita gente nem sabe que existe esse tipo de fórmula no Excel!
Caso prefira esse conteúdo no formato de vídeo-aula, assista aos vídeos abaixo ou acesse o nosso canal do YouTube!
O que você aprende neste vídeo
Resposta rápida: Nesta aula, o instrutor ensina fórmulas matriciais no Excel para contar valores únicos numa tabela. A ideia é dividir 1 pela contagem de cada valor (1/CONT.SE) e somar tudo, numa fórmula só, usando o intervalo como critério. Ele mostra o passo a passo da lógica e finaliza com Ctrl+Shift+Enter, que transforma a fórmula normal em matricial (com as chaves).
Neste vídeo (24 min):
- 1:00 - O objetivo: contar quantos produtos (ou lojas) diferentes há numa coluna, mesmo com valores repetidos.
- 3:00 - A alternativa manual: a ferramenta Remover Duplicatas, mas ela não deixa o número numa célula.
- 5:00 - A lógica: dividir 1 pelo número de vezes que cada valor aparece (
1/CONT.SE) e somar, o resultado é a contagem de únicos. - 12:00 - A fórmula matricial:
=SOMA(1/CONT.SE(intervalo;intervalo)), o critério passa a ser o intervalo inteiro. - 15:00 - Finalizar com Ctrl+Shift+Enter: vira matricial (aparecem as chaves na barra de fórmulas).
Para baixar a planilha utilizada nessa aula clique aqui!
Para receber por e-mail o(s) arquivo(s) utilizados na aula, preencha:
Fórmula matricial no Excel é uma fórmula que faz vários cálculos de uma vez sobre um intervalo (matriz) e pode retornar um ou vários resultados. Antes era confirmada com Ctrl+Shift+Enter; nas versões atuais do Microsoft 365, com arrays dinâmicos, ela é inserida normalmente e o resultado se espalha pelas células. É útil para somar com condições, extrair valores únicos e fazer contagens avançadas.
O que é uma Fórmula Matricial?
Fórmula Matricial é uma maneira do Excel trabalhar com matrizes, ou seja, é possível fazer as operações comuns entre matrizes e até mesmo utilizar fórmulas de uma maneira um pouco diferente, aplicando elas a toda a matriz selecionada.
Uma matriz é como se fosse uma tabela contendo linhas e colunas, é muito utilizada na matemática, principalmente para resolver alguns problemas. Portanto o Excel é capaz de fazer operações entre matrizes para que possa calcular corretamente as operações como as fórmulas que podem ser utilizadas para cada elemento da matriz.
Quando utilizar as Fórmulas Matriciais?
Vamos utilizar a fórmula matricial sempre que seja necessário trabalhar com matrizes principalmente quando precisamos utilizar uma fórmula para analisar um conjunto de dados (que seriam as matrizes), ou seja, uma fórmula SE ao invés de utilizar em cada uma das células da matriz é possível selecionar todo um intervalo para isso.
Neste caso vamos utilizar a fórmula matricial para verificar a quantidade de itens únicos em uma tabela, tanto de produtos quanto de lojas.
Excel ImpressionadorTudo que você precisa saber, do Básico ao Avançado, pra dominar a ferramenta mais importante do Mercado de Trabalho. Aprenda as principais ferramentas, funções e recursos do Excel para deixar qualquer planilha mais chamativa e eficiente.
Começar agora
Como utilizar esse tipo de função (Fórmulas Matriciais)?
Vamos começar com um exemplo mais simples para facilitar a explicação de como podemos obter a quantidade de itens únicos. Note que não será utilizada a ferramenta de Remover Duplicatas, pois isso iria gerar um trabalho extra que seria de copiar a coluna desejada em outro lugar, remover os itens repetidos para que pudesse ser feita a conta de quantos itens únicos existem.
Será utilizada essa tabela simples para exemplificar como será feita a contagem dos itens únicos. Temos algumas maneiras de fazer essa contagem, aqui vamos utilizar a divisão, ou seja, vamos pegar o valor 1 (que corresponde a 100%) e dividir pela quantidade de vezes que cada uma das lojas aparece, portanto se a Loja C aparece duas vezes vamos atribuir ao lado de cada uma o valor de 0,5, pois na soma precisaremos apenas do resultado final, visto que elas são iguais a soma deverá ser igual a 1. Isso quer dizer que se a Loja B aparece 4 vezes a soma de todas as lojas deve ser igual a 1, pois queremos apenas o valor único de cada loja.
Desta forma é possível verificar que o somatório total do valor de cada loja o resultado é igual a 3, que é exatamente a quantidade de lojas únicas dentro da coluna B. Para transformar isso em uma fórmula será necessário contar quantas vezes aquela loja aparece, portanto para esta parte será utilizada a fórmula CONT.SE, desta forma é possível contar quantas vezes cada loja aparece. Então ao dividir 1 pela soma teremos um valor que ao somado resultará em 1, ou seja, para que cada loja seja contada apenas como 1.
Ao escrever a fórmula o resultado obtido deverá ser igual ao da coluna C, portanto basta copiar a fórmula para todas as outras células.
Feito isso é possível observar o mesmo resultado, e, portanto, a soma também é a mesma. Com as fórmulas matriciais será possível resumir todas essas contas em uma única célula, ou seja, todos esses números de cada loja e sua soma serão comprimidos em uma célula apenas.
O primeiro passo é escrever a fórmula desta maneira, vamos somar essa fórmula dentro do intervalo selecionado, no entanto o intervalo de critérios será o mesmo intervalo utilizado para o critério. Isso acontece porque na fórmula matricial ela vai fazer a operação célula por célula, ou seja, quando estiver na célula B3 ela vai analisar quantas vezes a Loja C aparece em todo o intervalo, e assim por diante. É como se o Excel fizesse essa tabela que temos na célula D internamente e vai calculando o valor de cada célula, e como temos a função SOMA quer dizer que estamos somando todos esses valores internos calculados pela fórmula matricial.
Então a fórmula matricial vai fazer a conta em todo o intervalo selecionado, um a um e vai guardar esse valor internamente.
IMPORTANTE: Se o usuário pressionar ENTER como faz normalmente o resultado obtido será 0,5 que é somente o valor do primeiro cálculo, ou seja, da primeira célula. Para que a fórmula matricial funcione o usuário deverá pressionar CTRL + SHIFT + ENTER quando estiver ainda na parte de edição da célula. Feito isso o resultado será o correto e será apresentado em apenas uma célula.
Para verificar se a fórmula matricial está aplicada basta clicar na célula uma única vez e observar na barra de fórmulas que a fórmula da célula está entre chaves {}.
É importante ressaltar que não adianta o usuário colocar as chaves manualmente quando estiver escrevendo que não irá funcionar. A fórmula matricial só funcionará se o usuário pressionar CTRL + SHIFT + ENTER ao invés de apenas ENTER para concluir a fórmula.
Agora que aprendemos a aplicar as fórmulas matriciais em um contexto menor para facilitar e até mesmo contar para conferir se o resultado está correto, vamos utilizar essa fórmula matricial na aba principal que possui uma quantidade muito maior de itens. Vamos utilizar para descobrir tanto a quantidade de códigos únicos quanto a quantidade de lojas únicas.
A fórmula utilizada será exatamente a mesma, a única diferença é a quantidade de dados que agora será bem maior, ou seja, o intervalo selecionado será maior.
Desta forma o usuário não precisa fazer ações a mais como utilizar a ferramenta de remover duplicatas, criar colunas auxiliares entre outras atividades para efetuar essa soma de valores únicos.
Essa é uma forma simples, rápida e efetiva de descobrir quantos valores únicos temos dentro de uma tabela, seja ela pequena como o primeiro exemplo, ou seja, ela maior como o segundo exemplo que possui 1000 linhas. Tudo isso utilizando a fórmula matricial que permite essa flexibilidade e permite que o usuário economize tempo e fórmulas.
Apostila Básica de Excel
Essa é uma apostila básica de Excel para que você saia do zero de forma 100% gratuita!
Perguntas frequentes sobre fórmulas matriciais no Excel
1. O que é uma fórmula matricial no Excel?
É uma fórmula que opera sobre conjuntos de valores (matrizes) ao mesmo tempo, em vez de uma célula por vez. Ela permite cálculos avançados, como somar produtos de intervalos ou retornar listas de resultados, em uma única fórmula.
2. Como inserir uma fórmula matricial?
No Excel mais antigo, você digita a fórmula e confirma com Ctrl+Shift+Enter, e ela aparece entre chaves {}. No Microsoft 365, com arrays dinâmicos, basta pressionar Enter: o resultado se espalha automaticamente pelas células vizinhas.
3. Quando usar fórmulas matriciais?
Use quando precisar de cálculos que combinam vários intervalos ou condições — como somar valores que atendem a vários critérios, extrair itens únicos ou fazer contagens complexas — sem criar colunas auxiliares.
4. Fórmula matricial e array dinâmico são a mesma coisa?
São relacionadas. Os arrays dinâmicos são a evolução das fórmulas matriciais no Microsoft 365: trazem funções como FILTRO, ÚNICO e SEQUÊNCIA e dispensam o Ctrl+Shift+Enter, tornando o cálculo com matrizes muito mais simples.
Hashtag Treinamentos
Para acessar outras publicações de Excel Avançado, clique aqui!
