ÍNDICE CAPÍTULO I: FORMA FORMATO TO CONDICIONAL.............................................................2 FORMATO FORMATO CONDICIONAL.......... CONDICIONAL.................... ..................... ..................... ..................... ..................... ............................................ .................................. 3 ADMINISTRAR REGLAS..................... ............................... ..................... ..................... ..................... ..................... ...................... ............3 3 REGLAS DE CELDAS..................... ................................ ..................... ..................... ...................... ..................... ..................... ............... ....7 7 BARRA DE DATOS..................... ............................... ..................... ..................... ..................... ..................... ..................... .................... .........8 8 ESCALAS DE COLOR...................... ................................ ..................... ..................... ............................................. ................................... 9 CONJUNTO DE ICONOS..................... ............................... ..................... ..................... ..................... ..................... ....................10 ..........10
CAPÍTULO II: FUNCIONES LOGICAS...............................................................14 FUNCIONES LOGICAS.......... 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 “BUSCAR!..................... ................................ ..................... ..................... ..................... ..................... ......................29 ...........29 FUNCION “BUSCAR"!..................... ............................... ..................... ...................... ..................... ................................30 ......................30
CAPÍTULO I": TABLAS Y GRAFICOS DINAMICOS.........................................#4 TABLAS TABLAS DIN$MICAS.......... DIN$MICAS..................... ..................... ..................... ..................... ..................... ..................... ..................... .............................. ................... 35 GR$FICOS DIN$MICOS.......... DIN$MICOS..................... ..................... ..................... ..................... ..................... ..................... ................................... ......................... 39
CAPÍTULO ": "ALIDACION "ALIDACION DE DATOS....................................... DATOS................................................ .................. ............ ...42 42
"ALIDACION DE DATOS.......... DATOS..................... ..................... ..................... ..................... ..................... ............................................. .................................. 43
CAPÍTULO C APÍTULO "I: MA M ATEMA TEMATICA TICA FINANCIERA......................................................4%
NOMENCLATURA NOMENCLATURA B$SICA.......... B$SICA..................... ..................... ..................... ..................... ..................... ..................... ..................... .....................48 ..........48 RESUMEN DE FORMULAS......... FORMULAS.................... ..................... ..................... ...................... ..................... ......................................... ............................... 49 INTER&S SIMPLE........... SIMPLE..................... ..................... ..................... ..................... ..................... ..................... ..................... ..................... ..................... .............. .... 50 MONTO......... MONTO.................... ..................... ..................... ..................... ..................... ..................... ..................... ..................... ..................... ................................ ..................... 55 DESCUENTOS.......... DESCUENTOS.................... ..................... ..................... ..................... ..................... ..................... ...................... ..................... ..................... ....................58 .........58 FUNCIONES FINANCIERAS.......... FINANCIERAS..................... ...................... ..................... ..................... ..................... ..................... ............................. .................. 62 FUNCIÓN "F..................... ............................... ..................... ..................... ..................... ............................................... .................................... 62 FUNCIÓN "A..................... ............................... ..................... ..................... ..................... ............................................... .................................... 66 FUNCIÓN PAGO..................... ................................ ...................... ..................... ................................................... ......................................... 68 FUNCIÓN TASA TASA...................... ................................ ..................... ..................... ..................... .......................................... ............................... 70 FUNCIÓN NPER..................... ............................... ..................... ...................... ..................... ..................... ..................... ..................... ........... 74 FUNCIÓN TIR..................... ............................... ..................... ..................... ..................... .............................................. ................................... 75
CAPÍTULO "II: PROTECCIÓN DE LIBROS Y OJAS DE C$LCULO.............%' PROTECCIÓN DE LIBROS Y OJAS DE C$LCULO......................................................79 C$LCULO......................................................79
Cestec - Perú
CAPÍTULO I: I: FORMATO CONDICIONAL ...
FORMATO CONDICIONAL
Objetivos: • •
Conocer el manejo de formatos condicionales. !licar formatos condicionales condicionales a "na celda.
Excel 2016
Pág. 2
Cestec - Perú
CAPÍTULO I: I: FORMATO CONDICIONAL ...
FORMATO CONDICIONAL
Objetivos: • •
Conocer el manejo de formatos condicionales. !licar formatos condicionales condicionales a "na celda.
Excel 2016
Pág. 2
Cestec - Perú FORMATO FORMAT O CONDICIONAL CONDIC IONAL #l formato condicional !ermite modificar el as!ecto de determinadas celdas$ de ac"erdo al c"m!limiento o no de re%las l&%icas. '"( )til c"ando se trata de trabajar con condicionales. *o!orta +asta 64 condiciones.
ADMINISTRAR REGLAS #sta o!ci&n nos !ermite seleccionar todos los formatos condicionales creados en n"estro libro de #,cel.
EJERCICIO 1 Crear la si%"iente tabla ( a!licar las si%"ientes re%las: • •
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ú -ealice los si%"ientes !asos: 1. r a ini inicio. 2. Clic Clic en en form formato ato condic condicion ional. al.
Clic aquí
3. dmi dmini nist stra rarr re% re%la las. s.
Clic aquí
4. #n la si%"iente si%"iente ventana ventana crearas crearas las condicio condiciones nes indicada indicadass al !rinci!io: !rinci!io:
Excel 2016
Pág. 4
Cestec - Perú Clic aquí
5. /ar valores de formato
Ca#&ie ests 'alres
Lueg clic e! "r#at $ara %ar el clr
Excel 2016
Pág. 5
Cestec - Perú Clic e! esta $esta,a
Eli(a el clr )*+*
6. a !rimera re%la "edara de la si%"iente manera:
7. a%a los mismos !rocedimientos !ara definir el de color a"l.
Excel 2016
Pág. 6
Cestec - Perú
REGLAS DE CELDAS as o!ciones de administraci&n de re%las !odran ser reem!laados mediante esta o!ci&n de re%las de celdas.
Clic aquí
Pídele explicación al docente de turno
Excel 2016
Pág. 7
Cestec - Perú BARRA DE DATOS Otra o!ci&n "e nos da el ormato Condicional es !oder destacar %rficamente cada valor de la celda seleccionada$ en este caso tenemos 3 o!ciones !ara !oder destacar %rficamente el valor de "na celda. os !asos a se%"ir son:
Clic aquí
Excel 2016
Pág. 8
Cestec - Perú ESCALAS DE COLOR Como se !"ede observar en el %rafio al esco%er la o!ci&n #scala de colores ( esco%er "n ti!o$ cada celda seleccionada se colorea de distintos colores$ en este caso las celdas de color ms osc"ro son las celdas con valores ms altos ( las celdas con colores ms claros son las celdas "e tienen los valores ms bajos.
Clic aquí
Excel 2016
Pág. 9
Cestec - Perú CONJUNTO DE ICONOS Como se !"ede observar en el %rafio al esco%er la o!ci&n Conj"nto de conos ( *eleccionar "n ti!o de icono !redeterminado nos a!arece estos iconos en cada.
Clic aquí
Excel 2016
Pág. 10
Cestec - Perú EJERCICIO 2
!licar esta condicional de iconos
Excel 2016
Pág. 11
Cestec - Perú EJERCICIO # -#* - O*: • •
Notas <11 de color ROJO Notas >11 de color AZUL
-#* - *;CO • •
A!RO"ADO de color AZUL D#$A!RO"ADO de color ROJO
-#* - #*/O: • • • •
"#%ADO de color &#RD# $#'("#%ADO de color NARANJA !RO'O&(DO de color A'AR(LLO R#!()#N)# de color ROJO
Apli*ue condicional de +arras e iconos seg,n Ud. desea-
N
DATOS PERSONALES
N1
N2
N# PROMEDIO
SITUACION
ESTADO
01 02 03 04 05 06 07 08 09
aime ?ar%as inares$ atricia ?erste%"i iendo$ >essica @ambrano ?ele$ -ic+ard @avala *alinas$ Clever
14 1* +' +* 16 16 1% 14 +#
1* +' +% 11 2+ 1# 1' 12 14
16 14 14 12 1* 1, 1* 1+ +'
!robado !robado /esa!robado /esa!robado !robado !robado !robado !robado /esa!robado
romovido romovido -e!itente -e!itente
Excel 2016
16.## 1#.6% +*.6% 1+.6% 1'.## 14.6% 1'.++ 12.++ +'.##
Pág. 12
Cestec - Perú EJERCICIO 4 "nciones a "sar: • • •
romedio del al"mno romedio de %r"!o romedio de materia
A!romedioB A!romedioB A!romedioB
)mero de al"mnos otal de !robados otal de /esa!robados otros
AcontaraB Acontar.siB Acontar.siB
• • • • •
ormato condicional: • • • • •
romedio de l"mno -elleno -O>O si es menor de 13 romedio de l"mno -elleno @; si es ma(or o i%"al a 13. otal de !robados Conj"nto de iconos$ 3 banderas. otal de /esa!robados Conj"nto de iconos$ 4 clasificaciones. romedio de materia Conj"nto de iconos$ 3 semforos sin marco
Excel 2016
Pág. 13
Cestec - Perú
CAPÍTULO II: FUNCIONES LOGICAS ...
FUNCIONES LOGICAS
Objetivos: • •
Conocer f"nciones l&%icas. !licar f"nciones l&%icas
FUNCIONES LOGICAS FUNCION SI
Excel 2016
Pág. 14
Cestec - Perú /ev"elve "n valor si la e,!resi&n eval"ada es ?#-//#- ( el otro valor si dic+a e,!resi&n eval"ada res"lta ser *O. $intais
=SI (Prueba Lógica; Valor_V; Valor_! /&nde: Prueba Lógica- #s cual*uier /alor o epresi0n *ue pueda e/aluarse como
&#RDAD#RA o AL$O. Valor_V - #s la acci0n a reali2ar o /alor a de/ol/er si el argumento
!rue+a3L0gica es &#RDAD#RO. Valor_ - #s la acci0n a reali2ar o /alor a de/ol/er si el argumento !rue+a3L0gica es AL$O.
EJERCICIO , Obtener la sit"aci&n del al"mno$ si s"s !romedios son ma(ores o i%"ales a 17 ser <#C/O$ en caso contrario O <#C/O.
EJERCICIO 6 Obtener en "na +oja de clc"lo los mensajes: #n la columna "#$%I"I&$ indicar 4 I$')S# 5 o 4 $# I$')S# 56
sa+iendo *ue el punta7e m8nimo de ingreso es 199. #n la columna )S*+%# indicar 4 ,)$# %) )%+%5 o 4 ,+-# %)
)%+%56 sa+iendo *ue la mayor8a de edad se o+tiene a partir de los 1: a;os.
Pstula!tes %e exa#e! %e a%#isi! Excel 2016
Pág. 15
Cestec - Perú APELLIDOS NOMBRES
EDAD
PUNTAJE
$oria %ceres Norma 'orales lo2a Diana $nc@e2 $ol8s Juan $alas Ugarte Ly2 &ilca Rodr8gue2 Jorge Ro+les $uare2 $t@efany L0pe2 Ulloa JosE
1= 1 1 1C 1=
1:9.? :9.99 B?.99 :9.?9 1?.=? CC.?9 19.99
CONDICION
ESTADO
EJERCICIO % *e !ide saber si es C-O o <-O +os!edarse en "n +otel sabiendo s" !recio !or noc+e.
.#*)L)S P# "+*)'#/+S FO)#L A
P)"I#S P# $#".)
) caro o barao aar la oce e el oel
199
FO)#L "
=9
FO)#L %
=?
FO)#L D
?
"arato cuando los precios son menores de-
=?
EJERCICIO ' *e !ide com!letar la tabla sabiendo "e la col"mna D'#*>#D debe decir: DSID en caso de "e el *tocE sea menor a 20. DNOF en otro caso.
C!trl %e l#ac/! ARTICULOS Com!"tadoras la!to!s ablet otebooE He(board 'o"se antallas
Excel 2016
PRECIO *G. 2$200.00 *G. 2$700.00 *G. 450.00 *G. 1$500.00 *G. 34.00 *G. 18.00 *G. 580.00
STOC50 40 76 12 45 3 19
MENSAJE
Pág. 16
Cestec - Perú
EJERCICIO *
#l ';#*O se determinar se%)n el *;#/O de la !ersona: *i s"eldo ms de *G. 1$500.00$ *e le descontar el 15I a la cantidad
e,cedida a *G. 1$500.00. Caso contrario mostrar$ Cero.
$#,)S
"+*)'#I +
"rando Garc8a $@irley 'ende2 Darlyng !ic@ilingue $te/en %amasca $@arelli !ic@ilingue Jo@n Aguilera Ja2min Aguilera Lit22e Romero Aleey Romero
% " % A " D A % "
S)L%# $H. $H. $H. $H. $H. $H. $H. $H. $H.
I,P)S*#
6999.99 16?99.99 6?99.99 16?99.99 16999.99 :99.99 16?99.99 6999.99 1699.99
FUNCIÓN SI ANIDADA *e "tilia c"ando e,isten 2 o ms condiciones.
EJERCICIO 1+ a em!resa Jtlas s.a.F$ re"iere de "na a!licaci&n en #,cel "e determine la fec+a de cancelaci&n de doc"mentos se%)n el ti!o de venta. TIPO DE "ENTA C-1 C-2 C-3
DNI 123456 78 123456 79 123456 80
Fecha Emisión 100620 16 100620 16 120620 16
Excel 2016
FECA DE CANCELACION /entro de 90 das B3 meses /entro de 180 das B6 meses /entro de 365 das B1 aKo Tipo Vent a C)1 C)2 C)3
Fecha Cancelaci ón
Emplea do Pe%r )is Pe%r )is Pe%r )is
Cliente s!a ri'as +se cer!a Lucas ris
Monto . 6524.00 . 9562.00 . 7500.00
Pág. 17
Cestec - Perú 123456 81 123456 82 123456 83 123456 84
120620 16 120620 16 150620 16 150620 16
C)1
Pe%r )is Pe%r )is Pe%r )is Pe%r )is
C)2 C)3 C)1
!ia &rigi%a Pe%r i!"a!te ell sil'a t $ere
. 5600.00 . 4200.00 . 1523.00 . 8964.00
EJERCICIO 11 %R#D()O D#"()O %ON)ADO
+*I"L# AR)(%ULO 1 AR)(%ULO AR)(%ULO AR)(%ULO B AR)(%ULO ? AR)(%ULO AR)(%ULO =
19I ?I 9I
P)"I# %) LIS*+
Data Center Ruc 20124598758
#,+ %) P+'#
$H. $H. $H.
?6999.99 699.99 16:99.99
%R#D()O D#"()O %ON)ADO
$H. $H. $H. $H.
16?9.99 699.99 B6:99.99 B6B99.99
%R#D()O %R#D()O D#"()O %ON)ADO
P)"I# %) V)$*+
EJERCICIO 12 *e !ide$ "e en la col"mna -#*;/O debe a!arecer: L L
oles diferentes oles i%"ales
Excel 2016
Dan& D M Dombre D#m!ateD.
del !as %anadorD.
Pág. 18
Cestec - Perú EJERCICIO 1# Comisi&n se calc"la en f"nci&n a las ventas. *i las ventas son menores o i%"ales a mnimo no +a( comisi&n. *i las ventas son ma(ores "e mnimo se da "na comisi&n de 1.5I de las ventas. *i las ventas son ma(ores "e el objetivo se da "na comisi&n de 3.5I ( e,tras de 6.25I de las ventas res!ectivamente. REPRESENTANTES
"ENTAS
!aricio
COMISIÓN
1490 1520 2090 1220 830 2090 1480 1590 199 1?9 1.?9I .?9I .?I
EJERCICIO 14
Excel 2016
Pág. 19
Cestec - Perú EJERCICIO 1, #l pago semanal se calcula en +ase a la siguiente ta+la-
Ncnico #m!leado Obrero
500 300 200
Los descuentos se calculan en +ase a la siguiente ta+la-
Excel 2016
Pág. 20
Cestec - Perú Ncnico #m!lead o Obrero
#s*al"d 3I 2I
-entas 2.5I 1.5I
1I
0.5I
Excel 2016
Pág. 21
Cestec - Perú D # a # a " " % % r r P . P . P " P i " ! i ! t i ! $ e t ! ! e c # # e ! e c < ! % @ i c l u < c s a . i a s s s a ! e E l - i e % u c % a t s u a > a # r u 8 # & @ s @ u @ a % @ l F u l u @ t t # e x P u r a r : e s s s & < E =
5 . 3
* C . ; 0 0 0 0 5 0 2 0 4 5 @ @ @ @ : < < < < : < * A A C @ A @ A = @ @ @ @ @ @ : * C 0 3 5 @ 5 @ B @ B A ? ? ? ? : : : :
I 2 2 6 6 2 2 2 2 9 9 5 * ; 7 . 1 . . 7 . 7 . 7 . 2 . 3 . 8 . 4 . 7 . . 8 . 2 . 7 4 4 3 4 5 E 4 1 4 3 0 4 4 4 6 6 2 3 3 9 0 8 3 9 3 3 0 : . 0 2 1 1 0 3 0 3 5 3 0 2 0 2 5 1 5 3 2 3 5 1 0 2 1 3 0 2 . 0 0 5 8 8 0 4 4 0 8 0 8 8 0 * 8 1 1 7 4 4 8 2 7 8 4 6 4 ; 4 3 4 H 2 1 7 3 7 3 2 4 4 2 4 2 8 1 2 4 9 3 7 1 4 2 8 3 4 2 ) 8 8 8 8 8 4 8 6 . 1 . 1 . 5 . 4 . 8 . 8 2 2 6 2 2 2 . 2 . 6 . 4 . . . 1 . 5 . 1 8 8 9 0 9 9 4 7 9 5 0 0 0 2 9 6 6 2 6 3 1 6 4 9 6 6 7 1 4 4 2 4 2 2 4 4 2 2 P = 4 0 0 0 4 4 6 6 4 0 4 0 4 8 4 4 0 5 5 0 4 4 4 H L 4 1 8 4 2 2 2 1 1 8 2 2 8 1 2 1 E 0 5 0 0 5 0 5 0 0 0 5 0 5 0 0 0 0 0 5 0 0 0 5 0 0 0 5 2 2 2 4 2 2 5 5 2 4 2 4 2 < = 8 0 0 0 5 8 8 0 8 8 0 8 0 8 2 2 0 0 0 2 2 0 2 0 * 0 ) 1 8 1 1 1 1 1 8 1 1 8 1 1 1 P . P 0 0 0 0 0 0 5 0 5 0 0 0 0 0 0 0 0 0 0 0 5 0 0 < 0 2 2 2 2 0 2 2 2 2 0 3 0 3 0 2 2 2 2 : * 0 0 0 0 0 0 0 0 0 0 ; 0 6 6 0 0 E 9 0 1 9 9 7 9 9 1 9 9 0 1 9 9 9 C E : 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 * 0 0 0 0 8 8 0 8 0 8 8 5 2 2 0 C 1 1 3 3 3 1 1 1 3 3 1 1 3 1
> > > > 3 4 4 > 6 8 = * ) P P 0 0 0 0 0 0 2 0 3 5 2 0 2 0 2 < : * ; > > > > 2 3 3 > 5 0 : 1 E C : 0 0 0 0 * 0 0 C 5 0 2 0 0 0 8 0 0 3 3 3 1 1 * ) * E ) E * = * * ; ) ; C E : E : E ) ) ) : : C E : * E C C ; *
Excel 2016
r r r r t e r e e % i i r * i c i c r i c r % c % c ! a a i a i ! ! ! e t t r ! ! e e ! ! e e r t r r ) c & ! ! e ! c c g & c & g e e e c e e e ! ! * ; C ; C ; * C C ; ; * * * * * * * * * * : : : : : : : : : G : : : E L L L L L L : L L L : H : : H H H H H : H H H : = : C C C C E C C C E C E E C E E < E < E < E < E < 9 * < < < < < G E 2 0 1 3 5 1 3 7 8 2 4 2 5 2 E ? : : * ) E s a ! K ) l a ! r t # r ! g e ! J L e i r a c u a u % e e # e a a l a l r l ; g # r a r s s & i * i % r l # & e e a i ) C * i c + : P + L ;
Pág. 22
Cestec - Perú FUNCIÓN O /ev"elve ?erdadero si al%"nos de los ar%"mentos son ?erdaderoP dev"elve also si todos los ar%"mentos son also. a f"nci&n O se em!lea dentro de la "nci&n *. $intais
O ("/0L315 "/0L32)
EJERCICIO 1% #n la si%"iente relaci&n de #m!leados$ se desea obtener el bsico se%)n las cate%oras donde laboran cada em!leado. Obtendrn "n bsico de 1000 a"ellos "e ostenten "na Cate%ora JF o
J
APELLIDO S
CATEGORIA
OO-#* ;O ?/? O@Q C#- >;C *? #-#@ C/O -O*
< C < C < C / C
TIEMPO SER"ICI O 4 10 15 7 5 2 8 12 6 5
IJOS
TURNO
E. CI"IL
0 1 3 0 2 5 2 0 3 4
' '
* C ? * / ? C * / C
BASICO
BONIF
Solució $(O" 4A5K" 4"5K1999K :99 !ara calcular +onificaci0n use la funci0n $(
EJERCICIO 1' Obtener la *;CO del est"diante se%)n estos criterios:
Excel 2016
Pág. 23
Cestec - Perú
-O</O c"ando Nl I de inasistencias sea menor o i%"al a 25 o la nota obtenida sea ma(or o i%"al a 10.5. #n otro caso sea /#*-O</O DE INASISTENCIA 25 30 12 10 45 8
ALUMNOS LH:* LH:* LH:* LH:* LH:* LH:*
1 2 3 4 5 6
NOT A 09 17 11 15 19 05
SITUACION
FUNCIÓN Y /ev"elve ?erdadero si todos los ar%"mentos son ?erdaderoP dev"elve also si "no o ms ar%"mentos son also. a f"nci&n Q se em!lea dentro de la "nci&n *. $intais
- (Valor_Logico1; Valor_Logico2!
EJERCICIO 1* *e otor%a "na
CATEGORIA
OO-#* ;O ?/? O@Q C#- >;C *? #-#@ C/O
< C < C < C /
TIEMPO SER"ICI O 4 10 15 7 5 2 8 12 6
IJOS
TURNO
E. CI"IL
BASICO
0 1 3 0 2 5 2 0 3
' '
* C ? * / ? C * /
1000 1000 800 1000 1000 800 1000 800 800
BONIF
Solució $(M#B 4N5KB 4%5K=.?IGBK IGB
EJERCICIO 2+ ara a!robar "n c"rso los al"mnos deben considerar dos variables:
Excel 2016
Pág. 24
Cestec - Perú
R"e Nl I de inasistencias sea menor o i%"al a 25$ Q R"e la nota o+tenida sea mayor o igual a 19.? > =E :;E:C 25 30 12 10
LH:* LH:* LH:* LH:* LH:*
1 2 3 4
:*; 09 17 11 15
;HC*:
FUNCION ANIDADAS (SI(Y(O))) *e "sa c"ando se tiene "e realiar clc"los "sando varios criterios l&%icos.
EJERCICIO 21 #l instit"to JO#CF$ re"iere sol"cionar el !roblema de calc"lar el !recio real$ a!licando "n desc"ento se%)n el c"rso ( t"rno.
SI(Y(C47PP75D47M7)5E481495SI(Y(C47PP75D47T7)5E48*95SI(Y(C47JA"A75O(D47M75D4 7N7))5E481195SI(Y(C47JA"A75D47T7)5E48'95SI(Y(C47ORACLE75D47M7)5E481,95+)))))
EJERCICIO 22
1. Calc"lar !romedio$ *it"aci&n$ #stado. 2. *;CS debe considerar lo si%"iente: !robado c"ando el !romedio o a!laado es ma(or o i%"al a 13. •
Excel 2016
Pág. 25
Cestec - Perú • •
!laados c"ando el !romedio es ma(or o i%"al a 10 ( menor de 13. /esa!robado c"ando el !romedio es ma(or o i%"al a 00 ( menor de 10$ !ero si el a!laado es ma(or "e 13 ser a!robado$ en otro caso desa!robado.
#. #*/O debe considerar lo si%"iente: *i romedio del al"mno es ma(or "e todas las notas debe mostrar •
<#C/O$ en otro caso considerar -O'O?/O a todos los "e a!robaron ( -###* a todos los desa!robados (a sea en !romedio •
o a!laados. ota mnima !robatoria es 13.
N
DATOS PERSONALES
N1
N2
N#
01 02 03 04 05 06 07 08 09
aime ?ar%as inares$ atricia ?erste%"i iendo$ >essica @ambrano ?ele$ -ic+ard @avala *alinas$ Clever
14 1* +' +* 16 16 1% 14 +#
1* +' +% 11 2+ 1# 1' 12 14
16 14 14 12 1* 1, 1* 1+ +'
Excel 2016
PROMEDIO
NOTA APLAADO
SITUACION ESTADO
1,
11
Pág. 26
Cestec - Perú
CAPÍTULO III: FUNCION BUSCAR ...
FUNCION BUSCAR
Objetivos: • •
Conocer f"nciones <"scar !licar f"nciones <"scar
FUNCIÓN BUSCAR #sta f"nci&n b"sca "n valor en "n ran%o de "na col"mna o "na fila o "na matri. ara lo c"al solo debes indicar el valor a b"scar$ d&nde "ieres "e b"s"e ( de d&nde obtendrs el res"ltado.
! S"+ ( /alor3+uscado: /ector3de3comparacionP : /ector3resultadoP
EJERCICIO 2#
Excel 2016
Pág. 27
Cestec - Perú <"scar el ombre ( el !recio del !rod"cto en base al T de boleta$ "sando la f"nci&n <;*C-.
333333333333333333333333 333333333333333333333333333333333333 333333333333333333333333 333333333333333333333333 33333333333333333 33333 333333333333333333333333 333333333333333333333333333333333333 333333333333333333333333 333333333333333333333333 33333333333333333 33333 333333333333333333333333 333333333333333333333333333333333333 333333333333333333333333 3333333333333333333 3333333
EJERCICIO 24 <"scar el !ellido !aterno$ !ellido materno$ ombres ( rado$ mediante el c&di%o$ "sando la f"nci&n <;*C-. CODIGO
960005
APELLIDO PAT PATERNO ERNO APELLIDO MATERNO MATERNO NOMBRES GRADO
Excel 2016
Pág. 28
Cestec - Perú
CODIGO
960001 960002 960003 960004 960005 960006 960007 96000 960009 960010 960011 960012 960013 960014 960015 960016 960017 96001 960019 960020 960021 960022 960023
APELLIDO
APELLIDO
PATERNO PATERNO
MATERNO MATERNO
AGUILAR ANCAYA ANGULO ARAUCO AV AVALOS AG A GUILAR AL A LVAREZ AGUILAR AL ALIAGA ALIAGA BE T T ALARCON ANDRADE ANDRADE ALVAREZ AL ALVAREZ ALVA AREVALO AYESTA AYESTA AY AYESTA BECERRA ATENCIO
BENAVIDES CARBAJAL RIOS BOCANEGRA ROLDAN GARCIA BERTRAN MORON CORONEL CORONEL !UEA CALI"TO CORRALES CORRALES GARCIA GARCIA URRUNAGA ESCALANTE VARGAS VARGAS VARGAS MAZ DIAZ
NOMBRES
A I C N E D E C O R P
JHONATTAN FRANCISCO ALBERTO VL 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
L E V I N
O D A R G
N O I C C E S
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
A I R O NUMERO G E RECIBO T A C
B1 A A E A E E A E E E A B2 B2 B1 B1 B1 E A A A A B1 B1 E
6901 6902 6903 6904 6905 6906 6907 690 6909 6910 6911 6913 6914 6915 6916 6917 691 6919 6921 6922 6923 6924 6925
333333333333333333333333 333333333333333333333333333333333333 3333333333333333333333333 3333333333333333333333333 3333333333333333 3333 333333333333333333333333 333333333333333333333333333333333333 3333333333333333333333333 3333333333333333333333333 3333333333333333 3333 333333333333333333333333 333333333333333333333333333333333333 3333333333333333333333333 3333333333333333 333
FUNCION “BUSCAR! <"sca en la !rimera fila de la tabla o matri de valores ( dev"elve el res"ltado en la misma col"mna desde "na fila es!ecificada.
EJERCICIO 2, /"!li"e la tabla del ejercicio anterior ( !roceda a resolver "sando la f"nci&n <;*C-$ B!ida e,!licaci&n al docente si se re"iere.
Excel 2016
Pág. 29
Cestec - Perú
UUUUUUUUUUUUUUUUUUUUUUUU UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUU UUUUUUUUUUUUUUUUUUUUUUUU UUUUUUUUUUUUUUUUUUUUUUUU UUUUUUUUUUUUUUUUU UUUUU UUUUUUUUUUUUUUUUUUUUUUUU UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUU UUUUUUUUUUUUUUUUUUUUUUUU UUUUUUUUUUUUUUUUUUUUUUUU UUUUUUUUUUUUUUUUU UUUUU UUUUUUUUUUUUUUUUUUUUUUUU UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUU UUUUUUUUUUUUUUUUUUUUUUUU UUUUUUUUUUUUUUUUUUUUUUUU UUUUUUUUUUUUUUUUU UUUUU UUUUUUUUUUUUUUUUUUUUUUUU UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUU UUUUUUUUUUUUUUUUUUUUUUU UUUUUUUUUUU
FUNCION “BUSCAR"! <"sca "n valor en la !rimera col"mna de la i"ierda ( dev"elve el valor en la misma fila desde "na col"mna es!ecificada. iene el mismo efecto "e la f"nci&n anterior salvo "e en esta f"nci&n realia la b)s"eda !or col"mnas.
EJERCICIO 26 *e !ide obtener la col"mna observaci&n se%)n la nota de #VC# del al"mno. # #
$i la nota de #Q%#L es de 9 a 19.BCK mostrar D#$A!RO"ADO $i la nota de #Q%#L es de 19.? a 9K mostrar A!RO"ADO
Excel 2016
Pág. 30
Cestec - Perú
EJERCICIO 2% *e !ide calc"lar
9 a ?99 ?91 a 1999 1991 a 1?99 1?91 a ms
,#S*+
9I del +sico 1?I del +sico 1I del +sico 19I del +sico
EJERCICIO 2' *e !ide calc"lar la col"mna O<*#-?CO$ el c"al se determinar se%)n la nota de #VC# del al"mno: • • • • •
*i la nota de #VC# es de 00 a 05P mostrar DO# '* /#/CCOD *i la nota de #VC# es de 06 a 10P mostrar D; ;#/#* '#>O-- #* OD OD *i la nota de #VC# es de 11 a 14P mostrar DCO * >;**D *i la nota de #VC# es de 15 a 17P mostrar DO *#* COO-'*D COO-'*D *i la nota de #VC# es de 18 a 20P mostrar D#CCO#*$ D#CCO#*$ #-O O # /#*C;/#*D
Excel 2016
Pág. 31
Cestec - Perú
EJERCICIO 2*
*e !ide
'ostrar
9 a ?99 ?91 a 1999 1991 a 1?99 1?91 a ms
1?9.99 199.99 :9.99 ?9.99
EJERCICIO #+ Crear en "na +oja la si%"iente relaci&n Nro 1001 1002 100" 100( 1005 100 100 100& 100' 1010
Apellidos Cáceres Alanoca Paredes )uti*rre+ Pinto u3erio Castillo Pumacac!ua 7uis$e Cuti$a
Nombres Percibal udit! Carla ,annulio 4ic!ard uis Ale6ander uisa á+aro
Excel 2016
Edad 25 "2 2& "5 (5 2' "0 (2 (1 ("
Ciudad Tacna Are#ui$a Tacna Tacna Are#ui$a ima Tacna ima Are#ui$a Tacna
Carrera Profesional Ingeniería de Sistemas %erec!o Ar#uitectura Ingeniería Ciil Ingeniería de Sistemas Ar#uitectura %erec!o %erec!o Ingeniería Ciil Ingeniería Ciil
Aporta. S/. 101.0 S/. 105.0 S/. 1"'.0 S/. '0.0 S/. '1.0 S/. 122.0 S/. 1(.0 S/. 110.0 S/. 1(2.0 S/. 125.0
A.F.P. Profuturo Integra Integra nin Profuturo nin Profuturo nin Integra Integra
Pág. 32
Cestec - Perú 1011 1012 101" 101( 1015 101 101 101& 101' 1020 1021 1022 102" 102( 1025 102 102 102& 102' 10"0 10"1 10"2 10"" 10"( 10"5 10" 10" 10"& 10"' 10(0 10(1
Paredes Pinto Castillo Alanoca 8ega 7uis$e Cáceres Paredes Carrillo 7uis$e )uti*rre+ Pinto T:$ala una Cuea 7uis$e Paredes 4amos >áne+ )me+ Cáceres Alanoca Paredes )uti*rre+ Pinto u3erio Castillo Pumacac!ua 7uis$e Cuti$a Paredes
Pablo Cesar Paula Carlos )erardo 4onaldo 9li+abet! %aniel uana uliana Sabrina )alo Carmen ;uriel
2 "5 "2 2' "2 " ( "5 2& 2" 2 21 " (1 25 2' 2 2' "0 "' "5 "2 "1 "0 2& 2 (5 2 51 "2 (5
Tacna Are#ui$a Tacna ima Are#ui$a Puno Tacna Are#ui$a ima ima Tacna Tacna Are#ui$a ima Tacna Puno ima Tacna ima Are#ui$a Tacna Are#ui$a Tacna Tacna Are#ui$a ima Tacna ima Are#ui$a Tacna Tacna
Ar#uitectura %erec!o Ingeniería de Sistemas %erec!o Ar#uitectura Ingeniería Ciil Ingeniería Ciil Ingeniería de Sistemas %erec!o Ingeniería Ciil Ar#uitectura Ingeniería de Sistemas %erec!o %erec!o Ingeniería Ciil Ingeniería Ciil %erec!o Ingeniería de Sistemas Ingeniería Ciil %erec!o Ingeniería de Sistemas %erec!o Ar#uitectura Ingeniería Ciil Ingeniería de Sistemas Ar#uitectura %erec!o %erec!o Ingeniería Ciil Ingeniería Ciil Ar#uitectura
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/.
'(.0 1(&.0 11'.0 10.0 '0.0 1"".0 '.0 12.0 1".0 '.0 150.0 '0.0 125.0 11&.0 102.0 110.0 1((.0 1"2.0 1(2.0 1"'.0 12'.0 11(.0 12(.0 1(.0 1(5.0 112.0 ''.0 12.0 '0.0 10&.0 1(0.0
nin Integra Profuturo Profuturo nin Integra Integra nin Profuturo Integra Integra Profuturo nin nin Profuturo Integra nin Integra Profuturo nin Profuturo Integra Integra nin Profuturo nin Profuturo nin Integra Integra nin
10(2 Pinto 10(" Castillo
Alfredo uan
" "5
Are#ui$a Tacna
%erec!o Ingeniería de Sistemas
S/. S/.
1(5.0 11&.0
Integra Profuturo
10(( Alanoca
4enato
(5
ima
%erec!o
S/.
12'.0
Profuturo
10(5 8ega
9dson
"2
Are#ui$a
Ar#uitectura
S/.
11".0
nin
10( 7uis$e
9duardo
2
Puno
Ingeniería Ciil
S/.
1"2.0
Integra
10( Cáceres
=aren
2&
Tacna
Ingeniería Ciil
S/.
10'.0
Integra
10(& Paredes
%ann-
2'
Are#ui$a
Ingeniería de Sistemas
S/.
115.0
nin
10(' Carrillo
Pedro
25
ima
%erec!o
S/.
1(&.0
Profuturo
1050 Cáceres
4ocio
2(
Tacna
Ingeniería de Sistemas
S/.
115.0
Profuturo
/e la oja -elaci&n deber basarse !ara llenar estos res)menes "tiliando la f"nci&n adec"ada
Excel 2016
Pág. 33
Cestec - Perú
1. #ncontrar los datos del afiliado. Digite el NRO. Use funci0n "U$%AR o "U$%ARF o "U$%AR&
A.F.P.
C3==
T</ A>0<=
C;<3==
T</ A>0<=
C;<3==
T</ A>0<=
L3 T; P; A0?3> S T</:
. %uantos afiliados eisten por %iudad
?. %uantos afiliados eisten por #dades
C;<3==
I;
. %uantos afiliados eisten por A..!.
B. %uantos afiliados eisten por %arrera !rofesional
C;<3==
T</ A>0<=
C00?0 P0?3;/ I;?;3?0 =? S3
E== #+ 4+ S T</:
Excel 2016
Pág. 34
Cestec - Perú
CAPÍTULO I": TABLAS Y GRAFICOS DINAMICOS ...
TABLAS Y GRAFICOS DINAMICOS
Objetivos: • •
Conocer el conce!to de tablas dinmicas Conocer el conce!to de %rficos dinmicos
TABLAS DIN$MICAS rec"entemente nos sentimos ante "na verdadera selva de !iedras en !resencia de la maraKa de n)meros ( datos "e vis"aliamos en al%"nas +ojas de clc"lo.
Excel 2016
Pág. 35
Cestec - Perú #n tales ocasiones$ s&lo !ensamos en "na cosa: en c&mo res"mir la informaci&n de modo tal "e !odamos vis"aliar fcilmente los datos "e nos interesan. "es bien$ vamos a ofrecer la sol"ci&n !ara todos n"estros !roblemas: las mila%rosas tablas dinmicas. *e trata de tablas interactivas "e res"men r!idamente %randes cantidades de datos mediante formatos ( mNtodos de clc"lo es!ecficos. #sa dinmica obedece a la !osibilidad de des!laar los encabeados de lnea ( de col"mna en torno al rea !rinci!al de datos !ara obtener diferentes visiones de los datos f"ente$ as como a la act"aliaci&n a"tomtica obtenida a medida "e se modifican los datos.
EJERCICIO #1 #n la si%"iente base de datos tenemos "na colecci&n de datos de todos los !rod"ctos "e ofrece el comercial.
Come"cial #El Indio# s#s #Ms $ara H%. N$ %OLETA 1245 1246 1247 124 1249 1250 1251 1252 1253 1254 1255 1256 1257
P&ODUCTO C$%& %$(& 1 ()*+$ I/%& %$(& 1 ()*+$ P,) 1 ()*+$ C$(& +,&( 1 ()*+$ C$%& %$(& 2 ()*+$ I/%& %$(& 2 ()*+$ C$%& +,&( 3 ()*+$ A& %),($ A& )& G,/,+&, F+$ ( C,+,& )(,/ C,+,& %8,&
VENTAS MENSUALES MES
F,-+,+$ A-+)( E/,+$ M&+$ A-+)( M&+$ J/)$ E/,+$ F,-+,+$ J/)$ M&$ M&$ A-+)(
CANTID AD 45 23 20 23 25 65 23 12 74 25 70 120 9
P&ECIO UNIT'
TOTAL
1.5 1.5 1.7 1 4 4 3 1 1.5 3.5 2.5 5 4
)otal %antidad !recio
PASOS PARA CREAR TABLAS DINAMICAS.
Excel 2016
Pág. 36
Cestec - Perú ?eamos c&mo !odemos crear "na tabla dinmica a !artir de "nos datos "e (a tenemos. 1. ara ello vamos a la !estaKa I$S)*+ . 2. +acemos clic en J*+L+ %I$,I"+F. Clic quí
3. !arece el c"adro de dilo%o de creaci&n de tablas dinmicas. /esde a" !odremos indicar el lugar o
aali?ar ( el lugar o ubicarla .
Datos ()e ()e"emos anali*a"
L)ga" donde ()e"emos )+ica" la Ta+la Dinámic
Observaci&n 1: !odr8amos crear una conei0n con otra aplicaci0n para o+tener los datos desde otra fuente diferente a #cel.
Observaci&n 2: #n el caso de seleccionar la opci0n $elecciona una ta+la o rango de+emos seleccionar todas las celdas *ue /ayan a participar6 incluyendo las ca+eceras.
4. "lsamos +")P*+ !ara se%"ir.
*e%"idamente tendremos el si%"iente )SL*+%# :
Excel 2016
Pág. 37
Cestec - Perú
/esde l"e%o$ tendremos "e realiar ;/< a la base de datos$ !or ejem!lo: a 'ostrar a todos los productos /endidos por meses y con su respecti/o total.
s r e l l i s a c s l r a ' i t c .
APLICAR FILTROS A UNA TABLA DIN$MICA
Excel 2016
Pág. 38
Cestec - Perú os cam!os !rinci!ales en el !anel ( los r&t"los en la tabla estn acom!aKados$ en s" !arte derec+a$ de "na flec+a indicando "na lista des!le%able. or ejem!lo: •
*i !"lsamos sobre la flec+a del r&t"lo -&t"los de col"mna nos a!arece "na lista.
•
Clic quí
ara cerrar este c"adro debemos !"lsar en A?><0 o sobre Cancelar !ara cerrar ( dejarlo como estaba.
!licando el filtro a varios cam!os !odemos formar condiciones de filtrado ms com!lejas.
EJERCICIOS #2 Aplicar filtros a la ta+la dinmica # # #
'ostrar a los productos con NS de +oleta 1B:6 1? y 1?= 'ostrar a los productos /endidos en mar2o6 a+ril y 7unio. 'ostrar a los productos con totales mayores o iguales ?.
GR$FICOS DIN$MICOS
Excel 2016
Pág. 39
Cestec - Perú ;na ve terminada la abla /inmica se !"ede dar !aso a la realiaci&n del %rfico dinmico. ara ello es necesario se%"ir estos !asos: 1. ara ello vamos a la !estaKa I$S)*+ . 2. +acemos clic en J'+I"# %I$,I"# F. Clic quí
O+ser/aci0n- $i @aces clic en la pesta;a6 podrs elegir entre las dos opciones. T es necesario tener en cuenta *ue se *uiere crear.
3. a!arecer la si%"iente ventana$ donde !odrs ecoger el io
4. clic en +")P*+
EJERCICIOS ## %reaci0n de grficos dinmicos
Excel 2016
Pág. 40
Cestec - Perú ;tiliando la base de datos del ejercicio T 1 ( tabla dinmica:
Crear el si%"iente %rafico dinmico:
EJERCICIO #4
Excel 2016
Pág. 41
Cestec - Perú
Crear las tablas ( %rficos dinmicos !ara las si%"ientes !re%"ntas:
istar a todas medicinas de la em!resa enfar.
istar a todas medicinas de la em!resa enfar.
istar a todas medicinas de la em!resa enfar$ as como s" cantidad vendida.
istar a todas medicinas de la em!resa 'arfam$ as como s" cantidad vendida.
istar a todas medicinas de la em!resa enfar$ as como s" im!orte.
istar a todas medicinas de la em!resa 'arfarm$ as como s" im!orte.
istar a todas medicinas de la em!resa 'arfarm$ as como s" cantidad.
istar a todas medicinas de la em!resa 'arfarm$ as como s" cantidad.
istar a todas em!resas$ !or s"c"rsal ( las medicinas "e vende.
Excel 2016
Pág. 42
Cestec - Perú
CAPÍTULO ": "ALIDACION DE DATOS ...
"ALIDACION DE DATOS
Objetivos: • •
Conocer los conce!tos de validaci&n de datos !licar validaci&n de datos.
Excel 2016
Pág. 43
Cestec - Perú "ALIDACION DE DATOS a validaci&n de datos es s"mamente )til c"ando deseas com!artir "n libro o arc+ivo de 's #,cel con otros miembros de la or%aniaci&n ( deseas "e los datos "e se escriban en Nl sean e,actos ( co+erentes. "edes a!licar la validaci&n de datos !ara los si%"ientes casos:
-estrin%ir los datos a elementos !redefinidos de "na lista. -estrin%ir los n)meros "e se enc"entren f"era de "n intervalo es!ecfico. -estrin%ir las fec+as "e se enc"entren f"era de "n !erodo de tiem!o
es!ecfico. -estrin%ir las +oras "e se enc"entren f"era de "n !erodo de tiem!o
es!ecfico. imitar la cantidad de caracteres de te,to. ?alidar datos se%)n f&rm"las o valores de otras celdas.
PASOS a +erramienta de ?alidaci&n de datos se enc"entra en: 1. ic+a /atos 2. Clic en ?/CS /# /O*.
Clic a(),
3. l dar clic en ?/CS /# /O* se vis"alia el c"adro de dilo%o validaci&n de datos "e contiene tres !estaKas:
Excel 2016
Pág. 44
Cestec - Perú
1. 2. 3.
Confi%"raci&n. 'ensaje de entrada. 'ensaje de error.
#n la !estaKa de Confi%"raci&n se confi%"ra el criterio de validaci&n en la o!ci&n ermitir. #n la !estaKa 'ensaje de entrada se confi%"ra el mensaje de entrada "e alertar al "s"ario sobre el ti!o de datos "e !"ede in%resar. a !estaKa 'ensaje de error !ermite "e el "s"ario !ersonalice el mensaje de error en el caso de "e el "s"ario +a(a in%resado datos f"era del criterio de validaci&n.
EJERCICIO #,
Excel 2016
Pág. 45
Cestec - Perú
?/CO#*:
ntrod"cir en la celda C## solo letras$ !ero "e la cantidad no e,ceda de
15 caracteres. ntrod"cir en la celda -;C solo n)meros ( de 11 d%itos. *"s 2 !rimeros
n)meros sea 20 o 10 Bersona j"rdica o !ersona nat"ral res!ectivamente. ntrod"cir en la celda /-#CCO letras ( n)meros$ !ero "e la cantidad no
e,ceda de 18 caracteres. ntrod"cir en la celda #C solo a las fec+as ma(ores o i%"ales a o(. a celda O-' /# O debe ser "na lista "e m"estre C-#/O$
/#<O ( CO/O. a celda C;// debe ser "na lista "e m"estre ;-@$ '$ -;>O$
C'
Excel 2016
Pág. 46
Cestec - Perú
ntrod"cir en la #' 7 caracteres$ los 3 !rimeros debe ser D;D$ los 4
si%"ientes "n n"mero entre 100 ( 999. ntrod"cir en la celda /#*C-CO letras ( n)meros$ !ero "e la cantidad
no e,ceda de 25 caracteres. ntrod"cir en la celda C// solo n)meros enteros ( !ositivos. ntrod"cir en la celda C// n)meros enteros o con decimales ma(ores
o i%"ales a 0.10. ntrod"cir en la celda C// n)meros enteros o con decimales ma(ores
o i%"ales a 0.10.
dems$ c"ando el "s"ario seleccione la celda !"edes alertarlo al confi%"rar "n mensaje de entrada. /e i%"al forma$ si el "s"ario trata de in%resar "n te,to ma(or a los 11 caracteres$ !"edes confi%"rar !ara "e #,cel emita "n mensaje de error.
*ol"cionario de la validaci&n !ersonaliada: CLIENTE Y(ESTEKTO(D,)5 LARGO(D,) 1,) RUC Y(ESNUMERO(F#)5LARGO(F#)115O(IUIERDA(F#52)72+75IUIERDA(F#5 2)71+7)) ITEM Y(LARGO(C1+)%5IUIERDA(C1+5#)7UNI75"ALOR(DERECA(C1+54))1++ 5"ALOR(DERECA(C1+54))****)
Excel 2016
Pág. 47
Cestec - Perú
CAPÍTULO "I: MATEMATICA FINANCIERA
MATEMATICA FINANCIERA
Objetivos: • •
Conocer los conce!tos de matemtica financiera ;sar las f"nciones de matemtica financiera.
NOMENCLATURA B$SICA S/,#L# &A6!
SI'$II"+%# %apital6 principal6 monetarias
Excel 2016
&alor Actual
epresado
en unidades
Pág. 48
Cestec - Perú &
%apital ms el interEs6 monto6 &alor uturo epresado en unidades monetarias
7
)asa nominal o la tasa de interEs anual
t
N,mero de a;os6 tiempo
m
N,mero de capitali2aciones por a;o
n
N,mero de per8odos de composici0n
i
)asa peri0dica
)#A
)asa #fecti/a Anual
&AN
&alor Actual Neto
)(R
)asa (nterna de Retorno
%
Anualidad o cuota uniforme
&A
&alor presente de una anualidad
&
&alor futuro de una anualidad
ia
)asa de interEs anticipada
i/
)asa de interEs /encida
U'
Unidad 'onetaria
RESUMEN DE FORMULAS
Excel 2016
Pág. 49
Cestec - Perú
INTER&S SIMPLE Excel 2016
Pág. 50
Cestec - Perú #l interNs es el !rod"cto de los tres factores$ ca!ital B?$ tiem!o Bn ( tasa Bi$ as tenemos: I =VA ∗n∗i
I =VF −VA
/e esta misma f&rm"la se !"ede des!ejar la tasa de interNs: i=
I VA ∗n
V+ o P
"aial o Pricial
i
*aa ierB
Pla?o o C>ero
V
,oo $uma de capital ms interEs
EJERCICIO #6: BCalc"lando el interNs sim!le >oa"n 'artne es "n !ro!ietario de "n ta,i de servicio !)blico. Como res"ltado de s"s actividades +a a+orrado en "n aKo *G 2000. 00 en "n banco de la ci"dad. #l %erente del banco nota "e el *r. 'artne tiene "n saldo ac"m"lado de "n !oco ms de *G 6000. 00$ !or lo c"al lo llama ( le !ro!one colocar s" dinero en "n C/ a seis meses con "n interNs del 24I an"al. #l *r. 'artne desea saber c"nto dinero obtendr de dic+a transacci&n. *ol"ci&n A 6000 i A 24I n A 0. 5 • • •
#ntonces el interNs sim!le ordinario 1 ser: 1 Los cálculos eectuados con !ase en a"os de #$5 días calendario se conocen co%o cálculos &'(C)*+ , a-uellos -ue se reali.an con #$0 se/n la costu%!re co%ercial se deno%inan *RD(R*+3
Excel 2016
Pág. 51
Cestec - Perú
I 6+++. ++ 8 249 8 +. , %2+.++ #s decir6 es 19 al mes6 *ue sumados por meses al capital colocado suman $H =9.99.
#n el mismo ejem!lo$ el *r. 'artne le !ide consejo a "n ami%o ( este le dice "e$ si le !resta el dinero a Nl !or tres meses$ le devolver 6500. 00. C"l ser el interNs "e recibira el *r. 'artne. i=
500 6000∗0.25
3 ,++(6+++8+.2,) +.## ##. #9
O>O: a tasa de nterNs es 33.3I n"al. EJERCICIO #%: BCaja r"ral ;na Caja -"ral$ !a%a el 6I sobre los de!&sitos a !laos. /eterminar el !a%o an"al !or interNs sobre "n de!&sito de ;' 18$000.
Excel 2016
Pág. 52
Cestec - Perú *ol"ci&n: • • • •
? A 18$000 nA1 i A 6I AW
I 1'+++81869 UM 1+'+
-es!"esta: a Caja -"ral !a%a an"almente sobre este de!&sito la s"ma de ;' 1$080.
EJERCICIO #': BrNstamo a 'Q#* ;n
,+++++ "A818+.466$ des!ejamos ?:
Excel 2016
Pág. 53
Cestec - Perú VA =
500,000 0.466
= UM
'
1 072,961.37
-es!"esta: #l
EJERCICIO #*: BCalc"lando el !lao de "na inversi&n ;na entidad financiera invirti& ;' 250$000 al 17.6I en +i!otecas locales ( %an& ;' 22$000. /eterminar el tiem!o "e est"vo invertido el dinero. *ol"ci&n ? A 250$000 A 22$000 i A 0.176 nAW • • • •
/es!ejamos n de la f&rm"la: I "A8;83 n=
I VA ∗i
s"stit"(endo las cantidades: n=
22,000 250,000∗0.176
=
22,000 44,000
=
1 2
año
-es!"esta: #l dinero est"vo invertido d"rante medio aKo.
EJERCICIO 4+: BCalc"lando la tasa i de interNs *i "na em!resa +i!otecaria tiene invertido ;' 320$000 d"rante 3Y aKos a interNs sim!le ( obtiene en total ;' 146$250 de in%resos$ Xc"l es la tasa de interNsW *ol"ci&n
I 1462,+ "A #2++++
; #.,
3
/es!ejamos i de la f&rm"la: I =VA ∗n∗i
i=
I VA ∗n
Excel 2016
Pág. 54
Cestec - Perú i=
146,250 320,000∗3.5
=
0.13
-es!"esta: a em!resa +i!otecaria obt"vo el 13I sobre s" inversi&n.
MONTO #l monto es la s"ma obtenida aKadiendo el interNs al ca!ital$ esto es:
,#$*# = "+PI*+L E I$*)FS -eem!laando en la form"la anterior !or s"s res!ectivos smbolos$ obtenemos la f&rm"la %eneral !ara el monto:
&rm"la !ara el monto B? a interNs sim!le de "n ca!ital ?$ "e deven%a interNs a la tasa i d"rante n aKos.
Excel 2016
Pág. 55
Cestec - Perú /e donde:
T3> =? >/Q =? / 3;
eneralmente conocemos dos ti!os de !laos: a nterNs Comercial o
EJERCICIO 41: BnterNs *im!le Comercial >or%e de!osita ;' 2$300$ en "na libreta de a+orros al 9I an"al$ Xc"nto tendr des!"Ns de 9 mesesW
*ol"ci&n: #,!resamos la tasa en meses: 0.09G12 A 0.0075$ mens"al:
"A 2#++
3 +.++%,
;*
"F
"F 2#++ 1 (+.++%,8*) ;' 24,,.2,
Excel 2016
Pág. 56
Cestec - Perú
-es!"esta: #l valor f"t"ro es ;' 2$455.25
EJERCICIO 42: BnterNs *im!le #,acto ;n !e"eKo em!resario$ con "tilidades !or ;' 5$000 los de!osita en "na libreta de a+orros en "n banco al 9.7I an"al. Calc"lar c"nto tendr al final de 8 meses.
*ol"ci&n: #,!resamos el !lao en aKos: B8 meses !or 30 das A 240 das
240G365 = 0H6575 ao
"A ,+++
3 +.+*%
; +.6,%,
"F
"F ,+++ 81 (+.+*%8+.6,%,) UM ,#1'.'*
Excel 2016
Pág. 57
Cestec - Perú
-es!"esta: #l !e"eKo em!resario tendr al final de los 8 meses ;' 5$318.89
DESCUENTOS #s "na o!eraci&n de crNdito llevada a cabo !rinci!almente en instit"ciones bancarias ( consiste en "e estas ad"ieren letras de cambio$ !a%arNs$ fact"ras$ etc. de c"(o valor nominal desc"entan "na s"ma e"ivalente a los intereses "e deven%ara el doc"mento entre la fec+a recibida ( la fec+a de vencimiento. ntici!an el valor act"al del doc"mento. a f&rm"la !ara el clc"lo del desc"ento es:
donde:
D
A desc"ento
"F "N A valor del !a%arN o doc"mento Bmonto$ valor nominal =
A tasa de desc"ento
;
A n)mero de !erodos +asta el vencimiento del !a%arN
Excel 2016
Pág. 58
Cestec - Perú Otras f&rm"las del desc"ento B/es!ejando de la f&rm"la de desc"ento tenemos:
*"stit"imos el valor de ? en la f&rm"la de desc"ento:
inalmente "eda as:
EJERCICIO 4#: Ba%arN enemos "n !a%arN !or ;' 185$000$ %irado el 15G09G03 ( con vencimiento al 15G11G03$ con "na tasa de desc"ento de 50I an"al. /eterminar el desc"ento ( el valor act"al del doc"mento. *ol"ci&n: ? A 185$000 n A 2 meses d A B0.50G12 A 0.0417 /AW ?A W allando el desc"ento:
allando el valor act"al:
Excel 2016
Pág. 59
Cestec - Perú
-es!"esta: #l desc"ento es de ;' 15$416.64 ( el valor act"al del doc"mento es de ;' 169$583.33.
EJERCICIO 44: B/esc"ento de !a%arN ;na em!resa desc"enta "n !a%arN ( recibe ;' 20$000. *i la tasa de desc"ento es del 66I an"al ( el vencimiento es en tres meses des!"Ns del desc"ento. XC"l era el valor nominal del doc"mento en la fec+a de vencimientoW *ol"ci&n: ? A 20$000 d A B0.66G12 A 0.055 nA3 ? A W
allamos el desc"ento:
allar el valor nominal
Excel 2016
Pág. 60
Cestec - Perú "F 2++++ ##++ UM 2##++
-es!"esta: #l valor nominal B? del doc"mento en la fec+a de vencimiento es ;' 23$300.
EJERCICIO 4,: B/esc"ento de letra ;na em!resa desc"enta "na letra !or la c"al recibe ;' 2$520. *i la tasa de desc"ento es de 66I ( el valor nominal de ;' 2$950. XC"nto tiem!o faltaba !ara el vencimiento de la obli%aci&nW *ol"ci&n: ? A 2$950 ? A 2$520 d A B0.66G12 A 0.055 /AW
allamos desc"ento:
D 2*,+ 2,2+ UM 4#+.++
/es!ejando n de la f&rm"la % = V$ JJi obtenemos:
Excel 2016
Pág. 61
Cestec - Perú 9.?99 1C.?1 d8as
-es!"esta: altaba !ara el vencimiento 2 meses ( 20 das.
FUNCIONES FINANCIERAS #,cel es "na de las +erramientas ms !otentes !ara trabajar con informaci&n ( clc"los financieros$ ofrece "na am!lia %ama de f"nciones !rediseKadas "e te a("darn a realiar tareas sencillas con relaci&n a t"s finanas. as f"nciones financieras calc"lan informaci&n financiera como$ !or ejem!lo$ el valor neto !resente ( !a%os. or ejem!lo$ !"ede calc"lar los !a%os mens"ales re"eridos !ara com!rar "n a"to a "na determinada tasa de interNs.
FUNCIÓN "F ermite calc"lar ? a !artir del Ca!ital o del ?. ambiNn sirve !ara calc"lar el valor de ? indicando si es c"ota antici!ada Bti!oA1 o vencida Bti!oA0. *i lo "e "eremos calc"lar es ? a !artir de ? omitimos el valor del Ca!italP si la c"ota es vencida$ omitimos el valor ti!o. /ev"elve el valor f"t"ro B? de la inversi&n$ e"ivalente a los !a%os !eri&dicos "niformes a "na tasa de interNs constante.
Excel 2016
Pág. 62
Cestec - Perú *inta,is:
"F(< N>?0 > <3>) #l res"ltado !ro!orcionado !or esta f"nci&n lo obtenemos tambiNn con la si%"iente f&rm"la:
EJERCICIO 46: "nci&n ? *i a+orramos *G. 350.00 mens"ales en "n banco "e !a%a el 18I nominal an"al ( deseamos saber c"nto dinero tendremos a+orrado al final de los 3 aKos. *ol"ci&n /i%itar a contin"aci&n la si%"iente tabla ( a!licar la f"nci&n ? en la celda 6. #l res"ltado deber ser el si%"iente:
Excel 2016
Pág. 63
Cestec - Perú
Calc"le "sando la form"la indicada$ !ara com!robar la sol"ci&n: C A 350
n A B3Z12 A 36
i A 0.015 B0.18G12
"F
EJERCICIO 4%: BCalc"lando el ? Calc"lar el ? al final de 5 aKos de "na inversi&n de ;' 20$000 con "n costo de o!ort"nidad del ca!ital de 20I an"al. *ol"ci&n: ? A 20$000 nA5 i A 0.20 ? A W ;sando la f&rm"la:
!licamos la f"nci&n financiera ?:
Excel 2016
Pág. 64
Cestec - Perú
-es!"esta: #l ? al final de los 5 aKos es ;' 49$766.40
EJERCICIO 4': BCalc"lando el ? a !artir del ? Qo ten%o "n e,cedente de "tilidades de ;' 1$000 ( los %"ardo en "n banco a !lao fijo$ "e an"almente me !a%a 8IP Xc"nto tendrN dentro de 3 aKosW *ol"ci&n: ? A 1$000 nA3 i A 0.08 ? A W ndistintamente a!licamos la f&rm"la ( la f"nci&n financiera ?:
"F 1+++(1 +.+')# UM 12,*.%1
-es!"esta: #l monto al final de los 3 aKos es ;' 1$259.71
Excel 2016
Pág. 65
Cestec - Perú
FUNCIÓN "A ermite calc"lar ? a !artir del Ca!ital o de ?. ambiNn sirve !ara calc"lar el valor de ? indicando si es c"ota antici!ada Bti!oA1 o vencida Bti!oA0. ara calc"lar ? a !artir de ?$ omitir el valor del Ca!italP ( c"ando o!eremos con c"otas vencidas$ omitir el valor ti!o. /ev"elve el valor act"al de la inversi&n. #l valor act"al B? es la s"ma de "na serie de !a%os a f"t"ro. or ejem!lo$ c"ando !edimos dinero !restado$ la cantidad del !rNstamo es el valor act"al !ara el !restamista. #sta f"nci&n conserva las mismas observaciones efect"adas !ara ?.
*inta,is:
"A(< ;>?0 > <3>)
#l res"ltado !ro!orcionado !or esta f"nci&n lo obtenemos tambiNn con la si%"iente f&rm"la:
Excel 2016
Pág. 66
Cestec - Perú
EJERCICIO 4*: ;CS ? *i a+orramos *G. 400.00 mens"ales d"rante 5 aKos en "n banco "e !a%a el 15I nominal an"al ( Calc"lar c"nto re!resentan estas mens"alidades al da de +o(. *ol"ci&n: /i%itar a contin"aci&n la si%"iente tabla ( a!licar la f"nci&n ? en la celda 6. #l res"ltado deber ser el si%"iente:
Excel 2016
Pág. 67
Cestec - Perú
*ol"ci&n [ "se la f&rm"la !ara com!robar el res"ltado: C A 400
n A B5Z12 A 60
i A 0.0125B0.15G12
"A
EJERCICIO ,+: BCalc"lando Nl ? a !artir del ? nversamente$ al%"ien nos ofrece ;' 5$000 dentro de 3 aKos$ siem!re ( c"ando le entre%"emos el da de +o( "na cantidad al 10I an"al. XC"nto es el monto a entre%ar +o(W
-es!"esta: #l monto a entre%ar el da de +o( es ;' 3$757.57
Excel 2016
Pág. 68
Cestec - Perú FUNCIÓN PAGO ara encontrar la cantidad total !a%ada d"rante el !erodo del !rNstamo$ m"lti!li"e el valor dev"elto !or O !or el ar%"mento !er.
PAGO(< N>?0 <3>) #l res"ltado !ro!orcionado !or esta f"nci&n lo obtenemos tambiNn con la si%"iente f&rm"la:
EJERCICIO ,1: B"nci&n a%o Obtenemos "n crNdito de *G. 10$000 !ara s" !a%o en 24 c"otas trimestrales i%"ales$ a la tasa nominal an"al de 36I !or trimestre vencido. *ol"ci&n: /i%itar a contin"aci&n la si%"iente tabla ( a!licar la f"nci&n O en la celda 6. #l res"ltado deber ser el si%"iente:
Ojo: a tasa es el res"ltado de: *aa 36KG12
Excel 2016
Pág. 69
Cestec - Perú
FUNCIÓN TASA /ev"elve la tasa de interNs !or !erodo de la an"alidad. a * es calc"lada !or iteraci&n ( !"ede tener cero o ms sol"ciones. *i los res"ltados s"cesivos de * no conver%en dentro de 0.0000001 des!"Ns de 20 iteraciones$ * dev"elve el valor de error \];'^. Con esta f"nci&n es !osible calc"lar la tasa de interNs$ combinando no s&lo ? ( ?$ sino tambiNn ? ( C$ C ( ? ( ?$ C ( ?. or ser la tasa del !erodo tiene la caracterstica de ser sim"ltneamente nominal ( efectiva$ !ara convertir Nsta tasa en tasa an"al debe tenerse c"idado con la f&rm"la "tiliada$ de!endiendo de "N tasa "eremos calc"lar: la tasa nominal o la tasa efectiva an"al B#. *inta,is:
TASA(N>?0 P " " T3> E<30)
Excel 2016
Pág. 70
Cestec - Perú "nci&n "tiliada !ara calc"lar la tasa !eri&dica de las an"alidades.
Ojo: o e,isten f&rm"las !ara obtener la tasa de las an"alidades.
EJERCICIO ,2: B"nci&n asa Obtenemos "n crNdito de *G. 5$000 !ara s" !a%o en 5 c"otas i%"ales$ con "n !a%o !or c"ota de *G. 1250. XC"l en la tasa de interNsW *ol"ci&n: /i%itar a contin"aci&n la si%"iente tabla ( a!licar la f"nci&n * en la celda 6. #l res"ltado deber ser el si%"iente:
Excel 2016
Pág. 71
Cestec - Perú
RPTA:
L TASA ? +.+% ? =?30 %9 ;/
EJERCICIO ,#: BCalc"lando el ti!o de interNs i /eterminar la tasa de interNs a!licada a "n ca!ital de ;' 25$000 "e +a %enerado en tres aKos intereses totales !or ;' 6$500.
*ol"ci&n: B? A 25$000 M 6$500 iAW ? A 25$000 nA3 A 6$500 ? A 31$500
Excel 2016
Pág. 72
Cestec - Perú
!licando la f&rm"la
$ o la f"nci&n *$ tenemos:
-es!"esta: a tasa de interNs a!licada es de 8I an"al.
EJERCICIO ,4: BCalc"lando el incremento an"al #n 1978 el fran"eo de "n sobre a #"ro!a era de ;' 10. #n el 2003 colocar !or correo la misma carta c"esta ;' 70. XR"N incremento an"al en el fran"eo de "na carta e,!eriment& d"rante este tiem!oW
*ol"ci&n: Bn A 2003 L 1978 C A 10 ? A 70 n A B2003 L 1978 A 25
Excel 2016
Pág. 73
Cestec - Perú iAW
!licando la f"nci&n * obtenemos:
-es!"esta:
#l incremento an"al es 13.71I
FUNCIÓN NPER /ev"elve la cantidad de !erodos "e debe tener la inversi&n !ara "e sea e"ivalente a la serie de !a%os !eri&dicos i%"ales.
NPER(< > <3>) a "nidad de tiem!o consi%nada en la f"nci&n N>?0 debe ser la misma "e la "tiliada en la tasa de interNs. #l res"ltado !ro!orcionado !or esta f"nci&n lo obtenemos tambiNn con las si%"ientes f&rm"las$ se%)n los casos:
Excel 2016
Pág. 74
Cestec - Perú
EJERCICIO ,,: B"nci&n #- Obtenemos "n crNdito de *G. 93$345.50 !ara s" !a%o con "na tasa de interNs real del 6I$ con "n !a%o !or c"ota de *G. 14$000.00. XC"l en la cantidad de !a%os "e se deben de realiarW /i%itar a contin"aci&n la si%"iente tabla ( a!licar la f"nci&n * en la celda 6. #l res"ltado deber ser el si%"iente:
Excel 2016
Pág. 75
Cestec - Perú
RPTA: N?0 =? >?03= ? '.%6' ? =?30 * > .
FUNCIÓN TIR /ev"elve la tasa interna de retorno Btasa de rentabilidad de los fl"jos de caja re!resentados !or los n)meros del ar%"mento valores. #stos fl"jos de caja no son constantes$ como en las an"alidades. *in embar%o$ los fl"jos de caja deben oc"rrir en intervalos re%"lares$ como meses o aKos. a tasa interna de retorno e"ivale a la tasa de interNs !rod"cida !or "n !ro(ecto de inversi&n con !a%os Bvalores ne%ativos e in%resos Bvalores !ositivos "e oc"rren en !erodos re%"lares. *inta,is:
TIR(/0? ?<30)
Excel 2016
Pág. 76
Cestec - Perú Observaciones - est ntimamente relacionado a ?$ la f"nci&n valor neto act"al. a tasa de retorno calc"lada !or - es la tasa de interNs corres!ondiente a "n valor neto act"al 0 Bcero. a f&rm"la si%"iente dem"estra la relaci&n entre ? ( -:
V$+(*I(16!:16! e igual a 3:60)08 Dentro de la eactitud del clculo )(R6 el /alor 69#9: es en efecto 9 cero.P
EJERCICIO ,6: B"nci&n - 1. /i%ite la si%"iente tabla en "na +oja de clc"lo n"eva: 2. Calc"lar la celda C10 as:
Excel 2016
Pág. 77
Cestec - Perú en%a en c"enta: #l retorno des!"Ns de 5 aKos se calc"la as:
Q la tasa de retorno des!"Ns de 2 aKos con estimaci&n de 10I. *e calc"la as:
Excel 2016
Pág. 78
Cestec - Perú
CAPÍTULO "II: PROTECCIÓN DE LIBROS Y OJAS DE C$LCULO ...
PROTECCIÓN DE LIBROS Y OJAS DE C$LCULO
Objetivos: • •
Conocer ( a!render a !rote%er los libros de #,cel. Conocer ( a!render a !rote%er +ojas de #,cel.
Excel 2016
Pág. 79
Cestec - Perú PROTECCIÓN DE LIBROS Y OJAS DE C$LCULO DEFINICIÓN ;na contraseKa es "na !alabra$ "na frase o "na cadena de caracteres "e !"ede introd"cirse !ara tener acceso a "na +oja$ "n libro. #n 'icrosoft #,cel$ "na contraseKa !"ede contener +asta 15 caracteres ( c"al"ier combinaci&n de letras$ n)meros$ es!acios ( smbolos. C"ando se escriba la contraseKa$ 'icrosoft #,cel mostrar "n asterisco BZ !or cada carcter "e se escriba. as contraseKas distin%"en ma()sc"las ( min)sc"las.
PROTECCIÓN DE LIBROS *e !rote%e la estr"ct"ra de "n libro$ !ara "e las +ojas de Nste no !"edan moverse$ eliminarse$ oc"ltarse$ mostrarse o cambiarse de nombre$ ni !"edan insertarse n"evas +ojas.
EJERCICIO ,% 1. bra "n arc+ivo de #,cel 2. a%a clic en la fic+a -#?*3. a%a clic en el comando -O##- <-O 2. elecci!a )EK)
3. elecci!a P)*;EE) L)*
4. #n el c"adro rote%er estr"ct"ra ( ventanas$ active las casillas "e se !resentan en la !arte inferior de la ventana$ se%)n sea necesario. ara n"estro ejercicio di%itar la contraseKa. 1234.
Excel 2016
Pág. 80
Cestec - Perú
5. /i%ite "na contraseKa$ en la casilla de te,to ContraseKa BO!cional: 6. ?"elva a /i%itar la contraseKa en la si%"iente ventana.
7. a%a clic en el bot&n ce!tar. 8. #l libro est !rote%ido !ara "e no se le a%re%"e ni elimine +ojas$ ni se m"eva o modifi"e el tamaKo de la ventana del arc+ivo.
PROTECCIÓN DE OJA DE C$LCULO rote%e elementos de la +oja de clc"lo$ tales como$ formatos$ ediciones$ ( otros objetos incr"stados$ !"ede asi%nrsele contraseKa !ara !rote%erla.
Excel 2016
Pág. 81
Cestec - Perú EJERCICIO ,' 1. a%a clic en la fic+a -evisar. 2. a%a clic en la o!ci&n rote%er +oja. 1. elecci!a )EK)
2. elecci!a P)*;EE) ?*+
3. #n el c"adro rote%er +oja$ active las casillas "e se !resentan en la !arte inferior de la ventana$ se%)n sea necesario. 4. /i%ite "na contraseKa$ en el c"adro de te,to ContraseKa !ara des!rote%er la +oja BO!cional:
5. ?"elva a di%itar la contraseKa en la si%"iente ventana:
Excel 2016
Pág. 82
