Funciones de excel para la gestión de datos (I)

Vamos a introducir una serie de post destinados a mejorar nuestras habilidades como gestores de bases de datos.

Es frecuente que nos encontremos en nuestro dia a dia bases de datos que tengamos que consultar frecuentemente, lo cuál en ocasiones es un engorro. Incluso para mostrar informes el exceso de datos es negativo para el usuario final.Las tablas dinámicas, filtros, inmovilización de paneles suponen cierto alivio, pero no están al alcance de todos. Vamos a comentar una serie de funciones que nos van a ser útiles:

Sumar.si.conjunto

Empecemos con esta base de datos, sacada de la web del Banco de España, que da información sobre el euribor y tipos de interés.Al final del post incluiremos el excel con los datos.

blogssc1

Como vemos es un listado muy extenso y necesitamos introducir un pequeña pantalla de selección de datos para que sea más sencilla la obtención de datos.

 

blogssc2

Vamos ahora a crear en el mismo excel estos tres campos, que es lo que deseamos obtener: seleccionar el indice a consultar, seleccionar el periodo, y que nos ofrezca el tipo de interés porcentual.

 

blogssc5

Nos situamos en la casilla debajo de Indice. Seleccionamos en Excel el menú Datos / Herramientas de Datos / Validación de datos.

blogssc6

Elegimos como Criterio de validación “Lista”, y seleccionamos el Origen, que en este caso es el rango de columnas que incluye los títulos de cada índice. Podemos copiar y pegar (en este caso trasponer) para aislarlos de la tabla de datos, como hemos hecho debajo, o directamente sobre los títulos. Hacer click para agrandar.

blogssc7

Damos a aceptar y tenemos ya en el Indice un desplegable con los títulos de las columnas de la base de datos.

blogssc8

Vamos ahora a hacer lo mismo con el periodo, en este caso el rango incluirá las filas tipo fecha desde ene-05 a la fecha final del informe, ago-13:

blogssc9

Nos aparece otro desplegable para seleccionar el periodo:

blogssc10

Ahora vamos a poner a prueba lo hecho hasta ahora seleccionando dos valores al azar en cada campo. Vemos que  el periodo fecha nos aparece en formato número (38412):

blogssc11

Ahora  cambiamos su formato a fecha tipo mm-aa.

blogssc12

Nos queda ahora introducir la fórmula para que introducidos diferentes datos en los campos Indice y Periodo nos muestre el resultado.

Seleccionamos la tabla entera:

blogssc13

Ahora vamos al menú Fórmulas / Nombres definidos / Crear desde la selección

blogssc14

Marcamos que de nombre a los rangos de la fila superior, donde se encuentran los títulos de cada índice:

blogssc15

Ya se han creado los diferentes nombres de rango. Lo comprobamos tal como se muestra en la imagen de debajo. Esto es importante para en las fórmulas posteriores sea más sencilla la introducción de datos.

blogssc16

Nos situamos ahora en la casilla correspondiente, donde queremos que se nos muestre el resultado,  e introducimos la fórmula SUMAR.SI.CONJUNTO. Esta fórmula, aunque se utiliza para sumar datos dados diferentes rangos y criterios, también se puede usar para la visualización de datos como en la tabla, ya que una vez el operario introduzca el periodo no hace falta que se realice ninguna suma.

blogssc17

Sólo se desea que al seleccionar un periodo concreto (jun-05 por ejemplo), y un índice (Interés legal por ejemplo), nos ofrezca el dato correspondiente. Por tanto introducimos =SUMAR.SI.CONJUNTO(Interes_legal;PERIODO;J14)

Tengan en cuenta que hemos introducido, en vez de rangos, los nombres de rangos que establecimos en el paso anterior. Creamos nombres de rango con el título de cada fila, con la salvedad que si el título incluye un espacio (como en Interes legal), Excel le asigna como nombre Interes_legal.

Y la fórmula funciona de esta forma: (rango_suma; rango_criterios1; criterio1;…)

blogssc18

Esta fórmula ha buscado dentro del  rango Interés legal el rango de criterio Periodo, y  el criterio J14, que en este caso es jun-05.

Vemos como nos da el resultado correcto, 4.000 % de interés. Si cambiamos el periodo se nos modifica el resultado.

blogssc19

No obstante, si modificamos el índice en su desplegable, seleccionando por ejemplo Interés de demora, no nos actualiza el resultado. Es por ello que debemos introducir una nueva función.

Buscarv

 

Vamos primero a modificar los títulos de las filas de cada índice para hacerlos más sencillos:

blogssc20

Ahora creamos otro cuadro similar, esta vez con fondo azul; nos basta con copiar y pegar desde el que hemos estado trabajando,  y tal como hicimos en el paso anterior nos situamos bajo el campo Indice, introducimos en Excel el menú Datos / Herramientas de Datos / Validación de datos, lista, y seleccionamos los nuevos nombres, más sencillos.

blogssc21

 Y ahora vamos a introducir esta función, BUSCARV: (click para agrandar)

blogssc22

Básicamente esta función nos ofrece, dentro de un rango específico (la totalidad de la base de datos, $A$2:$G$105, siempre hay que dejar fijo este rango con el simbolo $), el valor a buscar, que en este caso se encuentra en la casilla J24 (la fecha seleccionada), siempre y cuando la casilla I24 contenga el  nombre del Indice que queremos buscar (para ello hemos incluido una función SI anidada, SI(I24=”Euribor 12m”), y nos ofrezca el valor que se encuentre dos filas a la derecha del valor a buscar, la fecha, contando como la primera de estas filas la propia de la fecha.

Ya tenemos construido el cuadro, para cualquier fecha que introduzcamos, y seleccionamos el índice que seleccionemos, nos dará el resultado correcto.

blogssc23

 

Descargue el archivo siguiente para descargar el contenido (haga click en el enlace):

HerranzJF gestiondatosI

 

 

Macros personalizadas II

En este blog vamos a añadir unas funcionalidades más a las macros creadas en nuestro blog anterior, Macros personalizadas (I).

Recordemos que hemos creado una macro que nos permite, a partir de un balance de sumas y saldos importado desde cualquier software de contabilidad, darle un formato característico. Hemos creado una macro llamada FormatoBalances, hemos personalizado la cinta de opciones para que aparezca una pestaña más con todas nuestras macros, y tenemos el balance ya formateado a nuestro gusto, tal y como queremos.

visión preliminar balance formateado en post anterior (click para agrandar)

visión preliminar balance formateado en post anterior (click para agrandar)

Vamos en primer lugar a introducir dentro del código de la macro un mensaje para que aparezca al ejecutar la macro.

Nos vamos a la cinta de opciones Programador, pulsamos el botón de macros tal y como se detalla en la imagen siguiente:

blog3ppvt008

Ya dentro de Macro, seleccionamos la macro creada FormatoBalances y pulsamos Modificar.

blog3ppvt010

Acto seguido se nos abre el Visual Basic para Aplicaciones donde podremos apreciar el código de la macro seleccionada.

blog3ppvt011

Vamos a insertar al final del procedimiento un simple código:

MsgBox “El formato del Balance ha sido modificado satisfactoriamente”

End Sub

blog3ppvt012

Damos a grabar

blog3ppvt015

Y salimos del VBA pulsando a Archivo/Cerrar y volver a Microsoft Excel.

Hemos regresado a la pantalla de comienzo de este blog, Programador, donde hemos grabado un texto que aparecerá a la hora de ejecutar la macro.

Vamos a proceder ahora a asignarle un icono a esa macro.

Dentro de Programador, pulsamos el icono Insertar (aparecen unas herramientas), y dentro del menú contextual seleccionamos Control de formulario y el icono Botón, tal como se muestra en la imagen:

blog3ppvt018

Acto seguido nos aparece un cursor en el que podremos dibujar una forma para nuestro botón y asignarle la macro correspondiente FormatoBalances para que al hacer click sobre ella se nos ejecute.

blog3ppvt019

Basta pinchar sobre el botón para añadirle el nombre que deseamos.

Tenemos otra opción aún más sencilla y visualmente más atractiva, es insertar una determinada imagen y asignarle una macro.

Insertamos una imagen previamente creada dentro del menú Insertar, y la añadimos a nuestro Excel. Es una imagen que hemos retocado previamente para darle un formato de icono y que represente el efecto que se va a conseguir, que en este caso será el de formatear los bordes.

blog3ppvt023

Podemos modificar el tamaño de forma manual con el ratón, y posteriormente hacemos click en esa imagen con el botón derecho del ratón y seleccionamos asignar macro, seleccionando posteriormente la macro que deseamos asignar a la imagen.

blog3ppvt024Ya por último les insertamos el enlace a un excel (hacer click en macroejemplo debajo) donde podrán encontrar la macro comentada en estos dos blog  y realizar las pruebas oportunas sobre la misma. Se trata de un Excel con macros por lo que deben aceptar y confiar en su contenido (no contiene virus). Pueden utilizar este excel como plantilla para exportar a ella los balances de otras aplicaciones y darles el formato deseado, pero les animamos a que  la modifiquen a su gusto.

macroejemplo

 

Macros personalizadas I

Vamos a comentar en este blog una forma rápida y sencilla de personalizar balances a través de la creación de una macro, asignándole posteriormente un botón.

Es usual que en el trabajo de controllers o financieros se obtengan de los ERP´s o programas de contabilidad existentes Cuentas de Pérdidas y Ganancias o Balances de Sumas y Saldos en un formato determinado, que posteriormente exportamos a excel, y que luego personalizamos. Cuando tenemos resultados en grupos de empresas o por centros de negocio, o tenemos muchos clientes a los que enviar esta información (gestorías, consultores),  esta labor se eterniza, por lo que resulta muy práctico introducir macros.

Empecemos con un modelo estándar de un Balance de sumas y saldos, exportado a Excel desde un software de contabilidad (hacer click sobre la imagen para agrandar)

Balance importado de aplicación contable

Vemos que para nuestros reportes hay que hacer ajustes en ancho de columnas, asignar bordes al conjunto del balance, relleno en los encabezados de columna, ajustar el formato de número para que aparezcan decimales y el sombreado de totales. Y tenemos 8 empresas diferentes pendientes por enviar, además de diferentes centros de beneficios dentro de cada una de esas empresas.

Empecemos:

1. Dentro de excel vamos a la barra de herramientas de acceso rápido Programador.

Nos situamos en Programador

2. Pulsamos el icono de Grabar Macro, donde nos aparecerá el siguiente menú contextual:

blog2macros004

Denominamos a esta macro FormatoBalances, y lo describimos brevemente, guardándolo en este libro de excel (existe la posibilidad de guardarlo en nuestro libro de macros personal). Ahora damos a Aceptar y empezamos a grabar la macro.

3. Se mejora la rapidez del proceso si se graba la macro desde la celda origen del rango, para así facilitar la tarea de grabación de datos. Importante!, todas las acciones que realicemos se están grabando (ver como en la barra de herramientas se nos activa un rectángulo tipo “stop” para parar la macro), por lo que conviene tener una idea clara de qué pasos vamos a dar, que atajos de teclado utilizar para que se defina bien nuestro rango, y evitar así borrar la macro ya realizada.

blog2macros005

4.Desde la celda A1 (Campo Cuenta en nuestro ejemplo) pulsamos en este orden Control (CTR)+Mayúsculas+Cursor derecho (→)

Ya tenemos  seleccionados los encabezados de fila, procedemos a sombrearlos en gris con el color de relleno.

b

5. Sin mover el cursor y sin desmarcar el rango anterior pulsamos CTR+May+Cursor inferior (↓)

para tener todo el rango seleccionado, justo antes de la fila de totales del balance, y procedemos a asignar bordes en Inicio, Párrafo, Bordes

blog2macros007

A modo de sugerencia seleccionamos primero todos los bordes y luego bordes externos.

blog2macros008

6. Damos formato ya a las celdas que contienen número, añadiendo separadores de miles y dos decimales, y ampliamos el ancho de la columna Título para ver sin problemas la denominación de las cuentas, quedándonos el Balance como sigue.

blog2macros009

Nota: es importante seguir los métodos de teclado abreviado que he incluido anteriormente para que así la macro sea adaptable a posteriores balances que tengan más o menos longitud, si lo hacemos seleccionando las celdas con el ratón dará problemas.

7. Pulsamos el  STOP situando en el borde inferior izquierdo de la pantalla para parar la macro.

pulsamos STOP (el cuadrado) para parar la macro

pulsamos STOP (el cuadrado) para parar la macro

8. Volvemos a la barra de herramientas Programador, donde tendremos la macro FormatoBalances añadida dentro del icono de Macros

blog2macros010

blog2macros011

9. Ya podemos utilizar la macro todas las veces que queramos, simplemente colocándonos en la celda de inicio del rango y pulsando el icono de Macros y Ejecutar.

10. Vamos a personalizar  ahora la cinta de opciones de excel para añadir una ficha principal en la cinta de opciones, a través del menú Inicio / Opciones /personalizar cinta de opciones. Crearemos dentro de las fichas principales una nueva ficha (tal como se muestra en la imagen) llamada Macros, (la situaré justo después de la ficha Programador), y le añadiré el comando disponible(estamos en el desplegable izquierdo, Macros) Formato Balances.

blog2macros017

Ya tenemos añadido una cinta de opciones nueva, a la que acceder rápidamente, y dónde añadiremos todas las macros que vayamos creando. Podemos también agregar todas las macros a un libro de macros personal que podremos utilizar en cualquier libro de excel.

blog2macros018

En el próximo blog explicaremos como asociar un icono o una imagen a una macro para que al pulsarlo se ejecute.

Agrupación de datos en excel y representación gráfica (I)

Vamos a hablar en este post de una sencilla forma de presentar nuestras Cuentas de Resultados, mediante la agrupación de datos por epígrafes y su posterior representación gráfica.

Alguna de las pautas que todo financiero debe tener en cuenta a la hora de presentar la información financiera es la sencillez de la misma y que ésta resulte visualmente atractiva.

A continuación representamos de forma esquemática como proceder, nos basamos en el Excel 2010.

Partimos de una Cuenta de Resultados exportada a excel, desde cualquier ERP o SAP,  de un determinado Hotel a una fecha concreta.

(HACER CLICK PARA AGRANDAR)

Corelpantallazo1006

Como vemos tal como se presenta es muy difícil discriminar la información que nos es útil.

Vamos a mejorarla.

Utilizando la Ficha Principal “Datos” del Excel, en el cuadro de diálogo “Esquema” , vamos a agrupar la información.

Corelpantallazo1008

Utilizando las agrupaciones que nos permite nuestro NPGC llegaremos hasta 5 niveles de agrupación (o más si así se desea),  basta con seleccionar los datos a máximo detalle (por ejemplo a nivel de cuenta o partida), seleccionando las filas a agrupar, y pulsar a continuación el botón de Agrupar (+); siempre podremos rectificar repitiendo la selección anterior y desagrupando o “borrando esquema”. A continuación  vemos como agrupamos los Gastos de Personal :

1010big

Si hacemos esto con el resto de agrupaciones nos encontramos con una Cuenta de Resultados más manejable, donde es más fácil agrupar o desagrupar la información que interesa, simplemente pulsando en los símbolos + y – de agrupación a la izquierda de la pantalla, hasta esos 5 niveles de agrupación comentados.

1016

Tanto el EBITDA como el EBT no figuran como tal en los plan contable español (NPGC) pero su uso se ha extendido de forma notoria en los últimos años, sobre todo del EBITDA, ya que permite una comparación homogénea de los resultados de diferentes empresas en diferentes sectores al no tener en cuenta las correspondientes políticas de amortizaciones (que como sabemos pueden variar en función de los tipos de activos o políticas contables), el resultado financiero (así se aísla el impacto que puede tener el coste por endeudamiento o la retribución de inversiones financieras), las provisiones de gastos y la carga impositiva. Es por tanto el indicador más adecuado para conocer si una empresa gana en su actividad propiamente dicha.

En el próximo post comentaremos como segmentar datos en excel para obtener representaciones gráficas como la siguiente:

blog11003