Curso Grátis de Excel Avançado - Módulo 1 - Lição: Usando Critérios Calculados

Existem situações onde uma simples filtragem com base em um ou mais critérios não é o suficiente para que você possa obter os resultados desejados. Por exemplo, em uma lista de pedidos você pode querer filtrar apenas os pedidos onde o valor do pedido é maior do que o valor médio dos pedidos. Em uma situação como esta temos que utilizar critérios calculados.

Existem algumas regras de devem ser observadas quando utilizamos Critérios Calculados em conjunto com o comando Filtro Avançado:

Regra 1: O cabeçalho do critério calculado, acima da célula onde está a fórmula de cálculo não pode ser igual ao nome de uma das colunas da lista, pois senão será gerado um erro. Este cabeçalho pode ficar em branco ou pode conter um texto qualquer, desde que este texto não seja igual ao nome de uma das colunas da lista.

Regra 2: Se a fórmula fizer referência a uma célula fora da lista de valores, esta referência deverá ser absoluta.

Regra 3: Se a fórmula fizer referência a uma célula dentro da lista de valores, esta referência deverá ser relativa.

Para melhor entender o uso de Critérios Calculados vamos utilizar um exemplo prático. Neste exemplo utilizaremos dois conjuntos de critérios diferentes, um para filtrar apenas os pedidos com valores maiores do que o valor médio dos pedidos e outro para filtrar os pedidos onde o número de dias, entre a data do pedido e a data do envio for maior do que 15.

Exercício 13:

Abrir o arquivo C:\ExcelAvançado\Exemplo 13.xls e utilizar critérios calculados para, primeiro, obter uma listagem apenas com os pedidos em que o valor do pedido é maior do que o valor médio dos pedidos. Limpar o critério anterior e definir um novo critério calculado onde serão filtrados apenas os pedidos onde o número de dias entre a data do pedido e a data do envio for maior do que 15.

1. 1. Abra o Excel;
2. 2. Selecione o comando Arquivo -> Abrir;
3. 3. Navegue até a pasta C:\ExcelAvançado;
4. 4. Selecione o arquivo Exemplo 13.xls;
5. 5. Clique em Abrir;
6. 6. Será aberta uma planilha com uma lista com 830 pedidos;
7. 7. O primeiro passo é calcular o valor médio dos pedidos em uma célula fora da lista de pedidos. Vamos usar a célula E1. Na Célula E1 digite a seguinte fórmula: =MÉDIA(H8:H837) . Você obterá um valor de R$ 1525,05;
8. 8. O valor da média calculada será usado como critério (na verdade uma referência absoluta a célula onde está o valor da média) para filtrar apenas os pedidos com valores acima da média;
9. 9. Na célula A1 digite o rótulo: Pedidos Acima da Média. Lembre que este rótulo não pode ser igual ao nome de uma das colunas da lista;
10. 10. Na Célula A2 digite a seguinte fórmula: =H8>$E$1. A célula H8 é onde está o valor para o primeiro registro. Quando o Excel executa o filtro, ele inicia pela célula H8. Compara o valor desta célula com o valor da média calculado na célula E1. Se o valor for maior do que a média, o registro será selecionado, caso contrário o Excel segue para o próximo registro. Isto será feito para todos os registras da lista do pedidos. O resultado final é que serão filtrados apenas os registros com valores acima da média;
11. 11. Na Figura 1.56 temos uma visão geral da definição do cálculo da média e do critério calculado.

1. 12. Selecione o comando Dados -> Filtrar -> Filtro avançado e defina as faixas conforme indicado na Figura 1.57:

1. 13. Clique em OK. Observe que serão exibidos apenas os pedidos com valores acima da média, que é de R$ 1525, 05 conforme indicado na Figura 1.58:

1. 14. Agora vamos alterar os critérios para filtrar apenas os pedidos onde a diferença, em dias, entre a Data de Envio e a Data do Pedido for maior do que 15;
2. 15. Vamos, em primeiro lugar, excluir a fórmula da célula E1, pois não iremos mais utiliza-la neste segundo exemplo;
3. 16. Altere o texto da célula A1 para: Diferença maior do que 15 dias;
4. 17. Na célula A2 digite a seguinte fórmula: E8-D8>15. Com esta fórmula o Excel irá percorrer cada linha, fazer a subtração entre as duas datas e filtrar apenas os registros onde a diferença for maior do que 15;
5. 18. Selecione o comando Dados -> Filtrar -> Mostrar todos;
6. 19. Selecione o comando Dados -> Filtrar -> Filtro avançado e defina as faixas conforme indicado na Figura 1.59:

1. 20. Clique em OK. Observe que serão exibidos apenas os pedidos onde a diferença entre a Data de Envio e a Data do Pedido for maior do que 15 dias, conforme indicado na Figura 1.60:

1. 21. Salve a planilha Exemplo 13.xls;
2. 22. Feche o Excel.

Módulo 1 - Trabalhando com Listas de Dados:

• Lição 001 - Introdução e Conteúdo do Módulo I
• Lição 002 - Importação de Dados - Parte 1
• Lição 003 - Importação de Dados - Parte 2
• Lição 004 - Importação de Dados - Parte 3
• Lição 005 - Ordenando Listas - Parte 1
• Lição 006 - Ordenando Listas - Parte 2
• Lição 007 - Criando Listas de Ordenação Personalizadas
• Lição 008 - Filtrando Dados em Uma Lista - Introdução e Conceito
• Lição 009 - Filtrando Dados - O Comando AutoFiltro - Parte 1
• Lição 010 - Filtrando Dados - O Comando AutoFiltro - Parte 2
• Lição 011 - Filtrando Dados - O Comando AutoFiltro - Parte 3
• Lição 012 - Localizando Registros Individuais
• Lição 013 - Operadores Lógicos: A Diferença Entre "E" e "OU"
• Lição 014 - Definindo e Utilizando Faixas de Critérios - Introdução
• Lição 015 - O Primeiro Exemplo Utilizando Faixas de Critérios
• Lição 016 - Usando Critérios de Texto
• Lição 017 - Usando Critérios Calculados
• Lição 018 - Usando Subtotais Para Análise de Dados
• Lição 019 - Exercícios com Faixas de Critérios
• Lição 020 - Resumo do Módulo 1