Use nomes de alcance dinâmico no Excel para suspensos flexíveis

As planilhas do Excel geralmente incluem suspensos celulares para simplificar e/ou padronizar a entrada de dados.  Essas 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 Data de validade (no Dados guia), selecione validação de dados, escolha Lista (em permitir :), e depois insira os itens da lista (separados por vírgulas) no Fonte: campo (veja a Figura 1).

Este segundo método facilita a edição das escolhas na lista, mas adicionar ou remover itens pode ser problemático.  Como o alcance nomeado (Fruitchoices, em nosso exemplo) refere -se a uma faixa fixa 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 DropDown (Como essas células não fazem parte da linha Fruitchoices).

Da mesma forma, se, por exemplo, as entradas das peras e morangos forem apagadas, elas não aparecerão mais no menu suspenso, mas, em vez disso, o menu suspenso incluirá duas opções "vazias", já que o menu suspenso ainda faz referência a toda a gama Fruitchoices, incluindo as células vazias H9 e H10.

Por esses motivos, ao usar um intervalo normal como a fonte da lista para uma suspensão, o próprio 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 dinâmico Nome do alcance como fonte para as opções suspensas.  Um nome de intervalo dinâmico é aquele que se expande automaticamente (ou contratos) para corresponder exatamente ao tamanho de um bloco de dados à medida que as entradas são adicionadas ou removidas.  Para fazer isso, você usa um Fórmula, em vez de uma faixa fixa de endereços celulares, para definir o intervalo nomeado.

Como configurar um alcance dinâmico no Excel

Um nome normal (estático) refere -se a uma gama especificada de células ($ H $ 3: $ H $ 10 em nosso exemplo, veja abaixo):

Mas uma faixa dinâmica é definida usando uma fórmula (veja abaixo, retirada de uma planilha separada que usa nomes de faixas dinâmicas):

Antes de começarmos, certifique -se de baixar nosso arquivo de exemplo do Excel (Macros de classificação foram desativados).

Vamos examinar esta fórmula em detalhes.  As opções de frutas estão em um bloco de células diretamente abaixo de um título (Frutas).  Esse título também recebe um nome: FruitSheading:

Toda a fórmula usada para definir a faixa dinâmica para as opções de frutas é:

= Offset (FruitSheading, 1,0, iferror (Match (true, índice (isblank (deslocamento (fruitsheading, 1,0,20,1)), 0,0), 0) -1,20), 1)

FruitSheading refere -se ao cabeçalho que está 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) para a lista (isso pode ser ajustado conforme desejado).

Observe que, neste exemplo, existem apenas 8 entradas na lista, mas também existem células vazias abaixo de onde entradas adicionais podem ser adicionadas.  O número 20 refere -se a todo o bloco onde as entradas podem ser feitas, não para o número real de entradas.

Agora vamos quebrar a fórmula em pedaços (codificação de cores cada peça), para entender como ela funciona:

= Offset (fruta -liquiding, 1,0, iferror (correspondência (true, í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 (embaixo da célula frutada), onde as opções podem ser inseridas.  Esta função de deslocamento diz basicamente: Comece no FruitSheading célula, desça 1 linha e mais de 0 colunas e selecione uma área com 20 fileiras 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 peça da fórmula é o ESTÁ EM BRANCO função:

= Deslocamento (fruta -lamentar, 1,0, iferror (correspondência (true, índice (Isblank (o acima),0,0), 0) -1,20), 1)

Aqui, a função de deslocamento (explicada acima) foi substituída por "os acima" (para facilitar a leitura das coisas).  Mas a função isblank está operando na faixa de 20 linhas de células que a função de deslocamento define.

Isblank então cria um conjunto de 20 valores verdadeiros e falsos, indicando se cada uma das células individuais na faixa de 20 linhas referenciadas pela função de deslocamento está em branco (vazia) ou não.  Neste exemplo, os 8 primeiros valores no conjunto serão falsos, pois as 8 primeiras células não estão vazias e os últimos 12 valores serão verdadeiros.

A próxima peça da fórmula é a função de índice:

= Offset (fruit -lamening, 1,0, iferror (correspondência (true,Índice (o acima, 0,0),0) -1,20), 1)

Novamente, "o acima" refere -se às funções isblances e compensadas descritas acima.  A função de índice retorna uma matriz que contém os 20 valores verdadeiros / falsos criados pela função isblank.

ÍNDICE é normalmente usado para escolher 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 o índice retorne uma matriz contendo todo o bloco de dados.

A próxima peça da fórmula é a função de correspondência:

= Deslocamento (fruta -lamentar, 1,0, iferror (Match (verdadeiro, o acima, 0) -1,20), 1)

O CORRESPONDER Função retorna a posição do primeiro valor verdadeiro, dentro da matriz que é retornada pela função de índice.  Como as 8 primeiras entradas da lista não estão em branco, os 8 primeiros valores na matriz serão falsos e o nono valor será verdadeiro (já que o 9^º Linha no intervalo está vazia).

Portanto, a função de correspondência retornará o valor de 9.  Nesse caso, no entanto, realmente queremos saber quantas entradas estão na lista, então a fórmula subtraia 1 do valor de correspondência (que dá a posição da última entrada).  Então, em última análise, corresponde (verdadeiro, o acima, 0) -1 retorna o valor de 8.

A próxima peça da fórmula é a função iferror:

= Offset (fruta -liquidação, 1,0,IFERROR (o acima, 20),1)

A função iferror retorna um valor alternativo, se o primeiro valor especificado resultar em um erro.  Esta função está incluída, pois, se todo o bloco de células (todas as 20 linhas) estiverem preenchidas com entradas, a função de correspondência retornará um erro.

Isso ocorre porque estamos dizendo à função de correspondência para procurar o primeiro valor verdadeiro (na matriz de valores da função isblank), mas se nenhuma das células estiver vazia, toda a matriz será preenchida com valores falsos.  Se a correspondência não conseguir encontrar o valor alvo (true) na matriz, ele está pesquisando, ele retorna um erro.

Portanto, se a lista inteira estiver cheia (e, portanto, a correspondência retornará um erro), a função IFERROR retornará o valor de 20 (sabendo que deve haver 20 entradas na lista).

Finalmente, Offset (FruitSheading, 1,0, o acima, 1) Retorna o alcance que estamos realmente procurando: comece na célula de fruto, desça 1 linha e mais de 0 colunas e selecione uma área que seja, no entanto, muitas linhas, desde que haja entradas na lista (e 1 coluna de largura).  Portanto, toda a fórmula juntos 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 do suspensão 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 suspensão sempre refletirá a corrente Lista (veja a Figura 6).

O arquivo de exemplo (listas dinâmicas) que foi usado aqui está incluído e é para download deste site. As macros não funcionam, no entanto, porque o WordPress não gosta de livros do Excel com macros.

Como alternativa para especificar o número de linhas no bloco da lista, o bloco da lista 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 este método.  Aqui, todo o bloco da lista (sob o cabeçalho "nomes", 40 linhas no arquivo de exemplo) é atribuído o nome do intervalo de NameBlock.  A fórmula alternativa para definir a lista de nomes é então:

= Deslocamento (namesheading, 1,0, iferror (correspondência (true, índice (isblank (NamesBlock), 0,0), 0) -1,Linhas (NamesBlock)), 1)

onde NamesBlock Substitui o deslocamento (fruta, 1,0,20,1) e Linhas (NamesBlock) substitui os 20 (número de linhas) na fórmula anterior.

Portanto, para listas suspensas que podem ser facilmente editadas (inclusive por outros usuários que podem ser inexperientes), tente usar nomes de faixas dinâmicas!  E observe que, embora este artigo tenha se concentrado nas listas suspensas, os nomes de alcance dinâmico podem ser usados ​​em qualquer lugar que você precise fazer referência a um intervalo ou lista que pode variar em tamanho. Aproveitar!