O número de itens disponíveis para filtragem é limitado. O Excel não pode filtrar colunas em que o número de itens exceda 999 (não o número de linhas).
Para filtrar quando houver mais de 999 itens, use o filtro avançado.
Para criar um filtro avançado, usaremos as funções “OFFSET” e “COUNTA” no Microsoft Excel.
CONT.valores: Ele retorna a contagem do número de células que contêm valores.
Sintaxe da função “CONT.valores”: = CONT.valores (valor1, valor2, valor3….)
Exemplo: No intervalo A1: A5, as células A2, A3 e A5 contêm os valores e as células A1 e A4 estão em branco. Selecione a célula A6 e escreva a fórmula-
= CONT.valores (A1: A5) a função irá retornar 3
DESLOCAMENTO: Ele retorna uma referência a um intervalo que é deslocado várias linhas e colunas de outro intervalo ou célula.
Sintaxe da função OFFSET: = OFFSET (referência, linhas, colunas, altura, largura)
Referência:- Esta é a célula ou intervalo do qual você deseja compensar.
Linhas e colunas para mover: - O número de linhas que você deseja mover do ponto inicial e ambos podem ser positivos, negativos ou zero.
Altura e Largura: - Este é o tamanho do intervalo que você deseja retornar. Este é um campo opcional.
Vamos dar um exemplo para entender a função Offset no Excel.
Temos dados no intervalo A1: D10. A coluna A contém o código do produto, a coluna B contém a quantidade, a coluna C contém o custo por produto e a coluna D contém o custo total. Precisamos retornar o valor da célula C5 na célula E2.
Para obter o resultado desejado, precisamos seguir as etapas abaixo mencionadas.
- Selecione a célula E2 e escreva a fórmula.
- = OFFSET (A1,4,2,1,1)e pressione Enter no teclado.
- A função retornará o valor da célula C5.
Neste exemplo, precisamos obter o valor da célula C5 a E2. Nossa célula de referência é a primeira célula no intervalo que é A1 e C5 está 4 linhas abaixo e 2 colunas à direita de A1. Portanto, a fórmula é = OFFSET (A1,4,2,1,1) ou = OFFSET (A1,4,2) (já que 1,1 é opcional).
Agora, vamos dar um exemplo para recuperar o último valor em uma lista dinâmica.
Temos nomes de países em uma série. Agora, se adicionarmos mais países a esta lista, ele deve estar disponível na lista suspensa automaticamente.
Para preparar o filtro avançado, siga as etapas abaixo: -
- Selecione a célula B2.
- Vá para a guia Dados, selecione Validação de dados no grupo Ferramentas de dados.
- A caixa de diálogo “Validação de dados” aparecerá. Na guia “Configurações”, selecione “Personalizado” na lista suspensa Permitir.
- A caixa de fórmula será ativada.
- Escreva a fórmula nesta caixa.
- = OFFSET (A: A, 1,0, COUNTA (A: A) -1,1).
- Clique em OK.
- Neste estágio, a última célula atualizada é A11.
- Para verificar se a validação de dados está funcionando corretamente, adicione um nome de cidade na célula A12.
Assim que você adicionar uma entrada em A12, ela será adicionada à lista suspensa.
É assim que podemos adicionar mais entradas do que 999 itens no Microsoft Excel.