Diretrizes e exemplos de fórmulas de matriz

Diretrizes e exemplos de fórmulas de matriz

Observação: Desejamos fornecer o conteúdo da Ajuda mais recente no seu idioma da forma mais rápida possível. Esta página foi traduzida de forma automatizada e pode conter imprecisões ou erros gramaticais. Nosso objetivo é que este conteúdo seja útil para você. No final da página, deixe sua opinião se estas informações foram úteis para você. Aqui está o artigo em inglês para facilitar a referência.

Uma fórmula de matriz é uma fórmula que pode executar vários cálculos em um ou mais itens em uma matriz. Você pode pensar em uma matriz como uma linha ou coluna de valores ou uma combinação de linhas e colunas de valores. Fórmulas de matriz podem retornar vários resultados ou um único resultado.

A partir da atualização de setembro de 2018 para o Office 365, qualquer fórmula que possa retornar vários resultados irá automaticamente despejar os itens para baixo ou para dentro das células vizinhas. Essa alteração no comportamento também é acompanhada por várias novas funções de matriz dinâmica. Fórmulas de matriz dinâmicas, se estiverem usando funções existentes ou funções de matriz dinâmicas, só precisam ser inseridas em uma única célula e, em seguida, confirmadas pressionando Enter. Antes, as fórmulas de matriz herdadas exigem primeiro selecionar todo o intervalo de saída e, em seguida, confirmar a fórmula com Ctrl + Shift + Enter. Eles são comumente chamados de fórmulas CSE .

Você pode usar fórmulas de matriz para executar tarefas complexas, como:

  • Crie rapidamente conjuntos de exemplos de exemplos de exemplos.

  • Contar o número de caracteres contidos em um intervalo de células.

  • Somar somente números que atendam a certas condições, como os valores mais baixos de um intervalo ou números que estejam entre um limite superior e inferior.

  • Somar cada enésimo valor em um intervalo de valores.

Os exemplos a seguir mostram como criar fórmulas de matriz de várias células e de uma célula única. Sempre que possível, incluímos exemplos com algumas das funções de matriz dinâmica, bem como as fórmulas de matriz existentes inseridas como matrizes dinâmicas e herdadas.

Baixar nossos exemplos

Baixe uma pasta de trabalho de exemplo com todos os exemplos de fórmulas de matriz neste artigo.

Este exercício mostra como usar fórmulas de matriz de várias células e de uma célula para calcular um conjunto de estatísticas de vendas. O primeiro conjunto de etapas usa uma fórmula de várias células para calcular um conjunto de subtotais. O segundo conjunto usa uma fórmula de uma célula para calcular um total geral.

  • Fórmula de matriz de várias células

    Função matriz com várias células na célula H10 = F10: F19 * G10: G19 para calcular o número de carros vendidos pelo preço unitário

  • Aqui estamos calculando o total de vendas de vitórias e sedãs para cada vendedor inserindo = F10: F19 * G10: G19 na célula H10.

    Ao pressionar Enter, você verá os resultados empingar para as células H10: H19. Observe que o intervalo de despejo é realçado com uma borda quando você seleciona qualquer célula dentro do intervalo de despejo. Você também pode notar que as fórmulas nas células H10: H19 estão acinzentadas. Eles estão apenas ali para referência, portanto, se você quiser ajustar a fórmula, será necessário selecionar a célula H10, na qual a fórmula Mestre reside.

  • Fórmula de matriz de célula única

    Fórmula de matriz de célula única para calcular um total geral com = soma (F10: F19 * G10: G19)

    Na célula H20 da pasta de trabalho de exemplo, digite ou copie e cole = Soma (F10: F19 * G10: G19)e, em seguida, pressione Enter.

    Nesse caso, o Excel multiplica os valores na matriz (o intervalo de células de F10 a G19) e, em seguida, usa a função soma para adicionar os totais juntos. O resultado é um total geral de R$ 1.590.000,00 em vendas.

    Esse exemplo mostra como esse tipo de fórmula pode ser eficiente. Por exemplo, vamos supor que você tenha 1.000 linhas de dados. Você pode somar esses dados no todo ou em parte, criando uma fórmula de matriz em uma única célula, em vez de arrastar a fórmula pelas 1.000 linhas. Além disso, observe que a fórmula de célula única na célula H20 é completamente independente da fórmula de várias células (a fórmula nas células H10 a H19). Esta é outra vantagem de usar fórmulas de matriz : a flexibilidade. Você pode alterar as outras fórmulas na coluna H sem afetar a fórmula no H20. Também pode ser uma boa prática para ter totais independentes como este, pois ele ajuda a validar a precisão dos seus resultados.

  • As fórmulas de matriz dinâmicas também oferecem estas vantagens:

    • Consistência    Se você clicar em qualquer uma das células da H10 para baixo, verá a mesma fórmula. Essa consistência pode ajudar a garantir mais precisão.

    • Segurança    Não é possível substituir um componente de uma fórmula de matriz de várias células. Por exemplo, clique em H11 da célula e pressione Delete. O Excel não alterará a saída da matriz. Para alterá-la, você precisa selecionar a célula superior esquerda na matriz ou a célula H10.

    • Arquivos menores    Você pode usar uma única fórmula de matriz em vez de várias fórmulas intermediárias. Por exemplo, o exemplo de vendas do carro usa uma fórmula de matriz para calcular os resultados na coluna E. Se você usou fórmulas padrão, como = F10 * G10, F11 * G11, F12 * G12, etc., usaria 11 fórmulas diferentes para calcular os mesmos resultados. Isso não é um grande negócio, mas e se você tivesse milhares de linhas para totalizar? Em seguida, ele pode fazer uma grande diferença.

    • Eficiência    As funções de matriz podem ser uma maneira eficiente de criar fórmulas complexas. A fórmula de matriz = soma (F10: F19 * G10: G19) é a mesma que: = SUM (F10 * G10, F11 * G11, F12 * G12, F13 * G13, F14 * G14, F15 * G15, F16 * G16, f17 * G17, F18 * G18, F19 * G19).

    • Abrange    Fórmulas de matriz dinâmicas serão automaticamente despejadas no intervalo de saída. Se os dados de origem estiverem em uma tabela do Excel, as fórmulas de matriz dinâmicas serão redimensionadas automaticamente à medida que você adicionar ou remover dados.

    • #SPILL! erros    Matrizes dinâmicas introduziu o erro #SPILL!, que indica que o intervalo de despejo pretendido está bloqueado por algum motivo. Quando você solucionar o bloqueio, a fórmula será automaticamente despejada.

As constantes de matriz são um componente das fórmulas de matriz. Para criar essas constantes, insira uma lista de itens e coloque-a entre chaves ({ }) manualmente, da seguinte maneira:

= {1, 2, 3, 4, 5} ou = {"janeiro", "fevereiro", "março"}

Se você separar os itens com vírgulas, será criada uma matriz horizontal (uma linha). Se você separar os itens com ponto-e-vírgulas, será criada uma matriz vertical (uma coluna). Para criar uma matriz bidimensional, você delimita os itens em cada linha com vírgulas e delimita cada linha com ponto-e-vírgula.

Os procedimentos a seguir oferecem alguma prática na criação de constantes horizontais, verticais e bidimensionais. Mostraremos exemplos usando a função sequência para gerar automaticamente constantes de matriz, bem como constantes de matriz inseridas manualmente.

  • Criar uma constante horizontal

    Use a pasta de trabalho dos exemplos anteriores ou crie uma nova pasta de trabalho. Selecione qualquer célula vazia e insira = sequência (1, 5). A função sequência cria uma matriz de 1 linha por 5 colunas igual a = {1, 2, 3, 4, 5}. O seguinte resultado será exibido:

    Crie uma constante de matriz horizontal com = sequência (1, 5) ou = {1, 2, 3, 4, 5}

  • Criar uma constante vertical

    Selecione qualquer célula em branco com a sala abaixo dela e insira = sequência (5)ou = {1; 2; 3; 4; 5}. O seguinte resultado será exibido:

    Criar uma constante de matriz vertical com = sequência (5) ou = {1; 2; 3; 4; 5}

  • Criar uma constante bidimensional

    Selecione qualquer célula em branco com a sala à direita e abaixo dela e insira = sequência (3, 4). O seguinte resultado será exibido:

    Criar uma constante de matriz de 3 linhas por 4 colunas com = sequência (3, 4)

    Você também pode inserir: ou = {1, 2, 3, 4; 5, 7, 8; 9, 10, 11, 12}, mas deseja pagar a atenção para o local onde você coloca pontos-e-vírgulas versus vírgulas.

    Como você pode ver, a opção sequência oferece vantagens significativas sobre a inserção manual de valores constantes de matriz. Basicamente, ele economiza tempo, mas também pode ajudar a reduzir erros na entrada manual. Também é mais fácil de ler, especialmente porque os dois pontos-e-vírgulas podem ser difíceis de diferenciar os separadores de vírgula.

Aqui está um exemplo que usa constantes de matriz como parte de uma fórmula maior. Na pasta de trabalho de exemplo, vá para a constante em uma planilha de fórmulas ou crie uma nova planilha.

Na célula D9, inserimos = sequência (1, 5, 3, 1), mas você também pode inserir 3, 4, 5, 6 e 7 nas células A9: H9. Não há nada especial sobre essa seleção de número específico, escolhemos algo que não seja o 1-5 para diferenciação.

Na célula E11, insira = Soma (D9: H9 * sequência (1, 5))ou = Soma (D9: H9 * {1, 2, 3, 4, 5}). As fórmulas retornam 85.

Use constantes de matriz em fórmulas. Neste exemplo, usamos = soma (D9: H (* sequência (1; 5))

A função sequência cria o equivalente da constante de matriz {1, 2, 3, 4, 5}. Como o Excel executa operações em expressões entre parênteses primeiro, os dois próximos elementos que entram em cena são os valores de célula em D9: H9 e a operador de multiplicação (*). Neste ponto, a fórmula multiplica os valores da matriz armazenada pelos valores correspondentes da constante. Isso equivale a:

= Soma (D9 * 1, E9 * 2, F9 * 3, G9 * 4, H9 * 5)ou = Soma (3 * 1, 4 * 2, 5 * 3, 6 * 4, 7 * 5)

Por fim, a função soma soma os valores e retorna 85.

Para evitar o uso da matriz armazenada e manter a operação completamente na memória, você pode substituí-la por outra constante de matriz:

= Soma (sequência (1, 5, 3, 1) * sequência (1, 5))ou = Soma ({3, 4, 5, 6, 7} * {1, 2, 3, 4, 5})

Elementos que você pode usar em constantes de matriz

  • As constantes de matriz podem conter números, texto, valores lógicos (como verdadeiro e falso) e valores de erro como #N/A. Você pode usar números em formatos inteiros, decimais e científicos. Se incluir texto, você precisa colocá-lo entre aspas ("texto").

  • As constantes de matriz não podem conter outras matrizes, fórmulas ou funções. Em outras palavras, elas só podem conter texto ou números separados por vírgula ou ponto-e-vírgula. O Excel exibirá uma mensagem de aviso quando você inserir uma fórmula como {1,2,A1:D4} ou {1,2,SOMA(Q2:Z8)}. Além disso, valores numéricos não podem conter sinais de percentual, cifrões, vírgulas ou parênteses.

Uma das melhores maneiras de usar constantes de matriz é nomeá-las. As constantes nomeadas podem ser mais fáceis de usar e ocultam um pouco da complexidade das fórmulas de matriz. Para nomear uma constante de matriz e usá-la em uma fórmula, siga este procedimento:

Vá para fórmulas _GT_ nomes definidos > definir nome. Na caixa nome , digite trimestre1. Na caixa Refere-se a, insira a seguinte constante (lembre-se de digitar as chaves manualmente):

={"Janeiro","Fevereiro","Março"}

A caixa de diálogo agora deve ter a seguinte aparência:

Adicionar uma constante de matriz nomeada a partir de fórmulas > nomes definidos > Gerenciador de nomes > novo

Clique em OK, selecione qualquer linha com três células em branco e Enter = trimestre1.

O seguinte resultado será exibido:

Use uma constante de matriz nomeada em uma fórmula, como = Trimestre1, em que Trimestre1 foi definido como = {"janeiro", "fevereiro", "março"}

Se você quiser que os resultados sejam despejados verticalmente em vez de horizontalmente, você pode usar =transpor(trimestre1).

Se você quiser exibir uma lista de 12 meses, como você pode usar ao criar um demonstrativo financeiro, é possível basear uma para fora do ano atual com a função sequência. A novidade sobre essa função é que, apesar de apenas o mês ser exibido, há uma data válida por trás dele que você pode usar em outros cálculos. Você encontrará esses exemplos na constante de matriz nomeada e nas planilhas de conjunto de trabalho de exemplo rápido na pasta de trabalho de exemplo.

= TEXTO (data (ano (hoje ()), sequência (1, 12), 1), "Mmm")

Use uma combinação das funções texto, data, ano, hoje e sequência para criar uma lista dinâmica de 12 meses

Isso usa a função data para criar uma data com base no ano atual, a sequência cria uma constante de matriz de 1 a 12 para janeiro até dezembro, e a função texto converte o formato de exibição em "Mmm" (Jan, Fev, mar, etc.). Se quiser exibir o nome completo do mês, como Janeiro, você usaria "MMMM".

Quando você usa uma constante nomeada como uma fórmula de matriz, lembre-se de inserir o sinal de igual, como in = Trimestre1, não apenas Trimestre1. Caso contrário, o Excel interpretará a matriz como uma cadeia de texto, e a fórmula não funcionará conforme esperado. Por fim, tenha em mente que você pode usar combinações de funções, texto e números. Tudo depende da criatividade que você deseja obter.

Os exemplos a seguir demonstram algumas das formas pelas quais você pode colocar constantes de matriz para utilização em fórmulas de matriz. Alguns dos exemplos usam a função transpor para converter linhas em colunas e vice-versa.

  • Vários cada item em uma matriz

    Enter = sequência (1, 12) * 2ou = {1, 2, 3, 4; 5, 6, 7, 8; 9, 10, 11, 12} * 2

    Você também pode dividir (/), adicionar com (+) e subtrair com (-).

  • Elevar os itens de uma matriz ao quadrado

    Enter = sequência (1, 12) ^ 2ou = {1, 2, 3, 4; 5; 6, 7, 8; 9, 10, 11, 12} ^ 2

  • Localizar a raiz quadrada de itens quadrados em uma matriz

    Enter =sqrt(sequência (1, 12) ^ 2)ou = sqrt ({1, 2, 3, 4; 5, 6, 7, 8; 9, 10, 11, 12} ^ 2)

  • Transpor uma linha unidimensional

    Enter = transpor (sequência (1; 5))ou = transpor ({1, 2, 3, 4, 5})

    Embora você tenha inserido uma constante de matriz horizontal, a função TRANSPOR converterá a constante em uma coluna.

  • Transpor uma coluna unidimensional

    Enter = transpor (sequência (5; 1))ou = transpor ({1; 2; 3; 4; 5})

    Embora você tenha inserido uma constante de matriz vertical, a função TRANSPOR converterá a constante em uma linha.

  • Transpor uma constante bidimensional

    Enter = transpor (sequência (3, 4))ou = transpor ({1, 2, 3, 4; 5; 6, 7, 8; 9, 10, 11, 12})

    A função TRANSPOR converterá cada linha em uma série de colunas.

Esta seção contém exemplos de fórmulas de matriz básicas.

  • Criar uma matriz com base em valores existentes

    O exemplo a seguir explica como usar fórmulas de matriz para criar uma nova matriz a partir de uma matriz existente.

    Enter = sequência (3, 6, 10, 10)ou = {10, 20, 30, 40, 50, 60; 70, 80, 90100110120; 130140150160170180}

    Certifique-se de digitar {(chave de abertura) antes de digitar 10 e} (chave de fechamento) depois de digitar 180, pois você está criando uma matriz de números.

    Em seguida, insira = D9 #ou = D9: i11 em uma célula em branco. Uma matriz de 3 x 6 de células é exibida com os mesmos valores exibidos em D9: D11. O sinal # é chamado de operador de intervalo derramadoe é Excel's de fazer referência a todo o intervalo de matriz em vez de ter que digitá-lo.

    Use o operador de intervalo derramado (#) para fazer referência a uma matriz existente

  • Criar uma constante de matriz com base em valores existentes

    Você pode pegar os resultados de uma fórmula de matriz derramada e convertê-la em partes do componente. Selecione a célula D9 e pressione F2 para alternar para o modo de edição. Em seguida, pressione F9 para converter as referências de célula em valores, que o Excel converte em uma constante de matriz. Quando você pressiona Enter, a fórmula, = D9 #, agora deve ser = {10, 20, 30; 40, 50, 60; 70, 80, 90}.

  • Contar caracteres em um intervalo de células

    O exemplo a seguir mostra como contar o número de caracteres em um intervalo de células. Isso inclui espaços.

    Contar o número total de caracteres em um intervalo e outras matrizes para trabalhar com cadeias de caracteres de texto

    = SOMA (LEN (C9: C13))

    Nesse caso, a função Len retorna o comprimento de cada cadeia de caracteres de texto em cada uma das células no intervalo. A função soma, em seguida, adiciona esses valores juntos e exibe o resultado (66). Se quiser obter um número médio de caracteres, você poderia usar:

    = MÉDIA (LEN (C9: C13))

  • Conteúdo da célula mais longa no intervalo C9: C13

    = INDEX (C9: C13, MATCH (MAX (LEN (LEN (C9: C13)), LEN (C9: C13), 0), 1)

    Esta fórmula só funcionará quando um intervalo de dados contiver uma única coluna de células.

    Vamos examinar mais detalhadamente a fórmula, começando dos elementos internos para os externos. A função Len retorna o comprimento de cada um dos itens no intervalo de células D2: D6. A função máximo calcula o maior valor entre esses itens, que corresponde à cadeia de texto mais longa, que está na célula D3.

    É neste ponto que as coisas ficam um pouco mais complexas. A função CORRESP calcula o deslocamento (a posição relativa) da célula que contém a cadeia de texto mais longa. Para fazer isso, são necessários três argumentos: um valor de pesquisa, uma matriz de pesquisa e um tipo de correspondência. A função CORRESP procura na matriz de pesquisa o valor de pesquisa especificado. Neste caso, o valor de pesquisa é a cadeia de texto mais longa:

    MAX (LEN (C9: C13)

    e essa cadeia de caracteres reside nesta matriz:

    LEN (C9: C13)

    O argumento tipo de correspondência nesse caso é 0. O tipo de correspondência pode ser um valor 1, 0 ou-1.

    • 1-retorna o maior valor que é menor ou igual ao valor de pesquisa

    • 0-retorna o primeiro valor exatamente igual ao valor de pesquisa

    • -1-retorna o menor valor que é maior ou igual ao valor de pesquisa especificado

    • Se você omitir um tipo de correspondência, o Excel adotará o valor 1.

    Por fim, a função índice leva estes argumentos: uma matriz e um número de linha e coluna dentro dessa matriz. O intervalo de células C9: C13 fornece a matriz, a função corRESP fornece o endereço da célula, e o argumento final (1) especifica que o valor vem da primeira coluna na matriz.

    Se você quisesse obter o conteúdo da menor Cadeia de caracteres de texto, você substituiria MAX no exemplo acima por mín.

  • Localizar os n menores valores de um intervalo

    Este exemplo mostra como encontrar os três menores valores em um intervalo de células, em que uma matriz de dados de exemplo nas células B9: B18has foi criada com: = int (matrizaleatória(10 1) * 100). Observe que MATRIZALEATÓRIA é uma função volátil, portanto, você receberá um novo conjunto de números aleatórios sempre que o Excel calcula.

    Fórmula de matriz do Excel para localizar o enésimo menor valor: = pequeno (B9 #, SEQUENCE (D9))

    Enter = pequeno (B9 #, Sequence (D9), = pequeno (B9: B18, {1; 2; 3})

    Essa fórmula usa uma constante de matriz para avaliar a função pequeno três vezes e retornar os 3 menores membros na matriz contida nas células B9: B18, onde 3 é um valor variável na célula D9. Para localizar mais valores, você pode aumentar o valor na função sequência ou adicionar mais argumentos à constante. Você também pode usar funções adicionais com essa fórmula, como SOMA ou MÉDIA. Por exemplo:

    = SOMA (PEQUENO (B9 #, SEQUENCE (D9))

    = MÉDIA (PEQUENO (B9 #, SEQUENCE (D9))

  • Localizar os n maiores valores de um intervalo

    Para localizar os maiores valores em um intervalo, você pode substituir a função menor pela função grande. Além disso, o exemplo a seguir usa as funções LINHA e INDIRETO.

    Enter = grande (B9 #, Row (indireto ("1:3"))) ou = Large (B9: B18, Row (indireto ("1:3")))

    Neste ponto, pode ser útil saber um pouco sobre as funções LINHA e INDIRETO. Você pode usar a função LINHA para criar uma matriz de números inteiros consecutivos. Por exemplo, selecione um vazio e digite:

    =LINHA(1:10)

    A fórmula cria uma coluna de 10 inteiros consecutivos. Para ver um possível problema, insira uma linha acima do intervalo que contém a fórmula de matriz (ou seja, acima da linha 1). O Excel ajusta as referências de linha e a fórmula agora gera inteiros de 2 a 11. Para corrigir esse problema, adicione a função INDIRETO à fórmula:

    =LINHA(INDIRETO("1:10"))

    A função inDIRETO usa cadeias de caracteres de texto como seus argumentos (que é por isso que o intervalo 1:10 está entre aspas). O Excel não ajusta valores de texto quando você insere linhas ou move a fórmula de matriz de alguma outra maneira. Como resultado, a função LINHA sempre gera a matriz de inteiros desejada. Você poderia simplesmente usar a sequência com facilidade:

    = SEQUÊNCIA (10)

    Vamos examinar a fórmula que você usou anteriormente – = grande (B9 #, ROW (inDIRETO ("1:3")), começando pelos parênteses internos e trabalhando para fora: a função inDIRETO retorna um conjunto de valores de texto, nesse caso, os valores de 1 a 3. A função Lin, por sua vez, gera uma matriz de colunas de três células. A função grande usa os valores no intervalo de células B9: B18, e é avaliada três vezes, uma vez para cada referência retornada pela função Lin. Se você quiser encontrar mais valores, adicione um intervalo de células maior à função inDIRETO. Por fim, assim como nos pequenos exemplos, você pode usar essa fórmula com outras funções, como soma e média.

  • Somar um intervalo que contém valores de erro

    A função soma no Excel não funciona quando você tenta somar um intervalo que contém um valor de erro, como #VALUE! ou #N/A. Este exemplo mostra como somar os valores em um intervalo chamado dados que contém erros:

    Use matrizes para lidar com erros. Por exemplo, = soma (se (isERROr (dados), ", dados) somará o intervalo chamado dados, mesmo que inclua erros, como #VALUE! ou #NA!.

  • =SOMA(SE(ÉERROS(Dados),"",Dados))

    A fórmula cria uma nova matriz contendo os valores originais menos quaisquer valores de erro. Das funções internas para as externas, a função ÉERROS pesquisa o intervalo de células (Dados) em busca de erros. A função SE retornará um valor específico se uma condição especificada for avaliada como VERDADEIRO e outro valor se ela for avaliada como FALSO. Neste caso, ela retorna cadeias de caracteres vazias ("") para todos os valores de erro, pois eles são avaliados como VERDADEIRO, e retorna os valores restantes do intervalo (Dados), pois eles são avaliados como FALSO, o que significa que eles não contêm valores de erro. Em seguida, a função SOMA calcula o total da matriz filtrada.

  • Contar o número de valores de erro em um intervalo

    Este exemplo é como a fórmula anterior, mas retorna o número de valores de erro em um intervalo chamado dados em vez de filtra-los:

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

    Essa fórmula cria uma matriz contendo o valor 1 para as células que contêm erros e o valor 0 para as que não contêm erros. Você pode simplificar a fórmula e obter o mesmo resultado removendo o terceiro argumento da função SE, da seguinte maneira:

    SOMA(SE(ÉERROS(Dados),1))

    Se você não especificar o argumento, a função SE retornará FALSO se uma célula não contiver um valor de erro. Você pode simplificar a fórmula ainda mais:

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

    Essa versão funciona porque VERDADEIRO*1=1 e FALSO*1=0.

Talvez seja necessário somar valores com base em condições.

Você pode usar matrizes para calcular com base em determinadas condições. = SOMA (se (Sales>0, Sales)) somará todos os valores maiores que 0 em um intervalo chamado Sales.

Por exemplo, essa fórmula de matriz soma apenas os inteiros positivos em um intervalo chamado Sales, que representa as células E9: e24 no exemplo acima:

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

A função se cria uma matriz de valores positivos e falso. A função SOMA essencialmente ignora os falsos valores, pois 0+0=0. O intervalo de células que você usa nessa fórmula pode consistir em qualquer número de linhas e colunas.

Você também pode somar valores que atendam a mais de uma condição. Por exemplo, essa fórmula de matriz calcula valores maiores que 0 e menores do que 2500:

= SOMA ((Sales>0) * (Sales<2500) * (vendas))

Lembre-se de que essa fórmula retornará um erro se o intervalo contiver uma ou mais células não numéricas.

Você também pode criar fórmulas de matriz que usem um tipo de condição OU. Por exemplo, você pode somar valores maiores do que 0 ou menores que 2500:

= SOMA (se ((Sales>0) + (Sales<2500), vendas))

Não é possível usar as funções E e OU diretamente em fórmulas de matriz, pois essas funções retornam um único resultado, VERDADEIRO ou FALSO, e as funções de matriz exigem matrizes de resultados. Você pode contornar o problema usando a lógica mostrada na fórmula anterior. Em outras palavras, você executa operações matemáticas, como adição ou multiplicação de valores que atendem à condição ou ou e.

Este exemplo mostra como remover zeros de um intervalo quando você precisar calcular a média dos valores desse intervalo. A fórmula usa um intervalo de dados chamado Vendas:

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

A função SE cria uma matriz de valores que não são iguais a 0 e passa esses valores para a função MÉDIA.

Esta fórmula de matriz compara os valores de dois intervalos de células chamados MeusDados e SeusDados e retorna o número de diferenças entre os dois. Se o conteúdo dos dois intervalos for idêntico, a fórmula retornará 0. Para usar essa fórmula, os intervalos de células precisam ter o mesmo tamanho e da mesma dimensão. Por exemplo, se os dados forem um intervalo de 3 linhas por cinco colunas, SeusDados também deverá ser 3 linhas por 5 colunas:

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

A fórmula criará uma nova matriz do mesmo tamanho dos intervalos que você está comparando. A função SE preencherá a matriz com o valor 0 e o valor 1 (0 para células que não correspondam e 1 para células idênticas). Em seguida, a função SOMA retornará a soma dos valores da matriz.

Você pode simplificar a fórmula deste modo:

= SOMA (1 * (MyData<>YourData))

Como a fórmula que conta valores de erro em um intervalo, essa fórmula funciona porque VERDADEIRO*1=1 e FALSO*1=0.

Esta fórmula de matriz retorna o número de linha do valor máximo em um intervalo de uma única coluna chamado Dados:

=MÍN(SE(Dados=MÁX(Dados),LINHA(Dados),""))

A função SE cria uma nova matriz que corresponde ao intervalo chamado Dados. Se uma célula correspondente contiver o valor máximo no intervalo, a matriz conterá o número da linha. Caso contrário, a matriz conterá uma cadeia de caracteres vazia (""). A função MÍN usa a nova matriz como segundo argumento e retorna o menor valor, que corresponde ao número da linha do valor máximo em Dados. Se o intervalo chamado Dados contiver valores máximos idênticos, a fórmula retornará a linha do primeiro valor.

Para retornar o endereço de célula real de um valor máximo, use esta fórmula:

=ENDEREÇO(MÍN(SE(Dados=MÁX(Dados),LINHA(Dados),"")),COLUNA(Dados))

Você encontrará exemplos semelhantes na pasta de trabalho de exemplo na planilha diferenças entre conjuntos de trabalho.

Este exercício mostra como usar fórmulas de matriz de várias células e de uma célula para calcular um conjunto de estatísticas de vendas. O primeiro conjunto de etapas usa uma fórmula de várias células para calcular um conjunto de subtotais. O segundo conjunto usa uma fórmula de uma célula para calcular um total geral.

  • Fórmula de matriz de várias células

Copie a tabela inteira abaixo e cole-a na célula a1 em uma planilha em branco.

Vendas Pessoa

Carro Digite

Número de telefone Vendidos

Unidade de Preço

Total Vendas

Oliveira

Sedã

5

33000

Cupê

4

37000

Ingle

Sedã

6

24000

Cupê

8

21000

Jordan

Sedã

3

29000

Cupê

1

31000

Gonçalo

Sedã

9

24000

Cupê

5

37000

Sanchez

Sedã

6

33000

Cupê

8

31000

Total Geral

Total Geral

'=SOMA(C2:C11*D2:D11)

=SOMA(C2:C11*D2:D11)

  1. Para ver o total de vendas de vitórias e sedãs para cada vendedor, selecione as células E2: E11, insira a fórmula = C2: C11 * D2: D11e, em seguida, pressione Ctrl + Shift + Enter.

  2. Para ver o total geral de todas as vendas, selecione a célula F11, insira a fórmula = Soma (C2: C11 * D2: D11)e, em seguida, pressione Ctrl + Shift + Enter.

Quando você pressiona Ctrl + Shift + Enter, o Excel coloca a fórmula entre chaves ({}) e insere uma instância da fórmula em cada célula do intervalo selecionado. Como isso acontece muito rápido, o que você vê na coluna E é o valor total das vendas de cada tipo de carro para cada vendedor. Se você selecionar E2, depois selecionar E3, E4 e assim por diante, a mesma fórmula será mostrada: {=C2:C11*D2:D11} 

Os totais da coluna E são calculados por uma fórmula de matriz

  • Criar uma fórmula de matriz de uma célula

Na célula D13 da pasta de trabalho, digite a seguinte fórmula e pressione Ctrl + Shift + Enter:

=SOMA(C2:C11*D2:D11)

Nesse caso, o Excel multiplica os valores na matriz (o intervalo de células C2 a D11) e, em seguida, usa a função somapara adicionar os totais juntos. O resultado é um total geral de R$ 1.590.000,00 em vendas. Esse exemplo mostra como esse tipo de fórmula pode ser eficiente. Por exemplo, vamos supor que você tenha 1.000 linhas de dados. Você pode somar esses dados no todo ou em parte, criando uma fórmula de matriz em uma única célula, em vez de arrastar a fórmula pelas 1.000 linhas.

Além disso, observe que a fórmula de célula única na célula D13 é completamente independente da fórmula de várias células (a fórmula nas células E2 a E11). Esta é outra vantagem de usar fórmulas de matriz : a flexibilidade. Você pode alterar as fórmulas na coluna E ou excluir essa coluna completamente, sem afetar a fórmula no D13.

As fórmulas de matriz também oferecem estas vantagens:

  • Consistência    Se clicar em qualquer das células de E2 para baixo, você verá a mesma fórmula. Essa consistência pode ajudar a garantir mais precisão.

  • Segurança    Não é possível substituir um componente de uma fórmula de matriz de várias células. Por exemplo, clique na célula E3 e pressione delete. Você precisará selecionar o intervalo de células inteiro (E2 a E11) e alterar a fórmula da matriz inteira ou então deixar a matriz como está. Como medida de segurança adicional, você precisa pressionar Ctrl + Shift + Enter para confirmar qualquer alteração na fórmula.

  • Arquivos menores    Você pode usar uma única fórmula de matriz em vez de várias fórmulas intermediárias. Por exemplo, a pasta de trabalho usa uma fórmula de matriz para calcular os resultados na coluna E. Se tivesse usado fórmulas padrão (como =C2*D2, C3*D3, C4*D4…), você teria usado 11 fórmulas diferentes para calcular os mesmos resultados.

Em geral, as fórmulas de matriz usam a sintaxe padrão de fórmulas. Todas elas começam com um sinal de igual (=), e a maioria das funções internas do Excel pode ser usada nessas fórmulas. A diferença principal é que, ao usar uma fórmula de matriz, você pressiona Ctrl + Shift + Enter para inserir a fórmula. Quando você faz isso, o Excel coloca a fórmula de matriz entre chaves — se você digitar as chaves manualmente, a fórmula será convertida em uma cadeia de texto e não funcionará.

As funções de matriz podem ser uma maneira eficiente de criar fórmulas complexas. A fórmula de matriz =SOMA(C2:C11*D2:D11) é igual a esta: =SOMA(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11).

Importante: Pressione Ctrl + Shift + Enter sempre que precisar inserir uma fórmula de matriz. Isso se aplica tanto a fórmulas de célula única como de várias células.

Sempre que trabalhar com fórmulas de várias células, também lembre-se do seguinte:

  • Selecione o intervalo de células que receberá os resultados antes de inserir a fórmula. Você fez isso quando criou a fórmula de várias células selecionando as células E2 a E11.

  • Não é possível alterar o conteúdo de uma célula individual em uma fórmula de matriz. Para testar isso, selecione a célula E3 na pasta de trabalho e pressione Delete. O Excel exibirá uma mensagem informando que não é possível alterar parte de uma matriz.

  • Você pode mover ou excluir uma fórmula de matriz inteira, mas não pode mover nem excluir parte dela. Em outras palavras, para reduzir uma fórmula de matriz, primeiro exclua a fórmula existente e depois comece do zero.

  • Para excluir uma fórmula de matriz, selecione todo o intervalo de fórmulas (por exemplo, E2: E11) e pressione delete.

  • Não é possível inserir células em branco em ou excluir células de uma fórmula de matriz de várias células.

Às vezes, poderá ser necessário expandir uma fórmula de matriz. Selecione a primeira célula no intervalo de matriz existente e continue até selecionar todo o intervalo para o qual você deseja prorrogar a fórmula. Pressione F2 para editar a fórmula e, em seguida, pressione Ctrl + Shift + Enter para confirmar a fórmula depois de ajustar o intervalo de fórmulas. A chave é selecionar todo o intervalo, começando com a célula superior esquerda na matriz. A célula superior esquerda é aquela que é editada.

As fórmulas de matriz são excelentes, mas também têm algumas desvantagens:

  • Ocasionalmente, você pode esquecer de pressionar Ctrl + Shift + Enter. Isso pode acontecer com os usuários mais experientes do Excel. Lembre-se de pressionar essa combinação de teclas sempre que inserir ou editar uma fórmula de matriz.

  • É possível que outros usuários da pasta de trabalho não entendam suas fórmulas. Na prática, normalmente as fórmulas de matriz não são explicadas em uma planilha. Portanto, se outras pessoas precisarem modificar suas pastas de trabalho, você deve evitar fórmulas de matriz ou garantir que as pessoas saibam sobre as fórmulas de matriz e entender como alterá-las, se precisarem.

  • Dependendo da velocidade de processamento e da memória do computador, fórmulas de matriz grandes podem reduzir a velocidade dos cálculos.

As constantes de matriz são um componente das fórmulas de matriz. Para criar essas constantes, insira uma lista de itens e coloque-a entre chaves ({ }) manualmente, da seguinte maneira:

={1,2,3,4,5}

Agora, você precisa pressionar Ctrl + Shift + Enter quando criar fórmulas de matriz. Como as constantes de matriz são um componente dessas fórmulas, coloque as constantes entre chaves manualmente digitando-as. Em seguida, use Ctrl + Shift + Enter para inserir a fórmula inteira.

Se você separar os itens com vírgulas, será criada uma matriz horizontal (uma linha). Se você separar os itens com ponto-e-vírgulas, será criada uma matriz vertical (uma coluna). Para criar uma matriz bidimensional, delimite os itens em cada linha usando vírgulas e delimite cada linha usando ponto-e-vírgulas.

Aqui está uma matriz em uma única linha: {1, 2, 3, 4}. Está é uma matriz em uma única coluna: {1;2;3;4}. E esta é uma matriz de duas linhas e quatro colunas: {1,2,3,4;5,6,7,8}. Na matriz de duas linhas, a primeira linha é 1, 2, 3 e 4, e a segunda linha é 5, 6, 7 e 8. Um ponto-e-vírgula separa as duas linhas, entre 4 e 5.

Como nas fórmulas de matriz, você pode usar constantes de matriz com a maioria das funções internas do Excel. As seções a seguir explicam como criar cada tipo de constante e como usá-las com funções no Excel.

Os procedimentos a seguir oferecem alguma prática na criação de constantes horizontais, verticais e bidimensionais.

Criar uma constante horizontal

  1. Em uma planilha em branco, selecione as células de a1 a E1.

  2. Na barra de fórmulas, insira a seguinte fórmula e pressione Ctrl + Shift + Enter:

    ={1,2,3,4,5}

    Nesse caso, você deve digitar as chaves de abertura e fechamento ({}) e o Excel adicionará o segundo conjunto para você.

    O seguinte resultado será exibido:

    Constante de matriz horizontal em uma fórmula de matriz

Criar uma constante vertical

  1. Na pasta de trabalho, selecione uma coluna de cinco células.

  2. Na barra de fórmulas, insira a seguinte fórmula e pressione Ctrl + Shift + Enter:

    ={1;2;3;4;5}

    O seguinte resultado será exibido:

    Constante de matriz vertical em uma fórmula de matriz

Criar uma constante bidimensional

  1. Na pasta de trabalho, selecione um bloco de células de quatro colunas de largura e três linhas de altura.

  2. Na barra de fórmulas, insira a seguinte fórmula e pressione Ctrl + Shift + Enter:

    ={1,2,3,4;5,6,7,8;9,10,11,12}

    O seguinte resultado será exibido:

    Constante de matriz bidimensional em uma fórmula de matriz

Usar constantes em fórmulas

Este é um exemplo simples que usa constantes:

  1. Na pasta de trabalho de exemplo, crie uma nova planilha.

  2. Na célula A1, digite 3 e depois digite 4 em B1, 5 em C1, 6 em D1 e 7 em E1.

  3. Na célula a3, digite a seguinte fórmula e pressione Ctrl + Shift + Enter:

    =SOMA(A1:E1*{1,2,3,4,5})

    Observe que o Excel colocará a constante entre um segundo par de chaves, porque você a digitou como uma fórmula de matriz.

    Fórmula de matriz com constante de matriz

    O valor 85 será exibido na célula A3.

A próxima seção explica como a fórmula funciona.

A fórmula que você acabou de usar contém várias partes.

Sintaxe de fórmula de matriz com constante de matriz

1. Função

2. Matriz armazenada

3. Operador

4. Constante de matriz

O último elemento dentro dos parênteses é a constante de matriz: {1,2,3,4,5}. Lembre-se de que o Excel não coloca as constantes de matriz entre chaves; você deve digitá-las. Lembre-se também de que depois de adicionar uma constante a uma fórmula de matriz, pressione Ctrl + Shift + Enter para inserir a fórmula.

Como o Excel executa as operações das expressões entre parênteses primeiro, os próximos dois elementos a serem processados são os valores armazenados na pasta de trabalho (A1:E1) e o operador. Neste ponto, a fórmula multiplica os valores da matriz armazenada pelos valores correspondentes da constante. Isso equivale a:

=SOMA(A1*1,B1*2,C1*3,D1*4,E1*5)

Por fim, a função SOMA somará os valores, e o resultado 85 será exibido na célula A3.

Para evitar usar a matriz armazenada e manter a operação inteiramente na memória, substitua a matriz armazenada por outra constante de matriz:

=SOMA({3,4,5,6,7}*{1,2,3,4,5})

Para experimentar isso, copie a função, selecione uma célula em branco na pasta de trabalho, Cole a fórmula na barra de fórmulas e pressione Ctrl + Shift + Enter. Você verá o mesmo resultado do exercício anterior que usou a fórmula de matriz:

=SOMA(A1:E1*{1,2,3,4,5})

As constantes de matriz podem conter números, texto, valores lógicos (como VERDADEIRO e FALSO) e valores de erro (como #N/D). Você pode usar números nos formatos inteiro, decimal e científico. Se você incluir texto, será necessário colocá-lo entre aspas (").

As constantes de matriz não podem conter outras matrizes, fórmulas ou funções. Em outras palavras, elas só podem conter texto ou números separados por vírgula ou ponto-e-vírgula. O Excel exibirá uma mensagem de aviso quando você inserir uma fórmula como {1,2,A1:D4} ou {1,2,SOMA(Q2:Z8)}. Além disso, valores numéricos não podem conter sinais de percentual, cifrões, vírgulas ou parênteses.

Uma das melhores maneiras de usar constantes de matriz é nomeá-las. As constantes nomeadas podem ser mais fáceis de usar e ocultam um pouco da complexidade das fórmulas de matriz. Para nomear uma constante de matriz e usá-la em uma fórmula, siga este procedimento:

  1. Na guia Fórmulas, no grupo Nomes Definidos, clique em Definir Nome.
    A caixa de diálogo definir nome será exibida.

  2. Na caixa Nome, digiteTrimestre1.

  3. Na caixa Refere-se a, insira a seguinte constante (lembre-se de digitar as chaves manualmente):

    ={"Janeiro","Fevereiro","Março"}

    O conteúdo da caixa de diálogo agora tem esta aparência:

    Caixa de diálogo Editar Nome com fórmula

  4. Clique em OK e selecione uma linha de três células em branco.

  5. Digite a seguinte fórmula e pressione Ctrl + Shift + Enter.

    =Trimestre1

    O seguinte resultado será exibido:

    Constante de matriz nomeada inserida como fórmula

Quando você usar uma constante nomeada como uma fórmula de matriz, lembre-se de digitar o sinal de igual. Caso contrário, o Excel interpretará a matriz como uma cadeia de texto, e a fórmula não funcionará conforme esperado. Por fim, lembre-se de que você pode usar combinações de texto e números.

Procure os seguintes problemas quando suas constantes de matriz não funcionarem:

  • Talvez alguns elementos não estejam separados pelo caractere correto. Se você omitir uma vírgula ou ponto-e-vírgula, ou se colocar um no lugar errado, a constante de matriz pode não ser criada corretamente ou talvez você veja uma mensagem de aviso.

  • Talvez você tenha selecionado um intervalo de células que não corresponda ao número de elementos da constante. Por exemplo, se você selecionar uma coluna de seis células para ser usada com uma constante de cinco células, o valor de erro #N/D será exibido na célula vazia. De maneira inversa, se você selecionar células de menos, o Excel omitirá os valores que não tiverem uma célula correspondente.

Os exemplos a seguir demonstram algumas das formas pelas quais você pode colocar constantes de matriz para utilização em fórmulas de matriz. Alguns dos exemplos usam a função transpor para converter linhas em colunas e vice-versa.

Multiplicar cada item de uma matriz

  1. Crie uma nova planilha e depois selecione um bloco de células vazias com quatro colunas de largura e três linhas de altura.

  2. Digite a seguinte fórmula e pressione Ctrl + Shift + Enter:

    ={1,2,3,4;5,6,7,8;9,10,11,12}*2

Elevar os itens de uma matriz ao quadrado

  1. Selecione um bloco de células vazias com quatro colunas de largura e três linhas de altura.

  2. Digite a seguinte fórmula de matriz e pressione Ctrl + Shift + Enter:

    ={1,2,3,4;5,6,7,8;9,10,11,12}*{1,2,3,4;5,6,7,8;9,10,11,12}

    Como alternativa, digite esta fórmula de matriz, que utiliza o operador circunflexo (^):

    ={1,2,3,4;5,6,7,8;9,10,11,12}^2

Transpor uma linha unidimensional

  1. Selecione uma coluna de cinco células em branco.

  2. Digite a seguinte fórmula e pressione Ctrl + Shift + Enter:

    =TRANSPOR({1,2,3,4,5})

    Embora você tenha inserido uma constante de matriz horizontal, a função TRANSPOR converterá a constante em uma coluna.

Transpor uma coluna unidimensional

  1. Selecione uma linha de cinco células em branco.

  2. Digite a seguinte fórmula e pressione Ctrl + Shift + Enter:

    =TRANSPOR({1;2;3;4;5})

Embora você tenha inserido uma constante de matriz vertical, a função TRANSPOR converterá a constante em uma linha.

Transpor uma constante bidimensional

  1. Selecione um bloco de células com três colunas de largura e quatro linhas de altura.

  2. Digite a seguinte constante e pressione Ctrl + Shift + Enter:

    =TRANSPOR({1,2,3,4;5,6,7,8;9,10,11,12})

    A função TRANSPOR converterá cada linha em uma série de colunas.

Esta seção contém exemplos de fórmulas de matriz básicas.

Criar matrizes e constantes de matriz com base em valores existentes

O exemplo a seguir explica como usar fórmulas de matriz para criar vínculos entre intervalos de células em planilhas diferentes. Também mostra como criar uma constante de matriz com base no mesmo conjunto de valores.

Criar uma matriz com base em valores existentes

  1. Em uma planilha no Excel, selecione as células C8:E10 e insira esta fórmula:

    ={10,20,30;40,50,60;70,80,90}

    Não deixe de digitar { (chave de abertura) antes de digitar 10 e } (chave de fechamento) depois de digitar 90, pois você está criando uma matriz de números.

  2. Pressione Ctrl + Shift + Enter, que insere essa matriz de números no intervalo de células C8: E10 usando uma fórmula de matriz. Em sua planilha, C8 a E10 deve ter a seguinte aparência:

    10

    20

    30

    40

    50

    60

    70

    80

    90

  3. Selecione o intervalo de células C1 a E3.

  4. Digite a seguinte fórmula na barra de fórmulas e pressione Ctrl + Shift + Enter:

    =C8:E10

    Uma matriz 3x3 de células é exibida nas células de C1 a E3 com os mesmos valores que você vê no C8 a E10.

Criar uma constante de matriz com base em valores existentes

  1. Com as células C1: C3 selecionadas, pressione F2 para alternar para o modo de edição. 

  2. Pressione F9 para converter as referências de célula em valores. O Excel converterá os valores em uma constante de matriz. A fórmula agora deve ser = {10, 20, 30; 40, 50, 60; 70, 80, 90}.

  3. Pressione Ctrl + Shift + Enter para inserir a constante de matriz como uma fórmula de matriz.

Contar caracteres em um intervalo de células

O exemplo a seguir mostra como contar o número de caracteres, inclusive espaços, em um intervalo de células.

  1. Copie esta tabela inteira e cole-a em na célula A1 de uma planilha.

    Dados

    Isto é um

    monte de células que

    se juntam

    para formar uma

    única sentença.

    Total de caracteres em A2:A6

    =SOMA(NÚM.CARACT(A2:A6))

    Conteúdo da maior célula (A3)

    =ÍNDICE(A6:A9,CORRESP(MÁX(NÚM.CARACT(A6:A9)),NÚM.CARACT(A6:A9),0),1)

  2. Selecione a célula A8 e pressione Ctrl + Shift + Enter para ver o número total de caracteres nas células a2: A6 (66).

  3. Selecione a célula A10 e, em seguida, pressione Ctrl + Shift + Enter para ver o conteúdo das células mais longas a2: A6 (célula a3).

A fórmula a seguir é usada na célula A8 conta o número total de caracteres (66) nas células de a2 a A6.

=SOMA(NÚM.CARACT(A2:A6))

Neste caso, a função NÚM.CARACT retornará o comprimento de cada cadeia de texto em cada uma das células do intervalo. A função soma , em seguida, adiciona esses valores juntos e exibe o resultado (66).

Localizar os n menores valores de um intervalo

Este exemplo mostra como localizar os três menores valores de um intervalo de células.

  1. Digite alguns números aleatórios nas células A1: a11.

  2. Selecione as células de C1 a C3. Esse conjunto de células conterá os resultados retornados pela fórmula de matriz.

  3. Digite a seguinte fórmula e pressione Ctrl + Shift + Enter:

    = PEQUENO (A1: A11, {1; 2; 3})

Essa fórmula usa uma constante de matriz para avaliar a função pequeno três vezes e retornar o menor (1), o segundo menor (2) e o terceiro (3) membros da matriz que está contido nas células A1: A10 para localizar mais valores, você adiciona mais argumentos ao constantes. Você também pode usar funções adicionais com essa fórmula, como SOMA ou MÉDIA. Por exemplo:

= SOMA (PEQUENO (A1: A10, {1, 2, 3})

= MÉDIA (PEQUENO (A1: A10, {1, 2, 3})

Localizar os n maiores valores de um intervalo

Para localizar os maiores valores de um intervalo, você pode substituir a função MENOR pela função MAIOR. Além disso, o exemplo a seguir usa as funções LINHA e INDIRETO.

  1. Selecione as células de D1 a D3.

  2. Na barra de fórmulas, digite essa fórmula e pressione Ctrl + Shift + Enter:

    = MAIOR (A1: A10, LINHA (INDIRETO ("1:3")))

Neste ponto, pode ser útil saber um pouco sobre as funções LINHA e INDIRETO. Você pode usar a função LINHA para criar uma matriz de números inteiros consecutivos. Por exemplo, selecione uma coluna vazia de 10 células na sua pasta de trabalho prática, insira esta fórmula de matriz e pressione Ctrl + Shift + Enter:

=LINHA(1:10)

A fórmula cria uma coluna de 10 inteiros consecutivos. Para ver um possível problema, insira uma linha acima do intervalo que contém a fórmula de matriz (ou seja, acima da linha 1). O Excel ajustará as referências de linha e a fórmula irá gerar inteiros de 2 a 11. Para corrigir esse problema, adicione a função INDIRETO à fórmula:

=LINHA(INDIRETO("1:10"))

A função INDIRETO usa cadeias de texto como argumentos (é por isso que o intervalo 1:10 é colocado entre aspas duplas). O Excel não ajusta valores de texto quando você insere linhas ou move a fórmula de matriz de alguma outra maneira. Como resultado, a função LINHA sempre gera a matriz de inteiros desejada.

Vamos dar uma olhada na fórmula que você usou anteriormente — = maior (A5: a14, linha (indireto ("1:3")) ) — começando pelos parênteses internos e trabalhando para fora: a função indireto retorna um conjunto de valores de texto, nesse caso, os valores de 1 a 3. A função Lin , por sua vez, gera uma matriz de colunas de três células. A função grande usa os valores no intervalo de células a5: a14, e é avaliada três vezes, uma vez para cada referência retornada pela função Lin . Os valores 3200, 2700 e 2000 são retornados para a matriz de colunas de três células. Se você quiser encontrar mais valores, adicione um intervalo de células maior à função indireto .

Assim como nos exemplos anteriores, você pode usar essa fórmula com outras funções, como soma e média.

Localizar a cadeia de texto mais longa de um intervalo de células

Volte para o exemplo de cadeia de caracteres de texto anterior, insira a fórmula a seguir em uma célula vazia e pressione Ctrl + Shift + Enter:

=ÍNDICE(A6:A9,CORRESP(MÁX(NÚM.CARACT(A6:A9)),NÚM.CARACT(A6:A9),0),1)

O texto "uma série de células" é exibido.

Vamos examinar mais detalhadamente a fórmula, começando dos elementos internos para os externos. A função Len retorna o comprimento de cada um dos itens no intervalo de células a2: A6. A função máximo calcula o maior valor entre esses itens, que corresponde à cadeia de texto mais longa, que está na célula a3.

É neste ponto que as coisas ficam um pouco mais complexas. A função CORRESP calcula o deslocamento (a posição relativa) da célula que contém a cadeia de texto mais longa. Para fazer isso, são necessários três argumentos: um valor de pesquisa, uma matriz de pesquisa e um tipo de correspondência. A função CORRESP procura na matriz de pesquisa o valor de pesquisa especificado. Neste caso, o valor de pesquisa é a cadeia de texto mais longa:

(MAX (LEN (A2: A6))

e essa cadeia de caracteres reside nesta matriz:

LEN (A2: A6)

O argumento tipo de correspondência é 0. O tipo de correspondência pode consistir em um valor 1, 0 ou -1. Se você especificar 1, CORRESP retornará o maior valor que seja menor ou igual ao valor de pesquisa. Se você especificar 0, CORRESP retornará o primeiro valor que seja exatamente igual ao valor de pesquisa. Se você especificar -1, CORRESP localizará o menor valor que seja maior ou igual ao valor de pesquisa especificado. Se você omitir um tipo de correspondência, o Excel adotará o valor 1.

Por fim, a função ÍNDICE opera com estes argumentos: uma matriz e um número de linha e de coluna nessa matriz. O intervalo de células a2: A6 fornece a matriz, a função CORRESP fornece o endereço da célula, e o argumento final (1) especifica que o valor vem da primeira coluna na matriz.

Esta seção contém exemplos de fórmulas de matriz avançadas.

Somar um intervalo que contém valores de erro

A função SOMA do Excel não funciona quando você tenta somar um intervalo que contém um valor de erro, como #N/D. Este exemplo mostra como somar os valores em um intervalo chamado Dados que contém erros.

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

A fórmula cria uma nova matriz contendo os valores originais menos quaisquer valores de erro. Das funções internas para as externas, a função ÉERROS pesquisa o intervalo de células (Dados) em busca de erros. A função SE retornará um valor específico se uma condição especificada for avaliada como VERDADEIRO e outro valor se ela for avaliada como FALSO. Neste caso, ela retorna cadeias de caracteres vazias ("") para todos os valores de erro, pois eles são avaliados como VERDADEIRO, e retorna os valores restantes do intervalo (Dados), pois eles são avaliados como FALSO, o que significa que eles não contêm valores de erro. Em seguida, a função SOMA calcula o total da matriz filtrada.

Contar o número de valores de erro em um intervalo

Este exemplo é semelhante à fórmula anterior, mas retorna o número de valores de erro em um intervalo chamado Dados em vez de filtrá-los:

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

Essa fórmula cria uma matriz contendo o valor 1 para as células que contêm erros e o valor 0 para as que não contêm erros. Você pode simplificar a fórmula e obter o mesmo resultado removendo o terceiro argumento da função SE, da seguinte maneira:

SOMA(SE(ÉERROS(Dados),1))

Se você não especificar o argumento, a função SE retornará FALSO se uma célula não contiver um valor de erro. Você pode simplificar a fórmula ainda mais:

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

Essa versão funciona porque VERDADEIRO*1=1 e FALSO*1=0.

Somar valores com base em condições

Talvez seja necessário somar valores com base em condições. Por exemplo, esta fórmula de matriz soma apenas os inteiros positivos em um intervalo chamado Vendas:

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

A função SE cria uma matriz de valores positivos e falsos. A função SOMA essencialmente ignora os falsos valores, pois 0+0=0. O intervalo de células que você usa nessa fórmula pode consistir em qualquer número de linhas e colunas.

Você também pode somar valores que atendam a mais de uma condição. Por exemplo, esta fórmula de matriz calcula valores maiores que 0 e menores ou iguais a 5:

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

Lembre-se de que essa fórmula retornará um erro se o intervalo contiver uma ou mais células não numéricas.

Você também pode criar fórmulas de matriz que usem um tipo de condição OU. Por exemplo, você pode somar valores que sejam menores que 5 e maiores que 15:

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

A função SE localiza todos os valores menores que 5 e maiores que 15 e passa esses valores para a função SOMA.

Não é possível usar as funções E e OU diretamente em fórmulas de matriz, pois essas funções retornam um único resultado, VERDADEIRO ou FALSO, e as funções de matriz exigem matrizes de resultados. Você pode contornar o problema usando a lógica mostrada na fórmula anterior. Em outras palavras, execute operações matemáticas, como adição ou multiplicação, em valores que atendam à condição OU ou E.

Calcular uma média que exclua zeros

Este exemplo mostra como remover zeros de um intervalo quando você precisar calcular a média dos valores desse intervalo. A fórmula usa um intervalo de dados chamado Vendas:

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

A função SE cria uma matriz de valores que não são iguais a 0 e passa esses valores para a função MÉDIA.

Contar o número de diferenças entre dois intervalos de células

Esta fórmula de matriz compara os valores de dois intervalos de células chamados MeusDados e SeusDados e retorna o número de diferenças entre os dois. Se o conteúdo dos dois intervalos for idêntico, a fórmula retornará 0. Para usar esta fórmula, os intervalos de células devem ter o mesmo tamanho e a mesma dimensão (por exemplo, se MeusDados for um intervalo de 3 linhas por 5 colunas, o intervalo SeusDados deverá ter o mesmo tamanho):

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

A fórmula criará uma nova matriz do mesmo tamanho dos intervalos que você está comparando. A função SE preencherá a matriz com o valor 0 e o valor 1 (0 para células que não correspondam e 1 para células idênticas). Em seguida, a função SOMA retornará a soma dos valores da matriz.

Você pode simplificar a fórmula deste modo:

= SOMA (1 * (MyData<>YourData))

Como a fórmula que conta valores de erro em um intervalo, essa fórmula funciona porque VERDADEIRO*1=1 e FALSO*1=0.

Localizar o valor máximo em um intervalo

Esta fórmula de matriz retorna o número de linha do valor máximo em um intervalo de uma única coluna chamado Dados:

=MÍN(SE(Dados=MÁX(Dados),LINHA(Dados),""))

A função SE cria uma nova matriz que corresponde ao intervalo chamado Dados. Se uma célula correspondente contiver o valor máximo no intervalo, a matriz conterá o número da linha. Caso contrário, a matriz conterá uma cadeia de caracteres vazia (""). A função MÍN usa a nova matriz como segundo argumento e retorna o menor valor, que corresponde ao número da linha do valor máximo em Dados. Se o intervalo chamado Dados contiver valores máximos idênticos, a fórmula retornará a linha do primeiro valor.

Para retornar o endereço de célula real de um valor máximo, use esta fórmula:

=ENDEREÇO(MÍN(SE(Dados=MÁX(Dados),LINHA(Dados),"")),COLUNA(Dados))

Confirmação

Partes deste artigo se baseavam em uma série de colunas de usuários avançados do Excel escritas por Carlos Lacerda e adaptadas dos capítulos 14 e 15 das fórmulas do Excel 2002, um livro escrito por John Walkenbach, um antigo MVP do Excel.

Precisa de mais ajuda?

Você pode sempre consultar um especialista na Excel Tech Community, obter suporte na Comunidade de respostas ou sugerir um novo recurso ou melhoria no User Voice do Excel.

Confira também

Comportamento de matrizes despejadas e matrizes dinâmicas

Fórmulas de matriz dinâmica versus fórmulas de matriz de CSE herdadas

Função FILTRO

Função RANDARRAY

Função SEQUÊNCIA

Função SIMPLES

Função CLASSIFICAR

Função CLASSIFICARPOR

Função ÚNICO

Erros #DESPEJAR! no Excel

Visão geral das fórmulas

Expanda suas habilidades no Office
Explore o treinamento
Obtenha novos recursos primeiro
Ingressar no Office Insider

Essas informações foram úteis?

Obrigado por seus comentários!

Agradecemos pelos seus comentários! Parece que pode ser útil conectar você a um de nossos agentes de suporte do Office.

×