Como usar a Fórmula Matricial – Outra Bruxaria do Excel

Vamos lá. Já viu aquelas fórmulas no Excel que são fechadas com chaves? Assim: {=FÓRMULA(parâmetros)}. Isso é uma Fórmula Matricial!

Geralmente te pedem pra apertar Ctrl+Shift+Enter e pum! Temos uma fórmula matricial.

Ah não usa Excel, usa Google Planilhas? Funciona da mesma forma. A diferença é que lá é a função ARRAYFORMULA(FÓRMULA(parâmetros) )

Embora o objetivo em ambos aplicativos seja o mesmo, o comportamento da fórmula matricial em ambos é ligeiramente diferente.

Antes de Começamos, Algumas Notas

Nota 1: Fórmulas matriciais devolvem ou um valor único ou uma matriz inteira.

Seja lá o que você estiver calculando, digamos, esperar-se uma soma de valores, média ou contagem etc. você receberá um valor único como resposta.

Mas se você estiver esperando uma resposta por linha de cálculo, então, é isso que você obtém. Parece complexo mas não é.

Nota 2: No Microsoft Excel, depois que você escrever a fórmula, ao invés de você simplesmente dar um Enter, você usará a combinação de teclas Ctrl+Shift+Enter.

Se não fizer você obterá um erro, geralmente #VALOR. Se você acertou o cálculo e espera um resultado único, pronto. Aí está.

Entretanto, se você espera um range, ou seja, digamos, uma coluna inteira de valores, você pode simplesmente “puxar” a fórmula – já matricial – para o intervalo todo.

Se esqueceu de fazer o paranuê do Ctrl+Shift+Enter antes, não tem problema. Selecione o intervalo todo, aperta F2 para editar a fórmula e, então, mete o Ctrl+Shift+Enter .

Nota 3: O Google Planilhas é um pouco mais esperto. Apenas coloque a fórmula que você quer que seja matricial dentro da função ARRAYFORMULA( ).

Como ele é um pouco mais esperto que o Excel, caso a resposta esperada pela sua fórmula seja um intervalo, ele automaticamente irá preencher o intervalo todo, atropelando o que estiver pela frente.

Nota 4: Nos exemplos que vou usar adiante, usarei E: toda a vez que eu me referir a uma fórmula para ser usada no Excel e G: no Google Spreadsheet.

Nota 5: Não tem nota 5.

Mas que Diabos Tem Dentro de Uma Fórmula matricial?!

Como a Microsoft explica, fórmulas matriciais são poderosas fórmulas que permite a você cálculos complexos que frequentemente não podem ser feitos com funções padrões de planilha.

Você pode usar as fórmulas matriciais para fazer aquelas coisas que aparentemente são impossíveis. Claro. Certeza. Bela explicação.

É mais ou menos como se eles explicassem que a porcaria é uma mágica e que não vão explicar nada a você. Se você der uma volta pela internet, irá encontrar diversos e diversos exemplos de como usar a fórmula matricial, como aqui e aqui.

Bom, beleza. Mas nenhum explica exatamente o que está acontecendo nessa bruxaria do Mickey de forma que você compreenda para fazer suas próprias funções e não dependa de apenas modificar os exemplos fornecidos.

Aqui. Simples. Como a Bruxaria da Fórmula Matricial Funciona

Uma fórmula matricial executa a dada função que você escolher em cada linha (ou coluna) do seu intervalo uma-a-uma.

Enquanto ele executa a função, armazena o resultado do cálculo em um lugar na memória interna, da mesma forma que você faria em uma coluna auxiliar de resultados.

Aí, depois de tudo calculado, ela te devolve todas as respostas de uma vez só ou apenas uma resposta caso você queira a soma/média/contagem.

Digamos que você tenha uma complexa matriz de valores como abaixo:

Pega uma célula aleatória na sua planilha – outra que não sejam as com os valores (dã) e coloque a fórmula. Lembre do esquema das { } explicado na Nota 2 lá em cima:

E: {=SUM(A1:A9*B1:B9)} 
G: =ARRAYFORMULA(SUM(A1:A9*B1:B9))

Ambos aplicativos irão fazer esses passos:

1. Multiplicar A1 por B1, salvar o resultado na memória e ir para a próxima célula;
2. Multiplicar A2 por B2, salvar o resultado na memória e ir para a próxima célula;
3. ZzZzZzz…
4. Multiplicar A9 por B9, salvar o resultado na memória e para a iteração;
5. Por fim, ele pegará todas as respostas, somará e te devolverá o valor.

Vamos entender o que está acontecendo aqui:

Em uma operação comum de multiplicação, você escreveria A1*B1 para obter o resultado da célula A1 multiplicada pela célula B2.

Mas, se você escrever algo do tipo A1:A9 * B2:B9 como parâmetros dá operação SOMA( ) em uma fórmula matricial, a planilha irá iterar, ou seja, executar a operação em todo intervalo.

Ela irá interpretar A1:A9 * B2:B9 como A1*B1, depois, A2*B2, depois, A3*B3 e assim por diante, armazenando cada resultado em sua memória temporária.

Ao final da operação, o Excel ou Google Planilhas irá agora resolver o que estiver por fora dos parênteses, no caso, a função SOMA. Sempre executando parênteses de dentro pra fora, como na matemática.

Falando em matemática, aproveito pra lembrar da ordem das operações PEMDAS: Parênteses, Expoentes, Multiplicação, Divisão, Adição e Subtração. Ambos aplicativos respeitam isso.

Como eu dizia, a segunda operação no caso é a SOMA. Ela está dizendo para performar esta operação em tudo que o Excel ou o Google Planilhas guardaram na sua memória.

Acho que ficou claro, então que, toda a vez que você usar um intervalo como parâmetro – ex. A1:A4 – o Excel ou o Google Planilhas irá entender que isso deverá ser ITERADO.

Beleza, Entendi! Bora Para os Exemplos!

Usar a Fórmula Matricial para checar valores

E: {=SOMA(A1:A9>2)}
G: =ARRAYFORMULA(SOMA(A1:A9>2))

O que está acontecendo aqui? Ela irá testar se A1 é maior que dois e armazenar VERDADEIRO ou FALSO na sua memória. No caso da nossa planilha complexa, A1 é 1, logo, menor que 2.

Então o computador armazena FALSO na sua memória.

Aí ele partirá para a segunda linha, afinal, você disse que era de A1 a A9. Irá checar novamente se A2 é maior que 2.

E armazena FALSO. A3, armazena VERDADEIRO. E assim vai até A9.

Depois de chegar uma por uma de A1 a A9, ele agora vai verificar o que está abraçando essa fóruma com intervalo “A1:A9>2 “. É uma soma.

Adivinha só: Ele irá somar todos os VERDADEIROS e dar o valor 42 para você, que é a resposta para a soma 3+4+5+6+7+8+9 .

Nota: VERDADEIRO é armazenado como 1 e FALSO é armazenado como 0. Você já deve ter ouvido falar disso.

Usando Multiplicação com o operador E.

Rá! Esse é um bom. O operador E pode ser expressado por uma multiplicação.

Digamos que você tenha uma lista de peças em boa ou má condições. Algumas serão recicladas. Quantas peças recicladas estavam em boas condições?!

E:{(B2:B10="Good")*(C2:C10="Yes")} 
G:=ARRAYFORMULA((B2:B10="Good")*(C2:C10="Yes"))

O que aconteceu aqui?! O computador tratou FALSO como 0 e VERDADEIRO como 1, lembra?

Assim, a primeira linha foi o teste da operação entre parênteses B2:B10=”Good”.

No caso, B2 foi armazenado VERDADEIRO na memória. Depois testou B3, B4 etc.

Depois, ele começou o segundo termo da operação, C2:C10=”Yes”, e armazenou para C2 FALSO na memória. E assim foi adiante testando C3, C4, enfim.

Terminados os testes de ambos parênteses, é hora de executar o que está entre os dois blocos de parênteses: a multiplicação.

Novamente, o primeiro parêntese VERDADEIRO com o segundo FALSO.

Então, para primeira linha C2, VERDADEIRO multiplicado por FALSO, ou seja, 1 * 0 = 0. Logo, zero. Assim, armazenou FALSO para essa operação. Parte A1 foi reciclada e estava em boas condições? Não.

Veja, nessa segunda etapa estamos trabalhando com os resultados das operações de dentro de cada um dos parênteses e todos esses resultados estão na memória do computador.

Agora, estamos fazendo a multiplicação destes resultados internamente, com os dados da memória dele. Logicamente, você não verá nada acontecer nas colunas.

Agora, para respondermos à pergunta inicial: QUANTAS peças foram recicladas que estavam em boas condições, vamos abraçar tudo com a fórmula SOMA, que será executada uma vez apenas, afinal, todo o resto das iterações já foi concluído.

E:{SOMA((B2:B10="Good")*(C2:C10="Yes"))} 
G:=ARRAYFORMULA(SOMA(B2:B10="Good")*(C2:C10="Yes")))

Você obterá apenas o valor 2 como resposta, pois é a quantidade.

Dá pra colocar uma terceira coluna nessa jogada?! Sim!

E: =SOMA(B2:10*(C2:C10="Good")*(D2:D10="Yes")) 
G: =ARRAYFORMULA(SOMA(B2:10*(C2:C10="Good")*(D2:D10="Yes")))

Vamos deixar a coisa sofisticada. Como eu somo só os valores à cada 2 linhas?

Agora vamos somar o valor à cada duas (ou à cada enésima linha) na tabela. Isso significa executar a fórmula na linha 2 e pular a 3.

Executa na 4 e pula a 5 e assim por diante.

E: {=SOMA(((MOD(LIN(B2:B10), 2)=0)) * (B2:B10))} 
G: =ARRAYFORMULA(SUM(((MOD(LIN(B2:B10), 2)=0)) * (B2:B10)))

• A função MOD retorna o resto da operação de divisão. A sintaxe é MOD(dividendo; divisor).
• A função LIN retorna o número da linha de uma célula.

Matemática: Quando você divide o número da linha por 2, se o resto for zero, significa que a linha é par, então a célula será somada, pois o resultado do primeiro parêntese será VERDADEIRO.

Para complicar sua vida, se você quiser apenas as linhas ímpares, então troque o ‘=0’ por ‘=1’. Lembre-se, a função LIN apenas retorna o número da linha de uma célula, não incluindo a informação de coluna dela.

Você obterá o resultado 1.110 dessa operação.

Não entendeu bulhufas? Vamos passo-a-passo.

1. Então, o intervalo começa em B2 e vai até B10. Primeira parte, MOD(LIN(B2:B10), 2)=0
   1. LIN(B2) = 2
   2. MOD (2, 2) significa, 2 / 2 = 2 e o resto é zero. MOD retornará 0.
   3. Significa que, se MOD(2, 2)=0 e 0=0, então o resultado do primeiro parêntese é VERDADEIRO (tente escrever =0=0 no Excel)
   4. Assim, o programa armazena 1 (VERDADEIRO) como resposta para essa primeira iteração na sua memória.
2. Agora, nossa próxima célula é B3.
   1. LIN(B3) = 3
   2. MOD (3, 2) significa, 3 / 2 = 1.5 então tem um resto de 0.5. MOD retorna 0,5.
   3. Isso significa que MOD(3, 2)=0,5 e 0,5=0 retorna FALSO pois a afirmação é falsa. (tente escrever =0,5=0 in Excel)
   4. Assim, o programa armazena 0 (FALSO) como resposta para esta iteração na sua memória.
3. ZzZzZzz…
4. Agora que todas as linhas do intervalo foram testadas, o programa irá multiplicar o que está na sua memória [1, 0, 1, 0, 1, 0, 1, 0, 1] pelo pelo valor de cada uma das células do intervalo B2:B10.
5. Which gives [130, 0, 23, 0, 98, 0, 32, 0, 2] as an answer
6. Ou seja, 1*130 = 130, 0*574=0 etc. até armazenar na memória dele [130, 0, 23, 0, 98, 0, 32, 0, 2].
7. Por fim, a função SOMA abraça todo mundo, ou seja, irá ser executada sobre os valores, assim SOMA([130, 0, 23, 0, 98, 0, 32, 0, 2])
8. 285 será a resposta.

Conclusão

Espero que você tenha compreendido esse processo todo que acontece por trás das câmeras, que é o que lhe dará o poder de inventar as mandingas que quiser agora!

A internet é cheia de exemplos, como eu disse antes. Agora que você compreende o funcionamento da fórmula matricial, poderá facilmente entender o que está acontecendo nos exemplos e alterá-los para o que lhe convier.

Então, Resumindo o Pulo do Gato

• Se sua fórmula matricial só usa intervalos como em (B2:B10=”Good”)*(C2:C10=”Yes”), então sua resposta será um intervalo também (uma resposta para cada linha).
• Se sua fórmula tiver um agregador abraçando a turma toda, como SOMA, SOMASE, MÉDIA, CONTAR, CONT.SE etc., então você terá apenas 1 valor como resposta.

Boa sorte!

Gostou dessa postagem?! Manda pros seus amigos perdidões no Excel!