CURSO EXCEL

Nivel Avanzado
 MANUAL
Que gusto dar inicio al curso de Excel avanzado, donde aprenderemos una
amplia gama de herramientas y técnicas esenciales para elevar tus habilidades
en el análisis y gestión de datos, el presente manual te guiará, a través, de
diversos temas como la elaboración avanzada de gráficos, funciones para
análisis de datos, consolidación de datos, diferencias entre VLOOKUP y
XLOOKUP, regresiones estadísticas, algunas herramientas para el análisis
financiero, uso del Gestor de Escenarios, Power Query, PowerPivot, principios de
lenguaje DAX y una introducción a Google Sheets, donde te puedes apoyar
como material de consulta en el paso a paso de los anteriores temas, para que
así estes capacitado y puedas aprovechar al máximo las capacidades de Excel.

¡Prepárate para embarcarte en este emocionante viaje de aprendizaje y

dominar el arte de Excel avanzado!
Video 1 - Introducción técnicas avanzadas de elaboración de gráficos:
dinámicos y sparklines (Minigraficos)

Introducción técnicas avanzadas de

elaboración de gráficos: dinámicos y
sparklines (Minigraficos)

Los gráficos dinámicos son una herramienta que permite crear gráficos
interactivos y fácilmente actualizables basados en tablas dinámicas, estos
gráficos son especialmente útiles cuando se trabaja con grandes conjuntos de
datos, puesto que facilitan la visualización y análisis de tendencias y relaciones
entre los datos.

Entre algunos beneficios de los gráficos dinámicos se encuentran:

Ofrecen la posibilidad de interactuar con los gráficos mediante filtros,

segmentaciones y otros elementos, permitiendo explorar y analizar los datos de
manera más profunda y personalizada.

Los gráficos dinámicos se actualizan automáticamente al modificar o agregar

datos a la tabla dinámica asociada, lo que garantiza que siempre se muestre
la información más actualizada y precisa.

También, permiten arrastrar y soltar campos fácilmente para cambiar la forma en

que se presentan los datos en el gráfico, lo que facilita la adaptación a
diferentes necesidades de análisis.

Facilitan la comparación de múltiples series de datos en un solo gráfico, lo que

ayuda a identificar discrepancias o similitudes entre diferentes categorías o
períodos de tiempo.
Ofrecen una amplia gama de opciones de diseño y formato, lo que permite
adaptar el gráfico a las preferencias y necesidades específicas del usuario.

Los gráficos dinámicos permiten combinar y resumir datos de múltiples fuentes en

un único gráfico, lo que simplifica el análisis de información proveniente de
diversas hojas de cálculo o bases de datos.

Al proporcionar una representación visual de los datos, los gráficos dinámicos

ayudan a tomar decisiones informadas basadas en el análisis de tendencias y
patrones.

Al automatizar el proceso de creación y actualización de gráficos, los gráficos

dinámicos reducen el tiempo necesario para analizar y presentar información, lo
que permite a los usuarios enfocarse en tareas más importantes.

Los gráficos dinámicos son una herramienta fácil de aprender y utilizar, lo que
permite a los usuarios de todos los niveles de habilidad en Excel aprovechar sus
beneficios para mejorar el análisis y la presentación de datos.

Los pasos para crear un gráfico dinámico son:

• Selecciona un rango de datos en tu hoja de cálculo que contenga la
información que deseas analizar.
• Ve a la pestaña "Insertar" en la barra de herramientas y haz clic en "Gráfico
Dinámico"

• Elige dónde deseas colocar el gráfico dinámico (en la hoja actual o en una
nueva hoja) y presiona "Aceptar".
• Utiliza el panel "Campos de gráfico dinámico" para arrastrar y soltar los
campos que deseas incluir en el gráfico, estableciendo los ejes, valores y
filtros necesarios, cuando hablamos de "Ejes" y "Valores" en los gráficos
dinámicos de Excel, nos referimos a los componentes fundamentales que
determinan cómo se representan los datos en el gráfico, los ejes son las
dimensiones de los datos que se muestran en el gráfico, ejemplo en un
gráfico 2D, por lo general se tiene un eje horizontal (también conocido
como eje X) y un eje vertical (también conocido como eje Y); los valores
son los datos numéricos que se representan en el gráfico,

En un gráfico dinámico, puedes arrastrar y soltar campos de tu tabla de

datos a las áreas de "Ejes" y "Valores" para decidir qué datos quieres
representar y cómo quieres representarlos, además, puedes cambiar estos
campos en cualquier momento para ver diferentes representaciones de tus
datos.
 • Personaliza el diseño y formato del gráfico usando las opciones disponibles en
las pestañas "Diseño", "Formato" y "Análisis de gráfico dinámico”, igualmente
puedes adicionar filtros y demás.

Otros Gráficos:

Gráfico de cascada, también es conocido como gráfico de puente, es una

forma de visualización de datos que ilustra cómo una cantidad inicial se
incrementa y disminuye con una serie de valores intermedios, resultando en un
valor final, este tipo de gráfico es útil para entender o explicar la transición
gradual de un valor inicial a un valor final a través de varias etapas o
categorías.

Cada barra en el gráfico representa una parte de la suma total y las barras
pueden ser tanto positivas (incrementando el total) como negativas
(disminuyendo el total), el "puente" entre las barras indica que la barra siguiente
comienza donde termina la anterior, lo que facilita la visualización de la
acumulación o descomposición de valores.

Por ejemplo, un gráfico de cascada podría ser utilizado para mostrar cómo las
ganancias de una empresa se acumulan a lo largo de un año fiscal, con barras
que representan diferentes factores como ingresos, costos, impuestos, entre otros.

Para crear un gráfico de cascada realiza:

Haz clic en cualquier lugar dentro de tu tabla de datos.

Ve a la pestaña "Insertar" en la cinta de opciones de Excel y en el grupo "Gráficos", haz

clic en el icono de "Gráficos de cascada o de cotización".
Selecciona "Gráfico de cascada" y personalízalo.

Gráfico de Pareto es una representación gráfica de los datos que combina un

gráfico de barras y una línea de gráfico, el propósito de un gráfico de Pareto
es destacar los factores más significativos entre un conjunto de datos, como su
nombre lo indica se basa en el principio de Pareto, también conocido como
regla 80/20, sostiene que en muchos eventos aproximadamente el 80% de los
efectos provienen del 20% de las causas.

Algunas de las ventajas de los gráficos de Pareto, es la priorización porque

ayudan a identificar los factores o problemas más importantes que necesitan
atención, permitiendo a los equipos concentrarse en las áreas que
proporcionarán los mayores beneficios, así mismo al análisis de causa y efecto,
puesto que proporcionan una visualización clara de los problemas o factores,
ayudando a identificar las causas principales, igualmente presenta facilidad de
interpretación, siendo fácil de entender e interpretar, lo que permite una rápida
toma de decisiones.

Para crear un gráfico de Pareto debes realizar clic en cualquier parte de la

tabla.
Ve a la pestaña "Insertar" en la cinta de opciones de Excel, en el grupo "Gráficos",
haz clic en "Gráfico de histograma" y selecciona "Pareto".

Selecciona "Gráfico de Pareto" y personalízalo.

Gráfico Radial, también se le conoce como gráfico de radar o de araña, es

una representación gráfica de datos multivariantes en la forma de una gráfica
de dos dimensiones con tres o más variables cuantitativas representadas en ejes
que se inician desde el mismo punto, este tipo de gráfico es utilizado a menudo para
visualizar perfiles o para representar visualmente una comparación de múltiples
categorías.

Ejemplo: Se requiere llevar a cabo una evaluación de empleados en cinco

categorías diferentes y se tiene los siguientes datos:

Para crear un gráfico de Pareto debes realizar clic en cualquier parte de la

tabla.
Ve a la pestaña "Insertar" en la cinta de opciones de Excel, en el grupo "Gráficos",
haz clic en "Gráfico de Cascada" y selecciona "Radial".
Cada eje representa una de las categorías de la evaluación y cada línea representa
a uno de los empleados, este gráfico permite visualizar y comparar fácilmente las
fortalezas y debilidades de cada empleado en las diferentes categorías, sin embargo,
los gráficos de radar pueden ser difíciles de interpretar cuando se usan con demasiados
datos o categorías.

Los sparklines son gráficos en miniatura que se sitúan en una única celda de Excel, son
útiles para visualizar tendencias en una serie de datos como los precios de las acciones,
el clima, estadísticas, entre otros, sin la necesidad de crear un gráfico completo, por
ejemplo, se puede usar un sparkline en una hoja de cálculo de inventario para mostrar
la tendencia de ventas de un producto en particular o en un reporte de rendimiento
para mostrar cómo ha variado un KPI con el tiempo.

Algunas ventajas de los sparklines son: proporcionan una visualización de datos que
está directamente en línea con los datos, lo que significa que el contexto no se pierde,
como también debido a que son pequeños y se sitúan en una única celda, puedes
tener muchos sparklines en una hoja sin que se sienta abarrotada, además que son
excelentes para mostrar tendencias a lo largo del tiempo, incluso si los valores absolutos
no son necesariamente importantes.
Un ejemplo, una empresa vende tres tipos de productos (Producto A, Producto
B, Producto C) y ha registrado las ventas mensuales durante un año,
representada en la siguiente tabla:

Para crear Sparklines a partir de los datos anteriores, realizar clic en la celda
donde se quiere que aparezca el sparkline puede ser al final de la tabla o en
el costado derecho de alguna celda, ir a la pestaña "Insertar" en la cinta de
opciones de Excel, en el grupo "Minigráficos", haz clic en el de “Líneas”

Se realiza el mismo procedimiento para el resto de los productos.

Para cada producto en el ejemplo anterior, se puede visualizar rápidamente las
tendencias de ventas de cada producto a lo largo del año, igualmente se
puede personalizar los sparklines utilizando las opciones en la pestaña
"Minigrafico"

Funciones SUMIF, SUMIFS y COUNTIF

SUMIF o [Link] es una función en Excel que permite sumar los valores en un rango
especifico que cumplen con un criterio determinado, se utiliza principalmente para sumar
datos con base a una condición, es decir cuando se busque realizar una suma de un
conjunto de datos basado en un criterio específico, permitiendo así realizar cálculos
basados en condiciones específicas, lo que facilita el análisis de datos, evitando la
necesidad de filtros o tablas dinámicas.

Se debe tener en cuenta que la función SUMIF sólo permite sumar datos basados en un
único criterio, para sumar datos basados en múltiples criterios, tendrías que usar SUMIFS.
Un ejemplo, se tiene la siguiente tabla con las ventas mensuales de tres diferentes
productos:

Ahora, se requiere conocer la venta total de los meses en que las ventas del
Producto Sillas superaron las 140 unidades, para ello inicia ubicándose en la
celda donde quieres el resultado.

El primer argumento (B2:B7) es el rango en el que buscas el criterio, el segundo

argumento (">140") es el criterio y el tercer argumento (B2:B7) es el rango a sumar, por
ello Excel suma los valores del rango B2:B7 en los que el correspondiente valor en el
rango B2:B7 es mayor que 140 y como resultado será la suma total de las ventas del
Producto A para los meses en que las ventas superaron las 140 unidades.
SUMIFS o [Link] es una función en Excel que suma los valores en
un rango determinado que cumplen con múltiples criterios, es una extensión de
la función SUMIF que permite tener más de un criterio, dentro de sus ventajas se
encuentra que permite realizar cálculos basados en múltiples condiciones, lo que
facilita un análisis de datos más detallado y una mayor flexibilidad en los
cálculos.

Se debe tener en cuenta que si se utilizan muchos criterios en SUMIFS, puede

ralentizar el rendimiento de Excel, especialmente con grandes conjuntos de
datos.

Continuando con el ejemplo, donde se tiene la siguiente tabla con las ventas
mensuales de tres diferentes productos:

Ahora se busca saber la venta total de los meses en que las ventas del Producto Sillas
superaron las 140 unidades y las ventas del Producto Base Cama fueron menores que
180, para ello inicia ubicándose en la celda donde quieres el resultado.

El primer argumento (B2:B7) es el rango a sumar, luego, los pares de argumentos son los
rangos y criterios que deben cumplirse, entonces Excel suma los valores del rango B2:B7
donde el correspondiente valor en el rango B2:B7 es mayor que 140 y el
correspondiente valor en el rango C2:C7 es menor que 180, por consiguiente el
resultado será la suma total de las ventas del Producto Sillas para los meses en que las
ventas del Producto Sillas superaron las 140 unidades y las ventas del Producto Base
Cama fueron menores que 180.

COUNTIF o [Link] es una función en Excel que cuenta las celdas dentro de un
rango que cumplen con un criterio específico, esta función es muy útil cuando necesitas
saber cuántas celdas cumplen con una cierta condición, se utiliza cuando necesitas
contar las celdas que cumplen con una condición específica en lugar de sumar sus
valores, una de sus ventajas es que permite conocer rápidamente la cantidad de veces
que se cumple un criterio específico dentro de un conjunto de datos.

Al igual que SUMIF, COUNTIF está limitado a un solo criterio., si se necesita contar celdas
basadas en múltiples criterios, se tendrá que usar COUNTIFS.

Un ejemplo, se tiene la siguiente tabla con las ventas mensuales de un producto y se

requiere saber en cuántos meses las ventas superaron las 150 unidades.
Selecciona una celda en la que quieras que aparezca el resultado

El primer argumento (B2:B7) es el rango en el que se aplica el criterio y el segundo

argumento (">150") es el criterio, Excel cuenta las celdas en el rango B2:B7 que tienen
un valor mayor que 150, el resultado será la cantidad de meses en los que las ventas
superaron las 150 unidades.
Video 2 - Consolidación de datos y diferencias entre VLOOKUP o
BuscarV y XLOOKUP o BuscarX.

Consolidación de datos para fusionar

datos de varias hojas de cálculo en
una única hoja de cálculo

La consolidación de datos es una herramienta en Excel que combina valores de

rangos de celdas de varias hojas de trabajo en una única ubicación, esta
característica es especialmente útil cuando tienes datos distribuidos en varias
hojas de trabajo y quieres resumirlos en una sola porque al consolidar los datos
en una sola hoja de cálculo, se evita la redundancia de datos y el archivo de
Excel es más manejable, sin embargo se debe tener en cuenta que al consolidar
se debe unificar y mantenerse el mismo formato.

Un ejemplo, se tiene dos hojas de trabajo que muestran las ventas mensuales de
un producto en dos años diferentes:
Se requiere consolidar los datos de ventas de 2023 y 2024 en una única hoja
de trabajo, para ello abrir una nueva hoja de cálculo donde quieras que
aparezca la consolidación, en la cinta de opciones de Excel, ir a la pestaña
"Datos", “Herramientas de datos” y seleccionar "Consolidar", en el cuadro de
diálogo que aparece, selecciona la función de agregación que deseas utilizar
para la consolidación, sin embargo en este caso, vamos a seleccionar "Suma".

Realizar clic en "Agregar" en la sección "Referencias" y seleccionar el rango de

la primera hoja de trabajo que deseas consolidar (en este caso, el rango B2:B7
en la Hoja 1 Ventas año 2023), repetir este paso para agregar el rango de la
segunda hoja de trabajo (B2:B7 en la Hoja 2 Ventas año 2024), ssegúrate de
que las casillas de verificación "Fila superior" y "Columna izquierda" están
seleccionadas si tus datos tienen etiquetas de fila o de columna, finalmente hacer
clic en "Aceptar", con lo anterior Excel suma las ventas de cada mes en 2023 y
2024 y las muestra en la Hoja 3.
Así quedan consolidado la suma en la hoja 3

VLOOKUP y XLOOKUP

VLOOKUP o BuscarV es una función de Excel que se utiliza para buscar un valor
en la primera columna de un rango de celdas y luego devuelve un valor en la
misma fila de una columna especificada, es una de las funciones más utilizadas
en Excel

XLOOKUP o BuscarX es una función de Excel introducida en el año 2020 para

reemplazar VLOOKUP y HLOOKUP. XLOOKUP busca un valor en un rango (o
una matriz) y devuelve un resultado correspondiente de un rango o matriz
relacionada.
Diferencias entre Vlookup o BuscarV y Xlookup o BuscarX

• VLOOKUP o BuscarV solo puede buscar valores en la primera

columna del rango especificado y devolver el valor en la misma fila
de la columna de índice, por el contrario, XLOOKUP o BuscarX
puede buscar en cualquier columna (no sólo en la primera) y
devolver un valor de cualquier otra columna a la izquierda o a la
derecha, lo que proporciona una mayor flexibilidad.

• XLOOKUP o BuscarX tiene un argumento opcional que permite al

usuario especificar un valor de retorno en caso de que no se
encuentre el valor buscado, VLOOKUP o BuscarV, por otro lado, no
tiene esta opción y devolverá un error #N/A si no se encuentra el
valor.

• VLOOKUP o BuscarV hace una búsqueda aproximada por defecto,

mientras que XLOOKUP o BuscarX hace una búsqueda exacta por
defecto.

• XLOOKUP o BuscarX puede buscar en reversa, es decir, de abajo

hacia arriba o de derecha a izquierda, algo que VLOOKUP o
BuscarV no puede hacer.

• En VLOOKUP o BuscarV, se tiene que especificar el número de la

columna de la que se desea extraer el valor, en cambio en
XLOOKUP o BuscarX se especifica directamente el rango de donde
se quiere extraer el valor.

• Aunque VLOOKUP o BuscarV tiene una funcionalidad de búsqueda

binaria (cuando el rango está ordenado, VLOOKUP puede buscar
 más rápidamente), XLOOKUP o BuscarX no tiene esta funcionalidad
y siempre realiza una búsqueda lineal, sin embargo, esta es una
pequeña desventaja, ya que XLOOKUP o BuscarX supera a
VLOOKUP en casi todos los otros aspectos.

Ejemplo de VLOOKUP o BuscarV

Se tiene la siguiente tabla de ventas de empleados:

Se requiere buscar el nombre del empleado con el ID 2.

La anterior fórmula busca el valor 2 en el rango A2:C11, luego devuelve el

valor en la misma fila de la segunda columna, FALSO significa que queremos
una coincidencia exacta, luego presiona Enter y Excel mostrará el resultado:
"María".
Ejemplo de Xlookup o BuscarX

Basándonos en la misma tabla anterior, ahora se requiere buscar el total de

ventas del empleado "Carlos", para ello en una celda vacía se debe escribir
la siguiente formula:

Esta fórmula busca "Carlos" en el rango B2:B11 y devuelve el valor

correspondiente del rango C2:C11. presionar Enter y Excel mostrará el
resultado: 6000.
Se puede observar la función XLOOKUP es más intuitiva y flexible que
VLOOKUP porque no tienes que contar el número de columnas para encontrar
el valor que buscas.

Video 3 – Regresiones estadísticas, principios de estadística inferencial y

análisis financiero PRECIO, VA, VF, PAGO.

Regresiones estadísticas y principios de

estadística inferencial en Excel.
Las regresiones estadísticas son una herramienta que se utiliza para identificar y
cuantificar las relaciones entre dos o más variables, en Excel se puede realizar
una regresión lineal utilizando la herramienta Análisis de datos, que se encuentra
en la pestaña Datos.

La estadística inferencial, por otro lado, es una parte de la estadística que se

utiliza para hacer inferencias o predicciones sobre una población basándose
en muestras de datos.

Excel permite visualizar los resultados de una regresión, a través, de un gráfico

de dispersión con una línea de tendencia, lo que puede ayudarte a entender
mejor la relación entre las variables.
Así mismo, Excel proporciona una tabla de resultados de regresión completa, que
incluye la R cuadrada, los coeficientes de la regresión, el valor p y otros
indicadores estadísticos que ayudan a interpretar los resultados de la regresión.

Como Excel es una herramienta común en la mayoría de las empresas, la

posibilidad de realizar regresiones estadísticas en Excel significa que más
personas tienen acceso a estas técnicas de análisis.

Las regresiones pueden ser usadas para una amplia variedad de propósitos,
desde pronosticar ventas hasta analizar la correlación entre distintas variables.

Excel permite manejar múltiples variables independientes en una regresión

múltiple, lo que amplía las posibilidades de análisis.

Un ejemplo de regresiones estadísticas, se requiere analizar la relación entre las

horas de estudio y las calificaciones finales de los estudiantes, los datos se
exponen en la siguiente tabla:

Luego ir a la pestaña "Datos" y selecciona "Análisis de datos", Si no lo ves "Análisis

de datos", necesitarás habilitar el complemento Análisis de datos, para ello dar
clic en “Archivo” “Opciones” “Complementos” “Complementos en Excel” “ir”
Activar “Herramientas para análisis” y Aceptar

Continuando con el ejercicio dar clic en “Análisis de datos” y “Regresión”

En el cuadro de diálogo de Regresión, establece el rango de entrada Y
(Calificación final) en el campo Rango de entrada Y y el rango de entrada X
(Horas de estudio) en el campo Rango de entrada X, se debe asegurar que la
casilla "Etiquetas" esté marcada si has seleccionado las etiquetas de las
columnas, luego hacer clic en "Aceptar", Excel calculará los resultados de la
regresión y los mostrará en una nueva hoja de cálculo, para el respectivo análisis
del usuario.

En los resultados, puedes ver el valor R cuadrado que indica cuánta variación
en las calificaciones finales puede ser explicada por las horas de estudio,
también se puede ver la ecuación de la línea de regresión que se puede usar
para predecir la calificación final de un estudiante en función de sus horas de
estudio, entre otros análisis.
 Excel para análisis financiero: TASA, VA, VF,
PAGO
Excel es una herramienta fundamental para el análisis financiero y presenta varias
funciones financieras incorporadas, algunas de las funciones más comúnmente
utilizadas son TASA, VA, VF y PAGO.

TASA en Excel es una herramienta financiera que se utiliza para calcular la tasa
de interés por período de una anualidad, se puede usar para determinar la tasa
de interés de un préstamo o la tasa de retorno de una inversión, dado el número
de períodos de pago, el pago por período y el valor presente o futuro de la
inversión o préstamo, es utilizada a menudo en finanzas para calcular la tasa
de retorno de una inversión, la tasa de interés de un préstamo, la tasa de
crecimiento de una inversión, entre otras.

VA (Valor Actual): Calcula el valor actual de una serie de pagos futuros dados
a una tasa de descuento específica, se utiliza cuando quieres saber cuánto
valdrían en términos actuales una serie de pagos futuros.

VF (Valor Futuro): Calcula el valor futuro de una inversión basada en una tasa
de interés constante y un número de períodos, se utiliza para calcular cuánto
valdrá una inversión en el futuro si se invierte a una tasa de interés específica.

PAGO: Calcula el pago de un préstamo basado en pagos constantes y a una

tasa de interés constante, se utiliza para determinar el pago periódico de un
préstamo, hipoteca., leasing, entre otros.

En general las funciones financieras en Excel ofrecen una forma rápida y fácil de
realizar cálculos financieros complejos y presentan ventajas de eficiencia,
precisión, versatilidad, comodidad y flexibilidad, determinando de manera
rápida el valor de la inversión, definir base de tiempos, comparación de cálculos
entre otros.

Ejemplo TASA, Una persona está considerando adquirir un préstamo de automóvil

que requiere pagos mensuales de 2.721.500 Cop durante 5 años, es decir 60
meses y el valor presente del préstamo es de 100,000.000 Cop por ello requiere
saber cuál es la tasa de interés mensual.

Se aplica la función TASA, escribiendo “=TASA”, presionar Tab y dar clic en fx.
El resultado le puedes dar formato de porcentaje con decimales y significa que
la tasa de interés mensual para este préstamo es 1.77%.

Ejemplo VA, un inversionista está considerando invertir en un bono que paga

5.000.000 Cop al final de cada año durante 5 años y la tasa de interés es del
5% efectiva anual, se requiere conocer el valor actual del bono.

Se aplica la función VA, escribiendo “=VA”, presionar Tab y dar clic en fx.
El resultado significa que dada una tasa de interés anual del 5%, se debería
estar dispuesto a pagar alrededor de 21.647.383 Cop hoy por un bono que te
pagará 5.000.000 Cop al final de cada año durante 5 años.

Ejemplo VF, un padre de familia planea ahorrar para la universidad de su hijo,

para ello busca depositar 300.000 Cop al final de cada mes en una cuenta de
ahorros que ofrece una tasa de interés efectiva anual del 3%, durante los
próximos 18 años, es decir 216 meses, se requiere saber cuánto tendría en la
cuenta después de 18 años.

Se aplica la función VF, escribiendo “=VF”, presionar Tab y dar clic en fx.
El resultado que es un número positivo representa la cantidad de dinero que se
tendrá en la cuenta de ahorros después de 18 años, dados los parámetros de
inversión que se ingrese, para este ejemplo si se deposita 300.000 Cop al final
de cada mes en una cuenta de ahorros con una tasa de interés efectiva anual
del 3%, se tendrá alrededor de 85.782.105 Cop después de 18 años.

Ejemplo PAGO, una persona está pensando en comprar una finca, el precio de
la finca es de 30.000.000 Cop y planeas financiarlo con un préstamo de 5 años,
es decir 60 meses con una tasa de interés efectiva anual del 26.3%, se requiere
saber cuál es el pago mensual del préstamo.
Se aplica la función PAGO, escribiendo “=PAGO”, presionar Tab y dar clic en fx
El resultado significa que al adquirir un préstamo de 30.000.000 Cop con una
tasa de interés efectiva anual del 26.3% durante 5 años, se tendrá que hacer
pagos mensuales de 903.550 Cop para pagar completamente el préstamo.

Uso del Gestor de Escenarios para crear y analizar múltiples escenarios

Video 4 – Gestor de escenarios, Utilizar la función Power Query para importar,

transformar y cargar datos en hojas de trabajo.

Uso del Gestor de Escenarios para crear y

analizar múltiples escenarios.

El Gestor de Escenarios en Excel es una herramienta útil para evaluar una

variedad de posibles resultados, lo que se conoce como "escenarios", siendo
útiles en situaciones donde se quiere ver cómo cambiar ciertas entradas
afectará a ciertos resultados.

El gestor de escenarios es especialmente fundamental en la toma de decisiones

de negocios y análisis financieros, su importancia y ventajas incluyen:

Análisis de Múltiples Escenarios: El Gestor de Escenarios permite la creación y el

análisis de múltiples escenarios con diferentes variables de entrada, esto es
especialmente útil en la modelización financiera y de negocios, donde puede
ser crucial entender cómo las diferentes suposiciones impactan en los resultados.

Toma de Decisiones Informada: Al permitir el análisis de diferentes escenarios, el

Gestor de Escenarios puede ayudar a tomar decisiones más informadas, un
ejemplo de lo anterior es cuando permite ver cómo diferentes niveles de precio
o costos pueden impactar en el beneficio de una empresa.
Eficiencia: En lugar de cambiar manualmente las entradas de un modelo para
diferentes escenarios, el Gestor de Escenarios permite cambiar rápidamente de
un escenario a otro, ahorrando tiempo y esfuerzo.

Presentación de Información: La función Resumen de Escenarios proporciona una

forma clara y concisa de presentar y comparar la información de múltiples
escenarios.

Versatilidad: Aunque es particularmente útil en análisis financieros y de negocios,

el Gestor de Escenarios puede ser útil en una amplia gama de aplicaciones,
incluyendo la planificación de proyectos, la predicción de ventas, la
planificación de la capacidad y muchas otras áreas.

Análisis de Sensibilidad: El Gestor de Escenarios puede ayudar a realizar un

análisis de sensibilidad para determinar cómo pequeños cambios en las
variables de entrada pueden tener un impacto significativo en los resultados.

En resumen, el Gestor de Escenarios es una herramienta importante en Excel para

analizar múltiples suposiciones y para la toma de decisiones basada en esos
diferentes escenarios.

Ejemplo: Un experto requiere presentar un análisis financiero para un pequeño

negocio, para ello debe proyectar diferentes niveles de ventas y costos que
afectarán al beneficio neto.
Calcular el beneficio neto, puedes crear una celda que calcule "Ventas*Costo
por unidad - Gastos Fijos".

Para practicidad se recomienda nombrar las celdas (Separación entre palabras

con guion bajo) donde están los valores con los de la descripción así:

Pasos:

• Hacer clic en la pestaña "Datos" en la cinta de opciones de Excel e ir

hasta el grupo previsión, desplegar el menú en "Análisis de hipótesis" y dar
clic en “Administrador de Escenarios”.
• Hacer clic en "Agregar" para crear un nuevo escenario, en el cuadro de
diálogo "Agregar escenario", introduce un nombre para el escenario, por
ejemplo, "Escenario 1" en "celdas cambiantes", introduce las celdas que
quieres cambiar en este escenario (en este caso, las celdas
correspondientes a Ventas, Costo por unidad y Gastos Fijos), luego, haz
clic en "OK", igualmente se debe nombrar el escenario de base.
• Ahora introduce los valores que quieres para este escenario, para el
ejemplo se podría tener un escenario donde las ventas son 2.000
unidades, el costo por unidad es 40 y los gastos fijos 8000, hacer clic en
"OK" cuando hayas terminado.
Cuando quieras ver el escenario dar clic en mostrar.

• Repite los pasos anteriores para crear tantos escenarios como quieras.

• Una vez que hayas creado todos tus escenarios, puedes verlos todos
haciendo clic en "Resumen de escenario" en el Gestor de escenarios,
donde se genera otra hoja de calculo titulada “Resumen del Escenario”
 Función Power Query para importar,
transformar y cargar datos en hojas de
trabajo.
Power Query es una herramienta de Excel increíblemente potente y flexible que
proporciona extensas capacidades de manipulación de datos, esta herramienta
permite a los usuarios extraer datos de diversas fuentes, transformar esos datos
en un formato utilizable y luego cargarlos en Excel para su análisis posterior.

Power Query es catalogado por varios expertos como la "navaja suiza" de Excel
para la gestión de datos porque si tienes datos sucios o desordenados o si
necesitas combinar información de varias fuentes antes de analizarla, Power
Query es la solución.

La interfaz de Power Query se presenta como un editor independiente en Excel,

que puedes abrir yendo a la pestaña "Datos" y seleccionando "Obtener Datos".
Vista del Power Query:

Una vez que estás en el editor de Power Query, puedes seleccionar una fuente
de datos para importar, esto puede ser cualquier cosa, desde un archivo CSV
o una hoja de cálculo de Excel hasta una base de datos o una página web.
Después de importar tus datos, Power Query te ofrece una serie de opciones
para transformar esos datos, se puede limpiar datos, cambiar tipos de datos,
combinar datos de diferentes fuentes y mucho más, todas estas transformaciones
se pueden realizar utilizando las diversas opciones y herramientas disponibles en
las pestañas "Inicio" y "Transformar" de Power Query.
Formatear lo que se requiera, ejemplo:
Al equivocarse o si se requiere eliminar un paso realizado, se debe dar clic en
“PASOS APLICADOS” para la respectiva eliminación.

Así mismo, se puede duplicar tablas, eliminar columnas y renombrar de ser

necesario.

Duplicar, clic derecho en la tabla

Quitar otras columnas.

Quitar duplicados
Renombrar tablas, dando clic sobre el nombre de la tabla.

Puedes agregar columna

Puedes agregar columnas personalizadas

Realizar formulación en las nueva columna

v
v
Importar CSV
Finalmente, una vez que has terminado de transformar tus datos, puedes
cargarlos en una hoja de trabajo de Excel para su posterior análisis. se puede
hacer seleccionando "Cerrar y cargar" en la pestaña "Inicio" de Power Query.

Si requieres hacer otra modificación debes ir “CONSULTAS Y CONEXIONES” y dar

clic derecho y seleccionar editar en la tabla que se requiera.
Se transforman los datos que sean necesarios:

En la cinta de opciones puedes transformar los datos, dependiendo de lo que

necesite en la base de datos.
Una vez finalices puedes actualizar.

Datos Generales.

Lo que realmente distingue a Power Query es su capacidad para "recordar" y

repetir tus pasos de transformación de datos, es decir que si tus datos de origen
cambian o si importas nuevos datos, Power Query repetirá las mismas
transformaciones en los nuevos datos de forma automática, siendo una
característica que convierte a Power Query en una herramienta inestimable para
cualquier persona que trabaje regularmente con la gestión y el análisis de datos
en Excel.

Importación de Datos con Power Query

La importación de datos es el primer paso que debes realizar al utilizar Power

Query, esta herramienta es capaz de importar datos desde una amplia gama
de fuentes, incluyendo archivos locales como CSV o Excel, bases de datos como
SQL Server o Access, fuentes web y muchas más.

Para comenzar a importar datos con Power Query, primero debes abrir el editor.
Esto se puede hacer yendo a la pestaña "Datos" en la cinta de opciones de
Excel y seleccionando "Obtener Datos",
Una vez que estás en el editor de Power Query, puedes seleccionar la fuente de
datos que deseas importar, para hacer esto, ve a la pestaña "Inicio" y selecciona
"Obtener Datos", Verás una lista de categorías de fuentes de datos, como
"Archivo", "Base de datos", "Azure", "Web", etc. Al seleccionar una de estas
categorías, se te presentarán opciones específicas de esa categoría.

Por ejemplo, si seleccionas "Archivo", te aparecerán varias opciones como "Desde

Excel", "Desde CSV", "Desde XML", entre otros, al seleccionar una de estas
opciones, se abrirá un cuadro de diálogo que te permitirá navegar hasta el
archivo que deseas importar.

Después de seleccionar tu archivo y hacer clic en "Importar", Power Query

cargará tus datos en su editor, aquí podrás ver una vista previa de tus datos y
comenzar a realizar las transformaciones necesarias.
Es importante recordar que cuando importas datos con Power Query, estás
creando una conexión a tu fuente de datos, no estás importando los datos
directamente a tu hoja de trabajo, lo que significa que cualquier cambio que
hagas en los datos dentro de Power Query no afectará a tus datos originales,
sin embargo, una vez que estés satisfecho con tus transformaciones, puedes
cargar los datos transformados en tu hoja de trabajo de Excel seleccionando
"Cerrar y cargar" en la pestaña "Inicio".

Transformar Datos en Power Query

Una vez que hayas importado tus datos a Power Query, puedes empezar a
transformarlos para satisfacer tus necesidades específicas de análisis, Power
Query proporciona una amplia gama de herramientas y funciones para
transformar tus datos, desde operaciones simples como cambiar el tipo de datos
o renombrar columnas hasta operaciones más complejas como crear columnas
calculadas o pivotar y despivotar tus datos.

Para comenzar a transformar tus datos, primero necesitas seleccionar la columna

o las columnas que deseas transformar. Puedes hacer esto haciendo clic en el
encabezado de la columna.

Una vez que hayas seleccionado tu columna, verás una serie de opciones en la
cinta de opciones de Power Query, las opciones disponibles dependen del tipo
de datos de la columna seleccionada, por ejemplo, si seleccionas una columna
de texto, verás opciones para cambiar el caso del texto, dividir el texto en varias
columnas, reemplazar valores, etc.

Supongamos que deseas cambiar el tipo de datos de una columna. Para hacer
esto, selecciona la columna y luego ve a la pestaña "Transformar" en la cinta de
opciones, aquí verás una opción para "Tipo de datos", al seleccionar esta
opción, se te presentará una lista de tipos de datos a los que puedes convertir
tu columna.

Otra transformación común es la creación de columnas calculadas, se puede

hacer en la pestaña "Agregar columna" en la cinta de opciones, aquí
encontrarás varias opciones para crear columnas calculadas, incluyendo
columnas condicionales, columnas de fecha, columnas de texto, y más.

Es importante recordar que todas las transformaciones que hagas en Power

Query son pasos en una consulta, cada paso se registra en el panel de pasos
de consulta en el lado derecho del editor de Power Query, lo que significa que
puedes volver a cualquier paso de tu consulta en cualquier momento, lo que
hace que la transformación de datos con Power Query sea un proceso altamente
flexible y controlable.

Cargar Datos con Power Query

Después de importar y transformar tus datos con Power Query, el último paso es
cargar tus datos en una ubicación donde puedas realizar un análisis más
detallado, en la mayoría de los casos, esta ubicación será una hoja de trabajo
de Excel o un modelo de datos en Power BI.

Para cargar tus datos, primero debes asegurarte de que estás satisfecho con
todas las transformaciones que has realizado en el Editor de Power Query, una
vez que estés listo para cargar tus datos, haz clic en el botón 'Cerrar y cargar'
en la esquina superior izquierda del Editor de Power Query.

Al hacer clic en 'Cerrar y cargar', se te presentarán varias opciones, puedes

elegir cargar tus datos en una hoja de trabajo de Excel, cargarlos en el modelo
de datos para usar con Power Pivot, o solo crear una conexión a los datos sin
cargarlos en ninguna parte, en la mayoría de los casos, querrás cargar tus datos
en una hoja de trabajo o en el modelo de datos.

Si eliges cargar tus datos en una hoja de trabajo, se creará una nueva hoja de
trabajo en tu libro de Excel con tus datos, si eliges cargar tus datos en el modelo
de datos, tus datos estarán disponibles para usar con Power Pivot, donde
podrás crear tablas dinámicas y gráficos basados en tus datos.

Una vez que tus datos se han cargado, cualquier cambio que hagas en la
fuente de datos original puede ser fácilmente actualizado en tus datos
cargados, solo necesitas ir a la pestaña 'Datos' en la cinta de opciones de Excel
y hacer clic en 'Actualizar todo’, Power Query recordará todas las
transformaciones que realizaste en tus datos y las aplicará a los datos
actualizados.

Video 5 Análisis de datos con PowerPivot y Principios de lenguaje DAX.

Realización de análisis de datos con

PowerPivot

¿Qué es PowerPivot?
PowerPivot es un potente complemento de Microsoft Excel que amplía las
capacidades de análisis de datos del programa, PowerPivot mejora el análisis
de datos proporcionando tecnología que puede manejar grandes volúmenes
de datos, establecer relaciones entre datos de múltiples fuentes y realizar
cálculos avanzados.

PowerPivot está diseñado para recoger, conectar y explorar los datos de una
manera que las herramientas de análisis de datos tradicionales a menudo luchan
para hacer. Puedes importar datos de múltiples fuentes, tales como bases de
datos SQL, cubos de Analysis Services, feeds de datos y otros archivos de Excel,
y luego establecer relaciones entre ellos para obtener una visión completa de
tus datos.

PowerPivot también mejora el rendimiento de Excel. Utiliza tecnología de

almacenamiento en columnas, que comprime y almacena datos para un
rendimiento y una velocidad increíbles, esto hace posible trabajar con millones
de filas de datos en Excel sin que el rendimiento se vea significativamente
afectado.

Además de recoger y explorar datos, PowerPivot también te permite crear nuevos

cálculos y métricas para tus datos. PowerPivot utiliza un lenguaje de fórmulas
llamado DAX (Data Analysis Expressions) que permite crear fórmulas más
complejas y potentes que las que se pueden hacer en Excel solo, con DAX,
puedes crear medidas y cálculos que te ayudan a obtener una visión más
profunda de tus datos.

En resumen, PowerPivot transforma Excel de una herramienta de hoja de cálculo

en una herramienta de análisis de datos de alta potencia, si trabajas con
grandes conjuntos de datos, necesitas combinar datos de múltiples fuentes o
simplemente quieres hacer cálculos más avanzados, PowerPivot puede ser una
adición valiosa a tu conjunto de herramientas de Excel.

Cómo usar PowerPivot

El uso de PowerPivot en Excel implica una serie de pasos, que incluyen habilitar
el complemento, importar datos y crear relaciones entre diferentes conjuntos de
datos.

Habilitar PowerPivot

El primer paso para usar PowerPivot es habilitarlo en Excel, para ello sigue
estos pasos para hacerlo:

1. Haz clic en "Archivo" en la barra de menú en la esquina superior

izquierda de la pantalla de Excel.
2. Selecciona "Opciones" en el menú desplegable.

3. En la ventana "Opciones de Excel", haz clic en "Complementos".

 4. En el cuadro "Administrar" en la parte inferior de la ventana, selecciona
"Complementos de COM" y luego haz clic en "Ir".

5. Aparecerá una nueva ventana llamada "Complementos de COM".

Marca la casilla junto a "Microsoft Office Power Pivot" y luego haz clic
en "Aceptar".

Una vez habilitado, verás una nueva pestaña "Power Pivot" en la cinta de
opciones de Excel.
Importar datos a PowerPivot

Para realizar análisis de datos avanzados con PowerPivot, necesitarás importar

datos a la herramienta; PowerPivot es compatible con una amplia gama de
fuentes de datos, incluyendo bases de datos SQL, hojas de cálculo de Excel,
feeds de datos y mucho más. Aquí se explica cómo importar datos a PowerPivot.

Pasos para importar datos a PowerPivot

1. Haz clic en la pestaña "Power Pivot" en la cinta de opciones en la parte

superior de la pantalla.

2. Dentro de la pestaña "Power Pivot", selecciona "Administrar" en el grupo

"Base de datos de Power Pivot", al hacer clic en "Administrar", se abrirá la
ventana de PowerPivot.

3. En la ventana de PowerPivot, busca y selecciona el botón "Inicio" en la

esquina superior izquierda, luego selecciona "Obtener datos externos".
4. En el grupo "Obtener datos externos", tendrás varias opciones para
importar datos. Puedes elegir importar desde una base de datos, desde
servicios de datos o desde un archivo de datos (como un archivo Excel
o un archivo de texto).

5. Después de seleccionar la fuente de datos, seguirás las indicaciones para

importar tus datos, este proceso variará dependiendo de la fuente de
datos que estés utilizando, pero generalmente te pedirá que especifiques
detalles como la ubicación de la fuente de datos, las tablas o los campos
que deseas importar y cualquier filtro que quieras aplicar a tus datos.
 6. Una vez que hayas completado el proceso de importación, tus datos
estarán disponibles en PowerPivot y estarás listo para comenzar a
analizarlos.

PowerPivot puede manejar grandes volúmenes de datos, por lo que no tengas

miedo de importar grandes conjuntos de datos, además, puedes importar datos
de múltiples fuentes y luego establecer relaciones entre ellos para realizar análisis
aún más detallados.

Crear relaciones en PowerPivot

Uno de los aspectos más poderosos de PowerPivot es la capacidad de

establecer relaciones entre diferentes conjuntos de datos, esto te permite realizar
cálculos y análisis que abarcan múltiples tablas de datos, aquí te explicamos
cómo crear relaciones en PowerPivot.

Pasos para crear relaciones en PowerPivot

1. Una vez que hayas importado tus conjuntos de datos a PowerPivot,

puedes comenzar a establecer relaciones, en la ventana de PowerPivot,
 busca el botón "Diagrama" en el grupo "Ver". Haz clic en "Diagrama" para
abrir un diagrama de todas tus tablas importadas.

2. En la vista de diagrama, verás todas tus tablas de datos, con los nombres de
las tablas en la parte superior y los campos de cada tabla listados debajo,
para crear una relación entre dos tablas, necesitarás identificar un campo común
entre ellas, siendo un campo será la "clave" que relacionará las dos tablas, en
la mayoría de los casos, este será un campo como un ID de producto, un ID de
cliente, o cualquier otro campo que ambas tablas tengan en común.

3. Para crear la relación, simplemente haz clic y mantén presionado el campo clave
en la primera tabla, y luego arrástralo hasta el campo correspondiente en la
 segunda tabla. Al soltar el botón del ratón, se creará una línea entre las dos
tablas, que representa la relación que has establecido.

4. Una vez que hayas creado la relación, PowerPivot podrá usarla para
realizar cálculos y análisis que abarquen ambas tablas.

Crear relaciones efectivas es crucial para aprovechar al máximo PowerPivot y

realizar análisis de datos profundos y significativos.

¿Qué es DAX?
DAX, o Data Analysis Expressions, es un lenguaje de fórmulas utilizado en
PowerPivot, Power BI y otras tecnologías de Microsoft, permite a los usuarios
definir medidas y cálculos personalizados en modelos de datos tabulares, DAX
incluye funciones, operadores y valores constantes que pueden ser usados para
definir fórmulas y expresiones personalizadas.
Entendiendo DAX

DAX fue diseñado para ser intuitivo y fácil de aprender para los usuarios que ya
están familiarizados con las fórmulas de Excel, sin embargo, DAX tiene
capacidades mucho más amplias que las fórmulas de Excel, ya que puede
trabajar con datos relacionales y realizar cálculos que abarcan múltiples tablas
y columnas.

Al igual que en Excel, las fórmulas DAX comienzan con un signo igual (=), sin
embargo, a diferencia de Excel, DAX te permite referenciar columnas enteras en
tus fórmulas, no solo celdas individuales, esto es posible debido al modelado de
datos tabulares que proporciona PowerPivot, adicional DAX te permite nombrar
la formula antes del signo igual.

Excel

DAX

Además, DAX es capaz de entender y utilizar las relaciones que estableces entre
las tablas en tu modelo de datos, esto significa que puedes crear cálculos que
incorporan datos de múltiples tablas relacionadas.

DAX también incluye una serie de funciones útiles que puedes usar en tus fórmulas,
algunas de estas funciones son similares a las que encontrarás en Excel, como
SUM y AVERAGE, pero muchas son únicas para DAX y ofrecen capacidades de
cálculo avanzadas.
DAX es una herramienta poderosa y flexible para realizar análisis de datos en
PowerPivot, con un poco de práctica, puedes usar DAX para transformar tus
datos y extraer valiosos insights que no serían posibles con las fórmulas de Excel
por sí solas.

Características clave de DAX

DAX es una herramienta poderosa y flexible que permite a los usuarios realizar
análisis de datos avanzados en PowerPivot y otras tecnologías de Microsoft, a
continuación, se presentan algunas de las características clave de DAX.

1. Funciones DAX

DAX incluye una amplia gama de funciones que pueden ser usadas para definir
fórmulas y expresiones. Estas funciones caen en varias categorías, incluyendo
funciones matemáticas y estadísticas, funciones de texto y funciones de fecha y
hora. Muchas de estas funciones serán familiares para los usuarios de Excel, pero
DAX también incluye muchas funciones únicas que permiten análisis de datos más
avanzados.

2. Referenciación de Columnas

A diferencia de Excel, que se basa en referencias a celdas individuales, DAX te

permite referenciar columnas enteras en tus fórmulas, esto es posible gracias al
modelado de datos tabulares de PowerPivot, que permite a DAX trabajar con
datos relacionales.
3. Cálculos Contextuales

DAX tiene la capacidad de realizar cálculos contextuales. Esto significa que los
resultados de tus fórmulas DAX pueden variar dependiendo del contexto en el
que se utilicen. Por ejemplo, si utilizas la misma fórmula DAX en dos tablas
diferentes, los resultados pueden variar dependiendo de los datos en cada
tabla.

4. Uso de relaciones

DAX es capaz de entender y utilizar las relaciones que estableces entre las
tablas en tu modelo de datos. Esto significa que puedes crear cálculos que
incorporen datos de múltiples tablas relacionadas, proporcionando un potente
análisis multidimensional.

5. Medidas y columnas calculadas

DAX te permite definir tanto medidas como columnas calculadas. Las medidas
son cálculos que se realizan en el contexto de una visualización de datos, como
un gráfico o una tabla, y se basan en los datos que se están visualizando en
ese momento, las columnas calculadas, por otro lado, son cálculos que se
agregan a tus tablas de datos y que se calculan para cada fila de la tabla.

DAX ofrece una serie de características potentes que te permiten realizar análisis
de datos avanzados en PowerPivot y extraer insights valiosos de tus datos.

Crear una fórmula DAX

Crear una fórmula en DAX es similar a la creación de fórmulas en Excel, con

algunas diferencias clave debido a las capacidades avanzadas de DAX, a
continuación te explicamos cómo crear una fórmula DAX.

Pasos para crear una fórmula DAX

1. En la ventana de PowerPivot, selecciona la tabla en la que deseas crear

una nueva columna calculada o una medida.
2. Si estás creando una columna calculada, selecciona la celda en la fila
"Agregar columna" y escribe tu fórmula.

3. Si estás creando una medida, selecciona el botón "Medida" en la cinta

de opciones y luego "Nueva medida".
 4. Al igual que en Excel, todas las fórmulas DAX comienzan con un signo igual
(=) después del signo igual, puedes escribir tu fórmula, por ejemplo, si estás
creando una medida que calcula el promedio de una columna llamada
"Ventas", tu fórmula podría ser =AVERAGE('Tabla'[Ventas]).
5. Cuando hayas terminado de escribir tu fórmula, presiona Enter. PowerPivot
comprobará tu fórmula para asegurarse de que no haya errores, si hay
un error, PowerPivot te proporcionará un mensaje de error que te ayudará
a identificar el problema.

Recuerda, las fórmulas DAX pueden ser tan simples o tan complejas como
necesites, pueden incluir funciones, referencias a columnas, operadores y valores
constantes, con un poco de práctica, puedes usar DAX para crear fórmulas
potentes que te ayudarán a analizar tus datos de manera más eficaz.
 REFERENCIAS

Microsoft, (2023), [Link]