MODULO 2

MODULO 2

MÓDULO 1

Presentación 1
Introducción y Orientación para su estudio 1
Objetivos 1
Tablas 2
Tablas dinámicas 4
Campos calculados 11
Actualizar una tabla dinámica 13
Segmentaciones 14
Gráficos dinámicos 16
Tableros de control (Dashboard) 22
MODULO 2

MÓDULO 1

La Unidad 2 comienza con la definición y características de las tablas, explicando

la diferencia entre trabajar con ellas y con rangos. Luego se trabajará con tablas
dinámicas, se comenzará desde lo básico a fin de poder comprender los temas
siguientes que será campos calculados, actualización de datos, segmentaciones
y gráficos dinámicos.

Por último haremos una introducción a los tableros de control, comúnmente

denominados Dashboard, describiremos sus características principales y los
pasos a tener en cuenta para el desarrollo de nuestros tableros.

Luego del desarrollo de todos los temas, podrá resolver un serie de ejercicios
para intensificar su práctica. Considérelos como una autoevaluación.

Recuerde que los temas siguen una secuencia que deberá respetar; a la vez,
esta Unidad es la base de las unidades siguientes. No son unidades
independientes, sino complementarias, cada una necesita de los temas
incluidos en la anterior.

• Incorporar los conceptos de tablas, tablas dinámicas y tableros de control.

• Llevar a la practica los contenidos incorporando el uso de los segmentadores.
• Resolver los ejercicios de clase.

01
MODULO 2

MÓDULO 1

Las tablas de Excel son un rango de celdas que contiene datos y su fila superior
es una fila de encabezados que describen el contenido de cada columna.

Cada fila de una tabla corresponde a una sola entidad, por ejemplo, una tabla
puede contener información de los empleados de una empresa y por lo tanto
cada fila hará referencia a un empleado diferente.

Las tablas tienen encabezados en la parte superior y cada uno de ellos describe
la información contenida en cada columna. Así por ejemplo, nuestra tabla de
empleados tiene los encabezados ID (de empleado), Nombre, Apellido, Salario,
Departamento.

Esta misma tabla se puede crear con cualquier versión de Excel, sin embargo los
beneficios los veremos cuando convertimos este rango de celdas en una tabla
“oficial”. Para crear una tabla de Excel debemos elegir la ficha Insertar y
posteriormente pulsar el botón Tabla. Esto convertirá el rango de celdas en una
tabla de Excel.

02
MODULO 2

MÓDULO
Una vez1 que hemos convertido un rango de celdas en una tabla, Excel podrá
responder de una manera más inteligente a las acciones que hagamos sobre la
tabla. Por ejemplo, si creamos un gráfico de Excel basado en una tabla, al
momento de agregar nueva información el gráfico será actualizado
automáticamente con las nuevas filas de la tabla.

A continuación se detallan algunas características de las tablas de Excel que las

hacen diferentes a los rangos de celdas:

1. Al activar cualquier celda de la tabla se habilitará la ficha contextual llamada

Herramientas de tabla la cual contendrá comandos adicionales para trabajar
con la tabla.

2. Se puede aplicar un estilo a la tabla con tan solo elegirlo de la galería de

estilos.

3. Cada encabezado de columna contiene una lista desplegable la cual

podemos utilizar para ordenar o filtrar los datos de la tabla.

4. Si tenemos una tabla que contiene muchas filas y movemos la barra lateral
derecha de hacia abajo, en lugar de que los encabezados de la tabla
desaparezcan, Excel remplaza automáticamente las letras de las columnas
por los encabezados de la tabla.

5. Las tablas soportan referencias estructuradas lo que significa que en lugar

de utilizar referencias de celdas, podemos utilizar el nombre de la tabla y de
los encabezados.

6. La esquina inferior derecha de una tabla tiene una pequeña flecha que nos
permite agregar fácilmente una nueva fila o una nueva columna. Solamente
debemos arrastrar dicha esquina hacia abajo o hacia la derecha.

03
MODULO 2

MÓDULO 1

¿Qué es una tabla dinámica?

Una tabla dinámica es una de las herramientas más poderosas de Excel,

permiten resumir y analizar fácilmente grandes cantidades de información con
tan sólo arrastrar y soltar las diferentes columnas que formarán el reporte.

Las tablas dinámicas en Excel reciben su nombre por su capacidad de cambiar

dinámicamente la información agrupada con tan solo rotar las columnas o filas
de la tabla.

Funcionamiento

Las tablas dinámicas en Excel permiten agrupar datos en una gran cantidad de
maneras diferentes para poder obtener la información que necesitamos.
Supongamos que tienes una tabla de datos que contiene mucha información
sobre las ventas de la compañía entre las cuales se encuentra una columna con
los productos de la empresa, también la ciudad donde se vende y las ventas
correspondientes para cada ciudad.

Te han solicitado un reporte con el total de ventas por ciudad y el total de ventas
por producto. Así que lo que necesitas hacer es sumar las ventas para cada
ciudad y sumar las ventas de cada producto para obtener el reporte. En lugar de
utilizar fórmulas podemos utilizar una tabla dinámica para obtener el resultado
deseado.

04
MODULO 2

Una tabla dinámica nos permite hacer una comparación entre diferentes
MÓDULO 1
columnas de una tabla. Puedes imaginarte una tabla dinámica de la siguiente
manera:

Lo primero que debemos hacer es especificar los campos de nuestra tabla de

datos que vamos a comparar. Elegimos las ciudades como las filas de
nuestra tabla dinámica:

Excel tomará todos los valores de ciudades de nuestra tabla de datos y los
agrupará en la tabla dinámica, es decir, colocará los valores únicos de la
columna de datos eliminando las repeticiones.

05
MODULO 2

❑ Ahora hacemos
lo mismo para
especificar las
columnas de la
tabla dinámica.

MÓDULO 1
❑ Finalmente
elegimos una
columna de
valores
numéricos que
serán calculados
y resumidos en
la tabla dinámica:

❑ Así tendrás un reporte que

compara las ciudades y los
productos y para cada
combinación obtendrás el
total de ventas. Lo más
importante que quiero
transmitir con este artículo
es que las tablas
dinámicas te permiten
elegir entre todas las
columnas de una tabla de
datos y hacer
comparaciones entre ellas
para poder realizar un
buen análisis de la
información.

06
MODULO 2

1) Haz clic sobre cualquier celda de la tabla de datos que se desea considerar en la
nueva tabla dinámica

MÓDULO 1

2) Ahora selecciona el comando Tabla dinámica que se encuentra dentro del grupo
Tablas de la ficha Insertar.

Se mostrará el cuadro de diálogo Crear tabla dinámica. Si es necesario podrás

ajustar el rango de datos que se considerará en la tabla dinámica.

07
MODULO 2

❑ En este mismo cuadro de

diálogo se puede elegir si se
desea colocar la tabla
dinámica en una nueva hoja
de Excel o en una ya
existente. Haz clic en el
botón Aceptar y se creará la
nueva tabla dinámica.

MÓDULO 1

❑ Excel agregará en
la parte izquierda
del libro la tabla
dinámica y en la
parte derecha la
lista de campos.
Esta lista de
campos está
dividida en dos
secciones,
primero la lista de
todos los campos
de los cuales
podremos elegir y
por debajo una
zona a donde
arrastraremos los
campos que
darán forma al
reporte ya sea
como columnas,
filas, valores o
como un filtro.

08
MODULO 2

❑ Para completar la tabla

dinámica debemos arrastrar los
campos al área correspondiente.
Siguiendo el ejemplo, colocaré como
columna el campo Producto y como
fila al campo Ciudad. Finalmente
como valores colocaré el campo
Ventas.

IMPORTANTE: No existe una forma

particular de ordenar los valores
en columnas y filas, pero lo
MÓDULO 1 importante es que el resultado de
la tabla dinámica sea fácil de
visualizar.

De manera predeterminada Excel aplica la función SUMA a los valores y la tabla

dinámica que resulta después de hacer esta configuración es la siguiente:

09
MODULO 2

Justo cuando se ha creado una tabla dinámica se muestra en la parte derecha

de la hoja la lista de campos disponibles y por debajo las áreas donde podemos
arrastrar dichos campos. Estas áreas denotan cada una de las partes de una
tabla dinámica.

MÓDULO 1

❑FILTROS: Los campos que coloques en esta área crearán filtros para la tabla
dinámica a través de los cuales podrás restringir la información que ves en
pantalla. Estos filtros son adicionales a los que se pueden hacer entre las
columnas y filas especificadas.

❑COLUMNAS: Esta área contiene los campos que se mostrarán como columnas
de la tabla dinámica.

❑FILAS: Contiene los campos que determinan las filas de la tabla dinámica.

❑VALORES: Son los campos que se colocarán como las “celdas” de la tabla
dinámica y que serán totalizados para cada columna y fila.

Una vez especificados los campos para cada una de las áreas, la tabla
dinámica cobra vida. Puedes tener una tabla dinámica funcional con tan solo
especificar las columnas, filas y valores. Los filtros son solamente una
herramienta para mejorar el análisis sobre los datos de la tabla dinámica.

10
MODULO 2

A estas alturas comprendemos que las tablas dinámicas son excelentes porque
podemos crear diferentes tipos de informes en unos segundos. Sin embargo,
pero a veces seguimos pensando: “Falta esto o aquello”, cosas que con algunas
fórmulas de Excel sería posible resolver.
Esto es posible utilizando la herramienta de campos calculados.

Veamos el siguiente ejemplo:

Disponemos de la siguiente tabla, donde tenemos los campos Valor e Interés.

Nuestro objetivo es hacer su suma.
MÓDULO 1

Nuestra tabla dinámica ya está creada y con el Valor e Interés respectivos, pero
queremos el Valor Total. Luego insertemos una fórmula en la tabla dinámica de
Excel para agregar este valor:

11
MODULO 2

En la pestaña Analizar, haga clic en Campos, Elementos y Conjuntos y luego en

Campo Calculado:

En Nombre
MÓDULO 1 escribiremos el nombre como nos gustaría que se llamara nuestra
columna. En este caso le daremos el nombre Valor Total. Como queremos
sumar Valor + Interés, hagamos esto en el campo Fórmula: haga doble clic en el
campo Valor, escriba el signo + y haga doble clic en el campo Intereses. Presiona
Aceptar:

Si no desea que sigan apareciendo los valores de Valor e Intereses, sino solo el
Valor Total, puede eliminarlos de Valor:

12
MODULO 2

Nuestro resultado:

MÓDULO 1
Supongamos que hemos creado nuestra tabla dinámica y agregamos campos
calculados, pero en nuestra base agregaron nuevos datos y queremos que
queden reflejados en nuestra tabla dinámica.

Deberemos ir al menú Análisis de tabla dinámica donde tenemos un botón

llamado “Actualizar” en el que podemos hacer clic en él o elegir la opción
“Actualizar todas” que actualiza todas las tablas dinámicas de la carpeta.

Veremos que nuestros datos han cambiado rápidamente y esto fue solo una
prueba para comprender cómo hacer esta actualización.

Importante: Para actualizar automáticamente la tabla dinámica en Excel, es

necesario crear una macro. Una macro, para aquellos que no lo saben, es una
característica realmente interesante que tenemos en Excel que automatiza las
tareas para el usuario.

13
MODULO 2

La segmentación es una característica fantástica de Excel que permite filtrar

datos en Excel de una manera muy interesante.

El filtro que estamos acostumbrados a trabajar con Excel tiene este parecido:

MÓDULO 1

Pero, ¿Qué tal si, en lugar del filtrado tradicional, los filtros fueran con botones
interactivos, donde podamos hacer clic en los datos que necesitamos y los datos
se filtran automáticamente? Veamos a continuación:

Como podemos observar, esta función para segmentar datos es fantástica,

podemos crear botones que filtran e interactúan con los datos de tabla, tabla
dinámica y gráfico dinámico.

14
MODULO 2

Importante: esta función está disponible para versiones anteriores a Office

2010.

Con el recurso de segmentación de datos puede crear Dashboards increíbles

que hacen que todo sea muy interactivo y fácil de usar.

1) Seleccionar la tabla o tabla dinámica en la que desea crear segmentación de

datos.

2) Veremos un menú dinámico llamado “Herramientas de tabla dinámica”.

Haga clic en este menú, vaya a Analizar y seleccione la opción Insertar
Segmentación de Datos.
MÓDULO 1

3) Veremos todas las columnas de su base de datos en la lista (vea la imagen a

continuación):

15
MODULO 2

4) Seleccione los campos (columnas) en los que desea crear los segmentos
(filtros) y haga clic en Aceptar. En el ejemplo de la imagen de arriba, se
seleccionaron 3 campos: Sucursal, Región y Vendedor, por lo tanto, el
resultado es este:

MÓDULO 1
5) Listo. Nuestro segmentación de datos está lista para usar.

Una buena práctica será diferenciar los sarmentadores aplicándole colores

distintos.

Nos referimos a uno de los modelos de gráficos más interesantes de Excel.

Los gráficos dinámicos son excelentes para trabajar con datos complejos,
cuando tenemos muchas variables y es difícil plasmar todo en un gráfico.

Para realizar un gráfico dinámico sigamos el siguiente ejemplo:

Disponemos de una planilla con Año, Local, Vendedor, Fecha de venta y Cliente:

16
MODULO 2

Podemos decir que el gráfico dinámico es el hermano gemelo de la tabla

dinámica y solo cambia la forma en que expresa la información. Mientras que la
tabla nos muestra información a través de palabras y números, los gráficos
dinámicos nos muestran a través de imágenes moldeables.

El primer paso es seleccionar la hoja de cálculo, ir a la pestaña Insertar y en

Gráfico Dinámico elegir Gráfico Dinámico:

MÓDULO 1

Se abrirá la ventana Crear gráfico dinámico:

17
MODULO 2

En Elegir el lugar para colocar el gráfico dinámico podemos colocarlo en la

misma hoja de cálculo o en una nueva. En este punto, dejémoslo en esta misma
hoja de cálculo. Por lo tanto, verifiquemos la hoja de cálculo existente. En su
lugar, debemos hacer clic en cualquier celda junto a nuestra hoja de cálculo
y presionar Aceptar:

MÓDULO 1

Debemos tener en cuenta que en el lado derecho de la pantalla de Excel se ha

creado una nueva ventana llamada Campos del gráfico dinámico. También que
nuestros encabezados (necesarios para este procedimiento) son los mismos
campos que aparecen en esta ventana.

18
MODULO 2

A continuación se muestran las áreas del gráfico. Aquí es donde ocurre la magia:

MÓDULO 1

Tal como trabajamos en las tablas dinámicas, debemos arrastrar los campos que
deseamos (para este ejemplo arrastraremos el campo Año a Filtros). Podemos
ver que en la parte superior izquierda del área del gráfico había un filtro con los
años que tenemos en la hoja de cálculo:

Seguido, arrastraremos el campo Valor de Ventas a

Valores:

19
MODULO 2

Veamos que el gráfico ya ha comenzado a tomar forma. Sin embargo, todavía se

encuentra un poco desfigurado:

Ahora arrastraremos
MÓDULO 1 el campo Local al Eje. Y observemos el cambio producido.

Si arrastramos Vendedor al Eje nuestra gráficos dinámicos en Excel separará

según el estado lo que cada vendedor ha vendido:

20
MODULO 2

Teniendo en cuenta que nuestro gráfico es dinámico hagamos un pequeño

cambio: cambie, en el Eje la posición de Ubicación y Vendedor, dejando al
Vendedor primero. Ahora nos muestra lo que produjo cada vendedor en cada
estado:

MÓDULO 1

Listo, pero vamos por más, hagamos clic en filtro y seleccionemos 2019 y luego
presionemos aceptar.

Veamos que los resultados del gráfico se modifiquen dinámicamente. Esto solo
fue una pequeña demostración del poder de los gráficos dinámicos.

21
MODULO 2

Un dashboard es una herramienta de organización, control y análisis de

información que permite reunir datos de manera visual para hacer un
seguimiento de métricas clave y evaluar el rendimiento de un proyecto, proceso
o negocio en particular.

Excel es capaz de crear sus propios tableros de control, y dada su versatilidad,

los dashboards en Excel pueden tener desde tablas, hasta gráficos dinámicos y
fórmulas que calculen automáticamente determinadas métricas.

Estos sirven para resumir, monitorear y analizar grandes volúmenes de datos.

Lo que, a su vez, facilita la evaluación del desempeño de un proyecto o proceso
de acuerdo a un conjunto de indicadores clave de desempeño (KPIs).

Gracias al cruce de datos en Excel y la implementación de fórmulas y tablas

dinámicas, el dashboard en Excel facilita mucho el análisis de datos y su
seguimiento, así como mejora la precisión de los resultados.

Los dashboards en Excel pueden variar mucho dependiendo de las métricas y

datos que reúnan. Sin embargo, todos tienen en común las siguientes
características:

Los indicadores clave de desempeño (KPIs por sus siglas en inglés) son métricas
que varían de una empresa a otra, ya que cada proyecto tiene metas y datos
distintos.

Los dashboard en Excel son visualmente atractivos porque dependen de los

elementos visuales para resumir datos, reflejar métricas clave y resumir
información. Y es que es más fácil entender una gráfica de barras que una tabla
repleta de porcentajes y números.

22
MODULO 2

Un panel de control siempre debe ser fácil de entender, después de todo, la

idea de usar un dashboard es resumir información y datos complejos de
manera más visual y comprensible.

Todo panel de control debe contar con datos y métricas actualizadas. No tiene
caso analizar el rendimiento de una empresa con información antigua. Para que
un dashboard en Excel sea verdaderamente útil, debe contar con datos
actualizados.

Crear un dashboard en Excel es relativamente sencillo, sin embargo, muchos

que no saben del todo cómo hacer un dashboard en Excel se saltan pasos
cruciales o no los realizan en el orden correcto.
Los pasos que debemos seguir son los siguientes:

Lo primero es establecer el objetivo de tu tablero de control. ¿Qué métricas

deseas evaluar? ¿Qué datos deseas incluir? ¿Cuáles preguntas necesitas
responder? Dependiendo de eso, el tablero reunirá cierta información y
descartará otra.

El segundo paso para crear un dashboard en Excel, es hacer la base de datos de

tu tablero de control. Para eso, deberás añadir una hoja a tu archivo de Excel, en
esa hoja debes crear una tabla con toda la información que necesitarás reflejar
en tu dashboard.

Una vez que tengas tu base de datos, es momento de crear las tablas dinámicas
de tu dashboard en Excel. La tabla dinámica resumirá información y métricas
clave de la base de datos que hiciste antes. Para crearla, haz clic en Insertar en
el panel superior, y luego pulsa la opción Tabla dinámica.

23
MODULO 2

Cuando ya tengas definidas tus tablas dinámicas, podrás crear gráficos

dinámicos que reflejen su información visualmente y se actualicen con cada
cambio de las tablas o base de datos. Para crear gráficos dinámicos en Excel,
pulsa la opción Insertar y luego Gráfico dinámico.

No te olvides de definir y destacar tus KPIs con el formato adecuado. Puedes

representarlos en tu tablero de control de Excel como grandes recuadros o
tarjetas visualmente atractivas. Crearlas es sencillo, pulsa Insertar en el panel
superior, luego haz clic en la opción Forma y en la celda de la forma que hayas
creado coloca el signo “=” seguido del nombre de la celda que deseas destacar.

La segmentación de datos en Excel es una de sus grandes ventajas. Aplicarla es

sencillo: selecciona la tabla dinámica cuyos datos quieres segmentar, luego, en
el panel de Herramientas de tabla dinámica, sigue la ruta: Analizar > Insertar
segmentación de datos ruta: Herramientas de tabla dinámica > Analizar >
Insertar escala de tiempo.

Con esto, podrás segmentar determinadas métricas, extraerlas y reflejarlas

puntualmente en tu tablero.

El diseño es el toque final de tu dashboard en Excel. La idea es que muestre con

claridad las métricas más importantes y que sea fácil de entender. En este
sentido, recuerda que cada retoque visual y gráfico debe darle prioridad a los
objetivos principales del dashboard.

Resolver los ejercicios del archivo U2- Práctica 02.xls

FIN MÓDULO 2

24