ANÁLISIS DE DATOS Y TABLAS

DINÁMICAS CON MICROSOFT EXCEL

Facilitador: Patricio Majano

San Salvador, diciembre de 2017

Análisis de datos y tablas dinámicas con Excel

CONTENIDO

1. Introducción a las bases de datos con Excel.

2. Diseño de bases de datos con Excel.
3. Uso de la herramientas tablas en las bases de datos con
Excel.
4. Uso de fórmulas.
5. Búsqueda de datos (Función Buscarv).
6. Ordenamiento de datos (simple y por varios campos).
7. Filtros de campos y filtros avanzados.
8. Funciones de bases de datos.

2
Análisis de datos y tablas dinámicas con Excel

CONTENIDO

9. Uso de las tablas dinámicas.

◦ Aplicar filtros a la tabla dinámica.
◦ Cambiar la representación de los datos en las tablas
dinámicas.
◦ Uso de campos calculados.
◦ Usar la tabla dinámica como pivote para obtener reportes
de salida.
◦ Tablas dinámicas en tres dimensiones.
◦ Actualización de los datos en la tabla dinámica.
◦ Elaboración de gráficas dinámicas.

3
Análisis de datos y tablas dinámicas con Excel

Introducción a las bases de datos

• Las bases de datos son conjuntos de datos

relacionados entre sí, pertenecientes a un
mismo contexto y almacenados de manera
sistemática para su uso futuro.
• Su información se organiza en:
Campos
(columnas)
Registros (filas)

4
Análisis de datos y tablas dinámicas con Excel

Introducción a las bases de datos

• Un campo es el espacio para almacenar un elemento

de información (dato).
• Un registro es un conjunto de campos que contienen
los datos que pertenecen a una misma entidad.
Campos
Ejemplo:

Código Nombres Apellidos Departamento

0001 Pedro Luis Gómez Ventas
Registros 0003 Mario Ernesto Vanegas Díaz Ventas
0002 Ricardo Salazar Cañas Cobros

5
 Reglas para diseñar bases de datos
Análisis de datos y tablas dinámicas con Excel

con Excel
Diseñar una base de datos en Excel, es escribir los nombres de
los campos, en la fila de encabezados.
Consideraciones:

No filas o columnas en blanco No celdas combinadas

Base de datos ubicada en un rango

Registros únicos
de celdas adyacentes

Tipos de datos uniformes en cada

Un registro por fila
campo

Nombres de campos únicos y

No filas de totales
explícitos

6
 Tareas que se pueden realizar con
Análisis de datos y tablas dinámicas con Excel

las bases de datos hechas en Excel

Ordenar los registros

Filtrar la base de datos

Resumir en tablas dinámicas

Resumir en gráficas dinámicas

Mantenimiento a los datos

Extracción de datos para informes varios

Combinar las bases de datos

7
 “Regla de oro” al manipular las
Análisis de datos y tablas dinámicas con Excel

bases de datos en Excel

Excel reconoce a cada rango de
celdas que tienen una fila de
encabezados y datos como una base
de datos, por lo que no es necesario
“seleccionar” total o parcialmente el
rango para realizar las operaciones de
filtrado, ordenamiento de los registros,
o resúmenes en tablas o gráficas
dinámicas.
8
 Análisis de datos y tablas dinámicas con Excel

3
2
1

9
Diseño de bases de datos con Excel
Análisis de datos y tablas dinámicas con Excel

Uso de la herramienta Tabla

La herramienta Tabla facilita el

manejo de las bases de datos
hechas en Excel.
Procedimiento: Colocar la
“celda activa” sobre la base de
datos.
Menú Insertar, opción Tabla.

Nótese que Excel ha seleccionado el rango de la

base de datos y además ha identificado la fila de
encabezados.
10
Análisis de datos y tablas dinámicas con Excel

Uso de la herramienta Tabla

Excel activa el uso de

filtros para la tabla y
habilita el menú de
Herramientas de tabla.
Por medio del menú se
puede modificar el
formato de la tabla y
agregar filas de totales
entre otras tareas.

11
Análisis de datos y tablas dinámicas con Excel

Ventajas al utilizar el formato Tablas

Auto llenado de fórmulas y funciones

Uso de bandas de colores automáticas en filas y
opcional en columnas
Ampliación automática del rango de datos y de la
fuente de datos al elaborar tablas dinámicas
Fila de sub totales
Facilita el manejo en el uso de funciones de
búsqueda de datos
Manejo avanzado de las referencias
12
Análisis de datos y tablas dinámicas con Excel

Uso de fórmulas
• Las fórmulas en Excel se utilizan para realizar cálculos
sobre valores constantes o referenciados a celdas.
• Para aplicar la fórmulas efectúa las operaciones de
izquierda a derecha y utiliza la siguiente jerarquía de
operaciones:

1. ^ Exponenciación
2. * Multiplicación
Para efectuar una
3. / División operación de menor
4. + Suma jerarquía antes,
5. - Resta
utilizamos ( )

13
Análisis de datos y tablas dinámicas con Excel

Ejemplo de escritura de fórmula

Se desea escribir la fórmula para realizar el cálculo del pago de una
comisión del 5% por ventas arriba de US$1,000.00, más el 1% sobre
US$1,000.00. Las ventas menores o iguales a US$1,000.00 tienen la
comisión del 1%.
Pasos:
1. Conocer si las ventas exceden los US$1,000.00.
2. Si las ventas exceden a US$1,000.00 calcular el excedente por
medio de una resta.
3. Calcular la comisión por medio de una multiplicación.
Resolución:
=si(ventas>1000,(ventas-1000)*5%+1000*1%,ventas*1%)

14
Fórmulas
Análisis de datos y tablas dinámicas con Excel

Búsqueda de datos (función Buscarv)

• La función Buscarv pertenece al grupo de funciones de
búsqueda y referencia.
• Se utiliza para extraer cualquier dato ubicado a la
derecha de un campo de referencia en la base de
datos.
Ejemplo:
Se tiene la base de datos de los productos en existencia en un pequeño
supermercado. Se desea elaborar una pantalla de manera que al digitar el
código del producto obtengamos el nombre del producto y el precio.

15
Análisis de datos y tablas dinámicas con Excel

Ejemplo de la función Buscarv

Columna Columnas con datos a
de obtener como resultado
referencia
id p ro d uc to p ro d uc to p re c io
1 Lo mo d e a g uja 14.68
2 Bis te c 12.94
3 Ca rne Mo lid a 13.84

Base de datos
4 P e chug a 14.19
5 P e chug a s in hue s o 11.29
6 J a mó n p a ra s a nd wich 13.76
7 le che 13.84
8 q ue s o 11.74
9 cre ma 12.03
10 vino tinto 14.86

Digite e l c ó d ig o d e l p ro d uc to 6  Dato a buscar

No mb re d e l p ro d uc to Jamón para sandwich
Celdas de
P re cio 13.76 resultado

Sintaxis: =buscarv(idproducto,A:C,2,falso)

16
 Combinando bases de datos con la
Análisis de datos y tablas dinámicas con Excel

función Buscarv
De manera similar a los
manejadores de bases de datos,
Excel puede combinar los datos de
diferentes bases, de manera que
los datos compartidos se actualizan
en cascada.
La finalidad de mantener separadas las tablas es no mezclar
conjuntos de datos independientes, solamente relacionarlos y
utilizarlos cuando es necesario.

17
 Combinando bases de datos por
Análisis de datos y tablas dinámicas con Excel

medio de la función Buscarv

Libros Clientes

Órdenes

)
Buscarv( )

vr (
s ca
Bu

18
Análisis de datos y tablas dinámicas con Excel

Ordenamiento de datos

Una de las tareas básicas que se

realizan en el manejo de las bases
de datos, es el ordenamiento de
sus registros.

Ordenamiento simple: Ordenamiento personalizado:

Ordena la base en función de un Agrupa y ordena la base en función
solo campo y criterio de varios campos y criterios

Ordenar la base de datos es reubicar los registros de

acuerdo a criterios diferentes.

19
Análisis de datos y tablas dinámicas con Excel

Ordenamiento simple de datos

1. Colocar la celda activa

en cualquier ubicación 2. Seleccionar el tipo
del campo que se desea de ordenamiento
utilizar de criterio para según el tipo de
ordenar la base. datos.

Resultado 

20
 Ordenamiento personalizado de
Análisis de datos y tablas dinámicas con Excel

datos
1. Colocar la celda activa
en cualquier ubicación 2. Seleccionar Orden
de la base de datos. personalizado

3. Elegir el primer campo de agrupación y el orden

para dicho campo.

21
 Ordenamiento personalizado de
Análisis de datos y tablas dinámicas con Excel

datos
4. Agregar un nivel y elegir el segundo campo y el
criterio de ordenación.

Resultado

Si se agregan más niveles, Excel hace agrupaciones

internas, tantas como niveles se hayan agregado,
dentro de cada bloque los registros estarán ordenados.
Además los bloques están ordenados entre ellos.

22
Análisis de datos y tablas dinámicas con Excel

Filtros de datos

Método de búsqueda de registros según criterios, los

cuales pueden ser varios y acumulativos.

Filtros
El resultado se muestra en la misma base de datos,
Autofiltros
desplegándose únicamente los registros resultantes y
ocultándose el resto de ellos.
o filtrosavanza
Tipos de
de filtros
campos
dos
23
Análisis de datos y tablas dinámicas con Excel

Autofiltros o filtros de campo

Pasos para su aplicación:

1. Menú inicio
2. Filtro

Autofiltros activados
24
Análisis de datos y tablas dinámicas con Excel

Autofiltros o filtros de campo

Base de datos filtrada

25
Análisis de datos y tablas dinámicas con Excel

Filtros personalizados
Para eliminar la aplicación del filtro se
elige Borrar y se desactivan cuando ya no
se van a realizar más aplicaciones.
Excel identifica el tipo de datos en cada campo, por lo que el
ordenamiento estará en función de estos, ejemplo:
Filtrar por precios entre $14.00 y $15.00 ambos inclusive.

26
Análisis de datos y tablas dinámicas con Excel

Filtros personalizados
Uso de comodines.
Se utilizan para representar
caracteres y ser más precisos
en el filtrado.
Ejemplo. Filtrar los productos
que comienzan con “queso”,
independiente del resto del
nombre.
Los comodines también se
pueden utilizar con Buscarv.
27
Análisis de datos y tablas dinámicas con Excel

Filtros avanzados
Con el filtro avanzado, los criterios de filtrado se encuentran
en la hoja de trabajo, permitiendo la visualización del
conjunto de criterios. El resultado es igual al del autofiltro.
Pasos:
1. Copiar la fila de encabezados abajo o arriba de la base
2. Escribir los criterios de filtrado, así:

3. Elegir en el menú , , lo cual activa el

cuadro de diálogo de filtro avanzado.
28
Análisis de datos y tablas dinámicas con Excel

Filtros avanzados

4. Seleccionar el
rango de los criterios

Resultado
29
Análisis de datos y tablas dinámicas con Excel

Ejemplos de filtros avanzados

Criterios Significado

Categorías lácteos o bebidas, ambas.

Categoría
(operador O)
Lácteos
bebidas

Precio Precio Precio por unidad entre 18 y 30, ambos

por unidad por unidad inclusive.
>=18 <=30
(operador Y)

Nombre del producto

Contiene “queso” en el nombre del producto
*queso*

Proveedor de la letra “g” en adelante

Proveedor

>g
30
Análisis de datos y tablas dinámicas con Excel

Funciones de bases de datos

Funciones específicas para el manejo de datos en
estructuras de datos de Excel

Bdmin
Bdsuma

Bdmax Bdpromedio
Bdcontar
(para
números) Bdcontara
(para texto)
31
Análisis de datos y tablas dinámicas con Excel

Funciones de bases de datos

Funciones específicas para realizar operaciones simples
con estructuras de datos de Excel.
Utiliza criterios de la misma manera que el filtro avanzado y
las funciones de operación comunes, como la suma, contar,
max, min, promedio, entre otras.
Ejemplo:
En la base de datos de productos, contar el número de productos
cuyo nombre contenga la palabra “queso”.
El conteo requerido puede hacerse sobre los datos numéricos o
alfanuméricos, en este caso se hará sobre datos alfanuméricos.
32
Análisis de datos y tablas dinámicas con Excel

Funciones de bases de datos

Datos de la función
(base de datos, nombre
del campo a contar,
rango de criterios)

Rango de criterios

Resultado

33
Análisis de datos y tablas dinámicas con Excel

Tablas dinámicas
Es una herramienta para el análisis de datos por medio de
resúmenes.
Tiene la funcionalidad de modificar reportes de manera rápida y
simple.
Su finalidad es resumir información de grandes cantidades de
datos, de manera ágil y dinámica.
Procedimiento:

Base de datos Estructura TD

34
Análisis de datos y tablas dinámicas con Excel

Tablas dinámicas

Diseñar la tabla
dinámica es
“arrastrar los campos
en las diferentes
ubicaciones de la
tabla dinámica
(Filtros, filas,
columnas o valores)

35
 Procedimiento para crear las tablas
Análisis de datos y tablas dinámicas con Excel

dinámicas
1. Posicionar la celda activa sobre la base de datos.
2. Menú Insertar, Tabla dinámica
3. Elegir parámetros y Aceptar.
4. Colocar los campos en las diferentes estructuras de la tabla
dinámica (considerar que hay campos de agrupación y campos
de datos, y que los datos pueden ser operables o no operables).
5. El resultado es una tabla dinámica con los datos resumidos,
según los campos elegidos.
6. Se colocan en el área de valores los campos que deseamos
analizar.

36
 Análisis de datos y tablas dinámicas con Excel

5
2

3
4
Procedimiento para crear las TD

37
Análisis de datos y tablas dinámicas con Excel

Aplicar filtros en las tablas dinámicas

Se desea analizar únicamente los datos de los productos en
ciudades específicas. Ejemplo.

Como resultado se obtiene el mismo

cuadro con los datos según los
campos seleccionados.

38
 Cambiar la representación de los
Análisis de datos y tablas dinámicas con Excel

datos en las tablas

Excel suma por defecto los datos numéricos colocados en el
área de valores y los datos de texto los cuenta.
Es posible obtener valores promedio, mínimo o máximo,
entre otros, o representar los datos en términos porcentuales.

39
Análisis de datos y tablas dinámicas con Excel

Campos calculados
Cuando necesitamos insertar campos calculados en la tabla
dinámica, sin necesidad de hacerlo en la base de datos de
origen, lo hacemos por medio de los siguientes pasos:
Se desea agregar un campo que calcule el Nombrar campo e insertar campos y
10% sobre la cuota, en la siguiente tabla: fórmula

Elegir
opción
40
Análisis de datos y tablas dinámicas con Excel

Obteniendo sub tablas

Las tablas dinámicas también son conocidas como tablas
pivote, por su propiedad de que por medio de la tabla se
puede obtener una nueva tabla, según el dato elegido.
Ejemplo:

Se desea obtener un
listado de los personajes
cuya afición es la
natación, para ello
hacemos “doble click” en
el botón principal de
ratón, habiendo elegido el
dato del cual deseamos
obtener el listado.
41
Análisis de datos y tablas dinámicas con Excel

Segmentación de datos
En el menú de TD Opciones, elegir Insertar
Segmentación de datos.

Elegir los campos por los que se desea segmentar, y

no necesariamente los que se están mostrando en la
tabla en ese momento.

Ajustar el tamaño y
ubicación de los cuadros de
botones y filtrar según se
necesite.
Para elegir de manera
alterna utilizar la tecla Ctrl.

42
Análisis de datos y tablas dinámicas con Excel

Tablas dinámicas en 3 dimensiones

Las tablas dinámicas 3D se utilizan para resumir datos que se
encuentran en diferentes hojas y que comparten una
estructura de datos.

Además pueden
utilizarse para
resumir bases de
datos con más de
un campo de
agrupación.

43
Análisis de datos y tablas dinámicas con Excel

Procedimiento TD 3D
Habilitar en la barra de herramientas de acceso
rápido, el asistente para tablas y gráficos dinámicos.

Abrir el asistente y elegir Rangos de

consolidación múltiple.

Elegir Crear un solo campo de página.

Seleccionar el rango de
la primera hoja y
presionar “agregar”.
Repetir el proceso con
las demás hojas y luego
presionar “finalizar”.
44
Análisis de datos y tablas dinámicas con Excel

Procedimiento TD 3D
En la lista de
campos
aparece
habilitado el
Filtro de
informe.

Cada elemento en el filtro de informe representa los datos de

cada hoja. Ya que el filtro permite seleccionar más de un
elemento, se puede entonces agrupar los datos en la tabla
dinámica, ejemplo: resumen de un trimestre.
Se llama tabla dinámica 3D porque al filtrar cada elemento,
aparenta estar mostrando a cada uno como la página de un
libro, pudiendo “atravesar” y mostrar la(s) que se necesiten.

45
Análisis de datos y tablas dinámicas con Excel

Actualizando los datos

Base de Reportes
datos

Resumen de la
Fuente de datos Salidas
base de datos

Actualizar Generación
Mantenimiento 2
resumen de reporte
de datos
3
1

46
Análisis de datos y tablas dinámicas con Excel

Gráficas dinámicas
Las gráficas dinámicas se utilizan para representar
gráficamente los datos resumidos en la tabla dinámica.
Lo que diferencia una gráfica dinámica de una gráfica
convencional es que la primera se sincroniza a una tabla
dinámica, de manera que representa los datos mostrados en
la tabla, además, si la tabla se modifica en su forma y
contenido, también lo hace la gráfica dinámica.

Tabla dinámica Lista de campos

Gráfica dinámica
47
Análisis de datos y tablas dinámicas con Excel

Pasos para crear gráficas dinámicas

Una vez se ha elaborado la tabla dinámica, en el menú Analizar se
elige la opción Gráfico dinámico.

El siguiente paso es elegir el tipo de gráfico, luego se presiona

“Aceptar”.

48
Análisis de datos y tablas dinámicas con Excel

Modificando la gráfica dinámica

Una vez se tiene elaborada la gráfica dinámica, se activan 3 menús:
Analizar, diseño y formato. El primero es propio para tareas de
análisis de gráfica dinámica y los otros dos son comunes con las
gráficas convencionales.
En el menú análisis se encuentran, principalmente: actualizar la
gráfica, cambiar los datos de origen y activar la lista de campos y
los botones de campo.
Las demás tareas que se pueden
realizar consisten en aplicar filtros a los
campos graficados, cambiar el diseño
de la tabla y la gráfica o personalizar la
gráfica.
49
50