TABLAS DINÁMICAS

Ficha insertar grupo tablas

Los informes de tablas dinámicas permiten al usuario resumir la información por categorías y subcategorías, totalizar los valores para facilitar la toma de decisiones y analizar los datos desde diferentes puntos de vista, permiten manipular grandes cantidades de datos y agregar nuevos campos con fórmulas personalizadas. Es muy ágil el manejo de los formatos para presentar comparaciones, facilitan la organización y el resumen de datos en bases de datos.

COMPONENTES DE LAS TABLAS DINÁMICAS

Campos de filas: Son los campos de base de datos ubicados en el área de Fila, que representan las categorías por las cuales se resume la información.

Campos de columnas: Son los campos de la base de datos ubicados en el área de Columnas, que representan las categorías por las cuales se resume la información

Campos de página: Son los campos de la base de datos ubicados en el área de Página, que permiten mostrar los registros que cumplen un determinado criterio, muy similar al manejo de autofiltros.

Campo de datos: Es el campo de la base de datos ubicado en el área de Datos que se va a totalizar, con las funciones para realizar totales. Normalmente, un campo de datos resume datos numéricos, pero también puede contener texto. Los valores numéricos de forma predeterminada utilizan la función de SUMA y los valores texto tienen como función predeterminada CONTAR.

Elementos calculados: Son aquellas subcategorías de un campo en el área de filas o columnas basadas en fórmulas creadas por los usuarios.

Campos calculados: Corresponden a los campos que se crean en el área de datos con fórmulas creadas por el usuario.

OBJETIVO DE UNA TABLA DINÁMICA

El objetivo de la tabla dinámica es aplicar herramientas de análisis para la toma de decisiones, (costos, presupuestos, inventarios, ventas) en bases de datos robustas, grandes o complejas donde la manipulación y administración de la información es compleja.

CREAR UNA TABLA DINÁMICA

Al crear un informe de tabla o gráfico dinámico, se conecta con un origen de datos y luego se especifica la ubicación del informe.

1. Seleccionar una celda de la base de datos.

2. En la ficha Insertar, en el grupo Tablas, hacer clic en Tabla dinámica y, a continuación, en Tabla dinámica.

3. En el cuadro Seleccione una tabla o un rango; si estaba ubicado en una celda de la base de datos, el rango se visualiza en este cuadro, si la ubicación es una celda vacía se debe seleccionar el rango de celdas o la referencia del nombre de tabla.

4. Si los datos de origen se encuentran en otro archivo en una fuente de conexión externa:

Hacer clic en Elegir conexión.

Se mostrará el cuadro de diálogo Conexiones existentes.

Seleccionar en la lista desplegable Mostrar, de la parte superior del cuadro de diálogo una de las categorías de conexiones existentes o, Todas las conexiones existentes (que es el valor predeterminado).

Seleccionar una conexión del cuadro de lista Seleccionar una conexión y hacer clic en Abrir.

5. En la parte inferior permite activar la ubicación de la tabla dinámica.

Para poner el informe de tabla dinámica en una hoja de cálculo nueva que empiece por la celda A1, hacer clic en Nueva hoja de cálculo.

Para poner el informe de tabla dinámica en una hoja de cálculo existente, seleccionar Hoja de cálculo existente y, a continuación, escribir la primera celda del rango de celdas donde se desea situar el informe de tabla dinámica.

6. Hacer clic en Aceptar.

Excel crea una tabla dinámica en blanco para insertar los campos en las diferentes ubicaciones.

Para ubicar los campos en la tabla dinámica se debe arrastrar los Encabezados de las columnas situados en la Lista de Campos de tabla dinámica con clic sostenido al área de diseño donde aparecen los campos de rótulos de fila, columna, o datos, dependiendo de la información requerida.

Ejemplo:

1. Del cuadro lista de campos, arrastrar el campo artículos a rótulos de fila parte inferior.

2. Del cuadro lista de campos, arrastrar el campo Ciudad a rótulos de columna.

3. Del cuadro lista de campos, arrastrar el campo cantidad a valores.

En la Lista de Campos el botón permite cambiar la forma de visualización de los campos y las áreas de la tabla dinámica.

Para organizar los campos una vez elaborada la tabla dinámica se puede dar clic derecho en el nombre de campo en una de las áreas y, a continuación, seleccionar la opción Mover y elegir una de las opciones siguientes:

MOVER CAMPOS EN UNA TABLA DINÁMICA

Subir Sube el campo una posición en el área.

Bajar Baja el campo una posición en el área.

Mover al principio Mueve el campo al principio del área.

Mover al final Mueve el campo al final del área.

Mover al filtro de informe Mueve el campo al área de filtro de informe.

MOVER CAMPOS EN UNA TABLA DINÁMICA

Mover a rótulos de fila Mueve el campo al área de etiquetas de fila.

Mover a rótulos de Mueve el campo al área de etiquetas decolumna columna.

Mover a valores Mueve el campo al área de valores.

CONFIGURACIÓN DE CAMPO

Para cambiar el nombre de un campo se debe situar sobre el área de diseño y en la celda del título del campo, ubicarse en la barra de fórmulas y escribir el nuevo nombre o en la ficha Opciones, en el grupo Campo activo, hacer clic en el cuadro de texto Campo activo, escribir el nuevo nombre.

Además se puede modificar el nombre situándose en la celda del nombre del campo y con clic derecho seleccionar la opción Configuración de campo; esta posibilidad permite controlar las diversas configuraciones de formato, impresión, subtotales y filtros de los campos de un informe de tabla dinámica. Así mismo obtendremos el cuadro Configuración de campo en la ficha Opciones, en el grupo Campo activo al dar doble clic sobre un nombre de un campo.

El cuadro Configuración de campo cambia su aspecto dependiendo de la ubicación del campo en el diseño de la tabla dinámica.

Para campos de Fila, Columna o Página:

Nombre personalizado: Muestra el nombre del campo actual en el informe de tabla dinámica, o el nombre del origen si no hay ningún nombre personalizado

Subtotales

Automáticos: Utiliza la función predeterminada para el campo.

Ninguno: Muestra el campo sin ningún subtotal.

Personalizados: Habilita la selección de una de las funciones siguientes como subtotal.

FUNCIÓN DESCRIPCIÓN

SUMA La suma de los valores. Es la función predeterminada de los valores numéricos.

CONTAR Cuenta las celdas que tengan un valor ya sea numérico o texto

PROMEDIO El promedio de los valores.

MÁX El valor máximo.

MÍN El valor mínimo.

PRODUCTO El producto de los valores.

CONTAR Cuenta las celdas que tengan un valor numérico.NÚMEROS

DESVEST Un cálculo de la desviación estándar de una población, donde la muestra es un subconjunto de toda la población.

Filtros:

Incluir nuevos elementos en el filtro manual:

Activar o desactivar esta casilla de verificación para incluir o excluir nuevos elementos en un informe de tabla dinámica con un filtro aplicado.

Diseño e impresión

Mostrar etiquetas de elementos en formato de esquema: Activar esta casilla de verificación para ver los elementos de los campos en formato de esquema.

Mostrar elementos del campo siguiente en la misma columna (forma compacta): Activar o desactivar esta casilla de verificación para mostrar u ocultar los rótulos del campo siguiente en la misma columna de forma compacta.

Mostrar subtotales en la parte superior de cada grupo: Si se habilita esta casilla de verificación permite mostrar los subtotales en la parte superior de cada grupo.

Mostrar etiquetas de elementos en formato tabular: Active esta casilla de verificación para ver los elementos de los campos en formato tabular. Esta configuración sólo afecta a los campos situados en el área de rótulos de fila.

Insertar línea en blanco después de cada elemento: Activar esta casilla de verificación para insertar una línea en blanco a continuación de cada elemento y aumentar el espaciado de elementos como los subtotales.

Mostrar elementos sin datos: Al activar esta casilla de verificación muestra los elementos que no contengan datos.

Para campos de Valores

Nombre de origen:

Indicar el nombre del campo en el origen de datos.

Nombre personalizado:

Muestra el nombre del campo actual en el informe de tabla dinámica, o el nombre del origen si no hay ningún nombre personalizado.

Resumir Por muestra:Representa la función utilizada para el resumen de los datos en los campos de valor, si se desea modificar, se debe seleccionar nuevamente la función.

D E FIN IR CAMP O S E N T É RM INO S D E %

FUNCIÓN RESULTADO

DIFERENCIA DE Muestra los valores como la diferencia del valor de

Elemento base en Campo base.

% DE Muestra los valores como un porcentaje del valor de

Elemento base en Campo base.

% DE LA Muestra los valores como la diferencia de porcentaje

DIFERENCIA DE del valor de Elemento base en Campo base.

TOTAL EN Muestra los valores de elementos sucesivos en

Campo base como un total.

% DE LA FILA Muestra el valor de cada fila o categoría como un porcentaje del total de la fila o categoría.

% DE LA Muestra todos los valores de cada columna ó serie

COLUMNA como un porcentaje del total de la columna ó serie.

Muestra los valores como un porcentaje del total% DEL TOTAL general de todos los valores o puntos de datos en el

informe.

ÍNDICE Calcula los valores como se indica a continuación:

Función Resultado

((valor en celda) x (Suma total de sumas totales)) / ((Suma total de fila) x (Suma total de columna))

Ejercicio:Determinar en términos de %, las ventas por ciudad, para cada artículo.

1. En la tabla dinámica sobre el campo cantidad que se encuentra en la sección valor, hacer clic sobre la flecha desplegable.

2. Del menú contextual seleccionar configuración del campo valor.

3. En la ventana configuración del campo valor, hacer clic sobre la pestaña mostrar valores como.

4. De la lista desplegableseleccionar % de la fila.

5. Hacer clic sobre el botón Aceptar.

De esta forma la tabla dinámica esta en términos de porcentaje (%) determinando que del 100% de los discos duros, en Bogotá se ha vendido el 44.44%

Agrupar y desagrupar datos

Esta opción le permite agrupar por números o textos una sección de la tabla dinámica.

Desagrupar celdas

1. Seleccionar todo el rango de celdas o clic en el rótulo del campo artículos2.

2. Hacer clic derecho del mouse y seleccionar del menú desagrupar o en la ficha Opciones dar clic en Desagrupar que se encuentra en el grupo de opciones Agrupar.

Se pueden agrupar los elementos de los campos para generar nuevos subconjuntos de valores de resumen adicionales. Los niveles de agrupamiento varían de acuerdo con el tipo de datos que se utiliza:

Agrupar elementos numéricos

1. Seleccionar el campo numérico en el informe de tabla de dinámica que se desea agrupar.

2. En la ficha Opciones, en el grupo Agrupar, hacer clic en Agrupar campos.

3. En el cuadro Comenzar en, escribir el primer elemento que se desee agrupar.

4. En el cuadro Terminar en, escribir el último elemento que se desee agrupar.

5. En el cuadro Por, escribir un número que represente el intervalo incluido en cada grupo.

Agrupar fechas u horas

1. Seleccionar el campo de fecha u hora en el informe de tabla dinámica que se desea agrupar.

2. En la ficha Opciones, en el grupo Agrupar, hacer clic en Agrupar campos.

3. Escribir la primera hora o fecha que se desee agrupar en el cuadroComenzar en y la última en el cuadro Terminar en.

4. En el cuadro Por, hacer clic en uno o más períodos de tiempo para los grupos. La opción de días en la parte inferior permite determinar el Número exacto.

AGRUPAR ELEMENTOS SELECCIONADOS

Seleccionar los elementos del informe de tabla dinámica que se deseen agrupar; para ello, hacer clic en los elementos y arrastrarlos o presionar la tecla <Ctrl> o MAYÚS mientras se hace clic en los elementos.

En la ficha Opciones, en el grupo Agrupar, hacer clic en Agrupar selección.

Ejemplo

1. En la tabla dinámica seleccionar el rango de celdas que corresponde a memorias.

2. Hacer clic derecho del mouse y seleccionar Agrupar, o en la ficha

Opciones dar clic en Agrupar selección que se encuentra en el grupo de opciones Agrupar.

3. Se crea un nuevo campo a la izquierda llamado artículos2, y un grupo llamado Grupo1. Cambiar el rótulo artículos2 por Tipo Artículo, y el rótulo Grupo 1 por Memorias simplemente ubicando el cursor en la celda con el rótulo y sobrescribir.

Desagrupar elementos

Seleccionar los desagrupar.

elementos que se deseen

En la ficha Opciones, en el grupo Agrupar, hacer clic en

Desagrupar. También estas opciones de agrupamiento se

pueden realizar dando clicderecho sobre el campo a agrupar o desagrupar.

MOSTRAR U OCULTAR LOS

DETALLES DE UN INFORME

Cuando existen diferentes categorías en los campos de fila o en los campos de columna, es posible expandir o contraer hasta cualquier nivel de detalle de los datos, e incluso todos los niveles en una sola operación. Si solo se tiene un campo, la opción Expandir, permite activar nuevos campos en el área correspondiente.

Para expandir o contraer un campo se puede ubicar en la Ficha Opciones de Tablas Dinámicas, en el Grupo Campo Activo y seleccionar la opción Expandir todo el Campo o Contraer todo el campo según corresponda. También es viable si se da doble clic sobre la categoría del elemento situado en el área de fila o columna o utilizando el clic derecho del mouse sobre el elemento y seleccionar la opción Expandir/Contraer con las siguientes alternativas:

Para ver los detalles del elemento actual, hacer clic en

Expandir. Para ocultar los detalles del elemento actual,

hacer clic en Contraer.

Para ver los detalles de todos los elementos de un campo, hacer clic enExpandir todo el campo.

Para ocultar los detalles de todos los elementos de un campo, hacer clic en Contraer todo el campo.

Para ver un nivel de detalle posterior al siguiente nivel, hacer clic enExpandir hasta "<nombre del campo>".

Para ocultar un nivel de detalle posterior al siguiente nivel, hacer clic enOcultar hasta "<nombre del campo>".

FORMATOS DE TABLA

DINÁMICA

Si se desea aplicar un formato a la tabla dinámica, estando en una celda del área del diseño, hacer clic en la ficha Inicio, del grupo Estilos y en la opción Dar formato como tabla y seleccionar un modelo.

Para dar un formato al área de valores se puede utilizar el cuadro Configuración de Campo en la opción Formato de Número, si se está ubicado en una celda de esta área, o también es posible seleccionar las celdas a formatear y en la ficha Inicio en el grupo Estilos, seleccionar la opción Estilos de Celdas.

ORDENAR CAMPOS EN TABLAS DINÁMICAS

La opción ordenar, permite organizar la información de acuerdo al tipo de datos del campo, ubicado en cualquier área de la tabla dinámica.

Así mismo la opción Ordenar activa un cuadro de diálogo que permite organizar los elementos de un nivel en una forma que sean fáciles de entender.

En la ficha Opciones, en el grupo Ordenar, realizar uno de los siguientes procedimientos:

Se debe ubicar sobre el área a organizar, luego dar clic en los

íconos Orden ascendente u Orden descendente para ordenar un campo de la tabla dinámica. De acuerdo al tipo de datos se organizan los datos en la tabla.

Para organizar también se puede utilizar la ficha Opciones, en el grupo Ordenar, para esto se deben realizar uno de los siguientes procedimientos:

Si el campo es de tipo Texto

Para ordenar en orden alfanumérico ascendente, hacer clic enOrdenar de A a Z.Para ordenar en orden alfanumérico descendente, hacer clic enOrdenar de Z a A.

Si el campo es de tipo Numérico

Para ordenar en orden alfanumérico ascendente, hacer clic enOrdenar de menor a mayor.Para ordenar en orden alfanumérico descendente, hacer clic enOrdenar de mayor a menor.

Si el campo es Fechas y Horas

Para ordenar en orden alfanumérico ascendente, hacer clic enOrdenar de más antiguos a más recientes.Para ordenar en orden alfanumérico ascendente, hacer clic enOrdenar de más recientes a más antiguos.

ACTUALIZAR TABLA DINÁMICA

Esta opción permite actualizar el contenido de la tabla dinámica para que refleje los cambios en los datos de origen correspondiente. Si la tabla dinámica está basada en datos externos, al actualizarla se ejecutará la consulta para recuperar los datos nuevos o los que hayan cambiado.

Con el teclado se pueden presionar las teclas <Alt> <F5> para actualizar los datos.

CAMBIAR ORIGEN DE DATOS

Si se aumentan registros o eliminan registros en la base de datos que origina la tabla dinámica, se debe volver a seleccionar el origen de datos.

Para modificar el rango de origen de los datos se debe:

- En la ficha Opciones, en el grupo Datos, hacer clic en Cambiar origen de datos y luego en Cambiar origen de datos. Aparecerá el cuadro de diálogo Cambiar origen de datos de tabla dinámica.

Para usar una tabla o rango de celdas diferente de Microsoft Office Excel, hacer clic en Seleccione una tabla o rango y luego especificar la primera celda en el cuadro de texto Tabla o rango.

- Para usar una conexión diferente, hacer clic en Utilice una fuente de datos externa y luego en Elegir conexión.

- Se mostrará el cuadro de diálogo Conexiones existentes.

- En la lista desplegable Mostrar, situada en la parte superior del cuadro de diálogo, seleccionar la categoría de conexiones para la que desea elegir una conexión o seleccionar Todas las conexiones existentes (opción predeterminada).

- Seleccionar una conexión del cuadro de lista Seleccione una conexión y, a continuación, hacer clic en Abrir.

ACCIONES EN LA TABLA DINÁMICA

Si se requiere quitar todos los campos ubicados en el diseño de la tabla dinámica, se debe dar clic en la ficha Opciones, en el grupo Acciones, en la opción Borrar Todo. Si en el campo de página hay filtros activos la opción Borrar Filtros del Grupo Acciones en la ficha Opciones, quita los filtros mostrando nuevamente todos los registros en el informe de tabla dinámica.

Para seleccionar información de una tabla dinámica se debe:

Situar el puntero del mouse en la esquina superior del campo hasta que cambie a una flecha descendente y, a continuación, hacer clic.

En la ficha Opciones, en el grupo Acciones, hacer clic en Seleccionar y, a continuación, hacer clic en lo que se desee seleccionar:

Etiquetas y datos para seleccionar ambos.

Datos para seleccionar sólo los valores de los elementos.

Etiquetas para seleccionar sólo los rótulos de los elementos.

Si se desea cambiar la posición de la tabla dinámica se debe activar la Ficha Opciones en el Grupo Acciones y opción Mover Tabla dinámica, donde en el cuadro de diálogo se puede cambiar la celda de inicio de la tabla dinámica si se quiere ubicar en la misma hoja, o seleccionar la opción Hoja nueva, para insertar una nueva hoja con la tabla dinámica.

ELEMENTOS Y CAMPOS CALCULADOS EN TABLAS DINÁMICAS

Elemento Calculado: Es una categoría adicional de un campo de una tabla dinámica, situado en el área de filas o columnas que utiliza una fórmula que haya creado el usuario.

Campos Calculados: Es un campo ubicado en el área de datos en la tabla dinámica que utiliza una fórmula que se haya creado. Los campos calculados pueden ejecutar cálculos utilizando el contenido de otros campos de la tabla dinámica.

Para crear un elemento calculado a un campo:

1. Si los elementos del campo están agrupados, en la fichaOpciones, en el grupo Agrupar, hacer clic en Desagrupar.

2. Hacer clic en el campo donde se desee agregar el elemento calculado.3. En la ficha Opciones, en el grupo Herramientas, hacer clic en

Fórmulas y, seleccionar la opción Elemento calculado.4. En el cuadro Nombre, escribir un nombre para el elemento calculado.

5. En el cuadro Fórmula, escribir la fórmula para el elemento, en la cual se pueden utilizar operadores y expresiones como en la hoja de cálculo iniciando con el =. Pueden utilizarse constantes y hacer referencia a los datos de la tabla dinámica (elementos), pero no pueden utilizarse referencias de celda o nombres, posteriormente hacer clic en Agregar.

Para crear un campo calculado a un campo:

1. Hacer clic sobre un campo en el área de datos del informe de tabla dinámica.

2. En la ficha Opciones, en el grupo Herramientas, hacer clic en

Fórmulas y, a continuación, hacer clic en Campo calculado.

2. En el cuadro Nombre, escribir un nombre para el campo.

3. En el cuadro Fórmula, escribir la fórmula para el campo, en las cuales se pueden utilizar operadores y expresiones como en la hoja de cálculo iniciando con el =. Pueden utilizarse constantes y hacer referencia a los datos de la tabla dinámica (elementos), pero no pueden utilizarse referencias de celda ó nombres.

5. Hacer clic en Agregar.

Para modificar un elemento o campo calculado, hacer clic en Fórmulas que se encuentra en el grupo Herramientas de la ficha Opciones, a continuación, hacer clic en Elemento o Campo calculado, en el cuadro nombre seleccionar de la lista el nombre del campo a modificar y luego en el campo Fórmula cambiar la fórmula.

Si se tienen varios elementos o fórmulas calculados, ajustar el orden de cálculo haciendo lo siguiente:

1. En la ficha Opciones, en el grupo Herramientas, hacer clic en

Fórmulas y, a continuación, hacer clic en Orden de resolución.

2. Hacer clic en una fórmula y, posteriormente, hacer clic en Subiro en

Bajar.

Para mostrar una lista de todas las fórmulas utilizadas en el informe de tabla dinámica actual.

En la ficha Opciones, en el grupo Herramientas, hacer clic enFórmulas y, a continuación, hacer clic en Crear lista de fórmulas.

En Opciones de Tabla dinámica el último grupo Mostrar u Ocultar permite activar o desactivar la lista de campos, los botones para expandir o contraer elementos y los rótulos de los campos.

GENERAR UN GRÁFICO DINÁMICO

Los gráficos dinámicos se pueden basar sobre tablas dinámicas para observar los informes de una manera diferente, más clara y concisa.

1. Ubicarse en la base de datos.

2. En la ficha insertar, en el grupo tablas, seleccionar de la flecha desplegable tabla dinámica, gráfico dinámico.

3. Aparecerá una ventana en la cual se puede seleccionar el origen de lo que se desea representar o bien de una base dedatos externa, seleccionar el botón de opción.

Seleccionar una tabla o rango y el botón de opción nueva hoja de cálculo y hacer clic sobre el botón Aceptar.

4. Arrastrar el campo cantidad al área de valores.5. Arrastrar el campo artículos al área de campos de ejes x.