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 na qual os dados serão inseridos, 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 campo Source : (veja a Figura 1).
Neste tipo de lista suspensa básica, 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. Isso pode ser difícil, no entanto, para usuários inexperientes ou em casos em que a lista de opções é longa.
Outra opção é colocar a lista em um intervalo nomeado dentro da planilha e, em seguida, especificar esse nome de intervalo (prefaciado com um sinal de igual) no campo Origem : da validação de dados (conforme mostrado na Figura 2).
Esse 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, no nosso exemplo) se refere a um intervalo fixo de células ($ H $ 3: $ H $ 10, como mostrado), se mais opções forem adicionadas às células H11 ou abaixo, elas não aparecerão no menu suspenso (uma vez que essas células não fazem parte do intervalo FruitChoices).
Da mesma forma, se, por exemplo, as entradas Pears e Strawberries forem apagadas, elas não aparecerão mais no menu suspenso, mas a lista suspensa incluirá duas opções “vazias”, pois a lista suspensa ainda faz referência ao intervalo inteiro do FruitChoices, incluindo as células vazias H9 e H10
Por esses motivos, ao usar um intervalo nomeado normal como a origem da lista de um menu suspenso, o intervalo nomeado deve ser editado para incluir mais ou menos células se as entradas forem adicionadas ou excluídas da lista.
Uma solução para esse problema é usar um nome de intervalo dinâmico como a origem das opções suspensas. Um nome de intervalo dinâmico é aquele que se expande automaticamente (ou contrai) para corresponder exatamente ao tamanho de um bloco de dados conforme as entradas são adicionadas ou removidas. Para fazer isso, você usa uma fórmula, em vez de um intervalo fixo de endereços de células, para definir o intervalo nomeado.
Como configurar um intervalo dinâmico 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, tirada de uma planilha separada que usa nomes de intervalo dinâmico):
Antes de começarmos, baixe nosso arquivo de exemplo do Excel (as macros de classificação foram desativadas).
Vamos examinar essa fórmula em detalhes. As escolhas para Frutas estão em um bloco de células diretamente abaixo de um cabeçalho ( FRUTOS ). Esse título também recebe um nome: FruitsHeading :
A fórmula inteira usada para definir o intervalo dinâmico para as opções de frutas é:
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (VERDADEIRO, ÍNDICE (ISBLANK (OFFSET (FruitsHeading, 1, 0, 20, 1)), 0, 0), 0) -1, 20), 1)
FruitsHeading refere-se ao cabeçalho que é uma linha acima da primeira entrada na 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, nas quais entradas adicionais podem ser adicionadas. O número 20 refere-se ao bloco inteiro onde as entradas podem ser feitas, não ao número real de entradas.
Agora, vamos dividir a fórmula em partes (codificação por cor de cada peça), para entender como ela funciona:
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (VERDADEIRO, ÍNDICE (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. Esta função OFFSET diz basicamente: Comece na célula FruitsHeading, desça 1 linha e mais de 0 colunas e, em seguida, selecione uma área com 20 linhas de comprimento e 1 coluna de largura. Então, 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 (VERDADEIRO, ÍNDICE ( ISBLANK (acima), 0, 0), 0) -1, 20), 1)
Aqui, a função OFFSET (explicada acima) foi substituída por “the above” (para tornar as coisas mais fáceis de ler). Mas a função ISBLANK está operando no intervalo de 20 linhas de células que a função OFFSET define.
ISBLANK então cria um conjunto de 20 valores TRUE e FALSE, indicando se cada uma das células individuais no intervalo de 20 linhas referenciadas pela função OFFSET está em branco (vazia) ou não. Neste exemplo, os primeiros 8 valores no conjunto serão FALSE, uma vez que as primeiras 8 células não estão vazias e os últimos 12 valores serão TRUE.
A próxima parte da fórmula é a função INDEX:
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (VERDADEIRO, ÍNDICE (o acima, 0, 0), 0) -1, 20), 1)
Novamente, “o acima” refere-se às funções ISBLANK e OFFSET descritas acima. A função INDEX retorna um array contendo os 20 valores TRUE / FALSE criados pela função ISBLANK.
INDEX é normalmente 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 ÍNDICE retorne uma matriz contendo o bloco inteiro de dados.
A próxima parte da fórmula é a função MATCH:
= OFFSET (FruitsHeading, 1, 0, IFERROR ( MATCH (VERDADEIRO, acima, 0) -1, 20), 1)
A função MATCH retorna 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 (já que a 9ª linha da faixa estará vazia).
Então a função MATCH retornará o valor de 9 . Nesse caso, no entanto, queremos 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, o acima, 0) -1 retorna o valor de 8 .
A próxima parte da fórmula é a função IFERROR:
= OFFSET (FruitsHeading, 1, 0, IFERROR (acima, 20), 1)
A função IFERROR retorna um valor alternativo, se o primeiro valor especificado resultar em um erro. Esta função é incluída desde que, se o bloco inteiro de células (todas as 20 linhas) estiver preenchido com entradas, a função CORRESP retornará um erro.
Isso ocorre porque estamos dizendo à função MATCH para procurar o primeiro valor TRUE (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 puder encontrar o valor de destino (TRUE) na matriz que está procurando, ele retornará um erro.
Portanto, se a lista inteira estiver cheia (e, portanto, MATCH retorna um erro), a função IFERROR retornará o valor de 20 (sabendo que deve haver 20 entradas na lista).
Por fim, OFFSET (FruitsHeading, 1, 0, acima, 1) retorna o intervalo que realmente estamos procurando: Comece na célula FruitsHeading, desça 1 linha e mais 0 colunas e selecione uma área com muitas linhas de comprimento. existem entradas na lista (e 1 coluna de largura). Assim, a fórmula inteira retornará o intervalo que contém apenas as entradas reais (até a primeira célula vazia).
Usar esta fórmula para definir o intervalo que é a fonte para o menu suspenso significa que você pode editar livremente a lista (adicionando ou removendo entradas, contanto que as entradas restantes comecem na célula superior e sejam contíguas) e a lista suspensa sempre refletirá a corrente lista (veja a Figura 6).
O arquivo de exemplo (Listas Dinâmicas) que foi usado aqui está incluído e pode ser baixado deste site. As macros não funcionam, no entanto, porque o WordPress não gosta de livros do Excel com macros.
Como alternativa à especificação do número de linhas no bloco de listas, o bloco de listas pode receber seu próprio nome de 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 de lista inteiro (abaixo do título "NOMES", 40 linhas no arquivo de exemplo) é atribuído ao nome do intervalo de NameBlock . A fórmula alternativa para definir o NamesList é então:
= OFFSET (NamesHeading, 1, 0, IFERROR (MATCH (VERDADEIRO, ÍNDICE ( ISBLANK ( NamesBlock ), 0, 0), 0) -1, ROWS (NamesBlock) ), 1)
onde o NamesBlock substitui OFFSET (FruitsHeading, 1, 0, 20, 1) e ROWS (NamesBlock) substitui o 20 (número de linhas) na fórmula anterior.
Assim, para listas suspensas que podem ser facilmente editadas (inclusive por outros usuários que podem ser inexperientes), tente usar nomes de intervalo dinâmicos! E observe que, embora este artigo tenha sido focado em listas suspensas, os nomes de 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. Apreciar!
