Usar los nombres de rango dinámico en Excel para las reducciones flexibles

Las hojas de cálculo de Excel a menudo incluyen celdas desplegables 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 introducirán los datos, luego haga clic en Data Validation (en la pestaña Data), seleccione Data Validation, elija List (bajo Allow:), y luego introduzca los elementos de la lista (separados por comas) en el campo Source: (ver Figura 1).

En este tipo de desplegable básico, la lista de entradas permitidas se especifica dentro de la propia validación de datos; por lo tanto, para hacer cambios en la lista, el usuario debe abrir y editar la validación de datos. Sin embargo, esto puede ser difícil para los usuarios inexpertos, 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 (con un prefacio de signo igual) en el campo Source: de la validación de datos (como se muestra en la Figura 2).

Este segundo método facilita la edición de las opciones de la lista, pero añadir o quitar 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 inferiores, no se mostrarán en el desplegable (ya que esas celdas no son parte del rango FruitChoices).

De la misma manera si, por ejemplo, se borran las entradas de Peras y Fresas, ya no aparecerán en el desplegable, sino que el desplegable incluirá dos opciones “vacías” ya que el desplegable todavía hace referencia a todo el rango de FruitChoices, incluyendo las celdas vacías H9 y H10.

Por estas razones, cuando se utiliza un rango nombrado normal como fuente de la lista para un desplegable, el rango nombrado en sí mismo debe ser editado para incluir más o menos celdas si se agregan o eliminan entradas de la lista.

dynamic como la fuente de las opciones desplegables. Un nombre de rango dinámico es aquel que se expande (o contrae) automáticamente para coincidir exactamente con el tamaño de un bloque de datos a medida que se añaden o eliminan entradas. Para ello, se utiliza una fórmula , en lugar de un rango fijo de direcciones de células, 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 células ($H$3:$H$10 en nuestro ejemplo, ver abajo):

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

Antes de empezar, asegúrate de descargar nuestro archivo de ejemplo de Excel (las macros de ordenación han sido desactivadas).

Examinemos esta fórmula en detalle. Las opciones para las Frutas están en un bloque de células directamente debajo de un encabezamiento (FRUITS). A ese encabezamiento también se le asigna un nombre: FrutasHeading:

/p>

La fórmula completa usada para definir el rango dinámico de las elecciones de las frutas es:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)

FrutasCabeza se refiere al encabezamiento que está una fila por encima de la primera entrada de 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 puede ser ajustado como se desee).

Nota que en este ejemplo, hay sólo 8 entradas en la lista, pero también hay celdas vacías debajo de éstas donde se podrían añadir entradas adicionales. El número 20 se refiere al bloque completo donde se pueden hacer las entradas, no al número real de entradas.

Ahora vamos a descomponer la fórmula en pedazos (código de color de cada pedazo), para entender cómo funciona:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)

La pieza “más interior” es OFFSET(FruitsHeading,1,0,20,1). Esto hace referencia al bloque de 20 celdas (debajo de la celda FruitsHeading) donde se pueden introducir las opciones. Esta función de compensación dice básicamente: Empieza en la celda FruitsHeading, baja 1 fila y sobre 0 columnas, luego selecciona un área de 20 filas de largo y 1 columna de ancho. Eso nos da el bloque de 20 filas donde se introducen las opciones de los Frutos.

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

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(the above),0,0),0)-1,20),1)

Aquí, la función OFFSET (explicada anteriormente) ha sido reemplazada por “lo anterior” (para hacer las cosas más fáciles de leer). Pero la función ISBLANK está operando en el rango de 20 filas de células que la función OFFSET define.

ISBLANCO entonces crea un conjunto de 20 valores de VERDADERO y FALSO, indicando si cada una de las celdas individuales en el rango de 20 filas referidas por la función OFFSET está en blanco (vacía) o no. En este ejemplo, los primeros 8 valores del 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(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(the above,0,0),0)-1,20),1)

De nuevo, “lo anterior” se refiere a las funciones ISBLANK y OFFSET descritas anteriormente. La función INDICE devuelve un array que contiene los 20 valores VERDADEROS / FALSOS creados por la función ISBLANK.

>strong>INDEX se utiliza 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 poner las entradas de fila y columna a cero (como se hace aquí) hace que INDEX devuelva 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(TRUE,the above,0)-1,20),1)

La función MATCH devuelve la posición del primer valor VERDADERO, dentro de la matriz que devuelve la función ÍNDICE. Como las primeras 8 entradas de la lista no están en blanco, los primeros 8 valores de la matriz serán FALSOS, y el noveno valor será VERDADERO (ya que la novena fila del rango está vacía).

Así que la función MATCH devolverá el valor de 9. En este caso, sin embargo, realmente queremos saber cuántas entradas hay en la lista, por lo que la fórmula resta 1 del valor de MATCH (que da la posición de la última entrada). Así que en última instancia, MATCH(TRUE,the above,0)-1 devuelve el valor de 8.

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

=OFFSET(FruitsHeading,1,0,IFERROR(the above,20),1)

La función IFERROR devuelve un valor alternativo, si el primer valor especificado resulta en un error. Esta función está incluida ya que, si todo el bloque de celdas (las 20 filas) está lleno de entradas, la función MATCH devolverá un error.

Esto es porque le decimos 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 del objetivo (TRUE) en la matriz que está buscando, devuelve un error.

Así que si la lista entera está llena (y por lo tanto, MATCH devuelve un error), la función IFERROR devolverá en su lugar el valor de 20 (sabiendo que debe haber 20 entradas en la lista).

Finalmente, OFFSET(FruitsHeading,1,0,the above,1) devuelve el rango que realmente estamos buscando: Comience en la celda FruitsHeading, baje 1 fila y sobre 0 columnas, y luego seleccione un área que sea lo más larga posible de filas mientras haya entradas en la lista (y de 1 columna de ancho). Así que toda la fórmula junta devolverá el rango que contiene sólo las entradas reales (hasta la primera celda vacía).

Utilizar esta fórmula para definir el rango que es la fuente del desplegable significa que puede editar libremente la lista (añadiendo o eliminando entradas, siempre que las entradas restantes empiecen en la celda superior y sean contiguas) y el desplegable siempre reflejará la lista actual (ver Figura 6).

El archivo de ejemplo (Listas dinámicas) que se ha utilizado aquí está incluido y se puede descargar de este sitio web. Sin embargo, los 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 la lista, se puede asignar al bloque de la lista su propio nombre de rango, que puede ser utilizado en una fórmula modificada. En el archivo de ejemplo, una segunda lista (Nombres) utiliza este método. Aquí, a todo el bloque de la lista (bajo el título “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.

Así que, para las listas desplegables que pueden ser fácilmente editadas (incluso por otros usuarios que pueden ser inexpertos), ¡intenta 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 los rangos dinámicos se pueden utilizar en cualquier lugar donde se necesite hacer referencia a un rango o lista que puede variar en tamaño. ¡Disfruta!

Soy un ingeniero que usa Excel todos los días, para muchas tareas diferentes. Me encanta trabajar con Excel, y me encanta enseñar a otros sobre Excel. Lea la biografía completa de Mark.

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *