Listas desplegables dependientes múltiples

En la nota anterior sobre listas desplegables dependientes mostramos cómo crearlas con validación de datos.
En esa nota vimos como crear una lista desplegable de países y ciudades. Una vez elegido el país, la segunda lista desplegable mostraba sólo ciudades de ese país.
Algunos lectores me consultan como hacer lo mismo pero con más de dos niveles de dependencia. Por ejemplo, continentes-países-ciudades.
La técnica es básicamente la misma. Incluimos las listas en rangos nominados (dentro de nombres, usando Insertar-Nombres-Definir) y luego usamos validación de datos con la opción Lista y en Origen usamos fórmulas con la función INDIRECTO.
Supongamos este cuaderno con cuatro hojas



En la hoja Continentes tenemos una lista de los continentes



Estos valores nos servirán como referencia a los nombres que contendrán la lista de países de cada continente. Definimos el rango A1:A7 dentro del nombre "continente"

continente=Continentes!$A$1:$A$7

En la hoja Países creamos campos con las listas de los países de cada continente. Por comodidad (la mía) he puesto sólo dos países por continente



Finalmente ponemos listas de las ciudades por países en la hoja Ciudades




Ahora tenemos que definir los nombres. Una forma de ahorrar tiempo es usar el método Insertar-Nombre-Crear. En la hoja Países seleccionamos la celda A1 y abrimos el menú Insertar-Nombre-Crear



Como queremos que Excel use la fila superior como rótulo para los nombres, señalamos la opción "crear nombres en fila superior".

El resultado será:

África=Paises!$A$2:$A$3
América_del_Norte=Paises!$B$2:$B$3
América_Central=Paises!$C$2:$C$3
América_del_Sur=Paises!$D$2:$D$3
Asia=Paises!$E$2:$E$3
Europa =Paises!$F$2:$F$3
Oceanía=Paises!$G$2:$G$3
Como verán Excel ha agregado "_" en los casos que el nombre del continente está formado por más de una palabra. Esto se debe a que por definición los nombres en Excel no pueden tener espacios en blanco (ni símbolos especiales).

Usamos el mismo método para definir los nombres en la hoja Ciudades, con este resultado

Congo =Ciudades!$A$2:$A$61
Canadá =Ciudades!$B$2:$B$61
México=Ciudades!$C$2:$C$61
Venezuela=Ciudades!$D$2:$D$61
China=Ciudades!$E$2:$E$61
Alemania=Ciudades!$F$2:$F$61
Australia=Ciudades!$G$2:$G$61
Angola =Ciudades!$H$2:$H$61
Estados_Unidos=Ciudades!$I$2:$I$61
Guatemala=Ciudades!$J$2:$J$61
Colombia=Ciudades!$K$2:$K$61
Vietnam=Ciudades!$L$2:$L$61
Francia =Ciudades!$M$2:$M$61
Islas_Fidji=Ciudades!$N$2:$N$61

Como ven, todos los nombres tiene el mismo tamaño de rango (de la fila 2 a la 61), lo que hará que en la lista desplegable aparezcan espacios en blanco. Más adelante veremos como solucionar este problema.

Ahora que hemos definido todos los nombres, definimos las listas desplegables en la hoja "Elección"

En la celda B1 definimos la lista con la opción Lista y la fórmula "=continente". Esto crea una referencia al rango que contiene los nombres de los continentes



En la celda B2 creamos la lista de países que será dependiente del continente elegido en la celda B1



En este caso creamos la referencia al rango usando la función INDIRECTO. Además tenemos que usar la función SUSTITUIR para poner las líneas "_" en lugar de los espacios entre las palabras, para que el valor de la celda coincida con el nombre del rango

=INDIRECTO(SUSTITUIR(B1," ","_"))

Usamos la misma técnica en la celda B3, usando como referencia el valor de la celda B2



Si elegimos el continente América del Sur, podremos elegir sólo Venezuela o Colombia. Si elegimos Colombia podremos elegir una de las ciudades que hemos incluido en la columna Colombia de la hoja Ciudades



Si fuera necesario podríamos agregar más listas dependientes creando los campos adecuados (barrios, jurisdicciones, etc.).

La técnica que hemos mostrado adolece de un defecto estético, los espacios en blanco. Para solucionar este problema tenemos que usar rangos dinámicos. Normalmente usamos la función DESREF para crear rangos dinámicos. El problema en nuestro caso es que la función INDIRECTO solo acepta rangos como argumento.
Para superar este problema usaremos esta fórmula en la creación de la lista desplegable dependiente de la celda B3 (ciudades)

=DESREF(INDIRECTO(SUSTITUIR(B2," ","_")),0,0,CONTARA(INDIRECTO(SUSTITUIR(B2," ","_"))),1)



Ahora la lista dependientes de ciudades no mostrará espacios en blanco.



La fórmula fue tomada del excelente sitio Contextures de Debra Dalgleish.

El cuaderno con el ejemplo y las fórmulas puede descargarse aquí.

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.

Listas ordenadas en validación de datos de Excel - segunda nota

Esta es una ampliación a mi nota anterior sobre el tema. A partir del comentario de un lector he notado la posibilidad de usar una función matricial en lugar de columnas auxiliares.
Básicamente usaremos las mismas funciones, pero combinándolas en una única fórmula matricial.
Volviendo a nuestro ejemplo, tenemos esta lista



donde hemos creado dos columnas: B para obtener el número de orden de los miembros de la lista y en C la lista ordenada alfabéticamente para crear nuestra validación de datos ordenada.

Para mostrar la solución con fórmula matricial empezamos por crear una nueva hoja, "matricial", y poniendo un rango dinámico para la lista de valores de la columna A en el nombre "frutasm". En la columna B obtendremos la lista ordenada, usando una fórmula matricial



Para el rango de la columna B creamos nombre con rango dinámico, "ordenm"



La fórmula en la columna B es una combinación de las fórmulas que usamos en la solución no matricial (en la hoja "con formulas"), pero utilizando rangos de celdas (matrices) en lugar de una celda por fila

={INDICE(frutasm,COINCIDIR(FILA()-1,CONTAR.SI(frutasm,"<="&frutasm),0))} Como en toda fórmula matricial, la introducimos en el rango de celdas relevante (en nuestro ejemplo B2:B10) usando la combinación de teclas Ctrl+Mayúsculas+Enter.


Para evitar ver los resultados #N/A debidos a las celdas vacías, podemos aplicar formato condicional, pero esto no evitará que los valores #N/A aparezcan en la lista desplegable.



Ahora tenemos que corregir la definición de la lista de validación de datos usando el nombre "ordenm"



Ahora podemos agregar nuevos valores a la lista en la columna A, y estos aparecerán ordenados en la columna B y en la lista desplegable






El archivo con el ejemplo se puede descargar aquí.

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.

Listas ordenadas en validación de datos de Excel

La situación es la siguiente: en una hoja de Excel tenemos una lista que nos sirve como base para crear una lista desplegable con validación de datos. Esta lista cambia de tanto en tanto. Si queremos mantener la lista actualizada sin necesidad de realizar cambios en forma manual, definimos un rango dinámico como ya hemos mostrado en el pasado.
Por comodidad queremos que la lista desplegable aparezca ordenada alfabéticamente. El problema reside en que a lista que nos sirve de base no sigue ningún orden preestablecido. Una posibilidad, obviamente, es ordenar la lista manualmente cada vez que ésta cambie. Pero, como ya habrán supuesto, queremos que esto suceda en forma automática.
Tenemos dos posibilidades: programar un evento o utilizar fórmulas y columnas auxiliares.
Empezaremos por la segunda opción. Supongamos esta lista (la misma que usamos en la nota sobre cómo agregar valores únicos en la lista desplegable de validación de datos)



Definimos un nombre con un rango dinámico



y creamos una lista desplegable con validación de datos



Al abrir la lista desplegable, veremos los miembros de la lista en el mismo orden que figuran en el rango original



Para ordenarlos con fórmulas empezamos por crear una columna auxiliar, donde le daremos a cada miembro un número de orden con la técnica que mostramos en la nota sobre cómo ordenar texto con fórmula en Excel. Creamos la columna auxiliar "No. de orden" y usamos esta fórmula:

=CONTAR.SI(frutas,"<="&A2)

Ahora creamos una segunda columna auxiliar, donde ordenamos la lista usando las funciones INDICE y COINCIDIR

=INDICE(frutas,COINCIDIR(FILA()-1,orden,0))



Hemos creado un segundo nombre, "orden", que es también un rango dinámico que contiene los números de orden de la columna auxiliar. En la fórmula que usamos en la columna auxiliar "Lista ordenada", usamos la función FILA para obtener el número de orden deseado sin necesidad de cambiarlo manualmente en cada celda.

Todo lo que nos queda por hacer ahora es crear una nombre que contenga el rango de los valores ordenados, es decir, de la columna "Lista ordenada"



Ahora reemplazamos el nombre "Frutas" en la definición de validación de datos, por el nombre "lista_ordenada"



La lista desplegable aparecerá ahora ordenada alfabéticamente



Podemos hacer lo mismo sin columnas auxiliares, programando un evento. Este código hará que con cada cambio nuestra lista se reordene alfabéticamente.

En el módulo Vba de la hoja correspondiente (en nuestro caso, la hoja "evento"), ponemos este código



Como pueden ver, usamos el nombre que define el rango dinámico en nuestro código. La sentencia On error resume next, nos permite borrar todas la lista en la hoja sin que aparezca un mensaje de error.

Ahora al agregar un nuevo valor, por ejemplo "Melón", este aparecerá automáticamente en el lugar deseado.



El archivo con el ejemplo se puede descargar aquí.

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.

Validación de datos entre varias hojas Excel – otra variante

El método que propongo en mi nota anterior para validación de datos entre varias hojas de Excel no funciona en algunos casos, como le ha sucedido a uno de mis lectores. No he logrado encontrar una explicación por qué en mi ejemplo el método funciona pero en el archivo de mi lector no.


18/02/2012
Esta entrada ha sido actualizada con esta nota.

Validación de Datos entre varias hojas de Excel.

Ya hemos visto que cuando queremos aplicar validación de datos refiriéndonos a una lista remota (es decir, que no se encuentra en la hoja a la que queremos aplicar validación de datos), tenemos que usar nombres para crear la referencia a la lista.
Un lector me consulta cómo aplicar validación de datos para evitar registros duplicados, teniendo en cuenta los valores que aparecen en varias hojas del cuaderno.

En una breve búsqueda en la Internet encontré esta respuesta de Bob Umlas (un Excel MVP):


Data validation across sheets isn't supported. You may need an event macro with code to do the checking for you as the worksheet changes (no se puede aplicar validación de datos a través de varias hojas. Habría que usar una macro de tipo evento para hacer la comprobación a medida que se van introduciendo cambios en la hoja).

Estaba a punto de ponerme a escribir la macro, cuando decidí intentar hacerlo de todas maneras sin macros.


El resultado es que si se puede. El truco parece residir en no crear ninguna referencia explícita a las hojas del cuaderno. Paso a explicar.

Esta entrada ha sido actualizada en esta nota.

Presupuesto de viaje con Excel

Desde el 7 al 29 de setiembre estaré de viaje con mis hijas. Pasaremos nuestras vacaciones en la Argentina donde visitaremos Puerto Madryn para hacer el avistaje de ballenas en la península de Valdés, Bariloche y San Martín de los Andes, las Cataratas del Iguazú y por supuesto Buenos Aires, mi ciudad natal.

Cuento todo esto por dos motivos. El primero es que durante esas tres semanas el blog estará inactivo. El segundo es para sugerir cómo se puede construir con Excel un modelo sencillo pero efectivo para programar el presupuesto de un viaje (o de cualquier otra actividad).
Los "ingredientes" de nuestro "plato" serán: tablas dinámicas, validación de datos y rangos dinámicos definidos con nombres y la función DESREF.
También usaremos un evento para validar fechas.

La tabla dinámica funciona como un generador de reportes que nos permitirá agrupar los gastos según los distintos conceptos que hayamos definido (tipo de gastos como vuelos, alojamiento, excursiones, etc.; o gastos por tramos).

Usaremos Validación de Datos para generar listas desplegables en los distintos campos de la lista que será la base de la tabla dinámica.

Aquí pueden descargar el archivo con el ejemplo del presupuesto de viaje.

Empezamos por crear la lista, las base de datos que servirá a la tabla dinámica (en la hoja BD en nuestro ejemplo). Aquí definimos que campos queremos que contenga la tabla. En nuestro ejemplo definimos (empezando por la columna A): Tipo de Gasto, Fecha, Tramo, Detalle, Moneda, Suma, Peso, Dólar, Euro.

Agregamos las últimas cuatro columnas bajo la suposición que tendremos gastos en distintas moneda. En la columna Moneda pondremos la denominación de la moneda en la cual realizamos el gasto (peso, dólar o euro); en las últimas tres convertimos cada suma a su equivalente con fórmulas que mostraremos luego.

En esta hoja hemos agregado también un evento que abre un calendario cuando queremos introducir una fecha en alguna celda de la columna B. Esta técnica la hemos mostrado en la nota sobre validación de fechas en Excel.

Ahora creamos una hoja que llamamos "Parámetros" donde tenemos los valores de tipo de gastos que servirá a la lista desplegable de la validación de datos. Además tenemos un cuadro de cambio cruzado de monedas para las conversiones.



En esta hoja definimos los nombres:

cambio =parametros!$C$2:$F$5
db_range =DESREF(BD!$A$1;0;0;CONTARA(BD!$A:$A);CONTARA(BD!$1:$1))
monedaH=parametros!$C$2:$F$2
monedaV=parametros!$C$2:$C$5
Tipo_de_Gasto=DESREF(parametros!$A$3;0;0;CONTARA(parametros!$A:$A)-1;1)



Antes de crear la hoja con el reporte, introducimos algunos datos en la hoja BD:





Ahora ya podemos crear nuestro reporte, usando una tabla dinámica que ponemos en la hoja Reporte. Por ejemplo si queremos ver el presupuesto por tipo de gasto:



o por tramo



de acuerdo a los campos que arrastremos a la zona de campos de fila.

Technorati Tags: MS Excel

Ocultar gráficos en Excel

En las últimas notas hemos hablado sobre cómo ocultar y mostrar hojas en un cuaderno Excel. Siguiendo con esta onda del "ocultismo", veamos hoy cómo ocultar un gráfico que hemos insertado en una hoja Excel.
Supongamos esta tabla de datos con la cual construimos un gráfico



Una forma de ocultar el gráfico sería ocultar las filas de la hoja sobre las cuales "flota" el gráfico. Pero si queremos ubicar el gráfico a la izquierda de la tabla, no podremos hacerlo sin ocultar, al mismo tiempo, la tabla misma.

La solución consiste en ligar el gráfico a una imagen y crear un nombre que contenga una fórmula con una condición para mostrar u ocultar la imagen. Veamos la solución por partes.

Empezamos por mudar el gráfico cortándolo y pegándolo en una nueva hoja. Nos aseguramos que en la nueva hoja, la ubicación del gráfico coincida con un rango determinado. En nuestro ejemplo, el gráfico está ubicado sobre el rango A1:F16.



Seleccionamos el rango sobre el cual se encuentra el gráfico y le damos un nombre. En nuestro caso lo llamaremos "grafico"



Esto puede hacerse usando el cuado de nombres (como muestro en la imagen) o con el menú Insertar—Nombres—Definir

Ahora seleccionamos una celda en blanco (por ejemplo, H1) y la damos el nombre "ocultar".

Volvemos a la Hoja1, copiamos (Ctrl+C) una celda en blanco cualquiera, por ejemplo J1. Seleccionamos la celda que será el extremo superior izquierdo del gráfico, en nuestro caso D1. Mientras apretamos la tecla Mayúsculas (Shift) abrimos el menú Edición—Pegar vínculo de imagen



Esta opción sólo aparece si apretamos la tecla Mayúsculas (Shift) al abrir el menú Edición. Hemos creado una imagen que está vinculada a la celda J1.



Reemplazamos el vínculo de la imagen, $J$1, por el nombre "grafico" que contiene el rango sobre el cual se encuentra el gráfico



Inmediatamente aparece el gráfico de la Hoja2 que hemos vinculado a la imagen.

Nuestro próximo paso consiste en crear un nombre con una fórmula condicional que muestre u oculte el gráfico y una celda con un valor que sirva de parámetro a esta fórmula.

Creamos una lista desplegable con dos valores, si y no, en la celda C1. Para esto usamos Validación de Datos



Ahora creamos un nombre, mostrar_grafico, conteniendo la siguiente fórmula:

=SI(Hoja1!$C$1="si",grafico,ocultar)

Seleccionamos la imagen en la Hoja1 y reemplazamos el vínculo al nombre "grafico" en la barra de fórmulas por el nombre "mostrar_grafico"



A partir de este momento, cuando seleccionamos "si" en la celda C1, veremos el gráfico. Si seleccionamos "no", el gráfico desaparecerá.

Aquí pueden descargar el archivo con el ejemplo.

Technorati Tags: MS Excel

Valores únicos en lista de Validación de Datos – Explicación de las fórmulas

En la entrada sobre valores únicos en una lista de Validación de datos, usamos fórmulas un tanto complicadas. Me han pedido una explicación más detallada sobre las fórmulas, así que aquí va!

Primero analizaremos las fórmulas en uso en las tablas auxiliares.

El rango en la columna C contiene la fórmula

=SI(CONTAR.SI($A$5:A5,A5)=1,A5,"")

En esta fórmula CONTAR.SI cuenta cuantas veces aparece el valor de la celda en la columna A en el rango. Si aparece una sola vez el resultado es el valor de la celda de la misma fila en la columna A. Si aparece más de una vez, el resultado es "blanco".
Con esta fórmula copiamos al rango en la columna C valores únicos de los valores que aparecen en la columna A.

El problema es que esta lista contiene celdas en blanco, y por lo tanto es poco "elegante" para ser usada como referencia para la lista de Validación de Datos.
Nuestra tarea ahora es reordenar la lista en la columna C de manera que los espacios en blanco aparezcan al final de la lista.

Para lograr esto utilizamos una nueva lista auxiliar en la columna D. Lo que queremos es dar un número de orden a las celdas en la columna C que contengan valores y dejar en blanco cuando la celda en la columna C no contenga ningún valor.
Para lograr esto usamos la fórmulas =SI(CELDA("contents",C5)="","",FILA(C5))

La función CELDA da como resultado el contenido de la celda analizada ("contents" es uno de los parámetros posibles; más información se puede obtener en la ayuda on-line de Excel). La función FILA da como resultado el número de fila de la celda. Si la función CELDA da como un resultado distinto de "blanco", el resultado de la fórmula será el número de fila. Este número nos ayudará a reordenar los valores en la lista auxiliar en la columna F.

En la columna F usamos la fórmula

INDICE($C$5:$C$25,COINCIDIR(K.ESIMO.MENOR($D$5:$D$25,FILA()-4),$D$5:$D$25))

La fórmula INDICE da como resultado el valor del miembro de la matriz indicada (en nuestro caso $C$5:$C$25) que ocupa el lugar indicado por el segundo argumento de la función.

Este segundo argumento utiliza la función COINCIDIR con la función (K.ESIMO.MENOR($D$5:$D$25,FILA()-4) como primer argumento.

Esta función devuelve el k-ésimo menor valor de un conjunto de datos. Una explicación sobre esta función se puede encontrar aquí.

La expresión FILA()-4, el segundo argumento de K.ESIMO.MENOR es la posición, dentro de la matriz de los datos que se van a devolver, determinada a partir del menor de los valores. Como empezamos nuestra lista de la fila 5, restamos 4 para obtener 1 en la primera celda del rango, 2 en la segunda y así sucesivamente.

De esta manera veremos aparecer en la lista en la columna F los valores de acuerdo a su aparición en la columna A.

Para evitar resultados #NUM! cuando la celda en la columna D está en blanco, anidamos la fórmula dentro de una función condicional SI, como explicamos en la entrada sobre el tema.


Categorías: Funciones&Formulas_, Varios_

Technorati Tags: Excel

Valores unicos en una lista de Excel

En la entrada de ayer mostrábamos como generar una lista de valores únicos con validación de datos. La lista de validación de datos se actualizaba a medida que se agregaban nuevos datos en el rango seleccionado. Para lograrlo usamos fórmulas bastante complicadas y tablas auxiliares.

En realidad hay una solución mucho más sencilla para esta tarea. Excel produce este tipo de listas en forma automática. Mostraré esto con un ejemplo.

Supongamos la lista de frutas del ejemplo de ayer




Si después de seleccionar la celda A11 pulsamos simultáneamente las teclas ALT y flecha hacia abajo, se abrirá una lista desplegable que contiene valores únicos de la lista



Utilizando las flechas o moviendo mouse podemos elegir el valor deseado. Para aceptar el valor podemos pulsar el botón derecho del mouse o apretar Enter.




Categorías: Varios_

Technorati Tags: NAMES, Nombres en Excel

Validación de Datos en Excel - Agregar valores únicos a la lista desplegable

Todo usuario que lleve algún tiempo trabajando con Excel, conoce la funcionalidad Validación de Datos.
Esta funcionalidad permite controlar el tipo de datos que son introducidos en un rango determinado. Existen varias posibilidades






Las más interesantes, en cuanto a posibilidades y flexibilidad, son Lista y Personalizada.
Lista permite generar una lista desplegable de la cual puede el usuario elegir valores válidos.
Ya hemos mostrado diferentes técnicas para poblar la lista, en especial haciendo uso de nombres, para referirnos a rangos que se encuentran en otra hoja y para establecer referencias dinámicas.
En una entrada anterior mostraba como crear listas desplegables dependientes. Es decir, que los valores de una lista de validación de datos dependan de la elección de un determinado valor en otra lista.

Hoy veremos como agregar valores únicos a una lista desplegable de validación de datos.
El archivo con el ejemplo se puede descargar aquí.
La técnica consiste en generar una lista de valores únicos en un rango de alguna hoja, que será la referencia de la lista de validación de datos.
Para lograrlo usamos una tabla auxiliar con dos rangos.


En el rango "Valores únicos" usamos la fórmula =SI(CONTAR.SI($A$5:A6,A6)=1,A6,"")
Esta fórmula nos permite establecer si un valor aparece más de una vez. Es importante prestar atención a la definición del rango $A$5:A6, donde la primer celda tiene una referencia absoluta y la segunda una referencia relativa.

En el rango "No. De Orden" generamos un número que nos servirá de argumento en la fórmula que generará la lista de valores únicos.
=SI(CELDA("contents",C5)="","",FILA(C5))
El número es el número de fila sólo par los valores que aparecen por primera vez. Esta fórmula usa como argumento el resultado de la fórmula del rango "Valores únicos".

En el rango "Lista" usamos la fórmula

INDICE($C$5:$C$25,COINCIDIR(K.ESIMO.MENOR($D$5:$D$25,FILA()-4),$D$5:$D$25))

para hacer aparecer los valores que tiene número de orden.
Para evitar resultados #¡NUM!, agregamos una condición (en color verde) para evaluar resultados de error

=SI(ESERROR(INDICE($C$5:$C$25,COINCIDIR(K.ESIMO.MENOR($D$5:$D$25,FILA()-4),$D$5:$D$25))),"",INDICE($C$5:$C$25,COINCIDIR(K.ESIMO.MENOR($D$5:$D$25,FILA()-4),$D$5:$D$25)))

Una explicación detallada de las fórmulas se puede leer aquí.


Categorías: Funciones&Formulas_, Varios_

Technorati Tags: Validacion de Datos