Excel Intermedio

Excel 15
Taller
Excel
Intermedio - Avanzado
Ing. Alexander Aragón Ch. M.Sc.
Facultad de Ingeniería
Departamento de Operaciones y Sistemas
/-\\ 2017-01. Material didáctico de Apoyo – Ing. Alexander Aragón Ch. – Universidad Autónoma de Occidente Excel Intermedio/Avanzado
1
1. Presentación del curso Excel 15
- Presentación del docente

- Presentación de los estudiantes: Nombre y Programa académico
- Contenido del curso:
• Sesión 1: Pre-requisitos,
• Referencias Relativas, Absolutas y Mixtas
• Funciones lógicas y su anidación SI(), Y(), O(), SI.ERROR()
• Búsquedas de información: BUSCARV(), BUSCAR(H)
• Sumas y conteos condicionales.
• Subtotales
• Sesión 2: Tablas y gráficos dinámicos
• Sesión 3: Escenarios, Objetivos, herramientas de SOLVER.
• Sesión 4 y 5: Macros y VBA
EJERCICIOS PRACTICOS EN CADA TEMA TRATADO
Archivos complementarios:
www.alexanderaragon.co (Cursos: Excel Intermedio 15H – Clave: Excel_2017)
• Datos_Cartera_Clientes.xlsx
• Datos_Población.xlslx
• Datos_Comercio_Internacional.xlsx
• Datos_Estatura_Adultos.xlsx
• Datos_Ventas_Automoviles.xlsx
• Datos_Ambientales.xlsx
2
1. Pre-requisitos Excel 15
- Conocimientos básicos necesarios:
• Conceptos de Hoja de Cálculo, Libro, Celda, Rango, Direcciones de Celda

• Formatos de celda: Fuentes, Cuadros, Números, Fondos
• Ordenación de datos, autollenado
• Conceptos y operaciones básicas para construcción de Fórmulas y Funciones
• Diseño e impresión de hojas de cálculo
3
1. Referencias Relativas, Absolutas y Mixtas Excel 15
El símbolo de moneda “$” controla la manera como se copian las direcciones de celda:
A1 + A2 Referencia relativa
al copiar la celda, las referencias cambiarán para actualizarse
$A$1 + $A$2 Referencia absoluta

al copiar la celda, las referencias permanecerá igual
$A1 + A$2 Referencia mixta

al copiar la celda, las referencias cambiarán dependiendo de si se
fija la fila o la columna de la celda referenciada
Tecla Especial
para definir tipo de
referencia al editar la celda
y colocar el cursor en la
dirección respectiva
F4
4
1. La anidación de funciones lógicas SI() Excel 15
Tres (3) parámetros!
= SI ( prueba lógica ; valor verdadero ; valor falso )
Se pueden anidar hasta 64 funciones SI() pero es más aconsejable empelarla en

combinación con otras como Y(), O(), BUSCARV().
= SI ( prueba lógica ; SI ( prueba lógica ; valor verdadero ; valor falso ) ; SI ( prueba lógica ; valor verdadero ; valor falso ) )
Consejo:
Se recomienda iniciar fórmulas complejas abriendo y cerrando sus paréntesis y luego,

incluir su contenido:
= SI ( prueba lógica ; SI () ; SI () )
= SI ( prueba lógica ; SI ( prueba lógica ; valor verdadero ; valor falso ); SI ())
= SI ( prueba lógica ; SI ( prueba lógica ; valor verdadero ; valor falso ); SI ( prueba lógica ; valor verdadero ; valor falso ))
5
1. La anidación de funciones lógicas SI() Excel 15
Ejercicio:
=SI(A2=1;"uno";SI(A2=2;"dos";SI(A2=3;"tres";SI(A2=4;"
cuatro";SI(A2=5;"cinco";SI(A2=6;"seis";SI(A2=7;"siete";
=ENTERO(ALEATORIO()*10) SI(A2=8;"ocho";SI(A2=9;"nueve";"cero")))))))))
Tecla Especial
Para recalcular toda la hoja
y generar nuevos números
aleatorios en la función
ALEATORIO()
F9
La función ALEAORIO() genera números uniformemente distribuidos entre 0 <= n < 1
6
1. Funciones lógicas Y() – O() Excel 15
= Y ( valor lógico1 ; valor lógico2; … )
Devuelve VERDADERO si se cumplen TODAS las condiciones evaluadas
= O ( valor lógico1 ; valor lógico2; … )
Devuelve VERDADERO si se cumple por lo menos UNA de las condiciones evaluadas
Se pueden incluir hasta 255 valores condicionales en ambas funciones
7
1. Funciones lógicas Y() – O() Excel 15
Ejercicio: =SI(Y(A2<5;B2<5;C2<5;D2<5);"Todos menores de

5";SI(Y(A2>=5;B2>=5;C2>=5;D2>=5);"Todos mayores o
=ENTERO(ALEATORIO()*10) iguales a 5";"valores en ambos rangos"))
=SI(O(A2<5;B2<5;C2<5;D2<5);"Al menos uno menor de

5";SI(O(A2>=5;B2>=5;C2>=5;D2>=5);"Al menos uno
mayor o igual a 5";"valores en ambos rangos"))
8
1. Control de errores Excel 15
Permiten controlar fórmulas que generan algún tipo de error entre:
#N/A, #¡VALOR!, #¡REF!, #¡DIV/0!, #¡NUM!, #¿NOMBRE? o #¡NULO!.
SI.ERROR ( valor a evaluar ; valor a mostrar )
Si la expresión o fórmula genera un error, se mostrará el valor indicado en el segundo

parámetro, de lo contrario, mostrará el resultado de la fórmula o expresión.
9
1. Búsquedas de Información Excel 15
Las funciones más utilizadas para búsqueda de información son:
BUSCARV ( valor buscado ; matriz de búsqueda ; columna a recuperar ; búsqueda aproximada )

BUSCARH ( valor buscado ; matriz de búsqueda ; fila a recuperar ; búsqueda aproximada )
Búsqueda aproximada es:

FALSO si se quiere una búsqueda exacta.
VERDADERO si se quiere una búsqueda aproximada.
Columna 1 2 3
10
1. Búsquedas de Información Excel 15
Ejercicio: en la hoja de cálculo con datos de población por municipios, construir un área de resumen de
información según el nombre de la ciudad que se digite:
Escribir ciudad aquí Recuperar información aquí
11
1. Sumas y conteos condicionales Excel 15
= SUMAR.SI ( rango a evaluar ; criterio ; rango a sumar )
Si el rango a evaluar es el mismo a sumar, se puede omitir el tercer parámetro.
12
= SUMAR.SI ( rango a evaluar ; criterio ; rango a sumar )

= SUMAR.SI.CONJUNTO ( rango a sumar ; rango a evaluar 1 ; criterio 1 ; rango a evaluar 2 ; criterio 2 … )
En la primer función, si el rango a evaluar es el mismo a sumar, se puede omitir el tercer parámetro.
En la segunda función, se admiten hasta 127 pares de criterios.
13
= CONTAR.SI ( rango a evaluar ; criterio )

= CONTAR.SI.CONJUNTO ( rango a evaluar 1 ; criterio 1 ; rango a evaluar 2 ; criterio 2 … )
En la segunda función, se admiten hasta 127 pares de criterios.
= CONTARA ( valor1, valor2, … )

= CONTAR.BLANCO ( rango a evaluar )
Estas funciones cuentan las celdas con datos y sin datos respectivamente.
14
Ejercicio: al indicar un número de habitantes, contar cuantos municipios tienen mas y cuantos menos del
valor indicado.
15
1. Ejercicio práctico Excel 15
Construir una planilla de notas con el siguiente formato:

Codigo NOMBRE ESTUDIANTE NOTA 1 NOTA 2 NOTA 3 NOTA 4 NOTA 5 % TOTAL DEFINITIVA Ranking
Porcentajes: 10 15 20 35 20 100
1 Estudiante 1 5.00 5.00 5.00 5.00 5.00 5.00 Alto
2 Estudiante 2 5.00 4.00 3.00 2.00 1.00 2.60 Medio-bajo
3 Estudiante 3 4.00 4.00 4.00 4.00 4.00 4.00 Alto
4 Estudiante 4 3.00 3.00 3.00 3.00 3.00 3.00 Medio
5 Estudiante 5 3.70 3.70 3.70 3.70 3.70 3.70 Medio-alto
6 Estudiante 6 3.50 3.50 3.50 3.50 3.50 3.50 Medio
7 Estudiante 7 2.10 2.10 2.10 2.10 2.10 2.10 Medio-bajo
8 Estudiante 8 2.00 1.00 2.00 1.00 2.00 1.50 Bajo
Todas las
notas en este
RESUMEN: RANKING: ranking
Promedio de notas 3.175 Bajo (0 a 2) 1 1
Estudiantes aprobados 5 Medio - bajo (2.1 a 2.9) 2 1
Estudinates reprobados 3 Medio (3 a 3.5) 2 2
Nota más alta 5 Medio - alto (3.6 a 3.9) 1 1
Nota más baja 1.5 Alto (4 a 5) 2 2
CONSULTA:
Código: 8
Nombre: Estudiante 8
Nota 1 2.00
Nota 2 1.00
Nota 3 2.00
Nota 4 1.00
Nota 5 2.00
Definitiva 1.50
Ranking Bajo
16
2. Tablas Dinámicas Excel 15
Facilita la organización de datos en listas configurables de una manera más flexible.
17
2. Tablas Dinámicas Excel 15
Basta ubicar los campos de la tabla en las ubicaciones deseadas para configurarla según las
necesidades de cada caso.
Se arrastran los
campos a las
posiciones
deseadas
Son el tipo de datos los que determinan

cómo es la configuración de una tabla
apropiada.
18
2. Gráficos Dinámicos Excel 15
Como en las tablas, puede configurarse un gráfico dinámico que muestre los datos.
19
2. Ejercicio Excel 15
Con los diferentes juegos de datos, construir las tablas y gráficos dinámicos más apropiados.
20
3. Escenarios Excel 15
Excel puede guardar valores cambiantes (Escenarios) para generar un resumen de resultados
para cada uno de los mismos.
21
3. Escenarios Excel 15
Deben indicarse, las celdas cambiantes y las celdas resultado para que Excel, evalúe los
diferentes escenarios..
22
3. Objetivos Excel 15
Permite encontrar un valor de entrada para obtener un resultado conocido.
23
3. Objetivos Excel 15
Ejercicio: Un estudiante posee las siguientes 3 notas y desea determinar, cuanto debe sacar en
la última para aprobarla
24
3. SOLVER Excel 15
Complemento de Excel utilizado para encontrar valores óptimos considerando restricciones o

limitaciones.
Este complemento debe ser activado de la misma manera como se ha hecho las “Herramientas
para análisis”
Si se requieren comprar 500 unidades de un producto a 5 proveedores distintos según la tabla, ¿Cuántas pediría a cada
uno de ellos para minimizar el costo?
25
3. SOLVER Excel 15
Planteamiento
26
3. SOLVER Excel 15
Ejercicio: para un paseo de camping, tu dispondrás de una mochila para llevar los alimentos y
que puede cargar máximo 10 Kg de peso o un volumen de 0.125 m3.
Puedes elegir entre 4 tipos distintos de alimentos para llevarlos en la mochila teniendo en
cuenta que necesitarás muchas calorías y por lo menos, 200 g de proteína.
La lista disponible de alimentos es la siguiente:
Cantidades por cada Unidad

Volumen
Alimento calorías Proteína (g) Peso (Kg)
(m3)
Barra de Dulce 90 5 0.25 0.0005
Sandwich 130 40 0.35 0.002
Lata de Jugo 100 15 0.35 0.00075
Manzana 40 3 0.3 0.0009
¿Qué cantidad de cada alimento sería la apropiada?
27
4. Macros Excel 15
Las macros permiten automatizar tareas que se repiten frecuentemente en un libro, de tal
manera que evitan tener que repetir todo el proceso de manera manual.
Así por ejemplo, si periódicamente tenemos que pegar en un libro, datos de otra fuente para
posteriormente, ordenarlo, darle formato, eliminar columnas, etc., se puede automatizar todo
este proceso para ganar tiempo.
Una macro “graba paso a paso” lo que el usuario hace en la hoja de cálculo para que cuando
sea necesario repetirlo, simplemente se ejecute de nuevo.
IMPORTANTE:
Para grabar un libro que contenga “Macros”, deberá
cambiarse el tipo de archivo por Libro de Excel habilitado
para macros, el cual tiene extensión XLSM.
28
4. Macros Excel 15
Para crear una macro, se accede a la opción grabar macro desde el menú Vista:
Realizar las
tareas a grabar
en la Macro
Detener la grabación con el botón STOP mostrado en la

Barra de Estado de Excel
29
4. Macros Excel 15
Conviene al diseñar las hojas de cálculo, asignar la macros a botones con sus respectivas
etiquetas:
30
4. Macros Excel 15
Ejercicio: Al archivo, crear cuatro macros con sus respectivos botones de acción, para ordenar
los datos por cada columna.
Los datos ordenados, deberán tener color de fuente Azul.
Macro1 Macro3
Macro2 Macro4
De esta manera, cada que se quieran ordenar los datos para una columna en particular, baste con pulsar el
botón respectivo.
31
4. Macros – El Editor de VBA Excel 15
Las macros de Excel son construidas en Visual Basic para Aplicaciones (VBA) y pueden ser
personalizadas en el editor respectivo
Puede aprovecharse el editor para por ejemplo, unir varias macros que se graben individualmente, en una
sola.
32
4. Macros – El Editor de VBA Excel 15
Ejercicio: Para el archivo, construir estas tres macros individuales:
Macro1: Ordenar todos los datos por País y año.

Macro2: Aplicar Formato Condicional al la columna Año para que aquellos que sean
del 2011, tomen un color de fondo especial.
Macro3: Ordenar todos los datos por Linea para dejarlos como estaban y eliminar
el formato condicional.
Construir una cuarta macro que una a las dos primeras; asignarla a un botón “Ordenar”.
Agregar un segundo botón “Reordenar” para asignar la Macro3.
33
4. Macros – Instrucciones básicas de VBA Excel 15
Para aumentar el potencial de las macros, pueden aprovecharse algunas instrucciones básicas
de Visual Basic para Aplicaciones (VBA) y así personalizar más las macros automáticas.
Para indicar el valor que hay en una celda:

Range(“A1”).Value
Si la celda está en otra hoja del libro:
Worksheets(“nombre hoja”).Range(“A1”).Value
Para seleccionar una celda o un rango:

Range(“A1”).Select ó Worksheets(“nombre hoja”).Range(“A1”).Select
Range(“A1:A10”).Select
Para borrar el contenido de una celda o rango:

Range(“A1”).ClearContents ó Worksheets(“nombre hoja”).Range(“A1”).ClearContents
Range(“A1:A10”).ClearContents
34
Ejercicio: Construir una macro para el archivo que aplique Formato Condicional a aquellas
celdas para un año que indique el usuario en una celda especial:
Para este cometido, deberá definirse en la hoja de cálculo, la celda en donde el usuario escriba
el año deseado.
Luego “Grabar” una macro nueva en donde se asigne el formato condicional a todas las celdas
que por ejemplo el año sea 2011.
Se modifica finalmente la macro para buscar en el código VBA creado, el lugar donde se indica
la condición para ajustarla según nuestras necesidades
“=$E2=“ & Range(“A1”).Value
Y si en el futuro se extiende más el total de filas de la lista de datos, ¿Dónde y cómo podría personalizar el
respectivo rango para que los cobije esta macro?
35
En VBA también se pueden construir decisiones como en la función SI() de Excel:
If condición Then (para evaluar varias condiciones a la vez, pueden usarse And u Or)
instrucción 1 aquí si se cumple el SI
instrucción 2
Else
instrucciones aquí si no se cumple el SI
instrucción 2
End If
Para mostrarle un mensaje al usuario:
MsgBox “mensaje a mostrar", vbExclamation, “Titulo del mensaje“
36
Ejercicio: Modificar la macro anterior para que cuando el usuario suministre un valor menor del
año 2000 o mayor del año 2011, la macro le advierta que no existe información para esos años.
Si el usuario suministra un año válido (entre 2000 y 2011), se puede aplicar el formato.
If Range("M1").Value < 2000 Or Range(“M1").Value > 2011 Then

MsgBox "El valor suministrado, no es permitido. Escriba un valor entre 2000 y 2011!", vbExclamation, "Error por Valor no admitido"
Else
Range("E2").Select
,
,
End If
If Range("M1").Value >= 2000 And Range(“M1").Value <= 2011 Then

Range("E2").Select
,
,
Else
MsgBox "El valor suministrado, no es permitido. Escriba un valor entre 2000 y 2011!", vbExclamation, "Error por Valor no admitido“
End If
37
Para ejecutar ciclos repetitivos, como por ejemplo, recorrer un determinado número de
celdas, se utiliza la instrucción FOR:
For contador = valor inicial To valor final Step paso (de 2 en 2 por ej.)
instrucciones a repetir
…
Next contador
Ejemplo: recorrer desde A1 hasta A10 para escribir números de 1 a 10 en dicho rango
For fila = 1 To 10
Range(“A” & fila).Value = fila
Next fila
Recuerda el uso del símbolo “ampersand” para concatenar cadenas de caracteres
Obsérvese que hemos llamado al contador con el nombre fila y éste se usa cada que se requiere saber en qué valor va.
Conviene utilizar una “prueba de escritorio” para verificar lo que hace un programa.
38
Conviene utilizar lo que llamamos “Prueba de escritorio” para verificar lo que hace un
programa:
For fila = 1 To 10
Range(“A” & fila).Value = fila
Next fila
fila = 1  Range(“A” & 1). Value = 1  Range(“A1”). Value = 1

39
Como se habrá observado una Macro construye un bloque de código entre un Sub y un End
Sub; por consiguiente, puedes crear tus propios bloques de código usando el siguiente
procedimiento:
1. Comienza grabando una nueva macro en la que lo único que hagas es seleccionar un par de
celdas.
2. Detén la grabación de la macro
3. Modifica la macro personalizando su nombre y agregando el código propio o el de otras
macros dentro de ella.
4. Asigna a algún objeto (botón), la macro para su ejecución.
Nombre de la Macro
Los comentarios deben comenzar con una
comilla sencilla.
Instrucciones que ejecuta la macro
Fin de la macro
40
Ejercicio: Recorrer el archivo y para aquellas cifras totales (columna F) inferiores a USD $ 10
millones, calcularles el 15% en la columna M.
Para observar visualmente el proceso, hacer que la macro seleccione primero cada celda antes
de evaluarla. De esta manera, se verá como la macro recorre la hoja aunque claramente,
demorará un poco más el proceso.
Recomendación: Para observar simultáneamente la hoja de cálculo y el editor de VBA, reduzca

el tamaño de la ventana del editor.
41
Ejercicio Final: para el archivo construir una nueva hoja en donde el usuario escriba el nombre
del país y la macro, recupere la siguiente información:
42
Referencias: Excel 15
DANE. Datos del Censo General 2005. http://www.dane.gov.co

HARMON, Mark. Step by Step Optimization with Excel Solver. Excel Master Series. ISBN: 978-1-937159-11-5. 2011.
INE. Instituto Nacional de Estadística España. Estadísticas sobre medio ambiente. http://www.ine.es/jaxi/tabla.do?path=/t26/e068/e01/l0/&file=01002.px&type=pcaxis
/-\\ Material compilado por Alexander Aragón Ch. para uso académico exclusivamente.
Aviso de Derechos de Autor: Esta presentación puede contener material protegido por derechos de autor citados en las fuentes consultadas; otro material puede ajustarse a los usos
permitidos bajo las directrices de Multimedia y exenciones para uso académico en las leyes de Derechos de Autor en algunos países o bajo licenciamiento de Creative Commons. Se prohíbe
el uso para otros propósitos.
Las siguientes Imágenes fueron obtenidas de HAAP Media Ltd. (www.freeimages.com) cobijadas por el acuerdo de licencia de restricciones estándar para imágenes descargadas del sitio.
«Polish00» by Kalyana Sundaram , «Technical drawing» by Ilker, “writing” by Michael Lorenzo,
Ultima Actualización: Mayo 25 de 2017
43

Excel Intermedio

Cargado por

Copyright:

Formatos disponibles

Excel Intermedio

Cargado por

Información del documento

Descripción original:

Derechos de autor

Formatos disponibles

Compartir este documento

Compartir o incrustar documentos

Opciones para compartir

¿Le pareció útil este documento?

¿Este contenido es inapropiado?

Copyright:

Formatos disponibles

Excel Intermedio

Cargado por

Copyright:

Formatos disponibles

Excel 15

- Presentación del docente

EJERCICIOS PRACTICOS EN CADA TEMA TRATADO

- Conocimientos básicos necesarios:

• Conceptos de Hoja de Cálculo, Libro, Celda, Rango, Direcciones de Celda

$A$1 + $A$2 Referencia absoluta

$A1 + A$2 Referencia mixta

Tres (3) parámetros!

= SI ( prueba lógica ; valor verdadero ; valor falso )

Se pueden anidar hasta 64 funciones SI() pero es más aconsejable empelarla en

Se recomienda iniciar fórmulas complejas abriendo y cerrando sus paréntesis y luego,

La función ALEAORIO() genera números uniformemente distribuidos entre 0 <= n < 1

= Y ( valor lógico1 ; valor lógico2; … )

Devuelve VERDADERO si se cumplen TODAS las condiciones evaluadas

= O ( valor lógico1 ; valor lógico2; … )

Devuelve VERDADERO si se cumple por lo menos UNA de las condiciones evaluadas

Se pueden incluir hasta 255 valores condicionales en ambas funciones

Ejercicio: =SI(Y(A2<5;B2<5;C2<5;D2<5);"Todos menores de

=SI(O(A2<5;B2<5;C2<5;D2<5);"Al menos uno menor de

Permiten controlar fórmulas que generan algún tipo de error entre:

#N/A, #¡VALOR!, #¡REF!, #¡DIV/0!, #¡NUM!, #¿NOMBRE? o #¡NULO!.

SI.ERROR ( valor a evaluar ; valor a mostrar )

Si la expresión o fórmula genera un error, se mostrará el valor indicado en el segundo

Las funciones más utilizadas para búsqueda de información son:

BUSCARV ( valor buscado ; matriz de búsqueda ; columna a recuperar ; búsqueda aproximada )

Búsqueda aproximada es:

Escribir ciudad aquí Recuperar información aquí

= SUMAR.SI ( rango a evaluar ; criterio ; rango a sumar )

Si el rango a evaluar es el mismo a sumar, se puede omitir el tercer parámetro.

= SUMAR.SI ( rango a evaluar ; criterio ; rango a sumar )

= CONTAR.SI ( rango a evaluar ; criterio )

En la segunda función, se admiten hasta 127 pares de criterios.

= CONTARA ( valor1, valor2, … )

Construir una planilla de notas con el siguiente formato:

Facilita la organización de datos en listas configurables de una manera más flexible.

Son el tipo de datos los que determinan

Permite encontrar un valor de entrada para obtener un resultado conocido.

Complemento de Excel utilizado para encontrar valores óptimos considerando restricciones o

La lista disponible de alimentos es la siguiente:

Cantidades por cada Unidad

¿Qué cantidad de cada alimento sería la apropiada?

Detener la grabación con el botón STOP mostrado en la

Los datos ordenados, deberán tener color de fuente Azul.

Ejercicio: Para el archivo, construir estas tres macros individuales:

Macro1: Ordenar todos los datos por País y año.

Para indicar el valor que hay en una celda:

Para seleccionar una celda o un rango:

Para borrar el contenido de una celda o rango:

“=$E2=“ & Range(“A1”).Value

En VBA también se pueden construir decisiones como en la función SI() de Excel:

Para mostrarle un mensaje al usuario:

MsgBox “mensaje a mostrar", vbExclamation, “Titulo del mensaje“

If Range("M1").Value < 2000 Or Range(“M1").Value > 2011 Then

If Range("M1").Value >= 2000 And Range(“M1").Value <= 2011 Then

Recuerda el uso del símbolo “ampersand” para concatenar cadenas de caracteres

fila = 1  Range(“A” & 1). Value = 1  Range(“A1”). Value = 1

Recomendación: Para observar simultáneamente la hoja de cálculo y el editor de VBA, reduzca

DANE. Datos del Censo General 2005. http://www.dane.gov.co

«Polish00» by Kalyana Sundaram , «Technical drawing» by Ilker, “writing” by Michael Lorenzo,