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:\Excel 2007 Avançado\Exemplos\Exemplo 13.xlsx 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.       Abra o Excel 2007.
2.       Selecione Botão do Office  -> Abrir.
3.       Navegue até a pasta C:\Excel 2007 Avançado\Exemplos.
4.       Selecione o arquivo Exemplo 13.xlsx.
5.       Clique em Abrir.
6.       Será aberta uma planilha com uma lista com 830 pedidos.
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.       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.       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.     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 2007 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 2007 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.     Na Figura 1.69 temos uma visão geral da definição do cálculo da média e do critério calculado.

Figura 1.69 – Definindo critérios calculados.

12.       Selecione a Guia Dados -> Grupo Classificar e Filtrar -> Avançado e defina as faixas conforme indicado na Figura 1.70.

Figura 1.70 – Definindo o Intervalo da Lista e o Intervalo de critérios.

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.71.

Figura 1.71 – Somente pedidos com valores acima da média.

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.
15.     Vamos, em primeiro lugar, excluir a fórmula da célula E1, pois não iremos mais utiliza-la neste segundo exemplo.
16.     Altere o texto da célula A1 para: Diferença maior do que 15 dias.
17.       Na célula A2 digite a seguinte fórmula: =E8-D8>15. Com esta fórmula o Excel 2007 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.
18.       Selecione a Guia Dados -> Grupo Classificar e Filtrar -> Avançado... e defina as faixas conforme indicado na Figura 1.72.

Figura 1.72 – Definindo o Intervalo da Lista e o Intervalo de critérios.

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.73:

Figura 1.73 – Somente pedidos com diferença entre as datas maior do que 15 dias.

VOCÊ CONHECE A UNIVERSIDADE DO EXCEL?

UNIVERSIDADE PRÁTICA DO EXCEL - Domine o Excel, do Básico até a Criação de Planilhas Profissionais com Excel e VBA!

Aplica-se ao Excel 2019, 2016, 2013 e 2010!

22 Cursos - 925 Vídeo-Aulas - 120:42 horas - Só R$ 97,00

Para Todos os Detalhes, Acesse:

https://juliobattisti.com.br/excel-curso-completo.asp