Funções de pesquisa: Filtros, OFFSET e INDEX:INDEX

Dependendo do tipo de situação que você se encontra, pode ser que você queira não trabalhar com seu conjunto de dados inteiro. Nas aulas passadas vimos perguntas como "Qual o segundo produto com maior lucro dentro do tempo 1?". Essa pergunta se torna bem complicada visto que temos que primeiro reduzir nosso banco de dados apenas para o tempo 1 para depois ranquear os produtos conforme o lucro.

Vamos então ver formas de reduzir o banco de dados.

O Excel ou google planilhas tem a opção de "Criar filtro". Siga os passos a seguir para criar um:

Selecione seus dados

Depois de selecionados, clique em "Dados"

Depois clique em "Criar filtro"

Um símbolo aparecerá ao lado de cada nome, clique nesse símbolo

Vamos criar um filtro novo, queremos só mostrar o que está no tempo 1! Para isso precisa limpar todos os filtros e depois clicar no "1" para deixar apenas os valores com tempo 1 na tabela.

Os problemas do filtro

O filtro não remove os dados que não quer trabalhar, ele apenas os tira de vista. Isto é, ao aplicar uma função como por exemplo =MAX(A2:A) que veria o máximo do tempo, ela ainda retornaria 12 (o tempo máximo na tabela), por que o dado ainda se encontra lá.

O filtro é muito importante quando queremos ter uma ideia rápida do que está acontecendo em uma base de dados grande, deixando à vista apenas o que importa. Mas, devemos mencionar aqui que ele ainda não contribui para responder perguntas que precisam remover parte do seu banco de dados.

A função OFFSET retorna uma referência de intervalo deslocada a um número especificado de linhas e colunas de uma referência de célula inicial.

Sintaxe da função OFFSET

=OFFSET(referencia_da_celula, linhas_a_deslocar, colunas_a_deslocar, [altura], [largura])

Onde:

- referencia_da_celula é o ponto a partir do qual contar as linhas e colunas a serem deslocadas.

- linhas_a_deslocar é o número de linhas a deslocar.

- colunas_a_deslocar é o número de colunas a deslocar.

- altura - [opcional] a altura do intervalo que deve ser retornado a partir do deslocamento especificado.

- largura - [opcional] a largura do intervalo que deve ser retornado a partir do deslocamento especificado.

Exemplo

1. Qual o menor valor de venda entre o tempo maior ou igual a 4?

2. Qual o maior valor de venda entre o tempo maior que 2 e menor que 6?

Resolução

1. =MIN(OFFSET(A1:B8,4,1)) ou =MIN(OFFSET(A1:B8,MATCH(4,A:A)-1,1))

2. =MAX(OFFSET(A1:B8,4,1,2,1)) ou =MAX(OFFSET(A1:B8,match(2,A:A),1,match(6,A:A) - match(2,A:A) - 1,1))

A função INDEX o conteúdo de uma célula especificada pelo deslocamento de linha e coluna. Ela na verdade retorna a célula em si que está sendo deslocada. Isto é, você pode utilizar da função INDEX com outra função INDEX para especificar uma parte do seu banco de dados.

Sintaxe da função INDEX:INDEX

=INDEX(referencia, [linha], [coluna]):INDEX(referencia, [linha], [coluna])

Onde:

- referencia é a matriz de células a deslocar.

- linha - [opcional] é o número de linhas a deslocar.

- coluna - [opcional] é o número de colunas a deslocar.

Exemplo

1. Qual o menor valor de venda entre o tempo maior ou igual a 4?

2. Qual o maior valor de venda entre o tempo maior que 2 e menor que 6?

Resolução

1. =MIN(INDEX(A1:B8,5,2):INDEX(A1:B8,8,2)) ou =MIN(INDEX(A1:B8,MATCH(4,A:A,0),2):INDEX(A1:B8,MATCH(MAX(A:A),A:A,0),2))

2. =MAX(INDEX(A1:B8,4,2):INDEX(A1:B8,6,2)) ou =MAX(INDEX(A1:B8,MATCH(3,A:A,0),2):INDEX(A1:B8,MATCH(5,A:A,0),2))

A função CLASSIFICAR no Excel permite ordenar um intervalo de dados com base em uma ou mais colunas. É uma função dinâmica que retorna um intervalo de células ordenado, sem a necessidade de alterar a estrutura original dos dados.

=CLASSIFICAR(matriz; [índice_classificação]; [ordem]; [por_coluna])

Onde:

matriz: é o intervalo de células ou tabela que você deseja classificar.

[índice_classificação]: (opcional) é o número da coluna ou linha pela qual você deseja classificar. Se omitido, a classificação será feita pela primeira coluna ou linha.

[ordem]: (opcional) define a ordem da classificação:

1 para ordem crescente (padrão).

-1 para ordem decrescente.

[por_coluna]: (opcional) define se a classificação será feita por coluna (VERDADEIRO) ou por linha (FALSO, padrão).

Exemplo Prático

Vamos usar a seguinte tabela de vendas como exemplo:

Objetivo: Ordenar a tabela pela coluna Vendas em ordem crescente.

=CLASSIFICAR(A2:C8; 3; 1)

Objetivo: Ordenar a tabela pela coluna Vendas em ordem decrescente.

=CLASSIFICAR(A2:C8; 3; -1)

A função FILTRO no Excel permite extrair um conjunto de dados de uma tabela com base em critérios específicos. É uma função dinâmica que retorna um intervalo de células que atendem às condições definidas, sem a necessidade de usar fórmulas complexas ou filtros manuais.

=FILTRO(matriz; incluir; [se_vazio])

Onde:

- matriz: é o intervalo de células ou tabela que você deseja filtrar.

- incluir: é a condição ou critério que define quais linhas serão incluídas no resultado.

- [se_vazio]: (opcional) é o valor retornado caso nenhum dado atenda ao critério. Se omitido, a função retornará um erro.

Detalhes Importantes

1. A função FILTRO retorna um intervalo dinâmico, ou seja, o resultado pode incluir várias linhas e colunas, dependendo dos critérios.

2. Se o critério não for atendido por nenhum dado, a função retornará um erro, a menos que o argumento `[se_vazio]` seja especificado.

3. A função é sensível a maiúsculas e minúsculas, a menos que você use funções adicionais para ignorar essa sensibilidade.

Exemplo Prático

Vamos usar a seguinte tabela de vendas como exemplo:

Exemplo 1: Filtrar Vendas na Região "Sul"

Objetivo: Extrair todas as vendas realizadas na região Sul.

Fórmula:

=FILTRO(A2:C8; B2:B8="Sul")

Exemplo 2: Filtrar Vendas Acima de 5000

Objetivo: Extrair todas as vendas acima de 5000.

Fórmula:

=FILTRO(A2:C8; C2:C8>5000)

Exemplo 3: Filtrar Vendedores da Região "Norte" com Vendas Acima de 4000

Objetivo: Extrair vendedores da região Norte com vendas acima de 4000.

Fórmula:

=FILTRO(A2:C8; (B2:B8="Norte")*(C2:C8>4000))

Exemplo 4: Tratamento de Erros com [se_vazio]

Objetivo: Filtrar vendas na região Centro-Oeste. Como não há dados para essa região, vamos retornar a mensagem "Nenhum dado encontrado".

Fórmula:

=FILTRO(A2:C8; B2:B8="Centro-Oeste"; "Nenhum dado encontrado")

Clique aqui para acessar a planilha para resolver os próximos exercícios. Esta planilha é uma série de produtos vendidos por uma distribuidora.

1. Quantos produtos custam mais de 10 dólares?

2. Qual o nome do produto de SKU "DAG9419"?

3. Entre os produtos "BoardGames" qual tem o maior e qual tem o menor preço?

4. Qual o desconto médio dos produtos "Greater Than Games"?