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))

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"?