miércoles, 29 de marzo de 2017

Power Query: Consulta desde Carpeta

Un usuario en otro foro en el que participo planteaba la siguiente cuestión:
[...]Necesito realizar un informe con varios archivos de Excel
concentrado ventas 1,2,3,4,5,...,30
Esto es, con la finalidad de hacer el cierre del mes de las ventas. Los archivos los tengo por separado, ya que los vendedores lo envían individualmente.
Necesito una macro que me consolide los archivos en uno solo y poder así obtener el total de ventas en el mes con su respectivo promedio.[...]

Obviamente se puede realizar con una macro (lo resolveré en una entrada del blog posteriormente), pero hoy nos centraremos en la herramienta estrella de Microsoft: Power Query, o como se bautizó en la versión 2016: Obtener y transformar (Get and transform).

Este complemento está en constante evolución... cada poco tiempo Microsoft lanza actualizaciones y mejoras, lo cual es una garantía de continuidad.


Para resolver la cuestión planteada solo necesitamos cumplir un 'par' de condicionantes:
1- todos los ficheros deben estar en la misma carpeta (para mi ejercicio: 'E:\excelforo\EjemConsolidar').
2- los datos en cada libro estarán en una hoja que se llame de igual forma (en mi ejemplo hoja 'Datos').
3- los datos/encabezados/nombres de campos a consolidar de los distintos libros deben llamarse de igual manera. (NO importa que estén en diferentes posiciones!).


Con estas premisas claras, comenzamos con nuestro ejemplo.
Disponemos de tres orígenes (tres libros de Excel), cada uno correspondiente a un 'vendedor':
AAAA.xlsx
BBBB.xlsx
CCCC.xlsx
con los siguientes datos:

Power Query: Consulta desde Carpeta



Desde un nuevo fichero (lo llamaré 'Consolidado.xlsx'), accedemos a la Ficha Datos > grupo Obtener y transformar (si trabajas con versiones anteriores a 2016, ve a la Ficha de Power Query) > botón Nueva consulta > Desde un Archivo > Desde una carpeta.

Power Query: Consulta desde Carpeta



Esto nos abrirá una ventana donde se nos pregunta por la ruta de la carpeta. Podemos usar el 'browser'/'Examinar' o bien copiar y pegar la ubicación (lo que suele ser más rápido):

Power Query: Consulta desde Carpeta



Tras 'Aceptar' nos aparece una nueva ventana con los ficheros contenidos en la carpeta seleccionada.
Tenemos dos posibilidades:
1- botón Combinar > Combinar y editar
2- botón Editar (en este caso, una vez en la ventana de Edición del Power Query tendremos que Combinar).



Yo prefiero optar por la segunda alternativa: botón Editar.
Se abre una nueva ventana. No desesperes... ya casi terminamos.

En esta ventana indico de dónde tomar los datos.
Aquí cobra importancia las primeras condiciones, en particular aquella en que decíamos:
'2- los datos en cada libro estarán en una hoja que se llame de igual forma.'
Ya que seleccionaremos el objeto hoja 'Datos':

Power Query: Consulta desde Carpeta


Al Aceptar se abre el Editor de consultas de Power Query con los datos de los distintos libors contenidos en nuestra carpeta, y de aquellos con una hoja llamada 'Datos', combinados y dispuestos por columnas (unos debajo de otros).

Power Query: Consulta desde Carpeta


El último paso consiste en trasladar/cargar los datos en nuestro libro de trabajo. Así pues, desde el menú del Editor de consultas, presionamos el botón Cerrar y Cargar > Cerrar y Cargar en..., donde indicaremos el destino final de nuestra tabla consolidada.

Power Query: Consulta desde Carpeta



Mucho más simple, evitando un par de pasos anteriores, sería en la ventana de acción, presionar Combinar y Cargar.

Power Query: Consulta desde Carpeta



En cualquier caso, en nuestra hoja de cálculo aparece el listado consolidado de información de los archivos contenidos en nuestra carpeta.

Power Query: Consulta desde Carpeta



Especialmente interesante es que NUEVOS ficheros, incorporados a la carpeta posteriormente, se verán reflejados en el listado con una simple actualización de datos!!.
¿Qué mas se puede pedir?

jueves, 23 de marzo de 2017

VBA: Suma Selectiva con un ListBox

Un usuario, a partir de este post (ver), preguntaba la manera de acumular/sumar importes correspondientes a una selección de 'clientes':
[...]me gustaría saber como hacer lo mismo pero que me sume la cantidades seleccionadas; por ejemplo, si seleccionara el campo importe[...]


El objetivo es obtener la suma de los importes de aquellos clientes que hubiéramos seleccionado previamente:



Nuestra procedimiento la creamos en la ventana de código de la hoja que contenga los controles ActiveX insertados.

Private Sub CommandButton1_Click()
Dim seleccionados As Integer, lItem As Long
Dim Suma As Double, stClientes As String

Dim MisImportes() As Variant
'recorremos cada elemento del ListBox
For i = 0 To ListBox1.ListCount - 1
    'verificamos si está o no seleccionado
    'en caso afirmativo, acumulamos el contador
    If ListBox1.Selected(i) = True Then
        seleccionados = seleccionados + 1
    End If
Next i
'comprobamos que hay algún elemento seleccionado
If seleccionados = 0 Then
    MsgBox "Debes marcar al menos un cliente"
    Exit Sub
End If
'redimensionamos nuestra Matriz (Array) al tamaño necesario...
'tantas filas como clientes marcados y dos columnas
ReDim MisImportes(1 To seleccionados, 1 To 2) As Variant

x = 0
'recorremos todos los elementos del Listbox
For lItem = 0 To ListBox1.ListCount - 1
    'en caso de que el elemento esté seleccionado
    'lo añadimos a una matriz
    If ListBox1.Selected(lItem) = True Then
        x = x + 1
        'para idetificar listad de clientes
        MisImportes(x, 1) = ListBox1.List(lItem, 0)
        stClientes = stClientes & " " & MisImportes(x, 1)
        
        'para acumlar los importes
        MisImportes(x, 2) = ListBox1.List(lItem, 1)
        Suma = Suma + CDbl(MisImportes(x, 2))
        'dejamos el Listbox sin selección
        ListBox1.Selected(lItem) = False
    End If
Next lItem
'mostramos el resultado final...
MsgBox "Número de clientes seleccionados: " & seleccionados & vbCrLf & _
    "que son: " & stClientes & vbCrLf & _
    "Suma de importes:= " & Suma

End Sub



Para probarlo bastará seleccionar en el ListBox de la hoja los clientes necesarios y presionar el botón 'Ejecutar'.
La respuesta será la esperada, un MsgBox que devuelve el listado de clientes marcados y el importe acumulado de dichos clientes.

martes, 21 de marzo de 2017

Orden Personalizado en Excel

En ocasiones tenemos que realizar ordenaciones en nuestros rangos de datos que no responden a una lógica 'habitual', esto es, no son ordenaciones ascendentes o descendentes... es cuando hablamos de un Orden Personalizado.

Hoy comentaré la opción que nos ofrece Excel para ordenar nuestros datos según un criterio alternativo, según una lista personalizada.


Veámoslo con un ejemplo.
En el rango de la imagen siguiente:

Orden Personalizado en Excel


La meta a lograr es conseguir ordenar nuestros datos según el campo 'Departamento' siguiendo el criterio del rango H1:H4...
Como vemos es un orden distinto, que no responde a ningún patrón... simplemente es un criterio personal.


Nuestro trabajo comenzará entonces accediendo a la herramienta de Listas personalizadas.
La forma más ortodoxa es desde la ficha Archivo > Opciones de Excel > Avanzadas > sección General > botón Modificar Listas Personalizadas.

Orden Personalizado en Excel



Completamos los pasos, seleccionado el rango H1:H4 (donde se encuentre los valores con el orden adecuado), y tras asegurarnos están agregados, presionamos Aceptar.

Orden Personalizado en Excel



Con la Lista personalizada creada, estamos en disposición de realizar nuestra ordenación.

Seleccionamos nuestro rango de datos A1:D21 y desde la ficha Datos > grupo Ordenar y Filtrar > botón Ordenar, que nos abrirá la ventana diálogo con las opciones de ordenación.
Donde seleccionaremos, para este ejemplo:
1- Ordenar por el campo 'Departamento'
2- Según Valores
3- Criterio de ordenación: lista personalizada

nos abrirá la ventana de listas personalizadas, donde seleccionaremos la lista anterior creada:

Orden Personalizado en Excel



Se nos confirma el criterio y estamos dispuestos para Aceptar la condición.

Orden Personalizado en Excel



El resultado es por tanto el esperado... Como vemos en nuestra imagen:

Orden Personalizado en Excel



Meta conseguida.

Esta forma de trabajar es igualmente válida cuando queremos ordenar los meses del año según el orden natural de éstos, y no alfabéticamente!! (algo muy frecuente en tablas dinámicas u otros casos).
Bastaría seleccionar como criterio de ordenación una de las listas de meses predefinidas:

Orden Personalizado en Excel



Consiguiendo nuestros datos queden ordenados por el criterio temporal de los meses, y no el alfabético , que nos distorsionaría el horizonte temporal...