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:

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.

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):

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':

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).

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.

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

En cualquier caso, en nuestra hoja de cálculo aparece el listado consolidado de información de los archivos contenidos en nuestra 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?

Seguir leyendo...

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:

haz clic en la imagen

Nuestro 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.

Seguir leyendo...

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:

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.

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.

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:

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

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

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:

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

Seguir leyendo...

VBA: Validación Datos con Registros Únicos

Una cuestión frecuentemente planteada es cómo conseguir listar empleando la herramienta de Validación de datos, registros únicos y sin vacíos.

Partiremos del siguiente rango de trabajo, donde se repiten en el tiempo para diferentes usuarios el empleo de diferentes aplicaciones del paquete Office de Microsoft, tal como se muestra en la imagen siguiente:

Se ve claramente como el rango B3:E10 está compuesto por diferentes elementos repetidos y otros vacíos. Con un problema añadido, y es que los datos están dispuestos en una matriz (cuando sabemos que la validación de datos solo admite trabajr sobre vectores-una fila o una columna-).

El objetivo es incorporar una validación de datos en la celda G2 que permita introducir, y que muestre, solo los elementos de manera única y de elementos no vacíos.

Así pues, trabajaremos una macro en nuestro Excel.
Nuestra macro la creamos en un módulo estándar de nuestro libro de trabajo.
El código:

Sub ListaValidacionCelda()
Dim rngAplicaciones As Range
Set rngAplicaciones = Range("B3:E10")

Set apps = New Collection
'recorremos el rango
For Each celda In rngAplicaciones
    'nos centramos solo en celdas con valor...
    If celda.Value <> "" Then
        'identificamos valores únicos!!
        On Error Resume Next
        apps.Add celda.Value, CStr(celda.Value)
        On Error GoTo 0
    End If
Next celda

'trasladamos las aplicaciones únicas a una Array
Dim arr As Variant
ReDim arr(1 To apps.Count) As Variant
For i = 1 To apps.Count
    arr(i) = apps(i)
Next i
'para poder generar nuestra validación de celdas
With Range("G2").Validation
    .Delete
    .Add Type:=xlValidateList, _
           AlertStyle:=xlValidAlertStop, _
           Operator:=xlEqual, _
           Formula1:=Join(arr, ",")
End With
'liberamos memoria
Set rngAplicaciones = Nothing
End Sub

La magia del procedimiento se basa en tres puntos:
1- al pasar los datos por una Collection, nos aseguramos un listado de registros únicos... además, han pasado un filtro previo con el condicional de no vacíos.
2- generamos una Array con los elementos de la Collection. Es un paso necesario para poder realizar el tercer paso.
3- con la función Join unimos los elementos de la Array, con un separador de 'coma'... Cadena que añadimos a la herramienta de Validación.

Tras ejecutar nuestro procedimiento, el resultado lo observamos en la celda G2 comentada:

Consiguiendo nuestra meta.

Como curiosidad, si accedemos a la configuración de la validación de datos de nuestra celda G2, veremos la lista generada:

Seguir leyendo...

VBA: Localizar la Última Fila de nuestra Hoja

Recordaremos hoy una pregunta bastante recurrente en las cuestiones que se me plantean.
¿Cómo localizo o encuentro la última fila usada en mi rango de trabajo?.

Tiempo atrás resolvimos la cuestión mediante funciones, sin necesidad de programación...
ver post
pero en este caso daremos uso a técnicas habituales de VBA.

Nuestra macro la creamos en un módulo estándar de nuestro libro de trabajo.

Sub EncontrarUltimaFila()
Dim sh As Worksheet
Dim UltFila1 As Long, UltFila21 As Long, UltFila22 As Long, UltFila3 As Long, UltFila4 As Long

Set sh = ThisWorkbook.ActiveSheet
'Tres métodos fáciles de emplear... y uno diferente

'Método 1: Ctrl + Shift + Down (El rango de trabajo debe comenzar en la celda A1)
UltFila1 = sh.Range("A1").CurrentRegion.Rows.Count

'Método 2.1: Ctrl + Shift + End (Deben existir celda ocupadas en la columna A)
UltFila21 = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row

'Método 2.2: Ctrl + Shift + End (Deben existir celda ocupadas en la columna A)
UltFila22 = sh.Range("A" & Rows.Count).End(xlUp).Row

'Método 3: Empleando UsedRange
UltFila3 = sh.UsedRange.Rows(sh.UsedRange.Rows.Count).Row

'Método 4: Empleando el método Find
UltFila4 = sh.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

MsgBox "Método 1: " & vbTab & UltFila1 & vbCrLf & _
    "Método 2.1: " & vbTab & UltFila21 & vbCrLf & _
    "Método 2.2: " & vbTab & UltFila22 & vbCrLf & _
    "Método 3: " & vbTab & UltFila3 & vbCrLf & _
    "Método 4: " & vbTab & UltFila4 & vbCrLf

End Sub

Si ejecutamos nuestra macro vemos el resultado en el MsgBox:

Son técnicas muy extendidas, pero no por eso dejan de ser prácticas y fáciles de aplicar.
El objetivo de encontrar esta última fila es siempre el mismo, delimitar nuestros rangos de trabajo de una manera dinámica.

Adicionalmente, y de manera muy similar, podemos obtener el último número de columna empleada.
Nuestra macro la creamos en un módulo estándar de nuestro libro de trabajo.

Sub EncontrarUltimaColumna()
Dim sh As Worksheet
Dim UltCol1 As Long, UltCol21 As Long, UltCol22 As Long, UltCol3 As Long, UltCol4 As Long

Set sh = ThisWorkbook.ActiveSheet

'Método 1: Ctrl + Shift + Right (El rango de trabajo debe comenzar en la celda A1)
UltCol1 = sh.Range("A1").CurrentRegion.Columns.Count

'Método 2.1:Ctrl + Shift + End
UltCol21 = sh.Cells(1, sh.Columns.Count).End(xlToLeft).Column

'Método 2.2:Ctrl + Shift + End
UltCol22 = sh.Range("XFD1").End(xlToLeft).Column

'Método 3: Empleando UsedRange
UltCol3 = sh.UsedRange.Columns(sh.UsedRange.Columns.Count).Column

'Método 4: Empleando el método Find
UltCol4 = sh.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

MsgBox "Método 1: " & vbTab & UltCol1 & vbCrLf & _
    "Método 2.1: " & vbTab & UltCol21 & vbCrLf & _
    "Método 2.2: " & vbTab & UltCol22 & vbCrLf & _
    "Método 3: " & vbTab & UltCol3 & vbCrLf & _
    "Método 4: " & vbTab & UltCol4 & vbCrLf

End Sub

El resultado:

Seguir leyendo...

VBA: Insertar un CheckBox en nuestra Hoja con macros

En más de una ocasión me han preguntado por la forma de insertar mediante programación diferentes controles de formulario en celdas de nuestra hoja de cálculo Excel.
Básicamente la idea es automatizar la creación de plantillas o modelos en nuestras hojas.

Veremos qué tan sencillo es.
Nuestra macro la creamos en un módulo estándar de nuestro libro de trabajo.

Sub InsertarControlFormularioCheckBox()
'añadimos un control de formulario tipo CheckBox
'en una celda concreta de nuestra hoja de cálculo
Sheets("Hoja1").CheckBoxes.Add(Left:=Range("B1").Left, _
        Top:=Range("B1").Top, _
        Width:=Range("B1:C1").Width, _
        Height:=Range("B1").Height).Select

'asignamos propiedades a este CheckBox
With Selection
    .Name = "ChkBoxUNO"                     'Damos nombre al Control
    .Caption = "Test Añadir CheckBox"       'Texto a desplegar
    .LinkedCell = "D1"                      'cuál es la celda vínculada
    .Value = xlOn            'devuelve valor Marcado-Verdadero(Marcado/Desmarcado)
    .Display3DShading = True                'por estética damos formato de sombra 3D
End With
End Sub

Como observamos la primera sentencia
.CheckBoxes.Add
nos permite situar el control respecto de la ubicación actual de una celda concreta (en el ejemplo B1), asignándole además de las propiedades .Top y .Left otras dos habituales (Alto-Height y Ancho-Width).

En el siguiente grupo de instrucciones nos lanzamos a completar algunas de las propiedades que encontraríamos en el Formato de Control:
1-Valor (.Value)
2-Celda vinculada (.LinkedCell)
3-Sombreado 3D (.Display3DShading)
4-El Texto mostrado (.Caption)
5-y el más importante el nombre del control!! (.Name)

Con la macro explicada estamos listos para ejecutarla, obteniendo el siguiente resultado en nuestra Hoja1:

Seguir leyendo...

Las Líneas de División o Cuadrícula

Hoy toca algo simple pero muy visual. Aprenderemos a personalizar las Líneas de División o Cuadrícula de las celdas de nuestras hojas de cálculo.

Muy frecuentemente me encuentro que clientes, que no desean ver estas líneas, dan formato de relleno blanco a toda la hoja de cálculo!!.
Obviamente el resultado será el mismo que obtendremos nosotros (solo a priori).. pero esta forma de actuar puede traer asociado un error típico de exceso de formato en nuestras hojas (una especie de corrupción para nuestros ficheros).

La forma de no visualizar estas líneas de cuadrícula es sencilla, desde la Ficha Vista > grupo Mostrar > check en Líneas de Cuadrícula

Esta forma, además de infinitamente más simple, es completamente segura y sin 'efectos secundarios'.

Otra forma de conseguir el mismo efecto sería desde las Opciones de Excel (Ficha Archivo > Opciones), en el menú de Avanzadas > sección Mostrar opciones para esta hoja:

Aquí encontramos, como podemos ver en la imagen, dos posibilidades:
1- Mostrar/Ocultar las líneas de división/cuadrícula
2- Personalizar el color de estas líneas.
En la imagen siguiente vemos el efecto conseguido tras cambiar el color de las líneas a un azul intenso.

Recuerda que esta configuración únicamente afecta a la hoja que indiquemos o sobre la que estemos trabajando en este momento (hoja activa), y nunca sobre otros libros u hojas.

Seguir leyendo...

VBA: Una Macro para crear otras Macros

Buscando respuesta para un cliente me vi en la necesidad de generar un código de manera automática en un segundo libro de trabajo, para que a su vez se ejecutara de manera independiente...
Dudé al principio de que tal cosa fuera posible, pero investigando, encontré una explicación de Chip Pearson que solucionaba mi problema.

Y esto es lo que veremos hoy, cómo es posible crear una macro en Excel que a su vez cree un segunda macro en cualquier otro libro..

Lo más importante de este código, y antes de empezar, es que debemos activar la librería:
Microsoft Visual Basic for Applications Extensibility 5.3
A localizar entre las referencias del Editor de VB:

Esta librería (VBIDE) es fundamental para nuestro propósito, ya que nos proporciona todos los objetos y valores para construir nuestros Proyectos de VB dentro de nuestro Editor de VB.

Nuestra macro la creamos en un módulo estándar de nuestro libro de trabajo.
En el ejemplo, la macro la crearemos en nuestro mismo libro... pero recuerda que es posible trabajar sobre un segundo libro...
El código:

Sub Macro_crea_Macro()
'ACTIVAR REFERENCIA !!!
'Microsoft Visual Basic for Applications Extensibility 5.3

Dim TxtMacro As String
'Escribimos el código de la macro nueva que generará esta...
TxtMacro = "'Esta macro ha sido creada por otra macro" & Chr(13) & _
            "Sub MacroNueva()" & Chr(13) & _
            "MsgBox ""Macro Nueva generada con éxito!!""" & Chr(13) & _
            "End Sub"

Dim VBComps As VBComponents
Set VBComps = ThisWorkbook.VBProject.VBComponents
'en este ejemplo trabajamos sobre Este libro de trabajo,
'pero pdríamos crear la Nueva Macro en cualquier otro libro... !!

Dim VBComp As VBComponent
Dim VBCodeMod As CodeModule

'Aquí creamos un nuevo módulo estándar...
Set VBComp = VBComps.Add(vbext_ct_StdModule)
Set VBCodeMod = VBComp.CodeModule
'al que le damos nombre:
VBComp.Name = "Mod_B_Excelforo"

'Finalmente, insertamos el código en el módulo
With VBCodeMod
    .InsertLines .CountOfLines + 1, TxtMacro
End With
End Sub

Al lanzar nuestra macro podemos ver el resultado:

Seguir leyendo...