Tudo o que você precisa saber sobre constantes de matriz no Excel

As constantes de matriz no Microsoft Excel são ferramentas poderosas para realizar vários cálculos com uma única fórmula. O uso de constantes de matriz em suas planilhas do Excel evita a necessidade de fórmulas longas ou repetidas e elimina o risco de alterações acidentais de dados.

As pessoas que não estão familiarizadas com as constantes de matriz geralmente as acham assustadoras devido à sua sintaxe incomum e à necessidade de um símbolo especial para entrar nelas. Então, neste guia, eu os retirarei de volta aos ossos nus e mostrarei como usá-los em cenários do mundo real. No final, você será um especialista constante de matriz!

Constantes de matriz no Excel: Definição

Uma constante de matriz é um conjunto codificado de valores estáticos inseridos em uma fórmula, fechada em aparelhos encaracolados. Em um exemplo direto, digitando:

={10,20,30,40}

na célula A1 e pressionando Enter retorna uma matriz dinâmica de 10 na célula A1, 20 na célula B1, 30 na célula C1 e 40 na célula D1.

No exemplo acima, o resultado da matriz dinâmica é derramado horizontalmente (em colunas) porque as vírgulas são usadas entre cada valor na constante da matriz.

Por outro lado, o uso de semicolons entre os valores na matriz constante retorna uma matriz dinâmica vertical (em linhas):

={10;20;30;40}

Você também pode combinar vírgulas e semicolons em uma constante de matriz para devolver uma matriz dinâmica bidimensional:

={10,20;30,40}

Constantes de matriz funcionam de maneira diferente do que costumavam

Versões únicas do Excel de 2021 em diante, Excel for Microsoft 365 e Excel for the Web suportam fórmulas de matriz dinâmica. Isso significa que, quando você digita uma matriz constante em uma fórmula que espera vários valores e pressione Enter, o resultado derrama nas células adjacentes, com apenas a célula superior esquerda contendo uma fórmula.

No entanto, em versões mais antigas do Excel que não suportam fórmulas de matriz dinâmica, você precisa selecionar todas as células onde deseja que os resultados sejam exibidos, digite a fórmula e pressione Ctrl+Shift+Enter para cometê -lo. Neste ponto, a mesma fórmula aparece em todas as células que você selecionou. Isso é conhecido como uma fórmula de matriz CSE ou legado.

Usando constantes de matriz com funções do Excel

As constantes de matriz são mais úteis no Excel quando emparelhadas com funções, pois economizam de inserir fórmulas múltiplas ou longas. Aqui estão dois cenários de exemplo.

Soma e Countif: Contando quantos valores em uma matriz atendem a vários critérios

Imagine que você tem esta tabela de excel, nomeada T_TaskLoge você deseja descobrir o número de tarefas cujo status é CompletoAssim, Pendenteou Arremessado.

Para fazer isso sem constantes de matriz, você pode digitar a seguinte fórmula (dividida em linhas separadas para facilitar a leitura):

=COUNTIF(T_TaskLog[Status],"Complete")
+
COUNTIF(T_TaskLog[Status],"Pending")
+
COUNTIF(T_TaskLog[Status],"Pitched")

onde os resultados de três fórmulas Countif separadas são adicionadas usando o operador Plus (+).

No entanto, essa fórmula é longa e difícil de analisar. Em vez disso, você pode usar constantes de matriz para agrupar todos os critérios em um argumento. Primeiro, digite o nome da função, um parêntese de abertura e o intervalo para avaliar, seguido de uma vírgula:

=COUNTIF(T_TaskLog[Status],

Agora, para os critérios, você precisa inserir todos os três valores de status como uma matriz constante, fechada em aparelhos encaracolados. Em seguida, adicione um parêntese final e pressione Enter:

=COUNTIF(T_TaskLog[Status],{"Complete","Pending","Pitched"})

Neste ponto, o Excel retorna três resultados separados como uma matriz dinâmica – 3 para Completo2 para Pendentee 2 para Arremessado. Isso porque você ainda não disse ao Excel para adicionar os resultados.

Para consertar isso, envolva toda a fórmula dentro da função da soma e pressione Enter:

=SUM(COUNTIF(T_TaskLog[Status],{"Complete","Pending","Pitched"}))

Portanto, em vez de usar três fórmulas de contagem separadas separadas pelo sinal de mais para contar os três critérios de texto, as forças constantes da matriz se destacam para executar o cálculo três vezes – um para cada critério – e adicione os resultados através da função da soma.

Grande: encontrando o topo x Valores em um intervalo

Neste exemplo, digamos que você queira devolver os três principais lucros da tabela, nomeado T_Profitscomo uma matriz separada.

Uma maneira de fazer isso é usar a grande função em três células separadas.

Digitar esta fórmula na célula E2 retorna o maior valor na coluna de lucro:

=LARGE(T_Profits[Profit],1)

Em seguida, digitar esta fórmula na célula E3 retorna o segundo maior valor:

=LARGE(T_Profits[Profit],2)

Finalmente, digitar esta fórmula na célula E4 retorna o terceiro maior valor:

=LARGE(T_Profits[Profit],3)

Você pode usar a função xLookup para recuperar os IDs das lojas onde foram obtidos esses três maiores lucros:

=XLOOKUP(E2:E4,T_Profits[Profit],T_Profits[Shop])

No entanto, digitar três fórmulas separadas e desvinculadas leva tempo e é propenso a erros. Em vez disso, você pode usar uma matriz constante com a grande função para alcançar o mesmo resultado com uma única fórmula:

=LARGE(T_Profits[Profit],{1;2;3})

Essa fórmula aplica a grande função três vezes – a primeira vez para retornar o maior valor, a segunda vez para retornar o segundo maior valor e a terceira vez para retornar o terceiro maior valor – e retorna os três resultados como uma matriz dinâmica vertical porque um semicolon é usado entre cada número na constante da matriz.

Da mesma forma, se você deseja somar os três maiores lucros sem usar constantes de matriz, precisará inserir a grande função três vezes, separada pelo sinal de mais:

=LARGE(T_Profits[Profit],1)+LARGE(T_Profits[Profit],2)+LARGE(T_Profits[Profit],3)

No entanto, o uso de uma matriz constante com a função de soma torna a fórmula muito arrumada:

=SUM(LARGE(T_Profits[Profit],{1,2,3}))

Além disso, se você deseja incluir o quarto maior lucro no cálculo, basta adicionar uma vírgula e o número quatro à fórmula, em vez de digitar uma função extra grande na íntegra:

=SUM(LARGE(T_Profits[Profit],{1,2,3,4}))

Constantes de matriz de nomeação no Excel

Se você se encontrar repetidamente usando o mesmo conjunto de valores estáticos no Excel, nomear uma constante de matriz salva você de digitá -lo manualmente a cada vez.

Para criar uma constante de matriz nomeada, na guia Fórmulas na faixa de opções, clique em “Nome Manager”.

Então, na caixa de diálogo Gerenciador de nomes, clique em “Novo”.

Em seguida, no campo refere -se ao campo, digite o sinal igual, seguido de uma chave de abertura:

={

Agora, digite a matriz constante, lembrando que, para criar uma matriz horizontal (linha), os valores devem ser separados por vírgulas e, para matrizes verticais (coluna), você precisa separá -las com semicolons. Além disso, lembre -se de que os valores de texto devem ser fechados em cotações duplas. Quando terminar, feche os aparelhos encaracolados:

={"Mon","Tue","Wed","Thu","Fri","Sat","Sun"}

Finalmente, no campo Nome, digite um nome memorável para sua matriz constante e clique em “OK”.

Agora você pode usar essa matriz constante em sua pasta de trabalho digitando o sinal igual, seguido pelo nome que você acabou de atribuir e pressionando Enter:

Como nas constantes de matriz sem nome, você pode usá -las em fórmulas. Neste exemplo, a matriz constante nomeada Cinco é:

={5,10,15,20}

Portanto, multiplicar o valor na célula A1 por essa constante de matriz nomeada produz quatro resultados separados:

=A1*Fives

Pontos a serem observados ao usar constantes de matriz no Excel

Apesar dos muitos benefícios do uso de constantes de matriz no Excel, existem algumas regras e limitações que você deve estar ciente:

• As fórmulas de matriz dinâmica não podem se derramar em colunas de tabela estruturadas; portanto, as constantes de matriz que produzem resultados de várias células só podem ser usadas em células regulares. Dito isto, as próprias constantes da matriz podem operar em dados nas tabelas.
• As constantes da matriz podem conter apenas texto em cotações duplas, números simples (sem símbolos de moeda ou sinais percentuais) ou valores booleanos (verdadeiros e falsos), separados pelos delimitadores de vírgula e vírgula. Eles não podem conter referências celulares, outras matrizes, fórmulas ou curingas.
• Como você não pode fazer referência às células em constantes de matriz, todos os valores devem ser codificados (daí o nome deles, matriz constantes). Como resultado, as fórmulas que contêm constantes de matriz são menos flexíveis do que as que não têm.
• Se você planeja compartilhar sua pasta de trabalho com pessoas menos experientes no Excel, cuidado com o efeito da caixa preta que as constantes de matriz criam. Ou seja, como o processo de cálculo nas constantes de matriz é menos transparente do que nas fórmulas padrão, aqueles que não estão familiarizados com a lógica da matriz podem ter dificuldade para entender como a planilha funciona.
• Em alguns casos, o uso de uma função de matriz dinâmica é mais eficiente do que usar uma matriz constante. Por exemplo, em vez de entrada Ting uma constante como {1,2,3}, você pode usar a função de sequência para produzir o mesmo resultado.
• Como a maneira como as constantes de matriz são tratadas no Excel mudou drasticamente quando as matrizes dinâmicas foram introduzidas, quando um arquivo do Excel criado em uma versão moderna é aberto em uma versão mais antiga, as matrizes derramadas são convertidas em fórmulas CSE.

As constantes de matriz são particularmente úteis quando usadas como argumentos nas funções do Excel que não retornam matrizes dinâmicas por padrão. No entanto, as funções de matriz dinâmica-disponíveis em versões únicas do Excel a partir de 2021, Excel para Microsoft 365 e Excel para a Web-são projetados especificamente para retornar vários valores em um intervalo de células. Exemplos dessas funções especiais incluem filtro, classificação e classificação, exclusivo e xlookup.