EXCEL

AVANZADO:
FUNCIONES Y
FORMULAS
MODULO III

MINPE INSTITUCIÓN DE FORMACIÓN

CONTENIDO
FUNCIONES DE FECHA Y HORA ........................................................................................................................... 2
FUNCIONES HOY-AHORA-DIA-MES-AÑO ........................................................................................................ 2
FUNCIONES DIASEM-NUM.DE.SEMANA ......................................................................................................... 4
FUNCIONES MATEMÁTICAS ............................................................................................................................... 5
FUNCIÓN SUMAR.SI ........................................................................................................................................ 5
LA FUNCIÓN CONTAR.SI ................................................................................................................................. 6
FUNCIONES ESTADÍSTICAS ................................................................................................................................. 6
FUNCIÓN FRECUENCIA ................................................................................................................................... 6
FUNCION TENDENCIA ..................................................................................................................................... 8
FUNCIONES DE BÚSQUEDA Y REFERENCIA....................................................................................................... 10
FUNCIÓN BUSCARV-BUSCARH...................................................................................................................... 10
FUNCIÓN INDICE ........................................................................................................................................... 10
FUNCIÓN INDIRECTO .................................................................................................................................... 11
FUNCIONES DE BASE DE DATOS ....................................................................................................................... 13
FUNCIÓN BDSUMA ....................................................................................................................................... 13
FUNCIONES DE TEXTO ...................................................................................................................................... 14
FUNCIONES DERECHA-IZQUIERDA-EXTRAE .................................................................................................. 14
FUNCIONES LOGICAS ........................................................................................................................................ 15
FUNCIONES SI – Y – O ................................................................................................................................... 15
LABORATORIO I ................................................................................................................................................ 16
INDICE – INDIRECTO – COLUMNA – CUADRO COMBINADO ........................................................................ 16
CUADRO COMBINADO.................................................................................................................................. 19
LABORATORIO II ............................................................................................................................................... 21
LABORATORIO III .............................................................................................................................................. 22
HERRAMIENTA BUSCAR OBJETIVO ............................................................................................................... 24

Página 1 de 25
FUNCIONES DE FECHA Y HORA

FUNCIONES HOY-AHORA-DIA-MES-AÑO

UTILIDAD
Estas funciones se emplean para manipular datos ingresados como números en serie en los
formatos permisibles. Dichas funciones permitirán al usuario obtener datos específicos, e
inclusive realizar cálculos utilizando los valores de fecha y hora.

Excel considera a las fechas como valores numéricos. Estos valores están entre 1, el cual es
considerado como 1 de enero de 1900, al 2958465, correspondiente al 31 de diciembre de
9999 .Así, el número 5 corresponde al 5 de enero de 1900 y el 32, al 1 de febrero de 1900.

ACCESO
FÓRMULAS→ INSERTAR FUNCIÓN→ FECHA Y HORA → [FUNCIÓN]

EJEMPLO
1. Abre un archivo nuevo
2. Escribe en la celda A3: Fecha Actual
3. En la celda D3 escribe la fórmula = HOY()
4. Escribe en la celda A5: Fecha y hora actual
5. En la celda D5 escribe la fórmula: =AHORA()
6. Escribe en la celda A7: Día del mes
7. En la celda D7 escribe la fórmula : =DIA(D3)
8. Escribe en la celda A9 : Mes del año
9. En la celda D9 escribe la fórmula = MES (D3)
10. Escribe en la celda A11 : Año
11. En la celda D11 escribe la fórmula: =AÑO(D3)
12. En la celda G3 escribe: Días para año nuevo
13. En la celda J2 escribe : 31/12/2018
14. En la celda J3 ingresa la fórmula : =J2-D3

Página 2 de 25
15. A partir de la celda M3 escribe:

1 Enero

2 Febrero

3 Marzo

4 Abril

5 Mayo

6 Junio

7 Julio

8 Agosto

9 Septiembre

10 Octubre

11 Noviembre

12 Diciembre

Tabla 1 - Funciones y Formulas

16. Marca con el mouse desde la celda M3 hasta la N14

17. Asigna el nombre el rango : Meses
18. En la celda D9 ingresa la fórmula :
=BUSCARV(C9;meses;2;FALSO)
19. En la celda A15 ingresa la fórmula :
=CONCATENAR(“Es el mes de”;D9;”del año“;C11)
20. Guarda el archivo

EJERCICIO
1. Calcula cuántos días faltan oficialmente para la próxima navidad
2. Calcula cuántos días faltan para tu próximo cumpleaños

Página 3 de 25
FUNCIONES DIASEM-NUM.DE.SEMANA

ACCESO
FÓRMULAS → INSERTAR FUNCIÓN → FECHA Y HORA → [FUNCIÓN]

EJEMPLO
1. Abre un archivo nuevo
2. En la celda B3 escribe la fecha de hoy. Es decir escribe: =HOY()
3. En la celda C3 escribe : =DIASEM(B3)
4. El resultado indica el número de día correspondiente a la fecha de hoy. Para Excel el
día domingo es el primer día de la semana.
5. En la celda C4 escribe: =DIASEM(B3,2)
6. El resultado considerar el primer día de la semana al lunes. Ello se consigue agregando
el parámetro 2 a la función.
7. En la celda B10 ingresa : 31/12
8. En la celda C10 escribe la función : =NUM.DE.SEMANA(B10)
9. El resultado indica el número de la semana del año al cual pertenece la fecha indicada.

EJERCICIO
1. Abre el archivo MODULO III_3001_FECHAS1
2. Completa las columnas

EJERCICIO
COSTO DE ALMACENAJE DÍAS PARA EL PAGO

1. Abre el archivo MODULO III_3002_COSTO DE ALMACENAMIENTO

2. Inserta en la fecha B3 la fórmula : =HOY()
3. Escribe en la celda B5 el número 5 ( representa 5 metros cúbicos)
4. Inserta en la celda B7 una fecha anterior a diez días a la fecha de hoy
5. Escribe en la celda B9 la fórmula =B3-B7
6. Escribe en la celda B11 la fórmula :
=SI(B9<=30;B5*B17*B9;B17*30*B5+B18*(B9-30)*B5)
7. El resultado (celda B11) es el costo total de almacenamiento

EJERCICIO
1. Abre el archivo MODULO III_3003_FECHA DE PAGO
2. Escribir para cada cliente diferentes fechas de pago.
3. Calcular los días que faltan para el pago.
4. Aplicar formato condicional a la columna C

Página 4 de 25
FUNCIONES MATEMÁTICAS

FUNCIÓN SUMAR.SI

UTILIDAD
Suma las celdas en el rango que coinciden con el argumento criterio.

ACCESO
FÓRMULAS → INSERTAR FUNCIÓN → MATEMÁTICAS Y TRIGONOMÉTRICAS → SUMAR.SI

Ilustración 1 - Funciones y Formulas

EJEMPLO
1. Abre el archivo MODULO III_3004_FUNCIONES MATEMÁTICAS
2. Ubícate en la hoja : Casas
3. Selecciona la celda E4 y escribe : Valor
4. Selecciona la celda E5
5. Escribe la siguiente fórmula:
=SUMAR.SI($C$15:$C$31;C5;$E$15:$E$31)

A través de la fórmula anterior se calcula el valor total de las casas del departamento de
Piura.

6. Selecciona la celda G4 y escribe : Habitaciones

7. En la celda G5 escribe la siguiente fórmula :
=SUMAR.SI($C$15:$C$31;C5;$G$15:$G$31)

A través de la fórmula anterior se calcula el número total de habitaciones ubicadas en

casas del departamento de Piura

8. Selecciona la celda H4 y escribe : Baños

Página 5 de 25
9. En la celda H5 escribe la siguiente fórmula:
=SUMAR.SI($C$15:$C$31;C5;$H$15:$H$31)

A través de la fórmula anterior se calcula el número total de baños ubicados en casas del
departamento de Piura.

10. Copia las fórmulas al resto de las columnas : Valor, habitaciones y baños
11. Analiza la información obtenida

EJERCICIO
1. Inserta tres filas entre el cuadro resumen y la tabla de datos
2. Obtén el valor de las casas alquiladas y el valor de las casas propias.
3. Qué porcentaje del valor total de casas corresponde a las casas alquiladas.

LA FUNCIÓN CONTAR.SI

Aplicarla a la hoja Casas a través del campo Pisos. Resolver la pregunta: ¿Cuántas casas
tienen 4 pisos?

FUNCIONES ESTADÍSTICAS

FUNCIÓN FRECUENCIA

UTILIDAD
Calcula la frecuencia con que se repiten los valores de un rango y devuelve una matriz
vertical de números .Por ejemplo, utilice FRECUENCIA para contar el número de los
resultados que se encuentran dentro de un rango .Debe introducirse como una fórmula de
matrices debido a que FRECUENCIA devuelve una matriz.

ACCESO
FÓRMULAS → INSERTAR FUNCIÓN → ESTADÍSTICAS → FRECUENCIA

Página 6 de 25
 Ilustración 2 -Funciones y Formulas

EJEMPLO
1. Abre el archivo MODULO III_3005_FUNCIONES ESTADÍSTICAS
2. El archivo reúne la producción de unidades por máquina y por semana del año
3. Ubícate en la hoja : Produccion
4. Completa la tabla (sem_14 y sem_15) con valores entre 115 y 150.
5. Marca desde la celda B6 hasta la celda F20; crea el rango Producción
6. En la celda I5 escribe : Grupos
7. En la celda J5 escribe: Frecuencia
8. Escribe :
 En la celda I6: 120
 En la celda I7 : 125
 En la celda I8 : 130
 En la celda I9 : 135
 En la celda I10: 140
9. Marca con el mouse las celdas : desde la J6 hasta la J11
10. Escribe en la barra de fórmulas ( sin presionar ENTER):
=FRECUENCIA(produccion;I6:I10)
11. Para convertir la fórmula en matricial , presione la combinación de teclas
[Ctrl]+[Shift]+[Enter]
12. Como resultado final obtendrá las frecuencias
13. Escribe
 En la celda H6: “<=120”
 En la celda H7: “<=125”
 En la celda H8: “<=130”
 En la celda H9: “<=135”
 En la celda H10: “<=140”
 En la celda H11:“>140”

14. Inserte un gráfico de frecuencias (por ejemplo tipo barras)

Página 7 de 25
 27

20

9 9
6
4

<=120 <=125 <=130 <=135 <=140 >140

Ilustración 3 - Funciones y Formulas

EJERCICIO
1. A partir de la celda H20 calcule la frecuencia utilizando los siguientes grupos : <=120;
<=130 y >140
2. Grafique el resultado.

EJERCICIO
1. En el mismo archivo MODULO III_3005_FUNCIONES ESTADÍSTICAS
2. Ubícate en la hoja : Defectuosos
3. Calcule las frecuencias desde la celda K6 hasta la celda K11
4. Investiga (Utilizando F1)
 La función estadística: MODA
 La función estadística: MEDIANA

FUNCION TENDENCIA

UTILIDAD
Devuelve valores que resultan de una tendencia lineal. Ajusta una recta (calculada con el
método mínimo cuadrados) a los valores de las matrices definidas por los argumentos
conocido_y y conocido_x. Devuelve, a lo largo de esa recta , los valores y correspondientes
a la matriz definida por el argumento nueva_matrix_x especificado.

ACCESO
FÓRMULAS → INSERTAR FUNCIÓN →ESTADÍSTICAS → TENDENCIA

Página 8 de 25
 Ilustración 4 - Funciones y Formulas

EJEMPLO
1. Abre el archivo MODULO III_3005_FUNCIONES ESTADÍSTICAS
2. Ubícate en la hoja Tendencia
3. La hoja contiene las ventas en unidades reales de los doce primeros meses
4. A continuación se calculará (pronosticará) la venta los seis meses siguientes
5. Marque con el mouse los valores desde la celda B7 hasta la celda B18
6. Nombre el rango seleccionado como: Ventas
7. Marque con el mouse los valores desde la celda A7 hasta la celda A18
8. Nombre al rango seleccionado como: Meses
9. En la celda F7 ingrese la fórmula :
=TENDENCIA (ventas;meses;E7)
10. El resultado es la venta pronosticada para el mes 13 calculado en base a la tendencia
lineal
11. Copie la fórmula para los meses 14,15,16,17 y 18
12. Aplique formato de número sin decimales a los valores pronosticados.
13. Copie (utilizando pegar valores) los meses y los resultados obtenidos debajo del mes 12
14. Grafique utilizando el gráfico de líneas.
15. Agregar una línea de tendencia y presentar la ecuación en el gráfico
16. Analice el resultado.

EJERCICIO
17. En la misma hoja , escriba en la celda H6: Mes
18. Escriba en la celda H7: 24
19. Pronostique la venta para el mes 24
20. Investiga (utilizando F1)
 La función PENDIENTE

Página 9 de 25
FUNCIONES DE BÚSQUEDA Y REFERENCIA

FUNCIÓN BUSCARV-BUSCARH

UTILIDAD
Estas funciones, junto con las funciones lógicas, son las de mayor aplicación para resolver
una gran variedad de cálculos con los datos de las hojas de Excel.

Las funciones de búsqueda permiten al usuario obtener de manera rápida y sencilla valores
específicos de una matriz de datos, en base a la búsqueda de un valor específico que
coincida con los datos de la primera fila o columna de dicha matriz.

ACCESO
FÓRMULAS → INSERTAR FUNCIÓN →BÚSQUEDA Y REFERENCIA → [FUNCIÓN]

EJERCICIO
1. Utiliza F1 para revisar las funciones BUSCARV y BUSCARH
2. Construye un ejercicio utilizando la función BUSCARV
3. Construye un ejercicio utilizando la función BUSCARH

FUNCIÓN INDICE

UTILIDAD
Devuelve un valor o la referencia a un valor en una tabla o rango.

ACCESO
FÓRMULAS → INSERTAR FUNCIÓN → BÚSQUEDA → [FUNCIÓN]

EJEMPLO
1. Abre un archivo nuevo
2. Ingresa el siguiente cuadro:

ID Producto Cantidad Precio

1 Martillo 242 35

2 Destornillador 32 19

3 Taladro 23 300

4 Clavos 102 3

Tabla 2 - Funciones y Formulas

Página 10 de 25
3. Selecciona la data ingresada y crea el grupo Grupo1
4. Ingresa la siguiente fórmula en cualquier celda libre =INDICE (Grupo1;2;4). El resultado
debe ser 35. Es decir el valor de la celda ubicada en la fila 2 y la columna 4 del Grupo1
5. Ingresa la fórmula: =INDICE(Grupo1;5;2). El resultado debe ser Clavos
6. En una hoja nueva escribe:

10 20 30 40

Tabla 3 - Funciones y Formulas

Selecciona las celdas y crea un grupo: Grupo2

7. Ingresa la fórmula =INDICE(Grupo2;3). El resultado debe ser 30.

EJERCICIO
1. Crea dos grupos (GRUPO 10 y GRUPO 20). Cada grupo debe contener 3 filas y 3
columnas. Todos los valores de los grupos deben ser números.
2. En una celda nueva, suma un valor del GRUPO 10 y un valor del GRUPO20 utilizando la
función INDICE.

FUNCIÓN INDIRECTO

UTILIDAD
Devuelve un valor o la referencia a un valor en una tabla o rango.

ACCESO
FÓRMULA → INSERTAR FUNCIÓN →BÚSQUEDA → [FUNCIÓN]

EJEMPLO
1. Abre un archivo nuevo
2. Ingresa a partir de la celda A1 el siguiente cuadro:
Datos Valores
B2 1.333

B3 45

Paz 10

5 62

Tabla 4 - Funciones y Formulas

3. Selecciona la celda B4. Crea el grupo PAZ.

4. Selecciona la celda A7 e ingresa la fórmula: =INDIRECTO(A2) Analiza el resultado
5. Selecciona la celda A8 e ingresa la fórmula: =INDIRECTO(A3) Analiza el resultado
6. Selecciona la celda A9 e ingresa la fórmula: =INDIRECTO(A4) Analiza el resultado.

Página 11 de 25
7. Selecciona la celda A10 e ingresa la fórmula: =INDIRECTO(“B”&A5) Analiza el resultado

EJEMPLO
1. Abrir un archivo nuevo.
2. A partir de la celda A1 escribir lo siguiente:
PAÍS PERU ARGENTINA ECUADOR
CIUDAD LIMA BUENOS AIRES GUAYAQUIL
PIURA MENDOZA QUITO
TRUJILLO CÓRDOBA

Tabla 5 - Funciones y Formulas

3. Sombrear las celdas B1 y B2 de color amarillo.

4. Seleccionar los países (celdas D1,E1 y F1) y crear el grupo: PAÍSES
5. Seleccionar las ciudades de Perú (celdas D2, D3 y D4) y crear el grupo: PERÚ
6. Seleccionar las ciudades de Argentina (celdas E2, E3 y E4) y crear el grupo: ARGENTINA
7. Seleccionar las ciudades de Ecuador (celdas F2 y F3) y crear el grupo: ECUADOR
8. Seleccionar la celda B1
9. Seleccionar Validación de datos:
DATOS → VALIDACIÓN DE DATOS → VALIDACIÓN DE DATOS
10. En Permitir, Seleccionar Lista
11. En Origen, escribir : =PAISES
12. Presionar Aceptar
13. Podrás seleccionar un país en la celda B1. Haz la prueba
14. Seleccionar la celda B2
15. Seleccionar Validación de datos:
DATOS → VALIDACIÓN DE DATOS → VALIDACIÓN DE DATOS
16. En permitir, seleccionar Lista
17. En Origen ,escribir : =INDIRECTO(B1)
18. Presionar Aceptar
19. Resultado, cuando selecciones un país en la celda B1 aparecerá la lista de ciudades
en la celda B2

EJERCICIO
1. Realizar las operaciones del ejemplo 2 con la siguiente data
PAIS ESPAÑA ITALIA INGLATERRA
EQUIPOS REAL JUVENTUS MANCHESTER
MADRID CITY
BARCELONA MILAN MANCHESTER
UNITED
VALENCIA LAZIO CHELSEA

Tabla 6 - Funciones y Formulas

Página 12 de 25
FUNCIONES DE BASE DE DATOS

FUNCIÓN BDSUMA

UTILIDAD
Suma los números de una columna perteneciente a una lista o base de datos que cumplen
con las condiciones especificadas.

ACCESO
FÓRMULAS → INSERTAR FUNCIÓN → BASE DE DATOS →BDSUMA

Ilustración 5 – Funciones y Formulas

EJEMPLO
1. Abre el archivo MODULO III_3006_FUNCIONES BASE DE DATOS
2. Ubícate en la hoja Venta_Autos
3. La lista contiene datos de la venta mensual en unidades por marca de autos.
4. Marca con el mouse desde la celda A4 hasta la celda C64
5. Para el rango seleccionado asígnale el nombre: bd_autos
6. En la celda F3 escribe : Criterios
7. Escribe:
 En la celda F4: Mes
 En la celda G4: Marca
8. Marca con el mouse desde la celda F4 hasta la celda G5
9. Para el rango seleccionado asígnale el nombre: Criterios
10. En la celda J4 escribe: Resultado de la consulta
11. En la celda J5 escribe la fórmula:
=BDSUMA(bd_Autos,C4;Criterios)
12. Analiza el resultado
13. En la celda F5 escribe : Febrero

Página 13 de 25
14. Analiza el resultado
15. En la celda G5 escribe: Toyota
16. Analiza el resultado obtenido en la celda J5
17. ¿Cuántos autos Volkswagen se han vendido en Julio?
18. Coloca en la celda F5 un * (asterisco)
19. Coloca en la celda G5 un*(asterisco)
20. Interpreta el resultado.

EJERCICIO
1. En el mismo archivo MODULO III_3006_FUNCIONES BASE DE DATOS
2. Ubícate en la hoja Casas
3. Aplica la función BDSUMA donde:
 Nombre del campo de la celda E5: Valor
 Criterios esté definido por los campos Departamento, Tipo y Pisos.
4. ¿Cuál es el valor de las casas de Piura y que sean alquiladas?
5. ¿Cuál es el valor de las casas de 2 pisos en Lima?
6. ¿Cuál es el valor de las casas de Piura, Propias y de 2 pisos?
7. Investiga (utilizando F1) ,la función BDCONTAR, aplicar la función en la tabla ubicada en
la hoja Casas.

FUNCIONES DE TEXTO

FUNCIONES DERECHA-IZQUIERDA-EXTRAE

UTILIDAD
Extrae de una cadena de texto un conjunto de caracteres.

ACCESO
FÓRMULAS → INSERTAR FUNCIÓN → TEXTO → [FUNCIÓN]

EJEMPLO
1. Abre el archivo MODULO III_3007_FUNCIONES DE TEXTO
2. El número de cuenta indicado en el archivo adjunto contiene información acerca de :
 Tipo de tarjeta : Clásica y Dorada
 Sucursal: Lima, Piura y Cuzco
 Correlativo
3. La información se extraerá utilizando las funciones de texto
4. Ubicarse en la celda E6 e ingresa la siguiente formula: =DERECHA(B6;5)
5. A través de la formula se ha extraído los últimos 5 caracteres de la cuenta. Estos cinco
caracteres representan el correlativo
6. Copiar la formula al resto de la columna Correlativo
7. Ubicarse en la celda C6 e ingresa la siguiente formula: =IZQUIERDA(B6;4)

Página 14 de 25
8. A través de la formula se ha extraído los primeros 4 caracteres de la cuenta. Estos cuatro
caracteres representan el tipo de tarjeta
9. Copiar la formula al resto de la columna Tipo de Tarjeta
10. Ubicarse en la celda D6 e ingresar la siguiente formula: +EXTRAE(B6;6;2)
11. A través de la formula se han extraído dos caracteres a partir del sexto carácter
12. Copiar la formula al resto de la columna sucursal

EJERCICIO
1. En la columna F, extrae los dos primeros dígitos del correlativo
2. En la columna G, utiliza la función concatenar para unir mediante dos guiones las
celdas: tipo tarjeta, sucursal y correlativo
3. Investigar las funciones: TEXTO; CONCATENAR y LARGO

FUNCIONES LOGICAS

FUNCIONES SI – Y – O

UTILIDAD
Estas funciones son una de las más importante debido a la frecuencia con que se usan y la
gran cantidad de casos posibles de aplicación que poseen. Dichas funciones permitirán al
usuario establecer condiciones a comprobar y en base a los resultados obtenidos, realizar
una operación determinada; como ingresar un dato, ejecutar una formula o aplicar una
función.

ACCESO
FORMULAS → INSERTAR → FUNCION → LOGICAS → [FUNCION]

1. Abrir el archivo MODULO III_3008_FUNCIONES LOGICAS

2. Seleccionar la hoja SI-O-Y
3. Seleccionar la celda D6
4. Ingresa la siguiente formula:
=SI(C6=”1000”;”Tarjeta clásica”;“Tarjeta Dorada”)
5. Copiar la formula al resto de la columna
6. En la celda F6 ingrese la siguiente formula:
=SI(E6=”10”;”LIMA”;SI(E6=”25”;”PIURA”;SI(E6=”30”;”CUZCO”;”-“)))
7. La fórmula asigna el nombre Lima a las sucursales 10, Piura a las sucursales 25, Cuzco a
las sucursales 30 y “-“ al resto
8. Escribir en la H5: Bono
9. En la celda H6 ingrese la siguiente formula:
=SI(Y(C6=”2000”;E6=”25”);150;50)
10. La fórmula asigna un bono de 150 a las tarjetas que sean doradas y que sean de Piura.
Al resto de las tarjetas les asigna un bono de 50
11. Escriba en la celda I5: Castigo
12. En la celda I6 ingrese la siguiente formula:

Página 15 de 25
 =SI(O(C6=”1000”;E6=”30”);20;10)
13. La fórmula asigna un castigo de 20 a las tarjetas que son clásicas o a la que pertenezcan
a la sucursal Cuzco y al resto de 10

EJERCICIO
1. En la celda J5 escriba Bono2
2. Si el correlativo empieza con 1 asigne un Bono2 de 120, sino; el Bono2 será de 40

EJERCICIO
1. Del archivo MODULO III_3008_FUNCIONES LOGICAS selecciona la hoja Proveedores
2. En la celda G13 ingrese la siguiente formula:
+B13*$B$12/100+SI(C13=”S”;$C$12;0)+SI(D13<=20;$D$12;0)+
SI(E13=”S”;$E$12;0)+SI(F13=”S”;$F$12;0)
3. La fórmula calcula la calificación de la evaluación técnica de cada proveedor
4. Copiar la formula al resto de la columna
5. Aplicar formato porcentaje
6. Aplicar formato condicional, el de su preferencia
7. Ingresar la siguiente formula en la celda J13:
=H13*$H$12/100+SI(I13=”S”;$I$12;0)
8. La fórmula calcula la calificación de la evaluación económica de cada postor
9. Cope la formula al resto de la columna
10. Aplique formato porcentaje
11. Aplique formato condicional, el de su preferencia
12. Escribir en la celda K11: Calificación Final
13. Ingresar en la celda K13 la siguiente fórmula: =G13*$D$5+J13*$D$6
14. La fórmula calcula la calificación final de cada postor
15. Copie a formula al resto de la columna
16. Aplique formato porcentaje
17. Aplique formato condicional, el de su preferencia

LABORATORIO I

INDICE – INDIRECTO – COLUMNA – CUADRO COMBINADO

El ejercicio consiste en relacionar la información de cuatro hojas de Excel y presentar el

resumen en una hoja. El objetivo es conocer la interacción entre las funciones INDICE,
INDIRECTO y un cuadro combinado de la ficha Programador.

1. Abrir un archivo nuevo

2. Crear cinco hojas: PRINCIPAL, PIU, TRU, CHI y AQP

Página 16 de 25
3. En la hoja PIU, a partir de la celda A1 escribir lo siguiente:

PIURA

Venta año actual Venta año anterior Incr%

10 400 309
20 312 290
30 243 287
40 102 99
Tabla 7 - Funciones y Formulas

4. En la celda D4 escribe la fórmula del incremento: =(B4-C4)/C4

5. Copia la fórmula del incremento al resto de la columna
6. Seleccionar desde la celda A4 hasta la celda D7 y crea un grupo PIU
7. En la hoja TRU escribir a partir de la celda A1:

TRUJILLO

Venta año actual Venta año anterior Incr%

10 304 300
20 292 200
30 492 302
40 592 304
Tabla 8 - Funciones y Formulas

8. Ingresa la formula incremento

9. Seleccionar desde la celda A4 hasta la celda D7 y crea un grupo TRU
10. En la hoja CHI escribir a partir de la celda A1:

CHICLAYO

Venta año actual Venta año anterior Incr%

10 230 200
20 190 180
30 321 300
40 345 300
Tabla 9 - Funciones y Formulas

11. Ingresa la fórmula del incremento

12. Seleccionar desde la celda A4 hasta la celda D7 y crea un grupo CHI

Página 17 de 25
13. En la hoja AQP escribir a partir de la celda A1:

AREQUIPA

Venta año actual Venta año anterior Incr%

10 230 210
20 320 310
30 430 230
40 433 380
Tabla 10 - Funciones y Formulas

14. Ingrese la fórmula del incremento

15. Seleccionar desde la celda A4 hasta la celda D7 y crea un grupo AQP
16. En la hoja PRINCIPAL ingresar a partir de la celda A1 lo siguiente:

RESUMEN
Jerarquía

Venta año actual Venta año anterior Incr%

PIU
TRU
CHI
AQP
Tabla 11 - Funciones y Formulas

17. En la misma hoja PRINCIPAL, ingresar lo siguiente a partir de la celda G6:

10 10-VIDEO
20 20-AUDIO
30 30-PEDS
40 40-FOTOGRAFIA
Tabla 12 - Funciones y Formulas

18. Seleccionar la celdas G6 a G9, crear un grupo llamado INDEX1

19. Seleccionar la celdas H6 a H9, crear un grupo llamado INDEX2

Página 18 de 25
CUADRO COMBINADO

20. En el botón de Excel seleccionar Opciones:

Ilustración 6 - Funciones y Formulas

21. Seleccionar Mostar ficha DESARROLLADOR en la cinta de opciones

Ilustración 7 - Funciones y Formulas

Página 19 de 25
22. Ir a la sección de DESARROLLADOR hacer clic en Insertar
23. Seleccionar Cuadro Combinado
24. Insertar el objeto, dibujando el rectángulo en la celda B2
25. Dar clic derecho al Cuadro Combinado
26. Seleccionar Formato de Control
27. En la pestaña Control ingresar los siguientes valores:

Ilustración 8 - Funciones y Formulas

28. Presionar Aceptar

29. Probar la carga de la data haciendo clic en la fecha del Cuadro Combinado
30. En la celda B6, ingresar la siguiente formula:
=BUSCARV(INDICE(INDEX1;$G$2);INDIRECTO($A6);COLUMNA(B$1);FALSO)
31. Copia la formula desde la celda B6 hasta la celda D9
32. Marcar desde la celda A5 hasta la celda C9 e ingresar un gráfico
33. Seleccionar las categorías utilizando el Cuadro Combinado

Página 20 de 25
LABORATORIO II

Ejecuta lo realizado en el Laboratorio I con la siguiente información:

PLANTA 1

PRODUCCION 1 PRODUCCION 2
10 1200 1500
20 900 1000
30 100 50
Tabla 13 - Funciones y Formulas

PLANTA 2

PRODUCCION 1 PRODUCCION 2
10 500 300
20 700 600
30 1500 1800
Tabla 14 - Funciones y Formulas

PLANTA 3

PRODUCCION 1 PRODUCCION 2
10 1000 1100
20 100 150
30 200 300
Tabla 15 - Funciones y Formulas

10 10-uva
20 20-mango
30 30-palta
Tabla 16 - Funciones y Formulas

Página 21 de 25
LABORATORIO III

El ejercicio permitirá realizar ingresos y salidas de stock. Además, mostrará en una hoja
resumen el stock actual.

1. Crear un nuevo documento

2. A partir de la celda A1 escribir: Fecha, Producto, Descripción, Ingreso y Salida
3. A partir de la celda J1 escribir:

CODIGO PRODUCTO
110 110-Cuadernos
120 120-Libros
130 130-Lapiceros
140 140-Lapices
Tabla 17 - Funciones y Formulas

4. Seleccionar las columnas B, C, D y E. Crear el grupo gRegistros

5. Seleccionar la columna D. Crear el grupo gIngresos
6. Seleccionar la columna E. Crear el grupo gSalidas
7. Seleccionar desde la celda J2 hasta la celda J6. Crea el grupo gProductos1
8. Seleccionar desde la celda J2 hasta la celda K6. Crea el grupo gProductos2
9. Seleccionar la celda B2
10. Seleccionar Datos, Validación de Datos, Validación de Datos
11. Seguir los siguientes pasos:

Ilustración 9 - Funciones y Formulas

12. Presionar aceptar

13. Copiar el contenido de la celda B2 hasta la celda B100

Página 22 de 25
14. Seleccionar la celda C2, escribir la siguiente formula:

+SI(ESERROR(BUSCARV(B2;gProductos2;2;FALSO));”-“;BUSCARV(B2;gProductos2;2;FALSO))

15. Ingresar el siguiente contenido a partir de la celda A2

FECHA PRODUCTO DESCRIPCION INGRESO SALIDA

25/01/2017 110 110-Cuadernos 5000
25/01/2017 120 120-Libros 4800
25/01/2017 130 130-Lapiceros 12000
25/01/2017 140 140-Lapices 800
2/02/2017 110 110-Cuadernos 1000
2/02/2017 120 120-Libros 800
2/02/2017 130 130-Lapiceros 2000
2/02/2017 110 110-Cuadernos 400
3/02/2017 110 110-Cuadernos 100
3/02/2017 120 120-Libros 500
Tabla 18 - Funciones y Formulas

16. Nombrar a la Hoja como Registro

17. En una hoja nueva, a partir de la celda A1 ingresar lo siguiente:

CODIGO PRODUCTO STOCK ACTUAL

110 Cuadernos
120 Libros
130 Lapiceros
140 Lápices
Tabla 19 - Funciones y Formulas

18. En la celda C2 ingresar la función:

=SUMAR.SI(gRegistros;A2;gIngresos)-SUMAR.SI(gRegistros;A2;gSalidas)
19. Copiar el contenido de la celda C2 hasta la celda C5
20. Nombre a la hoja Resumen

Página 23 de 25
HERRAMIENTA BUSCAR O BJETIVO

1. Investigue presionando F1
2. A partir de la celda A1 ingresar lo siguiente:

PRECIO CANTIDAD TOTAL

PRODUCTO 1 120 10 =B2*C2
PRODUCTO 2 200 25 =B3*C3

TOTAL VENTA =D2+D3

GANANCIA 25%

TOTAL =D5*D7
GANANCIA
Tabla 20 - Funciones y Formulas

3. Seleccionar Buscar Objetivo: DATOS – ANÁLISIS DE HIPÓTESIS – BUSCAR OBJETIVO

Ilustración 10 - Funciones y Formulas

4. Ingrese la siguiente información:

Ilustración 11 - Funciones y Formulas

Página 24 de 25
5. La función Buscar Objetivo ha modificado la cantidad del Producto 1 de tal manera
que la ganancia sea 2000
6. Aplicar Buscar Objetivo para que la ganancia sea de 3500 modificando el precio del
Producto 2
7. Aplicar Buscar Objetivo para que la Venta sea de 10,000 modificando el precio del
Producto 1

Página 25 de 25