1.1. Tablas y Gráficas Dinámicas

1.1.1. Concepto:

Uno de los mayores retos en el mundo real es la necesidad de obtener informa-ción importante a partir de grandes cantidades datos. Las tablas y gráficas dinámicas proporcionan una manera fácil de resumir y analizar datos usan-do técnicas especiales de análisis cruzado.

Una tabla dinámica es una hoja de cálculo interactiva que resume rápidamente grandes cantidades de datos colocando los campos en diferente formato y con los cálculos que usted decida. En esta tabla podrá girar los encabezados de filas y columnas alrededor del área de los cálculos de los datos de origen y, con esto, obtener informes desde diferentes puntos de vista.

Existe un asistente que facilita la creación de tablas dinámicas y que contiene la opción para generar gráficas dinámicas para mostrar la relación entre la in-formación y no sólo cantidades muchas veces difíciles de comparar.

1.1.2. Asistente para tablas y gráficas dinámicas:

Esta herramienta le guiará en la creación de un informe de análisis avanzado con una tabla dinámica:

1. Seleccione sólo una celda en la lista o base de datos de Excel. 2. Cinta de opciones Insertar – Tablas dinámica… 3. Aparece el diálogo ‘Crear tabla dinámica’, para definir el rango de datos,

dónde se colocará el informe de tabla dinámica.

Excel automáticamente considera la región actual y propone su rango, que podemos modificar. Debemos asegurarnos que el rango contenga los encabezados de las columnas (campos).

La mayoría de las veces es preferible colocarla en una hoja de cálculo nueva, para no afectar información en una hoja existente.

4. Clic en “Aceptar”. Se crea una hoja con el esquema de las cuatro áreas de la tabla dinámica.

1.1.3. Diseño de la Tabla dinámica:

Una vez que el asistente generó el esquema de la tabla podemos diseñarla en función de nuestros requerimientos:

1. Trace su tabla dinámica arrastrando los campos desde la ‘Lista de campos de la tabla dinámica’ hasta una de las cuatro áreas en la hoja: * Diseño:

a) Páginas: Arriba de la tabla, para filtrar la información, similar a un Autofiltro.

b) Columnas: Área en la parte superior dentro de la tabla. c) Filas: Área en el lado izquierdo dentro de la tabla.

* Cálculos: d) Datos: Área donde se muestran los resultados de los cálculos.

También conocida como Eje. Los resultados dependerán del diseño aplicado, esto es, de los campos co-locados en las áreas de páginas, columnas y filas.

2. Puede rediseñar su tabla, arrastrando los campos de una a otra área. 3. Puede colocar más de un campo en cualquier área de la tabla dinámica. 4. Para eliminar un campo, simplemente arrástrelo fuera de la tabla o sobre él ha-

ga clic derecho – Quitar nombre del campo o desmárquela de la lista de campos.

1.1.4. Cinta de opciones "Opciones”:

Al usar el asistente o seleccionar una tabla o gráfica dinámica, aparece ésta:

Su funcionalidad se explica a lo largo de los siguientes apartados.

Nombre y Opciones de la tabla dinámica

Configuración del campo activo

Área de FILAS

Área de PÁGINAS

Área de COLUMNAS

Área de DATOS

Ocultar/Mostrar detalles (sólo en campos agrupados)

Crea un grupo con los elementos seleccionados (de un campo de diseño)

Crea un grupo de todo un campo numérico o de fecha.

Quita grupos

Ordena (incluso hacer un orden manual).

Actualizar tabla dinámica y ajustar rango origen

Borrar o Seleccionar elementos de la tabla

Moverla a otra hoja

Gráfica dinámica

Campos y Elementos calculados

Mostrar/Ocultar herramientas de la Tabla dinámica

1.1.5. Cinta de opciones "Diseño”:

Al usar el asistente o seleccionar una tabla o gráfica dinámica, aparece ésta:

Su funcionalidad se explica a lo largo de los siguientes apartados.

1.1.6. Agrupación por periodos:

La agrupación por periodos es particularmente importante ya que por omisión se muestran los valores del campo fecha de cada día, lo que ciertamente no es el objetivo de un informe de resumen directivo. Para lograr esto, una vez que haya arrastrado un campo Fecha a la tabla dinámica, sigua estos pasos:

1. En la celda con el título del campo de Fecha, clic derecho – Agrupar…

2. Las fechas que aparecen en ‘Automáticamente’, son la fecha mayor y la menor de la base de datos original. Puede redefinirlas manualmente.

3. En ‘Por’, seleccione el periodo por el que desea agrupar. Puede ser más de uno.

4. Aceptar. En la tabla las fechas se mostrarán agrupadas. Si seleccionó más de un agrupador de periodo, se generarán sub-campos del campo Fecha.

Quitar Agrupación: En la celda con el título del campo de Fecha, clic dere-cho – Desagrupar…

1.1.7. Formato a números:

Para aplicar formato numérico a los resultados de los campos en el área de “Datos”, seleccionar el rango y aplicar el formato de celda no es la forma más correcta, debido a que al modificar el diseño o actualizar la tabla se puede perder tal formato. Por ejemplo, suponga que colocó el campo Venta en el área de Datos con la función “Suma”, siga estos pasos:

Muestra /oculta Subtotales en campos agrupados

Muestra /oculta Totales generales

Separa cada elemento con una fila en blanco

Muestra /oculta Totales generales

Aplica o no formatos opcionales a los estilos, debe estar aplicado un ‘Estilo de tabla dinámica’.

Aplica formatos prediseñados, con una presentación profesional, para facilitar la lectura del informe.

1. Seleccione la celda con el título del campo “Suma de Venta”. 2. Siga una de estas dos opciones:

a. En la cinta de opciones ‘Opciones’ – ‘Configuración de campo’ , Aparece el diálogo “Campo de la tabla dinámica”.

b. Clic en el botón “Formato de número”. Muestra el diálogo “Formato de celdas”.

O a. En la celda con el título ‘Suma de Venta’, clic derecho – Formato de

número… Muestra el diálogo “Formato de celdas”.

3. Seleccione la categoría “Contabilidad” y Aceptar. 4. Si siguió la 1° opción, en el diálogo ‘Campo de la tabla dinámica’, Aceptar. Nota: este método es más sólido que dar formato de celdas, ya que se aplica al

campo completo de la tabla dinámica y no a las celdas de la hoja de cálculo. 1.1.8. Estilos de tabla dinámica:

Así como el formato a número dentro de una tabla dinámica, debe evitar el formato individual de celdas y de rangos debido a que al recalcularse o ac-tualizarse la tabla su diseño se vuelve a reorganizar cambiando por ejemplo su tamaño y consecuentemente el rango que ocupa en la hoja de cálculo. Pa-ra aplicar formato a toda la tabla lo mejor es aplicar estilos, siga estos pasos:

1. Seleccione cualquier celda de la tabla dinámica. 2. Cinta de opciones ‘Diseño’, del grupo ‘Estilo de tabla dinámica’, despligue

la lista de Estilos:

3. Seleccione el diseño de estilo que más coincida como requiere el informe. El formato se aplica. Si desea cambiarlo, repita los pasos.

1.1.9. Renombrar, Mover o Eliminar campos:

Las etiquetas que tienen los campos se basan en los nombres de campos de la base de datos origen, en ocasiones desearíamos cambiarlos por nombres más descriptivos o cortos (para reducir anchos de columna, por ejemplo).

A) Para nombrar un campo en la tabla dinámica: 1. Seleccione la celda con el título del campo por cambiar. 2. en la barra de fórmulas cambie el texto del campo.

Nota: esto se aplica sólo al campo de la tabla dinámica, que sigue estando vin-culada con el de la base de datos, y ésta no se afecta en lo absoluto.

B) Para mover un campo en la tabla dinámica:

En la ‘Lista de campos de tabla dinámica’, en su parte inferior, se encuen-tran las cuatro secciones del informe, simplemente arrastre el campo deseado desde su sección actual a la nueva.

C) Para quitar un campo en la tabla dinámica:

En la ‘Lista de campos de tabla dinámica’, en su parte superior, cada campo tiene un cuadro de verificación, sólo dé clic en él para desactivarlo.

1.1.10. Mostrar registros de un resultado:

Podemos obtener una instantánea (analítica) de los registros de detalle que forman un resultado en su tabla dinámica, por ejemplo, al revisar su informe le llama la atención por qué el resultado de los servicios de Internet en la zona 6 se dispara con respecto a las demás zonas, haga los siguiente:

En el resultado a revisar, dé doble clic a su celda.

Se genera una hoja de cálculo con una copia de los registros de la base de datos origen que forman tal resultado. Una vez que termine su revisión, esta hoja la puede guardar o eliminar, sin afectar la base de datos.

1.1.11. Actualizar la Tabla dinámica:

Al modificar los datos de la base de datos origen, la tabla dinámica no refleja automáticamente los cambios, para lograrlo haga lo siguiente:

A) Actualización de sólo valores:

Cinta Opciones – Actualizar; o <Alt +F5>, O,

En cualquier celda de la tabla dinámica, clic derecho – Actualizar.

Los cambios a los valores de la base de datos se reflejan en la tabla dinámica. Si es necesario, la tabla se rediseña cambiando su rango utilizado.

B) Actualización de registros: Si la modificación no es a valores sino que se agregaron o eliminaron registros,

debe redefinir el rango de la base de datos origen, siga los pasos siguientes: 1. Cinta Opciones – Cambiar origen de datos.

2. Actualice el rango de la base de datos, ya sea tecleándolo o arrastrándolo en la hoja, para considerar el cambio en la cantidad de registros.

3. Clic al botón ‘Finalizar’.

Los cambios al tamaño de la base de datos se reflejan en la tabla dinámica. Si es necesario, la tabla se rediseña cambiando su rango utilizado.

1.1.12. Filtrado de datos:

Así como en la base de datos o lista donde puede aplicar filtros automáticos di-rectamente a sus campos, en una tabla dinámica puede hacer lo mismo para cualquier campo de diseño —esto es, en el área de Fila, Columna o Pági-na— de la tabla dinámica.

A) Para filtrar un campo del área de Filas o Columnas: 1. Cada campo tiene una lista desplegable, similar al Autofiltro. 2. Despliegue el campo deseado y desmarque los elementos por excluir.

Estos son criterios múltiples aplicados a un mismo campo.

B) Para filtrar un campo del área de Páginas: 1. Use los campos de Página para filtrar toda la tabla dinámica, por ejemplo,

coloque el campo ‘Zona’ en el área de Páginas. 2. Despliegue su lista y seleccione la zona 3.

La tabla se rediseñará, considerando todas las demás zonas —Filas, Co-lumnas y Datos—. Nota: Por omisión, este filtrado es de criterio único. Si requiere que sea múltiple, marque la opción ‘Seleccionar varios elementos’.

1.1.13. Informe de páginas:

Esta herramienta guarda como informes separados en hojas de cálculo indivi-duales cada página de su tabla dinámica, por ejemplo, si tiene una tabla di-námica analizando las ventas por cada cliente–servicio y desea enviar por e-mail los informes por cada zona a los correspondientes gerentes, siga estos pasos:

Marque esta opción…

… Para poder seleccionar varias opciones.

1. Coloque el campo ‘Zona’ en el área de páginas. 2. Asegúrese que en su lista muestre ‘(Todas)’. 3. Cinta Opciones – gpo. Tabla dinámica – Opciones – ‘Mostrar páginas de

filtro de informe’.

4. En este diálogo, seleccione el campo ‘Zona’ y Aceptar.

Se generan hojas de cálculo con las tablas dinámicas para cada zona, las cuales puede mandar individualmente por e-mail a los respectivos gerentes.

1.1.14. Agrupación de datos:

Así como las fechas (por periodo) podemos agrupar campos de texto (por ca-tegorías) o campos numéricos (por rangos), por ejemplo en campo “Zona” en el área “Filas” de la tabla dinámica, siga estos pasos:

1. En el campo de “Zona”, seleccione las celdas con las zonas 1 y 2 (los ele-mentos a agrupar).

2. Clic derecho – Agrupar… Se genera el sub-campo “Zona2” —basado en el campo Zona— con el elemento “Grupo1”.

3. Repita para las zonas 3-4 y las zonas 5-6. Su tabla quedaría así:

Para hacer cálculos con los grupos, vea el tema “Subtotales”, más adelante. 1.1.15. Subtotales:

Excel automáticamente agrega filas de subtotales para cada grupo y de Total general a una tabla dinámica. Los subtotales calculan los resultado de los elementos agrupadores (esto es, los más externos) cuando existe más de un campo en el área de Filas o Columnas.

Seleccione los elementos a agrupar

Subtotales

Total General

Total General

A) Para mostrar u ocultar los Totales generales: 1. Cinta Opciones – gpo. Tabla dinámica – Opciones, ficha ‘Totales y filtros’. 2. Marque o desmarque “Mostrar totales generales de las filas”. 3. Marque o desmarque “Mostrar totales generales de las columnas”. 4. Aceptar.

B) Para mostrar u ocultar las filas o columnas de Subtotales: 1. Seleccione la celda del campo agrupador, por ejemplo: ‘Cliente’ agrupando

a ‘Servicios’. 2. En él, clic derecho – Subtotal nombre del campo

C) Para mostrar u ocultar los detalles de un grupo: 1. Para mostrar u ocultar los detalles de todos los elementos de un campo

agrupador, por ejemplo: Cliente agrupando a Servicios, seleccione el campo Cliente (agrupador) y en cinta Opciones, seleccione:

‘ Expandir todo el campo’ o

‘ Contraer todo el campo’. 2. Para mostrar u ocultar los detalles de un elemento en especial de un campo

agrupador, por ejemplo: ‘Dafi’ (del campo ‘Cliente’): a. Verifique que los botones +/- estén activos, si no: cinta Opciones –

gpo. Mostrar u ocultar – Botones +/-. b. Clic en el botón + para expandir o – para contraer.

Opcional: No es necesario que estén visibles los botones +/-, simple-mente dé doble clic en el elemento agrupador; se mostrarán u oculta-rán sus correspondientes Servicios (detalles) del cliente seleccionado.

1.1.16. Funciones de Cálculo:

Cada celda en el área de datos (cálculos) de una tabla dinámica contiene el re-sultado de un cálculo que resume los datos de la base de datos origen. Si el campo es numérico, por omisión se aplica la función SUMA; si el campo es de texto se aplica la función CUENTA.

Para cambiar la función de resumen, siga estos pasos: 1. Seleccione una celda con resultado, del campo requerido, en el área Datos.

2. Cinta Opciones – gpo. Campo activo – Configuración de campo; o en él clic derecho – Configuración de campo de valor. Aparece el diálogo ‘Configuración de campo de valor’.

Botón +/-

Cambie la función de resumen.

3. En la ficha ‘Resumir por:’, seleccione la nueva función de resumen deseada. 4. Aceptar.

La función sólo se aplica a este campo de Datos, debido a que puede haber más de un campo en el área Datos —incluso este mismo campo (de la base de datos) más de una vez—, puede usar una función de resumen diferente para cada campo.

1.1.17. Análisis avanzado:

Para obtener cálculos más sofisticados podemos cambiar el tipo de cálculo en los campos del área de datos, basándose en resultados de otras celdas en la misma área de Datos.

Para cambiar el tipo de cálculo de un campo de datos, siga estos pasos: 1. Seleccione una celda con resultado, del campo requerido, en el área Datos.

2. Cinta Opciones – gpo. Campo activo – Configuración de campo; o en él clic derecho – Configuración de campo de valor. Aparece el diálogo ‘Configuración de campo de valor’.

3. Ficha ‘Mostrar valores como’:

4. En la lista desplegable seleccione el tipo de cálculo que desee, por ejemplo: ‘% del total’. Algunos tipos de cálculo necesitan más información como el ‘Campo base’ y el ‘Elemento base’. [Vea tabla más abajo].

5. Aceptar. El informe (con la opción ‘% del total’) mostrará los porcentajes con res-pecto al gran total (esq. inf. der.), en vez de los valores de importe:

Los tipos de análisis y lo que devuelven, se enlistan a continuación (en orden de dificultad):

Análisis Resultado

1. % del total Presenta los datos en el área de datos como un porcentaje del total general de todos los datos del informe de tabla dinámica.

2. % de la fila Presenta todos los datos de cada fila como un porcentaje del total de cada fila.

3. % de la columna Presenta todos los datos de cada columna como porcentaje del total de cada columna.

4. Total en Visualiza los datos de elementos sucesivos como un total Acumula-do. Deberá seleccionar el campo (Base) cuyos elementos desee mos-trar en un total acumulado.

Cambie el tipo análisis entre los

datos.

Muestra % sobre el Gran total, en vez de importes.

5. Diferencia de Muestra todos los datos en el área de datos como la diferencia entre el valor del Campo base y del Elemento base especificados.

6. % De Muestra todos los datos en el área de datos como un porcentaje del valor del Campo base y del Elemento base especificados.

7. % de la diferencia de Muestra todos los datos en el área de datos como la diferencia entre el valor del Campo base y del Elemento base, pero presenta la dife-rencia como un porcentaje de los datos base.

8. Índice Presenta los datos utilizando los siguientes algoritmo: (Valor de Celda X Suma Total General) /

(Suma Total de Fila X Suma Total de Columna)

Para los tipo del 5 al 7, El campo base y el elemento base proporcionan los datos que se utilizan en el cálculo personalizado.

1.1.18. Campos Calculados:

Así como se generan sub-campos a partir de campos directos de la base de da-tos origen —y que se manejan en forma independiente—, como por ejem-plo el campo ‘Trimestres’ a partir del campo ‘Fecha’, también podemos agregar a la tabla dinámica campos personalizados con fórmulas que proce-sen valores de los campos de la base de datos origen.

Por ejemplo si en la base de datos existe el campo ‘Ventas’ (incluido el IVA), podemos crear un campo calculado ‘IVA’ para obtenerlo a partir de aquel. Siga estos pasos:

1. Seleccione una celda en el informe de tabla dinámica. 2. Cinta Opciones – gpo. Herramientas – Fórmulas – Campo calculado...

3. En el cuadro Nombre, teclee: ‘IVA’ 4. En el cuadro Fórmula, introduzca: ‘=’ 5. Utilice los datos del campo ‘Venta’ en la fórmula, seleccionándolo de la lista

‘Campos’ y, luego, clic en ‘Insertar campo’. 6. Complete la fórmula agregando: ‘/1.15*0.15’

La fórmula completo debe quedar así: ‘=Venta/1.15*0.15’ 7. Clic en el botón ‘Sumar’, para agregarlo. 8. Aceptar.

Campo calculado (agregado) ‘Suma de IVA’ en base a los importes de las ventas.

9. Si es necesario, mueva el campo calculado: arrastrando una de las celdas de encabezado (‘Suma de IVA’) a la posición deseada —antes o después de otros campos en área Datos— por ejemplo, arriba de ‘Suma de Venta’.

Nota: Estos campos se manejan igual que cualquier otro campo de la tabla di-námica, por ejemplo: puede cambiarle la función de resumen o el tipo de cálculo personalizado.

1.1.19. Gráficas dinámicas:

Una gráfica dinámica es una versión visual de la tabla dinámica. Las gráficas di-námicas se crean a partir de tabla dinámica y se colocan en una hoja de grá-fico nueva en el libro de trabajo. Al igual que las tabla las gráficas dinámicas tienen campos dinámicos que pueden arrastrarse desde al barra de herra-mientas y moverse alrededor del área de la gráfica. También puede modifi-car las funciones usadas para analizar datos en una gráfica dinámica.

Un informe de gráfica dinámica, tiene asociada una tabla dinámica. De hecho, la recomendación es que primero genere una tabla dinámica con el diseño deseado y, a partir de esta crear la gráfica dinámica. Siga estos pasos:

1. Una vez creada la tabla dinámica [Vea 2.2.2. y 2.2.3.], seleccione una celda dentro de ella, por ejemplo: las Ventas por Servicio – Trimestre en todas las Zonas:

2. Cinta Opciones – gpo. Herramientas – ‘Gráfico dinámico’.

3. Seleccione Grupo y Subgrupo: por ejemplo, ‘Columna’ y ‘Columna agru-pada 3D’, respectivamente.

4. Clic en Aceptar. Se inserta un objeto flotando sobre la hoja conteniendo la gráfica cuyas ba-rras son la representación gráfica de los datos numéricos de la tabla diná-mica asociada, y cualquier cambio hecho a esta gráfica se refleja en su tabla, o viceversa.

5. Rediseñe la gráfica: a. En la Leyenda, clic derecho – Formato de leyenda, ficha ‘Opciones de

leyenda’, marcar ‘Inferior’ y cerrar.

… y Subgrupo.

Seleccione Grupo…

b. Seleccione el Área del gráfico y en cinta Formato – Estilo visual – ‘Efecto sutil - Énfasis 6’ (naranja tenue).

c. En el Área del gráfico, clic derecho – Tamaño fuente = 12 y Negrita. 6. Muestre la cinta de opciones ‘Analizar’:

7. Asegúrese de que esté activo el botón ‘Filtro para gráfico dinámico’: aparece en la parte derecha el ‘Panel de filtros del gráfico dinámico’. La hoja debe tener una presentación similar a la siguiente:

Las áreas de la gráfica dinámica están asociadas a las áreas de la tabla dinámica: ‘Categorías’ se relaciona con ‘Filas’ y ‘Series’ con ‘Columnas’. Los campos de la gráfica dinámica se analizan por medio del ‘Panel de filtros del gráfico dinámico’, con la misma lógica que como se hace directamente en la tabla dinámica. Los cambios se reflejan en su tabla dinámica asociada y viceversa.

Área de Datos

Área de Páginas

Área de Categorías

Área de Series