Excel lista suspensa com 2 colunas

Criar uma lista pendente no Microsoft Excel: Aprende aqui 6 métodos diferentes para criares as tuas listas pendentes no Micosoft Excel.

A criação de listas pendentes, através do comando Validação de Dados, é dos métodos mais usados nos teus mapas do Microsoft Excel. Através desta opção (Validação de Dados) é possível selecionar os itens específicos de uma lista e, através do Item selecionado, executar uma série de operações que podem envolver outras funções do Microsoft Excel. Tornando os relatórios ou mapas mais apelativos e, acima de tudo, mais dinâmicos.

Assim, neste artigo, ensino 6 alternativas para obter o mesmo resultado. Cabe depois, ao utilizador, escolher o método ou função mais conveniente. O exemplo tem 2 listas pendentes:

• uma principal, com os Itens principais; e
• uma 2ª lista, que terá os valores apenas correspondentes ao item principal.

O primeiro passo será então criar a primeira lista pendente. Vamos lá?!

A criação de uma lista pendente é muito simples. Basta aceder ao menu Dados [Data] e à opção Validação de Dados [Data Validation].

Nas opções da Caixa de Diálogo escolhemos a opção “Lista” e definimos a Origem num intervalo de células, neste caso o conjunto de células que corresponde aos itens principais (Departamento).

O resultado é apresentado na imagem em baixo:

Definir as funções para a Lista secundária:

A lista secundária é criada da mesma forma que a lista principal. A única diferença está na origem. Em vez de um intervalo fixo, será um intervalo dinâmico proveniente do resultado de uma fórmula. Assim, a função utilizada deverá permitir obter um resultado em forma de lista que permite retornar vários valores (um array) em vez de um valor único (escalar).

Opção 1: Utilizar a tradicional função SE [IF]

Com a função SE podemos testar 2 ou mais condições. A primeira condição irá testar se o valor selecionado na lista tem correspondente ao primeiro item principal. No caso de a correspondência ser verdadeira, devolve o intervalo da sublista que corresponde ao item.

Caso a primeira condição não seja válida, será feita um segundo teste lógico, para avaliar agora o valor selecionado com o segundo item principal e devolver o segundo intervalo correspondente, caso a condição seja verdadeira.

Caso nenhuma das anteriores se verifique, devolve o último intervalo. O processo pode ser repetido para vários intervalos, da mesma forma que se avaliam várias condições numa expressão SE. Há a necessidade de encadear sempre uma nova função SE para testar novas condições. Por esta razão, este primeiro método pode não ser o mais aconselhado pela complexidade que a fórmula pode ter, com o encadeamento de várias funções.

Expressão: =SE(B4=G4;G5:G8;SE(B4=H4;H5:H9;I5:I10))

Opção 2: Utilizar a nova função SE. S [IFS]

A função SE. S [IFS] simplifica a sintaxe da expressão “SE”, ao testar várias condições dentro da mesma função. Significa que não é necessário encadear mais que uma função para testar um conjunto de condições.

Esta função testa a primeira condição e retorna o primeiro resultado verdadeiro, e passa automaticamente para outra condição e devolve um novo resultado caso a anterior não seja válida. O processo repete-se para as restantes condições a serem analisadas.

Expressão: =SE.S(B4=G4;G5:G8;B4=H4;H5:H9;B4=I4;I5:I10)

Opção 3: Utilizar a função ÍNDICE com a função CORRESP [INDEX & MATCH]

A função ÍNDICE [INDEX] pode ser utlizada neste cenário específico porque a função permite devolver um intervalo de valores, quando um dos seus argumentos (núm_linha ou núm_coluna) é omisso.

Neste caso, o exemplo será criado em que o argumento do núm_linha é omisso, e são devolvidas todas as linhas da coluna selecionada. Para que o valor da coluna seja dinâmico e escolhido em função do valor do departamento, utilizamos a função CORRESP [MATCH] para identificar a posição do valor no intervalo auxiliar.

Expressão: =ÍNDICE(G5:I10;;CORRESP(B4;G4:I4;0))

Opção 4: Utilizar a nova função PARÂMETRO [SWITCH]

A função PARÂMETRO [SWITCH] avalia uma expressão, que é comparada com um determinado valor, e caso seja igual, devolve um resultado. Esta função pode ser utilizada de uma forma muito semelhante à função SE.S onde a expressão, que neste cenário é apenas uma célula (B4) é comparada numa primeira instancia ao valor da célula (G4) – Vendas.

Caso as células sejam iguais, é devolvido o resultado do intervalo de vendas (G5:G8). O processo repete-se para as restantes conforme as condições que o utilizador necessita de comparar.

Expressão: =PARÂMETRO(B4;G4;G5:G8;H4;H5:H9;I4;I5:I10)

Opção 5: Utilizar a função SELECIONAR [CHOOSE]

A função SELECIONAR [CHOOSE] tem um argumento numérico, que corresponde ao valor de um índice que é com um resultado. Para o índice número 1 é devolvido o resultado 1, e assim sucessivamente conforme as condições que o utilizador queira testar.

Neste cenário, para atribuir o valor do índice, voltamos a usar a função CORRESP [MATCH] para atribuir a posição do valor da primeira lista de uma forma dinâmica, reconhecendo desta forma o valor do índice. Depois a função apenas necessita que seja atribuído um intervalo que faça a devida correspondência ao valor do índice.

Expressão: =SELECIONAR(CORRESP(B4;G4:I4;0);G5:G8;H5:H9;I5:I10)

Opção 6: Utilizar a função INDIRETO [INDIRECT]

A função INDIRETO [INDIRECT] é a função que tem a expressão mais simples. Esta função apenas necessita que cada intervalo devolvido esteja associado a um nome. Conforme o nome selecionado, a função devolve o conteúdo a que esse nome corresponde, neste caso o intervalo.

Passo 1: Atribuir o nome aos intervalos

Para atribuir o nome aos intervalos vamos utilizar a opção de Criar a partir da Seleção escolhendo os 3 intervalos.

Passo 2: Associar a função Indireto

Para associar a função indireto, basta utilizar o seu argumento para identificar a célula que contem o nome pretendido, neste caso (B4).

Expressão: =INDIRETO(B4)

Criar a segunda lista pendente:

Para criar a segunda lista pendente, repetimos o processo, agora para a célula (D4), onde colocamos na origem dos dados uma das fórmulas criadas.

Espero que este processo passo-a-passo tenha sido útil! Se preferires, confere o vídeo tutorial que publicámos recentemente. Alguma dúvida envia-nos a tua mensagem... ficamos à tua espera!

Tipos de junção no Power Query (Join Kinds)

Neste novo vídeo vais aprender como podes utilizar os vários tipos de junção (Join Kinds), para analisar se o valor de uma determinada célula existe num intervalo.

Continuar a ler...

Como importar dados de uma pasta para o Microsoft Excel (Power Query)?

Neste vídeo tutorial, vou mostrar-vos como podem utilizar um comando muito simples, do Power Query, para importar para uma única tabela dados provenientes de uma pasta!

Continuar a ler...

Como fazer duas listas suspensas no Excel?

Como fazer Validação de dados com duas colunas?

Como fazer seleções múltiplas no Excel?

Como criar uma lista suspensa com várias caixas de seleção no Excel?