Na última turma do curso de Excel Intermediário e Avançado na Feevale, fizemos um exercício muito interessante usando fórmulas matriciais, que vou reproduzir aqui. O objetivo é criar uma opção de busca ou filtro que vai agir sobre alguns lançamentos da planilha, para somar (considerar) apenas os itens encontrados.

Cada lançamento terá uma descrição textual seguida da informação que se deseja totalizar, ou seja, pode ser uma quantidade, um valor monetário, tempo em horas, etc. Neste exemplo vou demonstrar utilizando horas, então teremos algo parecido com a tela abaixo:

Utilizando uma fórmula matricial, podemos realizar uma busca pela descrição de cada lançamento, com palavras-chave, e somar o tempo total apenas dos lançamentos encontrados. Serão necessárias as seguintes funções:

• LOCALIZAR() - Localiza um termo dentro de uma string (texto)
• SE() - Permite tomar uma decisão a partir do resultado de uma condição
• ÉERRO() - Verifica se a informação é ou não um erro do Excel
• SOMA() - Soma os seus argumentos

Para totalizar apenas os tempos dos itens que contem “prod” na descrição, basta digitar o termo “prod” que a fórmula se encarrega de todo o processo.

Acima dos lançamentos temos a célula B4 onde pode-se informar a palavra-chave para a pesquisa. Inicialmente, a função LOCALIZAR() vai procurar pelo termo, em cada uma das células dos lançamentos (intervalo B7:B12). Se a função encontrar o termo, ela retorna a posição numérica do termo dentro da expressão, e se não encontrar, ela retorna o erro #VALOR!. Após a função LOCALIZAR() devemos usar as funções ÉERRO() em conjunto com a SE(), pois se retornou o erro, não deve somar o lançamento, e se encontrou, deve somar.

Então sempre que a LOCALIZAR() retornar um erro, a função SE() vai retornar zero, e este zero será somado pela função SOMA(). Mas sempre que a LOCALIZAR() encontrar o termo, a função SE() retorna diretamente o intervalo a ser somado (C7:12). Como estamos trabalhando com intervalos, devemos concluir a inserção da fórmula com CTRL+SHIFT+ENTER, para que o Excel saiba que queremos uma fórmula matricial.

A fórmula completa vai ficar assim:

=SOMA(SE(ÉERRO(LOCALIZAR(B4;B7:B12));0;C7:C12))

Sendo assim, apenas os lançamentos destacados abaixo serão considerados na soma:

This entry was posted on Segunda-feira, Fevereiro 23rd, 2009 at 12:25 pm and is filed under Excel. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.