Dicas do Microsoft Excel é ao Domingo…no Pplware – 46

Por Leandro Jekimim Goulart

Fórmulas de Matriz – Array Formulas (Parte II)

Ora vivam caros leitores. Mais um Domingo e mais uma artigo da rubrica “Dicas do Microsoft Excel é ao Domingo….no Pplware”. Lembramos que esta é uma rubrica “aberta” a todos os leitores que pretendam contribuir. Para tal devem enviar-nos um e-mail com a dica que pretendem ver publicada. Caso tenham também alguma dúvida ou problema a resolver no Excel, deixem essa informação nos comentários para que a comunidade Pplware ajude a resolver.

O Excel possui muitos recursos desconhecidos e pouco documentados, como as funções BDContar e a SOMARPRODUTO já apresentada em artigos anteriores. No ultimo tutorial ensinamos demos um exemplo básico de como usar Fórmulas de Matriz. Hoje vamos apresentar um exemplo mais complexo, para isso vamos considerar a seguinte informação:

Suponha que queremos saber o total de vendas do vendedor Barnhill. Para isso basta usarmos a função SE, e a fórmula fica:

=SOMA(SE(B2:B12=“Barnhill”;D2:D12*E2:E12))

Acompanhe o passo-a-passo da execução desta fórmula.

 

Agora um exemplo mais completo. Vamos fazer um relatório mestre-detalhe, onde possamos listar todas as vendas de um determinado vendedor. Desta vez vamos usar a fórmula abaixo.

=SEERRO(ÍNDICE($A$2:$E$12; MENOR(SE(B2:B12=“Barnhill”;LIN(A2:A12));LIN(1:3))-1; 3);””)

Como o retorno desta fórmula é uma matriz de 3 elementos, você deve:

• Selecionar 3 células no Excel
• Colar a fórmula acima
• Pressionar CTRL + SHIFT + ENTER

Antes de explicar a fórmula é preciso explicar as novas funções que usamos aqui:

• LIN(A2:A12) retorna uma matriz {2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12}. Usaremos como parâmetro para a função ÍNDICE.
• LIN (1:3) retorna uma matriz {1, 2, 3}, usaremos apenas como parâmetro para a função MENOR.
• MENOR (matriz, k) será usado para selecionar dentre os resultados da função SE até três vendas do vendedor selecionado. O parâmetro matriz é o resultado da função SE (ver passo 4 na tabela acima), e o parâmetro k é a matriz de LIN que nos permitirá selecionar o primeiro, segundo e terceiro valor da matriz resultante da função SE.
• ÍNDICE busca um elemento em uma matriz dado a linha e coluna do elemento. A matriz informada ($A$2:$E$12) é a própria tabela de vendas, a linha será o resultado da função MENOR e a coluna na fórmula acima é 3, que é a coluna “Produto”.
• SEERRO será usada para mostrar um valor vazio (“”) quando o vendedor possui menos que três vendas

O que a fórmula faz:

Juntando todos os exemplos temos o seguinte resultado. Neste exemplo abaixo basta trocar o nome do vendedor na célula A16 e automaticamente serão mostradas as suas vendas abaixo. Os campos Email e Cidade usam a função PROCV (já detalhada em artigos anteriores) para trazer seus dados da folha de cálculo Vendedor.

Como pudemos ver, Fórmulas de Matriz são extremamente poderosas, podem poupar um grande trabalho, evitar fórmulas intermediárias e sub-totais, além de permitir funcionalidades inéditas no Excel como relatórios metre-detalhe. Porém se não usadas com cuidado podem ser muito confusas e gerar resultados indesejados.

Por hoje é tudo! Esperamos que as dicas sejam úteis e que vos ajudem a dominar o Microsoft Excel. Como referi anteriormente, esta dica está aberta a todos os que pretendam contribuir. Enviem-nos as vossas dicas para ppinto @ pplware .com para publicarmos em próximos artigos.