todo sobre tablas dinámicas

74
Todo sobre tablas dinámicas Ocultar filas con valor cero en tablas dinámicas. Al trabajar con tablas dinámicas puede suceder que queramos ocultar filas cuyo valor es 0 (cero). La forma de hacerlo depende de la estructura de los datos que alimentan la tabla dinámica. Si no hay valores repetidos en el área de las filas, como en este caso Seguimos los siguientes pasos: 1 – Creamos la tabla dinámica y agregamos el campo “Valor” en el área de página 2 – Seleccionamos el campo de página y abrimos el menú de configuración de campo

Upload: sabeta1

Post on 18-Jun-2015

12.608 views

Category:

Documents


3 download

TRANSCRIPT

Page 1: Todo sobre tablas dinámicas

Todo sobre tablas dinámicas

Ocultar filas con valor cero en tablas dinámicas.

Al trabajar con tablas dinámicas puede suceder que queramos ocultar filas cuyo valor es 0

(cero). La forma de hacerlo depende de la estructura de los datos que alimentan la tabla

dinámica.

Si no hay valores repetidos en el área de las filas, como en este caso

Seguimos los siguientes pasos:

1 – Creamos la tabla dinámica y agregamos el campo “Valor” en el área de página

2 – Seleccionamos el campo de página y abrimos el menú de configuración de campo

Page 2: Todo sobre tablas dinámicas

3 – En la ventanilla “ocultar elementos” marcamos el 0 y apretamos “Aceptar”. Las filas de los

elementos con valor 0 no aparecen en la tabla.

Esta técnica no funciona si hay elementos repetidos en el área de las filas, como en este caso

En este caso agregamos una columna auxiliar con esta fórmula

=--SI(SUMAR.SI($A$24:$A$35,A24,$B$24:$B$35)<>0,1)

Page 3: Todo sobre tablas dinámicas

Esta fórmula evalúa el total de cada grupo de valores para cada línea del grupo que totalice 0

da 0; en caso contrario 1.

Arrastramos el campo Auxiliar al área de página y seleccionamos 1

Las filas que totalicen 0 desaparecen.

Totales acumulados en tablas dinámicas de Excel.

Crear totales acumulados en tablas dinámicas de Excel es fácil, como mostraremos en esta

nota.

Supongamos estos datos de ventas de dos sucursales

Page 4: Todo sobre tablas dinámicas

A partir de estos datos construimos una tabla dinámica normal

Para lograr que la tabla muestre los totales acumulados, abrimos el menú de configuración de

campo, apretamos el botón Opciones, en Mostrar datos como elegimos Total en y en

Campo base elegimos Mes

Page 5: Todo sobre tablas dinámicas

La tabla dinámica muestra ahora el acumulado por mes

Para mostrar el acumulado por Sucursal tenemos que agregar este campo a la tabla. Volvamos

al total normal y agreguemos el campo Sucursal en el área de columnas

Ahora al definir Mostrar datos como Total en y Campo base Mes, veremos

Si cambiamos la base a Sucursal, veremos

Ahora los acumulados son por fila, a lo largo del mes.

Page 6: Todo sobre tablas dinámicas

También podemos arrastrar el campo Sucursal al área de filas, a la izquierda de Mes y

poniendo la base a Mes veremos el acumulado por mes de cada sucursal

Determinar la última fecha de un acontecimiento.

Dada una lista de clientes y fechas de contacto, queremos determinar cuál es la fecha más

reciente en la cual hemos contactado a cada uno de los clientes.

Podemos resolver este problema de dos maneras: con fórmulas matriciales y con tablas

dinámicas.

Supongamos esta tabla

Page 7: Todo sobre tablas dinámicas

Solución con fórmulas matriciales.

Empezamos creando una lista donde en la primer columna ponemos los nombres de los

clientes

El segundo paso consiste en definir dos nombres: clientes que contiene el rango A2:A15 y

fechas que contiene el rango B2:B15

En la celda D2 ponemos esta formula matricial

=MAX((clientes=D2)*fechas)

Como toda fórmula matricial la introducimos apretando simultáneamente Ctrl+Mayúsculas+Enter. Ahora

copiamos la fórmula a las restantes celdas del rango

Page 8: Todo sobre tablas dinámicas

Para explicar como funciona esta fórmula matricial, veamos una solución que usaría columnas auxiliares.

En la primer columna auxiliar comparamos si el cliente de la fila coincide con el cliente que estamos buscando (en

nuestro caso Cliente 1).

En la segunda columna auxiliar simplemente multiplicamos la columna de las fechas por la primer columna

auxiliar. Dado que Excel interpreta VERDADERO como el valor 1 y FALSO como 0, obtenemos fechas sólo para

el cliente 1. Ahora es evidente que la fórmula =MAX(D2:D15) nos dará la última fecha del Cliente 1.

Al usar nuestra fórmula matricial hacemos lo mismo pero evaluando cada uno de los clientes de acuerdo a la fila

en la tabla de resultados.

Esta solución tiene dos inconvenientes:

1 - si se trata de una hoja con una gran cantidad de datos, el recalculado de la hoja puede ser muy lento.

2 - Cada vez que agreguemos un cliente a nuestra base de datos, tendremos que modificar la tabla de resultados

Page 9: Todo sobre tablas dinámicas

agregando el cliente y copiando la fórmula.

Estos inconveniente se pueden superar usando tablas dinámicas en lugar de fórmulas matriciales.

Solución con tablas dinámicas.

Seleccionamos la celda A1 (o cualquier celda de la lista) y creamos una tabla dinámica

Arrastramos el campo Clientes al área de filas y el campo Fechas al área de datos

Activamos el menú de configuración de campo para el área de datos y hacemos estos cambios:

# - elegimos resumir por Máx

Page 10: Todo sobre tablas dinámicas

# elegimos el formato de fecha para los valores

Como podemos ver obtenemos el mismo resultado como con las fórmulas matriciales

Page 11: Todo sobre tablas dinámicas

Por supuesto, la fila Total General no tiene ningún sentido, por lo cual quitamos la señal de la opción Totales

generales de columnas en el menú Opciones de tabal dinámica.

Esta solución es más eficiente que la anterior. Podemos usar rangos dinámicos para que las nuevas entradas sean

procesadas automáticamente al actualizar la tabla dinámica.

Agrupar datos de tablas dinámicas de acuerdo a valores numéricos

Hasta ahora hemos visto como agrupar (y desagrupar) datos en una tabla dinámica en base a

campos de fechas o texto. Existe también la posibilidad de agrupar de acuerdo a valores

numéricos.

Volviendo a al ejemplo de las tiendas, supongamos que queremos agruparlas por el monto de

ventas en intervalos de 150000, es decir, un grupo con tiendas con ventas de 0 a 149999, otro

grupo de 150000 a 299999 y así sucesivamente.

Antes de agrupar nuestra tabla dinámica se ve así

Page 12: Todo sobre tablas dinámicas

Como sólo podemos agrupar y desagrupar por campos de filas o de columnas, agregamos el

campo Ventas (que ya aparece en el área de datos) al área de filas

Seleccionamos el campo Ventas del área de filas (en nuestro ejemplo la celda B2) y abrimos el

menú Agrupar y mostrar detalle-Agrupar

Page 13: Todo sobre tablas dinámicas

Como Excel ha detectado que el campo contiene números (y éstos no son fechas) abre

automáticamente el formulario Agrupar-Automático.

Excel detecta automáticamente los valores mínimo (Comenzar en) y máximo (Terminar en) del

rango, los cuales pueden ser cambiados de ser necesario. En nuestro caso ponemos 0 como

mínimo y 500000 como máximo; en la ventanilla Por del formulario ponemos 150000.

Apretamos Aceptar

Ahora tenemos que arrastrar el campo Ventas del área de filas y ponerlo a la izquierda del

Page 14: Todo sobre tablas dinámicas

campo Tienda. Al hacerlo obtenemos este resultado

Podemos obtener el mismo resultado creando un campo adicional en la base de datos, pero

este método es más eficiente.

Agrupar y desagrupar datos manualmente en tablas dinámicas (segunda nota)

Ayer vimos como agrupar datos en tablas dinámicas de Excel basándonos en

campos de texto.

Ahora veremos como agregar o quitar líneas a los campos agrupados. Excel

no tiene un menú para editar campos agrupados. Podemos agrupar (como

mostramos ayer) o desagrupar usando la opción Desagrupar del asistente

de tablas dinámicas.

Hay situaciones en las cuales queremos agregar o quitar un miembro a un

grupo ya existente. En esta nota mostraré las técnicas para hacerlo.

Volvamos sobre nuestro ejemplo de ayer donde hemos creado el grupo

Norte

Page 15: Todo sobre tablas dinámicas

Si queremos agregar la Tienda 12 a este grupo empezamos por seleccionar

todos los miembros del grupo y también Tienda 12 (apretando Ctrl mientras

hacemos clic con el mouse)

Abrimos el menú Agrupar y mostrar detalle del asistente de tablas

dinámicas y apretamos agrupar

Page 16: Todo sobre tablas dinámicas

Tienda 12 aparece ahora incluida en el grupo Norte

Nótese que el nombre del grupo ha cambiado al nombre por defecto,

Grupo1.

Para quitar alguno de los miembros empezamos por seleccionar todos los

miembros del grupo, menos aquel que queremos quitar (en nuestro ejemplo

Tienda 19)

Page 17: Todo sobre tablas dinámicas

Ahora volvemos a usar Agrupar y mostrar detalle-Agrupar, lo que hará que

Tienda 10 quede fuera del grupo

Podemos combinar ambas técnicas en una sola. Por ejemplo, si queremos

quitar del grupo las tiendas 13 y 16 y agregar la tienda 2, seleccionamos

todas las tiendas del grupo excepto 13 y 16 y seleccionamos también la

tienda 2. Luego usamos Agrupar y mostrar detalle-Agrupar, obteniendo el

resultado deseado en una sola operación.

Más sobre agrupar datos en tablas dinámicas de Excel

En la nota Tablas Dinámicas en Excel – Agrupar datos vimos como agrupar

datos en una tabla dinámica de Excel basándonos en un campo de fechas.

Pero esta funcionalidad no se limita sólo a fechas. Podemos agrupar datos

basándonos también en campos de texto.

Supongamos, una vez más, esta tabla que muestra las ventas de cada

tienda de una cadena

Page 18: Todo sobre tablas dinámicas

Empezamos creando la tabla dinámica

Nuestro objetivo es agrupar las tiendas por zonas: Norte, Sur, Este y Oeste.

El problema es que la tabla de datos no incluye un campo que nos permita

agrupar las tiendas por zonas. Una solución posible es agregar el campo,

pero esto no es necesario.

Page 19: Todo sobre tablas dinámicas

Empezamos por seleccionar todas las tiendas que pertenecen a la zona

Norte. No es necesario que el rango sea continuo de manera que después

de seleccionar la primer tienda apretamos Ctrl al seleccionar las restantes

En nuestro caso hemos seleccionado las tiendas 1, 13, 16, 19 y 3 que

corresponden a la zona Norte. En el asistente de Tablas Dinámicas abrimos

el menú Agrupar y mostrar detalle--Agrupar.

Page 20: Todo sobre tablas dinámicas

Excel crea un nuevo campo de filas (Tienda2) y agrupa las tiendas que

hemos seleccionado en Groupo1.

Cambiamos el nombre del grupo a Norte

Repetimos el proceso para el resto de las zonas

Page 21: Todo sobre tablas dinámicas

Ahora podemos cambiar el nombre del campo de Tienda2 a Zonas y

arrastrar el campo Tienda fuera de la tabla

Un detalle a tomar en cuenta es que Excel crea el nuevo campo sin

subtotales

Page 22: Todo sobre tablas dinámicas

Es recomendable cambiar la definición a Automático, de manera que cuando

abrimos el detalle o agregamos el campo Tienda, podamos ver el total por

zona

Actualización simultánea de tablas dinámicas en Excel.

Después de realizar cambios en la base de datos de una tabla dinámica

tenemos que apretar el botón de actualización para que los cambios se

reflejen en la tabla

Page 23: Todo sobre tablas dinámicas

Si tenemos más de una tabla dinámica en el cuaderno, tenemos que

actualizar cada una por separado. Además de la molestia de tener que hacer

varios clics existe también el riesgo de olvidar de actualizar alguna de las

tablas.

Aparentemente Excel no tiene ningún método para actualizar todas las

tablas simultáneamente, pero podemos usar el botón Actualizar Todo, que

por defecto aparece en la barra de herramientas de Datos Externos, para

esta tarea. Para agregar el botón a la barra de herramientas Tabla Dinámica

hacemos lo siguiente:

1 - pulsamos la flecha de Opciones de la barra de Tablas Dinámicas para

abrir la posibilidad Agregar o quitar botones

2 - Elegimos Tabla Dinámica y señalamos el botón Actualizar todo

Page 24: Todo sobre tablas dinámicas

Al apretar el botón se actualizarán todas las tablas dinámicas del cuaderno.

Hay que tomar en cuenta que si hay rangos externos en el cuaderno, éstos

se actualizarán.

Totales en el tope de una tabla dinámica de Excel

Cuando creamos una tabla dinámica y no cancelamos los totales por columna (con el menú Opciones de tabla),

Excel pone los totales al pie de los campos de datos de la tabla

Hay situaciones en las cuales, a diferencia del ejemplo en esta nota, tenemos muchas líneas en nuestra tabla. Por

ejemplo, supongamos que en lugar de 5 departamentos de una tienda, tenemos 100 o 200 sucursales de una red.

En esos casos es conveniente mostrar el total de la tabla en la parte superior, en lugar de forzar al destinatario del

informe a moverse hasta el final de la tabla.

Excel no tiene un método integrado para hacer esto, pero podemos lograrlo con un pequeño truco.

Como en la nota sobre el cálculo de porcentaje para subtotales, lo que haremos es agregar una columna (campo)

auxiliar.

Siguiendo con nuestro ejemplo, insertamos una columna entre Mes y Ventas (Total)

Page 25: Todo sobre tablas dinámicas

Al insertar el nuevo campo entre dos existentes no tenemos que actualizar el rango de la tabla dinámica ya

existente.

Apretamos el icono de Actualización de datos para que el nuevo campo aparezca en la lista de campos. Lo

arrastramos al área de campos de fina asegurándonos que sea el primer campo del área (a la izquierda)

El próximo paso es abrir el menú de configuración del campo, apretamos el botón Diseño y marcamos las

opciones Mostrar elementos en formulario esquemático y Mostrar subtotales en la parte superior del grupo

Apretamos Aceptar dos veces y obtenemos esta tabla

Page 26: Todo sobre tablas dinámicas

Todo lo que nos queda por hacer es deshacernos de la línea de Total General al pie de la tabla. Para esto abrimos el

menú de Opciones de la tabla y quitamos la marca de la opción Totales generales de columnas

con lo cual logramos el resultado esperado

Calcular Porcentaje de Subtotales en tablas dinámicas de Excel

Un lector me pregunta como podemos calcular porcentajes de subtotales en una tabla

dinámica de Excel.

Page 27: Todo sobre tablas dinámicas

Excel permite mostrar porcentajes del total de una tabla con facilidad, pero no tiene ningún

método incorporado para calcular los porcentajes de subtotales. Veamos a qué me refiero.

Supongamos esta lista de datos

Con facilidad podemos crear una tabla dinámica que totalice las ventas por región y agente

Luego podemos usar el menú de opciones de campo para mostrar los resultados como

porcentaje del total

Page 28: Todo sobre tablas dinámicas

Todo esto con algunos clics del Mouse!

Pero qué pasa si queremos calcular el porcentaje de cada agente sobre el total de cada

región? Para hacer esto tendremos que dar un rodeo.

Empezamos por crear un campo auxiliar en nuestra lista de datos, que llamamos "% de

Región"

Los valores de este campo los calculamos con la fórmula:

=C2/SUMAR.SI($B$2:$B$13,B2,$C$2:$C$13)

que calcula el porcentaje de cada valor sobre el total de los valores de la región. Este nuevo

campo lo arrastramos al área de datos

Page 29: Todo sobre tablas dinámicas

Para evitar que Excel totalice los subtotales y muestre un total del 400%, cancelamos la opción

Total

La vida (en Excel) es más sencilla con Tablas Dinámicas

Queremos manejar una serie de datos, para lo cual los vamos registrando

en una hoja. Luego en otra hoja del cuaderno montamos un resumen de los

datos, lo que por lo general nos llevará a armar fórmulas bastante

complicadas.

Pero la vida en Excel puede ser mucho más sencilla si sabemos explotar las

posibilidades de tablas dinámicas.

Veamos el caso, tu quieres manejar un registro de tasas de cambios del

dólar y del euro frente al peso chileno. En una planilla se van registrando

las tasas de cambio por día. El problema de tubo mi amigo Chileno, tal como

lo describe en su consulta es:

Hola Sergio te comento mi problema…en otra planilla … tengo un cuadro

[con] los promedios de cada mes, entonces cuando están llenos los campos

del mes de agosto por ejemplo se llena la casilla con el valor promedio de

agosto, y asi una serie de indicadores…

Page 30: Todo sobre tablas dinámicas

Mi idea es que exista una casilla en la cual se pueda elegir año, mes y

dia...y busque los valores segun los datos proporcionados, o sea que si elijo

en año 2006, me muestre en pantalla el promedio de enero de 2006 en su

respectiva casilla, el promedio de febrero de 2006 en su respectiva casilla

etc. y si lo cambio a 2007 haga lo mismo y asi con con "n" años mas

Esta tarea es sencilla si usamos tablas dinámicas y en esta nota

mostraremos cómo hacerlo.

En una hoja de Excel ponemos los datos de tipo de cambio del peso chileno

frente al euro y al dólar por Ejemplo en los años 2006 y 2007. El nombre de

la hoja será "BD" (base de datos)

Hemos puesto los datos del dólar (USD) y del euro (EUR) en dos columnas

contiguas para evitar tener que duplicar las líneas con las fechas. Esto nos

obligará a hacer una pequeña manipulación en nuestra tabla dinámica.

Una vez que hemos completado nuestra base de datos, armamos la tabla

dinámica con el menú Datos—Informe de tablas y gráficos dinámicos

Page 31: Todo sobre tablas dinámicas

Arrastramos el campo Fechas al área de campos de filas y los campos USD y

EUR al área de datos

Ahora empezamos a hacer nuestras manipulaciones en la tabla. Primero

hacemos clic sobre "Datos" y lo arrastramos sobre "Total"

Como ven, ha desaparecido la columna Total, que no necesitamos. También

Page 32: Todo sobre tablas dinámicas

eliminamos el total de las columnas abriendo el menú Opciones de Tabla y

quitando la marca de las opciones Totales Generales de filas y columnas.

Ahora agrupamos las filas por mes y por año con el menú Agrupar de las

tablas dinámicas

y eligiendo las opciones "meses" y "años"

En este momento nuestra tabla nos muestra la suma de las tasas de cambio

de cada mes para moneda. Para cambiar la función a "promedio", abrimos el

menú Configuración de campo y elegimos la función promedio

Page 33: Todo sobre tablas dinámicas

Hacemos lo mismo para el campo de USD. Ahora cambiamos el formato de

los números y ya tenemos nuestra tabla con los promedios por año y por

mes. Cinco minutos de trabajo y éste es el resultado

Si queremos agregar promedios anuales, abrimos el menú de configuración

de campo para "Fecha"

Page 34: Todo sobre tablas dinámicas

y marcamos "Subtotales"

Todo lo que nos queda por hacer es definir el rango de la tabla en forma

dinámica, como esta explicado en la nota del enlace. De esta manera

podemos seguir agregando datos a nuestra base de datos, sin necesidad de

redefinir el rango de la tabla dinámica.

Page 35: Todo sobre tablas dinámicas

Tablas dinámicas en lugar de Autofiltro.

Alguien me pregunta si se puede aplicar autofiltro simultáneamente a varias hojas. En sus

propias palabras:

¿se puede aplicar autofiltro a varias hojas de una sola vez? Tengo 25 hojas y

quiero filtrar en todas desde una fecha determinada. ej: desde ej 01/06/2007.

La respuesta es no, no se puede. Pero podemos usar tablas dinámicas para lograr efectos parecidos a los de

Autofiltro. Para esto usaremos tablas dinámicas con la opción "rangos de consolidación múltiples".

Supongamos un cuaderno Excel con listas de datos compuestas de fechas e importes, en tres hojas. Nuestro

objetivo en consolidar los datos en una tabla y mostrar los datos de determinadas fechas.

Construimos la tabla dinámica con rangos de consolidación múltiples

Page 37: Todo sobre tablas dinámicas

Ahora empezamos a mejorar el resultado. Eliminamos el elemento Columna arrastrándolo fuera de la tabla y

movemos el elemento Página1 a la zona de campos de columna

Reemplazamos los rótulos de la tabla por otros más significativos

Para filtrar los datos según fechas abrimos el menú de ítems del campo

Page 38: Todo sobre tablas dinámicas

Y seleccionamos los elementos deseados

Este método es muy efectivo si queremos seleccionar sólo una fecha o un rango reducido. Si tenemos muchos

elementos (fechas) en nuestras listas el método puede ser muy trabajoso.

Presupuesto de viaje con Excel

Como se puede construir con Excel un modelo sencillo pero efectivo para

programar el presupuesto de un viaje (o de cualquier otra actividad).

Los "ingredientes" de nuestro "plato" serán: tablas dinámicas, validación de

datos y rangos dinámicos definidos con nombres y la función DESREF.

También usaremos un evento para validar fechas.

La tabla dinámica funciona como un generador de reportes que nos

permitirá agrupar los gastos según los distintos conceptos que hayamos

Page 39: Todo sobre tablas dinámicas

definido (tipo de gastos como vuelos, alojamiento, excursiones, etc.; o

gastos por tramos).

Usaremos Validación de Datos para generar listas desplegables en los

distintos campos de la lista que será la base de la tabla dinámica.

Empezamos por crear la lista, las base de datos que servirá a la tabla

dinámica (en la hoja BD en nuestro ejemplo). Aquí definimos que campos

queremos que contenga la tabla. En nuestro ejemplo definimos (empezando

por la columna A): Tipo de Gasto, Fecha, Tramo, Detalle, Moneda, Suma,

Peso, Dólar, Euro.

Agregamos las últimas cuatro columnas bajo la suposición que tendremos

gastos en distintas moneda. En la columna Moneda pondremos la

denominación de la moneda en la cual realizamos el gasto (peso, dólar o

euro); en las últimas tres convertimos cada suma a su equivalente con

fórmulas que mostraremos luego.

En esta hoja hemos agregado también un evento que abre un calendario

cuando queremos introducir una fecha en alguna celda de la columna B.

Esta técnica la hemos mostrado en la nota sobre validación de fechas en

Excel.

Ahora creamos una hoja que llamamos "Parámetros" donde tenemos los

valores de tipo de gastos que servirá a la lista desplegable de la validación

de datos. Además tenemos un cuadro de cambio cruzado de monedas para

las conversiones.

Page 40: Todo sobre tablas dinámicas

En esta hoja definimos los nombres:

cambio =parametros!$C$2:$F$5

db_range =DESREF(BD!$A$1;0;0;CONTARA(BD!$A:$A);CONTARA(BD!$1:$1))

monedaH=parametros!$C$2:$F$2

monedaV=parametros!$C$2:$C$5

Tipo_de_Gasto=DESREF(parametros!$A$3;0;0;CONTARA(parametros!$A:$A)-

1;1)

Antes de crear la hoja con el reporte, introducimos algunos datos en la hoja

BD:

Ahora ya podemos crear nuestro reporte, usando una tabla dinámica que

ponemos en la hoja Reporte. Por ejemplo si queremos ver el presupuesto

por tipo de gasto:

Page 41: Todo sobre tablas dinámicas

o por tramo

de acuerdo a los campos que arrastremos a la zona de campos de fila.

Tablas Dinámicas en Excel – Gráficos

Excel nos permite crear un gráfico basado en una tabla dinámica con un solo clic. En

nuestro ejemplo

Apretamos el icono de gráficos en la barra de herramientas de tablas dinámicas y

obtenemos un gráfico en una nueva hoja

Page 42: Todo sobre tablas dinámicas

Si queremos presentar el gráfico en la misma hoja de la tabla dinámica, cambiamos su

ubicación (clic en el botón derecho del mouse) a la hoja de la tabla

Page 43: Todo sobre tablas dinámicas

El gráfico esta ligado a la tabla dinámica de manera que todo cambio en la tabla afecta

inmediatamente al gráfico, y viceversa. Como se puede ver, en el gráfico aparecen los

mismos botones de campos que aparecen en la tabla.

Por ejemplo, si cambiamos la selección de departamentos en el gráfico para mostrar sólo

los departamentos 1 y 2

al apretar Aceptar veremos los cambios también en la tabla dinámica

Page 44: Todo sobre tablas dinámicas

Los gráficos basados en tablas dinámicas son menos flexibles que los gráficos basados

en tablas de datos corrientes de Excel. Si cambiamos formatos en el gráfico original (por

ejemplo el color de una serie), al actualizar la tabla volverán a aparecer los formatos

originales. Otra limitación es que ciertos tipos de gráficos no son permitidos. Si intentan

convertir el gráfico a uno del tipo XY recibirán esta advertencia

Tampoco podemos crear un gráfico usando sólo parte de las celdas en la tabla dinámica.

Aún cuando seleccionemos sólo un rango de la tabla, Excel generará un gráfico basado en

todos los datos presentes en la tabla dinámica.

Para sobreponernos a estas limitaciones lo que tenemos que hacer, básicamente, es

quebrar el vínculo entre el gráfico y la tabla dinámica. Esto lo podemos hacer de varias

maneras:

1 – Seleccionar la tabla y usar Copiar—Pegado Especial—Valores para copiar los datos en

formar estática en alguna otra ubicación. Luego a partir de estos datos generar el gráfico

deseado. Si copiamos sólo una parte de la tabla, no hace falta usar Pegado Especial—

Valores. También con Pegar (Ctrl+V) obtenemos datos estáticos.

Page 45: Todo sobre tablas dinámicas

2- Seleccionar toda la tabla, copiar (Ctrl+C) y luego Pegado Especial—Valores.

3 – Copiar el dato seleccionado de una tabla dinámica a un gráfico sin datos creado

previamente.

Tablas Dinámicas - Función IMPORTARDATOSDINAMICOS (GetPivotData)

Si queremos crear una referencia a una de las celdas de la tabla dinámica fuera de ella, Excel utiliza

automáticamente la función IMPORTARDATOSDINAMICOS (GETPIVOTDATA en la versión inglesa).

Por ejemplo, si queremos crear una referencia al total de ventas del 2005 en la celda A12

El objetivo de IMPORTARDATOSDINAMICOS (GetPivotData) es extraer datos de la tabla dinámica, basados en

los argumentos de la función, cuya sintaxis es

IMPORTARDATOSDINAMICOS(campo_datos,tabla_dinámica,campo1,elemento1,campo2,elemento2,…)

Por lo general queremos crear la referencia a la celda sin el uso de esta función. Existen dos maneras de hacer esto:

1 - Crear la referencia manualmente, es decir seleccionar A12 y escribir "=B10" en la barra de las fórmulas

2 – Cancelar la opción "generar getpivotdata". Esta opción es poco conocida. Existe un icono en que nos permite

activar o desactivar la opción. Para instalar el icono hacemos lo siguiente:

a. Abrimos el menú Herramientas—Personalizar

b. En la pestaña Comandos elegimos Datos. Al final de la lista aparece el icono Generar Getpivodata

Page 46: Todo sobre tablas dinámicas

c. Arrastramos el icono a alguna de las barras de herramientas (lo más lógico es instalarlo en la barra de Tablas

Dinámicas)

Este icono funciona como un interruptor. Con un clic desactivamos la opción y al crear una referencia veremos

sólo la dirección de la celda

Un detalle a notar es que referencias directas heredan el formato de la celda de referencia; referencias con

Getpivotdata reciben el formato "General".

Tablas Dinámicas en Excel – Manejo de campos y rangos

En las datos anteriores vimos distintas funcionalidades de las tablas

dinámicas en Excel:

Page 47: Todo sobre tablas dinámicas

- agrupar datos

- agregar campos y elementos con fórmulas

- cambiar la presentación de los datos sin fórmulas

Cuando agregamos líneas o columnas a la base de datos, debemos

modificar la información para que los nuevos datos sean incluidos en la

tabla dinámica. Para esto activamos el asistente de tablas dinámicas

Apretamos el botón "Atrás"

Seleccionamos el nuevo rango y apretamos "Finalizar"

Como podemos ver, la lista de campos y los datos se han actualizado.

Page 48: Todo sobre tablas dinámicas

Podemos ahorrarnos estas maniobras si definimos un rango dinámico para

la lista de datos desde el principio.

Para presentar una comparación, mes por mes, de los dos años,

arrastramos el campo "Año" al área de campos de columna

El total de las filas es innecesario y por lo tanto lo eliminamos quitando la

marca de la casilla "Totales de fila" en el diálogo de opciones de tabla

Page 49: Todo sobre tablas dinámicas

Rehacemos ahora la tabla colocando el campo Año en el área de página,

Departamento en el área de filas y Mes en el área de columnas

En esta situación, si agregamos Unidades al área de datos, Excel ubica los

nuevos datos en una línea por debajo del campo Ventas

Si queremos que los datos de Ventas y Unidades aparezcan unos al lado de

los otros, arrastramos Datos a la zona de columnas, a la izquierda de Mes

Page 50: Todo sobre tablas dinámicas

Alternativamente podemos arrastrar Mes por debajo de Datos y obtener

esta tabla

Tablas Dinámicas en Excel – Cálculos sin fórmulas

En los datos sobre campos y elementos calculados investigamos la opción Fórmulas del asistente de Tablas Dinámicas.

En esta entrada veremos otras posibilidades de presentación de datos en las tablas dinámicas de Excel.

Volvamos al ejemplo de las notas anteriores, que hemos adaptado al tema que vamos a tratar

Page 51: Todo sobre tablas dinámicas

Si queremos presentar las ventas de cada departamento como porcentaje del total hacemos lo siguiente:

1 - Seleccionamos alguna de las celdas de datos de la tabla, abrimos el menú de tablas dinámicas y activamos la opción "configuración de campo"

2 - Pulsamos el botón Opciones, en la ventanilla "Mostrar datos como" elegimos "% de columna" y apretamos aceptar

Page 52: Todo sobre tablas dinámicas

Excel calcula el porcentaje de cada departamento en forma automática. Si queremos exhibir el campo de las ventas junto con el porcentaje de cada departamento, hacemos lo siguiente:

1 – Agregamos nuevamente el campo de ventas al área de datos

2 - Seleccionamos alguna de las celdas del nuevo campo y volvemos a aplicar el método anterior

Por supuesto, existe también la opción "% de fila". Para dar un

Page 53: Todo sobre tablas dinámicas

ejemplo modificamos la tabla dinámica poniendo el campo Departamento en el campo de columnas

Volvemos sobre el método anterior pero elegimos "% de la fila"

Y obtenemos esta tabla

Finalmente podemos exhibir todos los datos como porcentaje del total, eligiendo la opción "% del Total"

Page 54: Todo sobre tablas dinámicas

Otras posibilidades son:

Diferencia de

Al elegir esta opción se abre una ventanilla donde debemos elegir el campo y el elemento para realizar la comparación. En nuestro caso queremos comparar ventas de cada mes en relación a enero

Page 55: Todo sobre tablas dinámicas

El resultado no es de lo más presentable, pero esta opción puede ser muy práctica cuando trabajamos con tablas de gran tamaño.

Otra opción práctica es "Total en" (que en inglés se llama tiene el nombre más apropiado Running Total). Aquí aplicamos la técnica de poner dos veces el mismo campo en el área de datos

Y así obtenemos una tabla con las ventas y con el saldo acumulado por mes

Tablas Dinámicas en Excel – Eliminar elementos caducos

Page 56: Todo sobre tablas dinámicas

Si examinan mi entrada de ayer sobre campos y elementos calculados en

tablas dinámicas, notarán que ciertos elementos siguen apareciendo en las

listas desplegables a pesar de que los hemos borrado de la base de datos.

Estos elementos persisten aún después de haber actualizado la tabla.

En el ejemplo de la nota anterior

los meses de mayo y junio siguen apareciendo a pesar de haber sido

borrados de la lista de datos.

Para que los elementos que han sido borrados de la lista de datos (los

elementos "caducos") desaparezcan hacemos lo siguiente:

1 - Eliminamos los elementos calculados que hubiéramos definido (en

nuestro caso Bimestre 1, Bimestre 2 y Total). Para eso abrimos el menú

Formulas del asistente de tablas dinámicas, elegimos el elemento calculado

que queremos borrar y pulsamos el botón Eliminar.

Page 57: Todo sobre tablas dinámicas

2 - Quitamos el campo que contiene los elementos arrastrándolo fuera del

rango de la tabla dinámica

3 - Actualizamos la tabla dinámica con el botón de actualizar

4 - Agregamos de nuevo el campo a la tabla. Sólo los elementos existentes

en la base de datos aparecen ahora en la lista desplegable de elementos del

campo.

Page 58: Todo sobre tablas dinámicas

Tablas Dinámicas en Excel – Campos y Elementos calculados.

Las tablas dinámicas son un tipo de objeto que "reside" en la hoja de cálculos pero el rango

ocupado por la tabla tiene un comportamiento distinto a los rangos normales de Excel. El

rango ocupado por la tabla dinámica no puede ser modificado directamente en la hoja. Por

ejemplo, no podemos agregar líneas o columnas, o fórmulas en las distintas celdas.

Cuando queremos hacer este tipo de modificaciones en una tabla dinámica tenemos dos

posibilidades:

1 - seleccionar la tabla y copiarla a otra ubicación (ya sea en la misma hoja, en otra o en otro

cuaderno) usando Edición – Pegado Especial – Valores. Este método tiene la ventaja de dar

mucha flexibilidad en el manejo de los datos (formatos, fórmulas, etc); pero tiene la gran

desventaja de romper el vínculo dinámico entre la tabla y los datos originales.

2 – Crear campos y/o elementos calculados. De esto nos ocuparemos en esta nota.

Basándonos en nuestra tabla de datos hemos creado esta tabla dinámica

Page 59: Todo sobre tablas dinámicas

En el área de Página hemos puesto los meses, de manera que podemos ver los datos de cada

mes con un clic.

Ahora digamos que queremos calcular el precio promedio de las unidades vendidas (ventas /

unidades = precio promedio) y también agrupar los meses por bimestres (enero + febrero =

bimestre 1).

El precio promedio consiste en crear un nuevo campo, dividiendo los elementos de un campo

por los de otro. Esto es un campo calculado. Los pasos a dar son los siguientes:

1 – cliqueamos en algún lugar de la tabla y en el asistente de Tablas Dinámicas activamos

Fórmulas – Campos Calculados

2 - En la ventanilla "nombre" anotamos "Precio Promedio" y en la ventanilla "Fórmula"

=Ventas/Unidades (con la ventanilla activada, hacemos doble clic a Ventas, luego anotamos el

símbolo "/" y luego doble clic a Unidades)

Page 60: Todo sobre tablas dinámicas

3 - Después de pulsar "Aceptar" y realizar algunos ajustes al formato, obtenemos esta tabla

dinámica

Como ven, hemos agregado un nuevo campo a la tabla: "Precio Promedio".

Para demostrar el uso de elementos calculados, reorganizamos nuestra tabla dinámica

poniendo los meses como campos de fila y los departamentos en el área de Página

Para crear el elemento calculado "Bimestre 1" procedemos de la siguiente manera:

Page 61: Todo sobre tablas dinámicas

1 - Seleccionamos la celda A4 (donde aparece "Mes", el nombre del campo con cuyos

elementos crearemos un elemento calculado), en el asistente de Tablas Dinámicas activamos

el menú Fórmulas – Elementos Calculados

2 - En el diálogo que se abre seleccionamos "Mes" en la ventanilla "Campos"; en la ventanilla

"elementos" vemos los meses (los elementos del campo). En la ventanilla nombre escribimos

Bimestre 1; luego seleccionamos la ventanilla Fórmula y anotamos =enero+febrero (lo que se

hace con un doble clic sobre el nombre del elemento). Luego apretamos Enter y volvemos a

seleccionar el campo Mes

3 - Repetimos la operación para el Bimestre 2 (marzo+abril) y pulsamos "Aceptar".

Excel tiene dos problemas relacionados con los elementos calculados:

1 - Son agregados automáticamente al final de la lista de elementos del campo

2 – El total general incluye los elementos calculados, por lo tanto da como resultado el doble

de lo que debería ser.

Page 62: Todo sobre tablas dinámicas

El primer problema lo resolvemos moviendo los elementos a la posición deseada. Una de las

formas de hacer esto es copiar Bimestre 1 y pegarlo inmediatamente debajo de "febrero" (o

escribir manualmente Bimestre 1). Excel reorganiza la tabla automáticamente

El problema del total general lo podemos tratar de dos maneras. Una es eliminar el total

general para las columnas en Opciones de Tablas.

La otra es crear un nuevo elemento, Total, que suma Bimestre 1 y Bimestre 2

Obteniendo

Page 63: Todo sobre tablas dinámicas

En resumen:

Un campo calculado es un nuevo campo creado realizando operaciones con campos existentes.

Un elemento calculado es un nuevo elemento en un campo, creado con los elementos existentes del campo.

Para eliminar los campos o elementos calculados tenemos que usar el asistente de Tablas Dinámicas. Por ejemplo,

para eliminar el campo Precio Promedio abrimos el diálogo de Fórmulas en Campos Calculados

En la ventanilla Nombre elegimos el campo que queremos eliminar y luego pulsamos el botón Eliminar.

Tablas Dinámicas en Excel – Agrupar datos

Page 64: Todo sobre tablas dinámicas

El manejo de pequeñas bases de datos en Excel usando tablas dinámicas

(pivot tables). En estas notas cubría aspectos funcionales de las tablas

dinámicas como actualización de datos y referencias dinámicas

En esta nota veremos otras funcionalidades de las tablas dinámicas que

ayudan a hacer el trabajo con ellas más eficiente.

Nuestra tabla tiene 725 líneas (181 días * 4 departamentos + 1 línea para

los encabezamientos).

Con facilidad creamos un informe que nos muestra el total de ventas del

medio año por departamento

El campo Fechas lo hemos puesto en el área de páginas de manera que

podemos elegir una fecha determinado y ver las ventas por departamento

El menú de Tablas Dinámicas ofrece la posibilidad de agrupar datos ligados

a campos que Excel puede reconocer como fechas. Para usar esta

funcionalidad el campo de fechas debe encontrarse en el área de las filas o

Page 65: Todo sobre tablas dinámicas

de las columnas. Por lo tanto movemos el campo Fechas al área de

columnas

Ahora seleccionamos el rótulo Fecha, abrimos el menú de tablas dinámicas

y elegimos la opción "agrupar y mostrar detalle"

Al elegir esta opción se abre un diálogo donde podemos definir la forma de

agrupar los datos. Las opciones van desde segundos a años. También

podemos definir las fechas de comienzo y fin.

Page 66: Todo sobre tablas dinámicas

Por ejemplo, si elegimos "trimestres" obtenemos

También podemos elegir más de una opción simultáneamente, por ejemplo

trimestre y mes

Con este resultado

Como habrán notado, no existe la opción de agrupar por semanas. Pero

Excel nos permite hacer esto, si seleccionamos "días" y en la ventanilla

"número de días" ponemos "7"

Page 67: Todo sobre tablas dinámicas

El resultado es

Nuestro ejemplo tiene una limitación potencial, ya que el número de

columnas de una hoja de Excel es 256 y el número de días del año es 365 (o

366 si es bisiesto).

Es decir que si tuviéramos las ventas de todo el año, día por día, no

podríamos poner el campo Fecha en el área de columnas, para luego

agruparlo.

Lo que podemos hacer en este caso, es poner el campo Fecha en el área de

filas,

Page 68: Todo sobre tablas dinámicas

agrupar, por ejemplo por mes

y luego arrastrar los campos agrupados al área de columnas

Page 69: Todo sobre tablas dinámicas

Consolidación de datos con Tablas Dinámicas (Pivot Tables).

En anteriores entradas ya he tratado el tema de consolidación de datos en Excel:

1 – Consolidar Datos con Excel

2 – Consolidar datos con Subtotales

3 - Comparar listas con consolidación de datos

Otro método de consolidar datos en Excel es usando tablas dinámicas (pivot tables).

Consideremos el siguiente. Tenemos un cuaderno Excel con una hoja por cada agente de

ventas. En cada una de estas hojas anotamos las ventas por mes y por producto de cada

agente.

Para consolidar las ventas de todos los agentes (en nuestro caso habrá sólo dos) en una sola

hoja de cálculo usando tablas dinámicas, procedemos de la siguiente manera:

1 – Abrimos una hoja en blanco (en nuestro ejemplo será "consolidado") y activamos el menú

Datos--->Informe de Gráficos y Tablas Dinámicos. En el diálogo que se abre elegimos la opción

"rangos de consolidación múltiples"

Page 70: Todo sobre tablas dinámicas

2 – En el siguiente paso debemos instruir a Excel cuantos campos de página queremos. Para

nuestro ejemplo elegimos "crear un solo campo de página"

3 – En el tercer paso debemos informar a Excel dónde se encuentran los rangos que contienen

los datos. Hacemos esto apunto a los rangos en las hojas adecuadas (agente1 y agente2) y

apretando el botón "agregar"

4 – Finalmente debemos decirle a Excel dónde ubicar la tabla dinámica (que consolida los

Page 71: Todo sobre tablas dinámicas

datos)- En nuestro ejemplo la ubicamos en la celda A6

Apretamos "finalizar" y obtenemos la tabla dinámica.

Esta tabla muestra los totales para cada producto y por cada mes de todos los agentes de

ventas. Si queremos ver los datos de un agente en particular apretamos el botón "Página 1" y

elegimos entre "elemento 1" (agente 1) y "elemento 2" (agente 2).

Dado que los encabezamientos de las columnas tienen un formato de fecha, podemos agrupar

los datos en trimestres en forma automática. Para hacer esto damos los siguientes pasos:

1 – Hacemos clic con el botón derecho del mouse en el encabezamiento del campo "columna",

y apretamos "agrupar"

Page 72: Todo sobre tablas dinámicas

2 – Seleccionamos "trimestres" y cancelamos la selección de "meses" (con un clic del mouse).

3 – Apretamos "aceptar" y la tabla se convierte en un reporte trimestral