Dos nuevas fuentes de recursos Excel en el blog

Invito a todos mis lectores a visitar dos páginas que acabo de agregar al blog: Recursos Excel de JLD y Tienda JLD Excel.  Pueden acceder a las páginas desde los enlaces en el linkbar del blog

En la página Recursos Excel de JLD podrán ver y descargar algunos de los modelos  y manuales que he desarrollado a lo largo de los años de existencia de este blog:

• Listas Desplegables 
• Generador de Facturas
• Planificador de Proyectos

La página Tienda JLD Excel les permitirá acceder a herramientas y manuales de otros autores que cuentan con mi recomendación.

Al presente podrán ver y descargar los complementos de Optipe, Data Tools Suite y Multimail 

(estoy adherido al programa de afiliados de Optipe y por lo tanto recibo una comisión por cada venta).


En el futuro seguiré agregando más herramientas y recursos para optimizar el trabajo con Excel.

Rangos con Tablas en listas desplegables y comboboxes

No me avergüenzo de decir que soy un fanático de las Tablas. Una de las mejores herramientas de Excel, la mejor, tal vez, después de las tablas dinámicas, el Power Query y el PowerPivot.
Una de las mejores características de las tablas es que crean rangos dinámicos en todo objeto que dependa de ellas. Por ejemplo, si creamos un gráfico basado en una tabla cada cambio se reflejará automáticamente en el gráfico

Al crear una tabla Excel le asigna un nombre, por defecto Tabla1, que podemos cambiar para usar algo más significativo. Por ejemplo, rebautizamos a nuestra tabla de ventas con  "tblVentas"

También veremos que Excel la incluye en administrador de nombres como un nombre definido que se refiere al rango de la tabla

Esto nos lleva a concluir que podemos crear rangos dinámicos, como aquellos que usamos en listas desplegables, sin necesidad de echar mano a fórmulas con las funciones DESREF o INDICE. Pero para poder usar las tablas o las columnas de una tabla como rangos dinámicos tendremos primero que crear nombres definidos que se refieran a esos rangos.

A los efectos del ejemplo supongamos dos tablas de datos. Una contiene nombres de continentes y la otra contiene una columna por cada continente donde se encuentran los países del continente

A la tabla de los continentes le damos el nombre "Continente"; a la segunda tabla le damos el nombre "Paises". Para poder usar la columna de los continentes en una lista desplegable con validación de datos tenemos que crear un nombre definido que se refiera al rango de la columna

Hemos creado el nombre definido "lstContinente" que se refiere a la tabla Continente usando el lenguaje estructural de las tablas: =Continente[Continente] (en este caso el nombre de la tabla y el de la única columna coinciden).
Ahora para definir la lista desplegable con validación de datos en la celda B2 usamos el nombre definido "lstContinente"

Para crear la lista desplegable dependiente tendremos que referirnos a la columna de la tabla Paises que coincide con el continente elegido en B2. Para eso creamos el nombre definido "PaisSelec" que se refiere a esta fórmula

=INDIRECTO("Paises["&valdat!$B$2&"]")

donde "valdat" es el nombre de la hoja; es decir, creamos una cadena de texto con el operador & que la función INDIRECTO convierte en rango.

El archivo se puede descargar aquí.

Si queremos evitar los espacios en blanco al final de algunas de las listas (el rango se determina según el tamaño de la tabla, no de una columna en particular), tendremos que crear una Tabla para cada continente. En este caso sólo necesitamos crear el nombre definido que se refiere al rango de la tabla de continentes.

La lista desplegable en la celda B2 la creamos como en el caso anterior. Para la validación de datos en la celda B3 usamos la fórmula =INDIRECTO(B2).

El ejemplo puede descargarse aquí.

También podemos usar esta técnica para poblar comboboxes y listboxes. En este ejemplo creamos un Userform con dos combobox, una para los continentes y el segundo combobox para los países cuyos valores dependerán del continente elegido. Como base vamos a usar el modelo con tablas separadas por continentes.

Creamos el Userform y agregamos dos comboboxes. La lista de valores del primer combobox  (el que muestra los continentes) lo definimos directamente en el cuadro de propiedades del objeto

Como puede verse, sencillamente ponemos el nombre definido que se refiere a la tabla de continentes.
La lista de valores del segundo combobox debe depender del valor seleccionado en el combobox de continentes para lo cual debemos definir un evento Change del combo de continentes.
Hacemos un doble clic al combobox de los continentes lo que abre el módulo del userform y agrega, por defecto, el evento Change del objeto donde ponemos este código

Private Sub cbxContinentes_Change()
    With Me
        .cbxPaises.RowSource = .cbxContinentes.Value
    End With
End Sub

Ahora podemos probar el funcionamiento del Userform y las cos comboboxes seleccionando el Userform en el editor de VB y apretando F5



Ahora que vemos que nuestro código funciona vamos a mejorarlo agregando una línea para limpiar el valor del combobox de países si el usuario cambia el continente antes de cerrar el Userform

Private Sub cbxContinentes_Change()
    With Me
        .cbxPaises.Value = ""
        .cbxPaises.RowSource = .cbxContinentes.Value
    End With
End Sub

Descargar el archivo del ejemplo.

Tablas dinámicas - uso de Rangos de Consolidación Múltiples

A partir de la versión 2007 de Excel, la funcionalidad "Rangos de consolidación múltiples" desapareció del asistente de de tablas dinámicas:

Pero el "viejo" asistente no desapareció; se puede acceder a él agregando el icono a la barra de herramientas de acceso rápido

En esta nota voy a mostrar una solución posible a la consulta de un lector y al pasar algunas consideraciones sobre cuál es la mejor manera de organziar datos en Excel.

Empecemos por presentar el problema. Nuestro lector tiene esta matriz de datos

pero para poder consolidar datos y realizar cálculos, necesita organizar los datos de esta manera (más adelante veremos si realmente ésta es la mejor manera)

Si nuestra matriz contiene muchos datos la posibilidad de cortar y pegar o arrastrar los rangos puede ser poco práctica. En ese caso podemos echar mano a la funcionalidad Rangos de consolidación múltiples del viejo asistente de tablas dinámicas.

El primer paso es abrir el asistente y seleccionar los rangos a consolidar

Al apretar el botón "Finalizar" obtenemos la tabla

Ahora podemos seleccionar la tabla dinámica y usar Copiar-Pegar Valores para poder usarla como una matriz de datos regular.

Este video muestra el proceso


Los datos quedan organizados como un matriz de datos "plana". En lugar de la forma "plana" de la tabla dinámica, donde cada "Tag" es una columna, podemos organizar los datos en forma tabular con una única columna para los "Tags". Esto reduce el número de columnas de la tabla y hace que sea más fácil de manejar para nuestros cálculos.
Para convertir la tabla dinámica de rangos consolidados en una matriz de datos tabular hacemos un doble clic en el total general

Como resultado se abre una nueva hoja con la matriz de datos

Ahora podemos cambiar el encabezado de las columnas A, B y C y eliminar la columna D.