unidad 22: tablas dinámicas material 2010* · 22.4 segmentación (solo en excel 2010) ... botón ^...

© 2010 Excel with Business 1 Unidad 22: TablasDinámicas

Unidad 22: Tablas Dinámicas*material 2010*

22.0 Introducción:

Esta unidad podría haberse llamado “resumir los datos con rapidez”. Excel ofrece una

herramienta poderosa para crear resúmenes tabulares de datos y cambiar el diseño y los

detalles del resumen rápidamente. Las Tablas Dinámicas tienen mucha funcionalidad

detallada y se puede ver como un uso de Excel muy avanzado – con unos pocos pasos

esenciales usted puede llevar a cabo muchas cosas y esta unidad enseña eso.

Esta es un área donde se ha provisto una gran cantidad de funcionalidades extras en la

versión de 2010 – vea el apartado 22.6.

22.1 ¿Qué son las tablas dinámicas?

Las Tablas Dinámicas resumen los datos de una base de datos – es decir, datos que

simplemente están en formato de tabla en donde la primera fila contiene un título y las

otras contienen ya sean categorías o valores.

La forma en que se hayan resumido los datos es flexible, pero en general la tabla de datos

que resulta de una Tabla Dinámica contiene valores resumidos sobre alguna de las

categorías en la base de datos. Así que, por ejemplo, en el conjunto de datos de arriba se

puede producir la siguiente Tabla Dinámica con rapidez:

TÍTULOS

VALORES

CA

TEG

OR

ÍAS


Este tipo de resumen podría crearse con la función SUMAR.SI (tratada en la Unidad 21:

Resumir los Datos), pero las ventajas de usar las Tablas Dinámicas aquí son:

Velocidad: La tabla superior se creó, a partir de los datos, con siete fáciles clics de

ratón.

Facilidad: La tabla superior se creó sin usar ninguna función simple o fórmula de

Excel, sólo esos siete fáciles clics con el ratón.

Flexibilidad: Por ejemplo, con un par de clics más se pueden cambiar los datos que

aparecen en los gastos nominales o reducir el número de años a mostrar.

Acceso a datos dinámicos: Las Tablas Dinámicas pueden vincularse directamente a

fuentes de datos externos tales como: una base de datos o un sistema contable y los

resultados se pueden actualizar automáticamente.

22.2 Crear tablas dinámicas: una receta:

Configure su fuente de datos:

Su fuente de datos necesita tener el formato de una base de datos, como se ha descrito

antes, esto significa que tiene que haber una parte superior, es decir, la fila del título de la

que desciendan las columnas, ya sea con categorías o con valores. Para configurar la Tabla

Dinámica sólo debe seleccionar toda la base de datos y hacer clic en el icono de: Tabla

Dinámica en la Cinta de Opciones1:

1 Si la tabla de datos está configurada sin filas o columnas en blanco, entonces usted debería ser capaz de seleccionar cualquier celda de la tabla y Excel "adivinará" el área con la que desea trabajar – chequee que esto haya sido adivinado correctamente!


Aparecerá una ventana con el menú que le permitirá confirmar la selección de la base de

datos y ajustar las otras opciones – usted ya ha configurado la fuente de datos, así que haga

clic en Aceptar:

En este momento, si una de las columnas de las fuentes de datos no tiene el título, la Tabla

Dinámica no se podrá crear y aparecerá un mensaje de error:

Si la columna a la que le falta el título es importante para usted (es decir, una que usted

quiere usar ya sea como categoría o como valor en su Tabla Dinámica), entonces asegúrese

que esté titulada razonablemente. Si, por el contrario, no piensa utilizar esa columna, puede

borrar toda la columna de la fuente de datos o introducir un texto cualquiera como título –

los dos servirán – para que se ponga en marcha con éxito la Tabla Dinámica; también es

buena idea asegurarse de que los títulos sean únicos, si hubiese duplicados, la Tabla

Dinámica se pondrá en marcha pero los títulos harán que usted se confunda).


Seleccione los datos que quiere que muestre:

Al seguir los pasos de arriba usted verá una pantalla que tiene un aspecto semejante a esto:

A la izquierda de su base de datos hay un cuadro que es su Tabla Dinámica vacía. A la

derecha, hay un cuadro de control llamado “Lista de campos de la tabla dinámica” que le

permite seleccionar qué datos de su base de datos quiere resumir. (Si no ve el cuadro de

control, haga clic con el botón izquierdo del ratón en la Tabla Dinámica a la izquierda – y

éste aparecerá).

Usted necesita seleccionar los valores que quiera añadir y las categorías que quiere resumir.

Los datos en el ejemplo registran el gasto del gobierno del Reino Unido por año y por

funciones. Si usted sólo quiere resumir el total de gastos (términos reales) por año, Necesita

seleccionar “Año (Year)” y “Gastos (Expenditure) (£bn, real)” en el cuadro de control:


Al hacer esto, comienza a crearse la Tabla Dinámica a su izquierda.

Cambio del diseño:

Los pasos anteriores han creado una Tabla Dinámica que se asemejará a esta:


El último paso del proceso es cambiar el diseño de la Tabla según usted lo desee. Esto se

puede realizar con facilidad mediante el cuadro de control de la derecha. Por ejemplo,

arrastrando el campo “Year” del cuadro de las “RowLabels” (Etiquetas de las filas), que

están abajo a la derecha, al cuadro vacío llamado “ColumnLabels” (Etiquetas de las

Columnas), lo que hará que aparezca como columna la categoría Year (Año) en vez de

aparecer como el título de la fila y la tabla figurará en la página:

Resumen de la receta:

Para resumir el proceso anterior:

Asegúrese que la fuente de datos tengan el formato de una base de datos.

Seleccione toda la base de datos, haga clic en el icono “Tablas Dinámicas” de la “Cinta

de Opciones” y luego, clic en “aceptar” en el siguiente cuadro del menú.

Marque las casillas en el campo del cuadro de control de la lista de la “Tabla Dinámica”

para seleccionar los datos que desea incluir en ésta (ya sea para resumirla desglosada

por “valores” o por “categorías”).

Vuelva a usar el cuadro de control de la “Lista del Campo” de la “Tabla Dinámica” para

arrastrar los campos, de modo que tenga el aspecto que usted desee - ¡una buena

manera de hacer esto es con la actitud de “pruebo aunque me equivoque”!

22.3 Unos pocos consejos más y errores comunes:

Cambiar dónde se inserta la Tabla Dinámica:

En el ejemplo anterior, la Tabla Dinámica se creó en una nueva hoja de cálculo propia.

Puede que haya notado una opción en el cuadro del menú que aparece a la vez que hace

clic en el icono de la Tabla Dinámica:


:

Si usted selecciona “Hoja de cálculo existente” aquí, eso le permitirá crear la Tabla Dinámica

en una ubicación de su elección dentro de la hoja de cálculo existente.

Tiene que tener cuidado de que la Tabla Dinámica no se monte sobre su trabajo existente –

el tamaño y la forma cambiará según las categorías que haya introducido, así que tiene que

dejar espacio para ello. – Le recomendamos que use una hoja de cálculo aparte para la

Tabla Dinámica a menos que tenga una razón de peso para no hacerlo.

No puedo ver la lista del Campo de la Tabla Dinámica:

Este cuadro de control se puede esconder. Si no puede verlo cuando haga clic en el botón

izquierdo del ratón en la Tabla Dinámica, haga clic en el botón derecho del ratón sobre la

tabla y le saldrá un menú:


Y seleccione “Mostrar la Lista de Campos”.

Filtrar la tabla completa:

Una de las opciones de la lista de campos de Tablas Dinámicas le permite filtrar todos los

resultados de la Tabla Dinámica por categorías. Esta es la opción de Filtro de Informe, la

cual podemos observar encerrada en un círculo rojo en el siguiente cuadro:

Su utilidad se puede ilustrar mejor con un ejemplo. Si en el caso de arriba, el campo

“Función” se añade al informe y el campo lo arrastra al cuadro del Filtro del Informes, se

añadirá un menú desplegable a la Tabla Dinámica que selecciona sólo los gastos

(expenditure) en una función particular en los resultados de la Tabla Dinámica:


Tenga en cuenta que la Tabla Dinámica resultante sumará el valor de la categoría del filtro

seleccionado (en el ejemplo:‘4. EconomicAffairs’ – Asuntos económicos -) ahora, solo.

22.4 Segmentación (solo en Excel 2010)

Una Segmentación es un objeto flotante que contiene botones para filtrar una Tabla

dinámica por todos los valores de un campo en particular. Debajo hemos seleccionado una

celda en nuestra Tabla dinámica y hemos elegido la Segmentación del grupo Filtro de la

pestaña Insertar. Después hemos elegido el campo País (‘Country’) de la lista de campos

disponibles en los datos de nuestra Tabla dinámica. Esto crea una Segmentación flotante

con un conjunto de botones de países. Hacer clic en un botón filtra nuestra Tabla dinámica

para mostrar los valores para ese país solamente.

Hasta ahora, esto no parece un gran paso adelante, usted podría añadir el campo país como

un filtro de informe para hacer prácticamente lo mismo sin preocuparse por la nueva

herramienta Segmentación. No obstante, las Segmentaciones son sobre todo para hacer

más fácil interactuar con Tablas dinámicas y que sea más fácil ver lo que la Tabla dinámica le

está mostrando. Si usted selecciona un país, entonces el filtro de informe mostrará el

nombre del país, pero si usted selecciona varios países, entonces solo mostrará “Múltiples

objetos”, haciendo imposible ver con una mirada lo que la Tabla dinámica muestra.

Aquí hemos movido nuestra Segmentación de país para que se encuentre debajo de nuestra

Tabla dinámica, y hemos utilizado la pestaña contextual de Herramientas de Segmentación

para cambiar el número de columnas y ajustarse mejor al espacio. Después hemos utilizado

Control+Clic para seleccionar varios países. En contraste con el filtro de informe, la

segmentación muestra claramente qué países están seleccionados:


Se pueden seleccionar múltiples elementos en la Segmentación usando técnicas estándar de

Windows, como Control+Clic para alternar la selección de elementos individuales,

Mayúsculas+Clic para seleccionar un bloque y Control+arrastrar con el ratón. La apariencia

de la Segmentación se puede cambiar utilizando los estilos diferentes para destacar campos

seleccionados y campos sin datos.

La mayor visibilidad y la facilidad de uso del filtrado de una Tabla dinámica con una

segmentación son útiles, pero quizá no como para tirar cohetes. No obstante, se vuelve más

impresionante cuando usamos más de una segmentación o adjuntamos segmentaciones

más de una Tabla dinámica. Aquí hemos establecido tres segmentaciones por país, fecha de

pedido y nombre de producto (‘Country’, ‘OrderDate’ y ‘ProductName’). Mientras los

elementos se seleccionan en una segmentación, las otras segmentaciones cambian para

mostrar al final de la lista elementos para los que no hay ahora datos:

Finalmente, veremos cómo adjuntar una segmentación a más de una Tabla dinámica. Aquí

hemos creado tres Tablas dinámicas separadas, todas basadas en los datos de nuestras


facturas Northwind. Tenemos una “tabla liguera” de las mejores 10 ventas por país, una

tabla mostrando las ventas por vendedor, y una segunda tabla que muestra las ventas por

periodos de fechas de pedidos. Después hemos añadido una segmentación según el nombre

del producto (‘ProductName’).

Para conectar esta segmentación a todas nuestras Tablas dinámicas, hacemos clic en el

botón “Conexiones de tablas dinámicas” en la pestaña contextual de Herramientas de

segmentación. Esto nos permite seleccionar a qué Tablas dinámicas de este libro queremos

conectarnos. Habiéndonos conectado a las tres Tablas dinámicas requeridas, cambiar los

objetos seleccionados en la segmentación aplicará el filtro a todas las Tablas dinámicas

conectadas. Esto significa que podemos crear una presentación interactiva de nuestros

datos sin utilizar una sola fórmula o línea de código:

22.5 Repetir encabezamientos (solo en Excel 2010)

Aunque PowerPivot y las Segmentaciones fueron las principales mejoras del 2010 en este

área, hay otra mejora que vale la pena estudiar. Para cualquiera que utilice Tablas dinámicas

para organizar datos para su procesamiento y formateado posterior, esto podría ahorrar

mucho tiempo. Cuando usted incluye varios campos en el área de columnas de una Tabla

dinámica, las columnas más a la izquierda solamente mostrarán el encabezamiento en la

primera fila de cada sección. Un ejemplo hará esto más claro:


Aquí hemos incluido tanto el país como el nombre del cliente como etiquetas de columna.

Los valores de cada cliente se agrupan dentro del país, pero el nombre del país solamente se

incluye en la primera fila de cada sección. En Excel 2010 hay una nueva opción en el

desplegable Diseño de informe de la pestaña Herramientas de Tablas dinámicas. Repetir

todas las etiquetas de elementos incluirá el nombre del país en cada fila como se muestra

debajo:


22.6 PowerPivots (Excel 2010)

PowerPivot es una extensión gratuita para Excel, diseñada para extender sus capacidades de

Inteligencia de negocios. Debido a que incorpora un conjunto de características de gestión

de base de datos de forma diferente a trabajar con fórmulas de Excel, sospechamos que

PowerPivots podría convertirse en una de las mejoras más radicales jamás introducidas en

Excel.

Datos

Una de las afirmaciones de Microsoft acerca de PowerPivot es que puede afrontar

alegremente millones (o incluso cientos de millones) de registros, por lo que para hacer una

prueba justa, haremos un poco de trampa y crearemos un par de millones de facturas

usando un método cartesiano, es decir, uno que incluya dos tablas no unidas. El efecto de

esto es repetir cada fila en una tabla por el número de filas de la otra tabla. Así que tenemos

2157 datos de pedidos y otra tabla con 1001 filas, lo que nos da 2157*1001=2.159.157 filas.

Convirtiendo esto en una consulta para “hacer tablas” tenemos más de dos millones de

detalles de pedidos para leer en nuestra tabla PowerPivot (¡aunque hay 1001 copias de cada

una!).

Una vez descargada de http://www.powerpivot.com/ e instalada, la extensión PowerPivot

añade una pestaña PowerPivot en la Cinta de Excel. Desde esta pestaña, haga clic en el

botón de la ventana de PowerPivot para ejecutar PowerPivot:

Ventana de PowerPivot

http://www.powerpivot.com/


En la pestaña Inicio de la ventana de PowerPivot hay una sección de “Obtener datos

externos” que incluye botones para acceder a los datos de diversas fuentes y un botón para

Refrescar. El botón “Desde base de datos” muestra opciones para el servidor SQL, Access y

AnalysisServices. Hemos escogido Access y luego utilizado el botón “Buscar” para localizar la

base de datos con nuestros dos millones y pico de registros.

En nuestro caso, ya tenemos una tabla idónea para proporcionar los registros con los que

queremos trabajar en PowerPivot, así que podemos utilizar la opción para seleccionar

nuestra tabla o consulta, como alternativa podemos escribir una consulta desde dentro de

PowerPivot para recuperar los datos que queremos.

Podemos elegir múltiples tablas, pedir a PowerPivot que recupere Tablas relacionadas y

Previsualizar y filtrar cada tabla:


La opción de filtro muestra una vista de los datos similar a la de una tabla de Excel 2010, y

con las mismas opciones de filtro avanzado a las que se puede acceder desde los menús

desplegables encima del encabezado de cada columna:

Una vez que tenga los datos que desea analizar, puede pulsar el botón “Finalizar”. Verá una

pantalla que muestra el progreso de la importación de su tabla o tablas de datos en

PowerPivot:

Una vez que se hayan importado los datos, cada tabla se mostrará como una “hoja”

separada dentro de la ventana de PowerPivot. De nuevo, los datos se muestran como una

tabla de Excel 2010 con encabezados de columna como filtro. Las opciones en la pestaña

Inicio de la Cinta de PowerPivot permiten establecer el formato de columnas individuales:

La ventana de PowerPivot funciona casi como su propia aplicación de base de datos, o como

una Microsoft Query mucho más extendida. Los datos se pueden importar de una gran


variedad de fuentes, incluyendo archivos de texto y hojas de cálculo de Excel, al igual que de

productos de bases de datos estándar. La pestaña de diseño de PowerPivot incluye

herramientas para Crear y administrar relaciones entre las diferentes tablas.

Informes

La fase final es transformar su colección de millones de filas de datos en un conjunto de

gráficos o informes con sentido. Desde la pestaña Inicio de la ventana de PowerPivot,

simplemente haga clic en la sección desplegable del botón de Tabla dinámica en el grupo

Informes para ver la gama de informes y gráficos disponibles:

Nosotros vamos a elegir “Cuatro gráficos”. Después podemos decidir si colocarlos en una

nueva hoja de cálculo o en la ya existente. Para nuestra opción de “Cuatro gráficos” se

crearán cuatro hojas adicionales, cada una con los datos de uno de los gráficos.

Inicialmente, usted verá cuatro Gráficos dinámicos en blanco. Simplemente haga clic en

cada gráfico y seleccione los campos que quiere que el gráfico represente para crear su

conjunto de gráficos. Después puede añadir una Segmentación arrastrando el campo de


criterios al área vertical u horizontal, y automáticamente se conectará a las cuatro Tablas

dinámicas que tienen los datos de su gráfico:

unidad 22: tablas dinámicas material 2010* · 22.4 segmentación (solo en excel 2010) ... botón ^...

Documents