Listas suspensas vinculadas

Descrição

Em Excel você pode de forma rápida e fácil crie uma lista suspensa , mas você já tentou criar uma lista suspensa dependente? Existem várias maneiras de criar essa lista, então vamos dar uma olhada nelas.

Método 1. Função INDIRECT

Esta técnica é baseada na aplicação da função INDIRECT, que pode fazer uma coisa simples - converter o conteúdo de qualquer célula especificada em um endereço de intervalo que Excel entenda. Ou seja, se a célula contiver o texto " А1 ", então a função dará uma referência à célula como resultado А1 . Se a célula contiver a palavra " Auto ", então a função produzirá uma referência ao intervalo nomeado com o nome Auto etc.

Veja, por exemplo, esta lista de modelos de automóveis Toyota, Ford e Nissan :

Selecione a lista completa de modelos Toyota (da célula А2 e até o final da lista) e dê um nome a esse intervalo Toyota no cardápio Inserir - Nome - Atribuir (Insert - Name - Define) . Então vamos repetir o mesmo com as listas Ford e Nissan , especificando os nomes dos intervalos de acordo Ford e Nissan .

Agora vamos criar a primeira lista suspensa para escolher uma marca de carro. Selecione uma célula vazia e abra o menu Dados - Verifique (Data - Validation) e, em seguida, na lista suspensa Tipo de dados selecione uma opção Lista e no campo Fonte - destaque as células com nomes de marcas (células amarelas em nosso exemplo). Depois de clicar em ОК a primeira lista suspensa está pronta:

Agora vamos criar uma segunda lista suspensa, que exibirá os modelos da marca selecionada na primeira lista. Assim como no caso anterior, selecione uma célula vazia e abra o menu Dados - Verifique - avançar Lista . No campo Fonte você precisará inserir a seguinte fórmula:

=INDIRECT(F3)

Onde:

• F3 - o endereço da célula com a primeira lista suspensa - substitua-o pelo seu.

Todos. Depois de clicar em OK o conteúdo da segunda lista será selecionado pelo nome do intervalo selecionado na primeira lista.

Contras deste método:

• Os intervalos dinâmicos especificados por fórmulas do tipo OFFSET não podem atuar como intervalos secundários (dependentes). Você pode usá-los para a lista primária (independente), mas a lista secundária deve ser definida de forma rígida, sem fórmulas.
• Os nomes dos intervalos secundários devem corresponder aos itens da lista suspensa principal. Ou seja, se contiver texto com lacunas, deverão ser substituídos por sublinhados, etc.
• Você precisa criar manualmente vários intervalos nomeados.

Método 2. Lista de correspondências (OFFSET e MATCH)

Este método requer uma lista ordenada de correspondências de marca e modelo do seguinte tipo:

Para criar uma lista suspensa primária de marcas, você pode usar o método usual descrito acima, ou seja:

• Dê o nome do intervalo D1:D3 (por exemplo Marcas )
• Selecione na aba Dados (Data) equipe Verificação de dados (Data validation)
• Selecione uma opção de verificação na lista suspensa Lista (List) e especifique como qualidade Fontes (Source) = Marcas ou simplesmente selecione as células D1:D3 (se estiverem na mesma planilha da lista).

Mas para uma lista dependente de modelos, você terá que criar um intervalo nomeado com a função OFFSET, que se referirá dinamicamente apenas às células de modelos de uma determinada marca. Por esta:

• Pressione Ctrl + F3 ou use o botão Gerenciador de nomes (Name manager) na aba Fórmulas (Formulas) . Nas versões anteriores a 2003, este era um comando de menu Inserir - Nome - Atribuir (Insert - Name - Define)
• Crie um novo intervalo nomeado com qualquer nome (por exemplo Modelos ) e no campo Link (Reference) na parte inferior da janela, insira manualmente a seguinte fórmula:

=OFFSET( $A$1 ; MATCH($G$7;$A:$A;0)-1 ; 1 ; COUNTIF($A:$A;$G$7) ; 1 )

A função OFFSET é capaz de emitir uma referência a um intervalo do tamanho desejado, deslocado em relação à célula inicial por um determinado número de linhas e colunas. Numa versão mais compreensível, a sintaxe desta função é a seguinte:

=OFFSET( célula_inicial ; shift_down ; shift_right ; range_size_in_rows ; range_size_in_columns )

Então:

• célula_inicial - pegamos a primeira célula da nossa lista, ou seja A1 ;
• shift_down - contamos a função MATCH, que, simplesmente, gera o número de série da célula com a marca selecionada (G7) no intervalo determinado (colunas E );
• shift_right =1 , então queremos nos referir aos modelos na coluna adjacente ( EM );
• range_size_in_rows - calculamos usando a função COUNTIF, que é capaz de contar o número de ocorrências na lista (coluna A) dos valores que precisamos - marcas de automóveis ( G7 );
• range_size_in_columns =1 , então precisamos de uma coluna com modelos.

O resultado deve ser algo assim:

Resta adicionar uma lista suspensa com base na fórmula criada à célula G8 . Por esta:

• Selecione uma célula G8
• Selecione na aba Dados (Data) equipe Verificação de dados (Data validation) ou no cardápio Dados - Verifique (Data - Validation)
• Selecione a opção de verificação na lista suspensa Lista (List) e entre como Fontes (Source) sinal é igual ao nome do nosso intervalo, ou seja = Modelos .

Artigos sobre o tema:

• Uma lista suspensa em uma célula
• Uma dica ao preencher dados em uma célula