Mostrando las entradas con la etiqueta Importar Datos. Mostrar todas las entradas
Mostrando las entradas con la etiqueta Importar Datos. Mostrar todas las entradas

miércoles, octubre 04, 2017

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


lunes, septiembre 11, 2017

¿Quién se ha llevado mi asistente de importar texto?

El título del post es una paráfrasis del popular libro de Spencer Johnson de fines de los 90. El libro trata de las dificultades de afrontar cambios, tanto en el trabajo como en la vida privada. ¿Cómo está relacionado esto con Excel? Todo usuario veterano sabe muy bien a qué me refiero. A lo largo de los años Excel ha ido evolucionando y moviendo nuestro "queso" (la interface del usuario). Algunos cambios han sido revolucionarios y a su vez traumáticos para gran parte de los usuarios, como la cinta en lugar de las barras de menú en Excel 2007. Otros pasan inadvertidos, por ejemplo el cambio en el asistente de Tablas Dinámicas.

En Excel 2016 los asistentes de importar datos (el de texto entre ellos) no se encuentra en la cinta de opciones. En Excel 2007-2013 aparecían en la pestaña Datos bajo Obtener datos externos



 En Excel 2016 el usuario verá ésto


Es decir, el mecanismo de Power Query, bajo la denominación Obtener y Transformar Datos, ha reemplazado al asistente de importar texto (y otros tipos de archivos).
Hay mucha lógica en esta decisión de Microsoft y todo usuario de Excel obtendrá enormes beneficios de esta herramienta. Dicho ésto hay situaciones en las cuales queremos de todas maneras usar el "viejo" asistente de importar datos. Por ejemplo, en el caso de los espacios múltiples que mostré en la nota anterior.
El asistente no ha sido eliminado sino que no aparece en la cinta. Para poder utilizarlo tenemos que llevar a cabo las siguientes acciones:

1 - En Archivo-Opciones seleccionamos al sección Datos 


2 - Marcamos las opciones de nuestro interés, por ejemplo las cuatro que aparecen en la columna de la izquierda. Finalmente apretamos Aceptar.

Ahora esta opciones estarán a nuestra disposición bajo Datos-Obtener Datos-Asistentes Heredados


jueves, septiembre 07, 2017

Power Query – El caso de los espacios rebeldes

Empiezo con una aclaración: todo el crédito de esta nota corresponde a KenPuls que hace dos años atrás publicó la solución al problema que muestro en este post.

Como sabemos Power Query es la mejor herramienta para extraer y transformar datos. Una bendición para los usuarios de Excel que trabajamos con grandes cantidades de datos de distintas fuentes.
Una de las grandes ventajas del Power Query es permitirnos realizar todo tipo de transformaciones, algunas realmente complejas, desde la interface del usuario (UI).

Una de las transformaciones usuales es dividir una columna usando el espacio entre los valores de la celda como criterio.

En Excel (en este ejemplo uso Excel 2013), por ejemplo, podemos usar usamos la funcionalidad Datos-Obterner Datos Extarnos-Dede texto lo que abre el asistente


Podemos ver claramente que el archivo tiene 6 columnas pero al observar con atención veremos que entre las columnas hay más de un espacio. Y, además, la cantidad de espacios entre cada columna no es constante. Sin embargo ésto no es un problema para el asistente de importar texto



Al señalar "Espacio" como separador Excel ignora todos los espacios inncesarios yrealiza la división en la forma deseada.



Power Query tiene, tal como Excel, esa posibilidad desde la interfaz del usuario (el menú) pero el comportamiento es distinto. Empezamos por crear la consulta editándola en el editor de PQ y usando "Dividir columna" con el separador Espacio

El resultado es totalmente distinto del esperado!!!


Esto se debe a que Power Query, a diferencia de Excel, interpreta cada espacio como separador. A esta altura de los acontecimientos volvemos sobre nuestros pasos echamos manos a Transformar-Recortar (Trim, el equivalente de la función ESPACIOS)


pero si lo intentan verán que tampoco ésto nos ayudará en la tarea. El problema es que Recortar (Trim) del PQ sólo elimina los espacios finales

Aquí es donde Ken Puls viene a nuestro rescate. En la nota que menciono al principio, Ken publica una solución basada en una función de PQ.
Sin más preámbulos, vamos a mostrar como usar la función. En el menú de Power Query iniciamos una consulta en blanco


En la ventana del PQ abrimos el editor avanzado


borramos todo el contenido y pegamos este código

(text as text, optional char_to_trim as text) =>
let
char = if char_to_trim = null then " " else char_to_trim,
split = Text.Split(text, char),
removeblanks = List.Select(split, each _ <> ""),
result=Text.Combine(removeblanks, char)
in
result

y presionamos el botón Listo


En la ventana del editor cambiamos el nombre de la función a algo más significativo, como  PowerTrim


Apretamos Cerrar y Cargar; la función aparece ahora en la ventana de las consultas.


Ahora volvemos a abrir la consulta de los datos, creamos una columna personalizada donde la función ha eliminado los espacios inneesarios; borramos la columna original y aplicamos "Dividir columna" a la columna que acabamos de crear. Todo el proceso puede verse en este video




lunes, enero 26, 2015

Tip para escribir macros eficientes en Excel

Hay muchas normas de buenas prácticas y tips para escribir macros eficientes. Una posibilidad raramente mencionada es usar en nuestros códigos métodos incorporados de Excel que podemos grabar con la grabadora de macros.

Un ejemplo puede ser la macro que propuse para importar fechas de un archivo .csv. La macro usa el loop For Each - Next para convertir fechas en formato mes/día/año (como en los Estados Unidos) al formato día/mes/año en uso en la mayoría de los países de habla hispana.

En lugar del código podemos usar la grabadora de macros para usar el método Texto en Columnas (Datos-Texto en Columnas) en nuestro código en lugar del loop For Each - Next. La ventaja inmediata de la grabadora de macros es que nos exime de tener que conocer la sintaxis del método. Además, como veremos más adelante, esta funcionalidad incorporada de Excel es mucho más eficiente.

No nos limitaremos a grabar la macro sino que eliminaremos las partes innecesarias; luego agregaremos variables para que nuestro código sea lo más flexible posible (al contrario del código que resulta de la grabadora de macros).

Supongamos que hemos importado un archivo .csv que contiene 400000 registros de fechas. Como el archivo fue originado en los Estados Unidos, tenemos que cambiar el formato de los datos, tal como explicamos en la nota mencionada.

Después de importar el archivo, activamos la grabadora de macros y usamos Texto en Columnas para transformar las fechas


El código resultante es el siguiente

codigo macro grabado

Eliminamos la primer línea del código, suponiendo que el usuario activará la macro después de haber elegido el rango a convertir. También podemos eliminar las propiedades definidas por defecto, es decir, aquellas que no hemos cambiado (como norma, aquellas donde el valor de la propiedad es False)

código macro

Podemos dar un paso más adelante y permitir al usuario definir donde pegar el resultado (en el código de arriba Destination:=Range("A1")); además queremos verificar que el usuario haya elegido un rango que contengo por lo menos dos celdas

codigo grabado

Ahora podemos verificar cuál es el código que corre más rápido: el que usa el loop For each - Next, de la nota mencionada, o éste basado en el método incorporado de Excel.

En mi máquina (Dell Latitude E5540 con procesador Intel Core i5-4300, 8 GB RAM, Excel 2010 64-bit), la macro que usa Texto en Columnas tomó 4.3 segundos en convertir las 400 mil fechas. La misma tarea con el loop For Each - Next tomó 180 segundos.

Conclusión: siempre considerar usar los métodos incorporados en Excel en nuestras macros.

viernes, enero 16, 2015

Fechas de .csv a Excel - otras técnicas

En relación a la nota sobre los problemas que pueden surgir al importar fechas de un archivo texto .csv a Excel, dos lectores me sugieren técnicas que no fueron tratadas en el post. Javi sugiere usar Texto en Columnas (la opción más obvia que ignoré olímpicamente, mea culpa!) y Miguel (Power User en Español) sugiere usar la propiedad "locale" del Power Query.

Ambas técnicas merecen algo más que un comentario al pie del post, así que vamos a mostrar cómo usarlas.

Recordemos que el formato de fechas en los distintos países genera en potencia un problema al importar fechas de un archivo .csv a Excel. Al abrir directamente un archivo .csv, Excel "decide" que tipo de dato irá en cada celda. Todo lo que se vea como número será transformado en número (también si va precedido por uno o más ceros); todo lo que se vea como fecha será transformado en fecha. Todo lo demás será importado como texto.
Dado que el formato de fecha en los Estados Unidos (y en otras áreas del mundo) es mes/día/año mientras que la mayoría de los países hispanoparlantes y en Europa es día/mes/año, las fechas pueden ser importadas incorrectamente. Por ejemplo la fecha 10/06/2014 en los Estados Unidos es el 6 de octubre, mientras que en la Argentina es el diez de junio.

La técnica sugerida por Javi es usar Texto en Columnas y en el tercer paso elegir el formato MDA lo que transformará las fechas en forma correcta. Este video muestra la técnica



La técnica sugerida por Miguel es más avanzada y requiere que tengamos instalado el complemento Power Query (Excel 2010 en adelante).

Entre otras fuentes el Power Query permite importar datos también de archivos .csv. Una vez importados los datos al editor del Power Query podemos determinar el formato de fecha del archivo de origen (la propiedad "locale") para que estas sean interpretadas correctamente. Al cargar los datos a la hoja de Excel estos serán transformados correctamente.

Los datos en el archivo .csv son los siguientes


El proceso con el Power Query puede verse en este video



miércoles, enero 14, 2015

Importar fechas de un archivo .csv a una hoja de Excel

Ya hemos tocado en el pasado los problemas que pueden surgir cuando abrimos en forma directa archivos texto .csv en hojas de Excel. Por "forma directa" me refiero a archivos .csv abiertos con un doble click o usando Abrir. Excel interpreta los datos de acuerdos a ciertas reglas y pueden producir cambios indeseados. Por ejemplo el texto "012345" que representa, digamos, un número de catálogo será transformado en "12345".

El problema es particularmente grave cuando los datos importados son fechas. Supongamos que recibimos un archivo .csv con una lista de fechas que nos envía una empresa de los Estados Unidos. En los Estados Unidos se usa el formato mes/día/año mientras que en la mayoría de los países hispanoparlantes el formato de fecha es día/mes/año. Al abrir el archivo .csv directamente, los valores que Excel no interpreta como fechas de acuerdo a las definiciones regionales serán transformados en texto.Veamos este ejemplo:

Podemos ver que algunas fechas están alineadas a la izquierda y otras a la derecha.  Los valores alineados a la derecha han sido importados como fechas (al ser números Excel los alinea a la derecha), mientras que los valores alineados a la izquierda son texto. Esto se debe que Excel no puede interpretar  esos valores como fecha siguiendo el formato regional día/mes/año (por ejemplo en la celda A2, donde el número de mes sería 23).

El valor en la celda A3 nos muestra el problema más grave que se puede generar cuando importamos archivos .csv con fechas. La fecha, siguiendo el formato de los Estados Unidos, es el 6 de Octubre pero Excel la ha transformado en el 10 de Junio.

Señalemos que los datos importados deben ser fechas, de manera que podamos realizar operaciones con ellos.

Podemos transformar los textos en fechas usando una fórmula como

=SI(ESTEXTO(A3),VALOR(EXTRAE(A3,4,2)&"/"&IZQUIERDA(A3,2)&"/"&DERECHA(A3,4)),VALOR(TEXTO(A3,"mm/dd/yyyy")))


Podemos ver que el valor de la celda A3 es transformado correctamente por la fórmula en 06/10/2012.

Otra solución es usar una macro para forzar la transformación. La ventaja de la macro consiste en que no debemos crear las fórmulas para cada hoja; podemos guardar la macro en el cuaderno Personal y usarla en cada hoja que necesitemos sin necesidad de cargarla con fórmulas.

El código básico de la macro es

Sub USDdate_to_EURdate()
    Dim rngcell As Range

    On Error Resume Next
    For Each rngcell In Selection
        rngcell = CDate(Format(rngcell, "mm/dd/yyyy"))
    Next rngcell
    On Error GoTo 0

End Sub


Esta macro reemplaza los valores en el rango seleccionado por fechas con formato (dd/mm/yyyy).

Este código más elaborado nos permite elegir el rango donde copiar los resultados

Sub USDdate_to_EURdate_2()
    Dim rngcell As Range
    Dim rngOrigin As Range, rngDest As Range
    Dim iX As Long

    'seleccionar el rango a transformar
    Set rngOrigin = Application.InputBox(prompt:="Seleccione el rango a transformar", _
                                        Title:="Rango a transformar", _
                                        Type:=8)

    'comprobar si el rango elegido es vertical/columna
    If rngOrigin.Columns.Count > 1 Then
        MsgBox "El rango seleccionado debe contener solo una columna", vbCritical, "Error en la seleccion"
        Exit Sub
    End If

    'seleccionar la primer celda del destino
    Set rngDest = Application.InputBox(prompt:="Seleccione la primer celda del rango del destino", _
                                        Title:="Destino", _
                                        Type:=8)
    'comprobar que se haya elegido una sola celda
    If rngDest.Count > 1 Then
        MsgBox "Debe seleccionar solo una celda", vbCritical, "Error en la seleccion"
        Exit Sub
    End If

    Application.ScreenUpdating = False
    On Error Resume Next 'en caso de haber celdas vacias o valores no validos en el rango elegido
    For iX = 0 To rngOrigin.Count - 1
        rngDest.Offset(iX, 0) = CDate(Format(rngOrigin(iX + 1), "mm/dd/yyyy"))
    Next iX
    On Error GoTo 0
    Application.ScreenUpdating = True
 
End Sub




lunes, febrero 10, 2014

Usos del Power Query - integrar datos de distintos archivos

Una de las tareas más frecuente de todo analista es integrar datos de distintos cuadernos. Excel nos permite hacerlo de varias maneras, desde copiar y pegar (la menos recomendable) hasta usando MsQuery, Access o Vba. El nuevo add-in Power Query nos facilita enormemente la tarea, como mostraremos en esta nota.

A los efectos del ejemplo vamos a suponer el siguiente escenario:
  • tres sucursales reportan las ventas en cuadernos de Excel;
  • los cuadernos están guardados en una carpeta común a las tres;
  • las hojas del reporte tienen la misma estructura (más adelante veremos qué pasa cuando esta condición no se cumple).
Nuestro objetivo es integrar los datos de las tres sucursales en una única tabla de manera que podamos analizarlos con facilidad usando tablas dinámicas.

Empezamos por abrir uno de los cuadernos en el editor del Query


En el cuadro del Query elegimos la hoja adecuada ("reporte" en nuestro ejemplo)


y apretamos el botón "Edit" para transferirla a la ventana de editor del Quey. Aquí no aseguramos que lo valores de la primer línea pasen a ser los encabezamientos de las columnas


En la ventana de las definiciones del Query (Query Settings) usamos la posibilidad "Load to data model" (cargar al modelo de datos). Finalmente apretamos "Apply & Close". En la hoja del cuaderno aparece ahora la ventana de los Workbook Queries, mostrando que hay una consulta (query)  con 6000 filas.
Repetimos el proceso para las otras dos sucursales. Al finalizar tendremos esta situación:


Ahora tenemos 3 consultas en el cuaderno. Para integrarlas en una única tabla vamos a usar la opción Append

Al apretar "Ok" Excel crea una nueva consulta con el nombre por defecto "Append1"


Esta nueva consulta tiene 12000 filas. El Power Query no tiene incorporada la posibilidad de combinar varios cuadernos de una vez, por lo que volvemos a repetir la operación integrando Append1 con "reporte(3)".

A diferencia de la veces anteriores, ahora usamos la posibilidad "Load to worksheet" (cargar en la hoja), por lo que obtenemos una tabla de 18000 filas


A partir de aquí todo lo que nos queda por hacer es analizar los datos combinados con las herramientas que Excel pone a nuestra disposición, principalmente tablas dinámicas.

Dos cuestiones quedan abiertas:

  1. ¿qué pasa si tenemos que integrar una cantidad considerables de hojas? ¿Hay alguna manera de automatizar el proceso?
  2. ¿qué hacer si las tablas no son exactamente iguales pero contienen los datos que queremos integrar?
En las próxima nota nos ocuparemos del primer tema




miércoles, febrero 05, 2014

Usos del Power Query - combinar datos de fuentes distintas

En las notas anteriores sobre los usos posibles del Power Query vimos como podemos aprovechar esta herramienta para "aplanar" datos (formato más conveniente para analizarlos con tablas dinámicas), importar datos de diversas fuentes y transformar los datos importados.

En este post vamos a mostrar cómo podemos combinar datos de distintas fuentes con facilidad usando el Power Pivot. Supongamos el siguiente escenario:
  • en un archivo Excel tenemos una lista de productos con los campos: número de catálogo, descripción, cantidad vendida e importe;
  • en otro archivo de Excel tenemos una lista que incluye el costo por unidad de los productos.
Nuestro objetivo es calcular la ganancia bruta (venta - costo de producción).

Antes de la aparición del Power Query y dependiendo del nivel del usuario hubiéramos completado la tarea con alguna de estas técnicas:
  1. abrimos los dos cuadernos; en el cuaderno de ventas traemos el costo de cada producto usando BUSCARV o INDICE;
  2. usando MsQuery; combinamos las hojas de los cuadernos e el MsQuery, creamos la consulta y la transferimos a una hoja de Excel;
  3. usando Access; creamos dos tablas en una base de datos de Access importando los datos de los cuadernos de Excel, creamos una consulta y la exportamos a Excel.
Power Query permite realizar la tarea en forma integral y sencilla, sin necesidad de abrir los cuadernos. En el ejemplo que voy a desarrollar tenemos dos archivos: "Ventas" y "Costo por unidad".

Empezamos por cargar el archivo "Ventas" en la ventana de edición del Power Query. En el grupo "Get external data" (datos externos) de la pestaña del Power Query elegimos "From file--From Excel"


En la ventana de diálogo que se abre elegimos el archivo indicado; al apretar "Ok" se abre la ventana de navegación del Query en la hoja de Excel


En la ventana vemos el icono del cuadernno y uno por cada hoja que contenga. En nuestro ejemplo hay una única hoja. Para cargar la hoja elegida en la ventana del Query apretamos "Edit". Esto nos permitirá realizar cambios y transformaciones en los datos y columnas. En caso de no necesitar realizar ninguna operación sobre los datos podemos apretar "Load", lo que hará que los datos sean transferidos directamente a la hoja de Excel.

En nuestro caso elegimos "Edit" para poder definir que la primera fila sea los encabezados de las columnas


En la sección derecha de la ventana del Query podemos dar un nombre y una descripción al Query (será útil en los próximos pasos), la sección "Applied Steps" (que hemos mencionado en la nota anterior) y la sección "Load Settings" donde podemos elegir entre cargar los datos a una hoja de Excel o al modelo de datos ("Data Model", tema que trataremos en futura nota). En este ejemplo cargamos los datos en la hoja apretando "Apply and Close"




Repetimos el proceso para el archivo con el costo de los productos. Al final del proceso, cuando activamos alguna de las hojas donde hemos transferidos los datos veremos una ventana que nos muestra los queries existentes en el cuaderno.


Ahora podemos combinar las tablas. Para esto usamos la opción Merge en el menú del Power Query lo que abre el diálogo del Merge



En la primer parte del Merge elegimos la tabla de Ventas y señalamos la columna "Producto" que es el campo en común con la tabla de Costos por Unidad; en la segunda parte ponemos, obviamente, la tabla Costo por Unidad y señalamos el campo Producto; marcamos la opción "Only iclude matching rows". Apretamos "Ok" y Excel abre la ventana del editor del Query con los datos combinados.

Como puede apreciarse la a segunda tabla aparece "concentrada" en una única columna con el encabezado "New Column". Podemos expandir y seleccionar qué columnas queremos que aparezcan en la tabla combinada apretando la doble flecha a la derecha de "New Column"


Como la columna Producto ya aparece en la tabla de venta, seleccionamoos sólo Costo por Unidad. Cambiamos el encabezado de la columna a "Costo por Unidad"



Ahora queremos calcular el costo de cada venta, para lo cual tenemos que multiplicar el Costo por Unidad por la Cantidad. Lo hacemos insertando una columna Custom



La forma de construir la columna calculada es bien intuitiva: en la columna "Available columns" seleccionamos "Cantidad" con un doble clic; agregamos * para indicar la multiplicaciòn y con otro doble clic agregamos "Costo por Unidad"; finalmente apretamos Ok.


Por defecto Excel pone el encabezado "Custom", que podemos cambiar a "Costo Total". Ahora podemos agregar una nueva columna: Ganancia bruta (Importe - Costo Total)


Este es el resultado


Todo lo que nos queda es aplicar y cerrar (Apply and Close) lo que transfiere la consulta a la hoja de Excel y de aquì en adelante podemos usar tablas dinámicas o cualquier otra arma de Excel para analizar y resumir los datos


Ahora supongamos que el responsable de los costos nos informa que ha corregido los costos en el cuaderno Costo por Unidad. Todo lo que tenemos que hacer es aplicar "Refresh" (Actualizar) y luego aplicar refresh a la tabla combinada.

Este video demuestra es proceso