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

martes, 14 de noviembre de 2017

Formato Condicional Ajustado a columnas

Veremos hoy cómo aplicar un sencillo formato condicional para mostrar celdas formateadas de acuerdo al valor de una tercera celda.
Este post nace por la necesidad expresada por una lectora:
[...]existe la posibilidad de pintar celdas en Excel con un condicional que diga coloree las siguientes x numero de celdas, por ejemplo:
Numero de piezas: 10
Entonces necesito que cambie el color de diez celdas para que sobre ellas se puedan anotar los nombres de cada pieza.[...]

Formato Condicional Ajustado a columnas



Veamos la resolución del caso concreto para valores de 1 hasta 10.
1- Seleccionamos el rango B2:K11. Me aseguro que mi celda activa es B2.
2- Accedo al formato condicional > Nueva Regla > Utilice una fórmula que determine las celdas para aplicar formato.
3- Introducimos la siguiente fórmula:
=COLUMNA()<=$A2+1

Formato Condicional Ajustado a columnas



Listo. Con esa simple fórmula solucionamos el caso propuesto por nuestra amiga.
La fórmula aplicada únicamente compara la columna de cada celda con el valor de la celda (columna A)-ajustada con un +1 para concretar correctamente columna vs valor.


Este formato condicional expuesto funciona correctamente, sin duda... pero tiene una deficiencia difícil de combatir. Y es que manualmente tendríamos que ajustar/modificar nuestra regla de formato condicional, en cuanto a la propiedad 'Se aplica a' para el caso en que el intervalo de los datos (columna A) no estuviera claramente delimitado entre 0 y 10.
Dicho de otro modo, cómo ajustar el rango donde aplicar nuestra regla independientemente de los datos a transformar (sean 10 o 200).

Tenemos, pues, que conseguir que se autoajuste el rango donde aplicar nuestra regla.
Por desgracia el campo donde incluimos la propiedad 'Se aplica a' no admite fórmulas, ni nombres definidos... solo y exclusivamente rangos (como objeto), lo que hace imposible con el estándar obtener el dinamismo perseguido.
Por tanto lograremos nuestra meta con una macro.


Nuestra macro siguiente recorrerá las posibles reglas de formato condicional existentes en nuestra hoja de trabajo, buscando aquella cuya fórmula aplicada coincida con la descrita anteriormente (=COLUMNA()<=$A2+1), en cuyo caso aplicando el método .ModifyAppliesToRange sobre dicho formato condicional.
Este método modificará el rango donde aplicar la regla, tal como queríamos; quedando ajustada al rango exacto según el valor máximo.

Para facilitar el trabajo calculamos en la celda A1 el dato máximo ( =MAX(A2:A11) ), que emplearemos en nuestra macro.


Añadimos el siguiente código en un módulo estándar de nuestro proyecto de VB... aunque también podríamos asociar la macro a un evento de hoja tipo _Change:

Sub CambioFC()
Dim FC As FormatCondition
Dim sh As Worksheet

Set sh = Hoja2
For Each FC In sh.Cells.FormatConditions
    '.AppliesTo.Address, .Type, .Formula1, .Interior.Color, .Font.Name
    'buscamos la regla de formato condicional con nuestra fórmula
    If FC.Formula1 = "=COLUMNA()<=$A2+1" Then
        'y modificamos el rango donde aplicar la regla de formato condicional
        'ajustando la celda final del rango con el valor máximo de los datos
        FC.ModifyAppliesToRange Range(Cells(2, "B"), Cells(11, Range("A1").Value + 1))
    End If
Next FC
End Sub

Al ejecutar la macro conseguimos nuestro objetico de modificar y ajustar el rango donde aplicar nuestra regla de formato condicional.

jueves, 9 de noviembre de 2017

Formas de extraer Fecha y Hora de una fecha completa

Daremos respuesta a una consulta planteada por una lectora, donde planteaba la manera de recuperar a partir de una fecha completa (fecha + hora) de manera separada la fecha (día/mes/año) y por otra parte la hora (hora:minuto:segundo).

Partiremos de una fecha completa y veremos cómo empleando algunas funciones específicas de tiempo y otras matemáticas podemos obtener lo deseado.

extraer Fecha y Hora de una fecha completa



Para recuperar la fecha (día/mes/año) podemos actuar de diferentes modos:
1-Empleando la función FECHA que permite construir una fecha a partir de tres elementos... año, mes y día que obtenemos con las funciones AÑO, MES y DIA:
=FECHA(AÑO(B2);MES(B2);DIA(B2))
aplicamos un formato de Fecha Corta y listo.

2-Sabiendo que las fechas son ordinales (números enteros) que comienza el 1/1/1900, basta aplicar la función ENTERO sobre el número para descartar la parte decimal (la de las horas..).
=ENTERO(B2)

3-y una tercera forma con la función FECHANUMERO aplicada sobre la función TEXTO que convierte en texto con formato ("dd/mm/aaaa") la fecha completa:
=FECHANUMERO(TEXTO(B2;"dd/mm/aaaa"))


De forma similar trabajamos para recuperar la hora (hora:minuto:segundo).
1-Usando la función NSHORA y las funciones HORA, MINUTO Y SEGUNDO para informar de los tres argumentos que componen una hora cualquiera:
=NSHORA(HORA(B6);MINUTO(B6);SEGUNDO(B6))
aplicamos un formato de hora (por ejemplo hh:mm:ss).

2-con la función HORANUMERO sobre la función TEXTO para tratar la fecha con formato "hh:mm:ss".. de manera similar a la fecha:
=HORANUMERO(TEXTO(B6;"hh:mm:ss"))

3-recuperando la parte decimal, i.e., la de las horas quitando al número completo su parte entera:
=B6-ENTERO(B6)

4-y por último un método curioso. Un efecto matemático, al dividir un número decimal entre uno, el resto será siempre la parte decimal... por tanto:
=RESIDUO(B6;1)
nos retorna únicamente la parte decimal del 'número' que corresponde a la hora (hora:minuto:segundo).. tal como necesitamos.


Y estas son las formas más habituales de conseguir Fecha y Hora por separado...