EXCEL AVANZADO

ADGG020PO

DOCENTE: PATRICIA PUENTE

Tel: 670 72 61 06
e-mail: ppmformacion@gmail.com

Docente: Patricia Puente. E-mail: ppmformacion@gmail.com

m 0
Unidad 5:
Estructuras de
control

1
Docente: Patricia Puente. E-mail: ppmformac
ion@gmail.com
 INDICE DE
1 CONTENIDOS
2. DOMINIO DE TABLAS DINÁMICAS...............................................................2

2.1 DISEÑO DE UNA TABLA DINÁMICA............................................................................ 2

2.2 PERSONALIZACIÓN DE ELEMENTOS..................................................................... 5
2.3 INCLUSIÓN DE CAMPOS ADICIONALES.................................................................. 7
2.4 INFORMES DE TABLA DINÁMICA......................................................................... 8
2.5 GRÁFICOS DINÁMICOS......................................................................................... 9
2.6 USAR DATOS DE TABLA DINÁMICA EN FÓRMULAS........................................................ 11

3. CONSOLIDACIÓN DE DATOS...................................................................12

Docente: Patricia Puente. E-mail:

0
 1. Introducción
Mediante las opciones vistas hasta ahora podemos crear automáticamente
totales o subtotales, agrupar la información o bien filtrarla.

Excel cuenta con un tipo de tabla, llamada dinámica, que combina toda esa
funcionalidad.

Las tablas dinámicas pueden actuar, además, como origen para la creación
de informes y gráficos dinámicos, en los que es posible seleccionar la
información que desea verse en cada momento.

Docente: Patricia Puente. E-mail:

1
 2 Dominio de
dinámicas.
2.1 Diseño de una tabla dinámica
Para crear una tabla dinámica tomaremos como punto de partida una
tabla de datos como la que ya hemos utilizado en ejemplos previos.
La tabla dinámica puede alojarse en otro lugar de la misma hoja o en una
hoja distinta añadida al libro a tal efecto. Hay que tener en cuenta que una
tabla dinámica no sólo resume y filtra los datos, sino que también altera su
disposición

La creación de una tabla dinámica, o un gráfico dinámico, se efectúa

mediante la opción Insertar>Tablas>Tabla dinámica, gestionándose
posteriormente mediante una serie de herramientas específicas (que
aparecen como dos nuevas fichas en la Cinta de opciones y las opciones de
los menús emergentes.

Para crearla, debemos tener un rango o una tabla de datos. Nos situamos
en cualquier celda de la tabla, y pulsamos la opción tabla dinámica desde
una tabla o rango.

Automáticamente Excel seleccionará

los datos a partir de los cuales se
creará la tabla dinámica. Veremos
la siguiente ventana emergente. En
el caso de que no sea un rango de
datos sino una tabla nos aparecerá el
nombre de la tabla.

Docente: Patricia Puente. E-mail: ppmformacion@gmail.com

2
 Podemos elegir si queremos crear la tabla dinámica en una nueva hoja o
bien
los datos originales, seleccionando la opción Hoja de cálculo existente.

En nuestro caso dejaremos los valores por defecto y pulsamos aceptar.

Veremos lo siguiente en una nueva hoja que se ha creado:

En la tabla, según puede verse, existen cuatro áreas bien

diferenciadas: página, columna, fila y datos. Estas áreas se corresponden
con los apartados Filtro de informe, Rótulos de columna, Rótulos de fila y
Valores, respectivamente, que hay en la parte inferior del panel de tareas
Lista de campos de tabla dinámica.

Desde allí podrás crear y configurar la tabla. Por ejemplo, desde este panel
podrás seleccionar los campos que quieras agregar al informe al hacer clic
sobre ellos.

A medida que vayas agregando elementos, estos se irán añadiendo a las

áreas de Filtros, Columnas, Filas o Valores.

Dependiendo del tipo de campo que selecciones, este se agregará a un

área predeterminada. Los campos no numéricos se indexarán al área
Filas, la información relacionada a fechas u horas irá a Columnas y los
campos numéricos estarán en el área de Valores.

Sin embargo, si quieres ubicar un campo en otra área a la que originalmente

Excel lo agrega, puedes arrastrarlo a donde quieras ubicarlo.

Puedes modificar la tabla cuantas veces quieras haciendo clic en los

campos que quieras seleccionar para verlos en la tabla o dejar de
seleccionarlos para quitarlos de la tabla.

Docente: Patricia Puente. E-mail:

3
En nuestro caso hemos creado la siguiente tabla, en la que vemos las
ventas

Los campos incluidos en la tabla dinámica pueden ser eliminados

fácilmente, basta con hacer clic en el elemento que los representa y
seleccionar la opción Quitar campo o bien arrastrarlos fuera de la tabla.
Igualmente, pueden ir de un área a otra de la tabla. Esta facilidad nos
permite reordenar la tabla tantas veces como necesitemos hasta obtener el
resultado que buscamos.

Docente: Patricia Puente. E-mail:

4
 2. Personalización de
Al generar la tabla dinámica Excel utiliza unos títulos y unos formatos por
defecto, además de generar unos totales que él cree apropiados según el
contenido de la tabla.

Éstos son parámetros que nosotros podemos

modificar a posteriori gracias a la opción
Configuración de campo que nos encontramos
en la Cinta de opciones, concretamente en
Herramientas de tabla
dinámica>Analizar>Campo activo, o bien en el
menú emergente de las celdas,
seleccionando la opción configuración
campo valor.

El aspecto de la ventana mostrada por esa

opción dependerá de la celda o celdas que
tengamos seleccionadas. Si nos situamos en el
título de uno de los subtotales creados
automáticamente en la tabla, por ejemplo, la
ventana nos permitirá modificar ese título y,
además, seleccionar el tipo de resumen que
deseamos o establecer el formato en que se
mostrarán los valores.

Veremos la siguiente ventana:

Docente: Patricia Puente. E-mail:

5
Desde aquí podemos elegir si queremos que el resumen de la
información

Además, podemos seleccionar la opción formato de número, para aplicar

el formato adecuado al campo, en nuestro caso aplicaremos un formato
moneda.

Vemos como todas las celdas de los campos se transforman

automáticamente a formato moneda:

Etiquetas de filaSuma de VENTAS Suma de MARGEN EMP

MARCELO GONZALEZ53.595,25 € TONY SANCHEZ57.597,20 € ANA GARCIA
JUAN PEREZ
Total

Mientras trabajamos sobre la tabla dinámica, podemos arrastrar y soltar

cualquier título de fila o columna para cambiar el orden en que aparecen
los datos, en este caso los países o los idiomas. Esta acción no
moverá únicamente la celda seleccionada, como ocurriría normalmente fuera
de una tabla dinámica, sino que afectará también a todos aquellos datos
asociados a esta categoría.

Asimismo, podemos colocarnos en una columna o una fila y utilizar los

botones de Herramientas de tabla dinámica>Ordenar para cambiar el
orden. En nuestro caso ordenamos la tabla por el campo margen empresa.

Docente: Patricia Puente. E-mail:

6
2.3 Inclusión de campos adicionales
Además de los campos que están en la tabla original de datos, en una
tabla dinámica podemos incluir también otros campos adicionales con
información obtenida, por ejemplo, a partir de una fórmula creada a tal
efecto.

En la ficha Analizar específica para la tabla dinámica existe un menú, llamado

Cálculos, con múltiples opciones.

Mediante Campo calculado y Elemento calculado podemos añadir a la

tabla un dato cualquiera, calculado a partir de una fórmula en la que
usaríamos los campos que originalmente había en la tabla.

En nuestro caso vamos a incluir una estimación de ventas para el próximo

año con un incremento del 8% sobre las ventas iniciales Para ello
utilizamos la opción Cálculos>Campo calculado para añadir un nuevo dato.

En la ventana introducimos el título del nuevo dato y la fórmula para

obtenerlo. En este caso, por ejemplo, prevemos que las ventas
se incrementarán en un 8% hasta el año 2022.

Veremos la siguiente ventana, y aplicaremos los siguientes datos:

Aceptamos y vemos como el campo se ha incorporado en la tabla.

Docente: Patricia Puente. E-mail:

7
2.4 Informes de tabla dinámica
Las tablas dinámicas son estructuras de datos que, como su propio nombre
indica, pueden modificarse dinámicamente para obtenerse diversos datos,
resúmenes, etc.

No son, sin embargo, el formato más adecuado para presentar la

información. Una vez que en la tabla disponemos de los datos que
deseamos mostrar, lo mejor es transformarlos para obtener un informe de
tabla dinámica.

Partiendo de la tabla dinámica que contiene los datos, usaríamos las opciones
de la ficha Herramientas de tabla dinámica>Diseño para dar formato al
informe. La galería Estilos de tabla dinámica nos ofrece varias
configuraciones predefinidas que podemos aplicar a la tabla.

Dichos estilos dependerán de las opciones que se encuentren marcadas en

Opciones de estilo de tabla dinámica. Sí, por ejemplo, no está marcada Filas
con bandas, entre los diseños no habrá estilos

El estilo de informe que seleccionamos afecta no sólo al formato de los

datos, sino también a los datos que se muestran. Mediante los menús
desplegables del apartado Diseño puede agregar y eliminar subtotales por
grupos, totales generales, insertar filas en blanco para separar grupos,

Docente: Patricia Puente. E-mail:

8
optar por un diseño compacto o esquemático del informe, etc.

Docente: Patricia Puente. E-mail:

9
2.5 Gráficos Dinámicos
Los datos numéricos son la base, pero una representación gráfica puede
hacer bastante más atractiva su presentación y más sencilla su
interpretación. Al tratarse de un gráfico basado en una tabla dinámica,
además, es posible rediseñarlo y reconfigurarlo tantas veces como
necesitemos.

Podemos insertar un gráfico directamente a partir de un rango de datos,

mediante la opción Insertar>Tablas>Tabla dinámica>Gráfico dinámico

O bien podemos tomar como origen una tabla dinámica existente., para ello
bastará con hacer clic en el botón Gráfico dinámico que hay en
Herramientas de tabla dinámica>Herramientas. Aparecerá la galería
de tipos de gráficos disponibles (elegiremos el que nos convenga y el gráfico
se insertará en la hoja.

También se hará visible el panel de tareas Panel de filtros del gráfico

dinámico, así como una serie de fichas adicionales en la Cinta de opciones.

Docente: Patricia Puente. E-mail:

10
Mostrando la lista de campos, el mismo panel que usábamos para diseñar
la
tabla dinámica, podemos añadir elementos de página, series de datos, etc.,

Por supuesto, podemos usar las habituales opciones de gráficos para

seleccionar el tipo, aspecto, leyendas, etc.

Cualquier dato o modificación que efectuemos en el gráfico tendrán un

reflejo inmediato en la tabla dinámica, y viceversa. Esto significa que
podemos, por ejemplo, añadir un nuevo campo a la tabla dinámica y ver cómo
aparece de forma inmediata en el gráfico. El gráfico en sí es dinámico.

Podemos seleccionar cualquier otro y ver cómo inmediatamente el gráfico se

actualiza. Lo mismo es aplicable para las series de datos que, en este caso,
son los continentes.

Docente: Patricia Puente. E-mail:

11
 2. Usar datos de tabla dinámica
fórmulas
La información que contiene la tabla dinámica está, en realidad, tomada de
otra tabla de datos, previamente introducida por nosotros.

Podríamos hacer referencia a dichos datos originales en las fórmulas

donde lo necesitemos, pero también puede ser útil componer fórmulas a
partir de los datos representados en cada momento en la tabla dinámica.

Lógicamente, dichas fórmulas se verían afectadas cada vez que se

manipulase la tabla dinámica.

Dado que la disposición de los datos en la tabla dinámica se puede ver

alterada en cualquier momento, ya sea por modificaciones en la lista de datos
original o por un cambio de configuración, no es lógico usar referencias
simples a las celdas, como se hace generalmente en las fórmulas.

En su lugar, existe una función específica para recuperar datos desde una
tabla dinámica: IMPORTARDATOSDINAMICOS.

Los parámetros indispensables que necesita esta función son dos: el

nombre del dato a recuperar, identificado por su título y una referencia que
le permita saber de qué tabla dinámica tiene que recuperarse.

Esta referencia estará, normalmente, compuesta del nombre de la hoja y una

referencia absoluta a cualquier celda de la tabla dinámica.

Docente: Patricia Puente. E-mail:

12
3. Consolidación de datos.
Imaginemos que después de recopilar la información sobre los comerciales
tenemos un libro de Excel donde cada hoja tiene la información detallada de
cada comercial por meses y necesitamos consolidar la información en una
sola hoja para poder crear nuestro informe.

Esto podemos hacerlo de forma sencilla utilizando el comando Consolidar

El botón Consolidar que se encuentra en la ficha Datos dentro del grupo
Herramientas de datos.

Se mostrará el cuadro de diálogo Consolidar en donde se deberá

especificar cada uno de los rangos de las diferentes hojas. Se puede
hacer clic en el botón de selección de referencia para seleccionar los
rangos fácilmente.

Una vez especificada la referencia se debe pulsar el botón Agregar. De la

misma manera se deben agregar cada una de las hojas hasta tener todas
las referencias incluidas.

Se pueden marcar los cuadros de selección de rótulos en la Fila superior y

en Columna Izquierda de manera que los datos sean presentados y
etiquetados adecuadamente.

Al pulsar el botón Aceptar, Excel realizará la consolidación de los datos y

los colocará en una nueva hoja de Excel. El resultado es consolidar en una

Docente: Patricia Puente. E-mail:

13
sola hoja la información que estaba distribuida en diferentes hojas de Excel

Docente: Patricia Puente. E-mail:

14