• Author / Uploaded
• Jonathan Browning

• Categories
• Finanzas Matemáticas
• Interés
• Microsoft Excel
• Tabla (base de datos)
• Bancos

Citation preview

ÍNDICE  CAPÍTULO I: FORMATO CONDICIONAL.............................................................2 FORMATO CONDICIONAL................................................................................................3 ADMINISTRAR REGLAS..................................................................................... 3 REGLAS DE CELDAS......................................................................................... 7 BARRA DE DATOS............................................................................................. 8 ESCALAS DE COLOR........................................................................................ 9 CONJUNTO DE ICONOS................................................................................... 10

 CAPÍTULO II: FUNCIONES LOGICAS...............................................................14 FUNCIONES LOGICAS....................................................................................................15 FUNCION SI.................................................................................................... 15 FUNCIÓN SI ANIDADA..................................................................................... 18 FUNCIÓN O..................................................................................................... 22 FUNCIÓN Y..................................................................................................... 23 FUNCION ANIDADAS (SI(Y(O)))........................................................................24

 CAPÍTULO III: FUNCION BUSCAR....................................................................26 FUNCIÓN BUSCAR.......................................................................................... 27 FUNCION “BUSCARH”..................................................................................... 29 FUNCION “BUSCARV”..................................................................................... 30

 CAPÍTULO IV: TABLAS Y GRAFICOS DINAMICOS.........................................34 TABLAS DINÁMICAS.......................................................................................................35 GRÁFICOS DINÁMICOS..................................................................................................39

 CAPÍTULO V: VALIDACION DE DATOS............................................................42

VALIDACION DE DATOS.................................................................................................43

 CAPÍTULO VI: MATEMATICA FINANCIERA......................................................47 NOMENCLATURA BÁSICA..............................................................................................48 RESUMEN DE FORMULAS.............................................................................................49 INTERÉS SIMPLE............................................................................................................. 50 MONTO............................................................................................................................. 55 DESCUENTOS.................................................................................................................. 58 FUNCIONES FINANCIERAS............................................................................................62 FUNCIÓN VF................................................................................................... 62 FUNCIÓN VA................................................................................................... 66 FUNCIÓN PAGO.............................................................................................. 68 FUNCIÓN TASA............................................................................................... 70 FUNCIÓN NPER............................................................................................... 74 FUNCIÓN TIR.................................................................................................. 75

 CAPÍTULO VII: PROTECCIÓN DE LIBROS Y HOJAS DE CÁLCULO.............78 PROTECCIÓN DE LIBROS Y HOJAS DE CÁLCULO......................................................79

Cestec - Perú



CAPÍTULO I: FORMATO CONDICIONAL …………..….…………………………………………………………………………

FORMATO CONDICIONAL

Objetivos:  

Conocer el manejo de formatos condicionales. Aplicar formatos condicionales a una celda.

Excel 2016

Pág. 2

Cestec - Perú FORMATO CONDICIONAL El formato condicional permite modificar el aspecto de determinadas celdas, de acuerdo al cumplimiento o no de reglas lógicas. Muy útil cuando se trata de trabajar con condicionales. Soporta hasta 64 condiciones.

ADMINISTRAR REGLAS Esta opción nos permite seleccionar todos los formatos condicionales creados en nuestro libro de Excel.

EJERCICIO 1 Crear la siguiente tabla y aplicar las siguientes reglas:  

Dar el color de fuente ROJO para las notas menores a 11. Dar el color de fuente AZUL para las notas mayores o iguales a 11.

Excel 2016

Pág. 3

Cestec - Perú Realice los siguientes pasos: 1. Ir a inicio. 2. Clic en formato condicional.

Clic aquí

3. Administrar reglas.

Clic aquí

4. En la siguiente ventana crearas las condiciones indicadas al principio:

Excel 2016

Pág. 4

Cestec - Perú Clic aquí

5. Dar valores de formato

Cambie estos valores

Luego clic en formato para dar el color

Excel 2016

Pág. 5

Cestec - Perú Clic en esta pestaña

Elija el color ROJO

6. La primera regla quedaría de la siguiente manera:

7. Haga los mismos procedimientos para definir el de color azul.

Excel 2016

Pág. 6

Cestec - Perú

REGLAS DE CELDAS Las opciones de administración de reglas podrían ser reemplazados mediante esta opción de reglas de celdas.

Clic aquí

Pídele explicación al docente de turno

Excel 2016

Pág. 7

Cestec - Perú BARRA DE DATOS Otra opción que nos da el Formato Condicional es poder destacar gráficamente cada valor de la celda seleccionada, en este caso tenemos 3 opciones para poder destacar gráficamente el valor de una celda. Los pasos a seguir son:

Clic aquí

Excel 2016

Pág. 8

Cestec - Perú ESCALAS DE COLOR Como se puede observar en el grafio al escoger la opción Escala de colores y escoger un tipo, cada celda seleccionada se colorea de distintos colores, en este caso las celdas de color más oscuro son las celdas con valores más altos y las celdas con colores más claros son las celdas que tienen los valores más bajos.

Clic aquí

Excel 2016

Pág. 9

Cestec - Perú CONJUNTO DE ICONOS Como se puede observar en el grafio al escoger la opción Conjunto de Iconos y Seleccionar un tipo de icono predeterminado nos aparece estos iconos en cada.

Clic aquí

Excel 2016

Pág. 10

Cestec - Perú EJERCICIO 2

Aplicar esta condicional de iconos

Excel 2016

Pág. 11

Cestec - Perú EJERCICIO 3 REGLAS PARA NOTAS:  

Notas =11 de color AZUL

REGLAS PARA SITUACION  

APROBADO de color AZUL DESAPROBADO de color ROJO

REGLAS PARA ESTADO:    

BECADO de color VERDE SEMIBECADO de color NARANJA PROMOVIDO de color AMARILLO REPITENTE de color ROJO

Aplique condicional de barras e iconos según Ud. desea:

No 01 02 03 04 05 06 07 08 09

DATOS PERSONALES Bohorquez Mamani, María Cano Sarmiento, Walter Carrasco Gomez, Luisa Flores García, Luis Luciano Torres, Jaime Vargas Linares, Patricia Verástegui Liendo, Jessica Zambrano Velez, Richard Zavala Salinas, Clever

Excel 2016

N1

N2

N3

PROMEDIO

SITUACION

ESTADO

14 19 08 09 16 16 17 14 03

19 08 07 11 20 13 18 12 14

16 14 14 12 19 15 19 10 08

16.33 13.67 09.67 10.67 18.33 14.67 18.00 12.00 08.33

Aprobado Aprobado Desaprobado Desaprobado Aprobado Aprobado Aprobado Aprobado Desaprobado

Promovido Promovido Repitente Repitente Becado Promovido Semibecado Promovido Repitente

Pág. 12

Cestec - Perú EJERCICIO 4 Funciones a usar:        

Promedio del alumno Promedio de grupo Promedio de materia

=promedio( ) =promedio( ) =promedio( )

Número de alumnos Total de Aprobados Total de Desaprobados otros

=contara( ) =contar.si( ) =contar.si( )

Formato condicional:     

Promedio de Alumno  Relleno ROJO si es menor de 13 Promedio de Alumno  Relleno AZUL si es mayor o igual a 13. Total de Aprobados  Conjunto de iconos, 3 banderas. Total de Desaprobados  Conjunto de iconos, 4 clasificaciones. Promedio de materia  Conjunto de iconos, 3 semáforos sin marco

Excel 2016

Pág. 13

Cestec - Perú



CAPÍTULO II: FUNCIONES LOGICAS …………..….…………………………………………………………………………

FUNCIONES LOGICAS

Objetivos:  

Conocer funciones lógicas. Aplicar funciones lógicas

FUNCIONES LOGICAS FUNCION SI

Excel 2016

Pág. 14

Cestec - Perú Devuelve un valor si la expresión evaluada es VERDADERA y el otro valor si dicha expresión evaluada resulta ser FALSO. Sintaxis =SI (Prueba Lógica; Valor_V; Valor_F) Dónde:  Prueba Lógica: Es cualquier valor o expresión que pueda evaluarse como



VERDADERA o FALSO. Valor_V: Es la acción a realizar o valor a devolver si el argumento



Prueba_Lógica es VERDADERO. Valor_F: Es la acción a realizar o valor a devolver si el argumento Prueba_Lógica es FALSO.

EJERCICIO 5 Obtener la situación del alumno, si sus promedios son mayores o iguales a 17 será BECADO, en caso contrario NO BECADO.

EJERCICIO 6 Obtener en una hoja de cálculo los mensajes:  En la columna CONDICIÓN indicar “INGRESO” o “NO INGRESO”,



sabiendo que el puntaje mínimo de ingreso es 100. En la columna ESTADO indicar “MENOR DE EDAD” o “MAYOR DE EDAD”, sabiendo que la mayoría de edad se obtiene a partir de los 18 años.

Postulantes de examen de admisión Excel 2016

Pág. 15

Cestec - Perú APELLIDOS NOMBRES

EDAD

PUNTAJE

Soria Cáceres Norma Morales loza Diana Sánchez Solís Juan Salas Ugarte Lyz Vilca Rodríguez Jorge Robles Suarez Sthefany López Ulloa José

17 21 16 19 22 23 17

180.25 80.00 45.00 280.50 152.75 99.50 310.00

CONDICION

ESTADO

EJERCICIO 7 Se pide saber si es CARO o BARATO hospedarse en un hotel sabiendo su precio por noche. HOTELES POR CATEGORÍAS

PRECIOS POR NOCHE

HOTEL A

100

HOTEL B

70

HOTEL C

75

HOTEL D

65

Barato cuando los precios son menores de:

75

¿Es caro o barato pasar la noche en el hotel?

EJERCICIO 8 Se pide completar la tabla sabiendo que la columna "MENSAJE" debe decir:  "SI" en caso de que el Stock sea menor a 20.  "NO” en otro caso.

Control de Almacén ARTICULOS Computadoras laptops Tablet Notebook Keyboard Mouse Pantallas

Excel 2016

PRECIO S/. 2,200.00 S/. 2,700.00 S/. 450.00 S/. 1,500.00 S/. 34.00 S/. 18.00 S/. 580.00

STOCK 50 40 76 12 45 3 19

MENSAJE

Pág. 16

Cestec - Perú

EJERCICIO 9 El IMPUESTO se determinará según el SUELDO de la persona:  Si sueldo más de S/. 1,500.00, Se le descontará el 15% a la cantidad 

excedida a S/. 1,500.00. Caso contrario mostrará, Cero.

NOMBRES Brando García Shirley Mendez Darlyng Pichilingue Steven Camasca Sharelli Pichilingue John Aguilera Jazmin Aguilera Litzze Romero Alexey Romero

CATEGORI A C B C A B D A C B

SUELDO S/. 2,000.00 S/. 1,500.00 S/. 2,500.00 S/. 1,500.00 S/. 1,000.00 S/. 800.00 S/. 1,500.00 S/. 2,000.00 S/. 1,200.00

IMPUESTO

FUNCIÓN SI ANIDADA Se utiliza cuando existen 2 o más condiciones.

EJERCICIO 10 La empresa “Atlas s.a.”, requiere de una aplicación en Excel que determine la fecha de cancelación de documentos según el tipo de venta.

TIPO DE VENTA CR1 CR2 CR3

  

DNI

Fecha Emisión

123456 78 123456 79 123456 80

10/06/20 16 10/06/20 16 12/06/20 16

Excel 2016

FECHA DE CANCELACION Dentro de 90 días (3 meses) Dentro de 180 días (6 meses) Dentro de 365 días (1 año)

Tipo Vent a CR1 CR2 CR3

Fecha Cancelaci ón

Emplea do Pedro Rios Pedro Rios Pedro Rios

Cliente

Monto

Mosna rivas Jose cerna

S/. 6,524.00 S/. 9,562.00 S/. 7,500.00

Lucas rios

Pág. 17

Cestec - Perú 123456 81 123456 82 123456 83 123456 84

12/06/20 16 12/06/20 16 15/06/20 16 15/06/20 16

CR1

Pedro Rios Pedro Rios Pedro Rios Pedro Rios

CR2 CR3 CR1

Sonia brigida Pedro infante Kelly silva Soto perez

S/. 5,600.00 S/. 4,200.00 S/. 1,523.00 S/. 8,964.00

EJERCICIO 11 CREDITO DEBITO CONTADO ARTICULO ARTICULO 1 ARTICULO 2 ARTICULO 3 ARTICULO 4 ARTICULO 5 ARTICULO 6 ARTICULO 7

10% 5% 0%

Data Center

PRECIO DE FORMA DE LISTA PAGO S/. 5,000.00 CREDITO S/. 3,200.00 DEBITO S/. 1,800.00 CONTADO S/. 1,250.00 CREDITO S/. 2,300.00 CREDITO S/. 4,800.00 DEBITO S/. 4,400.00 CONTADO

Ruc 20124598758 PRECIO DE VENTA

EJERCICIO 12 Se pide, que en la columna RESULTADO debe aparecer: -

Goles diferentes Goles iguales

Excel 2016

 "Ganó " + "Nombre del país ganador".  "Empate".

Pág. 18

Cestec - Perú EJERCICIO 13 Comisión se calcula en función a las ventas. Si las ventas son menores o iguales a mínimo no hay comisión. Si las ventas son mayores que mínimo se da una comisión de 1.5% de las ventas. Si las ventas son mayores que el objetivo se da una comisión de 3.5% y extras de 6.25% de las ventas respectivamente. REPRESENTANTES

VENTAS

Aparicio Baena Conde Dubar Estébez Fernàndez Gómez Peña Ventas mínimas Ventas objetivo Porcentaje ventas mínimas Porcentaje comisión Porcentaje extra

COMISIÓN

1490 1520 2090 1220 830 2090 1480 1590 100 150 1.50% 3.50% 6.25%

EJERCICIO 14

Excel 2016

Pág. 19

Cestec - Perú EJERCICIO 15 El pago semanal se calcula en base a la siguiente tabla: Técnico Empleado Obrero

500 300 200

Los descuentos se calculan en base a la siguiente tabla:

Excel 2016

Pág. 20

Cestec - Perú Técnico Emplead o Obrero

EsSalud 3% 2%

Rentas 2.5% 1.5%

1%

0.5%

Excel 2016

Pág. 21

Excel 2016

3500 3200 3000 1800 1000

9 5 14

SEXO F M F F M M M M M F M M M F

2% 3% 3% 5% 10%

200 300 250 200 200

DSEX FEMENINA MASCULINO FEMENINA FEMENINA MASCULINO MASCULINO MASCULINO MASCULINO MASCULINO FEMENINA MASCULINO MASCULINO MASCULINO FEMENINA

BASICO NCENT BONIF

NOMBRE NH Milagros 2 Pedro 0 Icela 1 Azucena 3 Gradimy 5 Jose 1 Alberto 3 Jimmy 7 Lorenzo 8 Rosa 2 Miguel 4 Simon 2 Abrahan 5 Carla 2 TOTAL VARONES TOTAL MUJERES TOTAL GENERAL

GERENTE INGENIERO CONTADOR TECNCO OBRERO

CARGO 3% 4% 4% 6% 8%

CARGO Tecnico Obrero Contador Contador Gerente Tecnico Tecnico Obrero Ingeniero Ingeniero Obrero Tecnico Contador Tecnico

P PROD

BASICO 1800 1000 3000 3000 3500 1800 1800 1000 3200 3200 1000 1800 3000 1800

INECET 90 100 90 90 70 90 90 100 96 96 100 90 90 90

BONIF 200 200 250 250 200 200 200 200 300 300 200 200 250 200

P. PROD 108 80 120 120 105 108 108 80 128 128 80 108 120 108

=0 =250 =400 =500

CONDICION AFAN SI NH = 0 ==> AFAM SI NH AFAM SI NH AFAM SI NH >5 ==> AFAM

AFAM 250 0 250 250 400 250 250 500 500 250 400 250 400 250

T.CAMBIO ESSALUD 144 80 240 240 280 144 144 80 256 256 80 144 240 144

3.5 IMP 269.28 151.8 408.1 408.1 470.25 269.28 269.28 206.8 464.64 437.14 195.8 269.28 424.6 269.28

SBRUTO S/. 2448 1380 3710 3710 4275 2448 2448 1880 4224 3974 1780 2448 3860 2448

S.NETO $ 2034.72 1148.2 3061.9 3061.9 3524.75 2034.72 2034.72 1593.2 3503.36 3280.86 1504.2 2034.72 3195.4 2034.72

Essalud =8% IMP =(Suma de Basico,Incent,Bonif,P.Prod y Afam)*11% Sbruto =Suma de Basico,Incent,Bonif,P.Prod y Afam SNeto =Sbruto - Essalud - Imp Dsex :M =Masculino. F =Femenio

Formulas

Cestec - Perú

Pág.

22

Cestec - Perú FUNCIÓN O Devuelve Verdadero si algunos de los argumentos son Verdadero; devuelve Falso si todos los argumentos son Falso. La función O se emplea dentro de la Función SI. Sintaxis O (Valor_Logico1; Valor_Logico2)

EJERCICIO 17 En la siguiente relación de Empleados, se desea obtener el básico según las categorías donde laboran cada empleado.  Obtendrán un básico de 1000 aquellos que ostenten una Categoría “A” o  

“B”. Los empleados que ostenten otra Categoría obtendrán un Básico de 800. Las bonificaciones serán por turno.

APELLIDO S

CATEGORIA

TOORES LUGO VALDIVIA GOZAY CERNA JULCA SILVA PEREZ CADILLO RIOS

B A C A B C B C D C

TIEMPO SERVICI O 4 10 15 7 5 2 8 12 6 5

HIJOS

TURNO

E. CIVIL

0 1 3 0 2 5 2 0 3 4

T N N T N M T M T N

S C V S D V C S D C

BASICO

BONIF

Solución: =SI(O(B2= “A”;B2= “B”);1000; 800) Para calcular bonificación use la función SI

EJERCICIO 18 Obtener la SITUACION del estudiante según estos criterios:

Excel 2016

Pág. 23

Cestec - Perú  

APROBADO cuando él % de inasistencias sea menor o igual a 25 o la nota obtenida sea mayor o igual a 10.5. En otro caso sea DESAPROBADO % DE INASISTENCIA 25 30 12 10 45 8

ALUMNOS ALUMNOS ALUMNOS ALUMNOS ALUMNOS ALUMNOS ALUMNOS

1 2 3 4 5 6

NOT A 09 17 11 15 19 05

SITUACION

FUNCIÓN Y Devuelve Verdadero si todos los argumentos son Verdadero; devuelve Falso si uno o más argumentos son Falso. La función Y se emplea dentro de la Función SI. Sintaxis Y (Valor_Logico1; Valor_Logico2)

EJERCICIO 19 Se otorga una Bonificación especial del 7.5% del Básico para aquellos Empleados que laboren en el Turno Noche(N) y además sean Casados (C); los Empleados que no cumplan con estos requisitos se les otorga el 2% del Básico. APELLIDO S

CATEGORIA

TOORES LUGO VALDIVIA GOZAY CERNA JULCA SILVA PEREZ CADILLO

B A C A B C B C D

TIEMPO SERVICI O 4 10 15 7 5 2 8 12 6

HIJOS

TURNO

E. CIVIL

BASICO

0 1 3 0 2 5 2 0 3

T N N T N M T M T

S C V S D V C S D

1000 1000 800 1000 1000 800 1000 800 800

BONIF

Solución: =SI(Y(E4= “N”;F4= “C”);7.5%*G4; 2%*G4)

EJERCICIO 20 Para aprobar un curso los alumnos deben considerar dos variables:

Excel 2016

Pág. 24

Cestec - Perú  

Que él % de inasistencias sea menor o igual a 25, Y Que la nota obtenida sea mayor o igual a 10.5 % DE INASISTENCIA 25 30 12 10

ALUMNOS ALUMNOS ALUMNOS ALUMNOS ALUMNOS

1 2 3 4

NOT A 09 17 11 15

SITUACION

FUNCION ANIDADAS (SI(Y(O))) Se usa cuando se tiene que realizar cálculos usando varios criterios lógicos.

EJERCICIO 21 El instituto “INFOTECH”, requiere solucionar el problema de calcular el precio real, aplicando un descuento según el curso y turno.

=SI(Y(C4="PHP";D4="M");E4*14%;SI(Y(C4="PHP";D4="T");E4*9%;SI(Y(C4="JAVA";O(D4="M";D4 ="N"));E4*11%;SI(Y(C4="JAVA";D4="T");E4*8%;SI(Y(C4="ORACLE";D4="M");E4*15%;0)))))

EJERCICIO 22 1. Calcular promedio, Situación, Estado. 2. SITUACIÓN debe considerar lo siguiente:  Aprobado cuando el promedio o aplazado es mayor o igual a 13.

Excel 2016

Pág. 25

Cestec - Perú  

Aplazados cuando el promedio es mayor o igual a 10 y menor de 13. Desaprobado cuando el promedio es mayor o igual a 00 y menor de 10, pero si el aplazado es mayor que 13 será aprobado, en otro caso desaprobado.

3. ESTADO debe considerar lo siguiente:  Si Promedio del alumno es mayor que todas las notas debe mostrar BECADO, en otro caso considerar PROMOVIDO a todos los que aprobaron y REPITENTES a todos los desaprobados ya sea en promedio 

o aplazados. Nota mínima probatoria es 13.

No

DATOS PERSONALES

N1

N2

N3

01

Bohorquez Mamani, María

14

19

16

02

Cano Sarmiento, Walter

19

08

14

03

Carrasco Gomez, Luisa

08

07

14

04

Flores García, Luis

09

11

12

05

Luciano Torres, Jaime

16

20

19

06

Vargas Linares, Patricia

16

13

15

07

Verástegui Liendo, Jessica

17

18

19

08

Zambrano Velez, Richard

14

12

10

09

Zavala Salinas, Clever

03

14

08

Excel 2016

PROMEDIO

NOTA APLAZADO

SITUACION

ESTADO

15

11

Pág. 26

Cestec - Perú



CAPÍTULO III: FUNCION BUSCAR …………..….…………………………………………………………………………

FUNCION BUSCAR

Objetivos:  

Conocer funciones Buscar Aplicar funciones Buscar

FUNCIÓN BUSCAR Esta función busca un valor en un rango de una columna o una fila o una matriz. Para lo cual solo debes indicar el valor a buscar, dónde quieres que busque y de dónde obtendrás el resultado. =BUSCAR (valor_buscado, [vector_de_comparacion], [vector_resultado])

EJERCICIO 23

Excel 2016

Pág. 27

Cestec - Perú Buscar el Nombre y el precio del producto en base al N° de boleta, usando la función BUSCAR.

_________________________________________________________________ _________________________________________________________________ _______________________________________________________

EJERCICIO 24 Buscar el Apellido paterno, Apellido materno, Nombres y Grado, mediante el código, usando la función BUSCAR.

CODIGO

960005

APELLIDO PATERNO APELLIDO MATERNO NOMBRES GRADO

Excel 2016

Pág. 28

NOMBRES

SECCION

CATEGORIA

AGUILAR ANCAYA ANGULO ARAUCO AVALOS AGUILAR ALVAREZ AGUILAR ALIAGA ALIAGA BETT ALARCON ANDRADE ANDRADE ALVAREZ ALVAREZ ALVA AREVALO AYESTA AYESTA AYESTA BECERRA ATENCIO

APELLIDO MATERNO

GRADO

960001 960002 960003 960004 960005 960006 960007 960008 960009 960010 960011 960012 960013 960014 960015 960016 960017 960018 960019 960020 960021 960022 960023

APELLIDO PATERNO

NIVEL

CODIGO

PROCEDENCIA

Cestec - Perú NUMERO RECIBO

BENAVIDES CARBAJAL RIOS BOCANEGRA ROLDAN GARCIA BERTRAN MORON CORONEL CORONEL QUEA CALIXTO CORRALES CORRALES GARCIA GARCIA URRUNAGA ESCALANTE VARGAS VARGAS VARGAS MAZ DIAZ

JHONATTAN FRANCISCO ALBERTO VLADIMIR GUILIANA VERONICA MANUEL DAVID ZOILA LUIS DAVID GUILLERMO MARIELA ANTONIO LUIS PAOLA GUILLERMO ANDRES LUIS LESLIE CLAUDIA PAOLA JOSE

PNP PNP PNP CIV PNP CIV CIV PNP CIV CIV CIV PNP PNP PNP PNP PNP CIV PNP PNP PNP PNP PNP CIV

S P P S P P P P P P P P S S P P S P S P P S P

2 5 6 1 4 1 1 2 3 1 4 1 4 1 6 2 2 1 3 5 5 4 1

B A C A C B B B C B B C B C A B B D C B A A D

B1 A A E A E E A E E E A B2 B2 B1 B1 E A A A A B1 E

6901 6902 6903 6904 6905 6906 6907 6908 6909 6910 6911 6913 6914 6915 6916 6917 6918 6919 6921 6922 6923 6924 6925

_________________________________________________________________ _________________________________________________________________ ____________________________________________________

FUNCION “BUSCARH” Busca en la primera fila de la tabla o matriz de valores y devuelve el resultado en la misma columna desde una fila especificada.

EJERCICIO 25 Duplique la tabla del ejercicio anterior y proceda a resolver usando la función BUSCARH, (pida explicación al docente si se requiere).

Excel 2016

Pág. 29

Cestec - Perú

_________________________________________________________________ _________________________________________________________________ _________________________________________________________________ _______________________________________________

FUNCION “BUSCARV” Busca un valor en la primera columna de la izquierda y devuelve el valor en la misma fila desde una columna especificada. Tiene el mismo efecto que la función anterior salvo que en esta función realiza la búsqueda por columnas.

EJERCICIO 26 Se pide obtener la columna observación según la nota de EXCEL del alumno. -

Si la nota de EXCEL es de 0 a 10.49; mostrar "DESAPROBADO" Si la nota de EXCEL es de 10.5 a 20; mostrar "APROBADO"

Excel 2016

Pág. 30

Cestec - Perú

EJERCICIO 27 Se pide calcular BONI1, el cual se determinará según el BASICO de la persona. SI SU BASICO ES    

0 a 500  501 a 1000 1001 a 1500 1501 a más

MOSTRARÁ

20% del básico  15% del básico  12% del básico  10% del básico

EJERCICIO 28 Se pide calcular la columna OBSERVACION, el cual se determinará según la nota de EXCEL del alumno:     

Si la nota de EXCEL es de 00 a 05; mostrar "PONLE MAS DEDICACION" Si la nota de EXCEL es de 06 a 10; mostrar "TU PUEDES MEJORAR ESA NOTA" Si la nota de EXCEL es de 11 a 14; mostrar "CON LAS JUSTAS" Si la nota de EXCEL es de 15 a 17; mostrar "NO SEAS CONFORMISTA" Si la nota de EXCEL es de 18 a 20; mostrar "FELICITACIONES, PERO NO TE DESCUIDES"

Excel 2016

Pág. 31

Cestec - Perú

EJERCICIO 29

Se pide BONI1 se determinará según el BASICO de la persona: Si su BASICO es 0 a 500 501 a 1000 1001 a 1500 1501 a más

   

Mostrará 150.00 100.00 80.00 50.00

EJERCICIO 30 Crear en una hoja la siguiente relación Nro 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010

Apellidos Cáceres Alanoca Paredes Gutiérrez Pinto Lujerio Castillo Pumacachua Quispe Cutipa

Nombres Percibal Judith Carla Fanny Julio Richard Luis Alexander Luisa Lázaro

Excel 2016

Edad 25 32 28 35 45 29 30 42 41 43

Ciudad Tacna Arequipa Tacna Tacna Arequipa Lima Tacna Lima Arequipa Tacna

Carrera Profesional Ingeniería de Sistemas Derecho Arquitectura Ingeniería Civil Ingeniería de Sistemas Arquitectura Derecho Derecho Ingeniería Civil Ingeniería Civil

Aporta. S/. 101.0 S/. 105.0 S/. 139.0 S/. 90.0 S/. 91.0 S/. 122.0 S/. 146.0 S/. 110.0 S/. 142.0 S/. 125.0

A.F.P. Profuturo Integra Integra Unión Profuturo Unión Profuturo Unión Integra Integra

Pág. 32

Cestec - Perú 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050

Paredes Pinto Castillo Alanoca Vega Quispe Cáceres Paredes Carrillo Quispe Gutiérrez Pinto Túpala Luna Cueva Quispe Paredes Ramos Yánez Gómez Cáceres Alanoca Paredes Gutiérrez Pinto Lujerio Castillo Pumacachua Quispe Cutipa Paredes Pinto Castillo Alanoca Vega Quispe Cáceres Paredes Carrillo Cáceres

Pablo Cesar Paula Carlos Gerardo Ronaldo Elizabeth Daniel Juana Juliana Sabrina Galo Carmen Muriel Hilda Karina Corina Luz Marina Pablo Hilario Carolina Paola Walter Sandra Maritza Maria Lucho Andres Vanessa Ivan Gustavo Alfredo Juan Renato Edson Eduardo Karen Danny Pedro Rocio

26 35 32 29 32 36 46 35 28 23 26 21 36 41 25 29 26 29 30 39 35 32 31 30 28 26 45 26 51 32 45 36 35 45 32 26 28 29 25 24

Tacna Arequipa Tacna Lima Arequipa Puno Tacna Arequipa Lima Lima Tacna Tacna Arequipa Lima Tacna Puno Lima Tacna Lima Arequipa Tacna Arequipa Tacna Tacna Arequipa Lima Tacna Lima Arequipa Tacna Tacna Arequipa Tacna Lima Arequipa Puno Tacna Arequipa Lima Tacna

Arquitectura Derecho Ingeniería de Sistemas Derecho Arquitectura Ingeniería Civil Ingeniería Civil Ingeniería de Sistemas Derecho Ingeniería Civil Arquitectura Ingeniería de Sistemas Derecho Derecho Ingeniería Civil Ingeniería Civil Derecho Ingeniería de Sistemas Ingeniería Civil Derecho Ingeniería de Sistemas Derecho Arquitectura Ingeniería Civil Ingeniería de Sistemas Arquitectura Derecho Derecho Ingeniería Civil Ingeniería Civil Arquitectura Derecho Ingeniería de Sistemas Derecho Arquitectura Ingeniería Civil Ingeniería Civil Ingeniería de Sistemas Derecho Ingeniería de Sistemas

S/. S/. S/. S/. S/. S/. S/. S/. S/. S/. S/. S/. S/. S/. S/. S/. S/. S/. S/. S/. S/. S/. S/. S/. S/. S/. S/. S/. S/. S/. S/. S/. S/. S/. S/. S/. S/. S/. S/. S/.

94.0 148.0 119.0 106.0 90.0 133.0 96.0 127.0 137.0 97.0 150.0 90.0 125.0 118.0 102.0 110.0 144.0 132.0 142.0 139.0 129.0 114.0 124.0 147.0 145.0 112.0 99.0 126.0 90.0 108.0 140.0 145.0 118.0 129.0 113.0 132.0 109.0 115.0 148.0 115.0

Unión Integra Profuturo Profuturo Unión Integra Integra Unión Profuturo Integra Integra Profuturo Unión Unión Profuturo Integra Unión Integra Profuturo Unión Profuturo Integra Integra Unión Profuturo Unión Profuturo Unión Integra Integra Unión Integra Profuturo Profuturo Unión Integra Integra Unión Profuturo Profuturo

De la Hoja Relación deberá basarse para llenar estos resúmenes utilizando la función adecuada

Excel 2016

Pág. 33

Cestec - Perú

1. Encontrar los datos del afiliado. Digite el NRO. Use función BUSCAR o BUSCARH o BUSCARV

A.F.P.

Ciudad

Cantidad

Total Aportado

Cantidad

Total Aportado

Cantidad

Total Aportado

Lima Tacna Puno Arequipa Suma Total:

3. Cuantos afiliados existen por Ciudad

5. Cuantos afiliados existen por Edades

Total Aportado

Integra Unión Profuturo Suma Total:

2. Cuantos afiliados existen por A.F.P.

4. Cuantos afiliados existen por Carrera Profesional

Cantidad

Carrera Profesional Ingeniería de Sistemas Ingeniería Civil Arquitectura Derecho Suma Total:

Edad 40 Suma Total:

Excel 2016

Pág. 34

Cestec - Perú



CAPÍTULO IV: TABLAS Y GRAFICOS DINAMICOS …………..….…………………………………………………………………………

TABLAS Y GRAFICOS DINAMICOS

Objetivos:  

Conocer el concepto de tablas dinámicas Conocer el concepto de gráficos dinámicos

TABLAS DINÁMICAS Frecuentemente nos sentimos ante una verdadera selva de piedras en presencia de la maraña de números y datos que visualizamos en algunas hojas de cálculo.

Excel 2016

Pág. 35

Cestec - Perú En tales ocasiones, sólo pensamos en una cosa: en cómo resumir la información de modo tal que podamos visualizar fácilmente los datos que nos interesan. Pues bien, vamos a ofrecer la solución para todos nuestros problemas: las milagrosas tablas dinámicas. Se trata de tablas interactivas que resumen rápidamente grandes cantidades de datos mediante formatos y métodos de cálculo específicos. Esa dinámica obedece a la posibilidad de desplazar los encabezados de línea y de columna en torno al área principal de datos para obtener diferentes visiones de los datos fuente, así como a la actualización automática obtenida a medida que se modifican los datos.

EJERCICIO 31 En la siguiente base de datos tenemos una colección de datos de todos los productos que ofrece el comercial.

Comercial "El Indio" … somos más para Ud. N° BOLETA 1245 1246 1247 1248 1249 1250 1251 1252 1253 1254 1255 1256 1257

PRODUCTO Coca cola 1 litro Inca cola 1 litro Pepsi 1 litro Cola real 1 litro Coca cola 2 litro Inca cola 2 litro Coca real 3 litro Agua cielo Agua vida Generade Frugos plus Cerveza pilsen Cerveza cusqueña

VENTAS MENSUALES MES

CANTID AD

PRECIO UNIT.

Febrero Abril Enero Marzo Abril Marzo Junio Enero Febrero Junio Mayo Mayo Abril

45 23 20 23 25 65 23 12 74 25 70 120 98

1.5 1.5 1.7 1 4 4 3 1 1.5 3.5 2.5 5 4

TOTAL

Total = Cantidad * Precio

PASOS PARA CREAR TABLAS DINAMICAS.

Excel 2016

Pág. 36

Cestec - Perú Veamos cómo podemos crear una tabla dinámica a partir de unos datos que ya tenemos. 1. Para ello vamos a la pestaña INSERTAR. 2. hacemos clic en “TABLA DINÁMICA”. Clic Aquí

3. Aparece el cuadro de diálogo de creación de tablas dinámicas. Desde aquí podremos indicar el lugar donde se encuentran los datos que queremos analizar y el lugar donde queremos ubicarla.

Datos que queremos analizar

Lugar donde queremos ubicar la Tabla Dinámica

Observación 1: Podríamos crear una conexión con otra aplicación para obtener los datos desde otra fuente diferente a Excel. Observación 2: En el caso de seleccionar la opción Selecciona una tabla o rango debemos seleccionar todas las celdas que vayan a participar, incluyendo las cabeceras.

4. Pulsamos ACEPTAR para seguir.

Seguidamente tendremos el siguiente RESULTADO:

Excel 2016

Pág. 37

Cestec - Perú

Desde luego, tendremos que realizar consultas a la base de datos, por ejemplo: a) Mostrar a todos los productos vendidos por meses y con su respectivo total.

Activar los casilleros

APLICAR FILTROS A UNA TABLA DINÁMICA

Excel 2016

Pág. 38

Cestec - Perú Los campos principales en el panel y los rótulos en la tabla están acompañados, en su parte derecha, de una flecha indicando una lista desplegable. Por ejemplo: 

Si pulsamos sobre la flecha del rótulo Rótulos de columna nos aparece una lista.



Clic Aquí

Para cerrar este cuadro debemos pulsar en Aceptar o sobre Cancelar para cerrar y dejarlo como estaba.

Aplicando el filtro a varios campos podemos formar condiciones de filtrado más complejas.

EJERCICIOS 32 Aplicar filtros a la tabla dinámica -

Mostrar a los productos con N° de boleta 1248, 1253 y 1257 Mostrar a los productos vendidos en marzo, abril y junio. Mostrar a los productos con totales mayores o iguales 25.

GRÁFICOS DINÁMICOS

Excel 2016

Pág. 39

Cestec - Perú Una vez terminada la Tabla Dinámica se puede dar paso a la realización del gráfico dinámico. Para ello es necesario seguir estos pasos: 1. Para ello vamos a la pestaña INSERTAR. 2. hacemos clic en “GRAFICO DINÁMICO”. Clic Aquí

Observación: Si haces clic en la pestaña, podrás elegir entre las dos opciones. … es necesario tener en cuenta que se quiere crear.

3. aparecerá la siguiente ventana, donde podrás escoger el tipo de gráfico. Para el ejemplo se escogerá el de tipo columnas.

4. clic en ACEPTAR

EJERCICIOS 33 Creación de gráficos dinámicos

Excel 2016

Pág. 40

Cestec - Perú Utilizando la base de datos del ejercicio N° 1 y tabla dinámica:

Crear el siguiente grafico dinámico:

EJERCICIO 34

Excel 2016

Pág. 41

Cestec - Perú

Crear las tablas y gráficos dinámicos para las siguientes preguntas: -

Listar a todas medicinas de la empresa Genfar.

-

Listar a todas medicinas de la empresa Genfar.

-

Listar a todas medicinas de la empresa Genfar, así como su cantidad vendida.

-

Listar a todas medicinas de la empresa Marfam, así como su cantidad vendida.

-

Listar a todas medicinas de la empresa Genfar, así como su importe.

-

Listar a todas medicinas de la empresa Marfarm, así como su importe.

-

Listar a todas medicinas de la empresa Marfarm, así como su cantidad.

-

Listar a todas medicinas de la empresa Marfarm, así como su cantidad.

-

Listar a todas empresas, por sucursal y las medicinas que vende.

Excel 2016

Pág. 42

Cestec - Perú

 CAPÍTULO V: VALIDACION DE DATOS …………..….…………………………………………………………………………

VALIDACION DE DATOS

Objetivos:  

Conocer los conceptos de validación de datos Aplicar validación de datos.

Excel 2016

Pág. 43

Cestec - Perú VALIDACION DE DATOS La validación de datos es sumamente útil cuando deseas compartir un libro o archivo de Ms Excel con otros miembros de la organización y deseas que los datos que se escriban en él sean exactos y coherentes. Puedes aplicar la validación de datos para los siguientes casos:   

específico. Restringir las horas que se encuentren fuera de un período de tiempo



Restringir los datos a elementos predefinidos de una lista. Restringir los números que se encuentren fuera de un intervalo específico. Restringir las fechas que se encuentren fuera de un período de tiempo

 

específico. Limitar la cantidad de caracteres de texto. Validar datos según fórmulas o valores de otras celdas.

PASOS La herramienta de Validación de datos se encuentra en: 1. Ficha Datos 2. Clic en VALIDACIÓN DE DATOS.

Clic aquí

3. Al dar clic en VALIDACIÓN DE DATOS se visualiza el cuadro de diálogo validación de datos que contiene tres pestañas:

Excel 2016

Pág. 44

Cestec - Perú

1. 2. 3.

Configuración. Mensaje de entrada. Mensaje de error.

En la pestaña de Configuración se configura el criterio de validación en la opción Permitir. En la pestaña Mensaje de entrada se configura el mensaje de entrada que alertará al usuario sobre el tipo de datos que puede ingresar. La pestaña Mensaje de error permite que el usuario personalice el mensaje de error en el caso de que el usuario haya ingresado datos fuera del criterio de validación.

EJERCICIO 35

Excel 2016

Pág. 45

Cestec - Perú

VALIDACIONES: 

Introducir en la celda CLIENTE solo letras, pero que la cantidad no exceda de 

15 caracteres. Introducir en la celda RUC solo números y de 11 dígitos. Sus 2 primeros 

números sea 20 o 10 (Persona jurídica o persona natural respectivamente). Introducir en la celda DIRECCION letras y números, pero que la cantidad no

 

DEBITO y CONTADO. La celda CIUDAD debe ser una lista que muestre HUARAZ, LIMA, TRUJILLO,



exceda de 18 caracteres. Introducir en la celda FECHA solo a las fechas mayores o iguales a Hoy. La celda FORMA DE PAGO debe ser una lista que muestre CREDITO,

CHIMBOTE, CHICLAYO, OTROS.

Excel 2016

Pág. 46

Cestec - Perú 

Introducir en la ITEM 7 caracteres, los 3 primeros debe ser "UNI", los 4 

siguientes un numero entre 100 y 999. Introducir en la celda DESCRIPCION letras y números, pero que la cantidad



no exceda de 25 caracteres. Introducir en la celda CANTIDAD solo números enteros y positivos. Introducir en la celda CANTIDAD números enteros o con decimales mayores 

o iguales a 0.10. Introducir en la celda CANTIDAD números enteros o con decimales mayores



o iguales a 0.10.

Además, cuando el usuario seleccione la celda puedes alertarlo al configurar un mensaje de entrada. De igual forma, si el usuario trata de ingresar un texto mayor a los 11 caracteres, puedes configurar para que Excel emita un mensaje de error.

Solucionario de la validación personalizada:

CLIENTE =Y(ESTEXTO(D5); LARGO(D5) =100 ;VALOR(DERECHA(C10;4))