Use nombres de rango dinámico en Excel para menores flexibles

Las hojas de cálculo de Excel a menudo incluyen menores de celdas para simplificar y/o estandarizar la entrada de datos.  Estos desplegables se crean utilizando la función de validación de datos para especificar una lista de entradas permitidas.

Para configurar una lista desplegable simple, seleccione la celda donde se ingresarán los datos, luego haga clic en Validación de datos (sobre el Datos pestaña), seleccione Validación de datos, elija Lista (en permitir :), y luego ingrese los elementos de la lista (separados por comas) en el Fuente: campo (ver Figura 1).

Este segundo método facilita la edición de las opciones en la lista, pero agregar o eliminar elementos puede ser problemático.  Dado que el rango nombrado (fruitchoices, en nuestro ejemplo) se refiere a un rango fijo de celdas ($ H $ 3: $ H $ 10 como se muestra), si se agregan más opciones a las celdas H11 o debajo, no aparecerán en el menú desplegable (Dado que esas células no son parte del rango de fruitchoices).

Del mismo modo, si, por ejemplo, las entradas de peras y fresas se borran, ya no aparecerán en el menú desplegable, sino que el menú desplegable incluirá dos opciones "vacías" ya que el menú desplegable aún hace referencia a todo el rango de fruitchoices, incluidas las celdas vacías H9 y H10.

Por estas razones, cuando se usa un rango con nombre normal como fuente de lista para un menú desplegable, el rango nombrado en sí mismo debe editarse para incluir más o menos celdas si las entradas se agregan o se eliminan de la lista.

Una solución a este problema es usar un dinámica Nombre de rango como fuente de las opciones desplegables.  Un nombre de rango dinámico es uno que se expande automáticamente (o contrata) para que coincida exactamente con el tamaño de un bloque de datos a medida que se agregan o eliminan las entradas.  Para hacer esto, usas un fórmula, en lugar de un rango fijo de direcciones celulares, para definir el rango nombrado.

Cómo configurar un rango dinámico en Excel

Un nombre de rango normal (estático) se refiere a un rango especificado de celdas ($ H $ 3: $ H $ 10 En nuestro ejemplo, ver más abajo):

Pero un rango dinámico se define utilizando una fórmula (ver más abajo, tomada de una hoja de cálculo separada que utiliza nombres de rango dinámico):

Antes de comenzar, asegúrese de descargar nuestro archivo de ejemplo de Excel (clasifique las macros han sido deshabilitadas).

Examinemos esta fórmula en detalle.  Las opciones de frutas están en un bloque de celdas directamente debajo de un encabezado (Frutas).  A ese encabezado también se le asigna un nombre: Frutas:

Toda la fórmula utilizada para definir el rango dinámico para las opciones de frutas es:

= Offset (frutas, 1,0, iferror (coincidencia (true, índice (isblank (offset (fruadingsing, 1,0,20,1)), 0,0), 0) -1,20), 1)

Frutas se refiere al encabezado que está una fila por encima de la primera entrada en la lista.  El número 20 (usado dos veces en la fórmula) es el tamaño máximo (número de filas) para la lista (esto se puede ajustar como se desea).

Tenga en cuenta que en este ejemplo, solo hay 8 entradas en la lista, pero también hay celdas vacías por debajo de estas donde se pueden agregar entradas adicionales.  El número 20 se refiere a todo el bloque donde se pueden hacer entradas, no al número real de entradas.

Ahora descompongamos la fórmula en pedazos (codificación de colores cada pieza), para comprender cómo funciona:

= Offset (frutas, 1,0, iferror (coincidencia (true, índice (isblank (Offset (frutas, 1,0,20,1)), 0,0), 0) -1,20), 1)

La pieza "más interna" es Offset (frutas, 1,0,20,1).  Esto hace referencia al bloque de 20 células (debajo de la celda de bote de frutas) donde se pueden ingresar las opciones.  Esta función de compensación básicamente dice: Comience en el Frutas celda, baja 1 fila y más de 0 columnas, luego seleccione un área que tenga 20 filas de largo y 1 columna de ancho.  Eso nos da el bloque de 20 filas donde se ingresan las opciones de frutas.

La siguiente pieza de la fórmula es la ESTÁ EN BLANCO función:

= Offset (fruadeading, 1,0, iferror (coincidencia (verdadero, índice (Isblank (lo anterior),0,0), 0) -1,20), 1)

Aquí, la función de compensación (explicada anteriormente) ha sido reemplazada por "lo anterior" (para facilitar la lectura).  Pero la función isblank está funcionando en el rango de celdas de 20 filas que define la función de compensación.

Isblank luego crea un conjunto de 20 valores verdaderos y falsos, lo que indica si cada una de las celdas individuales en el rango de 20 filas referenciada por la función de desplazamiento está en blanco (vacío) o no.  En este ejemplo, los primeros 8 valores en el conjunto serán falsos ya que las primeras 8 celdas no están vacías y los últimos 12 valores serán verdaderos.

La siguiente pieza de la fórmula es la función de índice:

= Offset (frutas, 1,0, iferror (Match (verdadero,Índice (lo anterior, 0,0),0) -1,20), 1)

Nuevamente, "lo anterior" se refiere a las funciones de Isblank y compensación descritas anteriormente.  La función de índice devuelve una matriz que contiene los 20 valores verdaderos / falsos creados por la función ISBLANK.

ÍNDICE se usa normalmente para elegir un cierto valor (o rango de valores) de un bloque de datos, especificando una cierta fila y columna (dentro de ese bloque).  Pero configurar las entradas de fila y columna en cero (como se hace aquí) hace que el índice devuelva una matriz que contenga todo el bloque de datos.

La siguiente pieza de la fórmula es la función de coincidencia:

,

El FÓSFORO La función devuelve la posición del primer valor verdadero, dentro de la matriz que devuelve la función de índice.  Dado que las primeras 8 entradas en la lista no están en blanco, los primeros 8 valores en la matriz serán falsos, y el noveno valor será verdadero (desde los 9^th .

Entonces la función de coincidencia devolverá el valor de 9.  .  8.

La siguiente pieza de la fórmula es la función iferror:

,,1)

.  .

Esto se debe a que estamos diciendo que la función de coincidencia busque el primer valor verdadero (en la matriz de valores de la función isblank), pero si ninguna de las celdas está vacía, toda la matriz se llenará con valores falsos.  Si la coincidencia no puede encontrar el valor de destino (verdadero) en la matriz que está buscando, devuelve un error.

.

Finalmente, Offset (frutas, 1,0, lo anterior, 1) Devuelve el rango que realmente estamos buscando: comience en la celda de puntas de frutas, baje 1 fila y más de 0 columnas, luego seleccione un área que sea muchas filas, ya que hay entradas en la lista (y 1 columna de ancho).  .

.

El archivo de ejemplo (listas dinámicas) que se ha utilizado aquí se incluye y se descarga desde este sitio web. .

Como alternativa a especificar el número de filas en el bloque de la lista, al bloque de la lista se le puede asignar su propio nombre de rango, que luego se puede usar en una fórmula modificada.  .  Nameblock. 

Bloqueo de nombres,Filas (Namesblock)

dónde Filas (Namesblock) .

Entonces, para listas desplegables que se pueden editar fácilmente (incluso por otros usuarios que pueden no tener experiencia), intente usar nombres de rango dinámico!  Y tenga en cuenta que, aunque este artículo se ha centrado en las listas desplegables, los nombres de rango dinámico se pueden usar en cualquier lugar donde necesite hacer referencia a un rango o lista que puede variar en tamaño. Disfrutar!