Una Tabla Dinámica, es un resumen de datos agrupados, que puntualizan en forma concreta datos de una información
Índice Asistente para tablas y gráficos dinámicos Lista o base de datos de Microsoft Excel -- Tabla dinámica ejecutada sobre una base de datos compacta, almacenada en otro libro de Excel -- Opciones de tablas dinámicas -- Ejemplos prácticos
Filtrado de la tabla dinámica ejecutada Ejecución de Subtablas Detalle semanal sobre un solo destino
-- Opciones avanzadas de Campo de tabla dinámica -- Campo Datos- Configuración -- Opciones personalizadas de Excel -- Formulas
Personalizar formulas Campos calculados Elementos calculados Orden de resolución Crear lista de formulas
-- Botones de selección -- Mostrar pagina -- Crear una tabla dinámica a partir de otra
Rangos de consolidación Múltiple -- Ejecución de una tabla dinámica a través de datos ubicados en múltiples hojas de un libro -- Ejecución de la tabla paso por paso -- Filtrado de datos -- Ejecución de una tabla dinámica, con fuente de datos, ubicados en distintas hojas de otro libro -- Creación de la tabla paso por paso -- Filtrado de la tabla ejecutada
--Grafico -Grafico dinámico -- Filtrado de datos del grafico dinámico
Fuente de datos externa -- Detalles de la fuente de datos externa a emplear -- Ejecución de una tabla dinámica, empleando una base de datos desde Access -- Empleo de Microsoft Query -- Ejecución Paso a paso, mediante una imagen de pantalla -- Consultar en Microsoft Query -- Ejecución de la tabla dinámica -- Filtrado de la tabla dinámica -- Opciones de tabla dinámica
Opciones de datos externos
-- Opciones avanzadas de campo de tabla dinámica -- Crear una tabla dinámica a partir de otra -- Crear gráficos dinámicos -- Filtrado de gráficos dinámicos
El empleo de Tablas Dinámicas se justifica si la cantidad de datos a manejar es importante El ejemplo que se empleara para ejecutar paso por paso todas la opciones que ofrece Excel para ejecutar las Tablas dinámicas consiste en una factura telefónica con algunas columnas agregadas, que permite asimilarla a una lista bastante completa para desarrollar el trabajo. La tabla es bastante amplia, manejándose en un rango de A1:H110, con fechas que van desde el 15/9/01 hasta el 30/09/01. Por razones de espacio se muestra una parte de la misma, que abarca un rango A1:H44. No obstante la totalidad de los datos serán analizados con la opción de Tablas Dinámicas. Excel 2000, presenta unas mejoras en el asistente, que permite a través de sus pasos la creación de las mencionadas tablas.
LISTA O BASE DE DATOS DE MICROSOFT EXCEL
Tabla Dinámica ejecutada sobre Datos almacenados en otro libro de Excel Ejemplo a emplear como lista
A Fecha
B C Día semana Hora inicial 15/09/01 sábado 0:00:04 15/09/01 sábado 10:42:49 15/09/01 sábado 20:31:44 16/09/01 domingo 19:10:44 16/09/01 domingo 23:25:47 16/09/01 domingo 23:43:20 16/09/01 domingo 23:46:29 16/09/01 domingo 10:11:37 16/09/01 domingo 22:21:51 17/09/01 lunes 22:28:17 17/09/01 lunes 23:21:59 17/09/01 lunes 9:14:50 17/09/01 lunes 19:08:07 17/09/01 lunes 20:55:07 18/09/01 martes 17:59:08 18/09/01 martes 18:15:50 18/09/01 martes 18:17:45 18/09/01 martes 22:56:18 18/09/01 martes 8:58:14 18/09/01 martes 20:08:33 18/09/01 martes 20:28:27 19/09/01 miércoles 7:53:57 19/09/01 miércoles 8:19:33 19/09/01 miércoles 21:43:26 19/09/01 miércoles 21:53:26 19/09/01 miércoles 22:44:00 19/09/01 miércoles 9:03:09 19/09/01 miércoles 19:59:23 19/09/01 miércoles 22:20:56 20/09/01 jueves 19:12:50 20/09/01 jueves 22:49:43 20/09/01 jueves 23:35:35 20/09/01 jueves 8:36:30 20/09/01 jueves 12:27:05 20/09/01 jueves 19:40:59 20/09/01 jueves 20:06:15 21/09/01 viernes 9:21:25 21/09/01 viernes 18:43:05 21/09/01 viernes 9:08:21 21/09/01 viernes 9:13:34 21/09/01 viernes 18:39:20 21/09/01 viernes 21:05:35 21/09/01 viernes 18:31:22
D Destino Correo Urbanas Internet Correo Correo Correo Internet Urbanas Inter-Urb Correo Correo Urbanas Celular Internet Correo Correo Correo Internet Urbanas Celular Celular Correo Correo Correo Correo Internet Urbanas Celular Inter-Urb Internet Correo Correo Urbanas Urbanas Urbanas Celular Correo Correo Urbanas Urbanas Urbanas Urbanas Internet
E Numero
F G H 1 Duración Hora final Importe 6102223847 0:00:46 2 0:00:50 $ 0,05 3 10:49:25 $ 0,19 4693881 0:06:36 6102227638 1:28:50 4 22:00:34 $ 0,56 6102223847 0:03:46 5 19:14:30 $ 0,05 6102223847 0:01:26 6 23:27:13 $ 0,05 6102223847 0:02:10 7 23:45:30 $ 0,05 6102227638 1:00:02 8 0:46:31 $ 0,05 4660671 0:05:08 9 10:16:45 $ 0,09 3514268888 0:05:00 10 22:26:51 $ 2,31 6102223847 0:01:57 11 22:30:14 $ 0,05 6102223847 0:01:54 12 23:23:53 $ 0,05 4694898 0:00:09 13 9:14:59 $ 0,05 155070059 0:08:23 14 19:16:30 $ 0,23 6102227638 1:04:02 15 21:59:09 $ 0,47 6102223847 0:03:18 16 18:02:26 $ 0,09 6102223847 0:01:05 17 18:16:55 $ 0,05 6102223847 0:00:50 18 18:18:35 $ 0,05 6102227638 1:05:56 19 0:02:14 $ 0,47 4694898 0:00:08 20 8:58:22 $ 0,05 155070059 0:01:03 21 20:09:36 $ 0,05 155070059 0:00:39 22 20:29:06 $ 0,05 6102223847 0:05:44 23 7:59:41 $ 0,09 6102223847 0:07:14 24 8:26:47 $ 0,19 6102223847 0:03:56 25 21:47:22 $ 0,05 6102223847 0:01:45 26 21:55:11 $ 0,05 6102227638 2:00:51 27 0:44:51 $ 0,80 4694898 0:00:07 28 9:03:16 $ 0,05 155070059 0:01:53 29 20:01:16 $ 0,05 3514268888 0:10:10 30 22:31:06 $ 4,69 6102227638 0:13:02 31 19:25:52 $ 0,33 6102223847 0:27:03 32 23:16:46 $ 0,19 6102223847 0:01:15 33 23:36:50 $ 0,05 4694898 0:00:06 34 8:36:36 $ 0,05 4602096 0:00:49 35 12:27:54 $ 0,05 4539695 0:00:37 36 19:41:36 $ 0,05 155070059 0:01:50 37 20:08:05 $ 0,05 38 9:22:38 $ 0,05 6102223847 0:01:13 6102223847 0:01:15 39 18:44:20 $ 0,05 4694898 0:00:07 40 9:08:28 $ 0,05 4693881 0:00:45 41 9:14:19 $ 0,05 4694898 0:00:10 42 18:39:30 $ 0,05 4660671 0:00:59 43 21:06:34 $ 0,05 6102227638 0:02:42 44 18:34:04 $ 0,09 La planilla como se puede observar se completa con la columna B que corresponde a Día semana. Para resolver que devuelva el día de la semana que corresponde a la fecha de la columna A se aplico la función Lógica SI, con su máximo anidamiento, y la función de Fecha
DIASEM. Por lo tanto en la celda B2 para trasladar hasta el final de la planilla se aplica la formula combinada:
B2=SI(A2=””;””;SI(DIASEM(A2;1)=1;”domingo”;SI(DIASEM(A2;1)=2;”lunes”; SI(DIASEM(A2;1)=3;“martes”;SI(DIASEM(A2;1)=4;”Miércoles”; SI(DIASEM(A2;1)=5;”jueves”; SI(DIASEM(A2;1)=6;”viernes”;”sábado”))))))) Por ultimo se agrego la columna G Hora final donde se incluyo la formula: G2= C2+F2
Para la ejecución de la tabla dinámica vamos a emplear el asistente para tablas y gráficos dinámicos, que en este curso se supone que el usuario tiene un conocimiento general del empleo del mismo. En primer lugar recurrimos al Menú-Datos-Asistente para tablas y gráficos dinámicos.
Paso 1 ¿ Dónde están los datos que desea analizar? Activar: Lista o base de datos de Microsoft Excel ¿Qué tipo de Informe desea crear? Activar: Tabla dinámica
Paso 2 En este paso vamos a producir una variante con respecto a la ubicación de la lista o base de datos, en este caso la lista que vamos a utilizar para extraer la información y crear la tabla dinámica se encuentra almacenada almacenada en otro libro, que llamamos Control tiempos, por lo tanto en el paso 2 empleamos el botón Examinar Pulsando el botón Examinar y ubicando el archivo que contiene los datos, Excel devuelve
‘C:\Mis documentos\Control tiempos.xls’!
Al pulsar siguiente Excel indicara error. Por lo tanto se deberá realizar la siguiente operación: Suprimir C:\Mis documentos\, encerrar con corchetes el nombre del libro e incluir el rango de datos que se deberá conocer con anterioridad, quedando de la siguiente manera
‘[Control tiempos.xls]Hoja 1’!$A$1:$H$110 1’!$ A$1:$H$110 Realizada la operación Pulsar siguiente
Paso 3 En este paso existe la opción de colocar la tabla en una hoja nueva, en la hoja actual o en otra creada. creada. Para los dos últimos últimos casos casos se deberá usar el botón botón de Contraer Contraer dialogo dialogo y ubicar ubicar la celda inicial de desarrollo de la tabla. ( Se selecciona la celda A 1 ). Concluido este paso pulsar finalizar Como consecuen consecuencia, cia, surge una nueva barra de herramie herramientas, ntas, que contiene contiene una serie de botones con los nombres de los encabezados de columna de datos. Siendo ubicados algunos de ellos en la nueva hoja siguiendo el siguiente criterio.
Pagina: Los botones que se colocan en esta área son los primeros que se filtran. En este caso colocamos el botón Fecha, pudiendo mostrar todas las fechas o una determinada. Fila: Son los elementos que queremos colocar como filas en la tabla dinámica, en este caso adoptamos el botón Destino Columna: Como en el caso anterior, colocamos los datos que forman parte de la columna de la tabla, ubicando en la misma el botón Día Semana Datos: En este caso se colocan los datos que se organizan y se resumen, optamos por Duración e Importe. En el caso de Datos, Datos, al ubicar los botones botones por defecto defecto,, aparece aparecerá rá Contar Contar Duración y Sumar Sumar Importe. En el caso del importe, obtendremos obtendremos la sumatoria total de los distintos importes. En el caso de Duración, también necesitamos la sumatoria de los tiempos empleados, para tal fin, debemos dar Formato al Botón
La operación mencionada se realiza de la siguiente manera: pulsar un clic con el botón derecho sobre sobre Contar Contar durac duración ión-- selecc seleccion ionar ar Config Configura uració ción n de campo campo y reemp reemplaz lazar ar en la tabla tabla desplegable que se origina, contar por sumar y al mismo tiempo dar formato personalizado, de la siguiente forma. Dentro del cuadro que se presenta pulsar sobre el botón numero. Categoría: hora Personalizada Tipo : h:mm:ss
aceptar Aceptar
Una ves concluida la tabla dinámica, a su finalización se produce el calculo total de los tiempos e Importes, respectivamente. En el caso de los tiempos, se debe seleccionar toda la fila y dar formato de categoría: hora, personalizado [h]:mm:ss. Después de todo lo expuesto el resultado obtenido es la siguiente tabla dinámica.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
A Fecha
Destino Celular
B
Datos Suma de Duración Suma de Duración Suma de Importe
Internet
Suma de Duración Suma de Importe
Inter-Urb
D
E
Día semana lunes
martes
G
H
I
J
Suma de Importe
Total tal Suma Suma de Dur Durac ació ión n Total tal Sum Sumaa de de Imp Impor orte te
viernes
sábado
domingo Total general
0:07:54
0:03:46
0:04:28
0:05:04
0:35:32
0,37
0,24
0,15
0,14
0,10
1,00
0:58:30
0:27:17
1:28:05
0:52:21
0:08:30
0:00:46
0:27:15
0,81
0,71
1,37
0,63
0,29
0,05
0,54
1:13:10
1:05:56
4:34:20
0:13:02
0:02:42
1:28:50
3:18:53
11:56:53
0,66
0,47
1,93
0,33
0,09
0,56
0,99
5,03
0:10:10
0:19:32
0:29:42
4,69
3,73
8,42
Suma de Duración Suma de Duración
miércoles jueves
0:14:20
Suma de Importe
Urbanas
F
(Todas)
Suma de Importe
Correo
C
4:22:44 4,40
0:07:06
0:01:17
0:02:41
0:06:36
0:10:46
0:21:23
0:29:55
1:19:44
0,19
0,25
0,24
0,40
0,43
0,62
0,46
2,59
2:33:06
1:42:24
6:19:02
1:16:27
0:21:58
1:50:59
4:40:39
18:44:35
2,03
1,67
8,38
1,50
0,81
1,23
5,82
21,44
Antes de seguir con el desarrollo y configuración de los botones que componen la tabla dinámica, veamos como funciona la tabla ejecutada. Como se puede apreciar, la tabla a ejecutado un resumen general por día de semana de los tiempos y costos de cada destino, con sus totales individuales y generales. Esto Esto se produ produce ce al habe haberr coloca colocado do en la zona zona Pagina el botón botón Fecha, encontrándose encontrándose agrupadas en su totalidad, por lo tanto figuran Todas. Fecha: En caso de interesarnos una fecha en especial contenida en la lista desplegable que se produce al pulsar el botón respectivo, la tabla se contrae a la fecha respectiva, identificándola con el día que corresponde. Produciendo los correspondientes totales. Destino: Permite mantener los 5 destinos, o los que se quiera adoptar en particular Datos: Optar por la totalidad Duración e Importe o uno en particular. Día Semana: Permite mantener la totalidad de los días, lunes a domingo, o un día en particular, donde se determine el total de Duración e Importe referido a ese día en el total de la lista. Todas estas opciones opciones se obtienen en el caso de la celda Fecha, seleccionando según la lista desplegable. El resto, Destino-Datos-Día Semana, manteniendo manteniendo tildada las las opciones respectivas respectivas en la lista desplegables.
Opciones de Tablas Dinámicas
Pulsando el botón Opciones que se encuentra en el Paso 3 de 3 del Asistente para crear tablas dinámicas, se abre un cuadro de dialogo con opciones avanzadas que se detallan.
Nombre: Permite mantener o cambiar el nombre de la tabla Opciones de formato Totales Generales de columnas- Totales generales de filas: Los resúmenes obtenidos con la ejecución de tablas dinámicas, necesitan en casi todos los casos de los totales de filas f ilas y columnas o por lo menos los totales en una de las dos opciones. Por defecto las dos opciones aparecen activadas.
Auto formato de tabla Esta casilla se encuentra activada automáticamente, el formato que se aplica a la tabla es el que tenemos por defecto en el menú Formato. En caso de querer optar por otro, seleccionamos el menú Formato y recurrimos a los que ofrece Excel.
Subtotales de elementos ocultos de pagina Esta casilla se encuentra desactivada, por cuanto sino Excel incluiría en los subtotales, datos de opciones no elegidas en el campo Pagina.
Combinar Rótulos Si se activa esta casilla se centran los nombres de los elementos, cualquiera cualquiera sea su formato. f ormato.
Mantener Formato Si se decide cambiar Formato, al mantener activada esta opción, al actualizar datos se vuelve al formato original
Diseño de Pagina Pagina tiene dos configuraciones. Hacia abajo, luego horizontalmente y Horizontalmente, y luego hacia abajo. Por defecto toma la primer opción. En caso de tener mas de un botón en el campo Pagina, permite cambiar la posición.
Campos por columnas Dentro de la zona Pagina, permite elegir el numero de botones que van a aparecer en una misma columna. Optar por 0 (cero), muestra los botones en la misma columna.
Para valores erróneos, mostrar Se activa esta casilla y se quiere sacar una frase, en caso de existir valores incorrectos.
Para celdas vacías, mostrar Por defecto se encuentra activada y mostrara celdas vacías, caso contrario mostrara cero.
Opciones de datos Guardar datos con el diseño de la tabla Por defecto se encuentra activada, si se desactiva no se podrá configurar de nuevo la tabla dinámica ni cambiar las acciones que realiza, Contar, sumar etc.
Habilitar traspasar detalles Manteniendo Manteniendo el casillero activado, permite al hacer doble clic sobre cualquier celda del área de datos, abrir otra hoja y crear otra tabla donde se muestra un detalle. Cada vez que se ejecute de la misma manera un doble clic sobre la celda seleccionada del área, se abrirá una nueva hoja.
Actualizar al abrir Si se han empleado datos de otro libro de Excel para crear la tabla dinámica como el caso del ejemplo, al activar la casilla, se actualizara la tabla cuando se habrá el libro que tiene los datos de origen.
Opciones de datos externos Corresponde a tablas dinámicas creadas con datos externos, sin usar datos del libro de Excel
Ejemplos Prácticos Antes de seguir avanzando en el proceso de afinar en todo lo que se refiere a la elaboración de las Tablas Tablas dinámicas, vamos a ejecutar algunas acciones sobra la tabla ejecutada que permita ver el comportamiento y funcionamiento de la misma de acuerdo a lo avanzado hasta el momento. Ejemplo 1
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
A Fecha
Destino Celular Correo Internet Urbanas
B
C
D
26/09/01 Día semana miércoles
Datos Suma de Duración Suma de Importe Suma de Duración Suma de Importe Suma de Duración Suma de Importe Suma de Duración Suma de Importe
Total otal Sum Sumaa de Dur Duraci ación ón Total otal Sum Sumaa de de Impo Import rtee
0:01:53 0,10 1:09:26 0,99 2:33:29 1,13 0:02:34 0,19 3:47:22 2,41
Como primer ejemplo del comporta comportamien miento to de la planilla, planilla, adoptamos adoptamos por elegir elegir una fecha determinada y obtener los datos correspondientes. En este caso el 26/9/01. Como se puede ver corresponde a un día miércoles y determina los consumos y costos de cada destino. Al mismo tiempo ponemos en practica el empleo de Opciones de Tablas dinámicas Botón derecho-Opciones- desactivar Totales generales de Filas- Aceptar En este caso no se necesitan Totales generales por fila, por que se repetirían los datos. Ejemplo 2
Ejecución de Subtablas Dentro de Opciones de tablas dinámicas, existe una opción que se encuentra habilitada, que se denomina Habilitar Traspasar Traspasar detalles. Esta operación se realiza haciendo doble clic sobre un dato que interesa, y automáticamente se abrirá una hoja y creara una Subtabla con todos los detalles correspondientes. Supongamos que quisiéramos obtener el detalle completo de la tabla respecto al empleo del destino Correo, los días miércoles. Se debe debe ejec ejecut utar ar dobl doble e clic clic sobr sobre e la celd celda a que que corre corresp spon onde de a la Suma Suma de dura duraci ción ón,, correspondiente al destino Correo y día semana miércoles. Con esta operación se obtiene la siguiente Subtabla
1 2 3 4
A Fecha
B C D Día semana Hora inicial Destino
26/09/01 miércoles 26/09/01 mi m iércoles 26/09/01 mi m iércoles
18:26:21 Correo 9:24:36 Co C orreo 0:12:54 Co C orreo
E Numero 6102223847 6102223847 6102223847
F G H Duración Hora final Importe 0:33:03 0:27:36 0:08:47
18:59:24 9:52:12 0:21:41
0,47 0,38 0,14
5 6 7 8
19/09/01 miércoles 19/09/01 miércoles 19/09/01 mi m iércoles 19/09/01 mi m iércoles
21:53:26 Correo 21:43:26 Correo 8:19:33 Co C orreo 7:53:57 Co C orreo
6102223847 6102223847 6102223847 6102223847
0:01:45 0:03:56 0:07:14 0:05:44
21:55:11 21:47:22 8:26:47 7:59:41
Ejemplo 3
Siguiendo Siguiendo con la misma opción, si quisiéramos saber el comportamiento comportamiento de Duración e Importe en totales de Día Semana solamente solamente en correo y totales mensuales del resto de los destinos, destinos, operar de la siguiente manera: Doble Doble clic sobre la celda celda Correo Correo de la tabla, tabla, automáti automáticame camente nte presenta presenta un cuadro Mostrar detalle, seleccionar Día semana obteniendo como resultado:
A Fecha
B
(Todas) 1 2 3 Destino Día semana 4 Celular 5 lunes 6 Correo 7 martes 8 9 miércoles 10 11 jueves 12 13 viernes 14 15
C Datos Suma de Duración Suma de Importe Suma de Duración Suma de Importe Suma de Duración Suma de Importe Suma de Duración Suma de Importe Suma de Duración Suma de Importe Suma de Duración Suma de Importe
D Total 0:35:32 1,00 0:58:30 0,81 0:27:17 0,71 1:28:05 1,37 0:52:21 0,63 0:08:30 0,29
0,05 0,05 0,19 0,09
16 17 18 19 20 21 22 23 24 25 26 27 28 29
sábado
Suma de Duración Suma de Importe Suma de Duración Suma de Importe
domingo
Suma Suma d dee Dura Duraci ción ón Corr Correo eo Suma Suma de Impo Import rtee Cor Corre reo o Internet Inter-Urb Urbanas
Suma de de Du Duraci ració ón Suma de Importe Suma de Duración Suma de Importe Suma de Duración Suma de Importe
Total otal Sum Sumaa de Dur Duraci ación ón Total otal Sum Sumaa de de Impo Import rtee
0:00:46 0,05 0:27:15 0,54 4:22:44 4,4 11:56: :56:5 53 5,03 0:29:42 8,42 1:19:44 2,59 18:44:35 21,44
Opciones avanzadas de campo de tabla dinámica Seleccionar una celda de la columna Destino, y efectuar un clic con el botón derecho del Mouse. Seleccionar y pulsar Campo. Muestra una pantalla Campo de la tabla dinámica Pulsar sobre el botón Avanzado. Presenta un cuadro de dialogo Opciones avanzadas de campo de tabla dinámica En primer lugar se cuenta con: Opciones de campo de pagina: Tiene que ver con datos de fuente externa. Opciones de Auto ordenar: Manualmente: en este caso permite ordenar la tabla a gusto. Ascendente:Se Ascendente:Se produce la operación al crear la tabla por primera vez. Descendente:Al Descendente:Al activar esta opción se invierte el caso anterior sobre la tabla t abla creada.
Opciones de Automostrar: Esta es una opción que permite mostrar los elementos superiores o inferiores de la tabla de acuerdo a un cierto numero. Manualmente: la tabla no sufre modificación a lo mostrado. Automáticamente: Automáticamente: al activar esta opción opción se activan las opciones opciones Mostrar y Usar campo Sobre la opción de Automostrar, vamos ejecutar el siguiente ejemplo, sobre la tabla dinámica ejecutada De los cinco Destinos disponibles, vamos a optar por los dos de mayor importe, para ello: Activar Automáticamente Automáticamente En Mostrar superiores 2 Usar campo Suma de importe
Aceptar Aceptar
Se obtiene como resultado el siguiente resumen de tabla A 1 2 3 4 5
Fecha
B
C
D
E
F
G
H
I
J
(Todas) Día semana
Destino
Datos
Internet
Suma de Duración
lunes
martes 1:13:10
1:05:56
miércoles 4:34:20
jueves 0:13:02
viernes 0:02:42
sábado 1:28:50
domingo 3:18:53
Total general 11:56:53
6 7
Suma de Importe Suma de Duración Suma de Importe
Inter-Urb
8 9 10
Total Suma S uma de Duración Total Suma S uma de Importe
0,66
0,47
1,93
0,33
0,09
0,56
0,99
5,03
0:10:10
0:19:32
0:29:42
4,69
3,73
8,42
1:13:10
1:05:56
4:44:30
0:13:02
0:02:42
1:28:50
3:38:25
12:26:35
0,66
0,47
6,62
0,33
0,09
0,56
4,72
13,45
11
Campo Datos – Configuración Para realizar esta operación una de las formas posibles es la siguiente: con un clic sobre una celda del campo Datos, Datos, se despliega una lista, seleccionar seleccionar Campo, se acceda al cuadro cuadro de dialogo Campo de la Tabla dinámica. Para determina determinarr la opción que debe ejecutar ejecutar Excel Excel en Resumir por, ya fue explicado explicado en el cambio Contar, por Suma, con respecto al botón Numero, al seleccionar el mencionado botón se despliegan dos zonas, Categoría y Muestra que son conocidas en Menú-Formato-Celdas, en ellas se le da el formato de acuerdo a los datos que representen.
Opciones Personalizadas de Excel Por ultimo tenemos el botón Opciones, en ella se describen la operaciones que ofrece Excel. Para ello se debe desplegar desplegar la lista Mostrar datos datos como, que contiene en en primer lugar lugar normal, es como esta la tabla actual. A continuación se enumeran las siguientes acciones:
Diferencia de: Calcula la diferencia de un dato seleccionado como rango en la tabla base y el resto, al hacer coincidir en base al campo base, el elemento base. Vamos amos a ejecut ejecutar ar un ejem ejemplo plo para para enten entender derlo lo mejor mejor.. Por ejemp ejemplo lo nos nos intere interesa sa sabe saberr la diferencia que existe entre el rango Suma de importe del destino Correo y todo el resto.
Ejecución
Seleccionamos con el cursor una celda de Suma de Importe del Destino Correo, en este caso nos ubicamos en la celda que corresponde al día Martes con un valor de 0,71 (puede ser cualquier celda del rango). Un clic con el botón derecho, seleccionar Campo, en Campo de la tabla dinámica, seleccionar Opciones en Mostrar datos como desplegar desplegar la lista y adoptamos adoptamos Diferencia de: en Campo Campo base, seleccionar seleccionar Destino y en Elemento base, base, seleccionar Correo Aceptar Si todo funciono bien la tabla aparecerá como:
1 2 3 4 5 6 7 8 9 10 11 12
A Fecha
B
C
D
E
F
G
H
I
J
(Todas)
Día semana Destino
Datos
Celular
Suma de Duración Suma de Importe
Correo
Suma de de Du Duración
lunes
martes
miércoles
jueves
viernes
sábado
domingo
Total ge g eneral
0:14:20
0:07:54
0:03:46
0:04:28
0:05:04
0:35:32
-0,44
-0,47
-1,22
-0,49
-0,29
-0,05
0:58:30
0:27:17
1:28:05
0:52:21
0:08:30
0:00:46
0,44 0:27:15
1:13:10
1:05:56
4:34:20
0:13:02
0:02:42
1:28:50
3:18:53
11:56:53
-0,15
-0,24
0,56
-0,3
-0,2
0,51
0,45
0,63
0:19:32
0:29:42
3,19
4,02
-
3,40 4:22:44
Suma de Importe Internet
Suma de Duración Suma de Importe
Inter-Urb
Suma de Duración Suma de Importe
0:10:10 -0,81
-0,71
3,32
-0,63
-0,29
-0,05
13 14
Urbanas
15 16
Total Suma de Duración
Suma de Duración
0:07:06
0:01:17
0:02:41
0:06:36
0:10:46
0:21:23
0:29:55
1:19:44
-0,62
-0,46
-1,13
0,14
0,57
-0,08
-1,81
2:33:06
1:42:24
6:19:02
0,23 1:16:27
0:21:58
1:50:59
4:40:39
18:44:35
Suma de Importe
Total Suma de Importe
Puede observarse como cada Destino muestra la diferencia de Suma de importe de cada día con respecto a la Suma de Importe de Correo, desapareciendo las de este lo mismo que los totales de columnas, quedando los totales en filas que marcan la diferencia total de cada uno
% De : Calcula el tanto por ciento de todos los datos relacionando relacionando con el dato seleccionado seleccionado en la tabla dinámica, para los datos seleccionados en la tabla de origen, el valor cera 100% En este este caso caso como como ejem ejempl plo o segu seguim imos os con con el dest destin ino o Correo, como como selecc selección ión base base,, seleccionando la celda de Suma de Duración, día martes con un tiempo de 0:27:17. La ejecución es similar al caso anterior, solamente vario la selección de la opción Un clic con el botón derecho, seleccionar Campo, en Campo de la tabla dinámica, seleccionar Opcione Opciones s en Mostrar datos datos como desplegar desplegar la lista y adoptam adoptamos os % De: en Campo Campo base, base, seleccionar Destino Destino y en Elemento base, seleccionar seleccionar Correo Aceptar
Si todo funciono bien la tabla aparecerá como: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
A Fecha
B
C
D
E
F
G
H
I
J
(Todas)
Día semana Destino
Datos
Celular
Suma de Duración Suma de Importe
Correo
Suma de d e Du D uración Suma de Importe
Internet
Suma de Duración Suma de Importe
Inter-Urb
Suma de de Du Duración
lunes
martes
Suma de Duración Suma de Importe
jueves
viernes
24,50%
28,96%
4,28%
8,53%
0,37
0,24
0,15
0,14
100,00%
100,00%
100,00%
100,00%
0,81
0,71
1,37
125,07%
241,66%
0,66 0,00%
0,00%
sábado
domingo
Total ge g eneral
0,00%
18,59%
100,00%
100,00%
0,1 0 100,00%
100,00%
0,63
0,29
0,05
0,54
4,40
311,45%
24,90%
31,76%
729,85%
272,86%
0,47
1,93
0,33
0,09
11586,96 % 0,56
0,99
5,03
0,00%
11,54%
0,00%
0,00%
0,00%
71,68%
11,30%
3,73
8,42
109,79%
30,35%
Suma de Importe Urbanas
miércoles
4,69 126,67% 2789,13%
13,52% 1,00
12,14%
4,70%
3,05%
12,61%
0,19
0,25
0,24
0,4
0,43
0,62
0,46
2,59
2,03
1,67
8,38
1,5 0
0,81
1,23
5,82
21,44
Total Suma de Duración Total Suma de Importe
Puede observarse como cada Destino muestra la diferencia en % de Suma de Duración de cada día con respecto a la Suma de Duración de Correo, que toman el valor de 100% desapareciendo desapareciendo los totales de columnas, quedando los totales en filas que marcan la diferencia total de cada uno
% de la diferencia de:
En este caso ejecuta la diferencia de los valores seleccionados en la tabla base con el resto y lo expresa en porcentaje, quedando en la nueva tabla los valores seleccionados en blanco. Para Para most mostra rarr el ejem ejempl plo, o, se real realiz iza a el mism mismo o caso caso ante anteri rior or,, los los paso pasos s son son simi simila lare res, s, seleccionando en Mostrar datos como, %de la diferencia de:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
A Fecha
B
C
D
E
F
G
H
I
J
(Todas)
Día semana Destino
Datos
Celular
Suma de d e Du D uración
lunes
martes
-91,47%
0,37
0,24
0,15
0,14
-100,00%
0,81
0,71
1,37
0,63
0,29
25,07%
141,66%
211,45%
-75,10%
-68,24%
0,66
0,47
1,93
0,33
-100,00%
-100,00%
-88,46%
-100,00%
domingo
-100,00%
-81,41%
Total ge g eneral -86,48%
0,1 0
1,00
0,05
0,54
4,40
629,85%
172,86%
0,09
11486,96 % 0,56
0,99
5,03
-100,00%
-100,00%
-28,32%
-88,70%
3,73
8,42
9,79%
-69,65%
Suma de de Du Durac ración Suma de Duración
Suma de Duración Suma de Importe
Urbanas
sábado
-95,72%
Suma de Importe Inter-Urb
viernes
-71,04%
Suma de Importe Internet
jueves
-75,50%
Suma de Importe Correo
miércoles
Suma de Duración
4,69 -87,86%
-95,30%
-96,95%
-87,39%
0,19
0,25
0,24
0,4
0,43
0,62
0,46
2,59
2,03
1,67
8,38
1,50
0,81
1,23
5,82
21,44
Suma de Importe
26,67% 2689,13%
Total Suma de Duración Total Suma de Importe
Total en : Produce el total de los campos en forma acumulativa, según el seleccionado en la tabla base Para Para produ producir cir el ejemp ejemplo lo selecc seleccion ionam amos os en la tabla tabla orige origen n en desti destino no Intern Internet, et, la celda celda correspondiente a la Suma de Importe de día miércoles, valor 1,93 La operación se realiza de la misma manera, seleccionando en Mostrar datos como Total en: y en Campo base destino.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
A Fecha
B
C
D
E
F
G
H
I
J
(Todas)
Día semana Destino
Datos
Celular
Suma de Duración Suma de Importe
Correo
Suma de de Du Duración Suma de Importe
Internet
Suma de Duración Suma de Importe
Inter-Urb
martes
Suma de Duración Suma de Importe
Total Suma de Duración Total Suma de Importe
miércoles
jueves
viernes
sábado
domingo 0:05:04
Total ge g eneral
0:14:20
0:07:54
0:03:46
0:04:28
0,37
0,24
0,15
0,14
0
0
0:58:30
0:27:17
1:28:05
0:52:21
0:08:30
0:00:46
0,1 0 0:27:15
1,18
0,95
1,52
0,77
0,29
0,05
0,64
1:13:10
1:05:56
4:34:20
0:13:02
0:02:42
1:28:50
3:18:53
11:56:53
1,84
1,42
3,45
1,1
0,38
0,61
1,63
10,43
0:19:32
0:29:42
Suma de Duración Suma de Importe
Urbanas
lunes
0:10:10
0:35:32 1,00 4:22:44 5,40
1,84
1,42
8,14
1,1
0,38
0,61
5,36
18,85
0:07:06
0:01:17
0:02:41
0:06:36
0:10:46
0:21:23
0:29:55
1:19:44
2,03
1,67
8,38
1,5
0,81
1,23
5,82
21,44
2:33:06
1:42:24
6:19:02
1:16:27
0:21:58
1:50:59
4:40:39
18:44:35
Se puede puede observar los totales totales acumulados acumulados de Suma Suma de Importe, Importe, quedando quedando en blanco blanco la fila correspondiente correspondi ente a Total Suma de Importe.
% de la fila : Saca el tanto por ciento de cada fila según el 100% del total de cada una. Lo que seleccionamos en la tabla origen, es una celda referida al destino que nos interesa, si Suma de duración o Suma de Importe, en este caso seleccionamos cualquier celda de Sumas de duración, y seleccionamos en Mostrar datos como: % por fila
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
A Fecha
B
C
D
E
F
G
H
I
J
(Todas)
Día semana Destino
Datos
Celular
Suma de de Du Duración
lunes
martes
Suma de Duración Suma de Duración
10,60%
12,57%
0,37
0,24
0,15
0,14
22,27%
10,38%
33,53%
19,93%
3,24%
0,81
0,71
1,37
0,63
10,21%
9,20%
38,27%
0,66
0,47
0,00%
0,00%
Suma de Importe Inter-Urb
Suma de Duración
0,00%
sábado 0,00%
domingo
100,00%
0,1
1,00
0,29%
10,37%
100,00%
0,29
0,05
0,54
4,40
1,82%
0,38%
12,39%
27,74%
100,00%
1,93
0,33
0,09
0,56
0,99
5,03
34,23%
0,00%
0,00%
0,00%
65,77%
100,00%
3,73
8,42
4,69
Suma de Duración
8,90%
1,61%
3,37%
8,28%
13,50%
26,82%
37,52%
100,00%
0,19
0,25
0,24
0,4
0,43
0,62
0,46
2,59
13,61%
9,11%
33,70%
6,80%
1,95%
9,87%
24,96%
100,00%
2,03
1,67
8,38
1,5
0,81
1,23
5,82
21,44
Suma de Importe Total Suma de Duración
Total ge g eneral
14,26%
Suma de Importe Urbanas
viernes
22,23%
Suma de Importe Internet
jueves
40,34%
Suma de Importe Correo
miércoles
Total Suma de Importe
Como puede observarse los totales de filas muestran el 100%
% de la columna :
Saca el tanto por ciento de cada columna según el 100% del total de cada una Es el mismo caso anterior pero en columnas, donde el total columnas muestran el 100%
% del total:
Calcula para cada celda de la tabla el tanto por ciento del total del dato seleccionado en la tabla origen. Para mostrar un ejemplo, seleccionamos una celda cualquiera en la tabla origen, referida a Suma de Duración, y cada celda contendrá un porcentaje que es el que emplea dentro del 100% de la selección total. Los pasos para realizarlo son los mismos, seleccionando en Mostrar datos como, % del total-
Aceptar 1 2 3 4
A Fecha
B
C
D
E
F
G
H
I
J
(Todas)
Día semana Destino
Datos
lunes
martes
miércoles
jueves
viernes
sábado
domingo
Total ge g eneral
5 6
Celular
7 8 9 10 11 12 13 14
Correo
15 16
Total Suma de Duración
Suma de Duración
1,27%
0,70%
0,33%
0,40%
0,37
0,24
0,15
0,14
5,20%
2,43%
7,83%
4,66%
0,81
0,71
1,37
6,51%
5,86%
0,66 0,00%
Suma de Importe Suma de Duración Suma de Importe Internet
Suma de Duración Suma de Importe
Inter-Urb
Suma de Duración
0,00%
0,45%
3,16% 1,00
0,76%
0,07%
0,1 0 2,42%
23,36%
0,63
0,29
0,05
0,54
4,40
24,39%
1,16%
0,24%
7,90%
17,69%
63,75%
0,47
1,93
0,33
0,09
0,56
0,99
5,03
0,00%
0,90%
0,00%
0,00%
0,00%
1,74%
2,64%
3,73
8,42
Suma de Importe Urbanas
0,00%
4,69
Suma de Duración
0,63%
0,11%
0,24%
0,59%
0,96%
1,90%
2,66%
7,09%
0,19
0,25
0,24
0,43
0,62
0,46
2,59
13,61%
9,11%
33,70%
0,4 0 6,80%
1,95%
9,87%
24,96%
100,00%
2,03
1,67
8,38
0,81
1,23
5,82
21,44
Suma de Importe
Total Suma de Importe
1,5 0
Se puede apreciar en la tabla, el porcentaje que incide por día en cada Destino, la Suma de Duración sobre el total general del 100%. Al mismo tiempo se puede observar en la columna Total general el acumulado de cada Destino que reflejan el % de incidencia del total que sumados representan el 100% de la celda J 15
Índice: Esta operación produce un resultado por celda según la siguiente operación: ((valor de la celda)*(valor total de total general))/ ((Suma de la fila correspondiente)*(Suma de la columna correspondiente)). Explicada sobre la tabla origen, consiste en lo siguiente: Seleccionar cualquier celda, en este caso optamos por E 10 , es decir corresponde a Suma de Importe, día miércoles, valor 1,93, la operación que realiza es : (E 10*J 16)/(J 10*E16), llevada a valores (1,93*21,44)/(5,03*8,38) Para realizar la operación operación seleccionar seleccionar en Mostrar datos como Índice. Esta operación la realiza en todas las celdas correspondientes correspondientes a Suma de Importe. Las celdas celdas de Totales generales mostraran valor 1. El resultado es el siguiente
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
A Fecha
B
C
D
E
F
G
H
I
J
(Todas)
Día semana Destino
Datos
Celular
Suma de Duración
0:14:20
0:07:54
0:03:46
0:04:28
Suma de Importe
3,9078
3,0812
0,3838
2,0011
0,0000
Suma de de Du Duración
0:58:30
0:27:17
1:28:05
0:52:21
Suma de Importe
1,9443
2,0716
0,7966
Suma de Duración
1:13:10
1:05:56
Suma de Importe
1,3858
1,1996
Correo Internet Inter-Urb
lunes
martes
Suma de Duración
miércoles
jueves
viernes
sábado
domingo
Total ge g eneral
0:05:04
0:35:32
0,0000
0,3684
1,0000
0:08:30
0:00:46
0:27:15
4:22:44
2,0465
1,7446
0,1981
0,4521
1,0000
4:34:20
0:13:02
0:02:42
1:28:50
3:18:53
11:56:53
0,9817
0,9377
0,4736
1,9406
0,7251
1,0000
0:19:32
0:29:42
0:10:10
Suma de Importe
0,0000
0,0000
1,4251
0,0000
0,0000
0,0000
1,6319
1,0000
Suma de Duración
0:07:06
0:01:17
0:02:41
0:06:36
0:10:46
0:21:23
0:29:55
1:19:44
Suma de Importe
0,7748
1,2392
0,2371
2,2075
4,3945
4,1726
0,6543
1,0000
Total Suma de Duración
2:33:06
1:42:24
6:19:02
1:16:27
0:21:58
1:50:59
4:40:39
18:44:35
Total Suma de Importe
1,0000
1,0000
1,0000
1,0000
1,0000
1,0000
1,0000
1,0000
Urbanas
Como el resultado lo determina en 9 decimales, a los efectos de mostrar la tabla se redujo a 4. Para realizar realizar la operaci operación, ón, un clic con el botón derecho derecho sobre sobre Suma de Importe –Formato Posición decimal celda—numero---número— —4
Formulas Formulas es uno de los contenidos que surge del menú que se despliega al efectuar un clic con el botón derecho sobre la tabla. Al selecciona seleccionarr Formulas Formulas,, se dispone dispone de cuatro elemento elementos s que se desarrolla desarrollaran ran con los ejemplos respectivos. Los mismos son Campo Calculado, Elemento calculado, Orden de resolución, Crear lista
de formulas.
Personalizar Formulas Excel ofrece la posibilidad de crear formulas dentro de la tabla dinámica. Para realizar esta operación se debe ejecutar sobre los Campos o Elementos de la tabla. Las operaciones a realizar sobre la tabla dinámica ejecutada son las siguientes:
Campos calculados Se realizaran todos los pasos sobre un ejemplo que consiste en aplicar un Impuesto del 6% a la Suma de Importes. Ejecución: En primer primer lugar lugar, seleccion seleccionamo amos s una celda de la tabla tabla dinámic dinámica a correspon correspondien diente te al Campo Campo Datos, que corresponde a la Suma de Importe, por ejemplo, la celda correspondiente al día miércoles de suma de importe, referido al destino Correo, cuyo valor es 1,37 Efectuar un clic sobre la celda con el botón derecho, se despliega un menú del cual seleccionar Formulas, pulsar Campo calculado. Con esta operación aparece una ventana que se debe completar 1) Nombre agregamos Impuesto 6% seleccionar en Campos Importe 2) Formula, seleccionar 3) Pulsar Insertar campo 4) El campo aparece en la celda Formula , donde se debe completar la misma colocando * 6% quedando la misma =Importe*6% Repetir esta operación si se quiere calcular otros campos. 5) Pulsar sobre el botón Sumar Si todo anduvo bien Impuesto*6% se inserta dentro del listado de Campos 6) Para finalizar Aceptar
1 2 3 4 5 6 7
A Fecha
D
E
F
G
H
I
J
(Todas)
Destino
Datos
Celular
Suma de Duración
Correo
11 12 13
Internet
17
C
Día semana
8 9 10
14 15 16
B
lunes
martes
jueves
viernes
sábado
domingo
Total ge g eneral
0:14:20
0:07:54
0:03:46
0:04:28
0:05:04
0:35:32
Suma de Importe
0,37
0,24
0,15
0,14
0,10
1,00
Suma de Impuesto 6% Suma de de Du Duración
0,02
0,01
0,01
0,01
0,00
0,00
0,01
0,06
0:58:30
0:27:17
1:28:05
0:52:21
0:08:30
0:00:46
0:27:15
4:22:44
Suma de Importe
0,81
0,71
1,37
0,63
0,29
0,05
0,54
4,40
Suma de Impuesto 6% Suma de Duración
0,05
0,04
0,08
0,04
0,02
0,00
0,03
0,26
1:13:10
1:05:56
4:34:20
0:13:02
0:02:42
1:28:50
3:18:53
11:56:53
0,66
0,47
1,93
0,33
0,09
0,56
0,99
5,03
0,04
0,03
0,12
0,02
0,01
0,03
0,06
0,30
0:10:10
0:19:32
0:29:42
4,69
3,73
8,42
Suma de Importe Suma de Impuesto 6% Inter-Urb Suma de Duración Suma de Importe
Urbanas
miércoles
Suma de Impuesto 6% Suma de Duración
0,00
0,00
0,28
0,00
0,00
0,00
0,22
0,51
0:07:06
0:01:17
0:02:41
0:06:36
0:10:46
0:21:23
0:29:55
1:19:44
18 19 20 21 22 23
Suma de Importe
0,19
0,25
0,24
0,40
0,43
0,62
0,46
2,59
0,01
0,02
0,01
0,02
0,03
0,04
0,03
0,16
2:33:06
1:42:24
6:19:02
1:16:27
0:21:58
1:50:59
4:40:39
18:44:35
Total Suma de Importe
2,03
1,67
8,38
1,50
0,81
1,23
5,82
21,44
Total Suma de Impuesto 6%
0,12
0,10
0,50
0,09
0,05
0,07
0,35
1,29
Suma de Impuesto 6% Total Suma de Duración
Observando la tabla, se puede verificar que cada destino tiene aplicado el impuesto del 6%, por día y un total general por filas y columnas. Como resumen de la operación realizada, realizada, se aclara que Excel opera sobre los datos resumidos que contiene la tabla dinámica, y no sobre los datos de origen. En la formula se pueden emplear mas de un campo. Para poner en practica este ultimo párrafo, empleemos el mismo ejemplo anterior, pero en este caso que el impuesto del 6% se incluya en el importe. Los pasos para realizar la operación son los mismos, realizando solamente dos cambios: 1- en Nombre , colocamos Importe con Impuesto 6% 2- en Formula, primero insertamos el campo Importe y le agregamos el signo +, insertamos nuevamente el campo Importe y agregamos *6%, quedando por lo tanto: Importe+Importe*6% El resultado es el siguiente:
1 2 3 4 5 6
A Fecha
D
E
F
G
H
I
J
(Todas)
Destino
Datos
Celular
Suma de Duración
8 9 10
Correo
11 12 13
Internet
lunes
martes
miércoles
sábado
domingo
0:03:46
0:04:28
0:05:04
Suma de Importe
0,37
0,24
0,15
0,14
Suma de Importe con Impuesto 6% Suma de de Du Duración
0,39
0,25
0,16
0,15
0,1 0 0,11
-
-
Total ge g eneral 0:35:32 1,00 1,06
0:58:30
0:27:17
1:28:05
0:52:21
0:08:30
0:00:46
0:27:15
Suma de Importe
0,81
0,71
1,37
0,63
0,29
0,05
0,54
4,40
Suma de Importe con Impuesto 6% Suma de Duración
0,86
0,75
1,45
0,67
0,31
0,05
0,57
4,66
Suma de Importe con Impuesto 6% Suma de Duración
4:22:44
1:13:10
1:05:56
4:34:20
0:13:02
0:02:42
1:28:50
3:18:53
11:56:53
0,66
0,47
1,93
0,33
0,09
0,56
0,99
5,03
0,70
0,50
2,05
0,35
0,10
0,59
1,05
5,33
0:10:10
0:19:32
0:29:42
4,69
3,73
8,42
3,95
8,93
Suma de Importe
19
viernes
0:07:54
Suma de Importe con Impuesto 6% Inter-Urb Suma de Duración
Urbanas
jueves
0:14:20
Suma de Importe
17 18
20 21
C
Día semana
7
14 15 16
B
-
-
4,97
-
-
-
0:07:06
0:01:17
0:02:41
0:06:36
0:10:46
0:21:23
0:29:55
1:19:44
Suma de Importe
0,19
0,25
0,24
0,43
0,62
0,46
2,59
Suma de Importe con Impuesto 6% Total Suma de Duración
0,20
0,27
0,25
0,4 0 0,42
0,46
0,66
0,49
2,75
2:33:06
1:42:24
6:19:02
1:16:27
0:21:58
1:50:59
4:40:39
18:44:35
2,03
1,67
8,38
1,5 0
0,81
1,23
5,82
21,44
Total Suma de Importe
22
Total Suma de Importe con Impuesto 6%
2,15
1,77
8,88
1,59
0,86
1,30
6,17
22,73
Elementos calculados El ejemplo se realiza sobre la tabla dinámica existente, donde introducimos una modificación para que se pueda apreciar en su totalidad. Como el ejemplo consiste en aplicar un impuesto diferencial por destino, excluiremos de la tabla el campo Suma de Duración Realicemos un repaso de cómo se modifica: Un clic con el botón derecho sobre la tabla- Seleccionar Asistente- Pulsar Diseño muestra el esquema de botones que conforman la tabla. Arrastras a su lugar de origen Suma de duración. Pulsar Terminar. Queda la siguiente tabla
A 1 2 3 4 5 6 7 8 9 10
Fecha
B
C
D
E
F
G
viernes
sábado
H
I
(Todas)
Suma de Importe Día semana
Destino
lunes
martes
miércoles
jueves
Celular
$ 0,37
$ 0,24
$ 0,15
$ 0,14
Correo
$ 0,81
$ 0,71
$ 1,37
$ 0,63
$ 0,29
Internet
$ 0,66
$ 0,47
$ 1,93
$ 0,33
$ 0,09
Inter-Urb
domingo
Total general
$ 0,10
$ 1,00
$ 0,05
$ 0,54
$ 4,40
$ 0,56
$ 0,99
$ 5,03
$ 3,73
$ 8,42
$ 4,69
Urbanas
$ 0,19
$ 0,25
$ 0,24
$ 0,40
$ 0,43
$ 0,62
$ 0,46
$ 2,59
Total general
$ 2,03
$ 1,67
$ 8,38
$ 1,50
$ 0,81
$ 1,23
$ 5,82
$ 21,44
Seleccionamos todos los destinos y le damos formato-celda moneda, y obtenemos el formato en todos los valores Para Para realiz realizar ar el ejemp ejemplo lo se incorp incorpora ora un impue impuesto sto difer diferen encia ciado do a cada cada desti destino no según según el siguiente porcentaje.
Celular Correo Internet Inter-Urb Urbanas
6% 4,5% 5% 4% 3%
Ejecución
1) Colocar el selector en un destino cualquiera, ejemplo Correo 2) Un clic con el botón botón derecho del Mouse 3) Seleccionar Formulas 4) Pulsar en elemento calculado
Con la operación realizada en el cuarto paso, aparece una ventana donde se realizan los cálculos de los elementos mediante el agregado de formulas. La ventana se debe completar paso a paso Nombr e colocamos colo camos Impuesto a Celular 6% 1- Nombre Formula: En la pantall pantalla a Campos, Campos, debe debe estar estar seleccion seleccionado ado Destino, caso contrario se 2- Formula selecciona. pantalla Elemento Elementos, s, seleccion seleccionar ar Celular. Pulsar Pulsar en Insert Insertar ar elem element ento. o. Apare Aparece ce en 3- En pantalla Formu For mula la =Celular , incorporarle *6%. Quedando la celda Formula = Celular *6% Pulsar Sumar Seleccionar y pulsar dos veces separadas Destino en la pantalla Campos y en la pantalla Elementos aparece incorporado el nuevo elemento Impuesto a Celular 6% Se repiten los pasos con el próximo Destino Nombre colocamos Impuesto a Correo 4,5% Suprimir de Formula Seleccionar Correo Pulsar Pulsar en Insertar Insertar element elemento o e incorpora incorporarle rle el porcenta porcentaje je de 4,5% quedando Formula = Correo*4,5% Se ejecutan las mismas operaciones que en el caso anterior y también es incorporado a la tabla de elementos. Los mismos pasos se ejecutan ejecutan para el resto resto de los Destinos Destinos,, cambian cambiando do el porcenta porcentaje je respectivo de acuerdo al listado. La totalidad de Destinos se incorporan a la pantalla de elementos. Para finalizar Pulsar Aceptar. Como aclaración aclaración se agrega que, que, al producirse la incorporación incorporación en en Formula del destino destino Inter-Urb, que se abrevio a las llamadas Inter Urbanas, Excel lo incorpora como un nombre compuesto de la siguiente manera ‘ Inter- Urb’, idéntico criterio se debe adoptar en el caso de que se incorpore un nombre compuesto en forma manual. Realizadas las operaciones descriptas la Tabla dinámica presenta la siguiente forma: A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
B
Fecha
(Todas)
Suma de Importe
Día semana
Destino
lunes
C
martes
D
miércoles
E
jueves
F
viernes
Celular
$ 0,37
$ 0,24
$ 0,15
$ 0,14
Correo
$ 0,81
$ 0,71
$ 1,37
$ 0,63
$ 0,29
Internet
$ 0,66
$ 0,47
$ 1,93
$ 0,33
$ 0,09
Inter-Urb
G
sábado
H
I
domingo Total ge general $ 0,10
$ 1,00
$ 0,05
$ 0,54
$ 4,40
$ 0,56
$ 0,99
$ 5,03
$ 3,73
$ 8,42
$ 4,69
Urbanas
$ 0,19
$ 0,25
$ 0,24
$ 0,40
$ 0,43
$ 0,62
$ 0,46
$ 2,59
Impuesto a Celular 6%
$ 0,02
$ 0,01
$ 0,01
$ 0,01
$ 0,00
$ 0,00
$ 0,01
$ 0,06
Impuesto a Correo 4,5%%
$ 0,04
$ 0,03
$ 0,06
$ 0,03
$ 0,01
$ 0,00
$ 0,02
$ 0,20
Impuesto a Internet 5%
$ 0,03
$ 0,02
$ 0,10
$ 0,02
$ 0,00
$ 0,03
$ 0,05
$ 0,25
Impuesto a Inter-Urb 4%
$ 0,00
$ 0,00
$ 0,19
$ 0,00
$ 0,00
$ 0,00
$ 0,15
$ 0,34
Impuesto a Urbanas 3%
$ 0,01
$ 0,01
$ 0,01
$ 0,01
$ 0,01
$ 0,02
$ 0,01
$ 0,08
Total general
$ 2,13
$ 1,75
$ 8,74
$ 1,57
$ 0,84
$ 1,28
$ 6,06
$ 22,36
Orden de resolución Esta solamente relacionada con los Elementos calculados. Al pulsar sobre cualquier operación realizada, se puede modificar el orden de la misma, mediante los botones Mover hacia arriba y Mover hacia abajo. El botón Eliminar, elimina la operación seleccionada. Al pulsar el botón Cerrar, cierra y recalcula la tabla en el caso de haber ejecutado alguna modificación.
Crear listas de formulas Este elemento permite visualizar todas las operaciones que se hallan realizado sobre la tabla dinámica. Seleccionando una celda de la tabla y volviendo a la lista desplegable Formulas, del menú Tablas ablas dinámica dinámicas, s, pulsar pulsar sobre sobre Crear Crear lista lista de formulas, formulas, se crea una nueva nueva hoja con la siguiente información
Campo calculado Orden de resolución
Campo 1 Im Impuesto 6%
Elemento calculado Orden de resolución
Elemento 1 'Impuesto a Celular 6%' 2 Impu mpuesto sto a Corr Corre eo 4,5 4,5%' %' 3 'Imp Impuesto esto a Inte Intern rne et 5%' 5%' 4 'Imp 'Impue uest sto o a Int Inter er-U -Urb rb 4%' 4%' 5 'Imp Impuesto esto a Urb Urba anas 3%' 3%'
Notas:
Fórmula =Importe*6%
Fórmula =Celular*6 r*6% =Co =Corre rreo *4 *4,5% ,5% =In =Intern terne et *5% ='In ='Inte terr-Ur Urb' b' *4% *4% =Urb =Urba anas *3 *3%
Cuando una celda se actualiza con más de una fórmula, el valor lo establece la fórmula con la última orden de resolución. Para cambiar el orden de resolución de fórmulas, use el comando Orden de resolución en la barra de comandos de la tabla dinámica.
Botones de selección Dentr Dentro o de la lista lista despl desplega egable ble,, ubica ubicarr Selecc Seleccion ionar, ar, esta esta opera operació ción n permit permite, e, tenien teniendo do habilita habilitado do el botón botón Habilitar Habilitar selección, selección, disponer disponer de una serie de botones botones que permiten permiten seleccionar partes de la tabla, según se seleccione, Rotulo, Datos, Datos y Rótulos, Toda la tabla
Mostrar pagina En este caso, pulsand pulsando o Mostrar Mostrar pagina, pagina, en la lista desplegabl desplegable, e, aparece aparece una pantall pantalla a dond donde e selecc seleccion iona a el conten contenido ido de la zona zona Pagin Pagina, a, en nuest nuestro ro caso caso Fecha Fecha,, pulsa pulsand ndo o Aceptar, divide la información en hojas que se corresponde con cada fecha acumulada.
Crear una Tabla Dinámica a partir de otra Una manera de optimizar memoria, cuando se desea ejecutar varias tablas con la misma base de datos, es emplear en el primer paso del asistente, la opción Otra tabla dinámica
RANGOS DE CONSOLIDACION MULTIPLE
Ejecución de una Tabla Dinámica a través de múltiples hojas de un libro Para ejecutar el presente ejemplo, se tomo para realizarlo r ealizarlo la Cotización de las acciones lideres en la Bolsa de Comercio de Buenos Aires, durante 4 semanas del mes de julio del año 2002. Cada Cada cotiz cotizaci ación ón seman semanal al se realiz realiza a en una una hoja hoja de calcul calculo, o, es decir decir,, en este este caso caso se emplearon 4 hojas de calculo. Con el presente ejemplo, se demuestra como se puede vincular, analizar y sintetizar datos a través de las tablas dinámicas, extrayendo los mismos de distintas hojas de calculo de un mismo libro. Para comenzar con el ejercicio, en primer lugar veremos los datos por semana de cotización, en cada hoja de calculo que llamaremos 1° Sem, 2° Sem, 3° Sem y 4° Sem.
1° Semana de Cotización 1° Sem.
Mes Julio
1° Semana
Cotización de las acciones lideres en la Bolsa de Comercio 1
A
B
C
D
E
F
G
H
I
Sociedad
28-06-02
01-07-02
02-07-02
03-07-02
04-07-02
05-07-02
Var.Sem.
Var. An Anual
2 Acindar
0,207
0,220
0,242
0,255
0,272
0,266
28,50
107,81
3 Atanor
1,980
2,050
2,010
2,060
2,150
2,080
5,05
160,00
4 Bco. Bansud
0,460
0,465
0,490
0,539
0,673
0,840
82,61
133,33
5 Bco. Francés
1,810
1,900
1,800
1,820
2,000
2,100
16,02
-28,81
6 Bco. Galicia
0,198
0,205
0,198
0,198
0,220
0,240
21,21
-48,05
7 Bco. Rió
1,350
1,350
1,350
1,350
1,300
1,300
-3,70
-19,62
8 Com. Del Plata
0,095
0,098
0,097
0,098
0,105
0,110
15,79
214,29
9
1,450
1,530
1,560
1,580
1,690
1,690
16,55
745,00
10 Ir Irsa
1,530
1,570
1,570
1,600
1,700
1,710
11,76
134,25
11 Molinos
4,920
5,300
5,400
5,650
5,850
5,800
17,89
205,26
12 PC Holding
2,240
2,440
2,300
2,390
2,420
2,310
3,13
33,53
13 Renault
0,395
0,405
0,390
0,400
0,440
0,500
26,58
185,71
14 Siderar
2,300
2,500
2,630
2,850
2,930
2,780
20,87
157,41
15 Siderca
6,490
6,800
6,600
6,600
6,650
6,300
-2,93
173,91
16 Telecom
0,595
0,578
0,555
0,670
0,759
0,730
22,69
-59,67
17 Telefónica
1,270
1,270
1,270
1,270
1,270
1,270
-7,97
5,83
18 TG T GSUD
0,510
0,530
0,540
0,540
0,575
0,580
13,73
-55,38
44,500
44,500
44,500
44,500
42,400
42,400
-4,72
79,66
350,650
370,740
360,800
371,480
387,730
382,750
9,15
29,57
Indupa
19 Y.P.F. 20 Merval
2° Semana de Cotización 2° Sem
Mes Julio
2° Semana
Cotización de las acciones lideres en la Bolsa de Comercio 1
A
B
C
D
E
F
G
H
I
Sociedad
05-07-02
08-07-02
09-07-02
10-07-02
11-07-02
12-07-02
Var.Sem.
Var. Anual
2 Acindar
0,266
0,268
0,268
0,242
0,242
0,230
-13,53
79,69
3 Atanor
2,080
2,080
2,080
2,000
2,030
2,000
-3,85
150,00
4 Bco. Bansud
0,840
0,924
0,924
1,010
1,050
0,980
16,67
172,22
5 Bco. Francés
2,100
2,230
2,230
2,080
2,050
2,020
-3,81
-31,53
6 Bco. Galicia
0,240
0,245
0,245
0,245
0,240
0,214
-10,83
-53,68
7 Bco. Rió
1,300
1,300
1,300
1,300
1,300
1,300
0,00
-19,62
8 Com. Del Plata
0,110
0,115
0,115
0,104
0,104
0,129
17,27
268,57
9 Indupa
1,690
1,760
1,760
1,630
1,630
1,670
-1,18
735,00
10 Ir Irsa
1,710
1,720
1,720
1,720
1,600
1,600
-6,43
119,18
11 Mo Molinos
5,800
5,800
5,800
5,690
5,550
5,500
-5,17
189,47
12 PC PC Holding
2,310
2,500
2,500
2,370
2,260
2,280
-1,30
31,79
13 Re Renault
0,500
0,530
0,530
0,510
0,500
0,490
-2,00
180,00
14 Si Siderar
2,780
2,850
2,850
2,750
2,670
2,640
-5,04
144,44
15 Si Siderca
6,300
6,660
6,660
6,650
6,200
6,100
-3,17
165,22
16 Te Telecom
0,730
0,760
0,760
0,698
0,684
0,710
-2,74
-60,77
17 Te Telefónica
1,270
1,270
1,270
1,270
1,270
1,270
0,00
5,83
18 TG TGSUD
0,580
0,610
0,610
0,605
0,590
0,587
1,21
-54,85
42,400
42,000
42,000
40,000
40,000
40,500
-4,48
71,61
382,750
402,120
402,120
385,010
373,490
371,030
-3,06
25,61
19 Y.P.F. 20 Merval
3° Semana de Cotización 3° Sem
Mes Julio
3° Semana
Cotización de las acciones lideres en la Bolsa de Comercio
1
A
B
C
D
E
F
G
H
I
Sociedad
12-07-02
15-07-02
16-07-02
17-07-02
18-07-02
19-07-02
Var.Sem.
Var. Anual
2 Acindar
0,230
0,232
0,230
0,247
0,255
0,265
15,22
107,03
3 Atanor
2,000
2,000
1,980
2,050
2,110
2,100
5,00
162,50
4 Bco. Bansud
0,980
0,920
0,900
0,910
0,930
0,925
-5,61
156,94
5 Bco. Francés
2,020
1,950
2,100
2,150
2,150
2,300
13,86
-22,03
6 Bco. Galicia
0,214
0,209
0,229
0,232
0,235
0,241
12,62
-47,84
7 Bco. Rió
1,300
1,300
1,300
1,300
1,300
1,300
0,00
-19,62
8 Com. Del Plata
0,129
0,143
0,140
0,138
0,145
0,139
7,75
297,14
9 Indupa
1,670
1,620
1,690
1,660
1,820
1,790
7,19
795,00
10 Irsa
1,600
1,660
1,660
1,660
1,690
1,670
4,38
128,77
11 M Mo olinos
5,500
5,600
5,600
5,600
5,600
5,850
6,36
207,89
12 PC PC Holding
2,280
2,230
2,330
2,270
2,240
2,170
-4,82
25,43
13 Re Renault
0,490
0,510
0,535
0,535
0,588
0,610
24,49
248,57
14 Si Siderar
2,640
2,600
2,530
2,530
2,560
2,870
8,71
165,74
15 Si Siderca
6,100
6,000
6,400
6,410
6,700
6,350
4,10
176,09
16 Te Telecom
0,710
0,640
0,650
0,660
0,680
0,680
-4,23
-62,43
17 Te Telefónica
1,250
1,250
1,250
1,200
1,190
1,100
-12,00
-8,33
18 TG TGSUD
0,587
0,575
0,575
0,575
0,640
0,670
14,14
-48,46
40,500
40,600
41,000
41,000
41,000
41,000
1,23
73,73
371,030
361,990
376,690
375,310
381,740
379,480
2,28
28,47
19 Y.P.F. 20 Merval
4° Semana de Cotización 4° Sem
Mes Julio
4° Semana
Cotización de las acciones lideres en la Bolsa de Comercio 1
A
B
C
D
E
F
G
H
I
Sociedad
19-07-02
22-07-02
23-07-02
24-07-02
25-07-02
26-07-02
Var.Sem.
Var. Anual
2 Acindar
0,265
0,255
0,280
0,305
0,290
0,275
3,77
114,84
3 Atanor
2,100
2,050
2,200
2,280
2,300
2,190
4,29
173,75
4 Bco. Bansud
0,925
0,860
0,830
0,780
0,702
0,701
-24,22
94,72
5 Bco. Frances
2,300
2,150
2,130
2,160
2,090
2,030
-11,74
-31,19
6 Bco. Galicia
0,241
0,222
0,228
0,238
0,222
0,215
-10,79
-53,46
7 Bco. Rió
1,420
1,420
1,540
1,540
1,680
1,830
28,87
13,16
8 Com. Del Plata
0,139
0,139
0,142
0,136
0,145
0,144
3,60
311,43
9 Indupa
1,790
1,796
1,910
2,000
1,970
1,990
11,17
895,00
10 Ir Irsa
1,670
1,570
1,570
1,570
1,620
1,620
-2,99
121,92
11 M Mo olinos
5,850
5,720
6,400
6,600
6,250
6,050
3,42
218,42
12 PC PC Holding
2,170
2,050
1,910
1,830
1,890
1,860
-14,29
7,51
13 Re Renault
0,610
0,560
0,600
0,635
0,615
0,580
-4,92
231,43
14 Si Siderar
2,870
3,100
3,080
3,030
2,930
2,850
-0,70
163,89
15 Si Siderca
6,350
6,500
6,850
6,900
6,860
6,900
8,66
200,00
16 Te Telecom
0,680
0,645
0,615
0,620
0,607
0,590
-13,24
-67,40
17 Te Telefónica
1,100
0,990
0,990
0,960
0,960
0,960
0,96
-20,00
18 TG TGSUD
0,670
0,610
0,580
0,545
0,580
0,555
-17,16
-57,31
41,000
37,200
37,200
35,500
37,000
37,000
-9,76
56,78
379,480
364,430
363,040
364,220
361,620
355,850
-6,23
20,47
19 Y.P.F. 20 Merval
El ejercicio consiste en obtener mediante una tabla dinámica el Promedio de Cotización semanal y mensual. La tabla dinámica se desarrollara en una hoja nueva que llamaremos Hoja Resumen
Ejecución Ubicar el cursor en la celda A3 de la hoja Resumen
Menú-Datos-Informe de tablas dinámicas y gráficos dinámicos Paso
1 de 3
Activar Rango de consolidación múltiples Activar Tabla dinámica
Paso
2a de 3
Activar crear un solo campo de pagina
Asistente para tablas y gráficos dinámicos --- paso paso 2 b Rango: Seleccionar 1° Semana
‘ 1° Sem’!$A$1:$G$20
Pulsar Agregar Seleccionar 2° Semana
‘ 1° Sem’!$A$1:$G$20 Agregar Efectuar la misma operación con la 3° y 4° Semana Si se ejecutaron en cada semana los datos por hoja dentro de los mismos rangos es decir
$A$1:$G$20, la selección se realiza por semana en forma automática.
Producida esta operación aparecen todos los rangos por semana incorporados en el sector
Todos los rangos Pulsar Siguiente
Paso 3 de 3 Pulsar diseño Por defecto aparece el diseño conformado de la siguiente manera Pagina
Pagina 1
Columna
Columna
Fila
Fila
Datos
Sumar Valor
El contenido de cada botón es el siguiente
Pagina 1 Agrupa a cada una de las semanas Columna Agrupa a todas las fechas de cotización Agrupa a todas las sociedades Fila Agrupa a todas las cotizaciones Datos Para ejecutar la tabla dinámica debemos realizar algunos cambios en el diseño y en la configuración de campos que propone Excel Por lo tanto el diseño de la tabla dinámica que resuma toda la información que nos interesa la realizamos de la siguiente manera: En Pagina ubicamos Columna, es decir agrupamos todas las fechas, obteniendo el resumen total por semana o hasta una fecha determinada. En Columna ubicamos Pagina 1 que agrupa las 4 semanas de cotización En Fila ubicamos Fila es decir agrupamos todas las sociedades que cotizan En Datos Colocamos Valor En este caso por defecto Excel coloca Sumar Valor, por lo tanto como nos interesa el Promedio, pulsando doble clic sobre el botón, aparece Campo de la tabla dinámica, seleccionamos Promedio, Aceptar. Como tenemos señalado la celda A3, pulsamos finalizar. Quedando conformada la tabla dinámica y el asistente en su parte superior.
Pulsando sobre la misma el botón Configuración de campo (segundo contando de la derecha), Promedio ya estaba configurado, por lo tanto pulsamos Numero, Categoría- numero— Posiciones decimales 3
Aceptar Aceptar.
Como retoque final le colocamos los nombre correspondientes es decir en Columna colocamos Fechas en Filas, Sociedades, en Pagina 1, Semanas y en Elemento 1, 1° Semana, 2° Semana, etc.
La tabla dinámica queda conformada de la siguiente manera
A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
B
Fechas
(Todas)
Promedio de Valor
Semanas
Sociedades
Acindar Atanor Bco. Bansud Bco. Francés Bco. Ga Galicia Bco. Rió Com Com. Del Del Plat Plataa Indupa Irsa Merval Molinos PC Holding Renaut Siderar
1°Semana 0,244 2,055 0,578 1,905 0,210 1,333 0,101 1,583 1,613 370,692 5,487 2,350 0,422 2,665
C
2° Se Semana 0,253 2,045 0,955 2,118 0,238 1,300 0,113 1,690 1,678 386,087 5,690 2,370 0,510 2,757
D
E
F
3° Se Semana 4° Se Semana Prom.general 0,243 0,278 0,254 2,040 2,187 2,082 0,928 0,800 0,815 2,112 2,143 2,070 0,227 0,228 0,226 1,300 1,572 1,376 0,139 0,141 0,123 1,708 1,909 1,723 1,657 1,603 1,638 374,373 364,773 373,981 5,625 6,145 5,737 2,253 1,952 2,231 0,545 0,600 0,519 2,622 2,977 2,755
19 20 21 22 23 24
Siderca Telecom Telefó efónica TGSUD Y.P.F. Prom rom.gen .gener eral al
6,573 0,648 1,270 0,546 43,800 23,372
6,428 0,724 1,270 0,597 41,150 24,104
6,327 0,670 1,207 0,604 40,850 23,444
6,727 0,626 0,993 0,590 37,483 22,828
6,514 0,667 1,185 0,584 40,821 23,437
Observando la tabla dinámica, se puede observar que se ha obtenido un resumen general de las 4 semanas a través del promedio de cotización por Semana y Sociedad
Filtrado de datos. Botón Fechas: Pulsando la flecha respectiva se despliegan todas las fechas que componen los días de cotización. Seleccionando una fecha, permite obtener las cotizaciones de esa fecha solamente de todas las sociedades. Botón Sociedades: Permite obtener el promedio de determinadas sociedades, desactivando el resto. Botón Semanas: Permite seleccionar las semanas que solamente se requieran obtener datos promedio de las 4 existentes.
RANGOS DE CONSOLIDACION MULTIPLE 2° Ejemplo En este ejemplo se emplearon para construir la Tabla dinámica, como fuente de datos, listas de elementos ubicados en distintas hojas de otro libro. Para ir comprobando su desarrollo y resultado final en los resúmenes, se emplearon para ejecutar el ejemplo, la planilla original de factura telefónica modificada, pero sus destinos divididos en hojas diferentes y ubicados en otro libro que llamamos Totales generales Cada lista se identifica por el nombre de la hoja que representa el destino. Teniendo en cuenta que los destinos corresponden a: Celular , Correo, Internet, Inter Urb y Urbanas, tendremos para cada uno la lista en una hoja respectiva dentro del libro Totales generales Según el siguiente esquema.
Libro Totales Generales Nombre de Hoja Celular
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
A Fecha 17/09/01 18/09/01 18/09/01 19/09/01 20/09/01 23/09/01 24/09/01 24/09/01 25/09/01 25/09/01 26/09/01 26/09/01 27/09/01 30/09/01
B Importe $ $ $ $ $ $ $ $ $ $ $ $ $ $
0,23 0,05 0,05 0,05 0,05 0,05 0,05 0,09 0,05 0,09 0,05 0,05 0,09 0,05
Libro Totales Generales Nombre de Hoja Correo
C Duración 0:08:23 0:01:03 0:00:39 0:01:53 0:01:50 0:03:24 0:00:18 0:05:39 0:00:34 0:05:38 0:00:43 0:01:10 0:02:38 0:01:40
D E Hora inicial Hora final 19:08:07 19:16:30 20:08:33 20:09:36 20:28:27 20:29:06 19:59:23 20:01:16 20:06:15 20:08:05 22:07:05 22:10:29 15:04:30 15:04:48 21:59:49 22:05:28 15:17:16 15:17:50 20:47:57 20:53:35 0:04:08 0:04:51 19:54:00 19:52:50 19:55:43 19:58:21 20:38:39 20:40:19
F Numero 155070059 155070059 155070059 155070059 155070059 155070059 155011312 155070059 155011312 155070059 155070059 155070059 155070059 155011312
A
B
C
D
E
F
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40
Fecha 15/09/01 16/09/01 16/09/01 16/09/01 17/09/01 17/09/01 18/09/01 18/09/01 18/09/01 19/09/01 19/09/01 19/09/01 19/09/01 20/09/01 20/09/01 21/09/01 21/09/01 23/09/01 23/09/01 23/09/01 23/09/01 24/09/01 24/09/01 24/09/01 25/09/01 25/09/01 25/09/01 26/09/01 26/09/01 26/09/01 27/09/01 27/09/01 27/09/01 27/09/01 27/09/01 28/09/01 28/09/01 28/09/01 30/09/01
Importe Duración Hora inicial Hora final $ 0,05 0:00:46 0:00:04 0:00:50 $ 0,05 0:03:46 19:10:44 19:14:30 $ 0,05 0:01:26 23:25:47 23:27:13 $ 0,05 0:02:10 23:43:20 23:45:30 $ 0,05 0:01:57 22:28:17 22:30:14 $ 0,05 0:01:54 23:21:59 23:23:53 $ 0,09 0:03:18 17:59:08 18:02:26 $ 0,05 0:01:05 18:15:50 18:16:55 $ 0,05 0:00:50 18:17:45 18:18:35 $ 0,09 0:05:44 7:53:57 7:59:41 $ 0,19 0:07:14 8:19:33 8:26:47 $ 0,05 0:03:56 21:43:26 21:47:22 $ 0,05 0:01:45 21:53:26 21:55:11 $ 0,19 0:27:03 22:49:43 23:16:46 $ 0,05 0:01:15 23:35:35 23:36:50 $ 0,05 0:01:13 9:21:25 9:22:38 $ 0,05 0:01:15 18:43:05 18:44:20 $ 0,05 0:00:58 22:25:50 22:26:48 $ 0,05 0:01:00 22:27:12 22:28:12 $ 0,05 0:01:00 22:31:12 22:32:12 $ 0,19 0:13:25 22:32:36 22:46:01 $ 0,19 0:15:26 7:44:21 7:59:47 $ 0,33 0:12:56 18:00:48 18:13:44 $ 0,19 0:26:17 22:33:00 22:59:17 $ 0,38 0:15:22 8:57:12 9:12:34 $ 0,05 0:01:54 16:09:25 16:11:19 23:41:44 $ 0,09 0:04:48 23:36:56 $ 0,14 0:08:47 0:12:54 0:21:41 $ 0,38 0:27:36 9:24:36 9:52:12 $ 0,47 0:33:03 18:26:21 18:59:24 $ 0,05 0:03:37 1:01:50 1:05:27 $ 0,05 0:00:46 7:49:38 7:50:24 $ 0,05 0:00:51 13:30:11 13:31:02 $ 0,05 0:01:21 13:32:47 13:34:08 $ 0,19 0:17:28 23:10:53 23:28:21 $ 0,05 0:01:22 17:12:53 17:14:15 $ 0,05 0:00:55 17:15:05 17:16:00 $ 0,09 0:03:45 17:55:15 17:59:00 $ 0,05 0:03:30 17:18:49 17:22:19
Numero 6102223847 6102223847 6102223847 6102223847 6102223847 6102223847 6102223847 6102223847 6102223847 6102223847 6102223847 6102223847 6102223847 6102223847 6102223847 6102223847 6102223847 6102223847 6102223847 6102223847 6102223847 6102223847 6102223847 6102223847 6102223847 6102223847 6102223847 6102223847 6102223847 6102223847 6102223847 6102223847 6102223847 6102223847 6102223847 6102223847 6102223847 6102223847 6102223847
Libro Totales Generales Nombre de Hoja Internet
1 2 3 4 5
A Fecha 15/09/01 16/09/01 17/09/01 18/09/01
B Importe $ $ $ $
0,56 0,05 0,47 0,47
C D E Duración Hora inicial Hora final 1:28:50 20:31:44 22:00:34 1:00:02 23:46:29 0:46:31 1:04:02 20:55:07 21:59:09 1:05:56 22:56:18 0:02:14
F Numero 6102227638 6102227638 6102227638 6102227638
6 7 8 9 10 11 12 13 14 15 16
19/09/01 20/09/01 21/09/01 23/09/01 24/09/01 24/09/01 26/09/01 26/09/01 26/09/01 26/09/01 30/09/01
$ $ $ $ $ $ $ $ $ $ $
0,80 0,33 0,09 0,75 0,05 0,14 0,05 0,47 0,05 0,56 0,19
2:00:51 0:13:02 0:02:42 1:50:10 0:00:33 0:08:35 0:00:33 1:05:55 0:00:33 1:26:28 0:28:41
22:44:00 19:12:50 18:31:22 20:16:13 20:41:42 20:45:47 20:55:34 20:56:31 23:33:41 23:34:38 23:37:37
C Duración
D Hora inicial
0:44:51 19:25:52 18:34:04 22:06:23 20:42:15 20:54:22 20:56:07 22:02:26 23:34:14 1:01:06 0:06:18
6102227638 6102227638 6102227638 6102227638 6102227638 6102227638 6102227638 6102227638 6102227638 6102227638 6102227638
Libro Totales Generales Nombre de Hoja Inter Urb
1 2 3 4
A Fecha
B Importe
16/09/01 19/09/01 30/09/01
$ $ $
2,31 4,69 1,42
0:05:00 0:10:10 0:14:32
E Hora final 22:21:51 22:26:51 22:20:56 22:31:06 20:43:18 20:57:50
F Numero 3514268888 3514268888 2322429622
Libro Totales Generales Nombre de Hoja Urbanas
1 2 3 4 5 6 7 8 9
A Fecha
B Importe
15/09/01 16/09/01 17/09/01 18/09/01 19/09/01 20/09/01 20/09/01 20/09/01
$ $ $ $ $ $ $ $
0,19 0,09 0,05 0,05 0,05 0,05 0,05 0,05
C Duración 0:06:36 0:05:08 0:00:09 0:00:08 0:00:07 0:00:06 0:00:49 0:00:37
D Hora inicial 10:42:49 10:11:37 9:14:50 8:58:14 9:03:09 8:36:30 12:27:05 19:40:59
E F Hora final Numero 4693881 10:49:25 4660671 10:16:45 4694898 9:14:59 4694898 8:58:22 4694898 9:03:16 4694898 8:36:36 4602096 12:27:54 4539695 19:41:36
10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39
21/09/01 21/09/01 21/09/01 21/09/01 22/09/01 22/09/01 22/09/01 22/09/01 22/09/01 23/09/01 24/09/01 24/09/01 25/09/01 25/09/01 25/09/01 25/09/01 26/09/01 26/09/01 26/09/01 27/09/01 27/09/01 27/09/01 27/09/01 27/09/01 28/09/01 29/09/01 29/09/01 30/09/01 30/09/01 30/09/01
$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $
0,05 0,05 0,05 0,05 0,05 0,09 0,05 0,05 0,05 0,14 0,05 0,09 0,05 0,05 0,05 0,05 0,05 0,09 0,05 0,05 0,05 0,05 0,05 0,05 0,23 0,05 0,09 0,09 0,09 0,05
0:00:07 0:00:45 0:00:10 0:00:59 0:01:07 0:04:58 0:00:11 0:01:34 0:00:15 0:10:04 0:00:08 0:06:49 0:00:07 0:00:40 0:00:13 0:00:09 0:00:07 0:02:19 0:00:08 0:00:07 0:00:10 0:02:48 0:01:46 0:00:13 0:08:45 0:00:08 0:06:34 0:06:38 0:07:47 0:00:18
9:08:21 9:13:34 18:39:20 21:05:35 15:28:24 19:07:03 19:22:41 19:23:22 19:27:33 11:24:48 9:12:38 20:31:09 9:12:51 11:26:32 11:44:21 23:56:19 9:12:24 11:35:23 23:02:29 9:19:07 10:20:27 20:44:13 22:04:38 22:28:38 11:41:20 10:02:37 13:30:25 9:52:21 10:52:50 21:04:45
9:08:28 9:14:19 18:39:30 21:06:34 15:29:31 19:12:01 19:22:52 19:24:56 19:27:48 11:34:52 9:12:46 20:37:58 9:12:58 11:27:12 11:44:34 23:56:28 9:12:31 11:37:42 23:02:37 9:19:14 10:20:37 20:47:01 22:06:24 22:28:51 11:50:05 10:02:45 13:36:59 9:58:59 11:00:37 21:05:03
4694898 4693881 4694898 4660671 4660671 4693881 4660571 4660671 4660671 4693881 4694898 4693881 4694898 4602096 4693881 4694898 4694898 4602096 4694898 4694898 4694898 4831814 4831814 4694898 4693881 4694898 4693881 4693881 4660671 4693881
Creación de la Tabla Dinámica paso por paso En un libro nuevo, nos ubicamos en una hoja que llamaremos Resumen General. Colocamos el selector en la celda B4. Menú- Datos- Asistente para tablas y gráficos dinámicos.
Paso 1 de 3 Activar Rangos de de consolidación consolidación múltiple múltiple
Paso 2ª de 3 Activar Campos de paginas personalizadas Esta acción nos permite insertar los nombres de elementos del campo en la tabla, en nuestro caso los destinos que se encuentran en hojas separadas Pulsar Siguiente
Paso 2b de 3 ¿ Dónde están los rangos de hoja de calculo que desea consolidar ? Pulsar Examinar
En este caso se ubica el archivo del nuevo libro, para realizar esta operación, en primer lugar se debe conocer de antemano, el nombre de la hoja donde se ubica la primer lista a consolidar y el Rango a emplear para obtener los datos. En todos los casos los datos que nos nos intere interesa sa para para ejecut ejecutar ar la tabla tabla correspo corresponde nden n a las column columnas as Fecha Fecha,, Import Importe e y Duración. En el recua recuadro dro Rang Rango, o, debem debemos os dejar dejar el nombr nombre e solam solament ente e del del libro, libro, encer encerrad rado o por corchetes, y agregarle el nombre de la hoja con el rango respectivo, quedando de la siguiente manera
‘[Totales ‘[Totales generales.xls]Celular’!$A$1:$C$15 generales.xls]Celular’ !$A$1:$C$15 Pulsar Agregar Y se incorpora a la pantalla Todos los rangos Pasamos a ¿ Cuantos campos de pagina desea ? En nuestro caso 1 Activar 1 Se activa el el casillero Campo uno uno Introducir Celular Manteniendo seleccionado el rango de datos al que se asocia Volver Volver a realizar toda la operación desde un principio para la segunda lista en nuestro caso
Correo.
Completada la operación la pantalla del asistente presente el siguiente panorama.
Muestra cierta variación en los datos de rangos por cuanto están extraídos de una tabla ejecutada en Excel, pero el procedimiento no varia Pulsar Siguiente
Paso 3 de 3 diseño Excel ofrece el siguiente esquema de ubicación Pagina
Pagina 1
Fila
Fila
Columna
Columna
Datos
Contar valor
En Pagina 1
ubica los destinos Las fechas en su totalidad Fila Columna Los dos elementos a calcular en Datos Importe y Duración Contar Importe y Duración Datos
En este caso vamos a producir unos cambios en la ubicación y configuración de campos. Ubicando los elementos de la siguiente manera Pagina
Fila
Fila
Pagina 1
Columna Columna Datos
Configurar Configurar el campo a Sumar valor
Por ultimo en Opciones- Opciones de Tabla Tabla dinámicaDesactivar Totales generales por fila Esto se realiza, por cuanto cuanto en este caso sumaria Importe con Duración. Duración.
Paso 3 de 3 Finalizar Una vez ejecutada la tabla, dar Formato- Celda a la columna Duración con Hora y a Importe con Moneda, quedando de la siguiente manera
A 1 2 3 4 5 6 7 8 9 10 11 12
B
C
Fechas
D
(Todas)
Suma de Valor Columna Duración Importe Destino Celular 00:35:32 $ 1,00 Correo 04:22:44 $ 4,40 Internet 11:56:53 $ 5,03 Inter-Urb 00:29:42 $ 8,42 Urbanas 01:19:44 $ 2,59
Total general
18:44:35
$ 21,44
Como Como se puede puede apreciar para darle otra característica característica,, se suplanto suplanto Fila, por Fechas Fechas y Pagina 1, por Destino Se puede observar que la tabla ofrece distintas opciones opciones para producir el filtrado de datos respectivos En Fechas: permite emplear la totalidad de las fechas para realizar los cálculos como en el caso del ejemplo, o una fecha en particular que se selecciona en una lista desplegable desplegable que posee al pulsar el selector. Destino: Permite desplegar y desactivar los destinos que no se deseen obtener datos. Columna : Permite obtener la totalidad de las opciones, Duración e Importe o desactivar alguna de ellas, en caso de querer obtener una información parcial.
Ejemplo empleando la misma tabla aplicada a una fecha determinada en este caso 26/09/01 A 1 2 3 4 5 6 7 8 9 10
B Fechas
C 26/09/01
D miércoles
Suma Suma de Valor alor Colu Column mna a Duración Importe Destino Celular 0:01:53 $ 0,10 Correo 1:09:26 $ 0,99 Internet 2:33:29 $ 1,13 Urbanas 0:02:34 $ 0,19 Total general 3:47:22 $ 2,41
Observando la tabla dinámica se puede apreciar en la celda D2, el día de la semana que corresponde a la fecha seleccionada, cosa que en la tabla anterior figura en blanco. Esto es un agregado que se crea en la celda respectiva con la incorporación de la siguiente función
D2=SI(C2=”(todas”;””;SI(DIASEM(C2;1)=1;”domingo”;SI(DIASEM(C2;1)=2;”lunes”; SI(DIASEM(C2;1)=3;“martes”;SI(DIASEM(C2;1)=4;”miércoles”; SI(DIASEM(C2;1)=5;”jueves”; SI(DIASEM(C2;1)=6;”viernes”;”sábado”)))))))
Otro dato importante de comprobar es la coincidencia entre las dos tablas ejecutadas por un lado empleando una lista compacta, en lo que respecta a totales generales, generales, e individuales individuales para cada destino y las tablas empleando la opción de Rango de consolidación múltiples, en este ultimo caso se selecciono la misma fecha que en el caso anterior
Actualizar la Tabla Dinámica En todos los casos de ejecución de tablas dinámicas vistos hasta el presente, de producirse alguna modificación en las listas o bases de datos, origen de la creación de la tabla, es necesario actualizarlas. Esta operación se realiza de 2 formas: 1° Seleccionar una celda de la tabla. Menú-Datos- Pulsar Actualizar datos
2° Clic sobre la tabla con el botón derecho y pulsar Actualizar datos
GRAFICO DINAMICO (con tabla dinámica ) Vamos a desarrolla desarrollarr otro punto referente referente a las posibilidad posibilidades es que presenta presenta el Asistente Asistente para tablas y gráficos dinámicos, como son los gráficos dinámicos a partir de una tabla dinámica. Para realizar el ejemplo vamos a emplear en primer lugar la tabla dinámica de totales realizada a través de Rangos de consolidación múltiple.
Ejecución 1° Seleccionar una celda de la tabla y pulsar una ves con el botón derecho del Mouse. Se despliega una lista donde se selecciona: Grafico dinámico
2° Pulsar sobre Grafico dinámico Excel automáticamente ofrece un grafico: Tipo estándar- tipo de grafico Columnas Subtipo de grafico: Columna apilada Como con este grafico no representa representa en su conjunto conjunto lo expresado en la tabla, debemos debemos cambiar el tipo de grafico.
1° Con el Mouse, pulsar sobre el grafico con el botón derecho, desplegándose una lista. 2° Seleccionar y pulsar sobre Tipo de grafico Adoptamos Tipo de grafico
Tipos personalizado personalizad o Líneas en dos ejes
Se obtiene un grafico de líneas con dos ejes de valores, uno de Duración y otro de
Importe
El aspecto del grafico luego se modifica a gusto. Sobre la línea de Importe se pulsa doble con el Mouse. Esta operación despliega en pantalla una serie de botones que corresponden a:
Formato de serie de datos Con el botón Tramas se modifica, Estilo y Grosor de la línea Con el el botón Rótulos de datos datos activar Mostrar valores valores Volcando cada valor en la interceptación correspondiente. Idéntica operación se realiza con la línea que representa Duración El grafico que se obtiene es el siguiente
Fechas (Todas)
Suma de Valor
14:24:00
$ 9,00 $ 8,42
12:00:00
$ 8,00
11:56:53
$ 7,00
09:36:00
$ 6,00 $ 5,03
$ 4,40
07:12:00
$ 5,00
Columna
Duracion $ 4,00
04:48:00 02:24:00
04:22:44
$ 2,59
$ 2,00
$ 1,00 00:29:42
00:00:00
01:19:44
00:35:32 Ce lula r
$ 3,00
Corre o
$ 1,00 $ 0,00
Inte rne t
Inte r-Urb
Urba na s
Destino
Podemos observar como los datos de Importe y Duración, se corresponden con el destino en el grafico, según se expresa en la tabla dinámica. También en el grafico se puede puede observar 3 botones, como sucede sucede en la tabla dinámica, dinámica, para producir el filtrado de datos, por eso el nombre de Grafico dinámico. Botón Fecha: Agrupa o selecciona una fecha determinada. Botón Columna: Permite graficar Duración e Importe, o elegir uno de ellos. Botón Destino: Destino: Permite todos todos o optar optar por uno de los destinos. destinos. Para Para obse observ rvar ar el comp compor orta tami mien ento to del del graf grafic ico, o, vamo vamos s a prod produc ucir ir el filt filtra rado do,, en correspondencia con el producido en la tabla dinámica, es decir, vamos a seleccionar la fecha 26/09/01 y producir el filtrado en el grafico. El resultado es el siguiente
Importe
Fechas 26/09/01
Suma de Valor
02:52:48
$ 1,20 $ 0,99
$ 1,13
02:24:00
$ 1,00 02:33:29
01:55:12
$ 0,80 Columna
01:26:24
$ 0,60
Importe
01:09:26 00:57:36 00:28:48 00:00:00
$ 0,40 $ 0,10
$ 0,19 $ 0,20 00:02:34 $ 0,00
00:01:53 Ce lula r
Corre o
Inte rne t
Urba na s
Destino
Como se puede observar se produce el filtrado y grafico, en correspondencia con la tabla dinámica. Al modificarse o agregarse algún dato, correspondiente a Duración o Importe en cualquier destino, se actualiza la tabla como ya se explico y el grafico automáticamente. Por lo general el grafico debe recibir retoques como sucedió en el primer caso.
Ejecutado por
Gonzalo H. Fernández
[email protected] Pcia Buenos Aires – Argentina
Duracion
