Manual Excel Avanzado
Manual Excel Avanzado
Manual Excel Avanzado
MICROSOFT EXCEL
AVANZADO
1|Página
Microsoft Excel Avanzado
2|Página
Microsoft Excel Avanzado
3|Página
Microsoft Excel Avanzado
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
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.
4|Página
Microsoft Excel Avanzado
5|Página
Microsoft Excel Avanzado
1.2 Subtotales
6|Página
Microsoft Excel Avanzado
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.
7|Página
Microsoft Excel Avanzado
Se pueden relacionar dos tablas de datos siempre y cuando existan datos que coincidan
entre ellas.
8|Página
Microsoft Excel Avanzado
9|Página
Microsoft Excel Avanzado
2 FUNCIONES AVANZADAS
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
2.1.2 BUSCARH
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.
2.1.4 COINCIDIRX
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(3;2)
2.1.6 ELEGIR
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.
12 | P á g i n a
Microsoft Excel Avanzado
2.1.8 ÍNDICE
2.1.9 INDIRECTO
=INDIRECTO (Referencia)
13 | P á g i n a
Microsoft Excel Avanzado
2.1.10 TRANSPONER
=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
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.
15 | P á g i n a
Microsoft Excel Avanzado
2.2.2 Sumar.Si.Conjunto
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
2.3.2 Contar.Si.Conjunto
2.3.3 Max.Si.Conjunto
17 | P á g i n a
Microsoft Excel Avanzado
2.3.4 Min.Si.Conjunto
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.
2.3.6 Promedio.Si.Conjunto
19 | P á g i n a
Microsoft Excel Avanzado
3 FORMATOS CONDICIONALES
20 | P á g i n a
Microsoft Excel Avanzado
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
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
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
23 | P á g i n a
Microsoft Excel Avanzado
24 | P á g i n a
Microsoft Excel Avanzado
• 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
• Utilice una fórmula que determine las celdas para aplicar formato.
26 | P á g i n a
Microsoft Excel Avanzado
=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
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
29 | P á g i n a
Microsoft Excel Avanzado
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.
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
MARCA
KIA
32 | P á g i n a
Microsoft Excel Avanzado
=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));"")
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
34 | P á g i n a
Microsoft Excel Avanzado
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:
36 | P á g i n a
Microsoft Excel Avanzado
37 | P á g i n a
Microsoft Excel Avanzado
38 | P á g i n a
Microsoft Excel Avanzado
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.
41 | P á g i n a
Microsoft Excel Avanzado
• No mostrar subtotales:
42 | P á g i n a
Microsoft Excel Avanzado
Esta opción nos permite elegir cómo se van a visualizar los totales de nuestra tabla
dinámica.
43 | P á g i n a
Microsoft Excel Avanzado
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
46 | P á g i n a
Microsoft Excel Avanzado
47 | P á g i n a
Microsoft Excel Avanzado
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.
48 | P á g i n a
Microsoft Excel Avanzado
49 | P á g i n a
Microsoft Excel Avanzado
50 | P á g i n a
Microsoft Excel Avanzado
51 | P á g i n a
Microsoft Excel Avanzado
52 | P á g i n a
Microsoft Excel Avanzado
53 | P á g i n a
Microsoft Excel Avanzado
54 | P á g i n a
Microsoft Excel Avanzado
55 | P á g i n a
Microsoft Excel Avanzado
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.
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
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.
57 | P á g i n a
Microsoft Excel Avanzado
58 | P á g i n a
Microsoft Excel Avanzado
• Desde un archivo
59 | P á g i n a
Microsoft Excel Avanzado
• Desde Azure
60 | P á g i n a
Microsoft Excel Avanzado
61 | P á g i n a
Microsoft Excel Avanzado
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.
• 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
63 | P á g i n a
Microsoft Excel Avanzado
64 | P á g i n a
Microsoft Excel Avanzado
65 | P á g i n a
Microsoft Excel Avanzado
Ejercicio
• 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.
67 | P á g i n a
Microsoft Excel Avanzado
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.
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
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:
72 | P á g i n a
Microsoft Excel Avanzado
73 | P á g i n a
Microsoft Excel Avanzado
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.
74 | P á g i n a
Microsoft Excel Avanzado
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
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.
81 | P á g i n a
Microsoft Excel Avanzado
82 | P á g i n a
Microsoft Excel Avanzado
83 | P á g i n a
Microsoft Excel Avanzado
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
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
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.
87 | P á g i n a
Microsoft Excel Avanzado
88 | P á g i n a
Microsoft Excel Avanzado
Ejercicio 1
89 | P á g i n a
Microsoft Excel Avanzado
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.
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
https://www.microsoft.com/en-us/download/details.aspx?id=29074
95 | P á g i n a
Microsoft Excel Avanzado
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.
97 | P á g i n a
Microsoft Excel Avanzado
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
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
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])
104 | P á g i n a
Microsoft Excel Avanzado
• 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])
• 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)
• 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
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.
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
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
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.
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
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.
113 | P á g i n a
Microsoft Excel Avanzado
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
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
119 | P á g i n a