+

Tablas dinámicas en Excel 2013

Dr. Raúl Morales Salcedo

+Introducción

n Las tablas dinámicas son una herramienta muy potente para resumir la información de modo interactivo y según distintos criterios.

n Veremos en detalle sus opciones y posibilidades, además de aprender a realizar cálculos con ellas, conoceremos la mejor manera de presentar y comprender la información.

+Tablas dinámicas

n  Cuando trabajamos con una base de datos, generalmente debemos realizar diferentes análisis (procesamiento) sobre su contenido para obtener la información que deseamos .

n  Para esto, necesitamos resumir y ordenar la información según distintos objetivos. Si bien podemos hacerlo mediante diferentes herramientas que ofrece Excel, esto puede resultar bastante complejo, principalmente, en caso de tener muchos registros y campos.

n  Una alternativa de gran utilidad en estos casos son las tablas dinámicas.

+Tablas dinámicas

n  Las tablas dinámicas nos permiten generar un informe que resume y ordena la información contenida en una hoja de cálculo, de modo que nos facilita el análisis de su contenido.

n  Su gran utilidad reside en que es posible ajustar los análisis y los resúmenes de la información de muchas maneras y según distintos criterios, mediante pocos clics.

n  En resumen de los datos puede hacerse utilizando diferentes operaciones de cálculo, como la suma y el promedio. Además, en este tipo de tabla es posible alterar la disposición de las filas y las columnas de un modo sencillo, es decir, intercambiar su ubicación. También se pueden agregar o quitar campos en cualquier lugar de la tabla.

+Cómo hacer una tabla dinámica

PROBLEMA:

n  Consideremos que contamos con una base de datos de una empresa, en la cual se registran las ventas realizadas por cada empleado. Los campos son Fecha, Vendedor, Producto, Cantidad y Monto.

+Conceptos

n  DBMS

n  SQL

n  Base de datos

n  Tablas = entidades

n  CREATE DATABASE

n  CREATE TABLE

n  DESCRIBE TABLE

n  INSERT INTO table VALUES ( ‘texto’, número)

n  Importación de DATOS an un archivo Excel

n  http://ubiquitous.csf.itesm.mx/~raulms/adm

+Datos Importantes

n  Usuario de base de datos: #_user número de estudiante sin A ni ceros iniciales

n  Password: número de estudiante sin A mayúscula ni ceros iniciales

+Importación de datos

SELECT Fecha,Vendedor,Producto,Cantidad,Monto FROM ventas INTO OUTFILE 'd:/archivo-excel.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

En donde: 1.  INTO OUTFILE: indica el directorio y archivo donde se

guardan los datos de la consulta. 2.  FIELDS TERMINATED BY: separador que utilizaremos para

los campos (columnas en excel). 3.  ENCLOSED BY: delimitador utilizado para cada campo. 4.  LINES TERMINATED BY: separador que utilizaremos para

indicar el fin de línea.

+Archivo creado despues de la importacion

+Cómo hacer una tabla dinámica

n  Para transformar la base en una tabla dinámica, nos posicionamos sobre ella, vamos a la opción Insertar y hacemos click sobre la opción de Tabla dinámica, dentro del grupo Tablas. Se abrira el cuadro de diálogo Crear tabla dinámica, en el que debemos confirmar el rango de datos de origen y la ubicación de la tabla: en la misma hoja o en una nueva tal y como lo vimos la clase anterior

+Cómo hacer una tabla dinámica

+Cómo hacer una tabla dinámica

n  A continuación, en la hoja de cálculo aparece un cuadro de diálogo que nos indica que debemos elegir los campos de la lista Campos de tabla dinámica, donde debemos completar los campos de filas, los de columnas, los de valores y los de filtro.

n  Esta ventana está ubicada a la derecha de la hoja y, como dijimos, tiene como nombre Campos de tabla dinámica. Allí debemos marcar los campos que queremos agregar al informe. Para averiguar la cantidad vendida de cada producto, en Campos de tabla dinámica seleccionamos Producto, que se ubicará dentro de Filas; y Cantidad, que se situará dentro de Valores. Al hacer esto, quedará confeccionada la tabla, y en ella veremos las unidades vendidas de cada producto.

+Cómo hacer una tabla dinámica

+Cómo hacer una tabla dinámica

n  Si en vez de la cantidad vendida queremos conocer el monto de las ventas por producto, desmarcamos el campo Cantidad y activamos Monto.

+Cómo hacer una tabla dinámica

n  Para realizar un análisis más complejo podemos necesitar cruzar dos o más campos; por ejemplo, si queremos saber qué cantidad de cada producto fue vendida por vendedor. En este caso, agregamos el campo Vendedor; al seleccionarlo, también se ubicará en Filas. Si queremos colocarlo como columna, lo arrastramos desde Filas a Columnas.

+Cómo hacer una tabla dinámica

n  En la tabla dinámica veremos la cantidad vendida de cada producto por cada vendedor, la cantidad total vendida de cada producto y la cantidad total vendida.

n  Si ubicamos el campo Vendedor como columna, podremos apreciar la cantidad total vendida por cada vendedor.

+Actualización de tablas dinámicas

n  Al agregar, quitar o modificar registros de la hoja de excel, las tablas dinámicas pueden actualizarse fácilmente. Sin embargo, debemos saber que la actualización no se realiza de modo automático, como ocurre con las fórmulas, sino que debemos hacer un clic con el botón derecho dentro de ella y pulsar Actualizar.

n  En nuestro ejemplo, si la empresa incorpora nuevos vendedores o comienza a comercializar otros productos, al actualizar la tabla dinámica se agregaran las filas necesarias, según los registros que se hayan incorporado en la base de datos, siempre y cuando estén incluidos en el rango de los datos de origen.

+Ejercicio tarea

n  Consideremos que deseamos crear una base de datos de una agencia de autos usados llama Autos Pedregal , el departamento comercial nos suministra un listado de los vehículos disponibles para la venta y deseamos conocer la situación real de precios, unidades, etc.

1.  El departamento comercial nos pide crear una base de datos con los siguientes campos: Tipo, Marca, Modelo, Precio, Cantidad y Descuento.

2.  Debemos de insertar los siguientes registros:

+Ejercicio tarea

3.  El gerente desea conocer la cantidad de vehículos disponibles por su tipo y nos piden crear un reporte con los siguiente datos, tomando en consideración que debemos tomar datos de la BD del departamento comercial:

a)  De que tipo de vehículo tenemos más cantidad y el precio más alto.

b)  De que tipo de vehículo tenemos menos cantidad y el precio más bajo.

c)  Cual es el precio total de venta de todos los vehículos.

d)  Cual es el precio total de venta de todos los vehículos, de los que disponemos de 3 unidad

+Ejercicio tarea

n  Genere un reporte en formato Word de cómo hacer y para qué les sirve una tabla dinámica a partir de una base de datos.

n  Realice un reporte de la diferrencia entre una hoja de cálculo y una base de datos, cuándo debe de utilizarse uno y cuando la otra.