Funções básicas do Excel funcionam bem para cálculos simples, mas rapidamente se tornam complicadas ao lidar com análises de dados complexas. Você acaba com fórmulas aninhadas difíceis de ler, várias colunas auxiliares que desorganizam sua planilha e fórmulas que podem quebrar quando seus dados são alterados. É aí que entram as fórmulas matriciais no Excel.
Fórmulas de matriz permitem que você execute cálculos em intervalos inteiros de dados em uma única fórmula. Portanto, você pode Realize pesquisas extremamente rápidas, filtre e classifique com uma expressão poderosa, em vez de escrever fórmulas separadas para cada linha ou coluna. Não é novidade no Excel, mas algumas pessoas continuam usando métodos antigos de fazer as coisas quando essas funções podem tornar seu trabalho mais simples e eficiente.
Links Rápidos
5. XLOOKUP
Supera PROCV todas as vezes.
XLOOKUP é a função de pesquisa que deveria existir desde o início. Ao contrário do VLOOKUP, que força a contagem de colunas e pesquisa apenas à direita, o XLOOKUP funciona em qualquer direção e usa referências de colunas reais. Ele tem a seguinte sintaxe:
=XLOOKUP(valor_procurado, array_procurado, array_retornado, [if_not_found], [modo_correspondência], [modo_pesquisa])
Veja o que cada parâmetro significa:
- valor_pesquisa: O valor específico que você está procurando. Pode ser um número de peça, código do produto ou qualquer identificador no seu conjunto de dados.
- lookup_array: O intervalo em que o Excel pesquisa lookup_value Seu. Geralmente, é uma única coluna ou linha contendo seus critérios de pesquisa.
- return_array: O intervalo que contém os valores que você deseja recuperar. Pode ser uma única coluna, várias colunas ou até mesmo uma seção inteira de uma tabela.
- if_not_found (opcional): Texto ou valor personalizado a ser exibido quando nenhuma correspondência for encontrada. Elimina os irritantes erros #N/D e permite exibir "Não Encontrado" ou "Verificar Número da Peça".
- match_mode (opcional): Controla o tipo de correspondência. Use 0 para correspondência exata (padrão), -1 para a próxima correspondência exata ou menor, 1 para a próxima correspondência exata ou maior e 2 para correspondência curinga.
- modo_de_pesquisa (opcional): Especifica a direção da pesquisa. Use 1 para uma pesquisa do primeiro ao último (padrão), -1 para uma pesquisa do último ao primeiro e 2 para uma pesquisa binária em dados ordenados.
Vejamos o exemplo de uma planilha de inventário mecânico. A fórmula a seguir busca o número da peça "BRG-002" em um intervalo de IDs de peças e retorna os dados correspondentes. Se a peça não estiver presente, a mensagem "Peça Não Encontrada" será exibida em vez de um erro.
=XLOOKUP("BRG-002", A:A, A:H, "Peça não encontrada")
XLOOKUP permite que você extraia dados de diferentes colunas sem os cálculos de coluna complicados encontrados em VLOOKUP, tornando-o um dos mais importantes Funções do Excel para encontrar dados rapidamente.
4. SUMPRODUCT
Estação de geração de energia para cálculos condicionais
SUMPRODUCT não apenas soma números, mas também multiplica matrizes e soma os resultados. Isso o torna útil para cálculos condicionais complexos que exigem múltiplas colunas auxiliares.
Possui a seguinte fórmula:
=SOMAPRODUTO(matriz1, [matriz2], [matriz3], ...)
aqui, array1 É o primeiro intervalo de valores a ser multiplicado – geralmente sua coluna de dados primária, como quantidades ou custos. array2 É um segundo intervalo opcional para multiplicação, que geralmente contém critérios ou lógica condicional usando operadores de comparação.
Eles se tornam mais úteis quando usamos operadores lógicos dentro de matrizes. Por exemplo, quando digitamos condições como (fornecedor="Siemens"), o Excel converte os resultados VERDADEIRO/FALSO para 1/0, permitindo cálculos.
Por exemplo, a fórmula a seguir calcula o valor total do estoque para peças fornecidas apenas pela Siemens. A fórmula multiplica as quantidades pelos custos unitários, mas apenas para as linhas em que o fornecedor atende aos critérios.
=SUMPRODUCT(D2:D100*H2:H100*(G2:G100="Siemens"))
Da mesma forma, a fórmula a seguir encontra o custo total de um estoque de rolamentos em bom estado:
=SUMPRODUCT((C2:C100="Bearings")*(D2:D100>=15)*H2:H100)
Duas condições se aplicam simultaneamente: a categoria deve ser “Rolamentos” e os níveis de estoque devem ser de 15 unidades ou mais, o que nos ajuda a identificar categorias de rolamentos que têm cobertura de estoque suficiente.
Ao contrário das funções SUM tradicionais com múltiplos critérios, SUMPRODUCT não requer estruturas aninhadas complexas porque lida com múltiplas condições em uma única fórmula legível. Funções SUM no Excel, Assim como SOMASE e SOMASES, elas são excelentes para somas condicionais simples, mas a função SOMARPRODUTO se destaca quando você precisa multiplicar valores antes de somar ou lidar com operações lógicas mais complexas.
3. FILTRO
Simplifica a extração dinâmica de dados
FILTER extrai linhas do seu conjunto de dados com base nas condições especificadas. Diferentemente da filtragem manual, esta função gera resultados dinâmicos que são atualizados automaticamente quando os dados de origem são alterados. A sintaxe FILTER é a seguinte:
=FILTER(matriz, incluir, [se_vazio])
Veja o que cada entrada controla:
- matriz (intervalo): A gama completa de dados que você deseja filtrar. Isso inclui todas as colunas que você deseja nos seus resultados, não apenas a coluna de critérios.
- incluem: Condição lógica que especifica quais linhas retornar – usa operadores de comparação para criar matrizes TRUE/FALSE para cada linha.
- if_empty (opcional): Exibe uma mensagem personalizada quando nenhuma linha atende aos seus critérios. Evita erros #CALC! e exibe um texto significativo, como "Nenhum resultado correspondente encontrado".
A função funciona avaliando sua condição em relação a cada linha do intervalo. Quando a condição retorna VERDADEIRO, toda a linha aparece nos resultados filtrados. Veja um exemplo de uma planilha de inventário mecânico:
=FILTER(A2:H101, (C2:C101="Bearings")*(G2:G101="Timken"))
Esta fórmula extrai todas as linhas onde o recurso é "Timken" e a categoria é "Rolamentos". O asterisco (*) cria uma condição AND multiplicando as matrizes lógicas.
Ao adicionar novos dados ao seu intervalo de origem, Usando a função FILTRO no Excel Faz mais sentido do que a classificação manual e tabelas temporárias, pois os resultados filtrados são atualizados automaticamente. Isso o torna útil para a criação de painéis e relatórios em tempo real.
2. UNIQUE
Extraia valores únicos sem duplicatas
UNIQUE extrai valores únicos do seu intervalo de dados e evita duplicatas automaticamente. Esta função é importante se você deseja criar listas suspensas, analisar categorias de dados e gerar relatórios resumidos. A fórmula é:
=UNIQUE(array, [por_coluna], [exatamente_uma_vez])
Veja como cada entrada funciona:
- matriz (intervalo): O intervalo que contém os dados dos quais você deseja remover duplicatas — pode ser uma única coluna, várias colunas ou uma seção inteira da tabela.
- por_col (opcional): FALSE compara linhas para determinar a exclusividade (padrão), enquanto TRUE compara colunas. No entanto, a maioria dos cenários usa a comparação de linhas padrão.
- exatamente_uma vez (opcional): FALSE retorna todos os valores exclusivos, incluindo aqueles que ocorrem várias vezes (padrão), e TRUE retorna apenas valores que ocorrem exatamente uma vez no conjunto de dados.
A função UNIQUE avalia cada linha ou valor em sua matriz e retorna apenas a primeira ocorrência de cada elemento único. A ordem corresponde à sequência de dados original. Veja um exemplo:
=UNIQUE(G2:G22)
Esta fórmula extrai todos os nomes exclusivos de fornecedores da coluna G "Fornecedor" e cria uma lista limpa e duplicada. Eu a utilizo para criar listas suspensas de fornecedores ou relatórios resumidos.
Você também pode usá-lo em toda a tabela, como mostrado abaixo:
=UNIQUE(A2:F100)
Retorna combinações únicas em todas as colunas (A a F), exibindo registros de inventário distintos. Se duas partes tiverem valores idênticos em cada coluna, apenas uma aparecerá nos resultados.
Ao trabalhar com grandes conjuntos de dados, o UNIQUE elimina o processo tedioso de remover manualmente duplicatas. Os resultados dinâmicos são atualizados conforme novos dados chegam e, como o UNIQUE cria matrizes de transbordamento, essa abordagem elimina o incômodo de redimensionar tabelas, escalando-as automaticamente para acomodar todos os valores únicos. Eu o utilizo para manter listas de referência limpas e criar intervalos de validação de dados confiáveis.
1. CLASSIFICAR e CLASSIFICAR POR
Organize seus dados sem comprometer o original
As funções SORT e SORTBY organizam os dados dinamicamente, mantendo a fonte intacta. SORT realiza a classificação básica por posição na coluna, enquanto SORTBY classifica com base em valores em colunas diferentes, oferecendo mais flexibilidade para classificações complexas.
SORT usa esta estrutura:
=SORT(matriz, [índice_classificação], [ordem_classificação], [por_col])
Veja o que cada parâmetro controla:
- matriz: O intervalo de dados que você deseja classificar inclui todas as colunas que devem aparecer nos resultados classificados.
- sort_index (opcional): O número da coluna dentro da matriz a ser classificada. Use 1 para a primeira coluna, 2 para a segunda e assim por diante (o padrão é 1).
- sort_order (opcional): Use 1 para ordem crescente (padrão) e -1 para ordem decrescente.
- por_col (opcional): FALSE para classificar por linhas (padrão), TRUE para classificar por colunas — a maioria dos cenários usa classificação por linhas.
A função SORTBY tem o seguinte formato:
=SORTBY(array, by_array1, [sort_order1], [by_array2], [sort_order2], ...)
Suas transações incluem:
- matriz: O intervalo de dados a serem classificados — semelhante à função SORT, ela contém todas as colunas que você deseja nos resultados.
- por_array1: O intervalo que contém os valores que determinam a ordem de classificação pode ser qualquer coluna, mesmo fora do intervalo da matriz principal.
- sort_order1 (opcional): 1 para ordem crescente (padrão), -1 para ordem decrescente.
- por_matriz2, ordem_de_classificação2 (opcional): Critérios de classificação adicionais para classificação multinível.
Observando um exemplo de uma planilha de inventário mecânico, essas funções lidam com cenários de classificação do mundo real:
=CLASSIFICAR(A2:H22, 4, -1)
Isso classifica todo o estoque por níveis de estoque em ordem decrescente, com os itens com maior estoque exibidos primeiro. A fórmula classifica pela coluna 4 (níveis de estoque), preservando todas as relações entre as linhas.
Estou usando a função SORTBY. Em vez de CLASSIFICAR, você pode usá-lo para obter melhor controle sobre os critérios de classificação e os vários níveis de classificação. Por exemplo, a fórmula a seguir classifica primeiro em ordem alfabética por categoria e, em seguida, por níveis de estoque, do maior para o menor dentro de cada categoria.
=Classificarpor(A2:H22, C2:C22, 1, D2:D22, -1)
Planilhas organizadas, resultados mais inteligentes
Fórmulas de matriz eliminam a desordem de colunas auxiliares e funções aninhadas que dificultam a manutenção de planilhas. Você obtém fórmulas únicas que lidam com múltiplas operações, tornando as pastas de trabalho mais organizadas e profissionais.
Uma vantagem notável são as funções dinâmicas, em que os resultados são atualizados automaticamente quando os dados de origem são alterados. Isso elimina atualizações manuais ou sequências de fórmulas quebradas, tornando suas planilhas mais confiáveis para análises contínuas.
A biblioteca de funções de matriz do Excel continua a expandir-se para além destas ferramentas básicas. Quando preciso combinar dados de várias fontes, utilizo as funções VSTACK e HSTACK para combinar intervalos. Juntas, estas funções criam fluxos de trabalho de processamento de dados poderosos que seriam impossíveis com fórmulas tradicionais.
