Este artigo trata de validação avançada de listas utilizando o Excel 2007. O artigo mostra como criar uma validação condicional de uma lista, isto é, os itens da lista dependerão da lista sendo escolhida. Observe a figura:
Figura 1: Resultado final da validação de listas múltiplas
Quando digitamos “Lista 1” a lista de validação mostra apenas os itens que pertencem à primeira lista. Quando digitamos “Lista 2” na célula A2 obtemos os itens pertencentes à segunda lista e assim por diante.
Para criarmos tal validação primeiramente precisamos definir as listas. Portanto, adicione uma nova planilha (caso tenha apenas uma disponível em sua pasta de trabalho) onde inseriremos três listas como mostra a figura:
Figura 2: Definindo as listas
É importante que o leitor não deixe nenhum item vazio na lista, pois isso afetará a fórmula que adicionaremos mais adiante.
Com isso feito, nós precisamos adicionar três nomes à nossa pasta de trabalho os quais serão utilizados na validação final. Para tanto, acesse a tabulação Formulas e em seguida clique em Name a range.
O primeiro passo é determinar a coluna onde se encontra a lista (no caso acima estará entre as colunas 1 e 3 inclusive, mas o comprimento não importa):
Figura 3: Determinando os nomes para utilização nas listas
Para este nome (chame-o de col) utilizaremos a fórmula abaixo:
=(MID(ADDRESS(1,MATCH(Sheet1!$A$2,Sheet2!$1:$1,0),3),1,LEN(ADDRESS(1,MATCH(Sheet1!$A$2,Sheet2!$1:$1,0),3))-1)&":"&MID(ADDRESS(1,MATCH(Sheet1!$A$2,Sheet2!$1:$1,0),3),1,LEN(ADDRESS(1,MATCH(Sheet1!$A$2,Sheet2!$1:$1,0),3))-1))
O que a fórmula acima faz é retornar a coluna inteira onde se encontra a lista (por exemplo, ela poderia retornar $B:$B caso seja digitado Lista 2)
Os elementos (funções) da fórmula são:
- Match à Determina a posição da coluna utilizando o valor digitado na célula A2 da planilha 1;
- Address à Retorna o endereço onde se encontra o nome da lista;
- Mid à É utilizado para remover o número da linha, pois a função Address retornará $B1 e estamos interessados somente em $B;
- Len à Utilizado para medir o comprimento do endereço e remover o número, isto é, Len(Endereço)-1 retorna o que desejamos $B.
O próximo passo requer o cálculo do deslocamento da coluna. Novamente, utilizaremos um nome (chame-o de colDesloc) e a fórmula será:
=MATCH(Sheet1!$A$2,Sheet2!$1:$1,0)-1
A fórmula acima calcula o deslocamento em coluna. Caso seja a primeira lista a função MATCH retornará 1 (coluna onde se encontra a primeira lista), porém, neste caso, não há deslocamento e precisamos subtrair 1. Quando for Lista 2, a função MATCH retorna 2 e a fórmula retorna 1 (um deslocamento na coluna).
Finalmente, precisamos criar o nome para a validação de dados (chame-o de Dados) o qual receberá a seguinte fórmula:
=OFFSET(Sheet2!$A$2,0,colDesloc,COUNTA(INDIRECT("Sheet2!"&col))-1,1)
A função OFFSET determina o deslocamento dos dados e é composta pelos seguintes argumentos:
- O primeiro argumento é a posição inicial do deslocamento Célula A2 da planilha que contém a lista. O deslocamento inicia em A2, pois o cabeçalho não será incluído na lista de validação;
- O segundo argumento é o deslocamento em linha que neste caso não ocorrerá;
- O terceiro argumento é o deslocamento de coluna o qual é calculado pelo nome criado no passo anterior;
- O quarto argumento é a altura do deslocamento e aqui utilizamos o resultado do primeiro nome criado. Note o uso da função INDIRECT para retornar indiretamente o endereço utilizado pela função COUNTA para determinar quantos itens estarão na lista. Do total subtraímos 1, pois não contamos o cabeçalho;
- O último argumento é a largura do deslocamento que neste caso é sempre 1.
Agora que terminamos a criação dos nomes, os mesmos estarão disponíveis no gerenciador de nomes:
Figura 4: Gerenciando nomes
Finalmente, para validar a lista ative a tabulação Data e no grupo Data tools clique na opção Data validation. A janela de validação de dados será aberta onde devemos determinar a lista retornada pelo nome Dados criada anteriormente:
Figura 5: Validando as listas
O leitor pode agora modificar o nome da lista que os somente os itens pertencentes à lista serão mostrados na célula de validação.
CONCLUSÃO
Este curto artigo mostra ao leitor algumas das novidades da versão 2007 do MS Excel e como criar uma validação de dados que vai além da lista usualmente utilizada.
Estaremos publicando vários artigos em preparação para o lançamento oficial do MS Office 2007. Fique ligado no Linha de Código!
Robert Friedrick Martim, Expert da Comunidade Excel
Robert Martim é Economista, Formado e Pós-Graduado em Finanças pela Universidade de Londres, Microsoft MVP Excel.
Conheça alguns dos e-books do autor sobre Excel e Access em nossa Loja Virtual.