Fórmulas de Matriz do Excel

Por Tudo Excel | 11 set 2013 | 51756 acessos

Talvez uma das mais interessantes funções 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, pagamos 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, pegam 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.

Clique para ampliar:

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 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 de matriz

As fórmulas de matriz 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.

 

 

 

Gostaríamos de saber se ficou alguma dúvida sobre: Fórmulas de Matriz do Excel. Por favor deixe seu comentário, ou faça sua pergunta.

 

 

Envie um comentário, ou faça uma pergunta:
 

 

14 Comments até agora. Sinta-se livre para se juntar a esta conversa.

  1. Nelson Neiva 25/07/2016 at 15:18 - Reply

    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?

  2. Xavier 08/05/2016 at 15:44 - Reply

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

    • Tudo Excel 09/05/2016 at 18:59 - Reply

      Funções SOMASE, SE, ou PROCV, use uma dessas, pode ser útil

  3. Priscila 13/03/2016 at 20:54 - Reply

    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!

  4. Andre Areas 24/02/2016 at 21:52 - Reply

    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

    • Tudo Excel 24/02/2016 at 22:10 - Reply

      sim, é possível utilizando fórmulas do Excel, que fazem o cálculo, de quantidades disponíveis

  5. Claudir Schneider 19/02/2016 at 22:58 - Reply

    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.

    • Tudo Excel 24/02/2016 at 22:12 - Reply

      qual é o banco de dados que está trabalhando?

  6. Simão Sampaio 01/02/2016 at 16:02 - Reply

    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

    • Tudo Excel 05/02/2016 at 18:14 - Reply

      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

  7. Amanda 04/01/2016 at 19:34 - Reply

    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

    • Tudo Excel 09/01/2016 at 09:33 - Reply

      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.

  8. Tiago Dias 25/11/2015 at 15:22 - Reply

    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.