Categorización de fechas en tablas dinámicas en Excel

Dado un conjunto de datos (por ejemplo la siguiente tabla)

Podemos crear una tabla dinámica con ellos, de manera que en las filas ponemos la fecha y en los datos mostramos el importe (ver imagen)

Para crear y analizar los datos agrupando por años, meses, etc. debemos poner el cursor sobre el campo Date (Celda E2 en la imagen) y ejecutar la opción de menú: Datos à Agrupar y Esquema à Agrupar

Y en la ventana emergente seleccionar el tipo de agrupación, por ejemplo meses y trimestres

Automáticamente en la tabla dinámica se añadirán las dimensiones que hayamos creado

Pudiendo filtrar la información por cualquiera de dichos conceptos.

Gráfico de real vs objetivo con formatos condicionales

Supongamos un gráfico en el que se muestra un valor real comparado con un objetivo para cada período con formato de columnas, es decir, se tienen configuradas dos series (una para el valor real y otra para el objetivo), cada una con un color.

Lo que se pretende es poder colorear el valor real en base al cumplimiento (en verde si se supera o igual el objetivo) y en rojo si es menor.

Para ello se crearan dos columnas adicionales, de real, y creamos un gráfico con 3 series (las dos de real y el objetivo:

Ahora, sólo falta cambiar las propiedades de las series.

Las otras dos series se trazaran en el eje secundario (vigilar la escala de los dos ejes).

El gráfico final queda de la siguiente manera:

DataCycle Planning, más allá de la gestión del proceso presupuestario

Hace tres años, ApeSoft, lanzó al mercado la solución DataCycle Planning con el propósito de que ayudara a las compañías a gestionar de forma más eficiente y con menos esfuerzo el proceso presupuestario, así como a mejorar su seguimiento facilitando la realización de más revisiones del presupuesto que ayudaran a ajustarlo mejor a la realidad de cada momento.

A lo largo de estos años, la rápida evolución de la herramienta y sobretodo la experiencia en diversos proyectos nos ha hecho ver que DataCycle Planning es mucho más que una solución para resolver el proceso de gestión presupuestaria. Y es que son numerosas las compañías que, además del presupuesto, gestionan múltiples procesos con hojas de cálculo Excel. Por ejemplo:

• Área de Ventas: Presupuestos de Ventas, Gestión de Acuerdos Comerciales, Ofertas y Propuestas con grandes clientes…
• RRHH: Headcounting, Incrementos salariales
• Operaciones: Proyectos
• Etc

En la gestión de cualquiera de estos procesos se repiten los mismos pasos y por consiguiente las mismas problemáticas de gestión:

Situaciones comunes en la Contribución y Documentación de Información

• Varios usuarios documentan información en una hoja Excel (Propuesta, Oferta o Acuerdos Comerciales con clientes/proveedores, Presupuesto Financiero o Ventas, Incrementos Salariales…)
• Posteriormente el usuario envía la plantilla cumplimentada al Responsable del Proceso

Problema: Información descentralizada, sin control y sin entorno seguro

Situaciones comunes en la Consolidación y Validación de Información

• El usuario Responsable valida la información y si no está conforme la devuelve al usuario para rectificarla.
• En este punto también se hace necesario la consolidación o agregación de toda la información enviada, este paso es realizado de forma muy manual y “artesanal” copiando y pegando datos.
• Varios niveles de validación de la información contribuida.

Problema: Pérdida de tiempo en tareas manuales, errores debido a la manipulación de datos, gestión de plantillas y sin control claro del proceso.

Situaciones comunes en el seguimiento y análisis

• Dificultad en obtener informes de seguimiento que permitan comparar el real con el previsto
• Este punto suele ser conflictivo en la mayoría de casos ya que se hace muy difícil poder tener una visión de la realidad y por lo tanto la reacción ante desviaciones es mucho más lenta, con todas las consecuencias que esto puede conllevar

Problema: Falta de información que permita tener una idea clara de la realidad. Puede que existan los informes de seguimiento pero se pierde mucho tiempo en obtenerlos.

Gracias a DataCycle Planning todo este proceso se simplifica y agiliza de forma “extraordinaria”, ya que se eliminan por completo los problemas y consecuencias descritos anteriormente. Estas son las mejoras y beneficios que comporta, en cada una de las fases, el hecho de contar con una solución como DataCycle Planning:

Beneficios en la contribución y documentación de información

• Información bajo control en un sistema centralizado, 100% seguro.

Beneficios en la consolidación y validación

• El sistema consolida y agrega los datos de forma automatizada. Además, el control del proceso de validación es absoluto.

Beneficios en el seguimiento y análisis

• Generación automatizada de informes de seguimiento que comparan el real con el objetivo.
• También, envío automatizado de alertas que avisan ante desviaciones críticas.

En definitiva, que todo ello convierte a DataCycle Planning en una potente plataforma capaz de resolver múltiples procesos de gestión en Excel.

Si desea saber más acerca de la solución contacte con nosotros a través del teléfono 902 789 981 (llamadas nacionales), +34 93 582 02 58 (llamadas internacionales) o bien envíe un email a: marketing@apesoft.com

Información para tiempos de crisis. Informes diarios, semáforos y alertas

Actualmente, todos los directivos están pensando cómo paliar los efectos de la crisis en sus organizaciones. Dentro de los distintos elementos que pueden considerarse para tal cometido, se encuentra uno vital: la información. Hablaremos en este artículo de la información para tomar decisiones, que se genera a partir de los datos que tiene cada organización en sus sistemas; y particularmente de uno de los informes básicos para gestionar en estos momentos: el informe diario.

En tiempos de crisis, la información debe correr rápidamente y desplegarse de arriba a abajo y viceversa hasta llegar a todo el mundo, con una distribución más numerosa y una mayor frecuencia.

INFORMES DIARIOS

Los informes diarios y entregados a gran parte de la empresa, constituyen una forma muy efectiva de atacar a la crisis. Son informes que se generan y distribuyen cada día, a primera hora de la mañana y llegan a casi todas las personas de la organización. Las características de los informes diarios son:

1. Informan. Muestran información relevante que cambia con el día a día, informando sobre el curso diario y relacionando de forma directa nuestra actividad diaria con los resultados de la compañía.

2. Conciencian. Informan sobre la realidad de la empresa, haciendo que todos sean conscientes sobre lo que está ocurriendo y suelen unir visualmente datos de distintas áreas de la empresa: ventas, entregas, producción, nuevos clientes, cobros,... Debe ser un único informe diario, independientemente del cargo y área, que muestra el mismo “pulso de la empresa” para todos. Opcionalmente pueden existir otros informes diarios por área, más específicos.

3. Implican. Al ser diarios, comunican un seguimiento muy cercano de la actividad, haciendo que todos se sientan involucrados y cada área esté más implicada en actuar en el día a día, para asegurar los resultados mensuales y anuales.

4. Aumentan la rapidez de tomar decisiones: Al tener una alta frecuencia, permiten analizar desviaciones y definir tácticas para corregirlas o mitigarlas con más rapidez.

¿Qué información deben contener?

Información de diversas áreas, que tengan datos que cambien en el día a día. Deberían contener objetivos para cada dato, con el fin de comunicar si vamos bien o mal y cómo de desviados.

¿Qué aspecto deben tener?

Tipo visual como un Dashboard (informe con semáforos, gráficos aceleradores y otros elementos visuales), con capacidad para detallar algún indicador para poder tomar decisiones. Debería ser un formato familiar para todos sin que se necesite formación especial para explotarlo, por ejemplo un Excel muy gráfico.

¿Cómo deberían idealmente de llegar al usuario?

Por correo electrónico cada día a las 7:00 am de la mañana.

ALERTAS

Imaginen recibir un email cuando ocurre algo importante en la compañía que me interesa a mi especialmente, y justo en el momento oportuno en el que sucede. Este concepto se denomina alerta y normalmente tienen forma de email o sms con un asunto que indica qué ocurre, un texto con el procedimiento de actuación y algún resumen de la alerta, y opcionalmente un informe que detalla con más datos lo ocurrido.

Por ejemplo:

1. Las ventas de la zona X se han desviado más de un Y% respecto al presupuesto o año anterior.

2. Tu cliente X hace más de Y meses que no compra un producto A

3. El cliente X debería haber pagado la factura M que tenía vencimiento DD/MM/YYYY

4. La productividad ha bajado por debajo de X unidades/día

5. Hay pedidos que no podrán servirse a tu cliente X por retrasos en producción

6. Uso de endeudamiento X% superior al marcado Y%

Las alertas aportan las siguientes ventajas:

1. Permiten reaccionar mucho más rápido ante situaciones de amenaza o de oportunidad.

2. Permiten tener controlados toda una serie de puntos críticos que no queremos que se desvíen, y en caso de hacerlo queremos ser informados lo antes posible.

3. Ahorran tiempo de análisis al no tener que mirar si estas situaciones van ocurriendo. Dan una sensación real de mayor control.

4. Avisan de forma reactiva, estés donde estés, sin necesidad de pedir la información o monitorizar un informe.

¿Qué información y aspecto deben tener?

Las alertas deben tener forma de email con un asunto personalizado según el receptor apropiado, describiendo la alerta y algún dato que describa la importancia. Por ejemplo: “El cliente ARAGONESA DE CERAMICAS hace más de 3 meses que no consume un producto tipo A”

El cuerpo del email debería contener algún detalle más y podría llevar anexado un informe (en Excel por ejemplo) con todo el detalle. En el ejemplo anterior podría incorporar el histórico de ventas del cliente ARAGONESA DE CERAMICAS.

¿Cómo deberían idealmente llegar al usuario?

Por correo electrónico o SMS cuando haya ocurrido la alerta

Desde Apesoft, estamos especialmente preocupados por dar soluciones de valor que ayuden en este tiempo de crisis. Por ello hemos preparado unas condiciones especiales tanto de descuentos como de uso por alquiler de nuestras soluciones software, para facilitar al máximo la incorporación de técnicas como los informes diarios y las alertas, que permitan lidiar con la crisis y salir victoriosos de ella.

Convertir un fichero .csv a Excel a través de Datacycle Reporting

La conversión de un fichero .csv a .xls se puede realizar a través de varias opciones, pero a continuación vamos a detallar los pasos de una de las posibilidades con Datacycle Reporting.

Desde Excel se pueden importar datos de archivos de texto u otros formatos y entre ellos los .csv.

Para ello, una opción de manera manual sería, dentro de Excel: Acceder al menú Datos - Obtener datos externos - importar datos. Seleccionar el fichero e informar datos tales como separador,..

Los pasos son:

1. Abrir un libro nuevo de Excel y grabar una macro con el detalle de las acciones al realizar la importación de datos comentada anteriormente. Detener la grabación al finalizar.

2. Definir un proceso nuevo en Dataycle Reporting de tipo Generador de informes y definir una plantilla nueva  basada en una de las plantillas modelo de Datacycle Reporting.  Este proceso, una vez ejecutado,  generará un Excel con el contenido del fichero origen .csv.

3. Copiar la macro generada en el paso 1 y pegarla en la plantilla utilizada en el proceso creado anteriormente.

4. Crear una tarea nueva, dentro del proceso, de tipo Ejecutar macros y escribir el nombre de la macro que se ha generado en el paso 1.

5. Ejecutar el informe.

El resultado final es un libro Excel con datos actualizados desde el fichero .csv.

Enviar desde Datacycle Reporting un dato por correo electrónico que está en una celda específica de Excel

El objetivo de este ejemplo es mostrar una de las muchas posibilidades que ofrece la configuración de procesos dentro de Datacycle Reporting. En este caso se desea enviar un correo por cada contenido especificado en las celdas de una columna de una hoja Excel.

Los pasos son los siguientes:

1. Definir en un Excel, una hoja con el nombre Envio y en la primera columna y la primera celda, un título: ContenidoMail y debajo, por ejemplo, 3 celdas con 3 contenidos diferentes.
2. En Datacycle Reporting, definir una base de datos nueva contra este Excel, donde tipo de sistema: General, Tipo de conexión: Excel y seleccionar el fichero anterior.
3. Importar entidades, en este caso, sólo importar la hoja Envio.
4. Definir una consulta, donde la fuente de datos es el Excel definido y seleccionar el campo ContenidoMail de la hoja Envio. Ejecutar y comprobar que devuelve 3 registros.
5. Definir un proceso compuesto (porque no necesito generar ningún Excel, sólo enviar correo) donde el tipo de proceso sea: Informe múltiple personalizado, y la consulta de evaluación sea la definida anteriormente. Esto hará que envíe tantos correos como contenidos tenga en mi hoja Excel.
6. Definir una tarea de Tipo Enviar Correo con un asunto y en el cuerpo del mensaje utilizar un parámetro del campo de evaluación, @PAR(ContenidoMail), que es el que devuelve la consulta. Informado el resto de datos: destinatarios, perfil de correo,..

Finalmente ejecutar el proceso. El resultado son 3 correos con 3 contenidos distintos.
(El nombre de la pestaña Excel, columna Excel,... son simplemente de ejemplo).

Tarea 'Save As' en Procesos DataCycle

Esta tarea realiza una segunda copia del resultado de un proceso y reproduce el comportamiento del ‘Guardar Como’ de otras aplicaciones o el Copiar Y Pegar de Windows. De esta manera evitamos explorar por carpetas para generar una segunda copia de los archivo Excel generados. Aunque la tarea de 'Comprimir fichero en zip' ya realiza una copia de seguridad, con esta Tarea Código Script, se consigue mover los ficheros a una ubicación de red o un dispositivo externo sabiendo que no se necesita un descompresor para su uso posterior. El código es como sigue:

Sub Main()

Dim fso,fldr,carpeta

carpeta = "C:\Temp\"

Set fldr = CreateObject("Scripting.FileSystemObject")

If fldr.folderexists(carpeta) then

Set fso = CreateObject("Scripting.FileSystemObject")

If fso.FileExists("@PAR(TARGET_FILE_PATH)\@PAR(TARGET_FILE_NAME)") Then

fso.CopyFile "@PAR(TARGET_FILE_PATH)\@PAR(TARGET_FILE_NAME)",carpeta

End If

End If

End Sub

En este ejemplo se determina un destino fijo, pero bien se puede definir un parámetro global o de proceso como valor de la variable carpeta.

Incluso podemos diseñar una variante donde el fichero nuevo tenga un nombre diferente, el cambio seria:

............................................

carpeta = "F:\Ventas\InfVentas_Copia_” & @PAR()CURRENT_MONTH_NAME[ESP]) & ” .xls"

Set fldr = CreateObject("Scripting.FileSystemObject")

If fldr. FileExists (carpeta) then

............................................

Cambiar el número de hojas por defecto al abrir

Cuando se abre un nuevo libro, Excel muestra 3 hojas por defecto. En la mayoría de casos sólo necesitamos una o dos, y, el resto las eliminamos. Para evitar esto, podemos indicar a Excel que sólo cree una hoja por defecto, y añadir más si las necesitamos. En Menu -> Herramientas --> Opciones -->General cambiar la opción “Número de hojas en nuevo libro” de 3 a 1.

Visualizar números en formato de miles y millones

Cuando se trabaja en Excel con números de gran magnitud, puede ser interesante formatear su visualización y ocultar los dígitos menos significativos (por ejemplo, mostrar los números en miles o en millones como unidad). A continuación se muestra un uso del formato personalizado de Excel que nos permitirá obtener los resultados que buscamos:

Primero formateamos la celda o serie de números que nos interese, aplicando el siguiente formato personalizado

Formato: [>=1000000]0.." M";[>=1000]0." K";0

Lo que hará que visualicemos el dato de la siguiente forma:

El ejemplo anterior se puede simplificar. Si por ejemplo sólo queremos un formato en miles y no el de millones, el formato personalizado sería: [>=1000]0." K";0

Incluso se puede combinar de forma que sigamos manteniendo el separador de miles y este formato (el separador de miles aplicaría a partir de los miles de millones). Para ello usaremos el siguiente formato personalizado: [>=1000000]#.##0.." M";[>=1000]0." K";0

Introducir la fecha y hora actual en una celda de Excel

Existen dos caminos para introducir de forma rápida la fecha y hora actual en una celda de Excel.

Fecha actual:CTRL+SHIFT+; en la celda dónde desee incluir la fecha.

Hora actual: CTRL+SHIFT+: en la fecha dónde desee incluir la hora.

En el caso que desee incluir fecha y hora en la misma celda sólo debe introducir la primera fórmula, teclear espacio e introducir la segunda.