As planilhas do Excel geralmente incluem listas suspensas de células para simplificar e / ou padronizar a entrada de dados. Essas listas suspensas são criadas usando o recurso de validação de dados para especificar uma lista de entradas permitidas.
Para configurar uma lista suspensa simples, selecione a célula onde os dados serão inseridos e clique em Validação de dados(na guia Dados), selecione Validação de dados, escolha Lista(em Permitir :) e insira os itens da lista (separados por vírgulas) no
Nesse tipo de menu suspenso básico, a lista de entradas permitidas é especificada dentro da própria validação de dados; portanto, para fazer alterações na lista, o usuário deve abrir e editar a validação de dados. No entanto, isso pode ser difícil para usuários inexperientes ou nos casos em que a lista de opções é longa.
Outra opção é colocar a lista em um intervalo nomeado na planilha e depois especificar esse nome do intervalo (precedido por um sinal de igual) no campo Origem: da validação de dados (conforme mostrado na Figura 2).
Este segundo método facilita a edição das opções na lista, mas adicionar ou remover itens pode ser problemático. Como o intervalo nomeado (FruitChoices, em nosso exemplo) se refere a um intervalo fixo de células ($ H $ 3: $ H $ 10 conforme mostrado), se mais opções forem adicionadas às células H11 ou abaixo, elas não aparecerão no menu suspenso (como essas células não fazem parte do intervalo FruitChoices).
Da mesma forma, se, por exemplo, as entradas Peras e Morangos forem apagadas, elas não aparecerão mais no menu suspenso, mas o menu suspenso incluirá dois As opções "vazias" desde o menu suspenso ainda referenciam todo o intervalo FruitChoices, incluindo as células vazias H9 e H10.
Por esses motivos, ao usar um intervalo nomeado normal como fonte de lista para um menu suspenso, o intervalo nomeado ela mesma deve ser editada para incluir mais ou menos células se as entradas forem adicionadas ou excluídas da lista.
Uma solução para esse problema é usar uma dinâmicanome do intervalo como fonte das opções suspensas. Um nome de intervalo dinâmico é aquele que se expande (ou contrai) automaticamente para corresponder exatamente ao tamanho de um bloco de dados à medida que as entradas são adicionadas ou removidas. Para fazer isso, use uma fórmula, em vez de um intervalo fixo de endereços de célula, para definir o intervalo nomeado.
Como configurar uma dinâmica Intervalo no Excel
Um nome de intervalo normal (estático) refere-se a um intervalo especificado de células ($ H $ 3: $ H $ 10 em nosso exemplo, veja abaixo):
Mas um intervalo dinâmico é definido usando uma fórmula (veja abaixo, extraído de uma planilha separada que usa nomes de intervalos dinâmicos):
Antes de começar, faça o download do nosso Arquivo de exemplo do Excel (as macros de classificação foram desativadas).
Vamos examinar essa fórmula em detalhes. As opções para Frutas estão em um bloco de células diretamente abaixo de um cabeçalho (FRUTAS). Também é atribuído um nome a esse cabeçalho: FruitsHeading:
Toda a fórmula usada para definir o intervalo dinâmico para o As opções de frutas são:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)
FruitsHeadingrefere-se ao cabeçalho que está uma linha acima da primeira entrada da lista. O número 20 (usado duas vezes na fórmula) é o tamanho máximo (número de linhas) da lista (isso pode ser ajustado conforme desejado).
Observe que neste exemplo, existem apenas 8 entradas na lista, mas também há células vazias abaixo delas, onde entradas adicionais podem ser adicionadas. O número 20 refere-se a todo o bloco em que as entradas podem ser feitas, e não ao número real de entradas.
Agora, vamos dividir a fórmula em partes (codificando cores em cada peça), para entender como ela funciona. :
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)
A peça "mais interna" é OFFSET (FruitsHeading, 1,0,20,1). Isso faz referência ao bloco de 20 células (abaixo da célula FruitsHeading) onde as opções podem ser inseridas. Essa função OFFSET basicamente diz: Comece na célula FruitsHeading, desça 1 linha e mais de 0 colunas e selecione uma área com 20 linhas e 1 coluna de largura. Isso nos dá o bloco de 20 linhas onde as opções de Frutas são inseridas.
A próxima parte da fórmula é a função ISBLANK:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(the above),0,0),0)-1,20),1)
Aqui, a função OFFSET (explicada acima) foi substituída por "acima" (para facilitar a leitura). Mas a função ISBLANK está operando no intervalo de 20 linhas de células que a função OFFSET define.
O ISBLANK cria um conjunto de 20 valores VERDADEIRO e FALSO, indicando se cada uma das células individuais nos 20- O intervalo de linhas referenciado pela função OFFSET está em branco (vazio) ou não. Neste exemplo, os 8 primeiros valores do conjunto serão FALSE, pois as 8 primeiras células não estão vazias e os 12 últimos serão VERDADEIROS.
A próxima parte da fórmula é a função INDEX:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(the above,0,0),0)-1,20),1)
Novamente, "o acima" refere-se às funções ISBLANK e OFFSET descritas acima. A função INDEX retorna uma matriz que contém os 20 valores TRUE / FALSE criados pela função ISBLANK.
INDEXnormalmente é usado para selecionar um determinado valor (ou intervalo de valores) de um bloco de dados, especificando uma determinada linha e coluna (dentro desse bloco). Mas definir as entradas de linha e coluna como zero (como é feito aqui) faz com que INDEX retorne uma matriz contendo todo o bloco de dados.
A próxima parte da fórmula é a função MATCH:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,the above,0)-1,20),1)
A função MATCHretorna a posição do primeiro valor TRUE, dentro da matriz retornada pela função INDEX. Como as 8 primeiras entradas da lista não estão em branco, os 8 primeiros valores da matriz serão FALSE e o nono valor será TRUE (uma vez que a linha 9 ado intervalo está vazia).
Portanto, a função MATCH retornará o valor de 9. Nesse caso, no entanto, queremos realmente saber quantas entradas estão na lista; portanto, a fórmula subtrai 1 do valor MATCH (que fornece a posição da última entrada). Então, finalmente, MATCH (TRUE, acima, 0) -1 retorna o valor de 8.
A próxima parte da fórmula é a função IFERROR:
=OFFSET(FruitsHeading,1,0,IFERROR(the above,20),1)
A função IFERROR retorna um valor alternativo, se o primeiro valor especificado resultar em um erro. Essa função está incluída, pois, se todo o bloco de células (todas as 20 linhas) for preenchido com entradas, a função MATCH retornará um erro.
Isso ocorre porque estamos dizendo à função MATCH para procurar o primeiro valor VERDADEIRO (na matriz de valores da função ISBLANK), mas se NENHUMA das células estiver vazia, a matriz inteira será preenchida com valores FALSE. Se MATCH não conseguir encontrar o valor alvo (TRUE) na matriz que está pesquisando, retornará um erro.
Portanto, se a lista inteira estiver cheia (e, portanto, MATCH retornar um erro), a função IFERROR será em vez disso, retorne o valor 20 (sabendo que deve haver 20 entradas na lista).
Finalmente, OFFSET (FruitsHeading, 1,0, acima, 1)retorna o intervalo que realmente estamos procurando: Comece na célula FruitsHeading, desça 1 linha e mais de 0 colunas e selecione uma área que tenha muitas linhas, contanto que haja entradas na lista (e 1 coluna de largura). Portanto, a fórmula inteira retornará o intervalo que contém apenas as entradas reais (até a primeira célula vazia).
O uso dessa fórmula para definir o intervalo que é a fonte da lista suspensa significa que você pode editar livremente a lista (adicionando ou removendo entradas, desde que as entradas restantes iniciem na célula superior e sejam contíguas) e o menu suspenso sempre refletirá a lista atual (consulte a Figura 6).
Os arquivo de exemplo (Listas dinâmicas) usados aqui estão incluídos e podem ser baixados deste site. As macros não funcionam, no entanto, porque o WordPress não gosta de livros do Excel com macros.
Como uma alternativa para especificar o número de linhas no bloco de lista, é possível atribuir ao bloco de lista seu próprio nome do intervalo, que pode ser usado em uma fórmula modificada. No arquivo de exemplo, uma segunda lista (Nomes) usa esse método. Aqui, o bloco inteiro da lista (abaixo do cabeçalho "NAMES", 40 linhas no arquivo de exemplo) recebe o nome do intervalo de NameBlock. A fórmula alternativa para definir a NamesList é:
=OFFSET(NamesHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(NamesBlock),0,0),0)-1,ROWS(NamesBlock)),1)
onde NamesBlocksubstitui OFFSET (FruitsHeading, 1,0,20,1) e ROWS (NamesBlock)substitui os 20 (número de linhas) da fórmula anterior.
Portanto, para listas suspensas que podem ser facilmente editadas (inclusive por outros usuários inexperientes), tente usar nomes de faixa dinâmica! E observe que, embora este artigo tenha sido focado em listas suspensas, os nomes dos intervalos dinâmicos podem ser usados em qualquer lugar em que você precise fazer referência a um intervalo ou lista que pode variar em tamanho. Aproveite!