jueves, 23 de noviembre de 2017

Opción Usar nombre de tablas en fórmulas

Hoy toca un recordatorio necesario de recordar cuando trabajamos con tablas.
La opción de Usar nombre de tablas en fórmulas.
Esta opción nos permite utilizar los nombres de los campos o elementos dentro de una tabla cuando construimos nuestras fórmulas y/o funciones.
Dentro de las muchas opciones existentes en Excel esta quizá es una de las más importantes por la facilidad que nos proporciona a la hora de construir nuestros informes.


¿Qué hace esta opción cuando está activada?. Sencillo permite referirnos a las partes de una tabla como si de un 'nombre definido' se tratara.
Véase en la imagen siguiente:

Opción Usar nombre de tablas en fórmulas



Se observa que en lugar de usar las referencias de estilo A1 habituales, los rangos o celdas sobre laa que trabajamos, emplean los nombres correspondientes de esa tabla.

Para activar o desactivar esta opción accederemos a la ficha Archivo > Opciones de Excel > Fórmulas > sección TRabajo con fórmulas > opción Usar nombre de tablas en fórmulas

Opción Usar nombre de tablas en fórmulas



Si la opción estuviera deshabilitada veríamos nuestras fórmulas y/o funciones con referencias estilo A1 'normales':

Opción Usar nombre de tablas en fórmulas



Las referencias ya existentes permanecerían con el estilo con el que fueron creadas...

Como curiosidad final si abrimos u libro de Excel 97-2003 (.xls) en modo de compatibilidad desde nuestro Excel versión 'actual', y creamos una Tabla, al trabajar sobre ésta, veremos nuestras referencias en estilo de celda A1,
quedando desactivada la opción comentada.

martes, 21 de noviembre de 2017

VBA: .DeleteAfterSubmit Eliminar email recién enviado

En una formación reciente un alumno planteaba la duda de cómo conseguir eliminar automáticamente desde Excel el último correo electrónico enviado...
La idea era automatizar envíos masivos de emails desde Excel e ir eliminando cada correo remitido.


Para dar respuesta al alumno emplearemos la propiedad .DeleteAfterSubmit asociado al objeto MailItem.
En el blog hemos escrito algunos artículos sobre la forma de enviar emails desde Excel empleado la aplicación de Outlook (ver post).. asi que nos centraremos en esta propiedad comentada.


Nuestra macro añadida dentro de un módulo estándar de nuestro proyecto de VB:

Sub EnvioMail_y_Borrado()
Dim olApp As Object
Dim olMailItm As Object

'Creamos 'la aplicación de MS Outlook'
'que será la que voy a utilizar
Set olApp = CreateObject("Outlook.Application")

'Emplearemos el email creado en la línea anterior
'y genero un email vacío... de momento
Set olMailItm = olApp.CreateItem(0)
With olMailItm
    'indicamos quién es el destinatario... y lo incluimos en el apartado de 'Para...'
    'podíamos haber empleado las opciones .CC (con copia)
    'o también .BCC (con copia oculta)
    .to = "cursos@excelforo.com"
    '.CC = "xx@excelforo.com"
    '.BCC = "yy@excelforo.com"
    'cuál es el asunto...
    .Subject = "Excelforo: prueba para eliminar email enviado"
    ' y cual el cuerpo del correo electrónico
    .Body = "Esto es una prueba de envío de emails desde Excel" & vbCrLf & _
            "El objetivo es eliminarlo automáticamente una vez enviado..." & vbCrLf & _
            "Saludos cordiales!"
    'si quisieramos ver el email...
    '.Display
    'indicamos que se elimnaré después del envío
    .DeleteAfterSubmit = True
    'y lo enviamos...
    .Send
End With

'Dejamos limpias las variables empleadas
Set olMailItm = Nothing
Set olApp = Nothing
End Sub



Tras ejecutar la macro, el resultado es el esperado...El email enviado se ha eliminado de la carpeta de Enviados.

jueves, 16 de noviembre de 2017

Gráfico Dinámico de una Pirámide Poblacional

Hoy trabajaremos sobre una base de datos de un listado de personal separados por sexo y edad...
A partir del cuál obtendremos un gráfico dinámico que represente la distribución poblacional por rango de edades.

Nuestros datos de partida son la tabla de empleados y un rango donde indicamos cuáles son los rangos de edad:

Gráfico Dinámico de una Pirámide Poblacional



Puesto que para solucionar este asunto necesitaremos crear un elemento calculado no será posible emplear un campo agrupado (el de Edad), por tanto el primer paso consistirá en traslados con una función BUSCARV el rango de edad correspondiente según la edad particular de cada empleado.
Añadimos un campo nuevo a la tabla donde incorporamos la función:
=BUSCARV([@Edad];$F$3:$H$7;3;1)

Gráfico Dinámico de una Pirámide Poblacional


Notemos que nuestro BUSCARV emplea una búsqueda aproximada, para lo cual el rango F3:H7 debe estar ordenado en sentido ascendente en su primera columna!!.

El siguiente paso consiste en generar la tabla dinámica.
Llevaremos el campo 'Rango' al área de filas, el campo 'Sexo' al área de columnas y el campo 'Empleados' al área de valores (resumido por cuenta).

Gráfico Dinámico de una Pirámide Poblacional



A continuación crearemos un elemento calculado dentro del campo 'Sexo', donde lo formularemos para obtener el dato del conteo obtenido por el elemento M en negativo:

Gráfico Dinámico de una Pirámide Poblacional


Este elemento calculado se ha podido incorporar por no existir operaciones no permitidas (promedios, desviaciones o varianzas) ni campos agrupados...

Sobre el campo 'Sexo', ya con el nuevo elemento, aplicamos un filtro para mostrar los elementos 'M-'
y 'H'.

El siguiente paso consiste en insertar un gráfico dinámico de tipo barras 'agrupadas' al que aplicaremos los pasos descritos en este post.
Básicamente consiste en Superponer las series y Disminuir el ancho del intervalo.



Logrando el objetivo buscado...