Validación de Datos en Celdas de Hojas de Cálculo de Microsoft Excel mediante Listas Desplegables.

Un cordial saludo nuevamente,

Sin mucho preámbulo, el consejo que les presento en esta ocasión habla por sí solo una vez que lo lleven a cabo. Sucede que a veces se tienen Hojas de Cálculo en donde se desea que en una o varias celdas el usuario seleccione una opción de un grupo de opciones posibles y predefinidas. En esta publicación podrán apreciar una manera práctica de otorgarle esa funcionalidad a las Hojas de Cálculo.

En esta ocasión voy a usar como ejemplo un extracto de una Hoja de Cálculo que utilizo para realizar los Cómputos Métricos del acero de refuerzo que se va a requerir para la construcción de Obras Civiles, como se muestra en la Figura 1. Aquí el vínculo desde donde pueden descargar el archivo para tal ejemplo: Validación de Datos con Listas Desplegables en Excel.xlsx.

Figura 1

La Hoja de Cálculo contiene dos tablas. En la tabla superior se lleva a cabo el registro de los Cómputos Métricos de forma consecutiva. En ella notarán una columna nombrada Phi (ϕ) en la cual se deben especificar los diámetros de cabillas (barras de refuerzo) que se usan para el correspondiente corte de acero. La tabla inferior contiene en su primera Columna la denominación en pulgadas o en milímetros de distintos diámetros de cabillas estandarizados según norma venezolana. Se podrá notar, mediante Figura 2, que en total se tienen registradas 24 denominaciones de diámetros.

Figura 2

El aspecto que se quiere facilitar es que al seleccionar cualquier celda bajo la columna Phi (ϕ) de la tabla superior aparezca un botón al lado de la celda, que al presionarlo despliegue una lista con las 24 denominaciones de diámetros que se muestran en la tabla inferior y que se pueda seleccionar una de ellas.

Como primer paso se van a convertir las dos tablas elaboradas en Tablas Formales de Excel para dotarlas de mejor visualización y funcionalidad (esta característica está incorporada en Excel 2007 y versiones posteriores).

1. Se selecciona cualquier celda dentro de la tabla superior y en la cinta de opciones se selecciona la ficha Insertar, luego en el Panel Tablas se hace clic en el comando Tabla. Se despliega un pequeño cuadro de diálogo y se dibuja un borde segmentado alrededor de los límites de la tabla superior como se muestra en la Figura 3.

Figura 3

Se debe asegurar que en el campo ¿Dónde están los datos de la tabla? esté correctamente ingresado el rango que abarque todos los datos de la tabla incluyendo los encabezados, en consecuencia, también hay que dejar activada la opción La tabla tiene encabezados. En caso de que no se haya seleccionado bien el rango de la tabla se puede hacer clic en el botón del extremo derecho del campo mencionado el cual permitirá que se seleccione el rango correcto directamente en la Hoja de Cálculo. Luego se hace clic en Aceptar y la tabla ahora mostrará un aspecto visual como el que se muestra en la Figura 4. El mismo procedimiento se debe aplicar a la tabla inferior tras lo cual las dos tablas deben quedar configuradas como se muestra en la Figura 5.

Figura 4

Figura 5

En este punto debo aclarar que convertir tablas en lo que yo llamo Tablas Formales de Excel no es un requisito previo para realizar la validación de datos en celdas, pero si dotará a las tablas con características importantes que explico en el transcurso de la explicación.

2. Se ubica el cursor del ratón sobre la parte superior de la celda que contiene el nombre del encabezado de la primera columna de la tabla inferior, donde se listan todos los diámetros de cabilla, y cuando el cursor tome forma de flecha negra apuntando hacia abajo se hace clic para seleccionar todos los datos de la columna como se muestra en la Figura 6.

Figura 6

3. Se selecciona la ficha Fórmula en la cinta de opciones y en el Panel Nombres Definidos se selecciona el comando Administrar Nombres. Se despliega un cuadro de diálogo como se muestra en la Figura 7, en donde se reflejan todos los nombres que se tienen asignados a celdas o rangos de celdas del Libro de Trabajo. Se puede notar que Excel automáticamente crea un nombre para las dos Tablas Formales que se han definido previamente.

Figura 7

4. Se hace clic en el botón Nuevo... para crear un nuevo nombre para todo el rango de celdas con las denominaciones de diámetros seleccionadas previamente en el paso 2. Se despliega un cuadro de diálogo como el mostrado en Figura 8. En el campo Nombre se debe ingresar lógicamente la identificación que queremos darle al rango de celdas. En ese caso se optó por colocar Diametros. En el campo Hace referencia a de forma automática refleja el rango de celdas que se ha seleccionado previamente. Notarán que a diferencia de un rango de celdas típico de Excel como $E$13:$E$36 aparece es Tabla3[N°. Realmente este es el nombre incompleto de la referencia de celdas seleccionadas. El nombre completo es =Tabla3[N° mm], lo que pasa es que en el encabezado de columna para que mm apareciera debajo de N° se insertó un salto de línea. Puede comprobarse seleccionando y arrastrando con el ratón hacia abajo en el nombre la referencia, copiar y luego pegar a un editor de texto en donde aparecerá algo como =Tabla3[N° mm] con un salto de línea entre N° y mm. Se notará que mediante las Tablas Formales las referencias son con la sintaxis NombreTabla[NombreColumna]. Este tipo de referencia nueva tiene una implicación importante que se explicará más adelante. Luego, se hace clic en el botón Aceptar tras lo cual vuelve a aparecer el cuadro de diálogo de Administrador de nombres como se muestra en la Figura 9, pero esta vez conteniendo el nombre que se acaba de crear. En este cuadro hacemos clic en el botón Cerrar.

Figura 8

Figura 9

5. En la tabla superior se selecciona el rango de celdas desde C2 a C9 donde están contenidos los diámetros de cabilla usados para cada uno de los cortes de acero de refuerzo especificados en dicha tabla. Después se hace clic en la ficha Datos de la cinta de opciones y en el Panel Herramientas de Datos se hace clic en el comando Validación de Datos tras lo cual aparece el cuadro de diálogo que se aprecia en la Figura 10.

Figura 10

6. En la lista desplegable Permitir se selecciona la opción Lista y se notará que al lado aparecerán dos opciones adicionales de la cual se debe asegurar que esté activa Celda con lista desplegable para que efectivamente muestre una lista desplegable de opciones en las celdas seleccionadas. En el campo Origen se debe especificar el rango de celdas que contiene todas las opciones posibles que se mostrarán en la lista desplegable de cada celda seleccionada. Para este caso particular, por supuesto, son las celdas que contienen los diámetros de cabillas en la tabla inferior. Este Origen se puede especificar de dos maneras: haciendo clic en el botón del extremo derecho que tiene una flecha roja lo que permite seleccionar directamente en la Hoja de Cálculo el rango de celdas y la segunda opción, que se va a utilizar en este ejemplo, es ingresar =Diametros para hacer referencia al rango de celdas que se ha nombrado previamente tal como se muestra en la Figura 11. Luego se hace clic en el botón Aceptar para aplicar la validación a las celdas seleccionadas. Si se selecciona ahora cualquier celda de la columna de diámetros en la tabla superior, al lado derecho de la celda aparecerá un botón con punta de flecha en el cual se podrá hacer clic para que despliegue todo el listado de diámetros posibles dentro de los cuales se puede seleccionar uno de ellos como se aprecia en Figura 12.

Figura 11

Figura 12

Con esto se cumple el objetivo propuesto al principio. Ahora, como la validación de datos en las celdas se hizo a través de un rango de nombres definido como Diametros en base a la tabla inferior que se configuró como Tabla Formal, esto permite una característica interesante y es que si, por ejemplo, se tiene que borrar o agregar una denominación de diámetro de cabilla en la tabla inferior, las validaciones de las celdas de diámetros en la tabla superior se van a actualizar automáticamente para reflejar tales acciones. Llevando esto a la práctica, se hace clic con el botón derecho en la celda E22 y en el submenú Insertar del menú contextual se selecciona el comando Filas de la Tabla Arriba (ver Figura 13) para insertar una fila vacía entre los diámetros 16M y 3/4". En esa fila vacía se ingresan los datos de la cabilla de 18 mm de diámetro como se muestra en la Figura 14.

Figura 13

Figura 14

En este momento se podrá seleccionar cualquier celda en la columna de diámetros de cabilla de la tabla superior y se comprobará que dentro de la lista desplegable de opciones que muestra ahora se incluye la nueva denominación 18M que se ha insertado en la tabla inferior, tal como se aprecia en la Figura 15. Similar situación ocurre cuando en vez de agregar se elimina un registro de diámetro de la tabla inferior.

Figura 15

Para culminar esta publicación, que bastante larga se ha hecho, aclaro que la tabla inferior que se está utilizando para reflejar las denominaciones de diámetros con sus propiedades, en versiones posteriores a Excel 2007 se puede colocar en una Hoja de Cálculo independiente. Con ello se evita tener como en este caso, la tabla de Cómputos Métricos y de diámetros de cabilla con sus propiedades en una misma Hoja que es como lo permite Excel 2007. Pero, para los que tienen dicha versión de Excel una solución muy sencilla puede ser ocultar todas las filas que abarca la tabla inferior y las validaciones de la tabla superior no se verán afectadas. También, si en la tabla superior se selecciona la celda H9 (de la esquina inferior derecha de la tabla) y se presiona la tecla Tabulador (Tab) se podrá notar que se inserta un registro en blanco que conserva el mismo formato de la Tabla Formal, y mágicamente, también conserva la validación en la celda correspondiente al diámetro de cabilla.

Hasta una próxima oportunidad Dios mediante,

Identificación consecutiva y automática de registros en tablas elaboradas en Microsoft Excel versión 2007 o posteriores.

Un cordial saludo,

Es común que cuando se elabora una tabla para tener un registro ordenado de datos en Excel, en una de las columnas iniciales se implemente una identificación consecutiva de los registros que se vayan ingresando progresivamente en dicha tabla. Se puede tratar de numeraciones consecutivas simples (1, 2, 3, ...) o combinaciones alfanuméricas (A-1, A-2, A-3, ...). En el caso de las numeraciones consecutivas simples una de las alternativas que se usa con frecuencia es la de asignar el número inicial en la primera celda de la columna de identificación de registros y, en las celdas consecutivas de esa misma columna, se ingresa una fórmula en la cual se va sumando una unidad al valor de la celda anterior hasta la celda del último registro de la tabla, como se ilustra en la Figura 1.

Figura 1

El inconveniente con esa manera de hacer la identificación es que al eliminar, por ejemplo, la fila del primer registro o de un registro intermedio de la tabla, entonces todas las celdas inferiores de la columna donde se realiza la identificación van a mostrar un error de referencia en vista de que cada celda de identificación depende del valor de la celda anterior para sumarle una unidad, y así mantener la identificación consecutiva como se ilustra en la Figura 2 y Figura 3.

Figura 2 Errores de referencia que se producen al eliminar la fila del primer registro de la Tabla.

Figura 3 Errores de referencia que se producen al eliminar la fila del quinto registro de la tabla.

Una alternativa para solucionar este inconveniente es haciendo uso de la función FILA. Es una función que puede tomar un argumento opcional consistente en una dirección de celda de la cual se desea obtener su número de fila. Por ejemplo, si escribimos en cualquier celda =FILA(D28) como resultado se va a obtener 28. En cambio si se omite el argumento y se escribe solo =FILA() en la celda E13, por ejemplo, entonces la fórmula va a producir el resultado 13 que es el que corresponde a la celda en la cual se ha escrito la función. No es una monstruosidad de fórmula, pero para notar su utilidad práctica en el ejemplo ilustrado anteriormente se debe proceder de la siguiente manera:

1. En la celda A2 se ingresa la fórmula =FILA() - 1. Es obvio que si se quiere que la numeración comience en 1 se debe restar una unidad al resultado de la función porque de otro modo daría como resultado 2, es decir, la fila en que se encuentra la celda A2.

2. Esta fórmula se copia y pega desde la celda A3 a la A10, o de otro modo se arrastra la fórmula haciendo clic en el pequeño cuadrado negro que aparece en la esquina inferior derecha de la celda A2 cuando está seleccionada.

Se notará que se genera una numeración consecutiva igual a la que se generó con la alternativa anterior como se muestra en la Figura 4.

Figura 4 Tabla con fórmula =FILA()-1 en el rango de celdas A2 a A10.

¿Qué inconveniente se ha resuelto realmente con esta nueva fórmula?
Uno muy tedioso que es que cuando se elimine una o varias filas iniciales o intermedias con registros de la tabla (seleccionadas de forma consecutiva o no), en la columna Nro las demás celdas de identificación en vez de mostrar errores de referencia van a ajustar de forma automática la numeración. En la Figura 5 se muestra nuevamente la tabla luego de haber eliminado las filas 3 y 9 seleccionadas simultáneamente y de forma no consecutiva obviamente. Se podrá notar que las fórmulas de la columna Nro se ajustan automáticamente y la numeración ahora llega hasta el 8 en vista de que se han eliminado 2 registros de los 10 que se tenían inicialmente.

Figura 5 Al eliminar los registros 3 y 9 de la Tabla en la columna Nro se ajusta de forma automática la numeración.

Hasta aquí todo bien, pero como se apreciará en la Figura 6 cuando se inserta una nueva fila inicial o intermedia para ingresar un nuevo registro en la tabla se genera un registro en blanco, por lógica, pero tampoco se incluye de forma automática la identificación numérica de dicho registro, aunque en los registros siguientes se mantenga correctamente el orden numérico. Esto obliga a que se tenga que ingresar manualmente la fórmula en esa celda para reflejar la identificación que le corresponde.

Figura 6 Se inserta una fila, pero no se refleja de forma automática la numeración en la columna Nro de la tabla.

Para solucionar de forma sistémica el inconveniente y dejar verdaderamente automatizada la numeración ya sea que se inserten o eliminen registros de la tabla se debe hacer lo siguiente:

1. Se selecciona una celda que esté dentro del área de la tabla que se tiene elaborada. En la cinta de opciones se selecciona la ficha Insertar, luego en el panel Tabla se hace clic en el comando Tabla tras lo cual aparecerá un pequeño cuadro de diálogo como el que se muestra en la Figura 7. Allí se debe asegurar que en el cuadro de texto ¿Dónde están los datos de la tabla? esté ingresado correctamente el rango de celdas que englobe toda la tabla, lo cual se puede apreciar visualmente por el borde punteado que se dibuja alrededor de la tabla. También dejar activada la opción La tabla tiene encabezados en caso de que aplique, como en el ejemplo. Por último, se hace clic en botón Aceptar.

Figura 7 Insertar, Panel de Tabla, Tabla para dar un formato de Tabla

 2. La tabla adoptará un formato visual similar al que se presenta en la Figura 8. Esta es una característica incorporada en Excel desde la versión 2007. De esta manera, cada vez que se inserta una fila inicial o intermedia para ingresar otro registro en la tabla, la identificación numérica en la columna Nro se va ingresar de forma automática como se puede apreciar en la Figura 9 en donde se ha insertado una fila entre los registros 4 y 5.

Figura 8 Tabla adoptando el formato visual a causa del comando Tabla de Excel.

Figura 9 Nueva fila ingresada entre registros 4 y 5, donde la identificación aparece automáticamente.

Otra interesante característica de esta función Tabla es que si se selecciona la celda de la esquina inferior derecha de la tabla y se presiona la tecla Tabulador (Tab) se inserta un nuevo registro en la parte inferior de la tabla con la identificación consecutiva que le corresponde como se puede apreciar en la Figura 10 en la cual se han insertado dos registros adicionales.

Figura 10

Como último punto, también es interesante mencionar que las identificaciones correlativas no tienen que ser en base a sólo números, sino que también se pueden utilizar combinaciones alfanuméricas tales como A-1, A-2, A-3,... ó PTO1, PTO2, PTO3, ... Para ello, en base al ejemplo que se está explicando en la celda A2 en vez de haber escrito la fórmula =FILA()-1 se tendría que escribir lo siguiente según aplique:

A-1, A-2, A-3,...: Usar fórmula ="A-" & FILA()-1
PTO1, PTO2, PTO3,...: Por simple deducción la fórmula sería ="PTO" & FILA()-1

Para ingresar texto literal en la fórmula de una celda se debe encerrar dicho texto entre comillas dobles y para concatenar (unir) un texto literal con el resultado numérico de una fórmula se utiliza el símbolo ampersand (&).

Bueno, hasta aquí me trajo el río y las ganas de escribir. Espero que le puedan sacar provecho a esta funcionalidad. Parece golosina, pero tiene vitamina.

Hasta la próxima con el favor de Dios...