jueves, 14 de diciembre de 2017

VBA: Subtotales y Repetir etiquetas en tabla dinámicas

Un lector pedía ayuda para mostrar ciertas opciones en su tabla dinámica:
1-Sin subtotales
2-En estilo de diseño Tabular
3-Repitiendo etiquetas de elementos
:
[...]En esta ocasión te deseo consultar que codigo utilizar para poner mi tabla dinamica de forma tabular, quitar los subtotales y repetir las etiquetas.[...]


Partimos de una tabla como origen de datos con cuatro campos: Fechas, Conceptos, Categorías e Importe,
a partir de la cual hemos construido una tabla dinámica con dos campos en el área de filas:
Conceptos, Categorías
y el campo de Importe en el área de valores resumido por suma.

VBA: Subtotales y Repetir etiquetas en tabla dinámicas



El aspecto de la tabla dinámica es que aparecen los subtotales por defecto, no se repiten las etiquetas de elementos y tiene un diseño Compacto.

Con la macro siguiente que incluimos en un módulo estándar de nuestro proyecto conseguiremos los tres puntos que requiere el lector:

Sub OpcionesTablaDinamica()
Dim PT As PivotTable
Dim PF As PivotField

On Error Resume Next
'Definimos el objeto Tabla dinámica sobre el que trabajar
Set PT = Application.ActiveSheet.PivotTables(1)
    'cambiamos a diseño Tabular
    PT.RowAxisLayout xlTabularRow
    'exigimos se repitan las etiquetas
    PT.RepeatAllLabels xlRepeatLabels
    'quitamos los subotales de todos los campos...
    For Each PF In PT.PivotFields
      PF.Subtotals(1) = True
      PF.Subtotals(1) = False
    Next PF
End Sub



El resultado tras ejecutar la macro es el esperado:

VBA: Subtotales y Repetir etiquetas en tabla dinámicas



Otra posibilidad para eliminar los subotales sobre un campo en concreto:

Sub OpcionesTablaDinamica()
Dim PT As PivotTable
Dim PF As PivotField

On Error Resume Next
Set PT = Application.ActiveSheet.PivotTables(1)
    PT.RowAxisLayout xlTabularRow
    PT.RepeatAllLabels xlRepeatLabels
    'quitamos el Subtotal solo del campo 'concepto'
    With PT.PivotFields("concepto")
        .Subtotals(1) = True
        .Subtotals(1) = False
    End With
End Sub

martes, 12 de diciembre de 2017

Localizar última visita condicionada

Leyendo el título del post puede resultar una pregunta algo 'rara', pero tiene bastante sentido...
Se trata, a partir de un listado de los registros de visitas a nuestros clientes, en qué casos (para qué clientes) han pasado más de seis días desde la última visita.
Supongamos el siguiente listado de registros de visitas, sabiendo que la fecha de hoy es 12/12/2017:

Localizar última visita condicionada



Se observa que tenemos marcadas con color aquellos clientes donde su última visita, respecto al día de hoy, fue superior a los seis días (plazo relevante para nuestro estudio).
Comprobamos como para los clientes E, B y A con fechas de última visita 06/12/2017, 08/12/2017 y 11/12/2017 respectivamente, no exceden de esos seis días estipulados...


El trabajo para conseguir esto es doble, por un lado, componer y crear unas fórmulas matriciales que incorporaremos a unos nombres definidos:
check =(HOY()-fecha)>6
fecha =MAX(SI(Hoja3!$B$3:$B$29=Hoja3!$B3;Hoja3!$C$3:$C$29;0))

Nota: importante que la celda activa esté en la fila 3!!

y por otro lado, seleccionar el rango completo de datos (B3:C29) donde aplicaremos una regla de formato condicional con la siguiente fórmula:
=Y(check;$C3=fecha)
apoyándonos en los nombres definidos anteriores...



Con lo que queda solucionada nuestra búsqueda.

la clave de este problema es el uso matricial que se hace de fórmulas dentro de los nombres definidos.
En concreto, la fórmula
=MAX(SI(Hoja3!$B$3:$B$29=Hoja3!$B3;Hoja3!$C$3:$C$29;0))
ejecutada matricialmente devolverá para cada fila/cliente la última fecha (la fecha mayor)...esta fecha la emplearemos con el segundo nombre definido para saber si respecto al día actual han transcurrido más de seis días:
=(HOY()-fecha)>6
En conjunto, finalmente dentro del formato condicional se evalúa si es cierto que han pasado más de seis días y si estamos en el caso de ser última fecha de ese cliente...

jueves, 7 de diciembre de 2017

VBA: Enviar Rango a Destinatario de correo

Una opción ya en desuso de Excel es el envío a destinatario de correo, que permite adjuntar un rango de celdas seleccionado en el cuerpo de un email, gestionado todo desde nuestra aplicación Excel favorita.

Para las últimas versiones deberemos sacar a la luz el enterrado botón que habilita esta posibilidad;
así pues desde el desplegable de la barra de herramientas de acceso rápido > Más comandos... > Todos los comandos > Enviar a destinatario de correo

VBA: Enviar Rango a Destinatario de correo



Una vez sacado el botón, podemos ejecutarlo, lo que nos llevará a la siguiente ventana... desde donde controlaremos el destinatario/s, el asunto o una breve introducción (en sustitución del típico cuerpo de un email).

VBA: Enviar Rango a Destinatario de correo



Accediendo a Outlook podremos comprobar el envío de dicho email:

VBA: Enviar Rango a Destinatario de correo



Este mismo proceso lo podemos replicar desde nuestra programación y así poder automatizarlo...

Así pues añadimos la siguiente macro dentro de un módulo estándar de nuestro proyecto de VB:

Sub Enviar_Rango_a_Destinatario_de_correo()
   
'Seleccionamos el rango de celdas a enviar Select
ActiveSheet.Range("A1:B5").Select

' Show the envelope on the ActiveWorkbook.
ActiveWorkbook.EnvelopeVisible = True

'Llamamos al envío...
With ActiveSheet.MailEnvelope
   .Item.To = "excelforo@excelforo.com"
   '.Item.cc = "cursos@excelforo.com"            'con copia a...
   '.Item.bcc = "consultoria@excelforo.com"      'con copia oculta a...
   .Item.Subject = "Asunto: Envío rango de Excel por email"
   .Introduction = "Ejemplo de rango adjunto con formato..."
   .Item.Send
End With
End Sub



Al ejecutarlo verificaremos que el resultado es equivalente...