Ediciones ENI Editorial | líder en informática
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 (Gastos2015 y Gastos2016), 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...
Hoja Gastos2015
Hoja Gastos2016
1. Creación de la primera tabla dinámica
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.
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 del rango (GasA2015) e introduzca la siguiente fórmula en la sección Se refiere a:=DESREF(Gastos2015!$A$1;0;0;CONTARA(Gastos2015!$A$1:$A$5000);4)
Haga clic...
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 sitios web de venta de libros. Cada uno de estos sitios 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 los dos sitios web para este editor.
Para poder realizar este trabajo, dispone de dos libros: VentasBarcelona.xlsx y Ventas-Valencia.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 30/11/2016.
 VentasBarcelona.xlsx |
 VentasValencia.xlsx |
Para facilitarle el trabajo, y antes de proporcionar los archivos vacíos a cada sitio, configure un rango dinámico de dos 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)
1. 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 poder 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 tres tablas vinculadas entre ellas: Empleados, Formaciones y FormacionesEmp.
A continuación, puede ver la ventana de relaciones de Access.
A continuación, le mostramos los primeros registros de cada una de las tablas.
Tabla Empleados:
Tabla Formaciones:
Cada registro corresponde a una formación.
Tabla FormacionesEmp:
Esta tabla recoge los empleados que han participado en cada formación.
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 utilizadas realizadas este año por cada empleado y el coste total para la empresa de formación por área de formación.
1. 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 ejemplo, la base de datos se ha guardado en una carpeta llamada: C:\SeguimientoFormaciones.
Cree un nuevo libro (Ctrl N).
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.
En el cuadro de diálogo Conexiones existentes, haga clic en el botón Examinar...
