Crear tablas dinámicas con datos externos

No sé cuántos de mis lectores usan o conocen la opción "Utilice una fuente de datos externa" para crear tablas dinámicas

Cuando no existe la necesidad de hacer transformaciones a los datos originales, esta opción es muy conveniente (cuando necesitamos transformar datos Power Query es la herramienta más apropiada).
La idea del post surgió a partir de la consulta de un colega en el trabajo. Su problema: tenía que construir un reporte con una tabla dinámica, pero la tabla con los datos de origen tenía varios cientos de miles de filas, número que iba incrementándose cada mes. A corto plazo la base de datos superaría el millón de filas, más allá de la capacidad de una hoja de Excel.
Inmediatamente comprendí que mi colega importaba los registros (filas) de la base de datos a una hoja de Excel, lo cual es innecesario.
Las ventajas de crear una conexión sin importar físicamente los datos a una hoja de Excel son evidentes:

• evitamos duplicar los datos (también en a base de datos y también en la hoja);
• nuestro archivo será mucha más liviano;
• cuando hay cambios en la base de datos (nuevos registros, por ejemplo) basta con apretar el botón Actualizar para refrescar los datos;
• podemos desconectar las tablas dinámicas creadas del origen y así proteger datos discretos. Sobre este tema pondré un ejemplo al final del post.

Para crear una tabla dinámica con esta técnica empezamos con una hoja vacía. Abrimos el menú Insertar-Tabla dinámica-Utilice una fuente..." y apretamos el botón "Elegir conexión".

Si la conexión que buscamos no aparece en la lista de conexiones disponibles, usamos el botón "Examinar en busca de más" (en la parte inferior izquierda del formulario). En este ejemplo voy a crear una conexión a la base de datos Northwind que puede estar en mi máquina, en la red a la cual estoy conectado o en la Web. Al apretar el botón aceptar veremos l plantilla de la tabla dinámica vacía y la lista de campos que podemos utilizar para crear el reporte dinámico

A partir de aquí podemos crear nuestros reportes sin que los datos de base estén físicamente en la hoja. Por ejemplo, este reporte de ventas donde usamos el campe de Filtro para filtrar los informes por vendedor

Una de las ventajas, como señalamos, es que nuestro archivo será más liviano. El archivo de este ejemplo peso 132 KB. Si hubiéramos importado los datos a una hoja del cuaderno pesaría 427 KB, tres veces más.

La otra ventaja de este método surge cuando trabajamos con datos que requieren discreción. Siguiendo con nuestro ejemplo supongamos que generamos un reporte para cada agente de ventas (que hacemos con facilidad con la técnica que muestro en este post) pero no queremos que puedan ver los resultados de los otros colegas.

Una posibilidad es copiar y pegar los datos en otra hoja con la opción "sólo valores". Pero esta técnica tiene dos inconvenientes:

• es trabajosa si se trata de muchas hojas (agentes en nuestro ejemplo);
• al pegar los datos los formatos de la tabla desaparecen (podemos usar el tip que muestro en este post, pero también es trabajoso).

Si creamos los reportes dinámicos en base a una tabla dinámica "conectada", todo lo que tenemos que hacer es cancelar al conexión

Excel transforma la tabla dinámica en una matriz de valores, sin fórmulas, sin conexiones y manteniendo los formatos. Veamos el proceso en este video

Reducción de valores mostrados en lista desplegable con criterio de búsqueda

Como ya hemos mostrado en este blog, hay muchas formas de crear listas desplegables en Excel:

• con Validación de Datos, Lista;
• incrustando en la hoja un cuadro combinado (combobox) o un cuadro de lista (listbox) de la colección de formularios;
• programando un cuadro combinado o un cuadro de lista en un Userform en el editor de Vba (macros).

Todas las técnicas pueden verse en mi e-book "Listas Desplegables - la guía JLD".

Uno de los requerimientos de los usuarios es la posibilidad de reducir los valores que aparecen en la lista de acuerdo a algún criterio. Supongamos que tenemos una lista de varios cientos de productos y queremos ver sólo aquellos que contengan la palabra "aceite" antes de seleccionar los valores deseados.

En este post voy a mostrar cómo hacerlo usando un ListBox para crear la lista desplegable y un cuadro de texto para introducir el texto del criterio de búsqueda. Todo ésto lo armamos en un Userform y, por supuesto, usaremos código de Vb para activar el modelo.

Esta captura de pantalla  muestra como funciona nuestro modelo. Cada vez que ingresamos un valor en la casilla de textos, la lista desplegable se reduce a los valores que contienen ese texto

El origen de los datos de la lista desplegable es una lista de precios que se encuentra en una tabla que llamaremos "tbl_Productos"

No me voy a extender aquí sobre las bondades de usar Tablas para organizar nuestros datos en las hojas de Excel, tema que he tocado varias veces en este blog.

Creamos un Userform con un cuadro de lista (Listbox), un cuadro de texto (Textbox) y dos botones de comando

Cuando incrustamos el cuadro de lista (Listbox) en el formulario, definimos ciertas propiedades en la ventana de propiedades

Como puede apreciarse RowSource (la fuente de los datos de la lista) se refiere directamente a la tabla con su nombre.
Para facilitar el código que mostramos más adelante, creamos también un nombre definido que se refiere a la tabla

Los códigos detrás de los objetos (que van en el módulo del Userform) son los siguientes:

# - un evento Change para el ListBox. Este evento se dispara cada vez que tecleamos algún valor en la casilla de texto; cuando esta vacía vemos todos los valores de la lista de precios

 Private Sub tboxCriterio_Change()  
   Dim v As Variant, i As Long  
   v = Range("lstProductos").Value  
   With Me.lbxProductos  
   If Len(Me.tboxCriterio.Value) = 0 Then  
     .RowSource = "lstProductos"  
   Else  
     .RowSource = ""  
     For i = LBound(v, 1) To UBound(v, 1)  
       If LCase(v(i, 1)) Like "*" & LCase(tboxCriterio.Value) & "*" Then  
         .AddItem v(i, 1)  
         .List(.ListCount - 1, 1) = v(i, 2)  
       End If  
     Next i  
   End If  
   End With  
 End Sub  

# - un evento Click para el botón Cancelar

 Private Sub cbtCancelar_Click()  
   Unload ufProductos  
 End Sub  

Si estuviéramos usando este formulario en un modelo real tendríamos que escribir código para el botón Aceptar. Aquí estamos mostrando solamente cómo crear la lista desplegable así que dejaremos ese código para algún post en el futuro (en caso que algunos de mis lectores quieran ver como aplicar esta técnica a un ejemplo).

Para activar el Userform usamos el botón "Lista de productos" al cua tiene asociado este código

 Sub listaProductos()  
   ufProductos.Show  
 End Sub  

El misterioso caso de los números cambiantes (Excel y las definiciones regionales)

En algún post del pasado he mencionado que, en ciertas circunstancias, Excel tiende a ser más "amable" de lo deseado. En su afán de ayudarnos Excel toma decisiones y transforma datos a medida que los vamos ingresando en una celda. Por ejemplo, si ingresamos el valor 15/9 Excel supondrá que se trata de la fecha 15 de setiembre y agregará el año corriente resultando 15/09/2017.

Esta característica de Excel conlleva problemas potencialmente graves. El resultado de la transformación está ligada a las definiciones regionales del sistema (Windows). Por ejemplo, si las definiciones regionales de nuestro sistema son las de España e ingresamos 10/9 Excel lo transformará en 10/09/2017 de acuerdo al patrón de la definición (día/mes/año)

Pero si nuestras definiciones regionales son las de Estados Unidos

si bien veremos en la celda 10/9/2017, de acuerdo al patrón mes/día/año, la fecha ingresada es el 9 de octubre.

El problema no está limitado solamente a las fechas. Supongamos que nuestro sistema tiene definiciones regionales donde la coma es el separador de miles y el punto de los decimales. Ahora copiamos este valor de la tasa GBP/USD (libra esterlina / dólar americano) y lo pegamos en una celda de Excel

El resultado será el siguiente

El valor fue copiado de un sitio español donde el separador de decimales es la coma; pero en mi sistema la coma es el separador de miles. Si nos fijamos veremos que en la barra de las fórmulas aparece 13845 (trece mil ochocientos cuarenta y cinco) y en la celda Excel ha aplicado el formato de número.
¿Cómo solucionamos este problema? Si se trata de un valor podemos hacer un clic en el icono de las opciones de pegado y usar el asistente para importar texto

Vamos directamente al paso 3

y cambiamos en Avanzadas las definiciones de separador de decimal y separador de miles. Ésto solucionará el problema.

¿Y si se trata de una tabla con muchos valores? Lo solucionamos con la opción Texto en Columnas (Datos-Herramientas de Datos) o con Power Query, por supuesto, como mostraré en una próxima nota.