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