11 Mdulo 1: Frmulas y funciones avanzadas 22Instituto de
Informtica - INFOPUC Mdulo 1 ndice Tema 1: Funciones lgicas
.................................................................................................
3 1.1Recordando la funcin SI
..............................................................................................
3 1.2Funcin Y y O
...............................................................................................................
4 1.3Funcin SI acompaada de otras funciones
................................................................. 6
Tema 2: Funciones de bsqueda
......................................................................................
12 2.1Funcin BUSCARV
.....................................................................................................
13 2.2Funcin
BUSCARH.....................................................................................................
14 2.3Casos prcticos usando funciones de bsqueda
........................................................ 15 Tema 3:
Funciones estadsticas y financieras
.................................................................
18 3.1Funciones Promedio, Contar, Min,Max, entre otras
.................................................... 18 3.1.1Funcin
PROMEDIO
.......................................................................................
18 3.1.2Funcin CONTAR
...........................................................................................
19 3.1.3Funcin MAX y MIN
.........................................................................................
20 3.1.4Otras funciones estadsticas: VAR, DESVEST, MODA
................................... 21 3.2Funciones financieras:
PAGO y VA
............................................................................
22 3.2.1Funcin PAGO
................................................................................................
23 3.2.2Funcin VA (valor actual)
................................................................................
24 3.3Casos prcticos sobre el uso de funciones financieras
............................................... 26 3.4Funciones
Contar.Si y Sumar.Si
.................................................................................
30 3.4.1SUMAR.SI(rango;criterio;rango_suma)
........................................................... 30
3.4.2CONTAR.SI (rango;criterio)
.............................................................................
31 Tema 4: Estrategias en el uso eficiente de frmulas y funciones:
funciones anidadas34 4.1Qu son funciones anidadas?
..................................................................................
34 4.2Combinar funciones de bsqueda y condicionales
..................................................... 35 4.3Casos
prcticos sobre el uso de funciones anidadas
.................................................. 38 3Pontificia
Universidad Catlica del Per Apli cacin y diseo de hojas de cl culo
en MS Excel - Nivel intermedio Tema 1: Funciones lgicas
1.1Recordando la funcin SI
Estafuncinlgicapermitedevolverunmensajeovalorluegoderealizaruna
comparacin. Por ejemplo: se desea resaltar en qu momento se ha
superado el tope
deunpresupuesto,demodoque,automticamente,aparecerenpantallaun
mensaje: PRESUPUESTO EXCEDIDO. Figura 1: Funcin SI
LafuncinSIdevuelveunvalorsielargumentoprueba_lgicaesVERDADEROy otro
valor si dicho argumento es FALSO. Sintaxis: Donde: prueba_lgica
Escualquiervaloroexpresinquepuedaevaluarsecomo VERDADERO o FALSO.
valor_si_verdaderoEs el valor que se devolver si prueba_lgica es
VERDADERO. Siprueba_lgicaesVERDADEROyseomiteelargumento
valor_si_verdadero, la funcin devuelve VERDADERO.
valor_si_falsoEselvalorquesedevolversiprueba_lgicaesFALSO.Si
prueba_lgicaesFALSOyseomiteelargumento valor_si_falso, la funcin
devuelve FALSO. La frmula, tal como se observa en la barra de
frmulas de la figura 1 es: Figura 2: Frmul a de funcin SI
SI(prueba_lgica, valor_si_verdadero, valor_si_falso) 4Instituto de
Informtica - INFOPUC Mdulo 1 Observe que se est colocando en el
segundo argumento para que sila condicin no se cumple, aparecer un
texto vaco. Si este argumento
sedejaenblanco,alnocumplirselacondicin,aparecerelvalor FALSO.
1.2Funcin Y y O Se utiliza para establecer condiciones entre
expresiones. Operadores lgicos Y Y lgico.O O lgico. Sintaxis:
Y(Valor_lgico1, valor_lgico2,,valor_lgico255) O(Valor_lgico1,
valor_lgico2,,valor_lgico255) Donde:
Valor_lgico1255,soncondicionesquepuedentenerunvalorVERDADEROo
FALSO. Estasdosfuncionesserigenporlasleyesdelalgicasegnsemuestraa
continuacin: SituacinYO Todas las condiciones son
VERDADERASVERDADEROVERDADERO Existen condiciones VERDADERAS Y
FALSASFALSOVERDADERO Todas las condiciones son FALSASFALSOFALSO
Ejemplo: Figura 3: Para usar funci n Y 5Pontificia Universidad
Catlica del Per Apli cacin y diseo de hojas de cl culo en MS Excel
- Nivel intermedio
Lafiguramuestraunalistadepacientesconsusrespectivastemperaturas.Enla
columna TEMPERATURA NORMAL deber aparecer VERDADERO si la
temperatura est entre 36.8 C y 37.5 C, o FALSO si est fuera de ese
rango. Dado que la temperatura para ser normal debe cumplir dos
condiciones a la vez: ser mayor o igual a 36.8 Y ser menor o igual
a 37.5, usaremos la funcin Y para resolver
estecaso.Lasdoscondicionessernlosargumentosdelafuncin.Lospasosa
seguir son: 1.Situado en la celda C11, inserte la funcin Y haciendo
clic en Figura 4: Insertar funcin Y 2.En la ventana Argumentos de
la funcin coloque los siguientes argumentos: Figura 5: Argumentos
de la funcin Y 6Instituto de Informtica - INFOPUC Mdulo 1 3.Haga
clic en Aceptar y copie la funcin a todo el rango C11:C15. El
resultado ser como se muestra a continuacin: Figura 6: Resultado de
la funcin Y 1.3Funcin SI acompaada de otras funciones
Comosehapodidoobservar,lasfuncionesYyOnosmuestranresultados
VERDADEROoFALSO.Sinembargo,siqueremosqueelmensajesea personalizado,
debemos necesariamente insertarlas como parte de una funcin SI. Por
ejemplo,sienelltimocasomostrado,envezdelosresultadosdelafuncinY
queremosqueseobtengaelmensajeTEMPERATURANORMALparaaquellas
temperaturas que estn entre los 36.8 y los 37.5, tenemos que
combinar la funcin Y con la funcin SI y en su primer argumento usar
la funcin Y. Lo que se conoce como anidar funciones. El
procedimiento para este caso es el siguiente: 1.Situado en C11,
inserte la funcin SI y en el primer argumento inserte la funcin Y
de la lista de la barra de frmulas, como muestra la figura: Figura
7: Funcin Y dentro de funcin SI 7Pontificia Universidad Catlica del
Per Apli cacin y diseo de hojas de cl culo en MS Excel - Nivel
intermedio 2.Entonces aparecen los argumentos de la funcin Y donde
en forma similar que en la figura 5, estos sern: Figura 8:
Argumentos de la funcin Y
3.HagadobleclicenlafuncinSIenlabarradefrmulasyregresealos
argumentos de dicha funcin y compltelos as: Figura 9: Volviendo a
la funcin SI 4.Finalmente haga clic en Aceptar y el resultado ser
el siguiente: Figura 10: Resultado funcin SI y funcin Y 8Instituto
de Informtica - INFOPUC Mdulo 1
OtroejemplodondeseutilizalafuncinSIacompaadadeotrasfuncioneses
anidndola con alguna funcin bsica: SUMA, PROMEDIO, MAX, etc. En el
siguiente ejercicio observar una lista de alumnos con cuatro notas
cada uno. El promedio se calcular slo si tienen las cuatro notas;
en caso contrario, deber aparecer el texto SIN NOTA. Figura 11:
Funcin bsi ca PROMEDIO con funcin SI Vamos a comenzar analizando la
condicin o prueba lgica de la funcin SI: dado que
laFnoesunnmero,podemosutilizarlafuncinCONTARparaverificarsiel
alumno tiene cuatro notas (nmeros) de la siguiente manera:
1.SituadoenlaceldaF18,insertelafuncinSIydentrodelprimerargumento
(Prueba_lgica) inserte la funcin CONTAR como muestra la figura:
Figura 12: Funcin CONTAR dentro de la funcin SI 2.Ya en la funcin
CONTAR: ingrese los valores que vamos a contar, en este caso las
cuatro notas, es decir el rango de celdas B18:E18 como muestra la
figura: 9Pontificia Universidad Catlica del Per Apli cacin y diseo
de hojas de cl culo en MS Excel - Nivel intermedio Figura 13: Rango
de celdas para l a funcin CONTAR
3.Observeenelcuadrodelafiguraanterior,elresultadodeCONTAR,parael
primeralumnoes3,esoyanosindicaelposibleresultado final.Regreseala
funcin SI haciendo clic en la barra de frmulas as: Figura 14:
Regresando a l a funcin SI 4.CompletePrueba_lgica,
quecomoyadijimos, CONTARdebedarcomo resultado:cuatro(4)para que se
calcule el promedio. Figura 15: Completando Prueba_lgica de l a
funci n SI 10Instituto de Informtica - INFOPUC Mdulo 1
5.Completelosotrosargumentos:silapruebalgicasecumple,calculamosel
promedio, entonces, en Valor_si_verdadero ingrese la funcin
PROMEDIO como muestra la figura: Figura 16: Insertando funcin
PROMEDIO 6.Ahoradebeindicarel rangodeceldasparala funcinPROMEDIO,
quecomo sabemos es el rango B18:E18. Figura 17: Argumentos de la
funcin PROMEDIO 7.Ahora regrese a la funcin SI haciendo clic en
ella en la barra de frmulas, de manera similar que en los ejemplos
anteriores, y complete el tercer argumento de la funcin SI:
Valor_si_falso, es decir el mensaje SIN NOTA, como muestra la
siguiente figura: 11Pontificia Universidad Catlica del Per Apli
cacin y diseo de hojas de cl culo en MS Excel - Nivel intermedio
Figura 18: Completando l a funcin SI 8.Haga clic en Aceptar y copie
la frmula hacia abajo. El resultado se ver como sigue: Figura 19:
Resultado de funcin SI con la funcin PROMEDIO
LesugerimosquereviseelDemoUsodefuncioneslgicas,elcualse encuentra
en la plataforma educativa Paideia PUCP. Ejercicio sugerido 1:
Luegodehaberrevisadosumaterialdeestudio,leproponemosrealizarel
siguiente ejercicio:
AbraelarchivoCOBERTURADECLIENTESyenlahojaClientes_Lima realice lo
indicado en la hoja Instrucciones_1. 12Instituto de Informtica -
INFOPUC Mdulo 1 Tema 2: Funciones de bsqueda Este tipo de funciones
se utiliza cuando es necesario buscar valores en listas o tablas; o
la
referenciadeunacelda.Enestoscasospuedenutilizarselasfuncionesdebsqueday
referencia. Por ejemplo, para buscar un valor en una tabla que
coincida con un valor en la primera columna de otra tabla utilice
la funcin BUSCARV. Para determinar la posicin de un valor en una
lista utilice la funcin COINCIDIR. Las funciones de bsqueda que
desarrollaremos en este tema son las siguientes: BUSCARV BUSCARH
Las funciones de bsqueda son de las ms potentes que tienen los
programas de hojas de clculo para optimizar los procesos de gestin
de informacin y se vuelven ms potentes cuando las utilizamos
vinculando hojas y libros de clculo.
Recordemosqueparahacerreferenciasaotrashojasdeclculoseutilizalasiguiente
sintaxis: Parahacerms fcilelenlaceentrehojasde
clculoutiliceelmousepararealizardicha
operacin.Enelejemplo,despusdeescribirunsignoigual(=),ounoperadorenuna
frmula, haga clic en la etiqueta de la hoja para activarla. As
tambin, seleccione las celdas a las que quiere hacer referencia. Si
el nombre de la hoja tiene espacios, deber colocarlos entre
comillas simples. Ejemplo:
planienero!$A$5.Sirealizalareferenciautilizandoelmouse,Excelagregalascomillas
automticamente. Nombre de la hoja de clculo, debe tener referencia
absoluta La referencia a la celda puede ser absoluta o relativa
=Hoja2!A1 Un signo de admiracin separa la referencia de la hoja de
la referencia de la celda 13Pontificia Universidad Catlica del Per
Apli cacin y diseo de hojas de cl culo en MS Excel - Nivel
intermedio 2.1Funcin BUSCARV Busca un valor especfico en la columna
de la izquierda de una matriz y devuelve el valor en la misma fila
de una columna especificada en la tabla. Utilice BUSCARV en
lugardeBUSCARHcuandolosvaloresdecomparacinseencuentrenenuna columna
situada a la izquierda de los datos que desea encontrar. Sintaxis:
Donde:
Valor_buscadoeselvalorquesebuscaenlaprimeracolumnadelamatriz.
Valor_buscado puede ser un valor, una referencia o una cadena de
texto. Matriz_buscar_en es el conjunto de informacin en el que se
buscan los datos:
unatablaquecontienetexto,nmerosovaloreslgicosdedondesevaa obtener o
recuperar informacin. Se le denota como una referencia a un rango o
un nombre de rango de celdas, como por ejemplo Base_de_datos o
Lista. Indicador_columnas es el nmero de columna de
matriz_buscar_en desde la
cualsedevuelveelvalorcoincidente.Sielargumentoindicador_columnases
iguala1,lafuncindevuelveelvalordelaprimeracolumnadelargumento
matriz_buscar_en; si el argumento indicador_columnas es igual a 2,
devuelve el
valordelasegundacolumnadematriz_buscar_en,yassucesivamente.Si
indicador_columnasesmenorque1,BUSCARVdevuelveelvalordeerror
#VALOR!;siindicador_columnasesmayorqueelnmerodecolumnasde
matriz_buscar_en, BUSCARV devuelve el valor de error #REF!
OrdenadoesunvalorlgicoqueindicasideseaquelafuncinBUSCARV
busqueunvalorigualoaproximadoalvalorespecificado.Sielargumento
ordenado es VERDADERO o se omite, la funcin devuelve un valor
aproximado,
esdecir,sinoencuentraunvalorexacto,devolverelvalorinmediatamente
menorquevalor_buscado.SiordenadoesFALSO,BUSCARVdevuelveel valor
igual al buscado. Si no encuentra ningn valor, devuelve el valor de
error #N/A. SielargumentoordenadoesVERDADERO,losvaloresdelaprimera
columnadelargumentomatriz_buscar_endeberancolocarseenorden
ascendente:...;-2;-1;0;1;2;...;A-Z;FALSO;VERDADERO.Delo contrario,
BUSCARV podra devolver un valor incorrecto. Para asegurarse, o si
slo busca los valores exactos, ponga siempre FALSO. Para colocar
los valores en orden ascendente elija Ordenar de la cinta Datos y
seleccione la opcin "Ascendente". El texto escrito en maysculas y
minsculas es equivalente.
BUSCARV(valor_buscado;matriz_buscar_en;indicador_columnas;ordenado)
14Instituto de Informtica - INFOPUC Mdulo 1
SiBUSCARVnopuedeencontrarvalor_buscadoyordenadoes
VERDADERO,utilizaelvalormsgrandequeseamenoroiguala valor_buscado.
Si valor_buscado es menor que el menor valor de la primera columna
de matriz_de_comparacin, BUSCARV devuelve el valor de error #N/A.
Si BUSCARV no puede encontrar valor_buscado y ordenado es FALSO,
devuelve el valor de error #N/A. 2.2Funcin BUSCARH
Buscaunvalorenlafilasuperiordeunatablaounamatrizdevaloresy,a
continuacin, devuelveun valor en la misma columna de una fila
especificada en la tabla o en la matriz. Use BUSCARH cuando los
valores de comparacin se encuentren
enunafilaenlapartesuperiordeunatabladedatosycuandodeseeencontrar
informacinqueseencuentredentrodeunnmeroespecificadodefilas.Use
BUSCARVcuandolosvaloresdecomparacinseencuentrenenunacolumnaala
izquierda de los datos que desee encontrar. Sintaxis: Donde:
Valor_buscado es el valor que se busca en la primera fila de
matriz_buscar_en. Valor_buscado puede ser un valor, una referencia
o una cadena de texto. Matriz_buscar_en es el conjunto de
informacin en el que se buscan los datos:
unatablaquecontienetexto,nmerosovaloreslgicosdedondesevaa obtener o
recuperar informacin. Se le denota como una referencia a un rango o
un nombre de rango de celdas, como por ejemplo Base_de_datos o
Lista.
Indicador_filaseselnmerodefilaenmatriz_buscar_endesdelacualse
devuelve el valor coincidente. Si el argumento indicador_filas es
1, devuelve el valor de la primera fila del argumento
matriz_buscar_en: Si indicador_filas es 2, devuelve el valor de la
segunda fila en matriz_buscar_en, y as sucesivamente.
Siindicador_filasesmenorque1,BUSCARHdevuelveelvalordeerror
#VALOR!:Siindicador_filasesmayorqueelnmerodefilasen
matriz_buscar_en, BUSCARH devuelve el valor de error #REF! Ordenado
es un valor lgico que especifica si desea que el elemento buscado
por la funcin BUSCARH coincida exacta o aproximadamente. Si
Ordenado es VERDADERO o se omite, la funcin devuelve un valor
aproximado, es decir, si no se encuentra un valor exacto, se
devuelve el mayor valor que sea menor que
elargumentovalor_buscado.SiOrdenadoesFALSO,lafuncinBUSCARH
encontrar el valor exacto. Si no se encuentra dicho valor, devuelve
el valor de error #N/A.
BUSCARH(valor_buscado;matriz_buscar_en;indicador_filas;ordenado)
15Pontificia Universidad Catlica del Per Apli cacin y diseo de
hojas de cl culo en MS Excel - Nivel intermedio Si el argumento
ordenado es VERDADERO, los valores de la primera fila
delargumentomatriz_buscar_endeberncolocarseenorden ascendente:
...-2; -1; 0; 1; 2;..., A-Z, FALSO, VERDADERO; de lo contrario, es
posible que BUSCARH no devuelva el valor correcto. Se pueden poner
los datos en orden ascendente de izquierda a derecha
seleccionandolosvaloresyeligiendoOrdenar delacintaDatos,opcin
Ordenar de izquierda a derecha, como se ve en la figura: Figura 20:
Ordenar de izquierda a derecha El texto en maysculas y minsculas es
equivalente.
SiBUSCARHnolograencontrarvalor_buscado,utilizaelmayorvalor que sea
menor que valor_buscado.
Sivalor_buscadoesmenorqueelmenorvalordelaprimerafilade
matriz_buscar_en, BUSCARH devuelve el valor de error #N/A. 2.3Casos
prcticos usando funciones de bsqueda El caso ms frecuente del uso
de las funciones de bsqueda es completar informacin
enunatablacondatos queseencuentranenotraa travsdeundatocomn.Por
ejemplo,enlatabladelafigura,queseencuentraenunahojallamada
Clientes_logstica y enla cual debemos completar el Cdigo del sector
(columna B) para los diferentes tipos de sector (columna A). Veamos
la siguiente figura: Figura 21: Completar con funciones de bsqueda
La tabla donde se encuentran los cdigos puede estar en la misma
hoja de trabajo, en otra hoja del mismo libro, o incluso en otro
libro. Ser siempre ms conveniente darle 16Instituto de Informtica -
INFOPUC Mdulo 1 nombreaestatabla,
queseencuentraenlahojaCod_sector,elrangoB2:C7.En este ejemplo le
daremos el nombre sectores, como muestra la figura 22: Figura 22:
Nombrando a l a matriz_buscar_en Recuerde que el rango nombrado no
debe incluir los ttulos de columna.
RegresandoalahojaClientes_logstica,nosubicaremosenlaceldaC2y
aplicaremoslafuncinBUSCARVconsusrespectivosargumentos,talcomose
muestra a continuacin: Figura 23: Argumentos de la funcin BUSCARV
El argumento Valor_buscado es B2, en el que se encuentra el cdigo
del sector del primer registro. El argumento Matriz_busca_en es el
rango definido como
sectores,elIndicador_columnaseselnmerodecolumnadedichorangoenlaquese
encuentra el dato buscado; en este caso, la segunda columna es la
que corresponde al cdigo del secroe. Por ltimo, en el argumento
Ordenado colocamos FALSO. El resultado, despus de aplicar la frmula
a todas las celdas de la columna, ser: 17Pontificia Universidad
Catlica del Per Apli cacin y diseo de hojas de cl culo en MS Excel
- Nivel intermedio Figura 24: Resultados de la funcin BUSCARV Le
sugerimos que revise el Demo Uso de funciones de bsqueda, el cual
se encuentra en la plataforma educativa Paideia PUCP. Ejercicio
sugerido 2:
Luegodehaberrevisadosumaterialdeestudio,leproponemosrealizarel
siguiente ejercicio:
AbraelarchivoCOBERTURADECLIENTES(elmismoqueeldelejercicio
sugerido1)yenlahojaClientes_Limarealiceloindicadoenlahoja
Instrucciones_2. 18Instituto de Informtica - INFOPUC Mdulo 1 Tema
3: Funciones estadsticas y financieras 3.1Funciones Promedio,
Contar, Min y Max
Estasfuncioneslepermitirnrealizaraplicacionesparaencontrar,porejemplo,
promediosdecostospresupuestalesporCdigodepartida,mximosdedeudas,
pagos mnimos, etc. Las funciones matemticas que desarrollaremos a
continuacin son las siguientes: PROMEDIO CONTAR MX MIN 3.1.1Funcin
PROMEDIO Devuelveelpromedio(mediaaritmtica)delosargumentos.Comoya
mencionamos lneas arriba, es de utilidad cuando se requiere
encontrar, por ejemplo, el promedio de costos de una partida
determinada del presupuesto en las ltimas cinco actividades
lectivas del rea. Sintaxis: Donde: nmero1;nmero2;... son de 1 a 255
argumentos numricos cuyo promedio se desea obtener.
Losargumentosdebensernmerosonombres,matriceso referencias que
contengan nmeros. Si un argumento contiene texto, valores lgicos o
celdas vacas, esosvaloressepasanporalto;sinembargo,seincluyenlas
celdas cuyo valor sea 0.
Alcalcularelpromediodelasceldastengaencuentala
diferenciaentrelasceldasvacasylasquecontienenelvalor cero,
especialmente si ha desactivado la casilla de verificacin Valores
cero. PROMEDIO(nmero1;nmero2;...) 19Pontificia Universidad Catlica
del Per Apli cacin y diseo de hojas de cl culo en MS Excel - Nivel
intermedio
EnExcel,parahallarelpromediodedosomsvalores,existenvarias formas:
con la definicin matemtica o con la funcin PROMEDIO. Por ejemplo,
si al rango de celdas A1:A5 se denomina Datos_1 y contiene los
nmeros10,5,8,9y13;elpromediosepuedehallarsegnmuestranlas frmulas de
la columna B: Figura 25: Formas de insertar la funcin PROMEDIO
Observe la frmula en B5, corresponde a la definicin de promedio.
Paradarnombreaunrango: seleccinelo, escriba un nombre en
cuadrodenombresypresionela tecla ENTER.
Sientrelosvaloresquevaapromediarexisteunvalordetextoquedebe
considerarse como cero, utilice la funcin PROMEDIOA. 3.1.2Funcin
CONTAR Cuenta los nmeros que hay en la lista de argumentos. Usar
CONTAR para obtener el nmero de entradas en un campo numrico de un
rango o de una matriz de nmeros. Sintaxis: Donde:
ref1;ref2;sonentre1y255argumentosquepuedencontenerohacer referencia
a distintos tipos de datos, pero solo se cuentan los nmeros.
CONTAR(ref1;ref2;...) 20Instituto de Informtica - INFOPUC Mdulo 1
Observemos Losargumentosquesonnmeros,valoresnulos,valores
lgicos,fechasorepresentacionestextualesdenmeros,se cuentan. Los
argumentos que son valores de error o texto que no pueden
traducirse a nmeros, se ignoran. Si un argumento es una referencia,
solo se cuentan los nmeros
deesamatrizoreferenciayseignoranlasceldasvacas,
valoreslgicos,textoovaloresdeerrordelamatrizodela referencia. En la
figura siguiente se muestran sombreadas, en la columna A, las
celdas consusrespectivosdatos.EnlacolumnaBsepresentanlasdiferentes
frmulas con la funcin CONTAR y en la columna C sus resultados.
Figura 26: Ej emplos de la funcin CONTAR
Compruebequehaydiferenciasentrelosdatosenceldasycomo argumentos.
3.1.3Funcin MAX y MIN
LafuncinMAXdevuelveelvalormximodeunalistadeargumentos. Sintaxis:
Dondenmero1;nmero2;...sonentre1y255nmerosparalosquese desea
encontrar el valor mximo. La funcin MIN devuelve el valor mnimo de
un conjunto de valores. Sintaxis:
Dondenmero1;nmero2;...sonentre1y255nmerosparalosquese desea
encontrar el valor mnimo, respectivamente. MIN(nmero1; nmero2,...)
MAX(nmero1, nmero2, ...) 21Pontificia Universidad Catlica del Per
Apli cacin y diseo de hojas de cl culo en MS Excel - Nivel
intermedio Observacin:silosargumentosnocontienennmeros,las
funciones MAX o MIN devuelven el valor 0 (ver figura 3) En la
figura 3 los resultados de las frmulas estn en rojo, observe que
los textoscomoargumentoscausanvaloresdeerrorycomoreferenciason
ignorados. Sinembargo,aligualqueparalafuncinpromediosideseamosquese
considere a los valores de texto como cero podemos usar la funcin
MINA. Figura 27: Ej emplos de la funcin MIN A continuacin un
resumen del resultado obtenido con valores no numricos
(enlasfuncionesbsicas),segnseusencomoargumento(A)ocomo referencia
(R)1: 3.1.4Otras funciones estadsticas: VAR, DESVEST, MODA Existen
un gran nmero de funciones estadsticas que ofrece Excel, adems de
las vistas en el acpite anterior existen otras que tambin se
aplican a un rango o matriz de datos. Su uso depender de los
clculos estadsticos que necesite hacer. Para una muestra de datos
podemos hallar: 1Recordemosque3seconvierteennmerocomo
argumento,comoreferenciaesignorado.Un nmero ingresado con un
apstrofo delante es un texto. Valores de error Como (A) da error
Como (R) da error Celdas vacas -------- Como (R) es ignorada Texto
que no se traduce a nmero Como(A) da error Como (R) es ignorado
Valores lgicos Como (A): VERDADERO vale 1 FALSO vale 0 Como (R) son
ignorados 22Instituto de Informtica - INFOPUC Mdulo 1
FuncinNombreFrmulaDefinicin VARVarianza VAR(nmero1, nmero2, ,
nmero255) DESVEST Desviacin estndar DESVEST(nmero1, nmero2, ,
nmero255) MODAModa MODA(nmero1, nmero2, , nmero255)
Devuelveelvalorquese repiteconmsfrecuencia enunamatrizorangode
datos. Esunamedidadela tendenciacentraldeuna muestra,aligualqueel
promedio y la mediana Como en todos los casos anteriores, para
obtener mayor informacin sobre
losargumentosyresultadosdelasfuncionespuedeleerloqueindicala
ventanadeArgumentosdefuncinparacadacasoypuedehacerclicen Ayuda
sobre esta funcin cada vez que lo necesite como indica la figura:
Figura 28: Obtener ayuda en una funcin
LesugerimosquereviseelDemoUsodefuncionesestadsticasy
financieras,elcualseencuentraenlaplataformaeducativaPaideia PUCP.
3.2Funciones financieras: PAGO y VA
EnExcel,lasfuncionesfinancierassonaquellasquenospermitentrabajarcon
informacinyclculosfinancieros,ofrecindonosunaampliavariedaddefunciones
prediseadas. En este mdulo estudiaremos las siguientes:
23Pontificia Universidad Catlica del Per Apli cacin y diseo de
hojas de cl culo en MS Excel - Nivel intermedio 3.2.1Funcin PAGO
Esta funcin calcula el pago de un prstamo basndose en pagos
constantes y en una tasa de inters constante. Sintaxis: Donde: Tasa
es el tipo de inters del prstamo, en porcentaje. Nper es el nmero
total de pagos del prstamo (cuotas). Va es el valor actual o lo que
vale ahora la cantidad total de una serie de pagos futuros. Tambin
se conoce como el principal. Vf es el valor futuro o un saldo en
efectivo que desea lograr despus de efectuar el ltimo pago. Si el
argumento vf se omite, se asume que el valor es 0 (es decir, el
valor futuro de un prstamo es 0). Tipo es el nmero 0 (cero) 1 e
indica el vencimiento de los pagos. Defina tipo comoSi los pagos
vencen 0 u omitidoAl final del perodo 1Al inicio del perodo
ElvalordevueltoporPAGOincluyeelcapitalyelinters,peronoincluye
impuestos,pagosenreservauotrosgastosquesuelenasociarseconlos
prstamos. Las unidades de los argumentos tasa y nper deben se
uniformes y si desea
encontrarlacantidadtotalquesepagalolargodetodoelprstamo,
multiplique el valor devuelto por PAGO por el argumento nper.
Silospagosfueranmensuales,dividalatasaentre12y multiplique el
periodo en aos por 12. Por ejemplo: si realiza pagos mensuales de
un prstamo de 3 aos con una
tasadeintersanualdel12%,use12%/12paraelargumentotasay3*12 para el
argumento nper. Si efecta pagos anuales del mismo prstamo, use 12%
para el argumento tasa y 3 para el argumento nper. Ejemplo:
Hallemos los pagos mensuales que se debenhacerporunprstamode
S/.11000 soles a 3 aos con una tasa anual de 11%. Los datos
(argumentos) seran los que estn en la columna B de la figura 28:
Figura 29: Ej emplos de la funcin PAGO PAGO(tasa;nper;va;vf;tipo)
24Instituto de Informtica - INFOPUC Mdulo 1 Calculemos el pago
mensual del ejemplo con la funcin PAGO, insertamos la funcin y
colocamos los argumentos as: Figura 30: Argumentos de la funcin
PAGO Comosepuedeleerel argumentovfseomite oseponecero,puesenun
prstamo el valor final es cero. EnlaceldaD2hemoshalladoel pago
mensual por un prstamo de11000solesporelperodode cuatro aos a una
tasa de 11%. Observe la frmula en la barra de frmulas. Figura 31:
Pago mensualpor un prstamo Por ser un pago se considera negativo y
por eso est en color de fuente rojo. 3.2.2Funcin VA (valor actual)
Devuelve el valor actual de una inversin. El valor actual es el
valor que tiene actualmente la suma de una serie de pagos que se
efectuarn en el futuro. Por ejemplo, cuando toma dinero prestado,
el monto total del prstamo (suma de pagos) es el valor actual para
el prestamista. Sintaxis: Tasa es la tasa de inters por perodo.Nper
es el nmero total de perodos en un ao.VA(tasa;nper;pago;vf;tipo)
25Pontificia Universidad Catlica del Per Apli cacin y diseo de
hojas de cl culo en MS Excel - Nivel intermedio
Pagoeselpagoqueseefectaencadaperodoyquenocambia
durantelavidadelaanualidad2.Porlogeneral,elargumentopago
incluyeelcapitalyelintersperonoincluyeningnotrocargoo impuesto.Vf
es el valor futuro o el saldo en efectivo que desea lograr despus
de efectuar el ltimo pago. Si el argumento vf se omite, se asume
que el valor es 0 (por ejemplo, el valor futuro de un prstamo es
0). Si no se consideraelargumentovfsedebeincluirelargumentopagoy
viceversa. Tipo es el nmero 0 1 e indica el vencimiento de los
pagos. Defina tipo comoSi los pagos vencen 0 u omitidoAl final del
perodo 1Al inicio del perodo Aligualqueparala
funcinPAGO,lasunidadesdelosargumentostasay nper de la funcin VA
deben se uniformes. Ejemplo: Supongamosqueestoydispuestoa
ahorrar360.13solesmensuales durante3aosenunaentidad financiera que
me va a dar una tasa de 11%anual.Quierosaberculesel valor actual de
esa inversin (el ahorro esunainversin,ascomoesuna
inversinparaunprestamistaprestar dinero). Figura 32: Caso: hallar
VA de pagos futuros
Entoncesahoralosdatos(argumentosparalafuncinVA)serancomo muestra la
figura 31. Insertamos la funcin VA y colocamos los argumentos como
sigue: 2 Se llama anualidad a una serie de pagos constantes en
efectivo que se hacen durante un periodo continuo. Por ejemplo: un
prstamo para comprar un artefacto elctrico, un automvil o la
hipoteca por una propiedad inmueble, etc. 26Instituto de Informtica
- INFOPUC Mdulo 1 Figura 33: Argumentos de la funcin Valor actual
Qu observamos? Que el valor actual de esa inversin son los 11000
soles que colocamos como prstamo (valor actual despus de una serie
de pagos futuros) en el ejemplo de la funcin PAGO. Qu nos dice
esto? Pues que
finalmente,dependiendodelpuntodevistaydelosdatosquetengamos,
utilizaremos una de las funciones o la otra.
Cuandotengaelmontodelvalortotaldelprstamoyquierasaberel pago
mensualquetendraquehacer:utilicelafuncinPAGO.Perositieneel monto
mensual que le va a producir una inversin, utilice la funcin VA,
valor actual para calcular el monto total ahora del total de los
pagos futuros. 3.3Casos prcticos sobre el uso de funciones
financieras Aplicacin 1: Prstamo, veamos el siguiente ejemplo:
Figura 34: Caso: prstamo a 5 aos El total del prstamo es de 11 000
soles por el cual se cobrar una tasa o inters de 10% anual durante
5 aos. Se pide calcular la cuota constante que debe pagar para
cancelarelprstamo.Paraefectosdelejemplo,considerecomoperiodoaunao.
Como ilustracin adicional obtendremos el valor neto de la
amortizacin y del inters. 27Pontificia Universidad Catlica del Per
Apli cacin y diseo de hojas de cl culo en MS Excel - Nivel
intermedio
Conelpropsitodequelatablaseaeficienteypuedaservirparacualquiercaso
utilizaremos en las frmulas las referencias a celdas y no valores
como se ve en la figura: Figura 35: Orden en las frmulas 1.En
primer lugar calculamos el monto de la cuota fija mensual y como ya
dijimos, consideramos que el perodo es un ao. Para ello, utilizamos
la funcin PAGO
enlaceldaD8,observequeutilizamosreferenciasabsolutasporquevamosa
copiar la frmula hacia abajo y las celdas de los datos deben
permanecer fijas, ejemplo: $B$3. Como hemos colocado $B$2 positivo,
la cuota saldr negativa.
2.Luegocalculamoselintersmultiplicandoelsaldoporelintersquees
10%=0.1, se entiende que para el primer periodo el saldo es toda la
deuda, que hemos colocado en la celda E7 (igual al principal).
3.Calculado el inters, hallamos el valor de la amortizacin (el
valor de la deuda que se est pagando) sin tomar en cuenta el inters
que va para el prestamista o
laentidadbancaria.Paraello,restaremoselvalordelintersdelmontodela
cuota. 4.Finalmente el saldo ser lo que an queda de deuda, es
decir, para el primer perodo, el principal menos lo que se est
amortizando en ese periodo. Veamos los resultados de esta primera
fila: Figura 36: Resultados para el primer periodo
Aqudebemosinterpretarqueal
finaldelprimerperiodo(pagodelaprimeracuota)
andebemosS/.9,198.23ydelosS/.2,901.77quehemospagadoenlaprimera
cuota, S/. 1,801.77 corresponden a principal y el resto: S/.
1,100.00 corresponden al inters. Si hubiramos colocado $B$2
negativo en la frmula de la funcin PAGO la cuota saldra positiva y
ya no se pondra -D8 en la frmula de la amortizacin sino solo D8.
Usted puede escoger cualquiera de las dos formas. 1234 28Instituto
de Informtica - INFOPUC Mdulo 1 Ahora, al copiar la frmula hacia
abajo tenemos: Figura 37: Frmulas para todos los periodos
Paravisualizarlafrmulaenunacelda,hagaclicenelcono Mostrar frmulas y
en vez del resultado ver la frmula, esto puede ser
ilustrativocuandoqueremosobservarelprocesoglobalcomoenla figura 36.
Vea el resultado desactivando el cono nuevamente.
Losresultadosfinalessemuestranenlafigurasiguienteyparacomprobarlos
resultados sumamos las columnas y comprobamos que al final de los
periodos el saldo es cero y el inters recibido por la entidad
prestadora es en total S/. 3,508.86. Figura 38: Resultados finales
La ventaja de trabajar con referencias es que con solo cambiar los
datos en las celdas B2:B4 los resultados cambian automticamente.
Trate de reproducir el ejemplo en una hoja de clculo y ahora pruebe
cambiando cualquiera de los datos. 29Pontificia Universidad Catlica
del Per Apli cacin y diseo de hojas de cl culo en MS Excel - Nivel
intermedio Es conveniente recordar que si la cuota es mensual hay
que dividir la tasa anual entre 12 y multiplicar los aos por 12
(nmero de meses total). Sobre el argumento tipo tenemos que decir
quesielpagovencealiniciodelperiodo (caso no muy comn) tenemos que
poner 1 eneseargumento,yparaelcasodel
ejemploanteriorlacuotaopagomensual sera como se muestra en la
figura, observe la frmula en la barra de frmulas: Como es lgico, la
cuota es menor, dado que se est pagando ANTES, el inters es menor
ylosvaloresamortizadosenestecasose calculan con otras funciones ms
avanzadas, porque tambin hay casos de intereses por periodos
menores o fracciones. Pero esto es tema de cursos avanzados de
aplicaciones financieras. Aplicacin 2: Ahorro para alcanzar un
monto en un determinado nmero de aos.
Enestecasoqueremoscalcularcuntotendramosqueahorrarmensualmente
durante10aosparaobtenerfinalmente S/.80,000 soles en una entidad
financiera que est dispuesta a pagar el 6.5% anual. Los datos seran
como muestra la figura: La funcin PAGO en esta oportunidad sirve
para hallar el monto del ahorro mensual. Figura 39: Ahorro mensual
con PAGO La frmula para calcular VA en la celda B23 sera: Figura
40: Frmula de PAGO con vf El resultado en la celda B44, nos dar el
monto mensual que tenemos que ahorrar, el cual es: 30Instituto de
Informtica - INFOPUC Mdulo 1 Figura 41: Ahorro mensual Ejercicio
sugerido 3:
Luegodehaberrevisadosumaterialdeestudio,leproponemosrealizarel
siguiente ejercicio:
Elaboreunatablasimilaraladelafigura37peroparadesarrollarlos
movimientos de la aplicacin 2 de las figuras 38 y 39. Obtenga las
frmulas paraelprimerperiodoyluegohalleparalos120periodosdelejemplo.
Compruebe los totales. 3.4Funciones Contar.Si y Sumar.Si Existen
dos funciones condicionales que son muy tiles porque nos permiten,
a travs de la verificacin de que se cumpla una condicin o prueba
lgica, indicarle a Excel
querealiceoperacionesbsicascomosumarycontar,estasfuncionesque
desarrollaremos a continuacin, son las siguientes: CONTAR.SI
SUMAR.SI LasfuncionesSUMAR.SIyCONTAR.SIlepermitirnsumarocontar
celdas de un rango solo si cumplen con una condicin predeterminada,
seimaginaentoncescmoseincrementanotablementelacantidadde
combinaciones,puesto quelacondicinpuede serelresultadodeuna funcin
u otra operacin. 3.4.1SUMAR.SI(rango;criterio;rango_suma)
Sumalasceldasdentrodeunrangoquecumplenconunadeterminada condicin o
criterio. Rango es el rango de celdas que desea evaluar.
Criterioeselcriterioenformadenmero,expresinotextoque
determinaquceldassevanasumar.Porejemplo,elargumento criterio puede
expresarse como 32; "32"; ">32"; "manzanas", etc.
Rango_sumasonlasceldasquesevanasumar.Lasceldas contenidas en este
rango se suman slo si las celdas correspondientes del rango
coinciden con el criterio. Si rango_suma se omite, se suman las
celdas contenidas en el argumento rango. 31Pontificia Universidad
Catlica del Per Apli cacin y diseo de hojas de cl culo en MS Excel
- Nivel intermedio Usted puede utilizar la funcin SUMAR.SI para
sumar datos con una caractersticaen comn, por ejemplo, si quiere
obtener el montototaldelasdeudasdelosrecibosqueseencuentran
vencidosdentrodeunalistadondeseencuentranrecibos vencidos y por
vencer. 3.4.2CONTAR.SI (rango;criterio) Cuenta las celdas, dentro
del rango, que no estn en blanco y que cumplen con el criterio
especificado. Rango es el rango dentro del cual desea contar el
nmero de celdas que no estn en blanco.
Criterioeselcriterioenformadenmero,expresinotextoque
determinalasceldasquesevanacontar.Porejemplo,elargumento criterio
puede expresarse como 32; "32"; ">32" o "manzanas". Ilustraremos
ambas funciones con un ejemplo: la tabla que se muestra contiene
los datos de vuelos a diferentes lugares y sus respectivos importes
en $: Figura 42: Tabla de vuelos Se pide llenar la tabla de resumen
con los valores de cuntos vuelos y el importe total para cada
destino: UsaremoslasfuncionesCONTAR.SIySUMAR.SI para obtener lo
solicitado segn el lugar de destino. Figura 43: Tabla resumen
32Instituto de Informtica - INFOPUC Mdulo 1 El procedimiento es el
siguiente:
1.Siobservalafigura42,tenemosqueevaluarlacolumnaEparacontarlos
destinos y sumar los importes de la columna G.
2.EnlaceldaF20delatablaresumeninsertamoslafuncinCONTAR.SIe
ingresamos los argumentos as: Figura 44: Funcin CONTAR.SI 3.Observe
que el criterio Lima se pone entre comillas porque es un texto. Si
no se hace, Excel igual entender que es un valor de texto. Haga
clic en Aceptar. 4.El resultado ser el nmero de vuelos cuyo destino
es Lima: dos (2). 5.En vez de escribir como criterio el valor de
texto Lima, en este caso se puede colocar la celda que lo contiene
en la tabla resumen, es decir E20.
6.Observequeenesecaso,siutilizamosademsreferenciasabsolutasparael
rangoaevaluar(E3:E17),podemoscopiarlafrmulahaciaabajo,haciendo
nuestro trabajo ms eficiente. Figura 45: Funcin CONTAR.SI con
referencias absolutas y rel ativas
7.Procedadeformasimilarparahallareltotaldelimporteen$conlafuncin
SUMAR.SI(queseencuentradentrodelacategoradelasfunciones matemticas
y trigonomtricas) de la siguiente manera: 33Pontificia Universidad
Catlica del Per Apli cacin y diseo de hojas de cl culo en MS Excel
- Nivel intermedio Figura 46: Funcin SUMAR.SI con referencias
absolutas y relativas 8.Copie hacia abajo la frmula y obtendr lo
siguiente: Figura 47: Resultado de SUMAR.SI Si tuviramos que
calcular el promedio en el ejemplo anterior podramos dividir el
importe obtenido entre el conteo (nmero de boletos) para cada
destino,yaqueestaramosaplicandoladefinicindepromedio.
Afortunadamente Excel 2007 tiene esta operacin incorporada como la
funcin PROMEDIO.SI. Como ejercicio hgalo para el ejemplo anterior.
SepuedeutilizarlafuncinCONTAR.SI,sisedeseaencontrarel nmero de
participantes de sexo femenino en un listado mixto. Ejercicio
sugerido 4: Luego de haber revisado su material de estudio, le
proponemos realizar el siguiente ejercicio:
AbraelarchivoCOBERTURADECLIENTES(elmismoqueeldel ejercicio sugerido
1) y realice lo indicado en la hoja Instrucciones_3. 34Instituto de
Informtica - INFOPUC Mdulo 1
Tema4:Estrategiasenelusoeficientedefrmulasy funciones: funciones
anidadas 4.1Qu son funciones anidadas?
ElMSExceladmitecrearfrmulaspararealizarclculoscomplicados.Endichas
frmulasloquesehace,generalmente,esanidarfunciones;aslosclculosse
puedentrabajarenunasolacolumnaynoenvariascomoserasiutilizamoslas
funciones una a una independientemente.
Enrealidad,enlosacpitesanterioreshemosvenidoaplicandolaanidacinde
funciones como en la frmula de la figura 9 del acpite 1.3 donde se
anid la funcin
SIconlafuncinYyseexplicdetalladamenteelprocedimiento.Ahora
presentaremosunejemplodelafuncinPROMEDIOanidadaconlafuncin
REDONDEO: Figura 48: Anidar funciones
Recuerdequeustedcrealasfuncionesanidadassobrelabasedelas funciones
simples que usted ya maneja. Los niveles a los cuales puede llegar
anidando funciones depender de la necesidad y de la prctica que
alcance con ello. Como podemos deducir, las funciones anidadas son
aquellas que se combinan en una sola frmula para poder realizar
acciones u operaciones ms complicadas y que
utilizadasjuntashacenquesemanejemseficientementelahojadeclculo,
ahorrando tiempoyhaciendoqueseamsverstil,puesto
quepermitecambiarlas variables para lograr un clculo dinmico como
en el caso de las frmulas que utilizan
referenciasabsolutasyrelativasparapodercopiarlasfrmulas.(Verfrmulasdel
ejemplo del acpite 3.4). 35Pontificia Universidad Catlica del Per
Apli cacin y diseo de hojas de cl culo en MS Excel - Nivel
intermedio 4.2Combinar funciones de bsqueda y condicionales
Unadelasventajasdecombinarlasfuncionescondicionalesconlasfuncionesde
bsqueda es permitir completar datos en una tabla segn se cumpla una
condicin o no, as podemos discriminar el tipo de informacin y
ampliar la gama de posibilidades
puestoquepodemosinclusoapartirdelosresultadosdeloscondicionalesrealizar
otras funciones adems de la bsqueda.
Ilustraremoslodichoconunejemplo:setiene
unatablaconlosrepresentantesdel rea de logstica y lo que se quiere
obtener: Figura 49: Completar datos con funciones anidadas
1.Completar la columna G con el obsequio correspondiente, de
acuerdo al sector
denegocioysegnlatablaquesemuestraacontinuacin,dondepodemos observar
que el obsequio depende del monto de cada representante. 2.Hemos
llamado a la tabla de criterios como Obsequios para la frmula.
Figura 50: Tabla con los criterios de bsqueda 3.Si el monto es
menor a 600 soles el representante no recibe obsequio y debe salir
el mensaje No aplica. 4.Situados en la celda G2 insertamos la
funcin condicional SI donde evaluaremos
silacondicinpararecibirunobsequiosecumple;observequepodemos
plantear la condicin de dos maneras: a)si el monto es menor que 600
coloca el mensaje No aplica, pero si no lo es entonces Busca b)si
el monto es mayor que 600, entonces Busca y si no, coloca el
mensaje. 36Instituto de Informtica - INFOPUC Mdulo 1
5.Laformaqueescojadependersolodeusted,puesambas,lgicamente,
funcionan igual, solo vara el orden de las funciones y sus
argumentos. Figura 51: Insertando la primera funcin SI
6.CuandollegamosaltercerargumentoValor_si_falso,debemosvolvera
preguntar con otra funcin SI anidada: Figura 52: Insertando la
segunda funcin SI 7.En la segunda funcin SI evaluamos la condicin
para buscar el obsequio la cual
essielmontoesmayora1500onoparahacerlabsquedaconlafuncin BUSCARV:
37Pontificia Universidad Catlica del Per Apli cacin y diseo de
hojas de cl culo en MS Excel - Nivel intermedio Figura 53:
Argumentos de la segunda funcin SI 8.Si el monto es mayor que 1500
el obsequio est en la columna 3 a partir de la primera columna de
la tabla de criterios Obsequios de la figura 50 Figura 54:
Argumentos de BUSCARV 9.Si no lo es buscar en la columna 4 as:
Figura 55: Segunda funci n SI compl etada 38Instituto de Informtica
- INFOPUC Mdulo 1 10.Ahora todos los argumentos estn completos,
entonces hacemos clic en Aceptar y el resultado se copia a las
filas inferiores: Figura 56: Resultado final Ntese que aqu hemos
anidado tres niveles de funciones: SI, SI y BUSCARV. Si observamos
la frmula final, esta tambin podra haberse escrito directamente, lo
cualselograconlaprcticayconocimientodelasintaxisdelasfunciones.Los
parntesis delimitan los argumentos de las funciones y hay tantos
que abren como los que cierran. Acorta considerablemente la
longitud de la sintaxis de la frmula el llamar a la matriz de
bsqueda con un nombre.
=SI(F21500,BUSCARV(B2,Obsequios,3,FALSO),BUSCARV(B2,Obsequios,4,FALSO)))
Piense en la posibilidad de que una condicin adicional pudiera ser
que solo habr obsequio si el monto es mayor que 600 y si es con
factura. 4.3Casos prcticos sobre el uso de funciones anidadas
Comoyadijimos,elniveldeaplicacindelasfuncionesanidadasdependerdela
complejidad de las condiciones del clculo y de la naturaleza de los
datos. Teniendo en cuenta que hay varias categoras de funciones,
podremos anidar combinaciones de funciones lgicas con financieras o
lgicas con las de texto, etc. An si no intervienen
lasfuncioneslgicasexistirnsituacionesenlasquetendremosqueaplicaruna
funcin sobre otra, como en el acpite 4.1.
Enresumen,laanidacindelasfuncionesdependerdelascondicionesdel
problema, y como hemos visto hasta ahora siempre existirn ms de una
solucin al problema, es decir una frmula, u orden en la anidacin.
Al igual que las leyes de la lgica y de la matemtica en el orden de
los factores, cada usuario le aplicar un toque personal al diseo de
frmulas y de la solucin en s. 39Pontificia Universidad Catlica del
Per Apli cacin y diseo de hojas de cl culo en MS Excel - Nivel
intermedio
Acontinuacin,presentamosalgunassituacionesdondesecombinandiferentes
funciones para lograr diversos resultados. Usted puede colocar
estas situaciones en una hoja de clculo y practicar para adquirir
la destreza necesaria. La situacin es la siguiente: Figura 57:
Completar segn rango de notas Veamos las frmulas utilizadas en el
clculo del promedio de prcticas: Figura 58: Uso de PROMEDIOA
Observe que se ha utilizado la funcin PROMEDIOA en vez de solo
PROMEDIO, ya que ante la posibilidad de la presencia de valores de
texto como F, los cuales deben ser considerados en el promedio, es
necesaria esa funcin. Figura 59: Clculo de un promedio ponderado
SiqueremosqueunvalordetextoseaconsideradocomoCeroparaelclculode
operacionesyfuncionesdeclculodebemosutilizarlafuncinN,comolohemos
hecho para el clculo de la nota final, que es un promedio ponderado
(ubquela en la categora de informacin: Tabla de criterios Ojo
40Instituto de Informtica - INFOPUC Mdulo 1 Figura 60: Uso de la
funci n N Ahora veamos cmo se han completado las columnas I y Jde
acuerdo a los criterios de la tabla que se encuentra en el rango
A13:C17. Para el calificativo tenemos que evaluar en qu rango de
notas se encuentra la nota final para colocar el calificativo:
Figura 61: Anidacin de funcin SI en tres niveles
Anlogamenteprocedemosconobtenerelcomentario,paralocualutilizamosel
resultado del calificativo y no los rangos (usted puede optar por
ello). Figura 62: BUSCARV para obtener el comentario
Observeenlafiguraanteriorla formadedenotarelrangodela tabla de
criterios. Esto se obtuvo al darle un formato predeterminado a
latabla,rangodedatos,ycomo tienertulosdecolumnas: Calificativo y
Comentario, entonces se denota de esa manera.
Tambinpuedenombrarlacomo vimos en ejemplos anteriores. Figura 63:
Formato automtico de tabl a 41Pontificia Universidad Catlica del
Per Apli cacin y diseo de hojas de cl culo en MS Excel - Nivel
intermedio Para que aparezca el mensaje si rinde rezagados o no la
frmula adems indica que si no rinde el rezagados no coloca ningn
texto que se denota as: . Figura 64: Frmula para el mensaje
Finalmente el resultado es el siguiente: Le sugerimos que revise el
Demo Aplicacin de funciones anidadas, el cual se encuentra en la
plataforma educativa Paideia PUCP. Ejercicio sugerido 5:
Luegodehaberrevisadosumaterialdeestudio,leproponemosrealizarel
siguiente ejercicio: Replique en una hoja de clculo el ejemplo del
acpite 4.2
yrealiceelmismoejemplo.Luegoaumenteunacondicinadicionalpara recibir
un obsequio: Adems de que el monto debe ser mayor que 600 debe ser
por una factura y no por boleta.
Luegodehaberrevisadosumaterialdeestudio,leproponemosrealizarlas
actividadespropuestasparaestemdulo.Paraello,ingresealaplataforma
educativaPaideiaPUCP,enellaencontrarlasindicacionesylafecha
programada3 de las actividades del mdulo. 3 La fecha la encontrar
en el calendario de actividades del curso.
