Tablas dinámicas en Excel PDF

Preview

Summary

Desarrollo de tablas dinámicas de Excel desde cero. Te lleva paso a paso y aprenderás forma rápida y práctica....

Description

Manual de Tablas dinámicas 2016/2013

Ing. Ric Ricardo ardo Cacique Consultor de Empresas

I. Contenido I.

CONTENIDO _________________________________________________________________ 1

II.

OBJETIVO DEL MANUAL. _______________________________________________________ 2

III.

INTRODUCCIÓN _____________________________________________________________ 2

IV. BASES DE DATOS EN EXCEL ___________________________________________________ 3 V. CREAR UNA TABLA DINÁMICA__________________________________________________ 4 1. ESTRUCTURAR BASE DE DATOS EN EXCEL ______________________________________________ 4 2. IDENTIFICAR LOS ENCABEZADOS POR COLUMNA __________________________________________ 6 3. CREACIÓN DE LA TABLA DINÁMICA ___________________________________________________ 7 ESTRUCTURA DE UNA TABLA DINÁMICA ___________________________________________________ 9 4. AGREGAR CAMPOS EN LA TABLA DINÁMICA ____________________________________________ 10 5. MODIFICAR CAMPOS EN LA TABLA DINÁMICA __________________________________________ 11 6. UTILIZAR EL ÁREA DE FILTRO _____________________________________________________ 17 7. FILTROS DE FILAS Y COLUMNAS. ___________________________________________________ 21 VI. FUNCIONES DEL BOTÓN DERECHO. ____________________________________________ 24 VII. HERRAMIENTAS DE TABLA DINÁMICA _________________________________________ 30 PESTAÑA ANALIZAR _______________________________________________________________ 30 PESTAÑA DE DISEÑO ______________________________________________________________ 37 VIII. GRÁFICOS DINÁMICOS _____________________________________________________ 43 IX.

CURRICULAR DEL AUTOR. ____________________________________________________ 47

ANEXO 1. DISEÑO DE TABLA DINÁMICA CLÁSICA (PERMITE ARRASTRAR CAMPOS EN LA CUADRICULA) _______ 48 ANEXO 2. EJEMPLOS PARA CONFIGURAR EL CAMPO… ________________________________________ 50 ANEXO 3. OPCIONES DE LA TABLA DINÁMICA ______________________________________________ 54 ANEXO 4. CÁLCULOS EN LA TABLA DINÁMICA ______________________________________________ 57 ANEXO 5. CONSOLIDACIÓN MÚLTIPLE ___________________________________________________ 61

pág. 1

II. Objetivo del Manual. Aprender a utilizar tablas dinámicas para analizar, organizar y manipular diferentes datos a partir de una extensa gama de información y obtener información útil para la toma de decisiones.

III. Introducción Hoy en día en el ámbito empresarial y de negocios, la información útil y precisa se vuelve esencial para la toma de decisiones en cualquier área de una compañía. Para obtener información útil y precisa deriva del conocimiento del negocio y de lo que se pretende gestionar. Por lo que existen herramientas poderosas que ayudan a facilitar el trabajo y obtener información útil como es el Excel. Microsoft nos presenta a Excel como un programa para la administración, manipulación y organización de datos por medio de hojas de cálculo. Dentro de la gran variedad de funciones que contiene el programa de Excel, contiene una herramienta poderosa que nos ayuda a analizar y presentar información de una forma rápida y sencilla: Tablas dinámicas La tabla dinámica es una herramienta versátil para el análisis de base de datos o información y tienen la capacidad de dar respuesta rápida y sencilla al cambiar los campos de diferentes formas y en diferentes perspectivas. Este dinamismo nos permite visualizar de diferentes modalidades la información y nos facilita la toma de decisiones con base a información analizada. Además, con las tablas dinámicas es práctico crear diferentes informes, indicadores y gráficos interactivos. Cabe mencionar que las tablas dinámicas son útiles para toda aquella persona que deseé analizar la información de su área, negocio o generar reportes de forma concisa. Sin embargo, el analista debe ser creativo para poder convertir datos en información útil y precisa por medio de una tabla dinámica. El presente manual se describirá de forma detallada de cómo hacer Tablas dinámicas en Excel y mediante ejemplos se analizará los datos utilizando dicha herramienta.

pág. 2

IV. Bases de Datos en Excel Una base de datos es un conjunto de información que está relacionada entre sí. La mayoría de los datos generados provienen de los sistemas de información de un negocio o una empresa. Las bases de datos son repositorios de información que almacenan gran cantidad de información que se va creando en el transcurso del tiempo. Por ejemplo, base de datos de las ventas netas de dos años de la compañía “Productos Premium S.A. de C.V” podemos encontramos registros como el número de factura, números de notas de crédito, remisiones, devoluciones, los clientes, día, mes, año de la transacción, el nombre del producto, el ID del producto, las cantidades, vendedores, comisión de vendedores, estados de la república, los almacenes de donde salió o entro la mercancía, el importe vendido, los descuentos comerciales, números de pedimento (si fuera el producto importado), etc. Como podemos interpretar, cada dato generado esta interrelacionado entre sí de tal manera que se genera una base de datos de ventas netas de la compañía “Productos Premium S.A. de C.V”, además, si por cada transacción te genera un registro entonces imaginemos que en dos años se van a generar miles de registros, lo cual dificulta el análisis de las ventas netas para el personal administrativo de la compañía “Productos Premium S.A. de C.V”. Para esto la herramienta de Tablas dinámicas nos va ayudar a analizar los datos de forma sencilla y rápida, a partir de una base de datos. Antes de empezar a crear tablas dinámicas necesitamos una base de datos del tema que se desea analizar o dar seguimiento para su gestión. Ya que la base de datos es la materia prima de las tablas dinámicas.

Base de Datos

Estructura en Excel Margen ($) 142 974 146 588 931 818 300 136 202 1,123 951 376 802 173 1,044

Tabla dinámica

%Margen

Referencia

Fecha pedido

Día

Mes Año

Origen venta

ID_cliente

24.0% 33.1% 37.1% 33.6% 29.1% 30.2% 30.6% 30.1% 30.4% 38.1% 33.9% 30.4% 35.0% 23.1% 34.0%

OSPIYYQFJ HQUBJUPEX IRSAMXQDY IRZCRXJDU HQTINZNI U ENYINUQBU DMZARTJHW GPTGLZQCW HQVEQZQDX GPUCLYRDZ BKYERALBU IRTIOYNFX GPWAOZKEZ DMZBRYOIU BKSFLTMEY

22/04/2013 16/08/2013 27/04/2013 28/11/2013 09/01/2013 11/07/2013 16/02/2013 25/02/2013 19/07/2013 01/12/2013 13/02/2013 26/04/2013 27/02/2013 09/12/2013 08/08/2013

22.00 16.00 27.00 28.00 9.00 11.00 16.00 25.00 19.00 1.00 13.00 26.00 27.00 9.00 8.00

4.00 2013 8.00 2013 4.00 2013 11.00 2013 1.00 2013 7.00 2013 2.00 2013 2.00 2013 7.00 2013 12.00 2013 2.00 2013 4.00 2013 2.00 2013 12.00 2013 8.00 2013

Sistema Sistema Teléfono Correo E. Correo E. Teléfono Sistema Otro Correo E. Sistema Otro Sistema Sistema Teléfono Teléfono

245 446 255 252 5 467 26 15 219 515 32 4 112 760 369

pág. 3

V. Crear una tabla dinámica 1. Estructurar base de datos en Excel Primero debemos preparar la base de datos dando estructura a los datos por columnas, es decir, debemos definir un encabezado por columna, esto nos va servir para identificar los campos en la tabla dinámica. Además, se recomienda que el analista coloque la mayor información para el análisis. En la estructura de la base de datos debemos evitar títulos en blanco o vacíos, la combinación de celdas, que no exista subtotales o columnas/filas escondidas. Además se recomienda unificar conceptos dentro de los datos. A continuación se presenta ejemplo de que debemos evitar y el deber ser en la estructura de bases de datos en Excel: Todas las columnas deberán tener un título o encabezado:

No deben existir celdas combinadas en los encabezados o en los campos

pág. 4

Es recomendable unificar conceptos en los datos de las columnas:

No debe haber filas o columnas escondidas:

No debe de haber subtotales o totales dentro de los campos:

pág. 5

2. Identificar los encabezados por columna Antes de crear la tabla dinámica, utilizaremos la siguiente base de datos de la compañía “Productos Premium S.A. de C.V”. En dicha base de datos encontramos registros de la facturación y notas de crédito del periodo de 2012 y 2013. Con base a la información de la base de datos derivaremos analizar la información utilizando tablas dinámicas. Debemos identificar la información que vamos analizar. Para esto describiremos los encabezados por columnas que contienen lo siguiente:

Encabezado Tipo No. Doc Fecha Díasem Mes Año Clave del cliente Crédito cliente Estado de la República Zona Clave Agente Clave del producto Clave corta Nombre del producto Segmento Alm. Promoción Cantidad Lote Precio unitario de venta Imp. C/Dscto Proveedor Costo unitario Margen

Descripción del encabezado Tipo de documento (FACE y NCEL) Folio del documento Fecha de transacción Día de la semana Mes de la transacción Año de la transacción Clave identificador del cliente Número de días de crédito Estado de la República ubicación cliente Zona ubicación cliente (distribución) Clave del vendedor Clave del producto Clave corta Nombre del producto Segmento el cual está dirigido el producto Almacén donde se generó la transacción Indica si hubo una promoción Cantidad de la transacción No. De Lote Precio unitario de venta Importe que se otorgó al cliente Nombre del proveedor Costo unitario Margen en pesos

Es importante identificar los encabezados, ya que va ser la parte fundamental para el análisis de datos. En el ejemplo, se tiene una base de datos de 24,022 registros originados en dos años.

pág. 6

3. Creación de la tabla dinámica Una vez que identificamos la estructura de la base de datos en columnas, comenzamos en seleccionar toda la base de datos. Siguiendo con el ejemplo, seleccionaremos desde la celda A1 hasta la celda X24023. Damos clic en la cinta de opciones de Insertar y pulsamos el botón “Tabla dinámica”:

pág. 7

Inmediatamente aparecerá un cuadro de dialogo “Crear tabla dinámica” en el cual solicita seleccionar los datos que se desea analizar. Sin embargo, podemos observar el rango que seleccionamos previamente (Vtas!A1:X24023). También nos pregunta en dónde deseamos colocar la tabla dinámica, se recomienda que sea en una Nueva hoja de cálculo y damos clic en Aceptar.

Nota: si deseamos colocar la tabla dinámica en una hoja conocida, únicamente señalamos a la hoja de cálculo que se desea colocar y seleccionar una celda.

Al dar aceptar nos creará una nueva hoja de cálculo y observaremos lo siguiente: 1) En la cinta de opciones observaremos que se habilita dos pestañas con nombre de Analizar y Diseño, también observamos arriba de las pestañas el texto Herramientas de tabla dinámica. Estas pestañas únicamente se habilitan cuando la celda esta activa dentro de la tabla dinámica, es decir, si movemos la celda activa fuera de la tabla dinámica se deshabilita las pestañas. Por lo tanto cada vez que deseamos hacer una modificación en la tabla dinámica primero debemos posicionar la celda dentro de la tabla dinámica:

2) En la hoja de cálculo observaremos del lado derecho todos los campos que corresponden a los encabezados de las columnas y del lado izquierdo observaremos la tabla dinámica. pág. 8

Estructura de una Tabla dinámica De la figura anterior del lado derecho observaremos cuatro áreas: Filtros, Columnas, Filas y Valores que corresponden a la estructura de la tabla dinámica (figura anterior del lado izquierdo). Para comprender la estructura de la tabla dinámica con datos, lo observamos en la siguiente imagen:

Como podemos observar en la figura anterior, la tabla dinámica tiene la estructura de una tabla o matriz (filas Vs columnas) la cual agrupa los valores que cruza entre las columnas y filas. Ahora describiremos las áreas o zonas de la tabla dinámica con el fin de entender la estructura: •

Área de filtros: Ésta área se colocan los datos o campos que se desean filtrar para toda la tabla dinámica y los filtros de dicha área son independiente a los filtros de filas y columnas. pág. 9

•

Área de valores: En esta zona se agregan los campos que contengan valores y que se deseé visualizar los valores agrupados que corresponden a los campos de columnas y filas asignados en la tabla dinámica. Además, todos los datos que contengan valores, en esta zona se pueden configurar operaciones sencillas para sumas, promedios, varianza, desviación estándar, entre otros. También se puede colocar datos que no contengan valores, pero en estos casos el programa solo contará los datos.

•

Área de Columnas: Son los valores que se desea ver de forma vertical y que van interrelacionados con respecto a los campos de filas.

•

Área de Filas: Son los valores que se desea ver de forma horizontal y que van interrelacionados con respecto a los campos de columnas.

4. Agregar campos en la tabla dinámica Podemos agregar campos desde el cuadro de diálogo del lado derecho. Lo único que hay que hacer es arrastrar el campo al área deseada, por ejemplo, vamos agregar el campo etiquetado como Tipo al área de Filas, Díasem al área de columnas, Zona al área de Filtros y Cantidad a Valores. Al agregar campos en las áreas se observa en la tabla dinámica los datos y valores agregados:

pág. 10

La tabla dinámica generada nos muestra un resumen de los días de la semana que hubo actividad por facturación o devoluciones en el transcurso de dos años. Es decir, del total de la base de datos de más de 24,022 registros obtenemos de forma rápida un resumen de las salidas y entradas de mercancía que hubo en dos años en todas las zonas que se distribuyó a los clientes. Lo impórtate de las tablas dinámicas es el análisis y las conclusiones que podemos obtener una vez que sepamos que deseamos evaluar. El ejemplo anterior es sencillo, sin embargo, podemos sacar conclusiones como, el día de la semana que hay más actividad por facturación, el día de la semana hay más devoluciones por clientes, podemos obtener la proporción de lo facturado por día o podemos visualizar de forma rápida la cantidad neta vendida durante dos años.

5. Modificar campos en la tabla dinámica El ejemplo anterior nos sirvió para crear tablas dinámicas y ver lo poderoso que son las tablas dinámicas a partir de una extensa información. Sin embargo, el ejemplo anterior aún podemos obtener información más precisa y desde otra perspectiva. Ahora procedemos a modificar los campos y utilizar los filtros para precisar la información de lo que se pretende analizar. Para modificar los datos existen dos formas. La primera es mover los datos desde la lista de campos que se ve en el cuadro de dialogo del lado derecho. Al mover los campos (arrastrar con el mouse el campo) nos percatamos que la tabla dinámica se vuelve versátil. Por ejemplo de la misma tabla dinámica hecha anteriormente moveremos el campo Tipo (solo hay que arrastrar el mouse) al área de columnas y el campo Díasem lo cambiamos al área de fila e inmediatamente la presentación de la tabla dinámica cambia de forma rápida:

pág. 11

Como podemos observar con solo cambiar los campos entre las áreas de la estructura de la tabla dinámica, la presentación también cambia respetando los valores totales. La forma o presentación depende del analista, no existe una forma genérica de presentar la información por lo que el analista deberá considerar a su juicio la forma de visualizar la tabla dinámica. La segunda forma de modificar los campos de la tabla dinámica es hacerlo directamente desde la tabla dinámica. Sin embargo, para las últimas versiones de 2016 a 2007 primero debemos asegurarnos de que la tabla dinámica nos permita arrastrar los campos en cuadricula. (Ver Anexo 1) pág. 12

Al pasar el mouse sobre las áreas de la tabla dinámica, observamos al cursor que cambia con la siguiente figura

esto nos indica que podemos mover el campo dentro de la tabla dinámica. Damos

clic sin soltar y arrastramos hasta el área dónde deseamos mover. Y obtenemos el mismo resultado.

Siguiendo el mismo ejemplo, ahora vamos a detallar la información agregando el campo del año en el área de Filas y obtenemos lo siguiente: pág. 13

Como podemos, la tabla dinámica nos presenta de forma más concisa la información de cantidades con respecto a los dos años, dándonos un total por cada año. Ahora podemos modificar la tabla si movemos el año como sigue:

En el segundo paso, observamos que se remarca de color verde obscuro una línea que indica dónde estamos moviendo el campo de “Año”, al soltar el mouse obtenemos la siguiente tabla dinámica:

pág. 14

La presentación volvió a cambiar, ahora nos muestra una tabla en la cual visualizamos los días de la semana por año, por ejemplo, los días Martes en 2012 hubo mayor facturación con respecto a 2013. Como nos podemos dar cuenta, las tablas dinámicas nos presentan la información de una manera que podemos analizar con diferentes modalidades. Ahora haremos otro movimiento del campo del “Año” que se encuentra en el área de filas y lo moveremos al área de columnas.

pág. 15

Obtenemos la siguiente tabla dinámica:

Como podemos observar, en el área de columnas, primero se encuentra el campo “ Año” y enseguida está el campo “Tipo”, sin embargo, podemos mover los campos (como lo venimos haciendo) en el cual primero sea “Tipo” y en seguida sea el campo “Año” y obtenemos:

Cada cambio que realicemos en la tabla dinámica también lo veremos los cambios en la tabla de campos, así mismo como el orden:

pág. 16

Por lo tanto, todos los cambios se pueden hacer entre diferentes áreas de la tabla dinámica ya sea de filas, columnas o incluso el área de filtros. También podemos cambiar el orden dentro de una misma área, como el ejemplo anterior, donde se observa primero el campo “Tipo” y en seguida el campo del “Año”.

6. Utilizar el área de filtro El área de filtro es una zona en la cual podemos realizar filtros para toda la tabla dinámica y este filtro es independientemente a los filtros del área de filas o columnas. Es útil cuando deseamos presentar o analizar datos específicos. En el ejemplo habíamos colocado el campo “Zona” sin embargo, puede ir cualquier campo o varios campos. Utilizaremos la siguiente tabla dinámica:

Al dar clic en el filtro del área de filtros, la tabla dinámica nos desplegará un cuadro de texto y debemos habilitar los campos para poder filtrar:

pág. 17

Una vez habilitado los campos, seleccionamos la zona que deseamos analizar o presentar en la tabla dinámica:

pág. 18

Obtenemos la siguiente tabla dinámica:

Como podemos observar la tabla dinámica con filtro del dato “Centro” únicamente muestra los datos y valores de la Zona Centro, cada cambio de campos en la tabla dinámica mostrará los datos de Centro, hasta que borremos el filtro o cambiemos el filtro por otra zona. El área de filtro también podemos agregar más campos, no tiene limitantes, por lo que es flexible en tener varios filtros para su análisis o gestión. En el mismo ejemplo agregaremos el campo “Segmento” y al dar clic en el filtro observamos los campos que contiene el campo segmento:

pág. 19

Obtenemos la siguiente tabla dinámica:

Ahora filtramos por Centro y Premium y obtenemos la siguiente tabla dinámica:

En el área de filtros podemos colocar cualquier campo que se desea evaluar o analizar de forma específica e independiente.

pág. 20

7. Filtros de filas y columnas. Los filtros de filas y columnas hacen referencia a los datos de los campos de existan en el área de filas y columnas. En el siguiente ejemplo o...