29/11/21 11:04 print

1 Objetivo del módulo

Objetivo del módulo

¡Hola!, te damos la bienvenida al primer módulo del curso de Excel Avanzado 2016, creado por Campus Servicio Civil. Los objetivos de este módulo
son:
Profundizar el conocimiento de las funciones que
ofrece Excel.
Manejar y aplicar el concepto de matriz, en el
ámbito de las funciones Excel.
Conocer y aplicar las principales funciones
estadísticas.
Conocer y aplicar las principales funciones de
bases de datos.
Manejar el concepto de base de datos que permite
utilizar las respectivas funciones.
Conocer y aplicar las principales funciones de
Búsqueda y referencia.

2 Funciones especializadas que omitiremos

1.1. Funciones especializadas que omitiremos

Excel ofrece una amplia gama de funciones. En este módulo nos

referiremos únicamente a las siguientes agrupaciones:
Estadísticas
Bases de datos
Búsqueda y referencia
También desarrollaremos los conceptos que se requiere conocer para
aplicar las funciones que se indiquen.

about:blank 1/7
29/11/21 11:04 print

Las agrupaciones de funciones, mayormente especializadas, son:

Información
Financieras
Matemáticas y trigonométricas
Estadísticas
Ingeniería
Búsqueda y referencia
Cubo
Web
No obstante, en este módulo abordaremos únicamente las funciones
estadísticas y de búsqueda y referencia más utilizadas.

3 Concepto 1: Funciones que entregan una Matriz como Resultado

2. Conceptos

2.1. Concepto 1: Funciones que entregan una Matriz como Resultado

La mayoría de las funciones entregan como salida un valor, que queda Seleccionar el rango donde queremos que quede
registrado en la celda donde se escribió la fórmula. No obstante, existen
algunas funciones cuyo resultado es un rango, es decir, un conjunto de
valores que quedan registrados en celdas contiguas. registrado el resultado.
En estos casos, se debe:
Escribir la fórmula.
Apretar en forma simultánea Mayúscula – Ctrl –
Enter (En las funciones normales, basta con apretar
Importante Enter).

about:blank 2/7
29/11/21 11:04 print
Al revisar la fórmula se apreciará que ella aparece escrita entre
paréntesis de llave. No se puede borrar parte de un rango con este tipo
de resultados, se debe borrar todas las celdas.

4 Concepto 2: Base de datos

2.2. Concepto 2: Base de datos

Existe un grupo de funciones que se denominan de base de datos.

Para utilizarlas se debe tener claro la estructura de los datos para ser
utilizadas.

Para efecto de las funciones, base de datos es una estructura con

información desplegada conforme al ejemplo siguiente:
Cada columna se denomina campo y cada campo tiene un
nombre, que aparece en la primera fila de la base.

Es un rango que se escribe en alguna zona de la hoja donde se

establecen algún criterio que permite seleccionar determinadas filas
de la base de datos. La estructura es uno o más nombres de campos y
una expresión que caracteriza ciertos valores.
Un ejemplo de criterio simple sería el siguiente:

about:blank 3/7
29/11/21 11:04 print

Ejemplo de Ejemplo de
criterio criterio
intermedio complejo

Se pueden agregar columnas con Se pueden agregar filas con

campos adicionales a los criterios, condiciones adicionales, lo cual
que significa que se deben significa que se selecciona de la
cumplir simultáneamente ambas base de datos los registros que
condiciones (Condición Y) por cumplen una u otra de las
ejemplo: condiciones (Condición O), por
ejemplo:

Este criterio indica que se

seleccionarán las filas que Este criterio indica que se
cumplen simultáneamente las seleccionarán:
condiciones de “Edad mayor que Las filas que cumplen
50 años” y “Género igual a M”. simultáneamente las condiciones
de “Edad mayor que 50 años” y
“Género igual a M”. También
selecciona las filas que cumplen
simultáneamente con tener
Género M y edad menor a 30
años.
La condición se expresa como
(Edad mayor que 50 Y Género M)
O (Edad menor que 30 Y Género F).

Según este criterio se seleccionarían sólo las filas cuyo campo

Edad tiene un valor mayor que 50.

5 Funciones más utilizadas en Excel 2016

2.2.1. Funciones más utilizadas en Excel 2016

Las funciones estadísticas más utilizadas son las siguientes:

about:blank 4/7
29/11/21 11:04 print

Por favor, antes de avanzar, descarga el siguiente material. En él encontrarás contenido extra, además de la plantilla de Excel utilizada en este
módulo.
Estas funciones, en general, hacen cálculos sobre una columna de una base de datos, considerando sólo los registros (filas) que cumplen las
condiciones Indicadas en un rango de criterios. Las principales son:

Las principales funciones de búsqueda y referencia son:

about:blank 5/7
29/11/21 11:04 print

Función TRANSPONER: esta convierte filas en columnas y viceversa.

Para utilizarla, en este ejemplo debemos tener escrito nombres de
meses en forma horizontal, entre las celdas B2 y G2. Queremos que
los valores se vean hacia abajo, entre las celdas A5 y A10.
Seleccionamos el rango A3:A8
Escribimos TRANSPONER(B2:G2)
Apretamos Mayúscula – Ctrl – Enter
Obtenemos el resultado que aparece en color amarillo.

6 Actividad

Actividad

Respuesta correctas

about:blank 6/7
29/11/21 11:04 print

7 cierre

¡Muy bien!

Has terminado el primer módulo del

curso. Ahora es momento de regresar a
la plataforma y continuar con el
siguiente módulo.

Terminar y volver a la plataforma

about:blank 7/7
29/11/21 11:07 print

1 Objetivo del módulo

Objetivo del módulo

¡Hola!, te damos la bienvenida al segundo módulo del curso de Excel Avanzado 2016, creado por Campus Servicio Civil. Los objetivos de este módulo
son:
Revisar resultados, ante diferentes valores en una
celda.
Saber cómo utilizar “Buscar Objetivo” para conocer
el valor que debe tener una celda para alcanzar un
resultado deseado.
Saber cómo utilizar “Tablas”, para visualizar
resultados de una o más fórmulas ante diferentes
valores en una celda.
Saber cómo utilizar “tablas”, para visualizar
resultados de una fórmula ante diferentes valores
en dos celdas.
Saber cómo Definir y guardar diferentes conjuntos
de información (parámetros y resultados) que
caracterizan diferentes escenarios.

2 Buscar objetivo, elegir comando

1.1. Buscar objetivo, elegir comando

Consiste en buscar que valor se debe asignar a una celda, para obtener un determinado resultado.

Escoger Datos – Análisis de Hipótesis – Buscar objetivo

Se solicitan tres datos:
about:blank 1/4
29/11/21 11:07 print
1. Definir la celda (Celda que contiene el resultado)
2. Con el valor (Valor que queremos alcanzar en la celda con el
resultado)
3. Cambiando la celda: Celda que queremos modificar hasta alcanzar
el resultado.

La planilla siguiente es un ejemplo donde existen dos resultados

(Utilidad y Rentabilidad, en celdas D7 y D8 respectivamente) que
dependen del Precio, Unidades y Costo Fijo (contenidos en celdas D2,
D3 y D4, respectivamente).

3 Escribir una zona de análisis

2. Escribir una zona de análisis

2.1. Escribir una zona de análisis

Rellenar, en una parte de la hoja, hacia abajo, los diferentes datos que
puede tomar la celda.
Escribir la o las fórmulas a analizar, en la parte superior y a la derecha de
los valores anteriormente indicados. Se recomienda escribir referencia a
la celda donde está el resultado, en lugar de mover la fórmula. Por
ejemplo, escribir =D8 si en la celda D8 está el resultado que interesa.
Seleccionar el rango que incluya los valores escritos y las fórmulas
Escoger Datos – Análisis de Hipótesis – Tabla
Se solicitan dos datos:
1. Celda de Entrada (fila): Dejarla vacía.
2. Celda de Fila (Columna): Seleccionar la celda que tomará los valores.
Por favor, antes de avanzar, descarga el siguiente material. En él encontrarás contenido extra, además de la plantilla de Excel utilizada en este
módulo.

about:blank 2/4
29/11/21 11:07 print

4 Actividad

Actividad

Respuesta correctas

5 Cierre

¡Muy bien!

Has terminado el segundo módulo del

curso. Ahora es momento de regresar a
la plataforma y continuar con el
siguiente módulo.

about:blank 3/4
29/11/21 11:08 print

1 Objetivo del módulo

Objetivos del módulo

¡Hola!, te damos la bienvenida al tercer módulo del curso de Excel Avanzado 2016, creado por Campus Servicio Civil. Los objetivos de este módulo
son:
Profundizar el conocimiento de las funciones que
ofrece Excel.
Manejar y aplicar el concepto de matriz, en el
ámbito de las funciones Excel.
Conocer y aplicar las principales funciones
Matemáticas.
Conocer y aplicar las principales funciones Lógicas.
Conocer y aplicar las principales funciones de
Texto.
Conocer el criterio numérico que utiliza Excel, para
representar fecha y hora y que permite operar
sobre esos datos.
Conocer y aplicar las principales funciones de
Fecha y Hora.

2 1.1. introducción

1.1 Introducción

Matemáticas
Lógicas
De fecha y hora
De texto

about:blank 1/6
29/11/21 11:08 print
También desarrollamos los conceptos
que se requiere conocer para aplicar las
funciones que se indiquen.

Algunas de las funciones se refieren a Información

campos particulares del conocimiento, Financieras
por lo que no serán tratadas, dejando su Matemáticas y trigonométricas
estudio al especialista que requiera
trabajar con ellas. Al profesional Estadísticas
especialista, en su respectivo campo del Ingeniería
conocimiento, le debiera resultar fácil Cubo
aplicarlas, revisando la descripción de las
funciones en el sistema, mediante el Web
comando Fórmulas – Insertar Función.
Las agrupaciones de funciones, No obstante, en este módulo abordaremos únicamente las funciones matemáticas más
utilizadas.
mayormente especializadas, son:

3 2. Fecha, Hora y texto

2. Fecha, Hora y texto

Para utilizar funciones de fecha y hora se

debe tener claro cómo se representa ello
en una planilla de Excel.
Los días están numerados a partir del 1°
de enero de 1900. Por ejemplo, el
número 5 representa el 5 se enero de
1900. Para comprobarlo, basta con
escribir el número 5 en una celda y
aplicarle un formato de fecha (que
muestra el número en un determinado
formato, pero no lo transforma.

about:blank 2/6
29/11/21 11:08 print

El día 12 de
noviembre de
2020 es el
número 44.147.
Eso lo podemos
comprobar
escribiendo
12/mar/2020.
Excel interpreta
que el usuario
quiere escribir
una fecha,
escribe el
número 44.147
y los muestra en
formato de
fecha. Para
comprobarlo,
escribamos el
número y
apliquemos el
formato de
número
“General”.

La Hora es una
fracción del día.
Por ejemplo:
Medio día
será 0,5
10 minutos
será 10 / (24
* 60). Porque
24 * 60 son
los minutos
que tiene un
día
4 horas será
4 / 24
Eso se puede
visualizar en el
ejemplo
siguiente. Se
escribe el valor
y a la derecha el
mismo valor en
un formato de
número.

Con estos conceptos claros, es

posible hacer operaciones como
sumar y restar fechas y horas,
además de aplicar adecuadamente
las funciones de fecha y hora que se
muestran más adelante.

Se puede utilizar el símbolo “&” para escribir fórmulas

que unen textos. Por ejemplo, en la celda A1 tenemos el
nombre y en la celda B1 el apellido y necesitamos el = A1 & “ “ & B1
nombre y el apellido en una misma celda. Podemos
about:blank 3/6
29/11/21 11:08 print
escribir una fórmula que combine ambas celdas: = A1 &
B1. El problema es que el nombre y el apellido
quedarán unidos, sin espacio entre medio. Para resolver
eso agregamos entremedio el símbolo que
necesitamos poner, encerrado entre comillas dobles, en
la siguiente forma:

4 3.2. Funciones

3.2. Funciones

Las funciones matemáticas más comunes son las

siguientes:

Las funciones de lógica más comunes son las

siguientes:

about:blank 4/6
29/11/21 11:08 print

Las funciones de fecha y hora más comunes son las

siguientes:

Las funciones de texto más comunes son las siguientes:

Por favor, antes de avanzar, descarga el siguiente material. En él encontrarás contenido extra, además de la plantilla de Excel utilizada en este
módulo.

5 Actividad

Actividad

6 Cierre
about:blank 5/6
29/11/21 11:08 print

¡Muy bien!

Has terminado el tercer módulo del

curso. Ahora es momento de regresar a
la plataforma y continuar con el
siguiente módulo.

Terminar y volver a la plataforma

about:blank 6/6
29/11/21 11:08 print

1 Objetivos del módulo

Objetivos del módulo

¡Hola!, te damos la bienvenida al cuarto módulo del curso de Excel Avanzado 2016, creado por Campus Servicio Civil. Los objetivos de este módulo
son:

Comprender las referencias circulares.

Saber en qué consiste el cálculo iterativo y las
situaciones en que se utiliza.
Saber cómo utilizar iteraciones para buscar
objetivos en muchas fórmulas.

2 1.1. introducción

1.1 Introducción

En forma directa (por ejemplo, en A3

se escribe la fórmula =A3+8), o
En forma indirecta (por ejemplo, en A3
se escribe = A2+7 y en A2 una fórmula
que diga =A3)

about:blank 1/6
29/11/21 11:08 print

Normalmente la existencia de una referencia circular se

produce por una equivocación, por lo que Excel avisa la
existencia de una referencia circular, mediante un
mensaje abajo a la izquierda, donde se indica cual es la
celda con problemas.
Al tener una referencia circular, Excel debe hacer
cálculos eternamente, ya que debe calcular el valor de
la primera celda y después la segunda. Como la
segunda depende de la primera debe calcularse
nuevamente y así eternamente. En lenguaje
informático se denomina “loop”.

En algunos casos las referencias circulares no se tratan

de errores, sino que de una situación donde realmente
una fórmula debe depender de sí misma. En estos
casos, para evitar el loop, se debe escoger una opción
que limita el número de veces que se realiza el cálculo
(denominado, en lenguaje informático, número de
iteraciones).
Para que Excel calcule las referencias circulares, sin
acusar error, y establecer el número de iteraciones, se
debe escoger Archivo - Opciones – Fórmulas y Activar
la casilla “Habilitar Cálculo iterativo”.

Existen dos aplicaciones típicas de las referencias

circulares:
Sencilla: El problema obliga a escribir una referencia
circular y Excel, al reconocer la situación, no realiza
el cálculo. En este caso, se debe indicar en Excel que
about:blank 2/6
29/11/21 11:08 print
se habilite el cálculo iterativo y el problema se
resuelve, normalmente con una iteración.
Compleja: El problema es del tipo “buscar un valor
objetivo en una fórmula”. Si es una o pocas fórmulas,
se puede resolver utilizando el comando Datos-
Buscar Objetivo. Si se trata de muchas fórmulas, es
más eficiente utilizar un método de iteraciones, que
se vaya aproximando al resultado deseado

3 1.2. Cuándo se usa, para qué se usa

1.2. Cuándo se usa, para qué se usa

El cálculo iterativo se usa cuando se

tiene un conjunto de fórmulas, cada una
requiere llegar a un valor objetivo
determinado y el número es tan grande
que es demasiado ineficiente utilizar
“Buscar Objetivo” en cada una.
Cuando existe una fórmula que no se
puede despejar, por lo que debemos
utilizar un método iterativo para
encontrar un valor. Esto es común en
varias áreas de la ingeniería, por ejemplo:

Se usa para determinar el valor de una

fórmula que depende de sí misma, en
virtud de otra fórmula, lo que requiere
iterar (repetir varias veces un mismo
cálculo, para converger a un resultado
final).

about:blank 3/6
29/11/21 11:08 print

4 1.3. Ejemplo 1 – Referencia circular sencilla

1.3. Ejemplo 1 – Referencia circular sencilla

Sea la siguiente situación:

Se cuenta con datos de ingresos y gastos de ciertos
productos
Se debe pagar un arriendo, igual al 15% de la
utilidad final, calculada ésta incluyendo el arriendo.
Al escribir las fórmulas deseadas se producen
referencias circulares:

Al escribir las fórmulas ocurre lo siguiente:

Las fórmulas no se calculan y aparece,

abajo a la izquierda, el mensaje de
advertencia sobre Referencia Circular.
Para resolver la situación vamos a
Opciones – Fórmulas y habilitamos
cálculo iterativo:

about:blank 4/6
29/11/21 11:08 print

La planilla quedará con el problema resuelto

Por favor, antes de avanzar, descarga el siguiente material. En él encontrarás contenido extra, además de la plantilla de Excel utilizada en este
módulo.

5 Actividad

Actividad

6 Cierre

¡Muy bien!

Has terminado el cuarto módulo del

curso. Ahora es momento de regresar a
la plataforma y continuar con el
siguiente módulo.

Terminar y volver a la plataforma

about:blank 5/6
29/11/21 11:09 print

1 Objetivos del módulo

Objetivos del módulo

¡Hola!, te damos la bienvenida al quinto módulo del curso de Excel Avanzado 2016, creado por Campus Servicio Civil. Los objetivos de este módulo
son:

Saber cómo activar la Herramienta Solver.

Saber para qué tipo de problemas se utiliza Solver.
Conocer las diferentes estrategias de optimización
que tiene Solver.

2 1.1. introducción

1.1 Introducción

Una fórmula que se busca maximizar o minimizar.

Típicamente, se busca maximizar ganancias
minimizar costos o minimizar tiempos.
Celdas que contienen valores, de los que depende la
fórmula que se busca maximizar o minimizar.
Existen restricciones para los valores que pueden
tomar las celdas. Típicamente, estas restricciones
pueden ser capacidades de producción,
presupuestos disponibles, horas máximas de trabajo,
etc.

about:blank 1/5
29/11/21 11:09 print

El comando Solver no aparece disponible por defecto,

por lo que debe ser activado para poder ser utilizado.
El comando Solver realiza iteraciones, es decir, va
cambiando valores en las celdas, hasta llegar al
conjunto de valores que conduce al objetivo. Es posible
visualizar los valores que Excel fue utilizando para
llegar al óptimo, es decir, cada iteración.
Existe una opción de borrar lo ingresado (Restablecer
todo), y por otra parte una opción, por defecto, que
restringe todas las variables a números mayores que
cero.

3 1.2. Cuándo se usa, para qué se usa

1.2. Cuándo se usa, para qué se usa

El comando Solver no aparece disponible por defecto, por lo que debe ser activado para poder ser utilizado.
Se tiene una fórmula, cuyo resultado depende de varias celdas.

Se quiere optimizar el valor de esa fórmula (que sea máximo o mínimo), asignando valores a las celdas.

Existen restricciones a los valores que pueden tomar las celdas.

Solver lo utilizaremos para resolver problemas de optimización, por ejemplo:

Distribuir personal por turnos, de modo de minimizar el costo y satisfacer la demanda por los diferentes tipos de servicios que
demanda la comunidad a una entidad pública.

Maximizar el beneficio social, eligiendo una cartera de proyectos. Sujeto a restricciones presupuestarias.

Minimizar el costo de alimentación, en un casino, definiendo una dieta que satisfaga las necesidades nutricionales que
recomienda el Ministerio de Salud.

about:blank 2/5
29/11/21 11:09 print

4 1.3. Activar la opción Solver

1.3. Activar la opción Solver

Sea la siguiente situación:

Haga clic en la pestaña Archivo, elija Opciones y
después haga clic en la categoría Complementos.
En el cuadro Administrar, seleccione Complementos
de Excel y después haga clic en Ir.
En el cuadro Complementos, active la casilla Solver y
después haga clic en Aceptar.
Si Solver no se enumera en el cuadro Complementos
disponibles, haga clic en Examinar para encontrarlo.
Si se le indica que Solver no está instalado
actualmente en el equipo, haga clic en Sí para
instalarlo.
Después de esta operación, aparecerá la opción
Solver.

Ejercicio Resolver un
problema de producción

Tener una planilla que contenga una situación de

optimización. Se presenta a continuación una ya
creada, que cumple estas características:
Tenemos el nivel de producción de cuatro productos
(B3:B6) en amarillo
Nos interesa maximizar la ganancia (E7) en verde
El costo total, el espacio total y la mano de obra
total calculados, en J7, K7 y L7) en azul, no pueden
superar los límites establecidos en C9, C10 y C11

Escoger Datos – Solver

Completar según corresponde:

Resolver, después Aceptar y aparece la solución.
about:blank 3/5
29/11/21 11:09 print

Por favor, antes de avanzar, descarga el siguiente material. En él encontrarás contenido extra, además de la plantilla de Excel utilizada en este
módulo.

5 Actividad

Actividad

6 Cierre

¡Muy bien!

Has terminado el quinto módulo del

curso. Ahora es momento de regresar a
la plataforma y continuar con el
siguiente módulo.

about:blank 4/5
29/11/21 11:10 print

1 Objetivos del módulo

Objetivos del módulo

¡Hola!, te damos la bienvenida al sexto módulo del curso de Excel Avanzado 2016, creado por Campus Servicio Civil. Los objetivos de este módulo
son:
Conocer lo que es un Dashboard y los principios
que debieran regir para construir uno.
Conocer la forma en que se utilizan las Tablas
Dinámicas para preparar Dashboard.
Conocer la forma en que se utilizan los gráficos
para preparar Dashboard.

2 1. Introducción

1. Introducción

Consta de sólo una o pocas planillas

Enfatiza el uso de información gráfica,
que permite de una mirada visualizar
los datos
Muestra KPIs (Key Process Indicator),
es decir indicadores que tienen
sentido para visualizar la situación y
tomar decisiones.
Es personalizado para los objetivos
particulares de la organización.

about:blank 1/5
29/11/21 11:10 print

Un Dashboard consta, normalmente, de los siguientes

elementos:
Una hoja que contiene una base de datos con
información detallada y desagregada
Una hoja que contiene resúmenes de los datos
detallados. Estos se generan, normalmente, con la
herramienta Tablas Dinámicas
Una hoja que es el Dashboard mismo, que son
gráficos que utilizan los resultados contenidos en las
tablas dinámicas

Para generar Dashboard manipulables (es decir, el

usuario puede modificar parámetros, para que el
gráfico muestre diferentes ámbitos), se recomienda
utilizar los siguientes comandos:
Insertar – Tabla dinámica – Insertar segmentación de
datos
Insertar – Gráfico dinámico
Según las necesidades, se puede modificar la estética
como se desee, por ejemplo, las necesidades más
habituales son:
Eliminar grillas del fondo (seleccionar toda la hoja,
Formato de Celdas, Relleno de la trama y escoger la
trama sin relleno)
Eliminar los bordes de referenciación de la planilla
(Archivo, Opciones, Opciones de Presentación para
esta hoja y desactivar casilla “mostrar encabezado
de filas y columnas”)

about:blank 2/5
29/11/21 11:10 print

Cuándo se usa,
para qué se usa

Cuando se requiere resumir el

comportamiento de una organización o
área de ella, enfatizando el uso de
gráficos y de indicadores de gestión.
En general, se utilizan gráficos, tablas
dinámicas, funciones de posicionamiento
(índice y buscar), formatos condicionales
y comandos de formulario.

Cuándo se usa, para qué se

usa

Para presentar información resumida a directivos,

relacionada por ejemplo con:
Resultados de Servicio Entregados
Resultados de Ejecución Presupuestaria de Glosas
Estadísticas de Desempeño
Indicadores Gestión PMG- MEI
Desempeño de Personas
Desempeño por Servicios Públicos
Comportamiento de Usuarios de Servicios
En general cualquier información de una base de datos
que caracterice una situación y se requiere visualizar en
forma amigable, para efectos de control de gestión.
Por favor, antes de avanzar, descarga el siguiente material. En él encontrarás contenido extra, además de la plantilla de Excel utilizada en este
módulo.

3 1.3. Dashboard manipulable

about:blank 3/5
29/11/21 11:10 print

1.3. Dashboard manipulable

Veamos a continuación un video que

contiene un ejemplo de una Dashboard
manipulable.

4 Actividad

Actividad

5 Cierre

about:blank 4/5
29/11/21 11:10 print

¡Muy bien!

Has terminado el sexto módulo del

curso. Ahora es momento de regresar a
la plataforma y continuar con el
siguiente módulo.

Terminar y volver a la plataforma

about:blank 5/5
29/11/21 11:13 print

1 Objetivo del módulo

Objetivo del módulo

¡Hola!, te damos la bienvenida al séptimo módulo del curso de Excel Avanzado 2016, creado por Campus Servicio Civil. Los objetivos de este módulo
son:
Conocer las potencialidades de Excel en cuanto a
manejo de bases de datos externas.
Saber cómo crear una base de datos utilizando
Power Pivot.
Ser capaz de desplegar información resumida de
bases de datos múltiples, con Power Pivot.

2 Funciones especializadas que omitiremos

1. Introducción

1.1. Introducción

Power Pivot no aparece en las pestañas de Excel que aparecen por

defecto, debe habilitarse, si queremos utilizarlo, en la siguiente forma:
Vaya a Archivo > Opciones > Complementos.
En el cuadro Administrar, haga clic en Complementos COM> Ir.
Active la casilla Microsoft Office Power Pivot y haga clic en
Aceptar. Si tiene instaladas otras versiones del complemento
Power Pivot, estas también se muestran en la lista Complementos
COM. Asegúrese de seleccionar el complemento Power Pivot para
Excel.
about:blank 1/11
29/11/21 11:13 print

Power Pivot se utiliza cuando se requiere extraer información de

bases de datos de grandes volúmenes, alojados en diferentes
orígenes (p. ej. otras planillas Excel, archivos de texto, bases de datos
de Microsoft Access y bases de datos en SQL ).

Power Pivot lo utilizaremos para:

Generar resúmenes de datos, cuando estos no son posibles de
almacenar completamente en una planilla Excel, debido al tamaño
de las bases de datos que los contienen.
Hacer más eficiente el trabajo cuando se trabaja diariamente con
miles de datos, provenientes de fuentes muy diferentes.

3 Concepto 1: Funciones que entregan una Matriz como Resultado

about:blank 2/11
29/11/21 11:13 print

1.2. Pestañas principales de Power Pivot

Modelo de datos (Administrar)

En esta pestaña, con el botón Administrar, se entra al ambiente Power

Pivot. Donde se indica las bases de datos que se van a utilizar para
extraer datos, se definen las relaciones que existen entre los datos y
se generan las tablas dinámicas que permiten visualizar los
resultados.

Cálculos (Medidas y KPI)

En esta pestaña se definen medidas (cálculos sobre ciertos datos,

como sumas, promedios, etc.) e indicadores (divisiones o fórmulas
más complejas entre variables, que se utilizan para generar datos que
resumen información relevante para el usuario).

Tablas (Agregar a modelo de

datos)

Se utiliza para crear o modificar manualmente tablas de Excel que

pueden ser utilizadas dentro del modelo de datos.

Relaciones (Detectar)

Muestra, en un esquema, las diferentes tablas incluidas en el modelo

de datos y sus relaciones.

4 Concepto 2: Base de datos

about:blank 3/11
29/11/21 11:13 print

1.3. Conceptos para operar con Power Pivot

Obtener datos externos: De otros Orígenes

Permite seleccionar las bases de datos externas que se utilizarán.
Tabla dinámica: Permite definir los reportes que se generarán a partir
de las bases de datos seleccionadas.

Crear relación: Permite definir las relaciones entre campos de tablas

dinámicas.
Administrar relaciones: Permite modificar o eliminar relaciones
creadas previamente.

Dos Ambientes
Al seleccionar la primera pestaña del comando Power Pivot, nos
encontramos en un ambiente diferente al de aquel que utilizamos
normalmente en Excel. En este ambiente, en la barra inferior de la
planilla aparecen las diferentes tablas incluidas en el modelo. Cuando
cerramos la hoja, volvemos al ambiente Excel habitual.

Nociones de Base de Datos Tabla: conjunto de filas con información.

Power Pivot opera sobre bases de datos, por lo que resulta Registro: fila de una tabla.
conveniente contar con ciertos conocimientos generales en esos Campo: Cada una de las columnas de una tabla. Debe tener un
temas. Existe literatura abundante y especializada, por lo que se nombre.
presenta en forma sencilla lo mínimo que es conveniente dominar: Clave: Es un campo o más, que caracterizan en forma única un
registro (los campos claves no se repiten en una tabla).
Relación: Campos de diferentes tablas que se deben utilizar para
combinarlas. Normalmente es un campo de una tabla, que no es
clave, que se combina con el campo clave de otra tabla.

Ejemplo gráfico de definiciones

Se presenta un ejemplo de tabla con su nombre, cuatro campos, ocho
registros y un campo clave que identifica cada registro.

about:blank 4/11
29/11/21 11:13 print

Ejemplo gráfico de relaciones

Sean tres tablas:
Ventas: Muestra ventas de vehículos
Descripción: Caracteriza cada vehículo con nombre, precio y
descuento
Zona: Indica el nombre de la zona para cada código.

Importante

Para obtener reportes de venta con nombre de la zona y con los datos
de los vehículos se requiere partir de la tabla ventas y obtener datos
de las otras dos tablas. Para ello, se debe indicar al sistema cual es el
“camino” a seguir. Esto se hace identificando las relaciones.
En este caso, el campo código Auto, de la tabla ventas, debe
relacionarse con el campo clave de la tabla Descripción. El campo
Zona, de la tabla Ventas, debe relacionarse con el campo clave Código
de la tabla Zona.

5 Funciones más utilizadas en Excel 2016

1.4. Pasos para confeccionar un modelo en Power Pivot

En primer lugar, se debe tener claridad sobre el modelo de datos a ser

utilizado, esto es:
Las diferentes tablas que se utilizarán
Las tablas deben ser accesibles. Existe una gran variedad de
software que son aceptados (Excel, Access, AQL, etc.).
Se Debe tener certeza sobre las relaciones que existen entre las
diferentes tablas.

about:blank 5/11
29/11/21 11:13 print
En segundo lugar, debe construirse el modelo, siguiendo los
siguientes pasos:
Abrir una hoja Excel en Blanco, la cual contendrá la definición del
modelo y los resultados.
Construir el modelo, indicándole a Power Pivot cuáles son las
tablas, donde están e indicar las relaciones entre las tablas.
Si es necesario, escribir tablas adicionales que requiera el modelo
y que no se encuentren previamente ingresadas en otro medio.
Crear Resultados
Crear tabla dinámica usando como base todas las tablas
contenidas en el modelo
Si se requiere, crear tablas Medidas, es decir, resultados pre
calculados, sobre un campo de una tabla. Para esto se utilizan las
funciones disponibles, que están en inglés y los nombres de campo
que se escriben indicando primero el nombre de la tabla y
posteriormente el nombre del campo entre paréntesis cuadrados.

6 Funciones más utilizadas en Excel 2016

1.4. Pasos para confeccionar un modelo en Power Pivot

about:blank 6/11
29/11/21 11:13 print

Ejemplo de Modelo de Base de datos Ventas Por

Zonasfavor, antes de avanzar, descarga el
siguiente material. En él encontrarás
Sea la siguiente situación: contenido extra, además de la plantilla de
Excel utilizada en este módulo.
Una planilla Excel llamada Base 1, que consta
de las siguientes dos hojas:

Una planilla Excel llamada Base 2, que consta Productos Función

de las siguientes dos hojas

Nos interesa conocer:

Las ventas, en unidades, de las diferentes funciones (Comercial,
Doméstico, Entretención), por zona
Las ventas por vehículo, en unidades, por vendedor
Los pasos que debemos seguir son:
1. Accesar datos
2. Comando Power – Pivot - Iniciar
3. Escoger “Otros Orígenes”, aparecen todas las alternativas de
orígenes posibles.

En nuestro caso, de la lista que se despliega escogemos “Archivo de

Excel” e indicamos la ruta donde está el archivo en nuestro
computador y el nombre del archivo. Marcar que la primera fila
contiene nombres.
Muestra todas las hojas existentes en la planilla y seleccionamos las
que nos interesa incorporar en el modelo (Ventas y Zona).

about:blank 7/11
29/11/21 11:13 print

Repetimos el proceso para la segunda planilla (Base 2).

[Link] relaciones
En este ejemplo, debemos establecer las siguientes relaciones:
De Ventas a Productos (para extraer el nombre y el código de
función)
De Productos a Función (Para extraer el nombre de la función)
De Ventas a Zona (Para extraer el nombre de la zona). Para indicarle
al modelo cuales son las relaciones, debemos ir al ambiente Power
Pivot (Primera Pestaña, Ventana de Power Pivot), escoger la
segunda pestaña en la parte superior (Diseño) y después, la opción
Crear Relación.

Aparece una pestaña que se debe completar. La llenaremos como

ejemplo con la primera relación (de Ventas a Productos).

Crear y repetimos el mismo proceso para las otras tres relaciones.

3. Generar Tabla Dinámica
Se puede hacer por dos caminos: El primero es fuera del ambiente de
Power Pivot;
O dentro del ambiente de Power Pivot

about:blank 8/11
29/11/21 11:13 print

Se prepara la tabla dinámica en la misma forma que una tabla

dinámica normal, sólo que escogiendo campos desde las diferentes
tablas que conforman el modelo de datos.
Para el primer requerimiento, Las ventas, en unidades, de las
diferentes funciones (Comercial, Doméstico, Entretención), por zona,
la tabla será:

La tabla queda en el ambiente Excel habitual

Para el otro requerimiento, Las ventas en Unidades, por vendedor,
sería como sigue:
Antes de generar una tabla, crearemos una Medida. Posicionados
sobre la tabla, en el ambiente Excel, escogemos la pestaña Nueva
Medida. Aparece un cuadro a rellenar, en el cual definiremos como
medida la suma de las unidades, en la siguiente forma:

Ahora tenemos un campo adicional disponible para incorporar en la

tabla dinámica.
El resultado será:

about:blank 9/11
29/11/21 11:13 print

7 Actividad

Actividad

Respuesta correctas

8 Cierre

¡Muy bien!

Has terminado el séptimo módulo del

curso. Ahora es momento de regresar a
la plataforma y continuar con el
siguiente módulo.

Terminar y volver a la plataforma

about:blank 10/11
29/11/21 11:14 print

1 Objetivo del módulo

Objetivo del módulo

¡Hola!, te damos la bienvenida al octavo módulo del curso de Excel Avanzado 2016, creado por Campus Servicio Civil. Los objetivos de este módulo
son:
Conocer las opciones que ofrecen los comandos
relacionados con macros.
Conseguir eficiencia en el trabajo, ejecutando
automatizadamente conjuntos de comandos.
Saber cómo activar la opción macros en la barra de
comandos.

2 Introducción

1. Introducción

1.1. Introducción

Excel, por defecto, no muestra la pestaña Programador, que es la que

se utiliza para desarrollar macros
En la pestaña archivo, vaya a Opciones> personalizar la cinta de
opciones.
En Personalizar la cinta de opciones y Pestañas principales, active
la casilla Programador.
Después de mostrar la pestaña, la pestaña programador
permanece visible, a menos que desactive la casilla o tenga que
volver a instalar un programa.
about:blank 1/4
29/11/21 11:14 print

Las macros se utilizan cuando se requiere realizar

automatizadamente un comando o secuencia de comandos, por
ejemplo:
Aplicar un formato determinado.
Insertar una fila y la subsiguiente, decenas de veces.
Recibir un archivo con el presupuesto diario y dejarlo en un
formato adecuado, eliminando filas, cambiando el formato del
título y ordenando las partidas por número.
En general son tareas que se deben realizar rutinariamente (todos los
días, semanas o meses, debemos seleccionar la misma secuencia de
comandos).

Las macros la utilizaremos ante cualquier acción que se desee Que se escriba un texto o fórmula.
realizar automatizadamente. Algunas de ellas son: Aplicar un formato.
Imprimir un rango predefinido o seleccionado.
Ordenar.
Guardar un archivo.
Cualquier combinación de comandos que se requiera.

3 Creando una macro

1.2. Creando una macro

Revisemos a continuación un video donde crearemos un macro sin necesidad de programar:

about:blank 2/4
29/11/21 11:14 print

Por favor, antes de avanzar, descarga el siguiente material. En él encontrarás contenido extra, además de la plantilla de Excel utilizada en este
módulo.

4 Actividad

Actividad

Respuesta correctas

about:blank 3/4
29/11/21 11:14 print

5 Cierre

¡Muy bien!

Has terminado el octavo módulo del

curso. Ahora es momento de regresar a
la plataforma y continuar con el
siguiente módulo.

Terminar y volver a la plataforma

about:blank 4/4
29/11/21 11:16 print

1 Objetivo del módulo

Objetivo del módulo

¡Hola!, te damos la bienvenida al noveno módulo del curso de Excel Avanzado 2016, creado por Campus Servicio Civil. Los objetivos de este módulo
son:
Conocer las posibilidades que entrega la opción
Controles de formulario, para operar con planillas
Excel.
Aprender cuales son los controles de formulario
existentes.
Saber cómo utilizar los diferentes controles de
formulario existentes.

2 Introducción

1. Introducción

1.1. Introducción

Los usaremos cuando se desee que el registro de datos y elección de

opciones en una planilla sea amigable para el usuario.

about:blank 1/5
29/11/21 11:16 print

Los controles de formulario permiten interactuar con datos en forma

amigable para el usuario y también permiten asociar la ejecución de
una macro a ellos. Permiten, por ejemplo:
Escribir textos sobre la planilla.
Ingresar un dato escogiéndolo de una lista predefinida.
Ingresar un dato numérico utilizando barras de desplazamiento,
que permiten elegir valores en un rango determinado.
Elegir un valor.
Que se active una macro haciendo click en un cuadro.
Seleccionar un nombre de una lista.

3 Controles de Formulario existentes

1.2. Controles de Formulario existentes

Los controles de Formulario son objetos que se insertan en una

planilla Excel.
Se debe escoger las pestañas Programador – Insertar y Escoger con el
mouse el control que se desea insertar y, a continuación, indicar con
el mouse el lugar de la planilla donde queremos insertarlo y estirar
hasta indicar el tamaño deseado.
Una vez que tenemos el Control sobre la planilla, al seleccionarlo con
el Mouse se ejecuta la acción que corresponde.
Por favor, antes de avanzar, descarga el siguiente material. En él encontrarás contenido extra, además de la plantilla de Excel utilizada en este
módulo.
Si queremos seleccionar un control, para eliminar o modificar, no es Seleccionarlo con el mouse y hacer click derecho.
conveniente su realización con el mouse izquierdo, porque ello Seleccionando pestañas Inicio, Buscar y seleccionar y escoger la
provoca que el control se ejecute. Para seleccionarlo existen dos opción Seleccionar objetos. Después de ello, al hacer click izquierdo
opciones: sobre el control, éste se selecciona, en lugar de ejecutarse.

about:blank 2/5
29/11/21 11:16 print

Botón
Los recorreremos de izquierda a derecha y de arriba hacia abajo, para
describirlos.
Sirve para ejecutar una macro haciendo click en el botón.
Como parámetro exige que se le asigne una macro que esté definida.

Cuadro Combinado
Sirve para escoger de una lista de valores y como resultado entrega
un número (1, 2, 3 …) que indica la posición de la celda escogida de la
lista.
Como parámetro exige que se indique el rango donde está la lista y la
celda donde aparecerá el resultado. En el diagrama anterior, el primer
parámetro será B2:B5 y el segundo será E2.
Es habitual utilizar este control con la función Buscarv(). Por ejemplo,
en el caso indicado, podemos agregar una lista 1, 2, 3 , 4 de A2 a A5 y
agregar una función Buscarv en F2, para que en esa celda se refleje el
texto escogido.

Casilla de verificación
Muestra una casilla y se escoge si la casilla está activa o no,
dependiendo de ello, se asigna el valor verdadero o falso en una
celda.
Requiere un parámetro, la celda que contendrá el resultado. En el
diagrama es la celda B2.
Para cambiar el nombre, se escoge el objeto y se elige Modificar
texto.
Normalmente este comando se utiliza con una función SI, donde se
obtiene un resultado u otro dependiendo del valor que toma la celda
asociada al Control:
=SI(B2;Resultado 1;Resultado 2)

about:blank 3/5
29/11/21 11:16 print
Control de Lista
Es exactamente igual que el Cuadro Combinado (Se selecciona de una
lista y devuelve un número que es la posición de lo escogido en la
lista), pero se utiliza cuando la lista es larga. En el ejemplo que sigue
son 284 opciones.

Botón de Opción
Es un conjunto de botones que se activan en forma excluyente (Si uno
está activado, los otros están desactivados).
Dependiendo del botón que se escoja, aparece un número (1, 2, 3…..)
Se debe crear más de un botón y asignarles a ellos la misma celda
con que se vincula

Cuadro de grupo
Se utiliza en combinación con otros controles, para circunscribirlos.
Por ejemplo, si queremos crear dos grupos de botones de opción, es
necesario crearlos dentro de cuadros de grupo.
Ejemplo:

Etiqueta
Es sólo un texto que se inserta a modo informativo. Sirve para agregar
títulos, instrucciones, comentarios o el texto estático que requiere
escribir el usuario.
Barra de Desplazamiento:
Tiene la misma funcionalidad que el Control de número (Permite
escoger números en un rango determinado y asigna ese valor a una
celda). La diferencia es que este control modifica el rango en que se
mueven los números moviendo una barra, en vez de una flecha.
Se debe indicar el número mínimo, el máximo, la variación al pinchar
la flecha (Incremento) y la variación al pinchar entre la flecha y la
barra (Cambio de página). Cuadro de texto, Cuadro combinado de lista, Cuadro combinado
desplegable.
about:blank 4/5
29/11/21 11:16 print
Estas tres opciones no están disponibles. Pertenecían a opciones
anteriores de Excel.

4 Actividad

Actividad

5 Cierre

¡Muy bien!

¡Muy bien! Has terminado el noveno

módulo del curso. Ahora es momento
de regresar a la plataforma y continuar
con el siguiente módulo.

Terminar y volver a la plataforma

about:blank 5/5
29/11/21 11:16 print

1 Objetivo del módulo

Objetivo del módulo

¡Hola!, te damos la bienvenida al decimo módulo del curso de Excel Avanzado 2016, creado por Campus Servicio Civil. Los objetivos de este módulo
son:

Conocer el ambiente de programación de Excel.

Conocer la forma en que se representan las macros
en ambiente de programación.
Aprender a crear macros escribiendo directamente
el código.
Aprender a programar acciones sobre hojas, ante
ocurrencias de eventos.

2 Introducción

1.1. Introducción

Cuando se quiere realizar automatizadamente un comando o

secuencian de comandos, con elementos complejos, tales como
condiciones. Por ejemplo:
Cuando deseamos trabajar con un formato amigable, donde
requerimos una pantalla de inicio con un menú, con botones desde
donde escogemos distintas alternativas de procesamiento y los
cálculos que se realizan depende de la elección que hacemos en
botones de datos. Por ejemplo, el monto de un beneficio depende
de si elegimos Hombre o Mujer en una pestaña y del número de
cargas, que escogemos en una lista desplegable.
Si queremos mostrar una pantalla, con una ficha. con fotografía y
datos personales, al escoger el nombre de un funcionario, desde
una lista desplegable.

about:blank 1/10
29/11/21 11:16 print

Para realizar cualquier acción que se desee realizar Que se escriba un texto o fórmula.
automatizadamente, en la cual no es suficiente el comando que graba Aplicar un formato.
macros, sino que se debe intervenir las líneas de comando. Algunas de Imprimir un rango predefinido o seleccionado.
ellas son:
Ordenar.
Guardar un archivo.
Cualquier combinación de comandos que se requiera.

3 Entrando al ambiente de programación

1.2. Entrando al ambiente de programación

Aparece lo que se denomina “Vista de Proyecto”.

En este esquema aparecen todos los objetos definidos dentro de la
planilla Excel. Pueden ser de cuatro tipos:
Hojas de cálculo
Formularios
Módulos
Clases

about:blank 2/10
29/11/21 11:16 print

Para esta parte del curso, nos enfocaremos sólo en el tercer tipo de
objeto: Módulo. Éste contiene las instrucciones que conforman cada
macro definida.
En los Módulos se almacena el código de las macros.
Si no aparece la Vista de Proyecto, utilizar una de las opciones de la
barra superior, como se indica a continuación:

4 Ejercicio de visualización de código

1.3. Ejercicio de visualización de código

Referencia absoluta
Ejecución abreviada con Control – A
Macro se llama “Prueba”
Va a la celda A1
Escribe “Hola que tal”, en esa celda
Pone esa celda en negrita
Se pone el cursor en celda A3
Escribe “Chao”

about:blank 3/10
29/11/21 11:16 print
Cuando pasemos al ambiente de programación y hagamos click sobre Sub Prueba()
Módulo 1 o el módulo nuevo que se haya creado, veremos el código '
necesario de escribir para crear la macro indicada: ' Prueba Macro
'
' Acceso directo: CTRL+a
'
Range("A1").Select
ActiveCell.FormulaR1C1 = "Hola que tal"
Range("A1").Select
[Link] = True
Range("A3").Select
ActiveCell.FormulaR1C1 = "Chao"
Range("A4").Select
End Sub

Se puede apreciar la complejidad del código necesario para crear una

macro muy sencilla, por lo que la recomendación es crearlas con
Grabar macro cuando sea posible.

Revisemos el siguiente video

5 Conceptos – Variables, Operadores, Arreglos y Funciones

1.4. Conceptos – Variables, Operadores, Arreglos y Funciones

La variable se puede declarar en el momento en que se va a utilizar, Edad = 27

por ejemplo: Mes3 = “Marzo”

También puede definirse previo a utilizarla: Dim Nombre_Variable as Tipo_Variable

Tipo variable puede ser:
about:blank 4/10
29/11/21 11:16 print
Dim Empleado Integer (Entero)
En este caso se debe indicar de que tipo es la variable y el tamaño Long (Valores numéricos grandes)
máximo de los datos que puede contener. La sintaxis es: Single (Decimales)
String (Texto)
Boolean (Verdadero o falso)
Variant (cualquier cosa).
Si una variable no se define al inicio, se asume variante, lo que es
ineficiente para uso de memoria.

Aritméticos: Son utilizados para hacer operaciones matemáticas,

como sumar, restar, multiplicar o dividir.
De comparación: Se utilizan para comparar expresiones devolviendo
VERDADERO o FALSO.
Lógicos: Con estos operadores es posible evaluar varias expresiones a
la vez, devolviendo VERDADERO o FALSO.

Un arreglo, es una estructura que almacena varios datos del mismo

tipo ordenados de forma lineal, bajo el mismo nombre.
Almacena los datos en posiciones de memoria contigua.
Tiene un solo nombre de variable que representa a varios
elementos. Los elementos se diferencian mediante un número de
índice.
Es posible acceder a cualquier elemento del arreglo a través de su
índice.
Según su dimensión o su tamaño un arreglo puede ser de una o
más dimensiones (lo que exige tener uno o más índices, según
corresponda).

Encontramos:
La función InputBox
Cuadro inputbox
La función MsgBox
Las funciones de conversión de tipo
Las funciones de comprobación
Las funciones matemáticas
Las funciones de texto
Las funciones de fecha y hora

6 Conceptos – Estructuras de programación

about:blank 5/10
29/11/21 11:16 print

1.5. Conceptos – Estructuras de programación

En esta parte se entregan algunos conceptos sobre estructuras de

programación, que son convenientes de manejar al programar macros. Estructuras condicionales.
Estructura With – End With.

Estructuras de bucle.

7 Conceptos - Objetos, Jerarquías y Colecciones

1.6. Conceptos - Objetos, Jerarquías y Colecciones

Excel es un conjunto de objetos de diferente tipo (hojas, rangos, Application: Hace referencia a la aplicación (Excel).
celdas, gráficos, etc). Estos objetos tienen jerarquías. La primera se Workbook: Hace referencia al libro de trabajo.
llama Application, la siguiente planilla y después una hoja, que a su Worksheet: Hace referencia a una hoja de cálculo.
vez contiene un conjunto de celdas, representadas por un objeto
Range: Range: Hace referencia a un rango de celdas.

Application es uno de los objetos más importantes de VBA, por eso [Link](“Nomina
cuenta con muchas propiedades. Algunas de ellas definen el ambiente [Link]”).Worksheets(“Resumen”).Range(“D80”).Select
donde se ejecuta Excel, tras controlan la presentación de la interfaz y
otras devuelven objetos.
Para hacer referencia a un objeto, se debe utilizar toda la jerarquía.
Por ejemplo, para hacer referencia a la celda D80 de la hoja Resumen,
en el libro “Nomina [Link]” habría que hacerlo de la siguiente
forma:

La instrucción se puede simplificar omitiendo el objeto Application, Worksheets(“Resumen”).Range(“C8”).Select

porque la instrucción está creada dentro de Excel (Application). En
general, si el objeto de mayor rango es el objeto activo, se puede
omitir. Si tienes activo el libro “Nomina [Link]”, se puede
seleccionar la celda C8 de la siguiente forma:

Todos los objetos del mismo tipo forman una colección. Las Coleccion!Objeto
colecciones permiten trabajar con un grupo de objetos como si se Coleccion![Objeto]
tratara de un solo objeto. Normalmente, el nombre de una colección Coleccion(“Objeto”)
es el objeto en plural de los objetos que contiene. Por ejemplo,
about:blank 6/10
29/11/21 11:16 print
Worksheets es el nombre de la colección que reúne todos los objetos Coleccion(var)
Worksheet. Coleccion(index)
Para hacer referencia a un objeto, dentro de una colección, se pueden
utilizar los siguientes métodos:

Var es una variable del tipo Texto que contiene el nombre del objeto.
Index es el número de la posición que ocupa el objeto dentro de la
colección.

8 Conceptos – Propiedades y Métodos

1.7. Conceptos – Propiedades y Métodos

La programación orientada a objetos, que usa Excel, se basa en la

modificación de las propiedades y métodos de los objetos.
Las propiedades son las características propias del objeto, que permite
distinguirlos de otros. Por ejemplo, nombre, tamaño, color, ubicación en
la pantalla, etc.
Mientras que los Métodos son acciones que puede hacer con un objeto.
Son órdenes que se le dan a los objetos, para que haga algo sobre sí
mismo. Por ejemplo, el objeto Range tiene los métodos Activate (activar)
y Clear (borrar), entre muchos otros.

9 Ejercicio crear una Macro con programación

about:blank 7/10
29/11/21 11:16 print

1.6. Ejercicio crear una Macro con programación

Sea la siguiente planilla, donde tenemos una lista de ministerios, con

sus gastos por servicios de publicidad de los años 2019 y 2020, son su
variación. Queremos una macro que destaque, con color amarillo,
aquellos ministerios que superan un cierto porcentaje de incremento
en los gastos. El porcentaje de referencia lo escogemos moviendo un
formulario de control, que varía el valor. Un Botón ejecuta la macro
que marca con amarillo los casos que superan al parámetro de
control y otro botón ejecuta una macro que limpia las marcas con
amarillo.

Por favor, antes de avanzar, descarga el siguiente material. En él encontrarás contenido extra, además de la plantilla de Excel utilizada en este
módulo.
Intuitivamente, podemos pensar en que la macro debe hacer lo
siguiente:
Posicionar el cursor en la planilla que nos interesa (Valor Acciones)
Posicionarse en la primera celda
Preguntar si el valor de la celda donde está el cursor es mayor a
15%
Si se cumple, desplazarse tres veces a la izquierda
Marcar con amarillo la celda donde está
Devolverse tres veces a la derecha
Bajar una celda
Volver a realizar lo anterior hasta que encuentre una celda vacía

Debemos hacer lo siguiente:

Ir al ambiente Programador (Programador – Visual Basic)
Insertar – Módulo
Escribir Sub Nombre de Macro()
(Escogemos Ilumina_Acciones)
Automáticamente aparece End Sub
Entre Sub y End Sub se debe escribir el código.

En lenguaje de código sería como sigue: Sub Ilumina_Acciones()

Range("D2").Select
Do While ActiveCell <> Empty
If ActiveCell > 0.15 Then
[Link](0, -3).Range("A1").Select
With [Link]
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
[Link](1, 3).Range("A1").Select
Else
about:blank 8/10
29/11/21 11:16 print
[Link](1, 0).Range("A1").Select
End If
Loop
End Sub

Recomendaciones With [Link]

Para realizar algunas acciones, se recomienda crear macros con el .Pattern = xlSolid
mecanismo de grabar macros y copiar el código que se genera. .PatternColorIndex = xlAutomatic
.Color = 65535
Por ejemplo, para pintar la celda activa de amarillo, es necesario .TintAndShade = 0
escribir el siguiente código: .PatternTintAndShade = 0
End With

En vez de escribir desde cero, es mejor crear una macro con Grabar [Link](1, 3).Range("A1").Select
macro y copiar el código que se genera. Eso se puede generar creando una macro que haga eso con Frabar
Igualmente, mover una celda tres veces a la derecha y una vez hacia macro y copiando el código que resulta.
abajo, se representa con:

10 Actividad

Actividad

Respuesta correctas

11 Cierre
about:blank 9/10
29/11/21 11:16 print

¡Muy bien!

Has terminado el décimo módulo del

curso. Ahora es momento de regresar a
la plataforma y continuar con el
siguiente módulo.

Terminar y volver a la plataforma

about:blank 10/10
29/11/21 11:18 print

1 Objetivo del módulo

Objetivos del módulo

¡Hola!, te damos la bienvenida al Undécimo módulo del curso de Excel Avanzado 2016, creado por Campus Servicio Civil. Los objetivos de este
módulo son:

Saber que son las funciones personalizadas.

Aprender cómo crear y utilizar funciones
personalizadas.

2 1.1. Introducción

1.1 Introducción

Se requiere tener activada la opción de

Programador
Para ello, se requiere entrar al ambiente
de Programación.
Una hoja con funciones propias definidas
deben ser grabadas en el formato Excel
habilitado para Macros.
Cuando creamos una función, esta
aparece disponible en el menú Fórmulas,
Insertar función, bajo la agrupación
“Funciones definidas por el usuario.

about:blank 1/5
29/11/21 11:18 print

Cuando se debe aplicar una determinada

fórmula muchas veces, en diferentes
momentos y/o en diferentes planillas

Para realizar cálculos predefinidos con

rapidez y seguridad, sin necesidad de
volver a escribir o copiar la fórmula de
cálculo.
about:blank 2/5
29/11/21 11:18 print
Puede automatizarse cualquier fórmula,
del ámbito financiero, de fecha,
matemáticas, ingeniería, etc.

3 1.2. Creación de una función

1.2. Creación de una función

Escoger la pestaña programación y

luego Visual Basic
Si no aparece la vista de Proyecto, se
debe activar.
Si no existen módulos en blanco,
insertar uno con la opción que se
indica en el diagrama anterior.

Crear la estructura de una función, en la

siguiente forma:
Entrar a un módulo en blanco.
Escribir Function más el nombre que
se le quiere dar a la función más
paréntesis redondo de abrir y cerrar.
Automáticamente aparecerá una línea
que dice End Function. Entre esas dos
about:blank 3/5
29/11/21 11:18 print
líneas se deben escribir las
instrucciones que ejecutará la función,
utilizando un lenguaje de macros.
Adicionalmente, deben escribirse
entre los paréntesis las variables que
se requiere especificar para la
función).

Por ejemplo, si escribimos =prodos(5;9) queremos que nos entregue Function Prodos(var1, Var2)
como resultado el promedio de 5 y 9.
Insertamos un módulo Prodos = (var1 + Var2) / 2
En el módulo escribimos el siguiente código: End Function

La función está lista, cada vez que en la

hoja se escriba =prodos(número
1;número 2), aparecerá el resultado de
esas dos funciones.

4 1.3. Tipos de variables en funciones

1.3. Tipos de variables en funciones

Las variables de entrada y el resultado de

la función pueden restringirse, agregando
el tipo de variable. Si hacemos lo que ves
en pantalla, la función aceptará sólo
números enteros como datos de entrada.
about:blank 4/5
29/11/21 11:18 print

Por favor, antes de avanzar, descarga el siguiente material. En él encontrarás contenido extra, además de la plantilla de Excel utilizada en este
módulo.

5 Actividad

Actividad

6 Cierre

¡Muy bien!

Has terminado el undécimo módulo del

curso. Ahora es momento de regresar a
la plataforma y continuar con el
siguiente módulo.

Terminar y volver a la plataforma

about:blank 5/5
29/11/21 11:24 print

1 Objetivos

Objetivos del módulo

¡Hola!, te damos la bienvenida al duodécimo módulo del curso de Excel Avanzado 2016, creado por Campus Servicio Civil. Los objetivos de este
módulo son:

Saber que son los UserForm.

Aprender cómo construir y utilizar un UserForm.
Conocer que son los controles Active X y cuales
existen.
Aprender a trabajar con los controles Active X.

2 Crear una tabla dinámica

1.2. Para qué se usa y cuándo se usa

Se usa para trabajar en Excel en forma

automatizada y amigable, mediante una
estructura de formularios amigables que
realizan procesos preestablecidos.
Estos permiten desarrollar sistemas en
Excel, los cuales son entregados a los
usuarios, quienes pueden operarlo con
seguridad, si necesidad de que sepan
manejar Excel, y minimizando la
posibilidad de errores, al tener
restringidos los campos que se pueden
modificar y las fórmulas establecidas.

about:blank 1/7
29/11/21 11:24 print

Se usa cuando se desea disponer de un Registrar y mantener datos en una o más planillas de datos, utilizando formularios.
sistema automatizado que permita
realizar cualquier operación Realizar acciones determinadas, dependiendo de si se escogen casillas, botones de
automatizada que se requiera, por opción o cuadros de lista.
ejemplo:

3 Estructura de la tabla dinámica

1.3. Formularios y Controles Active X

Para insertar un Control active X en una

planilla, se debe escoger la opción
Programador, Insertar y escoger el
control Active X que desea insertar.
Luego se marca con el mouse el lugar de
la planilla donde se quiere instalar.

Para insertar un Control active X, en

ambiente Visual Basic, se debe arrastrar
el control deseado desde el cuadro de
herramientas hacia el cuadro que
representa el UserForm.
Para trabajar sobre un control Active X
(Eliminarlo o modificarlo) en el ambiente
Excel, se debe activar la pestaña Diseño.
about:blank 2/7
29/11/21 11:24 print

Cuando se escoge un control Active X, al

hacer click derecho, es posible acceder a
las dos siguientes alternativas
relevantes:
Propiedades: Conduce a una tabla con
una gran cantidad de atributos que es
posible modificar. El número y tipo de
atributos depende del Control X que se
trate. Por ejemplo, la lista siguiente es
para un Control X de tipo Command
Button

Ver Código: Lleva al ambiente Visual

Basic donde se muestra el código
asociado al Control Active X, en el
lenguaje que utilizamos para escribir
macros. Para un Control X de tipo
Command Button, aparece:

Luego se debe insertar el código que

refleje lo que queremos que ocurra en
caso que se haga Click sobre el Control X.

4 Formato de una tabla dinámica

about:blank 3/7
29/11/21 11:24 print

1.4. Eventos y Controles Active X

Hacer Click sobre el Control

Hacer Click Derecho y escoger la
opción Ver código de la lista que
aparece
Aparecerá lo siguiente

El código de comando que escribamos

entre las dos líneas, será lo que hará el
comando al hacer Click sobre él.
Supongamos que queremos definir que
se haga algo al ocurrir otro evento, para
ello elegimos el evento seleccionando la
flecha enmarcada en rojo en el diagrama
anterior. Con eso, nos aparecen los
diferentes eventos posibles:

Escojamos, por ejemplo, Doble Click y

aparecerá el siguiente código:
Private Sub

CommandButton1_DblClick(ByVal
Cancel As [Link])
End Sub

Las instrucciones que escribamos entre

las dos líneas en azul será lo que
ejecutará el comando si hacemos doble
click sobre él.

5 Cambiar el formato numérico

about:blank 4/7
29/11/21 11:24 print

1.5. Creación de un formulario

Escoger la pestaña programación y

luego Visual Basic
Si no aparece la vista de Proyecto,
activarla con la opción que se indica
en el diagrama siguiente:

Elegir la opción Insertar, que se indica

en el diagrama anterior.
Escoger la opción UserForm, de la lista
que se despliega.
Aparece un UserForm en la estructura
de proyecto, un formulario en blanco
que lo caracteriza y un cuadro de
herramientas que contiene con los
controles que se pueden utilizar
dentro del UserForm.
Si no aparece el cuadro de
herramientas, aparece activando la
última opción del menú inferior.

A continuación, se deben agregar al

UserForm los controles Active X que se
requieran y programarlos. Para insertar
un control Active X, se toma del cuadro
de herramientas y se arrastra hacia el
UserForm.
Para utilizar un UserForm, ya creado, es
necesario invocarlo con una instrucción
Load, que debe ser escrita desde una
macro o desde un Control Active X.

Revisemos un ejemplo, sea el siguiente

requerimiento:

about:blank 5/7
29/11/21 11:24 print

Por favor, antes de avanzar, descarga el siguiente material. En él encontrarás contenido extra, además de la plantilla de Excel utilizada en este
módulo.

6 Actividad

Actividad

7 Cierre

about:blank 6/7
29/11/21 11:24 print

¡Muy bien!

Has terminado todos los módulos del

curso. Ahora es momento de regresar a
la plataforma y responder la Evaluación
Final de este curso.

Terminar y volver a la plataforma

about:blank 7/7