Bases de datos en excel

Las bases de datos nos permiten almacenar información de forma ordenada y estructurada. Las bases de datos surgieron ya hace unos años, con la necesidad de manejar las grandes cantidades de información que se empezaban a generar.

Por lo general, tenemos sistemas de implementación de bases de datos más avanzadas como mysql, pero también podemos diseñar e implementar bases de datos en excel. Eso es lo que vamos a ver en esta parte del curso de excel, englobando conceptos teóricos de como diseñar bases de datos y conceptos prácticos de como implementarlo en excel.

¿Qué es una base de datos?

Como he mencionado, una base de datos nos permite almacenar datos de forma ordenada y estructurada. Utilizando una base de datos podemos separar los datos en diferentes tablas y hacer relaciones entre ellas para tener los datos estructurados.

Las bases de datos están pensadas para poder tener la información estructurada de forma que sea fácil y rápido acceder a ella. Esta característica es muy importante, sobre todo en casos donde hay mucha información.

Las bases de datos se organizan en tablas. Cada una de las tablas tiene N filas y M columnas. Siendo N un valor variable, según el número de datos introducidos en la base de datos en cada momento; y M un valor estático, estipulado durante la creación de la base de datos y que hace referencia a los datos que vamos a guardar en la base de datos.

Por ejemplo, si quisiéramos almacenar en una base de datos con nuestros libros, debemos guardar, al menos título del libro, autor y editorial (por si se diera el caso de que hay más de una editorial que lo haya licenciado). Según el caso individual de cada uno, podrían añadirse más campos como idioma, o si quisiéramos año de publicación. Pero con los campos comentados inicialmente debería ser más que suficiente para llevar un cuenta de los libros que tenemos en nuestras estanterías.

Además, llevado al concepto visual de base de datos, tendríamos una tabla para almacenarlo de la siguiente manera:

Dada esta forma de organización, es fácil aplicar este concepto a las hojas de cálculo de excel, que nos permiten hacer de forma sencilla nuestras bases de datos.

Sin embargo, ¿en qué casos es necesario hacer una base de datos? Por lo general, en cualquier caso que tengamos una serie de datos que tienen características similares, es recomendable contar con una base de datos.

Un ejemplo muy claro y práctico es el de cualquier tienda que venda productos. Tenemos los productos (elemento que constituye una tabla) que tienen su código de barras, su precio, su marca, y otras variables dependiendo del tipo de producto. Pero en general, podemos extraer características que se podrían generalizar para construir la tabla y especificar en cada caso concreto.

En el ejemplo anterior, tenemos la generalización de los datos en título, autor y editorial, mientras que para cada fila de la tabla tenemos los datos concretos para cada libro.

Ahora que ya sabemos lo que es una base de datos a grandes rasgos y cuando nos hace falta, vamos a ver como se diseñan las bases de datos. Es importante mencionar que existen varios tipos de bases de datos, pero en este tutorial sólo vamos a tratar uno de ellos, que es el que más se usa: bases de datos relacionales.

Diseño de bases de datos aplicado a excel

Dado que ya he explicado en el artículo sobre bases de datos toda la teoría necesaria para que entiendas como funcionan, me voy a apoyar en esa teoría para aplicar en esta parte del curso la parte práctica.

Además, para ilustrar bien la parte teórica que voy a ir explicando y que lo entendáis, voy a poner un ejemplo durante la explicación. 

Vamos a poner un ejemplo sencillo, parecido al anterior pero llevado al mundo real, de una tienda que vende libros y quiere mantener la información organizada en una base de datos en excel. Además de la base de datos donde se almacenan los libros, también vamos a organizar la base de datos de clientes que compran en la tienda y el sistema de tickets o facturas.

De esta manera tendremos un ejemplo sencillo pero completo de lo que a grandes rasgos se haría en un caso real.

Identificar tablas en la base de datos

En nuestro ejemplo de la librería es bastante intuitivo pensar que una tabla de nuestra base de datos será la de los libros, donde se almacenará toda la información de cada uno de los libros.

Además, en otra tabla deberíamos almacenar los clientes, especificando los datos que se guarden de ellos. Por último, es preciso almacenar todas las compras que se han realizado, ya que puede ser necesario acceder a esta información por diversos motivos. Para ello, debemos crear una tabla de compras o facturas, donde se almacenarán las compras que ha hecho cada cliente, cada día y de los libros que ha comprado.

Por tanto, inicialmente esta base de datos contará con tres tablas principales: Libros, Clientes y Facturas.

Especificar los campos de las tablas

Para especificar los campos en la tabla de libros debemos guardar un ISBN, un título, autor, editorial y precio.

En principio con estos datos podemos identificar sin lugar a dudas un libro. Para identificarlo valdría únicamente son un ISBN, pero obviamente queremos guardar más información que nos sea útil, ya que si por ejemplo nos pregunta un cliente si contamos con un libro, nos va a decir un título, no un ISBN.

Para el caso de los clientes, podríamos almacenar un número de teléfono, un email, el DNI y nombre. De esta manera también podríamos plantear en el futuro un sistema para mandar sms o correo electrónico a todos aquellos clientes que les interese un autor o que hayan reservado un libro.

Es importante tener en cuenta estas decisiones de diseño desde el principio, ya que si quisiéramos ampliar en el futuro la base de datos, sería mucho más complejo y en algunos casos no se podrían añadir nuevos atributos a nuestras tablas.

Con los datos del DNI, teléfono y correo electrónico tenemos manera de diferenciar a todos los clientes, ya que pueden cambiar de número de teléfono o de correo electrónico, pero nunca de DNI.

Por último, para almacenar las facturas, debemos tener la información del cliente que ha hecho la compra, los libros que ha comprado y la cantidad, el precio total, la fecha de realización del pedido y el tipo de pago. En principio con estos atributos sería más que suficiente.

En los próximos apartados vamos viendo como relacionar estas tablas entre sí.

Identificar la clave primaria de cada tabla

En el caso de la tabla de libros la clave primaria ha de ser claramente el ISBN, ya que es único para cada libro es una manera de identificarlo mediante una clave numérica.

Para la base de datos de clientes, la clave primaria ideal es el DNI, ya que también identifica de forma individual a los clientes.

Por último, en la base de datos de facturas, debemos tener un identificador de factura que debe ser único para cada factura, de esta manera podemos identificarlas.

Especificar las relaciones entre tablas

La idea de la relación es que esté almacenada la base de datos de libros por un lado y la de clientes por otro. Por supuesto, estas tablas se pueden actualizar según van saliendo nuevos libros o descatalogándose y según aparecen nuevos clientes. Con estas dos tablas creadas, la base de datos nos va a facilitar la tarea de almacenar la información de compra porque ya la tenemos, solo tenemos que guardar esa relación.

La relación de compra, obviamente se guardará en la tabla de facturas, y se hará relacionando cada uno de los libros que se compra con el comprador. Como los datos de los libros y de los clientes ya los tenemos almacenados, simplemente es relacionarlos mediante sus claves primarias.

En nuestro ejemplo, en la tabla de facturas encontraremos la relación de ISBN de la tabla de libros con el ISBN de los libros en las facturas (es decir, es el mismo dato). También tenemos el ID de cliente, que lo más lógico es que sea el DNI. Así que tendremos el DNI en la tabla de clientes y en la de facturas. En principio, en nuestro ejemplo esas serían las relaciones que habría entre las tablas.

Identificar y manejar los datos repetidos en las bases de datos

En la tabla de libros, podríamos probar varios ejemplos de libros. El ISBN nunca se va a repetir. El nombre del libro puede repetirse pero sería algo muy poco común. Sin embargo, el autor es bastante fácil que se repita.

Cuando esto ocurre, la mejor práctica es separar esta tabla y crear una subtabla de autores, donde se guarde nombre y apellidos asociado a un ID. Este ID será el que se ponga en la tabla de libros para hacer referencia al autor, de esta forma no existirán problemas como escribir mal el nombre del autor y que la base de datos lo identifique como dos autores diferentes, por ejemplo.

Es importante entender que los datos repetidos en las bases de datos es mejor manejarlos de esta manera, evitando el problema que acabo de comentar e intentando que las tablas sean lo más robustas posible.

Por otro lado, en los datos de la tabla de cliente, donde tenemos almacenado DNI, nombre, correo electrónico y número de teléfono, es cierto que pueden repetirse nombres pero van a pertenecer a personas diferentes y no es algo que pueda ocurrir tan fácilmente, así que la tabla puede quedarse tal y como está.

Por último, en la tabla de facturas, tendríamos que poner por cada fila, un producto. Por tanto si un cliente compra dos libros diferentes a la vez, habría que introducir dos filas en la tabla, donde habría el mismo ID de factura. Eso rompería la regla de no tener datos repetidos, ya que precisamente el ID de factura debe ser único para cada fila, de forma que nos permita diferenciarlo del resto.

Por eso, en este caso tenemos que hacer lo mismo que en los autores de los libros. Para ello, creamos otra tabla, que puede llamarse FacturasCompletas, donde almacenamos toda la información de libros que se han comprado en esa factura. Para ello, almacenaremos el ID de factura, el ISBN de los libros, la cantidad de cada libro y el precio individual de cada libro. En este caso, marcaremos como clave primaria la unión del ID de factura y del ISBN, ya que no existe una manera más simplificada de identificar la compra de un libro.

De esta manera, como hemos visto, aunque parecía que la base de datos estaba terminada con tres tablas, hemos tenido que ampliar nuestra base de datos para evitar errores a futuro.

Crear la base de datos en excel

El diseño de la base de datos es sin lugar a dudas la fase que más trabajo y tiempo nos va a llevar, ya que tenemos que hacer un buen diseño si queremos que todo funcione correctamente.

Una vez tengamos dicho diseño, podemos implementarlo en este caso en Excel. Para ello vamos a utilizar tablas de Excel. Las tablas en Excel son la mejor manera de identificar rangos de celdas como información unificada, es como tener pequeñas hojas de calculo acotadas a un rango concreto.

Al formar el elemento tabla en Excel, se identifica automáticamente que la información dentro de cada tabla está relacionada y que no es arbitrario. Además, el hecho de utilizar tablas, hará que todo sea mucho más cómodo de manejar ya que se automatizan ciertas acciones como añadir filas o eliminarlas, de esta manera el manejo de la base de datos será mucho más rápido.

Para crear una tabla en Excel, debemos seleccionar el rango de celdas que queremos que se conviertan en una tabla y pulsar Tabla en el menú de insertar. Es importante ponerle un nombre coherente a la tabla para luego referenciarla.

El hecho de utilizar las tablas que nos proporciona Excel, tiene muchas ventajas, como que nos permite ordenar los datos por alguna de las columnas especificadas, facilita el crecimiento de los datos y reconoce automáticamente las nuevas filas y columnas añadidas.

Hacer consultas a las bases de datos en excel

Cuando ya tenemos creada la base de datos de Excel, los datos empezarán a crecer y crecer. Inicialmente, quizá nos resulte cómodo comprobar o buscar datos directamente en las tablas. Sin embargo, cuando tengamos mucha información en las tablas, será inviable buscar la información manualmente.

Por eso, tenemos fórmulas que nos permiten consultar la base de datos que tenemos montada en nuestras hojas de Excel. Con el ejemplo que hemos ido viendo a lo largo del tutorial, tendríamos las siguientes hojas con tablas:

Cada una de estas tablas están nombradas, tal y como he mostrado en el vídeo anterior. Para buscar datos entre tablas podemos usar la función de excel =BUSCARV(). Para ello, utilizamos los siguientes parámetros: el valor que queremos encontrar (1) en la tabla que queremos buscar (2) en la columna (numéricamente) que esperamos que está (3). En el ejemplo a continuación podemos ver un caso real en el que buscamos el correo electrónico correspondiente al DNI seleccionado.

Con esto, llegamos al final de este tutorial de bases de datos en excel. Hemos visto los conceptos necesarios para entender como diseñar una base de datos y como implementarla en Excel, Si tenéis alguna duda sobre lo que hemos visto en este curso de Excel, no dudéis en ponerlo en comentarios.