100% encontró este documento útil (1 voto)
201 vistas120 páginas

Manual Excel Avanzado

Descargar como pdf o txt
Descargar como pdf o txt
Descargar como pdf o txt
Está en la página 1/ 120

MANUAL DE

MICROSOFT EXCEL
AVANZADO

Elaborado por: Ing. Rodrigo Reyes MSc.


Microsoft Excel Avanzado

MICROSOFT EXCEL AVANZADO

1 GESTIÓN DE DATOS ........................................................................................... 4


1.1 ESQUEMAS ...................................................................................................... 4
1.2 SUBTOTALES .................................................................................................... 6
1.3 CONSOLIDACIÓN DE DATOS ............................................................................... 7
1.4 RELACIÓN DE TABLAS ....................................................................................... 8
2 FUNCIONES AVANZADAS ................................................................................ 10
2.1 FUNCIONES DE BÚSQUEDA Y REFERENCIA ....................................................... 10
2.1.1 BUSCARV................................................................................................. 10
2.1.2 BUSCARH ................................................................................................ 10
2.1.3 BUSCARX................................................................................................. 11
2.1.4 COINCIDIRX ............................................................................................. 11
2.1.5 DIRECCIÓN .............................................................................................. 12
2.1.6 ELEGIR ..................................................................................................... 12
2.1.7 HIPERVÍNCULO ....................................................................................... 12
2.1.8 ÍNDICE ...................................................................................................... 13
2.1.9 INDIRECTO .............................................................................................. 13
2.1.10 TRANSPONER ...................................................................................... 14
2.1.11 ÚNICOS ................................................................................................ 14
2.2 FUNCIONES MATEMÁTICAS.............................................................................. 15
2.2.1 Sumar.Si ................................................................................................... 15
2.2.2 Sumar.Si.Conjunto .................................................................................... 16
2.3 FUNCIONES ESTADÍSTICAS .............................................................................. 16
2.3.1 Contar.Si ................................................................................................... 16
2.3.2 Contar.Si.Conjunto .................................................................................... 17
2.3.3 Max.Si.Conjunto ........................................................................................ 17
2.3.4 Min.Si.Conjunto ......................................................................................... 18
2.3.5 Promedio.Si .............................................................................................. 19
2.3.6 Promedio.Si.Conjunto ............................................................................... 19
3 FORMATOS CONDICIONALES ......................................................................... 20
3.1 REGLAS PARA RESALTAR CELDAS .................................................................... 20
3.2 REGLAS PARA VALORES SUPERIORES E INFERIORES ......................................... 21
3.3 BARRA DE DATOS ........................................................................................... 22
3.4 ESCALAS DE COLOR........................................................................................ 23
3.5 CONJUNTO DE ÍCONOS .................................................................................... 23
3.6 NUEVA REGLA ................................................................................................ 23
3.7 ADMINISTRADOR DE REGLAS DE FORMATO CONDICIONALES .............................. 26
3.8 VALORES DUPLICADOS ................................................................................... 27
4 ORDENAMIENTO Y FILTROS ............................................................................ 28
4.1 ORDENAMIENTO BÁSICO ................................................................................. 28
4.2 ORDENAMIENTO AVANZADO O PERSONALIZADO ................................................ 29
4.3 FILTROS BÁSICOS........................................................................................... 29
4.4 FILTROS AVANZADOS...................................................................................... 31
4.5 FUNCIÓN FILTRAR .......................................................................................... 33
4.6 FUNCIÓN SUBTOTALES ................................................................................... 33
5 TABLAS Y GRÁFICOS DINÁMICOS .................................................................. 34

1|Página
Microsoft Excel Avanzado

5.1 ¿QUÉ ES UNA TABLA DINÁMICA? ...................................................................... 34


5.2 TABLAS DINÁMICAS SUGERIDAS ....................................................................... 34
5.3 ESTRUCTURA DE UNA TABLA DINÁMICA ............................................................ 35
5.4 MANIPULACIÓN DE LOS CAMPOS EN ÁREAS....................................................... 37
5.5 CONFIGURACIÓN DE CAMPO DE VALOR............................................................. 37
5.6 CONFIGURACIÓN DE FORMATO DE NÚMERO ...................................................... 38
5.7 CONFIGURACIÓN DE CAMPOS EN FILTROS ........................................................ 39
5.8 CREACIÓN DE CAMPOS CALCULADOS ............................................................... 39
5.9 DISEÑO DE UNA TABLA DINÁMICA ..................................................................... 41
5.9.1 Mostrar Subtotales. ................................................................................... 41
5.9.2 Totales Generales ..................................................................................... 43
5.9.3 Diseño de informe ..................................................................................... 44
5.9.4 Filas en blanco .......................................................................................... 46
5.10 ACTUALIZACIÓN DE LOS DATOS........................................................................ 47
5.11 AGRUPAR DATOS DE LA TABLA DINÁMICA .......................................................... 48
5.11.1 Fechas................................................................................................... 48
5.11.2 Rangos numéricos ................................................................................. 49
5.12 SEGMENTACIÓN DE DATOS .............................................................................. 50
5.13 CONEXIONES DE FILTRO.................................................................................. 51
5.14 ¿QUÉ ES UN GRÁFICO DINÁMICO? ................................................................... 52
5.15 ESTRUCTURA DE UN GRÁFICO DINÁMICO .......................................................... 52
5.16 HERRAMIENTAS DEL GRÁFICO DINÁMICO .......................................................... 54
5.17 FILTROS EN UN GRÁFICO DINÁMICO.................................................................. 54
6 TABLEROS DE CONTROL EN EXCEL .............................................................. 55
6.1 POWER QUERY .......................................................................................... 56
6.1.1 Definiciones .............................................................................................. 56
6.1.1.1 ETL .................................................................................................... 56
6.1.1.2 Formato Tabular ................................................................................ 57
6.1.1.3 Orígenes de Datos ............................................................................. 59
6.1.1.4 Conociendo Power Query .................................................................. 60
6.1.2 Consolidación de archivos en uno solo ..................................................... 62
6.1.3 Consolidación de tablas ............................................................................ 66
6.1.4 Cruce de Tablas ........................................................................................ 69
6.1.4.1 Tipos de combinaciones entre tablas ................................................. 74
6.1.5 Base de Datos........................................................................................... 81
6.1.6 Solucionar Problemas ............................................................................... 87
6.1.6.1 Agrupar registros................................................................................ 87
6.1.6.2 Anulación de dinamización de columnas............................................ 88
6.1.6.3 Columna Dinámica ............................................................................. 90
6.1.7 Consulta Web ........................................................................................... 91
7 POWER PIVOT ................................................................................................... 94
7.1 DEFINICIONES ................................................................................................ 94
7.2 INSTALACIÓN POWER PIVOT ............................................................................ 95
7.3 AGREGAR DATOS A POWER PIVOT ................................................................... 96
7.4 INTELIGENCIA DE TIEMPO .............................................................................. 101
7.5 LENGUAJE DAX ........................................................................................... 103
7.6 MEDIDAS EN POWER PIVOT ........................................................................... 107
7.6.1 Columna Calculada ................................................................................. 108
7.6.2 Power Pivot y Power Query..................................................................... 109

2|Página
Microsoft Excel Avanzado

7.7 MODELO DE DIAGRAMA ................................................................................. 109


8 POWER MAP .................................................................................................... 113
8.1 DEFINICIONES .............................................................................................. 113
8.2 VISUALIZACIÓN DE DATOS DENTRO DE MAPAS................................................. 113

3|Página
Microsoft Excel Avanzado

En la actualidad, la necesidad de estar al día en las tecnologías de la información, y


aplicarlas a las tareas cotidianas de nuestras empresas, han hecho que el conocimiento
de Microsoft Excel sea indispensable dentro de las habilidades que una persona posee.

La facilidad que nos brindan las aplicaciones ofimáticas, para el manejo de una gran
cantidad de datos, hace que la información se pueda centralizar o que simplemente los
datos que se obtienen de los procesos diarios puedan transformarse en información
valiosa para una empresa o negocio.

Con este fin, se ha creado este curso, que está dirigido al público en general con
conocimientos intermedios de Microsoft Excel; el mismo que nos proporcionará las
herramientas para consolidar, trasformar, optimizar o generar un manejo adecuado de
la información en una hoja de cálculo.

1 GESTIÓN DE DATOS

1.1 Esquemas

Los esquemas permiten agrupar y resumir un conjunto de datos y pueden tener un


máximo de 8 niveles de agrupación. Son muy útiles al momento de consolidar datos
para mostrar solo la información necesaria sin tener que eliminar filas o columnas;
simplemente se las agrupa y oculta.

Es recomendable que antes de crear los esquemas, las filas y las columnas estén
definidas con etiquetas y existan filas de resumen o totales, que ayuden a agrupar y
mostrar de mejor manera los esquemas.

1. Pestaña Datos – grupo Esquema


2. Clic en Agrupar – Autoesquema
3. Para mostrar y ocultar los niveles del esquema hacemos clic en los símbolos
ubicados en la parte superior derecha.
4. Si deseamos desagrupar filas o columnas, seleccionamos y, a continuación, en
el grupo Esquema de la pestaña Datos, hacemos clic en Desagrupar o si
deseamos Eliminar el esquema completo hacemos clic en Borrar esquema.

4|Página
Microsoft Excel Avanzado

5. Si, al contrario, vamos a crear un esquema personalizado, simplemente


seleccionamos las filas o columnas que necesitemos agrupar y presionamos la
opción Agrupar.

5|Página
Microsoft Excel Avanzado

1.2 Subtotales

Los subtotales se utilizan para calcular automáticamente subtotales y totales generales


en una lista o conjunto de datos, los mismos que no se admiten cuando los datos forman
parte de una Tabla ya que, al aplicarlos, el rango de datos se muestra esquematizado.

1. Ordenar el rango de datos según la agrupación de la que se requiera mostrar los


subtotales.
2. Seleccionamos una celda del rango.
3. Pestaña Datos – grupo Esquema
4. Clic en Subtotal
5. En la ventana de configuración de los Subtotales, seleccionamos como primera
opción la columna que da el ordenamiento del rango, luego la operación que
vayamos a realizar y por último la columna en la que se va a aplicar la operación.
6. Para mostrar y ocultar los niveles del esquema hacemos clic en los símbolos
ubicados en la parte superior derecha.
7. Para quitar los Subtotales, en el cuadro de diálogo Subtotal, hacemos clic en
Quitar todo.

6|Página
Microsoft Excel Avanzado

1.3 Consolidación de datos

La consolidación de datos se usa para resumir los resultados de las hojas de cálculo
independientes, en una hoja de cálculo principal. Estas hojas pueden estar en el mismo
libro de la hoja de cálculo principal, o en otros libros; los datos se actualizan de forma
automática.
Importante – Los rangos deben tener los mismos formatos o diseño.

1. Nos ubicamos en la hoja donde vamos a generar la tabla consolidada.


2. Pestaña Datos – grupo Herramientas de Datos

7|Página
Microsoft Excel Avanzado

3. Hacemos clic en Consolidar


4. Seleccionamos la función que vamos a aplicar para cada dato.
5. Seleccionamos las referencias (los rangos de datos que contiene los valores a
consolidar)
6. Agregamos cada referencia.
7. Indicamos la posición de las etiquetas de nuestro rango consolidado.
8. Indicamos si el rango consolidado va a tener referencias a los datos de origen o
no.

1.4 Relación de tablas

Se pueden relacionar dos tablas de datos siempre y cuando existan datos que coincidan
entre ellas.

8|Página
Microsoft Excel Avanzado

1. Se necesita por lo menos dos tablas para poder realizar la relación.


2. Es importante verificar que exista una columna común entre las dos tablas que
me permita generar la relación.
3. Se recomienda asignar un nombre a cada tabla.
4. Pestaña Datos – Grupo Herramientas de Datos
5. Clic en Relaciones
6. En la ventana de configuración, seleccionamos como tabla principal aquella que
contenga más información y la columna externa el nombre de la columna que es
común con la segunda tabla, no importa que tengan nombres diferentes, lo
importante es que contengan datos que pueden relacionarse.
7. En tabla relacionada, seleccionamos la segunda tabla y como columna
relacionada, aquella que contenga los datos a relacionar.
8. Verificamos que en la ventana Administración de relaciones se encuentre creada
nuestra relación, de esta misma ventana podemos eliminar o modificar las
relaciones existentes, así como crear nuevas relaciones si tenemos más de una
tabla.

9|Página
Microsoft Excel Avanzado

2 FUNCIONES AVANZADAS

2.1 Funciones de Búsqueda y Referencia

Son funciones que nos ayudan a encontrar valores en un libro, hoja o rango. Estos
valores deberán cumplir ciertos criterios establecidos dentro de la función.

2.1.1 BUSCARV

La función BUSCARV, se usa cuando los valores de comparación se encuentran en una


columna a la izquierda, esta columna de referencia debe ser la primera en la tabla.

=BUSCARV (valor_buscado; matriz_tabla; indicador_columnas; ordenado)

2.1.2 BUSCARH

La función BUSCARH, se usa cuando los valores de comparación se encuentran en la


fila superior, esta fila de referencia debe ser la primera en la tabla.

=BUSCARH (valor_buscado; matriz_tabla; indicador_fila; ordenado)

10 | P á g i n a
Microsoft Excel Avanzado

2.1.3 BUSCARX

La función BUSCARX, se usa para buscar datos en una tabla o rango por fila o columna,
sin importar que el dato a buscar esté al inicio de la tabla.

=BUSCARX (valor_buscado; matriz_buscada; matriz_devuelta; [si_no_se_encuentra];


[modo_de_coincidencia]; [modo_de_búsqueda])

2.1.4 COINCIDIRX

La función COINCIDIRX, busca un elemento específico en una matriz o rango de celdas


y, a continuación, devuelve la posición relativa del elemento.

=COINCIDIRX (valor_buscado, matriz_buscada, [tipo_de_coincidencia])

11 | P á g i n a
Microsoft Excel Avanzado

2.1.5 DIRECCIÓN

La función DIRECCIÓN, se usa para obtener la dirección de una celda en una hoja de
cálculo, dados los números de fila y columna específicos.

=DIRECCION (fila; columna; [abs]; [a1]; [hoja])

=DIRECCION(3;2)

2.1.6 ELEGIR

La función ELEGIR, devuelve un valor según la posición numérica que se asigne a la


lista de valores ingresados.

=ELEGIR (núm_índice; valor1; [valor2]; ...)

2.1.7 HIPERVÍNCULO

La función HIPERVÍNCULO, crea un acceso directo que salta a otra ubicación del libro
actual o abre un documento almacenado en un servidor de red, una intranet o Internet.

=HIPERVINCULO (ubicación_del_vínculo; [nombre_descriptivo])

12 | P á g i n a
Microsoft Excel Avanzado

2.1.8 ÍNDICE

La función ÍNDICE, permite encontrar valores en una matriz de datos determinando la


fila y columna, devuelve el valor que se encuentra en la posición indicada.

=INDICE (matriz; núm_fila; [núm_columna])

2.1.9 INDIRECTO

La función INDIRECTO, permite extraer la referencia de otra celda y no ingresarla dentro


de la función.

=INDIRECTO (Referencia)

13 | P á g i n a
Microsoft Excel Avanzado

2.1.10 TRANSPONER

La función TRANSPONER, permite intercambiar filas por columnas o viceversa y


mantener la referencia a los valores originales.

=TRANSPONER (matriz)

2.1.11 ÚNICOS

La función UNICOS, devuelve una lista de valores únicos de una lista o rango.

=UNICOS (matriz)

14 | P á g i n a
Microsoft Excel Avanzado

2.2 Funciones Matemáticas

Estas funciones nos permiten realizar la suma de una serie de datos, cuando se cumplen
una o varias condiciones o criterios.

2.2.1 Sumar.Si

La función SUMAR.SI, se usa para sumar los valores de un rango que cumplan con un
criterio específico.

=SUMAR.SI (rango; criterio; [rango_suma])

15 | P á g i n a
Microsoft Excel Avanzado

2.2.2 Sumar.Si.Conjunto

La función SUMAR.SI.CONJUNTO, se usa para sumar los valores de un rango que


cumplan con uno o varios criterios.

=SUMAR.SI.CONJUNTO (rango_suma; rango_criterios1; criterios1; [rango_criterios2;


criterios2];…)

2.3 Funciones Estadísticas

2.3.1 Contar.Si

La función CONTAR.SI, se usa para contar el número de celdas que cumplen un criterio
específico.

16 | P á g i n a
Microsoft Excel Avanzado

=CONTAR.SI (rango; criterio)

2.3.2 Contar.Si.Conjunto

La función CONTAR.SI.CONJUNTO, se usa para contar el número de celdas que


cumplen uno o varios criterios.

=CONTAR.SI.CONJUNTOI (rango_criterios1; criterio1; rango_criterios2; criterio2;…))

2.3.3 Max.Si.Conjunto

La función MAX.SI.CONJUNTO, se usa para encontrar el valor máximo entre celdas


especificadas por un determinado conjunto de condiciones o criterios.

17 | P á g i n a
Microsoft Excel Avanzado

=MAX.SI.CONJUNTO(rango_máximo; rango_criterios1; criterios1; [rango_criterios2;


criterios2];...)

2.3.4 Min.Si.Conjunto

La función MIN.SI.CONJUNTO, se usa para encontrar el valor mínimo entre celdas


especificadas por un determinado conjunto de condiciones o criterios.

=MIN.SI.CONJUNTO(rango_máximo; rango_criterios1; criterios1; [rango_criterios2;


criterios2];...)

18 | P á g i n a
Microsoft Excel Avanzado

2.3.5 Promedio.Si

La función PROMEDIO.SI, se usa para encontrar la media aritmética de todas las celdas
de un rango que cumplan una condición o criterio.

=PROMEDIO.SI(rango; criterios; [rango_promedio])

2.3.6 Promedio.Si.Conjunto

La función PROMEDIO.SI.CONJUNTO, se usa para encontrar la media aritmética de


todas las celdas de un rango, que cumplan con una o varias condiciones o criterios.

=PROMEDIO.SI.CONJUNTO(rango_promedio; rango_criterio1; criterio1; [rango_criterio2;


criterio2]; ...)

19 | P á g i n a
Microsoft Excel Avanzado

3 FORMATOS CONDICIONALES

El formato condicional es una herramienta muy útil al momento de realizar análisis de


datos, dando formatos especiales a las celdas en base a las configuraciones y
parámetros asignados. Estos formatos pueden ser relleno de celdas, bordes, formatos
de celda o cambio en las fuentes.
Lo primero que debemos hacer antes de configurar el formato condicional, es
seleccionar la celda o rango de celdas donde vamos a aplicar dicho formato.

3.1 Reglas para resaltar celdas


La opción resaltar reglas de celdas contiene algunas opciones que según las
necesidades de visualización tendremos que elegir. Las reglas para elegir son:

20 | P á g i n a
Microsoft Excel Avanzado

Precio por Unidad


$ 6,20
$ 104,70
$ 9,38
$ 15,44
$ 44,10
$ 101,60
$ 47,02
$ 190,21
$ 607,70
$ 3,86
$ 367,31
$ 1,96
$ 5,30
$ 6,53
$ 23,89
$ 126,13

3.2 Reglas para valores superiores e inferiores


Esta opción se usa para asignar formatos a los valores más altos y bajos en un rango
de celdas según un valor limite que se especifique.

21 | P á g i n a
Microsoft Excel Avanzado

Costo de Envío
$ 9,92
$ 47,11
$ 7,50
$ 33,20
$ 6,61
$ 218,45
$ 28,21
$ 38,04
$ 1.488,87
$ 8,10
$ 55,10
$ 4,22
$ 12,98
$ 14,03
$ 34,64
$ 37,84

3.3 Barra de datos


La Barra de datos, es un tipo de formato condicional que utiliza barras horizontales de
izquierda a derecha, simulando un gráfico de barras en cada celda.

Cantidad
32
9
16
43
3
43
12
4
49
42
3
43
49
43
29
6

22 | P á g i n a
Microsoft Excel Avanzado

3.4 Escalas de color


Esta opción se usa para asignar un color a cada celda en función de su valor, puede
configurarse esta escala en función de dos o tres colores.

Precio
$ 5,64
$ 96,94
$ 8,60
$ 14,30
$ 41,60
$ 94,96
$ 43,54
$ 174,50
$ 578,76
$ 3,60
$ 346,52
$ 1,82
$ 4,90
$ 5,99
$ 22,54
$ 116,79

3.5 Conjunto de íconos


El uso de iconos también forma parte de las reglas de formato condicional, este icono o
conjunto de iconos se usan para comentar y clasificar los datos, pudiendo usar entre
tres y cinco categorías separadas por un valor especifico. Cada ícono representará un
rango de valores, como se puede ver en las siguientes imágenes:
Descuento
10%
8%
9%
8%
6%
7%
8%
9%
5%
7%
6%
8%
8%
9%
6%
8%

3.6 Nueva Regla


Dentro de la configuración del formato condicional existe la opción de configuración
avanzada, donde nosotros podemos personalizar aún más los criterios para resaltar las
celdas, estas opciones se muestran haciendo clic en Nueva regla.

23 | P á g i n a
Microsoft Excel Avanzado

• Aplicar formato a todas las celdas según sus valores.

• Aplicar formato únicamente a las celdas que contengan.

24 | P á g i n a
Microsoft Excel Avanzado

• Aplicar formato únicamente a los valores con rango inferior o superior.

• Aplicar formato únicamente a los valores que estén por encima o por debajo del
promedio.

25 | P á g i n a
Microsoft Excel Avanzado

• Aplicar formato únicamente a los valores únicos o duplicados.

• Utilice una fórmula que determine las celdas para aplicar formato.

3.7 Administrador de reglas de formato condicionales


Esta herramienta nos permite crear una nueva regla, editar o eliminar reglas ya creadas.

26 | P á g i n a
Microsoft Excel Avanzado

3.8 Valores Duplicados


Existe la opción de identificar valores duplicados, utilizando un formato condicional
combinado con la función Contar.Si; esta combinación suele ser usada en conciliaciones
entre tablas.

=Y(CONTAR.SI($K$6:$K$20;C6)<>0;CONTAR.SI($L$6:$L$
20;E6)<>0;CONTAR.SI($M$6:$M$20;D6)<>0)

=CONTAR.SI.CONJUNTO($C$6:$C$18;K6;$D$6:$D$18;
M6;$E$6:$E$18;L6)<>0

27 | P á g i n a
Microsoft Excel Avanzado

4 ORDENAMIENTO Y FILTROS

4.1 Ordenamiento Básico


El ordenar datos de una tabla nos permite tener una mejor herramienta de análisis. La
opción de ordenar nos permite entre otras cosas tener una lista de datos ordenados
alfabéticamente, de menor a mayor o del más antiguo al más reciente, según el
contenido de la columna de referencia.

Ordenar texto
1. Seleccione una celda de la columna que quiere ordenar.
2. En la ficha Datos, en el grupo Ordenar y filtrar, seleccionamos la mejor opción
para ordenar nuestros datos.
• Para ordenar ascendentemente, hacemos clic en Comando A a Z en Excel
• Para ordenar descendentemente, hacemos clic en Comando Z a A en Excel

Ordenar números
1. Seleccione una celda de la columna que quiere ordenar.
2. En la ficha Datos, en el grupo Ordenar y filtrar, seleccionamos la mejor opción
para ordenar nuestros datos.
• Para ordenar de menor a mayor, hacemos clic en Comando A a Z en Excel.
• Para ordenar de mayos a menor, hacemos clic en Comando Z a A en Excel.

Ordenar fechas
1. Seleccione una celda de la columna que quiere ordenar.
2. En la ficha Datos, en el grupo Ordenar y filtrar, seleccionamos la mejor opción
para ordenar nuestros datos.
• Para ordenar de más antiguo a más reciente, hacemos clic en Comando A a
Z en Excel.
• Para ordenar de más recientes a más antiguos, hacemos clic en Comando Z
a A en Excel.

28 | P á g i n a
Microsoft Excel Avanzado

4.2 Ordenamiento Avanzado o personalizado


El orden personalizado, nos permite ordenar una tabla aplicando diferentes niveles de
ordenamiento o agrupamiento.

4.3 Filtros Básicos


Otra opción para el análisis de los datos de una tabla es la opción de filtrar la
información, esto nos permite visualizar solo la información que necesitamos, y no toda
la tabla completa.

1. Seleccione una celda o rango de la tabla.


2. En la ficha Datos, en el grupo Ordenar y filtrar, seleccionamos la opción Filtro.

3. Automáticamente observamos que junto a cada encabezado se añade una


flecha que nos permitirá seleccionar los filtros para ser aplicados a cada
columna según el tipo de datos que contenga.

29 | P á g i n a
Microsoft Excel Avanzado

Filtros de texto: Filtros de número:

Filtros fechas:

Si los filtros que nos muestran las opciones preestablecidas no son aplicables a nuestra
necesidad podemos utilizar los filtros personalizados, para tener más opciones o realizar
filtros más complejos en conjunto con las funciones Y - O.

30 | P á g i n a
Microsoft Excel Avanzado

Para eliminar un filtro o filtros aplicados a nuestra tabla, es necesario hacer clic en la
opción borrar, de la barra de herramientas. Con esta opción se borrarán todos los filtros
aplicados en la tabla o Borrar filtro dentro de las opciones del encabezado; si, solo
queremos borrar el filtro aplicado a cada columna.

Otra herramienta muy útil dentro de la segmentación de datos que se utiliza


frecuentemente en tablas dinámicas y principalmente para diseñar dashboard es la
segmentación de datos, que nos permite segmentar de una forma más gráfica y
amigable nuestra tabla.

4.4 Filtros Avanzados


En los filtros avanzados, los criterios se escriben en un rango separado en la hoja de
cálculo y sobre el rango de celdas o la tabla que desea filtrar. La diferencia con los filtros
básicos radica en que los filtros avanzados nos permiten extraer la información, mientras
que los básicos nos permiten solo visualizarlos.

31 | P á g i n a
Microsoft Excel Avanzado

1. Lo primero es copiar los encabezados que nos permitirán indicar los criterios que
nos servirán para realizar el filtro avanzado.

CATEGORÍA
F. INGRESO MARCA MODELO Precio
VEHICULAR

2. Insertar los criterios bajo cada encabezado.

MARCA
KIA

3. Ubicarnos en la celda en la que se va a generar el resultado del filtro.


4. Seleccionamos la opción Avanzadas del grupo Ordenar y Filtrar.
5. En la ventana Filtro avanzado ingresamos las opciones requeridas:
a. Seleccionamos la opción de ubicación del filtro resultante.
b. Rango de la lista: Seleccionamos la tabla completa donde se encuentran
los datos a filtrar.
c. Rango de criterios: Seleccionamos el rango donde se detalló los criterios
del filtro.
d. Copia a: Indicamos al Excel desde donde se va a desplegar la nueva
tabla resultante.
e. Si es necesario visualizar solo los registros únicos, activamos la casilla
de verificación en el cuadro solo registros únicos

32 | P á g i n a
Microsoft Excel Avanzado

4.5 Función Filtrar


La función FILTRAR, permite filtrar un rango de datos en función de los criterios o
condiciones que definamos.

=FILTRAR(matriz_devuelta;columna_comparación;[si_vacío])

La función FILTRAR permite realizar filtros teniendo en cuenta varias condiciones. Con
este fin la función filtrar utiliza los condicionales Y – O, pero reemplazados por los signos
* y +.

Y-*
=FILTRAR(matriz_devuelta;((columna_comparación1)*(columna_comparación2));"")

O-+
=FILTRAR(matriz_devuelta;((columna_comparación1)+(columna_comparación2));"")

4.6 Función Subtotales

La función SUBTOTALES, nos permite realizar varias operaciones dentro de una misma
base de datos. Esta función se utiliza mucho en combinación de la herramienta filtros.

=SUBTOTALES(núm_función,ref1,[ref2],...)

33 | P á g i n a
Microsoft Excel Avanzado

5 TABLAS Y GRÁFICOS DINÁMICOS

5.1 ¿Qué es una tabla dinámica?


Una tabla dinámica es una tabla que resume dinámicamente los datos contenidos en
una o varias tablas, con la posibilidad de indicarle a Excel los valores que debe presentar
como columnas, filas y filtros, teniendo la posibilidad de incluir campos calculados y
gráficos dinámicos.

5.2 Tablas dinámicas sugeridas


Excel nos permite obtener tablas dinámicas sugeridas sin necesidad de estructurarlas
manualmente, Excel analiza los tipos de datos, los encabezados y estructura de nuestra
tabla origen y nos da algunas opciones de tablas dinámicas que asume nos podría
interesar; estas tablas son totalmente editables posteriormente.

34 | P á g i n a
Microsoft Excel Avanzado

5.3 Estructura de una tabla dinámica


Una tabla dinámica está estructurada en columnas de la TD, filas de la TD y Valores
numéricos.

35 | P á g i n a
Microsoft Excel Avanzado

Columnas TD

Filas TD
Valores
numéricos

Una vez que hayamos seleccionado la tabla es importante seguir el siguiente esquema
para construir nuestra TD:

Paso 1 Paso 2 Paso 3


Configurar las opciones Configurar el diseño de
Crear nuestra TD
de la TD nuestra TD
Convertir el rango de datos
en tabla. Dar nombres a cada campo
Seleccionamos la tabla de de nuestra TD.
origen de nuestra TD. Dar el formato adecuado a Incluir o excluir los totales y
Elegir las filas y columnas cada campo de la TD. subtotales.
que serán parte del diseño Elegir la agrupación de los Cambiar el estilo de la TD si
de la TD. datos. fuese necesario.
Elegir si fuese necesario el Ordenar los datos.
campo que usaremos como Segmentar la TD.
filtro.

Paso 1 Paso 2 Paso 3


Configurar las opciones de Configurar el diseño de
Crear nuestra TD
la TD nuestra TD

36 | P á g i n a
Microsoft Excel Avanzado

5.4 Manipulación de los campos en áreas


Los campos que forman parte de la TD son los siguientes:

Filtros. Los campos que coloquemos en esta área


servirán como filtros para la TD, estos filtros son
adicionales a los que ya podemos aplicar en las
columnas y filas.
Columnas. Esta área contiene los campos que se
mostrarán como columnas de la TD.
Filas. Esta área contiene los campos que
determinan las filas de la TD.
Valores. Son los campos que serán parte de las
celdas de la TD y que forman parte de los valores
numéricos o de operaciones numéricas.

5.5 Configuración de campo de valor


Para cambiar la configuración del campo de valores es necesario hacer clic derecho
sobre cualquier valor de esta área y seleccionar la opción Configuración de campo de
valor. Esta opción nos permitirá cambiar el cálculo que realiza la TD con los datos o
mostrar los valores totales y subtotales en porcentajes.

37 | P á g i n a
Microsoft Excel Avanzado

5.6 Configuración de formato de número


En la misma opción de configuración de campo de valores, podemos encontrar la
herramienta de Formato de celdas, que nos permite cambiar el tipo de datos y la
visualización de éstos para entender de mejor manera lo que nos muestra la TD.

38 | P á g i n a
Microsoft Excel Avanzado

5.7 Configuración de campos en filtros


Esta herramienta es la misma que usamos en la opción de filtros básicos de una tabla.
Recordemos que esta herramienta nos permite visualizar solo los registros que cumplen
con las condiciones dadas, estos filtros no se muestran en el área de campos numéricos,
aunque si podemos acceder a ellos haciendo clic derecho sobre cualquier celda que
forme parte de una columna que contenga filtro y seleccionamos la opción Filtro.

5.8 Creación de campos calculados


Las TD permiten la creación de campos calculados que se añaden en una nueva
columna, ésta obtiene su valor de una operación realizada entre columnas de la TD.

39 | P á g i n a
Microsoft Excel Avanzado

40 | P á g i n a
Microsoft Excel Avanzado

Existe otra opción para realizar cálculos en nuestras TD, y son los elementos calculados;
éstos, cumplen con la misma función que los campos calculados, con la diferencia de
que los elementos no trabajan en columnas, sino en filas para agregar los registros.

Para eliminar campos o elementos calculados basta con buscar el campo a eliminar en
la lista Nombre y presionar eliminar.

5.9 Diseño de una tabla dinámica


El diseño de una tabla dinámica es importante al momento de presentarla. Con este fin,
se puede realizar algunas configuraciones para darle un buen aspecto.

5.9.1 Mostrar Subtotales.


Esta opción nos permite elegir cómo se van a visualizar los subtotales de nuestra tabla
dinámica.

41 | P á g i n a
Microsoft Excel Avanzado

• No mostrar subtotales:

• Mostrar los subtotales en la parte inferior del grupo:

42 | P á g i n a
Microsoft Excel Avanzado

• Mostrar los subtotales en la parte superior del grupo:

5.9.2 Totales Generales

Esta opción nos permite elegir cómo se van a visualizar los totales de nuestra tabla
dinámica.

• Desactivado para filas y columnas:

43 | P á g i n a
Microsoft Excel Avanzado

• Activado para filas y columnas:

5.9.3 Diseño de informe

Esta opción nos permite elegir cómo se van a visualizar las filas de nuestra tabla
dinámica.

• Forma Compacta

44 | P á g i n a
Microsoft Excel Avanzado

• Forma Esquema

• Forma Tabular

45 | P á g i n a
Microsoft Excel Avanzado

• Repetir todas las etiquetas de los elementos.

5.9.4 Filas en blanco


Esta opción nos permite agregar una fila en blanco después de cada elemento para
separarlo del grupo anterior.

• Insertar línea en blanco después de cada elemento.

46 | P á g i n a
Microsoft Excel Avanzado

• Quitar línea en blanco después de cada elemento.

5.10 Actualización de los datos

• Actualizar datos Manualmente


Hacer clic en herramientas de TD – grupo Datos

• Actualizar datos Automáticamente


Hacer clic sobre cualquier parte de la TD – Opciones de TD – Pestaña Datos

47 | P á g i n a
Microsoft Excel Avanzado

5.11 Agrupar datos de la tabla dinámica


Agrupar datos de una TD, ayuda a mostrar un subconjunto de datos que desea analizar
de una forma compacta y más amigable. Para entender la información, principalmente
si la TD tiene muchos datos; podemos agrupar solo datos de las tablas, no datos
generados por las TD, como totales, sumas, etc.

5.11.1 Fechas
Seleccionamos cualquier celda de nuestra TD que contenga un valor tipo fecha, en la
pestaña Analizar tabla dinámica – Grupo, seleccionamos la opción de Crear Grupo o
Crear grupo de campo. Por último, seleccionamos la forma en que vamos a agrupar los
datos.

Podemos seleccionar varias condiciones de agrupamiento.

48 | P á g i n a
Microsoft Excel Avanzado

De la misma forma podemos desagrupar datos.

5.11.2 Rangos numéricos


Seleccionamos cualquier celda de nuestra TD que contenga un valor numérico, en la
pestaña Analizar tabla dinámica – Grupo, seleccionamos la opción de Crear Grupo o
Crear grupo de campo. Por último, seleccionamos la forma en que vamos a agrupar los
datos.

49 | P á g i n a
Microsoft Excel Avanzado

5.12 Segmentación de datos


La Segmentación de datos es una herramienta utilizada para reemplazar en la medida
de lo aplicable a los filtros, genera una forma más amigable para filtrar la información de
cualquier tabla.
Para acceder a la herramienta de Segmentación, hacemos clic en la pestaña Analizar
tabla dinámica y grupo Filtrar.

50 | P á g i n a
Microsoft Excel Avanzado

Podemos incluir también la segmentación basada en fechas; la misma que se denomina


Escala de tiempo.

5.13 Conexiones de filtro


Las conexiones de filtro o informe permiten segmentar y modificar varias tablas
mediante la conexión de estos segmentadores a cada una de ellas.
Para realizar las conexiones de informe, debemos seleccionar el segmentador que
vamos a conectar, hacer clic en la pestaña Segmentación y Conexiones de informe. Es
importante que se le asigne un nombre a cada tabla para facilitar la identificación.

51 | P á g i n a
Microsoft Excel Avanzado

Podemos realizar configuraciones adicionales para la visualización de los segmentos de


datos, accediendo a sus configuraciones.

5.14 ¿Qué es un gráfico dinámico?


Un gráfico dinámico, es un gráfico basado en los datos presentados en una tabla
dinámica, estos gráficos interactúan con los filtros y segmentos que podríamos incluir
en nuestro diseño.

5.15 Estructura de un gráfico dinámico


Para iniciar necesitamos crear nuestra tabla dinámica, nos ubicamos en cualquier celda
de nuestra TD y nos dirigimos a la pestaña Analizar tabla de datos, grupo herramientas
y hacemos clic en Gráfico dinámico. Por último, seleccionamos el gráfico que mejor
represente la idea que necesitamos dar a conocer y aplicamos filtros o segmentadores
para dar más realce a los datos.

52 | P á g i n a
Microsoft Excel Avanzado

53 | P á g i n a
Microsoft Excel Avanzado

5.16 Herramientas del gráfico dinámico


A un gráfico dinámico, se le puede realizar las mismas configuraciones de diseño,
estilos, manejo de datos, tipo y ubicación como si fuese un gráfico de Excel normal.

5.17 Filtros en un gráfico dinámico


De la misma forma que se trabajó con las tablas dinámicas y los filtros con los gráficos
dinámicos, se puede aplicar diferentes segmentadores a un mismo gráfico, de esta
manera poder filtrar los datos y realizar conexiones entre diferentes segmentadores y
un mismo gráfico o diferentes gráficos.

54 | P á g i n a
Microsoft Excel Avanzado

6 TABLEROS DE CONTROL EN EXCEL

La inteligencia de negocios (Business Intelligence, BI) es un proceso que utiliza la


tecnología para analizar datos y presentar información que ayude a los ejecutivos,
gerentes y otros usuarios corporativos finales, a tomar decisiones comerciales.

55 | P á g i n a
Microsoft Excel Avanzado

6.1 POWER QUERY


Power Query es una herramienta ETL (extracción, transformación y carga) que permite
preparar, adecuar, limpiar, formatear, integrar, crear, depurar y resumir datos de forma
rápida, intuitiva y coherente, para cargar posteriormente estos datos a nuestra hoja de
cálculo o herramienta de modelado como Power Pivot.

6.1.1 Definiciones

6.1.1.1 ETL

ETL significa Extraer, Transformar y Carga. ETL es un concepto que está relacionado
con la transformación y modelación de datos para su posterior análisis y toma de
decisiones.

Extraer - Proceso en el cual se obtienen los datos desde bases de datos, archivos CSV,
archivos de texto, libros de Excel, celdas específicas en la misma hoja de cálculo, sitios
web, archivos PDF.

Transformar – Proceso en el cual se pueden depurar los datos (eliminar espacios,


dividir columnas, cambiar formatos de fecha, rellenar espacios en blanco, buscar y
reemplazar, etc.) y remodelar (eliminar columnas, etc.).

Carga – Al terminar de transformar los datos, estos deben colocarse o cargarse en otra
herramienta para poder usarlos; esta carga normalmente se la hace en otra hoja de
cálculo de Excel.

56 | P á g i n a
Microsoft Excel Avanzado

6.1.1.2 Formato Tabular

El formato tabular son aquellas características o requisitos que deben cumplir las tablas
y los datos para poder ser tabulados y cargados posteriormente a otra herramienta.

• Etiquetas de las columnas


o Evitar espacios en blanco
o Evitar caracteres no imprimibles
o Evitar tildes
o Primera letra en mayúscula
• Sin totales ni grandes totales
o Sin subtotales
o Sin totales generales
o Sin grandes totales
• Sin agrupación a nivel de filas
• Sin agrupaciones a nivel de columnas subniveles
• Sin columnas no apropiadas
o No columnas de error
o No columnas vacías
o No columnas no útiles
o No columnas duplicadas
• Sin filas no apropiadas
o No filas de error
o No filas vacías
o No filas no útiles
• Columnas con tipos de datos apropiados
• Para los elementos de las columnas
o Elementos estándares
o Elementos bien escritos
o Eliminar espacios innecesarios
▪ Espacios Dobles
▪ Espacios al principio y al final

57 | P á g i n a
Microsoft Excel Avanzado

o Eliminar caracteres no imprimibles


▪ Tabulaciones
▪ Espacios especiales
▪ Enter
▪ Etc.
• Sólo columnas de interés para el análisis
• Sólo registros de interés para el análisis

58 | P á g i n a
Microsoft Excel Avanzado

6.1.1.3 Orígenes de Datos

• Desde un archivo

• Desde una base de datos

59 | P á g i n a
Microsoft Excel Avanzado

• Desde Azure

• Desde otras fuentes

6.1.1.4 Conociendo Power Query

Lo primero que necesitamos realizar, es transformar el rango a formato de tabla e


importar los datos a Power Query.

60 | P á g i n a
Microsoft Excel Avanzado

La interfaz de Power Query está formada por las siguientes partes:

61 | P á g i n a
Microsoft Excel Avanzado

6.1.2 Consolidación de archivos en uno solo

La consolidación de archivos consiste en fusionar el contenido de diferentes archivos


que tengan la misma extensión y añadirlos a otro archivo.
Es importante que los archivos a consolidar tengan el mismo diseño de registros,
columnas, extensión, y en general, la misma estructura.

Ejercicio

El área de reportes nos emite un archivo mensual con las liquidaciones de impuestos a
pagar.
El problema es que nos resulta bastante tedioso tener que abrir los archivos de texto
todos los meses, y a éstos aplicarles los cambios necesarios.

Para resolver el problema debemos:

• Crear una consulta a la carpeta Históricos donde se cargan los archivos todos
los meses.
• Crear un procedimiento para automatizar esta tarea.

62 | P á g i n a
Microsoft Excel Avanzado

1. Extraer los datos (archivos) desde una carpeta.


2. Seleccionamos la carpeta que contiene nuestros archivos de texto.
3. Verificamos que Power Query haya detectado el archivo, y hacemos clic en
Transformar datos.

63 | P á g i n a
Microsoft Excel Avanzado

4. PQ visualiza el archivo y muestra sus propiedades.


5. Presionamos la opción de combinar archivos para que PQ nos muestre el
contenido del archivo de texto.

6. En la ventana de combinar archivos PQ, nos muestra los datos y simplemente


hacemos clic en Aceptar.

64 | P á g i n a
Microsoft Excel Avanzado

7. El siguiente paso será comenzar con la limpieza de los datos.


8. Por último, cargamos los datos en nuestra hoja de Excel.

9. Copiar el archivo de texto Marzo.txt a la carpeta donde se encuentra el archivo


Febrero.txt, asumiendo que nos llegó el archivo del nuevo mes.

10. Crear una TD que muestre la fecha, el tipo de transacción y el valor.

65 | P á g i n a
Microsoft Excel Avanzado

11. Actualizamos la tabla resultante del PQ y la TD.


Fecha Tipo de Transacción Valor Total
1/2/2022 $ 471.785,00
C $ 414.900,00
D $ 56.885,00
1/3/2022 $ 267.840,00
C $ 249.697,50
D $ 18.142,50
Total general $ 739.625,00

6.1.3 Consolidación de tablas

La consolidación de datos consiste en fusionar varias tablas que tienen información de


interés y añadirla a otra, todo dependiendo de la información que necesitemos obtener,
para consolidarlas en una tabla maestra que contendrá todos los datos; normalmente a
este proceso se le llama también conexión entre consultas.

Ejercicio

Se necesita analizar las liquidaciones (ventas) del primer trimestre de la empresa.


El problema es que nos entregan un archivo de Excel, donde las ventas de cada mes
están en diferentes tablas, y distintas hojas.

Para resolver esto necesitamos:

• Unir las tablas de los distintos meses en una sola tabla principal.
• Crear una tabla dinámica resumiendo el total de liquidaciones por mes.

66 | P á g i n a
Microsoft Excel Avanzado

1. Vamos a obtener los datos de una tabla o rango, en este caso de la tabla enero
que está en la pestaña Enero.
2. IMPORTANTE – Al importar nuestra tabla, le indicamos a PQ que se va a crear
únicamente la conexión a la tabla, pero no la vamos a cargar en Excel.
3. Realizamos el mismo proceso para los otros meses.

4. Las consultas creadas se van a mostrar en nuestra hoja de Excel en el panel de


consultas y conexiones.
5. Existen dos formas para anexar tablas: la primera es desde el editor de Power
Query -pestaña Datos -Obtener y transformar datos - Iniciar Editor de PQ, y la
segunda forma es accediendo directamente a la herramienta desde la pestaña
Datos - Obtener y transformar datos - Combinar consultas y Anexar.

67 | P á g i n a
Microsoft Excel Avanzado

6. Dentro de nuestro PQ, seleccionamos cualquier consulta en este caso Enero,


nos dirigimos a la pestaña Inicio – Combinar – Anexar Consultas para crear
una nueva.

7. En nuestro caso, vamos a seleccionar la opción de anexar Tres o más tablas


ya que contamos con enero, febrero y marzo.
8. Procedemos a agregar todas las tablas que vamos a anexar.
9. Una vez anexadas nuestras tablas procedemos a realizar la limpieza de los
datos.
10. Cambiamos el nombre a nuestra tabla resultante, cerramos y cargamos
nuestra tabla principal en Excel.

68 | P á g i n a
Microsoft Excel Avanzado

11. Realizamos una TD con los datos necesarios para verificar que las tablas
correspondientes a los meses están anexadas.

6.1.4 Cruce de Tablas

Cruzar o combinar tablas, es un proceso que nos ayuda a consolidar una tabla existente,
agregando una columna o columnas que pertenecen a otra tabla.

69 | P á g i n a
Microsoft Excel Avanzado

Para usar este comando, primero debemos estar seguros de que hay una columna
común entre ambas, es un proceso similar a la relación entre tablas con un campo
común.

Tabla 1

Como definición de relaciones en BDD, es importante indicar que una de las columnas
que servirá para relacionar las tablas (columna común) no debe tener registros
duplicados y valores únicos.

Tabla 2

Tabla 1

Tabla Relacionada

70 | P á g i n a
Microsoft Excel Avanzado

Ejercicio

Problema: Se necesita realizar la conciliación bancaria.

Se cuenta con un archivo donde se cargan los cheques emitidos por la empresa y otro
emitido por el área de reportes que se entrega todos los meses con la información que
envía el banco.
La tarea es corroborar qué cheques han ingresado al banco, y cuáles están pendientes
por ingresar.

Se requiere:

1. Realizar dos consultas: una a cada archivo, y crear solo una conexión.
2. Limpiar los datos como corresponda.
3. Cruzar ambas consultas, reportando en una tabla los cheques pendientes y los ya
ingresados al banco.

71 | P á g i n a
Microsoft Excel Avanzado

Desarrollo:

1. Lo primero es analizar el contenido de la información en cada uno de los


reportes, e identificar la columna que nos permitirá relacionar o combinar las dos
tablas.
2. Podemos identificar que la columna que nos permite relacionar las dos tablas es
la columna que tiene el detalle del número de cheque, que en este caso debe
ser única en el reporte del banco, ya que el banco paga el valor del cheque una
sola vez. Número de comprobante en el reporte del banco y NroCheque en el
reporte de la empresa.
3. Es común que los sistemas al generar reportes agreguen información adicional
a cada campo, como es el caso del reporte generado por la empresa que agrega
un valor numérico (51) al inicio del dato del número de cheque.
4. Vamos a abrir un nuevo archivo de Excel y desde ahí vamos a crear las
conexiones a cada reporte desde PQ.

5. Seleccionamos la TablaChequesBancos, siempre las consultas deben hacerse


a las tablas luego hacemos clic en Transformar datos.

72 | P á g i n a
Microsoft Excel Avanzado

6. Guardamos y hacemos solo la consulta, no cargamos los datos.

7. Repetimos el mismo proceso con la TablaChequesPropios.


8. Lo siguiente es limpiar nuestros datos, para esto iniciamos el Editor de Power
Query.
9. En la consulta TablaChequesBanco, filtramos la información de los cheques que
es la única información que necesitamos de esa tabla y eliminamos las columnas
que no necesitamos.

73 | P á g i n a
Microsoft Excel Avanzado

10. En la consulta TablaChequesPropios, filtramos la información de los cheques


que es la única información que necesitamos de esa tabla y eliminamos las
columnas que no necesitamos.

11. Como lo indicamos anteriormente las columnas que nos permitirán relacionar o
combinar las dos tablas son: la columna Nro. Comprobante y NroCheque,
necesitamos que los datos de estas dos columnas deben tener la misma
estructura.
12. Procedemos a extraer los últimos 6 caracteres de la columna NroCheque del
reporte de la empresa, de esta manera aseguramos que los datos sean similares
entre tablas.

13. Verificamos en las dos tablas que el tipo de dato de las columnas sea el mismo.
14. Para facilitarnos la identificación de la columna a relacionar, podemos
estandarizar el nombre de la columna en las dos consultas.
15. Vamos a combinar las consultas, para eso identificamos el tipo de JOIN o
combinación con el que vamos a trabajar.

6.1.4.1 Tipos de combinaciones entre tablas

• Combinación externa izquierda

74 | P á g i n a
Microsoft Excel Avanzado

• Combinación externa derecha

• Combinación externa completa

75 | P á g i n a
Microsoft Excel Avanzado

• Combinación interna

• Anti-combinación izquierda

• Anti-combinación derecha

16. Vamos a identificar qué tabla es la que contiene los datos únicos o la tabla que
contiene mayor cantidad de datos. Para nuestro ejercicio la tabla

76 | P á g i n a
Microsoft Excel Avanzado

ChequesPropios es la que contine más datos, ahora esta tabla será nuestra tabla
izquierda.
17. En PQ pestaña Inicio – Combinar – Combinar consulta para crear una nueva.

18. Identificamos y seleccionamos las dos tablas. En la parte superior nuestra tabla
madre o tabla izquierda, y en la parte inferior la tabla derecha o tabla que
contiene los registros únicos. Además, seleccionamos el tipo de combinación
Externa Izquierda, ya que necesitamos consultar nuestros cheques cobrados y
no cobrados.

19. Seleccionamos las columnas que nos permitirán relacionar las tablas (columnas
comunes) en las dos tablas.

77 | P á g i n a
Microsoft Excel Avanzado

20. Power Query crea una nueva consulta, a la que hay que colocarle un nombre
donde nos indica las coincidencias para cada cheque de nuestra tabla izquierda
o madre TablaChequesPropios, en la que ha agregado una nueva columna
TablaChequesBancos donde nos indica el registro que tiene relacionado.

21. Vamos a expandir esa nueva columna, qué en verdad, es un registro completo
comprimido y seleccionamos las columnas de la TablaChequesBancos que
vamos a necesitar para realizar el cruce.

78 | P á g i n a
Microsoft Excel Avanzado

22. Vamos a agregar una columna nueva, que nos servirá para realizar la operación
de verificación entre los valores de los cheques.
23. Pestaña Agregar columna – Columna personalizada y colocamos la operación
que nos servirá de control para verificar que el cheque de la empresa fue pagado
con el valor que corresponde al Banco.

79 | P á g i n a
Microsoft Excel Avanzado

24. Ahora necesitamos agregar una Columna condicional, para que el resultado de
nuestras operaciones sea más amigable para quien analice la información.

25. Por último, volvemos a limpiar nuestra tabla, eliminando columnas que ya no nos
serán útiles para nuestra tabla final, cerramos y cargamos la tabla en Excel.

80 | P á g i n a
Microsoft Excel Avanzado

6.1.5 Base de Datos

Cuando hablamos de BDD normalmente imaginamos tablas de miles o millones de


registros. Sin embargo, Power Query nos permite usar solo conexiones a nuestras
tablas y poder trabajar con esas conexiones sin la necesidad de descargar las tablas
resultantes.

Ejercicio

Vamos a realizar una consulta, a la carpeta que contenga los datos para consolidar las
ventas de los vendedores y a excluir los archivos que se encuentren en la carpeta y que
no sean necesarios para nuestra consulta.

Necesitamos crear una conexión y mostrar los datos en una tabla dinámica.
Incorporar los nombres de los vendedores a nuestra tabla o consulta.
Identificar los créditos como valores positivos y los débitos como valores negativos.

1. Vamos a obtener los datos desde la carpeta Detalle Ventas, y presionamos en


Transformar datos.

81 | P á g i n a
Microsoft Excel Avanzado

2. Depuramos el contenido de nuestra carpeta, excluyendo y filtrando los archivos


que no necesitamos. Como ya sabemos, para anexar información de las tablas,
es necesario que los archivos tengan la misma extensión y estructura.

3. Procedemos a combinar los datos.

82 | P á g i n a
Microsoft Excel Avanzado

4. Es importante que PQ entienda qué es lo que necesitamos combinar. En este


caso, debemos proporcionar los parámetros exactos para que encuentre las
concordancias entre tablas, hojas y libros. Podemos observar que, PQ nos
muestra como parámetros una Tabla y una hoja Registro; debemos verificar que
en los otros archivos de las tablas tengan estos nombres en común, de esta
manera PQ sabrá qué datos combinar.

5. Seleccionamos el nombre de la tabla Registro, y que dato es común entre las


tres tablas, y clic en aceptar.

83 | P á g i n a
Microsoft Excel Avanzado

6. Limpiamos nuestra información.

7. Cerramos y creamos solo la conexión, asumimos que la BDD tiene más registros
de los permitidos por Excel, que son 1048576 registros.

84 | P á g i n a
Microsoft Excel Avanzado

8. Vamos a insertar una tabla dinámica desde una fuente de datos externa, de esta
manera obtenemos los datos desde nuestra consulta.

85 | P á g i n a
Microsoft Excel Avanzado

9. Creamos las tablas dinámicas necesarias.

10. Vamos a identificar los Débitos como valores negativos y los Créditos con
valores positivos, creando una columna personalizada con lenguaje M.

86 | P á g i n a
Microsoft Excel Avanzado

6.1.6 Solucionar Problemas

6.1.6.1 Agrupar registros

Power Query permite agrupar los valores de las columnas y filas. Además, podemos
agrupar las columnas incluyendo varias operaciones como suma, promedio, máximos,
mínimos, etc.

Ejercicio 1 – Agrupación Columnas

87 | P á g i n a
Microsoft Excel Avanzado

Ejercicio 2 – Agrupación Filas

6.1.6.2 Anulación de dinamización de columnas

PQ permite anular la dinamización de las columnas, en atributos para poder ser


analizados de mejor manera.

88 | P á g i n a
Microsoft Excel Avanzado

Ejercicio 1

Seleccionamos las columnas donde vamos a eliminar la dinamización de las columnas,


hacemos clic derecho sobre cualquiera de ellas y seleccionamos Anulación de
dinamización de columnas.

89 | P á g i n a
Microsoft Excel Avanzado

6.1.6.3 Columna Dinámica

PQ nos permite crear varias columnas en función de otra columna y de los datos que se
encuentren en ella, principalmente cuando ésta contenga información que pueda servir
de etiqueta para los datos, es lo contrario de la anulación de la dinamización de
columnas.

90 | P á g i n a
Microsoft Excel Avanzado

Seleccionamos la columna que vamos a utilizar como columna dinámica, nos dirigimos
a la pestaña Transformar – Cualquier columna – Columna dinámica.

Limpiamos nuestra tabla si fuese necesario, cerramos y cargamos.

6.1.7 Consulta Web

PQ es una herramienta que nos permite obtener datos de diferentes orígenes y uno de
éstos, es la obtención de datos de tablas en páginas web.

91 | P á g i n a
Microsoft Excel Avanzado

https://www.xe.com/es/currencycharts/?from=EUR&to=USD

Vamos a obtener los datos desde la web, para esto accedemos desde Excel a la URL
que contenga la tabla que nos servirá para extraer los datos.

92 | P á g i n a
Microsoft Excel Avanzado

Seleccionamos la tabla que contiene los datos que vamos a usar para el ejercicio.

93 | P á g i n a
Microsoft Excel Avanzado

7 POWER PIVOT

7.1 Definiciones
Power Pivot permite analizar, relacionar tablas, agregar columnas calculadas, crear
medidas, KPI´s y manejar la inteligencia de tiempo de una gran cantidad de datos de
distintas fuentes de datos externas, permitiéndonos construir nuestros propios tableros
de análisis y de BI de manera independiente.

Usamos Power Query para limpiar, dar forma y transformar los datos y Power Pivot,
para modelar los datos y definir cálculos.

94 | P á g i n a
Microsoft Excel Avanzado

7.2 Instalación Power Pivot


Para instalar la herramienta de Power Pivot, nos dirigimos a la pestaña Archivo –
Opciones – Complementos – Administrar – Complementos COM y activamos las casillas
de verificación de Power Pivot y Power Map y listo, vemos que la pestaña Power Pivot
esta agregada a nuestro menú de opciones de Excel. Para versiones anteriores al 2016
podemos descargar la herramienta desde la página oficial de Microsoft.

https://www.microsoft.com/en-us/download/details.aspx?id=29074

95 | P á g i n a
Microsoft Excel Avanzado

7.3 Agregar datos a Power Pivot


Para relacionar dos tablas, es necesario tener una Tabla Hechos - Puente o calendario,
que sirva para realizar la vinculación por medio de los campos comunes. Si esta tabla
no existe ya en nuestro modelo, es necesario crearla. Esta forma de crear la Tabla
Hechos es comúnmente usada al trabajar con la fecha. A esta tabla se la denomina
tabla calendario.

• Tabla Hechos. Normalmente se encuentra en el centro de nuestro modelo,


contiene las claves secundarias que permiten relacionarla con otras tablas. En
la relación, esta tabla es la que tiene la cardinalidad varios; permitiendo realizar
las búsquedas de una forma más rápida y guardar eventos, como las ventas
realizadas dentro de sus registros; esta tabla se modifica de manera continua.

• Tabla Dimensiones. Almacenan información relacionada con los procesos de


negocio. Responden a las preguntas “Qué”, “Cuándo”, “Quién” y “Cómo”, son
usadas en los reportes para aplicar filtros y segmentadores. Por ejemplo, una
tabla que contenga el registro de los vendedores, ciudades o países. Estas
tablas son comúnmente estáticas y en la relación, son las que tienen la
cardinalidad 1.

Tabla 1 - Dimensiones Tabla 2 - Dimensiones

Tabla Hechos - Calendario

96 | P á g i n a
Microsoft Excel Avanzado

Recuerden que para trabajar en Power Query y Power Pivot, siempre es recomendable
transformar el rango de datos, a formato de tabla y darle un nombre.

Nos ubicamos en cualquier celda de nuestra tabla, nos dirigimos a la pestaña Power
Pivot y hacemos clic en Agregar a modelo de datos.

La interfaz de Power Pivot consta de las siguientes partes:

97 | P á g i n a
Microsoft Excel Avanzado

Agregamos a nuestro modelo de datos las tres tablas de nuestro ejemplo.

En el menú de Power Pivot nos dirigimos a la pestaña Inicio - grupo Ver – Vista de
diagrama.

98 | P á g i n a
Microsoft Excel Avanzado

En esta vista, podemos comenzar a crear las relaciones entre las tablas para
posteriormente trabajar con nuestro modelo de datos.
Para formar las relaciones, seleccionamos el campo de la primera tabla que vayamos a
relacionar, y lo arrastramos al campo relacionado de la segunda tabla. Power Pivot
detecta automáticamente el tipo de relación que debe formar, según los datos de
nuestras tablas.

Una vez creado nuestro modelo de datos, ya podemos comenzar a crear nuestros
reportes utilizando tablas y gráficos dinámicos.

99 | P á g i n a
Microsoft Excel Avanzado

Accedemos a la pestaña Inicio – Tabla dinámica y podemos elegir la opción que más se
ajusta al reporte que necesitamos.

100 | P á g i n a
Microsoft Excel Avanzado

7.4 Inteligencia de tiempo


Para poder trabajar o relacionar tablas mediante un campo tipo fecha, es necesario la
creación de una Tabla Hechos Calendario que contenga las fechas con valores no
duplicados, que nos permita crear nuestro modelo de datos.

101 | P á g i n a
Microsoft Excel Avanzado

Power Pivot creará una nueva tabla donde se muestren los registros de las fechas
encontrados en las Tablas Dimensiones que forman parte del modelo, a la nueva tabla
Calendario podemos relacionarla con las otras tablas mediante el campo DATE, que es
el que contiene las fechas en el mismo formato.

Creamos una TD que nos permita visualizar el valor de los gastos e ingresos por mes,
o gastos por día, etc.

102 | P á g i n a
Microsoft Excel Avanzado

7.5 Lenguaje DAX

DAX - Data Analysis Expressions, es una recopilación de funciones, operadores y


constantes parecidas a las que usamos en Excel, que se pueden usar en una fórmula o
expresión para calcular y devolver uno o varios valores llamadas medidas sobre un
modelo de datos.

El lenguaje DAX permite agregar columnas, campos (medidas) y tablas a nuestros


modelos. DAX no trabaja sobre celdas, sino sobre tablas. Las funciones DAX, esperan
como argumentos: tablas o columnas y si fuese necesario trabajar sobre datos
específicos, se puede aplicar filtros a las fórmulas.
En DAX existen dos tipos de datos según los resultados que devuelven:
• Funciones Escalares – funciones que devuelven un valor único, normalmente
numéricos totales. SUM.
• Funciones de Tabla – funciones que devuelven como resultado una tabla o un
conjunto de resultados. FILTER.

Para trabajar con DAX es importante tomar en cuenta las siguientes recomendaciones:
• NO existen columnas sin función
o Medida1:=Tabla[columna]
o Medida1:=SUM(Tabla[columna])

103 | P á g i n a
Microsoft Excel Avanzado

• Asignar las medidas a las tablas que contienen las columnas numéricas usadas
en la formula.
• Las funciones DAX se evalúan según la tabla de origen, NO sobre las tablas
dinámicas.
• DAX no puede devolver cálculos parciales o por agrupaciones.
• Cada medida es independiente en DAX y no afecta a otras medidas, las medidas
se calculan de la tabla original.
• Para agregar una columna calculada, se usa el signo (=) y para agregar una
medida se usa los signos (:=).
• DAX no admite como símbolo de cálculo el signo de %, si se quiere realizar algún
cálculo se debe colocar el valor dividido para 100, 5% - 0,05.

https://cartasdax.com/

• Funciones de Agregación
Suma todas las filas de una columna.
SUM([Columna])
= SUM([ImporteVenta])
Cuenta el número de filas de una columna que tenga datos
COUNT([Columna]) numéricos o fechas.
= COUNT([ImporteVenta])
Cuenta el número de filas de una tabla.
COUNTROWS(Tabla) = COUNTROWS('Cliente')
= COUNTROWS( RELATEDTABLE(Ventas))
Devuelve el promedio de todos los números de una columna.
AVERAGE([Columna])
= AVERAGE([ImporteVenta])

Cuenta el número de filas con valores distintos de una columna.


DISTINCTCOUNT([Columna])
=DISTINCTCOUNT(Ventas[Ticket]))

104 | P á g i n a
Microsoft Excel Avanzado

Devuelve el menor valor numérico de una columna que tenga


MIN([Columna]) datos numéricos o fechas.
= MIN(Ventas[TiendaSK]))
Devuelve el mayor valor numérico de una columna que tenga
MAX([Columna]) datos numéricos o fechas.
= MAX(Ventas[TiendaSK]))

• Funciones de Conteo
Cuenta el número de filas de una columna, datos numéricos o
COUNT([columna)] fechas.
COUNT([Descripción])
Cuenta el número de valores de una columna.
COUNTA([columna)]
COUNTA([Descripción])

Cuenta el número de celdas vacías.


COUNTBLANK([columna)]
COUNTBLANK([Marcas])

Cuenta el número de filas de una tabla.


COUNTROWS(Tabla)
COUNTROWS(Calendario)

Cuenta el número de filas con valores únicos de una columna.


DISTINCTCOUNT([columna)]
DISTINCTCOUNT([Marcas])

• Funciones Lógicas
Comprueba si todos los valores lógicos son
AND(valorlogico1; valorlogico2;…)
verdaderos y devuelve un verdadero.
Comprueba si uno o varios de los valores
OR(valorlogico1; valorlogico2;…) lógicos son verdaderos y devuelve un
verdadero.
Comprueba una condición y devuelve un
IF(prueba_lógica;valor_si_verdadero;valor_si_falso) valor si ésta se cumple; u otro, si no.
IF([Ventas Totales]>5;0,05;0,02)

IFERROR(valor;valor_si_error) Devuelve un valor si la expresión es un error.

NOT(valor_lógico) Cambia un Falso por Verdadero y viceversa.

• Funciones de Fecha
Devuelve el número que representa la fecha en código de
DATE(año;mes;día)
fecha y hora.
TODAY() Devuelve la fecha actual en formato de fecha y hora
Devuelve un número del 1 al 7 que representa el día de la
WEEKDAY(fecha;tipo_retorno)
semana.
Mueve el conjunto de fechas dado por un intervalo
DATEADD (Tabla[columna],
específico.
<NumIntervDezpla>, <M-D-Y> )
DATEADD(Calendario[Date];-1;MONTH)

• Funciones de Texto

105 | P á g i n a
Microsoft Excel Avanzado

CONCATENATE Une dos cadenas de texto, en una sola.


Compara dos cadenas de texto y devuelve TRUE si son
EXACT
exactamente iguales, FALSE en caso contrario.
Devuelve la posición inicial de una cadena de texto, dentro de
FIND
otra cadena de texto.
Devuelve el número especificado de caracteres, desde el
LEFT
principio de una cadena de texto.
LEN Devuelve el número de caracteres de una cadena de texto.

LOWER Convierte todas las letras de una cadena de texto en minúsculas.


Devuelve una cadena de caracteres desde el centro de una
MID
cadena de texto, dada una posición inicial y una longitud.
Reemplaza parte de una cadena de texto, en función del número
REPLACE de caracteres que especifique, por una cadena de texto
diferente.
Devuelve el último caracter o caracteres de una cadena de texto,
RIGHT
en función del número de caracteres que especifique.
Quita todos los espacios del texto, excepto los espacios
TRIM
individuales entre palabras.
UPPER Convierte todas las letras de una cadena de texto en mayúsculas.

VALUE Une dos o más cadenas de texto, en una sola.

• Funciones de Agregación terminadas en “X”


Devuelve la suma de una expresión evaluada por cada fila de
SUMX(Tabla; <Expresión>)
una tabla.

Devuelve el número de filas que cumplen la expresión


COUNTX(Tabla; <Expresión>)
evaluada.

Calcula el promedio de una expresión evaluada por cada fila de


AVERAGEX(Tabla; <Expresión>)
la tabla

Evalúa una expresión por cada fila de la tabla y devuelve el


MINX(Tabla; <Expresión>)
menor valor numérico.

Evalúa una expresión por cada fila de la tabla y devuelve el


MAXX(Tabla; <Expresión>)
mayor valor numérico.

• Funciones de Navegación entre tablas


Devuelve un valor relacionado de una columna de otra tabla, con
RELATED(Tabla[columna]) relación 1:N
RELATED(PRODUCTOS[Precio])
Devuelve una tabla especificando filtros, con relación 1:N
RELATEDTABLE(tabla)
COUNTROWS(RELATEDTABLE(PRODUCTOS))

106 | P á g i n a
Microsoft Excel Avanzado

• Función CALCULATE
Evalúa una expresión en un contexto modificado por filtros, si se
necesita realizar cálculos con las funciones Y – O de diferente
CALCULATE(Operación;<Filtro
columna, se debe usar la función Filter, además de Calculate.
1>;<Filtro2>;…)
CALCULATE(AVERAGE(pedidos[Demora
dias]);pedidos[Destino]="Valles";pedidos[Resultado]="Entregada")

• Función FILTER
Devuelve una tabla filtrada, con datos de esta o diferentes
FILTER(<Tabla>,
columnas, debe estar combinada con otras funciones.
<FilterExpression>)
FILTER (Vendedor;Vendedor[Continente] = "Europa")

• Función SUMMARIZE
SUMMARIZE ( <Tabla> [, <GroupBy_ColumnName> [, Crea un resumen de la tabla de entrada
[<Nombre>] [, [<Expresión>] [, <GroupBy_ColumnName> agrupada por las columnas
[, [<Nombre>] [, [<Expresión>] [, ... ] ] ] ] ] ] especificadas. Tabla dinámica interna.

7.6 Medidas en Power Pivot


Las medidas en Power Pivot son similares a los campos calculados que conocemos en
las tablas dinámicas de Excel; por lo tanto, son campos que contienen algún cálculo que
nos permite medir resultados y realizar un análisis posterior.

Vamos a crear una medida que nos muestre el Total de Ingresos bruto (resta de ingresos
menos egresos) utilizando el lenguaje DAX, las medidas deben estar creadas dentro de
una tabla.

107 | P á g i n a
Microsoft Excel Avanzado

7.6.1 Columna Calculada

Para agregar una columna calculada a nuestro modelo, basta con colocar en la barra
de fórmulas el signo igual (=) seguido de la función DAX y posteriormente cambiar el
nombre de la columna.

108 | P á g i n a
Microsoft Excel Avanzado

7.6.2 Power Pivot y Power Query

Si es necesario trabajar o extraer datos de orígenes diferentes, a tablas del mismo libro,
se debe obtener los datos mediante Power Query, realizar el ETL e importar los datos a
una tabla o crear la conexión y, por último, agregar nuestros datos al modelo de datos.

7.7 Modelo de diagrama


Recordemos que, para realizar el modelamiento de datos debemos tener en claro el tipo
de tablas que conforman nuestro modelo. La tabla Hechos que normalmente se
encuentra en el centro de nuestro modelo, contiene las claves secundarias que permiten
relacionarla con otras tablas y la tabla Dimensiones que almacena la información
relacionada con los procesos de negocio, responde a las preguntas “Qué”, “Cuándo”,
“Quién” y “Cómo”, son usadas en los reportes para aplicar filtros y segmentadores.

Tabla 1 - Dimensiones Tabla 2 - Dimensiones

Tabla Hechos - Calendario

De igual manera, podemos encontrarnos con modelamientos de tablas donde nuestra


tabla hechos deba relacionarse con otra tabla hechos, mediante una tabla puente y no
solo sirva como enlace de otras tablas dimensión. Este tipo de relación entre tablas
hechos suelen dar inconvenientes al momento de realizar las consultas cuando una

109 | P á g i n a
Microsoft Excel Avanzado

tabla hecho contiene a otra tabla hecho, por lo que es aconsejable aplanar las tablas
mediante un proceso Power Query para COMBINAR las dos tablas, y de esta manera
tener solo una tabla hechos, llamada tabla puente o catálogo.

Como podemos observar, el valor del sueldo total por Detalle tiene los mismos valores
para cada registro y además Excel nos muestra un error donde nos indica que no existe
relación.

110 | P á g i n a
Microsoft Excel Avanzado

Para solucionar este inconveniente, vamos a utilizar Power Query, importando las dos
tablas y combinándolas. Es importante asegurarse que la segunda tabla sea la que
contiene los valores únicos.

El siguiente paso, será desplegar y agregar los campos necesarios para combinar las
tablas en una sola.

111 | P á g i n a
Microsoft Excel Avanzado

Cerramos y seleccionamos las opciones: Crear únicamente la conexión y Agregar estos


datos al Modelo de datos.

Regresamos a nuestro Power Pivot, eliminamos las tablas hechos que formaban la tabla
puente y realizamos la relación de nuestras tablas dimensión con la nueva tabla hechos
combinada.

112 | P á g i n a
Microsoft Excel Avanzado

Una vez solucionado el problema con las tablas hechos, podemos realizar nuestras
tablas dinámicas.

8 POWER MAP

8.1 Definiciones
Power Map es un complemento de Excel que nos permite visualizar información
georreferencial y mapas en 3D, para poder visualizar estos mapas es necesario
mantener una conexión a internet durante la visualización.
En las versiones posteriores al 2016 del Office, solo necesitamos activar el
complemento, mientras que, en las versiones anteriores, es necesario descargarlo e
instalarlo.

Con Power Map podremos asignar datos a mapas 3D desde una tabla de Excel o desde
un modelo de datos, visualizándolos en un espacio geográfico, por último, podremos
realizar capturas de pantalla y generar paseos guiados en video, exportarlos y
compartirlos.

8.2 Visualización de datos dentro de mapas


Para crear nuestro Power Map debemos ubicarnos en cualquier parte de la tabla que
contenga datos geográficos.

113 | P á g i n a
Microsoft Excel Avanzado

Mediante las herramientas de Power Map, podemos personalizar las visualizaciones,


administrar temas, agregar etiquetas, cambiar el modo de la visualización de los datos,
etc.

114 | P á g i n a
Microsoft Excel Avanzado

Al final, podemos realizar una captura de pantalla o acceder a los mapas creados,
cuando inicialicemos el libro de Excel.

115 | P á g i n a
Microsoft Excel Avanzado

En Power Map, podemos también trabajar con coordenadas (latitud y longitud).

116 | P á g i n a
Microsoft Excel Avanzado

Al agregar fechas al campo Fecha, Power Map nos permitirá crear videos de tiempo y
visualizar el crecimiento o decrecimiento de los datos en el transcurso de los períodos.

Para crear un video más dinámico, vamos a dirigirnos a la pestaña Opciones de escena
y configuramos las diferentes opciones.

117 | P á g i n a
Microsoft Excel Avanzado

Para reproducir el video, basta con hacer clic en la opción Reproducir paseo.

Ahora, si queremos exportar el video para poder incrustarlo en otra aplicación, debemos
dirigirnos a Crear video.

118 | P á g i n a
Microsoft Excel Avanzado

Finalmente, al utilizar Power BI, aumenta la capacidad de desarrollar dashboard sobre


nuestros datos, ya que ésta, es una herramienta insigne del análisis de datos de
Microsoft.

119 | P á g i n a

También podría gustarte