Fórmulas de Matriz do Excel, Como Usar na Planilha

Essencialmente uma das ações mais pertinentes que o Excel desempenha é o cálculo com fórmulas de matriz. Para se tornar um usuário avançado do Excel, você precisa saber como usar as fórmulas de matriz, que podem executar cálculos que não podem ser feitos através de fórmulas não-matriz.

Por que usar as fórmulas de matriz?

Essa fórmula calcula apenas valores preenchidos em um intervalo, quando se trata de mínimo, média e máximo, entre outras inúmeras opções de cálculos e de informações.

Se você tem experiência com fórmulas do Excel, sabe que pode executar operações um tanto sofisticadas. Por exemplo, você pode calcular o custo total de um empréstimo em um dado número de anos. No entanto, se você realmente deseja conhecer a fundo as fórmulas do Excel, precisa saber como usar as fórmulas de matriz.

Use-as para realizar tarefas complexas, como:

  • Contar o número de caracteres contidos em um intervalo de células.
  • Somar apenas números que atendam a determinadas condições, como os valores inferiores de um intervalo ou os números que estão entre um limite inferior e superior.
  • Somar cada valor n em um intervalo de valores.

Para inserir uma fórmula de matriz, pressione CTRL+SHIFT+ENTER com a célula selecionada e a fórmula visualizada na célula.

Em nosso exemplo, pegamos uma planilha de cotação de preços, que contem 5 fornecedores, mas alguns fornecedores não vendem determinados itens e estas células contém uma fórmula, que pega os preços em outra tabela. Queremos saber o preço mínimo entre o intervalo, mas o valor não pode ser ZERO, nem um conteúdo de uma fórmula. É aí que utilizamos a fórmula de matriz.

Veja a imagem abaixo.

funcao-matriz-excel

 

Note que F01, F02, F03, F04 e F05 são as referências de fornecedores. Na penúltima coluna da imagem aparece o valor mínimo da linha e na última coluna os fornecedores que dispõem dos menores preços de cada item da linha.

Nota: este é um exemplo prático, para ilustrar nosso artigo sobre fórmulas de matriz. Esteja ciente que esta função tem inúmeras formas de fazer cálculos e aplicações.

Neste exemplo, usamos a seguinte função.

{=MÍNIMO(SE(E6:I6<>0; E6:I6;""))}

E na última coluna ainda usamos a função SE, para saber qual fornecedor tem o preço menor no item de cada linha. E ficou assim:

=SE(J6=0;".";SE(J6=E6;$E$4;SE(J6=F6;$F$4;SE(J6=G6;$G$4;SE(J6=H6;$H$4;SE(J6=I6;$I$4;""))))))

Você encontra esta planilha completa em: Guia de Compra, Planilha de Cotação

Uma planilha interessante para decifrar as fórmulas do Excel.

Desvantagens do uso das fórmulas matriciais

As fórmulas de matriz, ou fórmulas matriciais, podem parecer mágicas, mas elas também têm algumas desvantagens:

  • Ocasionalmente, você pode se esquecer de pressionar CTRL+SHIFT+ENTER. Lembre-se de pressionar essa combinação de teclas sempre que inserir ou editar uma fórmula de matriz.
  • Pode ser que outros usuários não compreendam suas fórmulas. As fórmulas de matriz são relativamente não-documentadas. Portanto, se outras pessoas precisarem modificar suas pastas de trabalho, evite as fórmulas de matriz ou verifique se esses usuários sabem como alterá-las.
  • Dependendo da velocidade do processamento e da memória do computador, as fórmulas de matriz grandes podem tornar os cálculos lentos.

Conheça as constantes de matriz

Esta seção fornece uma introdução às constantes de matriz e explica como inseri-las, editá-las e solucionar seus problemas.

Uma breve introdução às constantes de matriz

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

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

Anteriormente neste artigo, enfatizamos a necessidade de pressionar CTRL+SHIFT+ENTER durante a criação das fórmulas de matriz. Como as constantes de matriz são um componente das fórmulas de matriz, delimite manualmente as constantes com chaves digitando-as. Em seguida, use CTRL+SHIFT+ENTER para inserir a fórmula inteira.

Se você delimitar (separar) os itens com vírgulas, criará uma matriz horizontal (uma linha). Se você delimitar os itens com ponto-e-vírgulas, criará 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.

Assim como acontece com as fórmulas de matriz, você pode usar constantes de matriz com qualquer função interna do Excel. As seções a seguir explicam como criar cada tipo de constante e como usar essas constantes com as funções do Excel.



Confira Estes Produtos Que Eu Recomendo

Compre Planilhas de Excel

Confira os Preços

Climatizador de Ar

Condira os Preços

Notebook DELL Inspiron

Confira os Preços

Celular da Marca Xiaomi

Confira os Preços

Notebook Lenovo IdeaPad

Confira Os Preços

Smartphone Samsung Galaxy

Confira os Preços

Aspirador de Pó Robô

Confira os Preços

Os Melhores Ventiladores

Confira os Preços

Notebook Gamer

Confira os Preços

Materiais para Escritórios

Confira os Preços

Impressora Multifuncional de Tinta

Confira os Preços

Smart TV Samsung de 32 a 70

Confira os Preços

Edivaldo
Edivaldo

Edivaldo. Analista de funções e fórmulas de Excel, adoro fazer planilhas e compartilhar meus conhecimentos com as pessoas. Seja bem-vindo ao tudo Excel, onde você aprende de graça e tira suas dúvidas.

18 Comentários
Mostrar Todos Mais Útil Classificação Mais Alta Classificação Mais Baixa Adicionar a sua avaliação
  1. e se eu quisesse uma matriz coluna por até um número variável? ex.: E1 = 6. Ao invés de colocar {1;2;3;4;5;6}, como poderia colocar?

  2. Boa tarde.
    Como usar o "SE" para retornar valores quando a condição for entre 100 e 200 por exemplo.
    Para maior que 100 ou menor e para maior ou menor que 200, tranqulio.
    Minha dificuldade é trabalhar valores ENTRE 100 e 200...
    Darei mais detalhes em breve.

  3. Olá, boa tarde.
    Para criar uma constante de matriz horizontal, o Excel considera a vírgula como separador decimal, levando em conta somente a primeira vírgula. No caso de constante de matriz vertical com ponto-e-vírgula como separador funciona normalmente.
    Como proceder para matriz horizontal e bidimensional?

  4. Ola gostaria que me ajudasse, quero que o excel conte automáticamente os numeros inferiores a dez e me coloque o resultado numa celula

  5. Ola, gostaria de saber como obter a funcao MINIMO apos relacionar uma coluna de uma matriz a uma linha de outra matriz.

    Como realizar essa operacao no excel???

    Observe que o minimo deve ser gerado a partir de uma analize que o software fara entre cada relacao de cada par de elementos das respsectivas linhas e colunas das duas matrizes (Matirz relacional)

    Por favor, ajudem!

  6. Olá, minha função exige controle da quantidade de medicamentos para várias pessoas, gostaria de saber se é possivel a partir da quantidade de remédios, saber até que data ( dia / mês /ano ) eles estarão disponíveis

  7. Respostas
    Claudir Schneider 19/02/2016 as 22:58

    Olá, tudo bem
    Preciso de uma orientação ou uma formula para o seguinte. Tenho um banco de dados onde questões foram respondidas. Sendo na vertical o numero de questionários e na horizontal as respostas. Fiz o somatório referente ao valor de cada questão. Preciso agora fazer um somatório somente das questões respondidas.

  8. Olá,
    Gostaria de saber como é que consigo fazer com que o excel me dê um alerta para quando estiver a introduzir dados que já tivera anteriormente escrito.
    E que ele próprio me indique a localização dos dados.
    Se é possível claro.
    Obrigado

    • Olá, existem 4 opções para fazer isso, uma é por validação de dados, acesse Dados, Validação de dados e utilize a função =CONT.SE(xx:yy;aa)=1

  9. Olá estou precisando de ajuda para fazer uma fórmula não domino o Excel estou meio perdida. Preciso de colocar as horas de trabalho , colocar o valor que pago por hora no final preciso do total de horas e o valor a pagar por funcionário! Deste já agradeço

    • Olá. É fácil.
      Crie uma tabela com as seguintes colunas:
      CARGA HORÁRIA - digite a carga horária EX: 8
      VALOR POR HORA - digite o valor que paga por hora
      TOTAL A PAGAR - use a fórmula soma, e some valor por hora x carga horária

      Caso queira colocar horário de entrada e de saída para cada funcionário, adicione duas coluna antes das demais, com ENTRADA e SAÍDA.
      Depois some as horas na coluna CARGA HORÁRIA.
      Na sua planilha não esqeça de adicionar Nome dos funcionários, cargo e outras informações que desejar. Sempre antes das colunas que vai usar as fórmulas.

  10. Ola,

    Preciso de uma formula para usar em cotação de preços para apresentar subtotal dos menores valores praticados por cada fornecedor.
    Ja tenho formatação condicional associada a MINIMO para evidenciar os produtos com menores preços de cada fornecedor.
    Gostaria, então de conseguir somar os valores indicados pela formatação condicional de valor MINIMO para cada fornecedor.

Escreva seu Comentário

Tudo Excel
Logo