0064-Fórmulas matriciais

As fórmulas matriciais permitem calcular resultados que de outra forma necessitariam de células auxiliares para realização de cálculos intermediários.

Vamos a um exemplo simples:

Determinar a soma das diferenças entres os elementos das colunas A e B.

O pensamento que logo vem à mente é criar uma coluna auxiliar e nela calcularmos cada elemento da série de diferenças, começando com A2-B2 e seguindo até A6-B6. Em seguida faríamos a soma dos valores desta coluna.

Não há nenhum erro em pensar desta forma. Mas podemos simplificar o nosso trabalho, se utilizarmos uma fórmula matricial simples. Nesta fórmula calcularemos as diferenças e em seguida a soma das diferenças reunindo-as como argumentos da função. Para o caso em questão faremos: =SOMA($A$2:$A$6-$B$2:$B$6), pressionando em seguida CTRL+SHFT+ENTER para que o Excel interprete-a como uma fórmula matricial. Se clicarmos na célula veremos na barra de fórmulas que ela aparece na forma {}. As chaves indicam que se trata de uma fórmula matricial.

Observe os argumentos da fórmula:

$A$2:$A$6-$B$2:$B$6, se selecionarmos este trecho com o mouse na barra de fórmulas e pressionarmos F9, vamos obter o resultado {1;8;10;7;10}. Esta é uma matriz que corresponde à série de diferenças entre os elementos das colunas A e B no intervalo entre as linhas 2 e 6. Fica então mais claro o porque do funcionamento das fórmulas matriciais: elas simulam o funcionamento das operações de células e utilizam estes dados como argumentos de uma função. Se não pressionarmos a combinação CTRL+SHFT+ENTER, será exibida uma mensagem de erro, ou a fórmula será calculada considerando apenas o primeiro elemento da matriz, dependendo das funções utilizadas para sua construção.

Utilizando a mesma série de dados, podemos calcular a soma do quadrado das diferenças, utilizando a fórmula =SOMA(($A$2:$A$6-$B$2:$B$6)^2).

Ou ainda, a soma das diferenças ao quadrado =SOMA(($A$2:$A$6)^2-($B$2:$B$6)^2)

Outra aplicação das fórmulas matriciais, é a realização de operações de soma e contagem condicionais.

Na série de dados acima, qual a soma dos valores que possuem o rótulo “a”?

A resposta através de fórmula matricial pode ser dada por: =SOMA(SE($A$2:$A$11=”a”;$B$2:$B$11)).

Esta fórmula pode ser interpretada como: “Somar os elementos do intervalo B2:B11 que possuem valor “a” na linha correspondente no intervalo A2:A11.

E como poderíamos contar quantos elementos possuem o rótulo “b”?

A resposta pode ser calculada como: =SOMA(SE($A$2:$A$11=”b”;1)).

O raciocínio é análogo ao empregado anteriormente: “A cada valor do intervalo A2:A11 que contiver o valor “b”, considere o valor 1, somando todos os resultados obtidos”.

Vamos concluir com dois exemplos de fórmulas matriciais que respondam à questão:

Quantas ocorrências temos de um determinado caractere num texto?

Já havíamos respondido anteriormente utilizando uma UDF (Funções personalizadas de texto), mas agora vamos abordar o problema por meio de fórmulas matriciais.

Primeiramente necessitamos encontrar uma forma de retornar um caractere por vez de um texto. Temos a função EXT.TEXTO que nos permite retornar um número definido de caracteres a partir de uma dada posição no texto pesquisado.

Ou seja, precisamos retornar cada elemento da série EXT.TEXTO(texto;posição,1), sendo texto o argumento que indica o texto pesquisado e posição um número que varia de 1 até o total de caracteres de texto (e que pode ser calculado através da função =NÚM.CARACT(texto).

Eis aqui um macete que aprendi há muito tempo: para retornar um sequência numérica que se inicia em 1 e vai até um 10, por exemplo, podemos utilizar a fórmula LIN(INDIRETO(“A1:A10”)). O resultado desta fórmula é {1;2;3;4;5;6;7;8;9;10}. Se houver valores na coluna A, utilize uma vazia, como a Z por exemplo.

No nosso exemplo, vamos precisar retornar a série de vai de 1 até a o total de caracteres de texto, de forma que vamos utilizar a fórmula LIN(INDIRETO(“A1:A”&NÚM.CARACT(texto))).

Temos então uma forma de retonar cada caractere de texto, utilizando a fórmula

EXT.TEXTO(texto;LIN(INDIRETO(“A1:A”&NÚM.CARACT(texto)));1).

Se o valor de texto for “Excel”, a fórmula retorna a série {“E”;”x”;”c”;”e”;”l”}

Agora podemos comparar cada um dos caracteres com o caractere que queremos pesquisar, utilizando a fórmula

EXT.TEXTO(texto;LIN(INDIRETO(“A1:A”&NÚM.CARACT(texto)));1) = caractere

O resultado desta comparação é uma matriz na qual correspondências ok correspondem ao valor VERDADEIRO e quando não há correspondência, obtermos FALSO. Se buscássemos a letra “e” no nome “Excel” obteríamos:

{VERDADEIRO;FALSO;FALSO;VERDADEIRO;FALSO}

Lembro que nestas comparações, não diferenciação entre maiúsculas e minúsculas.

Antes e procedermos à soma, precisamos transformar os valores VERDADEIRO em 1 e os FALSO em 0, de forma que a soma corresponderá ao total de coincidências obtidas. A função N do Excel, faz exatamente isso. Temos então:

N(EXT.TEXTO(texto;LIN(INDIRETO(“A1:A”&NÚM.CARACT(texto)));1) = caractere)

Aplicando ao exemplo anterior teríamos a matriz:

{1;0;0;1;0}

Finalmente podemos completar a nossa fórmula matricial, concluindo-a com uma função SOMA.

SOMA(N(EXT.TEXTO(texto;LIN(INDIRETO(“A1:A”&NÚM.CARACT(texto)));1) = caractere))

Não esquecendo de digitar CTRL+SHFT+ENTER para inserí-la como fórmula matricial.

Uma última pergunta: E se quiséssemos uma função que diferenciasse maiúsculas e minúsculas?

Veja os exemplos deste post e a resposta a esta pergunta aqui: Fórmulas matriciais

Anúncios

In Excel We Trust

Marcado com: , ,
Publicado em Nível: Avançado
4 comentários em “0064-Fórmulas matriciais
  1. Flavio disse:

    tá aí outro excelente tutorial !

  2. Erislandia Lima Araujo disse:

    gostei, como faço para saber subtrair e dividir mutiplicar entre colunas?

    • Boa noite, Erislândia.
      Digamos que você queira realizar as quatro operações básicas entre os elementos dos intervalos A1:A10 e B1:B10
      ADIÇÃO: {=SOMA(A1:A10 + B1:B10)}
      SUBTRAÇÃO: {=SOMA(A1:A10 – B1:B10)}
      MULTIPLICAÇÃO: {=SOMA(A1:A10 * B1:B10)}
      DIVISÃO: {=SOMA(A1:A10 / B1:B10)}

      As chaves indicam que são fórmulas matriciais e devem ser inseridas pressionando-se CTRL+SHFT+ENTER.

      [ ]s

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

Visitas dos usuários
  • 2,074,857

Clique para assinar este blog e receber notificações de novos artigos por email.

Junte-se a 1.536 outros seguidores

Aumente sua interação

Siga-nos do twitter (@usuariosdoexcel).

Acesse o nosso fórum e compartilhe as suas dúvidas.

Curta nossa página no Facebook.

Junte-se ao grupo do LinkedIn.

Visite-nos utilizando o seu smartphone ou tablet.

Recomende este Blog
Visualizar notícias
Site monitorado:
Real Time Web Analytics

Clicky

%d blogueiros gostam disto: