tablas dinamicas en excel 2007 -2010
TRANSCRIPT
Empleando las mismas bases de datos, con el presente curso, aplico las variaciones que
surgen en los pasos de ejecución respecto a las versiones 2007- 2010 de Excel para ejecutar
Tablas Dinámicas.
Todo el desarrollo se ejecuta mediante la representación con imagen en pantalla de cada
paso, haciendo mas sencilla su interpretación.
Gonzalo H. Fernandez
Índice
1- Que es una tabla Dinámica
2- Crear una Tabla Dinámica - pasos de ejecución
3- Aplicar filtros a la Tabla dinámica
4- Cambio de diseño de la Tabla
5- Actualizar datos en la Tabla Dinámica
6- Aplicación de Filtros
7- Graficar una Tabla dinámica
8- Aplicación de tabla dinámica sobre una base de datos mas amplia
9- Obtención de Subtotales
10- Aplicar filtros sobre la tabla
11- Resumir en porcentajes los valores de la Tabla dinámica
12- Obtener Promedios
13- Obtener valores máximos
14- Cambiar diseño de la Tabla
15- Graficar Tabla
16- Ordenar la Tabla de acuerdo a los valores
17- Tabla dinámica conformada con base de datos incluidas en distintas hojas de calculo.
18- Ejecución de Filtrado de la Tabla
19- Graficar la Tabla
20- Porcentaje de incidencia sobre total de columna
Tablas Dinámicas
en Excel 2007 -2010
Partiendo de una base de datos confeccionada, tomando como ejemplo la circulacion de
vehiculos a traves de una estacion de peaje, se trata de aplicar sobre la misma un principio
de ordenamiento a traves de una herramienta de Excel, Tablas Dinamicas. Una Tabla
Dinamica nos permite resumir y analizar grandes volumenes de datos, mediante el
agrupamiento de acuerdo a las necesidades
Crear una Tabla Dinamica
La creacion de una Tabla Dinamica se realiza a traves de varios pasos.
La Tabla Dinamica se realiza sobre una base de datos existentes, los rangos de datos deben
tener una fila de encabezados.
Ejecucion
1. Selecconar una celda de la base de datos
2 . Pulsar Insertar
3. Pulsar sobre el desplazamiento Tabla Dinamica
4. Se abre el cuadro de dialogo Crear tabla dinamica, donde aparece el rango de celdas en el
que se encuentran los datos de origen de la base , A1:D21, en este caso.
a) Para el informe de Tabla dinamica en la hoja, elegir la opcion Hoja de calculo existente
y en el cuadro Ubicación seleccionar la celda donde se quiere ubicar el informe.
Para ubicar el informe en una hoja diferente, caso adoptado, seleccionar
Nueva hoja de calculo. Luego hacer Clic en Aceptar.
Automaticamente se habre una nueva hoja de calculo.
5 . Se crea un campo de diseño
6. A la derecha de la pantalla aparece el panel llamado Lista de campos de tabla dinamica,
desde el cual se puede administrar y organizar los datos de la tabla.
Se puede observar en su parte superior la totalidad de campos que contiene la base de
datos origen
En su parte inferior se encuentran 4 areas para trasladar los campos y permitir crear el
informe de tabla dinamica.
Areas
1- Filtro de informe 2- Etiqueta de columnas
3- Etiquetas de fila 4- Valores
Como primer paso, se deben tildar en la parte superior los campos a emplear en el informe,
en este caso dejamos sin activar Mes. Automaticamente se ubican en la parte inferior de
areas, los campos activados. El proximo paso es ubicar en las areas respectivas los campos
activados de acuerdo al informe a obtener, mediante el pulsado de la flecha en la parte.
derecha . En este caso, Semana a Etiquetas de Columna, Vehiculo a Etiquetas de fila y
y Cantidad a Valores
Aplicar filtos a la Tabla
En primer lugar se despliega la lista en semana y se desactiva la 2ª y 4ª
En segundo lugar se despliega la lista en vehiculo y se desactivan Camion-Camioneta y Moto
Se obtiene el siguiente resultado al aplicarle los filtros a la tabla
Cambio de diseño de la Tabla
Se ejecutan los siguiente pasos , se traslada Vehiculo a Etiqueta de Columna y Semana a
Etiquetas de fila, obteniendose la presenta tabla modificada
Actualizar Tabla dinamica en caso de cambio de datos en base horigen
En Herramientas de tabla dinamica-
pulsar Opciones-
Desplegar Actualizar-
pulsar Actualizar todo
Aplicación de Filtros
En primer lugar se despliega y se selecciona 1ª y 3ª Semana
Como segundo paso con igual proceso seleccionar Auto y Camioneta, obteniendo el
siguiente resumen
Segmentacion de datos
Es una herramienta que incluye la version de Excel 2010, que simplifica el proceso de
filtrado de una informacion de tabla dinamica. Consiste en una area con botones que
permite filtrar y mostrar datos deseados sin necesidad de abrir listas desplegables.
Para generar una segmentacion de datos vamos a ejecutar sus pasos en la tabla dinamica
realizada.
1- Seleccionar una celda de la tabla dinamica ejecutada. Ir a la ficha Herramientas de
Tabla dinamica/Opciones
2- Dentro del grupo Ordenar y filtrar , hacer clic en Insertar Segmentacion de datos
3- Se abre un cuadro de dialogo mostrando una lista de los campos de la tabla
4- Seleccionar los campos donde se efectuara la segmentacion de datos en la tabla.
En este caso adoptamos Semana y Vehiculo.
Pulsar Aceptar
Aparecen dos cuadros correspondientes a los campos seleccionados y el contenido de los
mismos.
Los cuadros se trasladan arrastrandolos al lugar desado. En este caso se colocaron debajo de
la tabla .
Si deseamos seleccionar en el campo Vehículos, Auto y Omnibus, en primer lugar, hacer clic
sobre Auto y luego mantener pulsada la tecla Ctrl y pulsar Omnibus. Igual operación
realizamos en 1ª y3ª Semana
Produciendose el filtrado correspondiente en el informe de tabla dinamica
Realizando la misma operación sobre los cuadros respectivos se pueden realizar todos los
cambios de filtrado que sean necesarios
Como graficar una Tabla Dinámica
1- Seleccionar una celda de la tabla
2- En Herramientas de Tablas Dinámicas pulsar Opciones
3- Pulsar Grafico dinamico, en este caso seleccionamos Barra
4- Por ultimo sobre el grafico obtenido , pulsar sobre una barra del grafico con boton
derecho, pulsar agregar etiqueta de datos.
Con esta operación se completan los datos en el grafico dinámico respectivo.
Se puede observar que el grafico contiene los filtros respectivos que permite
cambiar la condiciones del mismo, en cuanto a las selecciones de Vehículo y Semana
Aplicación de Tablas Dinámicas en una Base de Datos mas amplia
Se incluye en la presente base, la recaudación que produce cada vehículo en forma
semanal, sus totales en recaudación y beneficios.
Crear la Tabla Dinámica
Se aplica el mismo procedimiento que fue especificado en la base anterior para crear la
Tabla Dinámica aplicada a esta base mas amplia hasta llegar a Lista de campos de Tabla
dinámica
En ella aparecen incluidos todos los campos de la base de datos, para tildar los que se
emplearan en la tabla. En este caso todos , para ser trasladados a las distintas áreas de
creación de la tabla
Áreas
Semana, a Filtro de Informe
Mes a Etiqueta de filas
Auto- Camión- Ómnibus- Camioneta- Moto- Recaudado y Beneficios a Valores
Aparece la Tabla Dinámica diseñada, acompañada de una barra de herramientas que
permite filtrar datos por despliegue de los mismos
Se agrupo en forma mensual la recaudación por cada vehículo, el total recaudado y total
beneficios, como también el total general a través del semestre.
Obtención de Subtablas
Haciendo doble clic sobre una de las celdas, por ejemplo en la fila del mes de Febrero, en
Suma de auto, valor 1.088.166,72 se obtiene en forma automática en otra hoja el siguiente
informe
Aplicar filtros sobre la Tabla Dinámica
Sobre la tabla se puede aplicar filtros, por ejemplo sobre la lista desplegable de Etiquetas
de fila dejando activado solamente Enero, Abril y Junio, se obtiene el siguiente resultado.
Otro ejemplo de filtrado
Sobre la lista de filtrado de Semana, por ejemplo dejando solamente activada la semana 1 y
9, se obtiene el siguiente resultado
Resumir en porcentaje los valores de la Tabla Dinámica
Una primera opción de varias que permite la Tabla Dinámica
Se retira el filtrado realizado, dejando la Tabla completa
1- Seleccionar 1 celda, en este caso una de la primer columna, -Pulsar con el botón derecho
la celda seleccionada
2- Se desarrolla una lista- Seleccionar Resumir datos por ,surge otra lista- Pulsar sobre
Mas opciones
3- Surge la planilla - Configuración de campo de valor - Pulsar sobre - Mostrar valores como
4- Surge una lista desplegable , desplegar y seleccionar - % del Total general
Pulsar Aceptar
Se obtiene en porcentaje la recaudación mensual respecto al primer vehículo.
Esta operación se debe ejecutar en cada columna de la Tabla
Obtener Promedios
Siguiendo con las opciones que permiten las Tabla Dinámicas, en este caso vamos a obtener
de la tabla sin filtrar el promedio de recaudación por mes de cada vehículo
En primer lugar se debe retirar el Total general mediante los siguientes pasos.
1- Seleccionar una celda de la Tabla y pulsar el botón derecho.
2- Del listado que se desplaza seleccionar y pulsar Opciones de tabla dinámica
3- Seleccionar de la planilla que surge la celda Totales y filtros
4- Desactivar Mostrar totales de las columnas.
Pulsar Aceptar
1- Seleccionar una celda de la tabla dinamica.Partimos por una de Suma de Autos
Se activa Herramientas de Tabla dinámica- Pulsar Opciones
2- Pulsar Configuracion de Campo
3- Seleccionar la opción Promedio
4- Clic en Aceptar
Esta operación se realiza en una celda de cada tipo de vehículo, en una celda de Total y de
Beneficios obteniendo el promedio mensual de recaudación de cada vehículo, el promedio
total recaudado por Mes y el promedio de Beneficios
Se pueden sobre la tabla realizar los filtros sobre Semanas y Meses
Obtener la Recaudacion Maxima por Mes, Vehiculo,Total y Beneficios
Se realizan las mismas operaciones, seleccionando en este caso Max
Obteniendo la siguiente Tabla
Cambiar el diseño de la tabla
Siguiendo el mismo procedimiento, se puede cambiar el diseño de la Tabla, por ejemplo
en este caso , en la Lista de campos de la tabla dinamica se traslada Mes a Etiqueta de
columna y Valores a Etique de fila.
Obteniendose la tabla dinamica modificada.
Desplegando la Etiqueta de columna, dejando activados los meses , Enero, Marzo y Mayo,
obtenemos el filtrado de la tabla
Resultado de la Tabla dinamica Filtrada
Graficar la Tabla Dinámica
1- Seleccionar una celda de la tabla
2- En Herramientas de Tablas Dinámicas pulsar Opciones
3- Pulsar Grafico dinamico, en este caso seleccionamos Barra
4- Por ultimo sobre el grafico obtenido , pulsar sobre una barra del grafico con boton
derecho, pulsar agregar etiqueta de datos.
Con esta operación se complatan los datos en el grafico dinamico respectivo.
Ordenar
Otra opcion de la Tabla, es ordenarse de acuerdo a los valores contenidos, en este caso
vamos a ordenar las Recaudaciones de Menor a Mayor por vehiculo. En primer lugar de la
lista de Campos de Tabla Dinamica, desactivamos Recaudado y Beneficios.
Automaticamente la Tabla no los muestra. La operación continua, 1 - Seleccionar una celda
de la misma. 2 - Pulsar Ordenar 3 - Surge el recuadro, Ordenar por - En Opciones de
ordenacion Activar- De menor a mayor.
Pulsar Aceptar
La operación produce la ordenacion de de Tabla Dinamica.
Informe de Tabla Dinámica, conformada con base de datos incluidos en distintas hojas de
cálculo.
Muchas veces tenemos bases de datos ubicadas en distintas hojas que necesitamos resumir,
combinar y totalizar y otros datos que para su ejecución se debe recurrir al Asistente de
Tablas y gráficos dinámicos. Esta herramienta no aparece en la Cinta de Opciones
Para incorporarla en la Barra de herramientas de acceso rápido, realizamos los siguientes
pasos
1- Pulsar Archivo, seleccionar Opciones. Se despliega Opciones de Excel.
2- Seleccionar Barra de herramientas de acceso rápido- En la lista desplegable que se
presenta seleccionar Todos los comandos, buscar y seleccionar Asistenta para Tablas y
gráficos dinámicos, a la derecha pulsar la opción Agregar, por ultimo Aceptar
Con esta operación tenemos disponible el asistente en la Barra de herramientas de acceso
rápido
El presente ejercicio se realiza con bases de datos ubicadas en 6 hojas, las bases son
pequeñas para reducir espacio de ejecución, pero el proceso se puede ejecutar en bases
amplias. El informe de tabla dinámica se ejecuta en nueva hoja que llamamos Resumen.
Los datos de las bases se agrupan en 6 meses a partir de Enero hasta Junio.
Cada base se introduce en una hoja con el nombre del mes, es decir, 1-Enero, 2-Febrero,
3-Marzo, 4-Abril y 6-Junio
Pasos de ejecución de la Tabla Dinámica
En primer lugar abrimos la pagina Resumen, y seleccionamos una celda, en este caso,
optamos por la celda C3, como segundo paso pulsar sobre el Asistente para Tablas y gráficos
dinámicos
Surge el paso 1 de 3 del Asistente
Sobre el mismo Seleccionar Rangos de consolidación múltiples y Tabla dinámica
Pulsar Siguiente
Surge el 2 paso donde se debe mantener seleccionado , Crear un solo campo de pagina
Pulsar Siguiente
paso 2b de 3
En este paso se debe seleccionar los rangos de las hojas de calculo que se desea consolidar.
Pulsar Seleccionar en Rango
A partir de este paso, se comienza con la selección de datos, partiendo de la hoja 1-Enero
En la plantilla que surge pulsar - Agregar
Se ejecuta la misma operación de Selección y Agregar con el resto de las bases hasta la hoja
6-Junio, obteniendo el siguiente resultado
Sobre la planilla pulsar Siguiente
Se puede observar que aparece seleccionada la celda C3, donde se realizaron todos los
pasos. Pulsar Finalizar
Surge el presente informe de Tabla Dinámica donde le haremos algunas modificaciones.
Primero mediante Opciones de Tabla dinámica desactivamos Totales generales de filas.
Luego reemplazamos Pagina 1 por Mes, quedando la tabla dinámica siguiente
El próximo paso, es cambiar el diseño de acuerdo a lo que estimamos mas útil en el
Informe de Tabla Dinámica . Para ello nos trasladamos a Lista de campos de Tabla Dinámica
y ubicamos en Etiquetas de fila a Mes y Fila a Filtro de informe.
En el nuevo diseño, en Fila seleccionamos y colocamos Semana.
En Etiquetas de fila, en el listado de Elementos le colocamos el nombre del Mes, partiendo
con la selección de Elemento 1 y colocamos Enero, continuando con el resto, obteniendo el
siguiente informe de Tabla dinámica.
Vamos a realizar un filtrado, en primer lugar desactivamos en Semanas 2 y 4 y en
Etiquetas de fila dejamos Enero y Marzo, obteniendo el siguiente Resumen
En la tabla obtenida, producimos en Etiquetas de columna la selección de Auto, camioneta
y Ómnibus.
Graficar la Tabla Dinámica
Seleccionamos sobre la tabla una celda, luego debajo de Herramientas de tabla dinámica,
pulsamos Opciones y luego Gráficos Dinámicos, seleccionamos en tipo de grafico Área y
Área Apilada obteniendo el grafico
Volviendo la Tabla a su forma completa sin filtrado, vamos a ejecutar sobre la misma uno
de los tantos resúmenes que la misma permite, en este caso, produciendo los porcentajes
de incidencia sobre el total de columna.
Ejecución
1-Seleccionar una celda de la Tabla Dinámica. 2- Pulsar sobre configuracion de campo.
3- Pulsar en Mostrar valores como. 4- Seleccionar % total de columna. Aceptar.
Se obtiene el siguiente resultado en la totalida de la Tabla Dinámica.