Las hojas de cálculo de Excel a menudo incluyen desplegables de celdas para simplificar y / o estandarizar el ingreso de datos. Estos menús 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 (en la pestaña Datos ), seleccione Validación de datos, elija Lista (debajo de Permitir :) y luego ingrese los elementos de la lista (separados por comas) ) en el campo Fuente : (ver Figura 1).
En este tipo de menú desplegable básico, la lista de entradas permitidas se especifica dentro de la propia validación de datos; por lo tanto, para realizar cambios en la lista, el usuario debe abrir y editar la validación de datos. Sin embargo, esto puede ser difícil para usuarios sin experiencia, o en los casos en que la lista de opciones es larga.
Otra opción es colocar la lista en un rango con nombre dentro de la hoja de cálculo, y luego especificar ese nombre de rango (precedido con un signo igual) en el campo Fuente : de la validación de datos (como se muestra en la Figura 2).
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 inferior, no aparecerán en el menú desplegable (ya 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 la lista desplegable, sino que la lista desplegable incluirá dos opciones "vacías" ya que la lista desplegable aún hace referencia a todo el rango de FruitChoices, incluidas las celdas vacías H9 y H10.
Por estos motivos, cuando se utiliza un rango con nombre normal como fuente de la lista para un menú desplegable, el rango con nombre en sí debe editarse para incluir más o menos celdas si las entradas se agregan o eliminan de la lista.
Una solución a este problema es usar un nombre de rango dinámico como la fuente de las opciones desplegables. Un nombre de rango dinámico es uno que se expande automáticamente (o se contrae) para coincidir exactamente con el tamaño de un bloque de datos a medida que se agregan o eliminan entradas. Para hacer esto, usa una fórmula, en lugar de un rango fijo de direcciones de celda, 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 específico de celdas ($ H $ 3: $ H $ 10 en nuestro ejemplo, vea a continuación):
Pero un rango dinámico se define mediante una fórmula (ver más abajo, tomada de una hoja de cálculo separada que usa nombres de rango dinámico):
Antes de comenzar, asegúrese de descargar nuestro archivo de ejemplo de Excel (se han desactivado las macros de clasificación).
Vamos a examinar esta fórmula en detalle. Las opciones para Frutas están en un bloque de celdas directamente debajo de un encabezado ( FRUTAS ). A ese encabezado también se le asigna un nombre: FruitsHeading :
La fórmula completa que se usa para definir el rango dinámico para las opciones de frutas es:
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (VERDADERO, ÍNDICE (ISBLANK (OFFSET (FruitsHeading, 1, 0, 20, 1)), 0, 0), 0) -1, 20), 1)
FruitsHeading 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 según se desee).
Tenga en cuenta que en este ejemplo, solo hay 8 entradas en la lista, pero también hay celdas vacías debajo de éstas donde se podrían agregar entradas adicionales. El número 20 se refiere a todo el bloque donde se pueden realizar las entradas, no al número real de entradas.
Ahora vamos a desglosar la fórmula (codificar por colores cada pieza), para entender cómo funciona:
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (VERDADERO, ÍNDICE (ISBLANK ( OFFSET (FruitsHeading, 1, 0, 20, 1) ), 0, 0), 0) -1, 20), 1)
La pieza "más interna" es OFFSET (FruitsHeading, 1, 0, 20, 1) . Esto hace referencia al bloque de 20 celdas (debajo de la celda FruitsHeading) donde se pueden ingresar las opciones. Esta función de DESPLAZAMIENTO básicamente dice: Comience en la celda de FruitsHeading, vaya hacia abajo 1 fila y sobre 0 columnas, luego seleccione un área que tenga 20 filas de largo y 1 columna de ancho. Así que eso nos da el bloque de 20 filas donde se ingresan las opciones de Frutas.
La siguiente pieza de la fórmula es la función ISBLANK :
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (TRUE, INDEX ( ISBLANK (el 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á operando en el rango de celdas de 20 filas que define la función OFFSET.
ISBLANK luego crea un conjunto de 20 valores VERDADERO y FALSO, que indica si cada una de las celdas individuales en el rango de 20 filas referenciado por la función DESPLAZAMIENTO está en blanco (vacía) 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 INDEX:
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (VERDADERO, ÍNDICE (el anterior, 0, 0), 0) -1, 20), 1)
De nuevo, "lo anterior" se refiere a las funciones ISBLANK y OFFSET descritas anteriormente. La función INDEX devuelve una matriz que contiene los 20 valores VERDADEROS / FALSOS creados por la función ISBLANK.
Normalmente, el ÍNDICE se usa para seleccionar un determinado valor (o rango de valores) de un bloque de datos, especificando una determinada fila y columna (dentro de ese bloque). Pero al establecer las entradas de fila y columna en cero (como se hace aquí), INDEX devuelve una matriz que contiene todo el bloque de datos.
La siguiente pieza de la fórmula es la función MATCH:
= OFFSET (FruitsHeading, 1, 0, IFERROR ( MATCH (VERDADERO, el anterior, 0) -1, 20), 1)
La función MATCH devuelve la posición del primer valor TRUE, dentro de la matriz que devuelve la función INDEX. Como 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 (ya que la 9ª fila en el rango está vacía).
Así que la función MATCH devolverá el valor de 9 . Sin embargo, en este caso, realmente queremos saber cuántas entradas hay en la lista, por lo que la fórmula resta 1 del valor MATCH (que da la posición de la última entrada). Entonces, en última instancia, MATCH (VERDADERO, el anterior, 0) -1 devuelve el valor de 8 .
La siguiente pieza de la fórmula es la función IFERROR:
= OFFSET (FruitsHeading, 1, 0, IFERROR (el anterior, 20), 1)
La función IFERROR devuelve un valor alternativo, si el primer valor especificado produce un error. Esta función se incluye ya que, si todo el bloque de celdas (las 20 filas) están llenas de entradas, la función MATCH devolverá un error.
Esto se debe a que le estamos diciendo a la función MATCH que busque el primer valor VERDADERO (en la matriz de valores de la función ISBLANK), pero si NINGUNA de las celdas está vacía, entonces toda la matriz se llenará con valores FALSOS. Si MATCH no puede encontrar el valor objetivo (TRUE) en la matriz que está buscando, devuelve un error.
Entonces, si toda la lista está completa (y, por lo tanto, MATCH devuelve un error), la función IFERROR devolverá el valor de 20 (sabiendo que debe haber 20 entradas en la lista).
Finalmente, OFFSET (FruitsHeading, 1, 0, el anterior, 1) devuelve el rango que realmente estamos buscando: Comience en la celda de FruitsHeading, vaya hacia abajo 1 fila y sobre 0 columnas, luego seleccione un área que tenga muchas filas. hay entradas en la lista (y 1 columna de ancho). Entonces, toda la fórmula junto devolverá el rango que contiene solo las entradas reales (hasta la primera celda vacía).
El uso de esta fórmula para definir el rango que es la fuente de la lista desplegable significa que puede editar libremente la lista (agregar o eliminar entradas, siempre que las entradas restantes comiencen en la celda superior y sean contiguas) y la lista desplegable siempre reflejará la lista (ver Figura 6).
El archivo de ejemplo (Listas dinámicas) que se ha utilizado aquí se incluye y se puede descargar desde este sitio web. Sin embargo, las macros no funcionan porque a WordPress no le gustan los libros de Excel con macros.
Como alternativa a la especificación del número de filas en el bloque de lista, al bloque de lista se le puede asignar su propio nombre de rango, que luego se puede usar en una fórmula modificada. En el archivo de ejemplo, una segunda lista (nombres) utiliza este método. Aquí, al bloque de lista completo (debajo del encabezado "NOMBRES", 40 filas en el archivo de ejemplo) se le asigna el nombre de rango de NameBlock . La fórmula alternativa para definir la Lista de nombres es entonces:
= OFFSET (NamesHeading, 1, 0, IFERROR (MATCH (TRUE, INDEX (ISBLANK ( NamesBlock ), 0, 0), 0) -1, ROWS (NamesBlock) ), 1)
donde NamesBlock reemplaza a OFFSET (FruitsHeading, 1, 0, 20, 1) y ROWS (NamesBlock) reemplaza a los 20 (número de filas) en la fórmula anterior.
Por lo tanto, para las listas desplegables que pueden editarse 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!
