Listas desplegables dependientes en Excel con Validación de Datos.

Ya hemos visto que la función Validación de Datos de Excel (Datos---Validación de Datos) permite controlar los datos que son introducidos en una celda. Una de las posibilidades es crear una lista desplegable de la cual el usuario puede elegir el valor a introducir en la celda.
En una nota anterior hemos tratado como evitar duplicados con validación con validación de datos. En otra hemos mostrado un modelo para asignar operarios a máquinas, de manera que cada operario asignado "desaparezca" de la lista desplegable.

En esta nota mostraremos otras posibilidades de crear listas desplegables dependientes.

Supongamos por ejemplo, que queremos de acuerdo al país elegido en una celda, la lista desplegable en la celda contigua muestre solamente ciudades de ese país.

Los pasos a seguir son:

1 – creamos nombres que contengan la lista de los países y las listas de las ciudades de cada uno de los países. Es importante que el nombre del rango que contiene las ciudades sea idéntico al nombre del país.



2 – en la celda de los países aplicamos validación de datos, con la opción "lista" usando el nombre "países"



3 – en la celda de las ciudades aplicamos validación de datos, también con la opción "lista", pero en este caso aplicamos la función INDIRECTO para crear una referencia dinámica al nombre que contiene la lista:

=INDIRECTO($A$5)

Este es el motivo que el nombre del rango de las ciudades coincida con el nombre del país



A partir de este momento, al elegir un nombre de país en la celda de países, la lista de validación de datos se ajusta al nombre que contiene los nombre de las ciudades de ese país

Todo sobre listas desplegables en Excel, técnicas avanzadas y descarga gratuita de ejemplos  en la Caja de Herramientas Excel - Listas Desplegables de JLD. Ver la nota o ir a la página de descarga de la guía.

Ligando celdas a objetos en Excel – Uso de Validación de Datos, INDICE, COINDICIR, y otras funciones Excel un tanto exóticas

Ayer hablaba de la "cámara fotográfica" de Excel. Vimos que esta herramienta permite tomar "instantáneas" de porciones de datos de una hoja Excel, y que esta "foto" es dinámica.
Lo que hace esta herramienta es ligar un rango de celdas al objeto (la imagen), como se puede ver aquí


De manera similar, se puede ligar (referenciar) una celda a un objeto en la hoja de cálculo. Por ejemplo, a un rectángulo. Todo lo que tenemos que hacer es insertar el objeto en la hoja y, estando este seleccionado, tipear la referencia en la barra de fórmulas. Utilizando esta técnica podemos armar un modelo como este


Este modelo nos permite distribuir operarios en distintas máquinas, representando la distribución en forma gráfica. Usando solamente fórmulas y validación de datos, este modelo nos permite controlar que no ubiquemos un mismo operario más de una vez. A medida que vamos ubicando a los operarios, sólo los disponibles aparecen en la lista despegable. Como es costumbre en este blog, el archivo está a vuestra disposición.Paso a explicar cómo funciona el modelo. Cada uno de los rectángulos que representan a los operarios, está ligado a la celda correspondiente en el rango C4:C15 (cuyo nombre es "ocupados").



Este rango contiene validación de datos que genera una lista desplegable que está contenida en el rango E4:E19 de la hoja "operarios", al que dimos el nombre de "lista_operarios". Este es un rango dinámico definido por esta fórmula:

lista_operarios =DESREF(operarios!$E$4,0,0,CONTARA(operarios!$E$4:$E$19)-CONTAR.BLANCO(operarios!$E$4:$E$19),1)

Como ven, este rango contiene sólo los nombres de los operarios disponibles (aquellos que todavía no han sido ubicados en alguna máquina).
Para lograr este efecto, hemos construido algunas columnas auxiliares en la hoja "operarios":

Columna A, Operarios: contiene la lista de todos los operarios (a ver si alguien adivina en quienes está inspirada la lista).

Columna B, Ocupado: muestra quienes están ocupados y quienes disponibles usando esta fórmula

=SI(CONTAR.SI(ocupados,A4)>=1,"ocupado","disponible")
El nombre "ocupados" se refiere al rango =Hoja1!$C$4:$C$15

Columna C, Disponibles: sólo aparecen los nombres de los disponibles.

Columna D, No. De orden: muestra el número de fila del operario disponible, o blanco si no lo está. Aquí utilizamos la fórmula

=SI(CELDA("contenido",C4)="","",FILA(C4))

La función CELDA analiza, en este caso, el contenido de la celda. Aquí la usamos en una proposición lógica para saber si la formula de C4, por ejemplo, da como resultado BLANCO o no. No podemos utilizar la función ESBLANCO, ya que todas las celda en el rango C4:C15 contienen una fórmula y por lo tanto ESBLANCO daría FALSO en todos los casos.

Columna E, lista de operarios: para mostrar sólo los nombres de los disponibles en forma dinámica, usamos la siguiente fórmula:

=SI(ESERROR(INDICE(disponibles,COINCIDIR(K.ESIMO.MAYOR($D$4:$D$19,FILA()-3),$D$4:$D$19,0))),"",INDICE(disponibles,COINCIDIR(K.ESIMO.MAYOR($D$4:$D$19,FILA()-3),$D$4:$D$19,0)))

La primer parte (ESERROR…..) cumple la única función de evitar resultados #NUM cuando la celda de referencia no contiene un valor numérico (como en D10, por ejemplo).


Categorías: Funciones&Formulas_, Varios_

Uso de NOMBRES (NAMES) en Excel – Validación de Datos

En una nota anterior he hablado de las ventajas de usar NOMBRES en Excel. Hemos mostrado que los NOMBRES no sólo pueden servir para identificar rangos (lo que he llamado "nominar" rangos) sino que también pueden contener fórmulas.
Una demostración sobre este tipo de uso de NOMBRES puede verse en esta nota sobre Formato Condicional, y también en esta nota sobre valores #N/A.
Hoy veremos otro uso de como esta funcionalidad nos permite sobreponernos a algunas limitaciones de Excel.
En muchos de los modelos que desarrollo para mis clientes uso Validación de Datos. Esta funcionalidad permite controlar el tipo de datos permitidos.





Por ejemplo podemos establecer que sólo valores enteros entre 10 y 100 sean válidos para un rango determinado.



La variante que yo utilizo en muchos de mis modelos es "Lista"



Cuando usamos esta variante, en cada celda del rango se abre una lista de valores de la cual. Cualquier valor que no figure en la lista no será aceptado por Excel.
El problema con esta variante reside en que Excel aceptará solamente listas que sen encuentren en la misma hoja de la celda que queremos formar. Por ejemplo, no podemos usar una lista que se encuentre en Hoja2 para formar las celdas en Hoja1.
La solución es definir el rango de la lista como NOMBRE. Veamos esto con un ejemplo.
Supongamos que en Hoja2 tenemos una lista de precios:


Y en Hoja1 un formulario para calcular ofertas:



La idea es utilizar Validación de Datos para generar una lista de la cuál se elegirán los productos. El problema, como ya dijimos, es que Excel no permite utilizar rangos que se encuentren en otras hojas. Y nuestra lista de precios se encuentra en la Hoja2.
Para solucionar este problema hacemos lo siguiente:
1 – entramos en la hoja dos y seleccionamos el rango con los números de catálogo de los productos. En el cuadro nombres introducimos el nombre "productos".


2 – En la Hoja1 seleccionamos el rango A7:A12 y entramos en el menú de Validación de Datos. Elegimos la opción "lista".



3 – Pulsamos la ventanilla "origen" e inmediatamente el botón F3, lo que nos permitirá elegir uno de entre los nombre que hemos creado. Elegimos "producto" y pulsamos "aceptar".



4 – A partir de este momento cada vez que seleccionemos una celda en el rango A7:A12, veremos un pequeño triángulo. Si pulsamos este triángulo se abriera una lista que refleja los productos que se encuentran en la Hoja2.



En las celdas del rango B7:B12 utilizamos la fórmula

=SI(ESBLANCO(A7),"",BUSCARV(A7,lista_de_precios,2,0))

de esta manera, cuando elegimos una valor de la lista, automáticamente aparecerá el precio correspondiente.


Como ven, también hemos creado un NOMBRE que contiene el rango A2:B12 en la Hoja2, es decir el catálogo de productos con sus precios.
La función ESBLANCO nos sirve para evitar valores #N/A cuando no hay números de catálogo en la columna A de la Hoja1.


Categorías: Funciones&Formulas_, Manejo de Datos_, Varios_


Technorati Tags:
NOMBRES, Nombres en Excel

Evitando duplicados con Validación de Datos en Excel

La novedad de hoy es que finalmente he instalado el paquete castellano de Office. Así que inauguro la "nueva era" del blog con una nota sobre como evitar los duplicados en una lista.
Este tema está relacionado con los que traté en mis notas "Cómo construir una lista de valores únicos en Excel" y "Cómo comparar dos listas en Excel".
Tanto aquí como en mi blog en inglés he visto muchas entradas relacionadas con el tema de comparación de listas. Muchas veces lo que buscamos es descubrir si hay valores duplicados. Un enfoque distinto es preguntarse cómo evitar la duplicación de valores.
Supongamos que tenemos una hoja de Excel en la cual registramos los números de catálogo de los productos en un recuento de inventario. Nuestro objetivo es evitar que el mismo producto sea anotado en dos líneas. O sea, que en el momento de anotar un número de catálogo, Excel nos advierta en caso que ya exista una línea con este número.
Para lograr esto usamos la funcionalidad de Datos ---> Validación. Supongamos que tenemos esta hoja dónde anotaremos el recuento de inventario

Para descargar el archivo del ejemplo pulsar aqui.

Marcamos el rango de celdas en la columna A dónde anotaremos los número de catálogo y abrimos el menú Datos ---> Validación

Allí elegimos la opción "personalizada" y en la ventanilla escribimos la siguiente fórmula: =CONTAR.SI($A$4:$A$30,A4)=1

El rango $A$4:$A$30 incluye todas las celdas dónde anotaremos los números de catálogo. Es recomendable utilizar Nombres para señalar el rango. Cada vez que registremos un número de catálogo en la columna A, Excel contará cuántas veces aparece en el rango señalado. Si aparece más de una vez, el resultado de la función sera 2 o más y por lo tanto no se cumplirá la condición de la fórmula =CONTAR.SI($A$4:$A$30,A4)=1
Si intentamos registrar un valor ya existente recibiremos una advertencia

Este método tiene un inconveniente. Si copiamos el número de catálogo, por ejemplo de otra hoja, en lugar de escribirlo manualmente, Excel no reconocerá la duplicación y aceptará el registro.

Si te gustó esta nota anotala en del.icio.us

Technorati Tags: CONTAR.SI, COUNTIF, Evitar Duplicados, Validación de Datos en Excel

Categorías: Funciones&Formulas_, Manejo de Datos_