¡Acceso ilimitado 24/7 a todos nuestros libros y vídeos! Descubra la Biblioteca Online ENI. Pulse aquí
¡Acceso ilimitado 24/7 a todos nuestros libros y vídeos! Descubra la Biblioteca Online ENI. Pulse aquí
  1. Libros
  2. Análisis eficaz de datos
  3. Gestión de los datos de origen
Extrait - Análisis eficaz de datos Con tablas dinámicas de Excel (versiones 2019 y Office 365)
Extractos del libro
Análisis eficaz de datos Con tablas dinámicas de Excel (versiones 2019 y Office 365) Volver a la página de compra del libro

Gestión de los datos de origen

Introducción

La utilidad de los resultados obtenidos en una tabla dinámica depende del buen uso que se haga de los datos de origen. Excel permite utilizar varios tipos de origen:

  • Una hoja de cálculo cuyo rango es fijo.

  • Una hoja de cálculo de Excel cuyo rango es dinámico.

  • Una selección discontinua de Excel (diferentes tablas situadas en la misma hoja, diferentes hojas de un mismo libro, o varios libros).

  • Una base de datos externa (otro libro de Excel, una tabla de Access, una tabla de SQL Server…).

Conocimientos necesarios

Técnicas de creación de tablas dinámicas

Consolidación de lo que ya conoce y lo que va a aprender

Crear rangos de origen cuyas dimensiones se ajustan automáticamente.

Modificar el rango de origen.

Utilizar varios libros como origen.

Utilizar datos que vengan de otros programas.

Modificar un rango de origen

Las tablas dinámicas se basan en los datos especificados en el momento de la creación.

Se pueden dar casos en los que sea necesario modificar dichos datos:

  • No ha especificado un rango dinámico y el número de filas o de columnas del origen ha aumentado.

  • El resumen se tiene que realizar sobre un rango de datos diferente.

Para poner en práctica esta funcionalidad, vamos a trabajar en el libro GastosHogar.xlsx. Este libro tiene dos hojas (Gastos2020 y Gastos2021), cada una de las cuales contiene el conjunto de gastos anuales de un hogar.

Se van a crear dos tablas dinámicas: cada una hará referencia a los gastos de un año.

A continuación, puede ver las primeras filas de cada hoja...

images/OST10_232.png

Hoja Gastos2020

images/OST10_233.png

Hoja Gastos2021

Creación de la primera tabla dinámica

La tabla de 2020 está completa, pero la de 2021 se detiene en el 25/06/2021.

Para obtener un seguimiento en tiempo real de nuestros gastos a lo largo del año, tiene sentido crear un rango dinámico para cada año; de este modo, no necesitaremos modificar la fuente de datos a medida que introduzcamos nuevos gastos

 En la pestaña Fórmulas, grupo Nombre definidos, haga clic en el botón Administrador de nombres, o utilice el atajo de teclado CtrlF3.

 En el cuadro de diálogo Administrador de nombres, haga clic en el botón Nuevo.

 Introduzca el nombre...

Utilizar un rango de datos discontinuo

Es posible que alguna vez tenga la necesidad de consolidar información que provenga de diferentes tablas. Estos datos pueden ser:

  • Tablas no contiguas situadas en una misma hoja.

  • Tablas situadas en diferentes hojas de un mismo libro.

  • Tablas situadas en hojas de diferentes libros.

Si esta información se encuentra en otros libros, se considera que son datos externos. Excel es capaz de realizar un resumen a partir de esta información. De todos modos, le aconsejamos que organice la estructura de dichos datos de la misma forma.

Imagine que tiene dos librerías. Cada una de estas librerías nos envía por correo electrónico cada fin de semana un libro de Excel con el detalle de ventas realizadas desde el inicio del año, solo para las obras de un editor en concreto. Nuestro objetivo consiste en establecer, a partir de estos dos libros, una estadística comparativa de ventas de las dos librerías para este editor.

Para poder realizar este trabajo, dispone de dos libros: VentasAlicante.xlsx y Ventas-Cuenca.xlsx.

 Abra estos dos libros.

Cada libro tiene una hoja llamada Ventas cuya estructura puede ver a continuación. Se han introducido las ventas hasta 13/11/2021.

images/OST10_241.png

VentasAlicante.xlsx

images/OST10_242.png

VentasCuenca.xlsx

Para facilitarle el trabajo, y antes de proporcionar los archivos vacíos a cada sitio, vamos a dar por hecho que ha configurado un rango dinámico de tres columnas en cada archivo.

En cada uno de los libros, el rango dinámico se llama ListaVentas, y se define con la fórmula: =DESREF(Ventas!$A$1;;;CONTARA(Ventas!$A$1:$A$5000);3)

Activar el Asistente para tablas y gráficos dinámicos

Vamos a añadir a la barra de herramientas de acceso rápido el Asistente para tablas y gráficos dinámicos.

 Haga clic en la pestaña...

Utilizar un rango de datos externo

Excel es una fantástica herramienta de cálculo y de análisis. Permite, como con un SGBDR (Sistema de Gestión de Base de datos Relacional), administrar las relaciones entre tablas. Si tiene un sistema de gestión creado en Access, puede realizar sus análisis con Excel a partir de una conexión a una base de datos de Access.

Para ilustrar el uso de orígenes externos, utilizaremos una primera base de datos de Access llamada BDFormaciones.accdb.

Esta base de datos contiene la formación realizada por los empleados de una empresa y está compuesta por dos tablas vinculadas entre ellas: Empleados y Formaciones.

A continuación, puede ver la ventana de relaciones de Access.

images/OST10_259.png

A continuación, le mostramos los primeros registros de cada una de las tablas.

Tabla Empleados:

images/OST10_260.png

Tabla Formaciones:

Para cada formación, esta tabla recoge los empleados que han participado en ella.

images/OST10_261.png

Después de esta presentación de los datos de origen, vamos a crear la tabla dinámica.

Nuestro objetivo es conocer el número de horas de formación, así como el número de formaciones realizadas este año por cada empleado, así como el número de personas formadas por área de formación.

Base de datos de Access - Método 1

Copiar y pegar la información de las tablas de Access en una hoja de cálculo de Excel no sería una buena opción por dos razones principales:

  • En primer lugar, este método no crea un vínculo dinámico entre Access y Excel. Para actualizar la tabla dinámica tendrá que abrir la base datos y copiar los datos nuevos en la hoja de Excel.

  • Además, este método ralentiza el proceso, ya que la información se almacenaría en Excel.

Vamos a ver ahora una técnica que nos permite resolver estos dos problemas.

En nuestro...

Utilizar una conexión existente

Para poder utilizar una conexión que ya existe, vamos a crear una tabla que nos permite obtener el número de personas formadas en cada área.

 Inserte el cursor en la celda F1 (puede hacerlo en la misma hoja).

 En la pestaña Insertar, haga clic en el botón Tabla dinámica.

 Marque la opción Utilice una fuente de datos externa y haga clic en el botón Elegir conexión.

 Ya se había establecido una conexión para la anterior tabla dinámica. Seleccione un archivo de conexión que haga referencia a nuestra base de datos de Access.

images/OST10_281.png

 Haga clic en Abrir y a continuación, en Aceptar.

Excel muestra de nuevo en la hoja activa la lista de campos.

 Cree la tabla dinámica.

images/p220-v2.png

Puede ser interesante acceder a los parámetros de conexión para personalizarla en función de sus necesidades.

 Haga clic en la última tabla dinámica creada.

 En la pestaña Datos, grupo Consultas y conexiones, haga clic en Propiedades.

Personalice los parámetros en función de sus necesidades y del uso que vaya a darle.

Si la base de Access está en su red, la actualización de las formaciones se realiza regularmente y desea tener en tiempo real las estadísticas, puede configurar una actualización automática con una frecuencia determinada.

images/OST10_283.png

La pestaña...