CLASE 4

.

Esta Herramienta le ayudar en la toma de decisiones de proyectos de inversin y activos en

su empresa.

Sesin 04 - 1

SESION 04

PPRROOCCEESSAANNDDOO FFEECCHHAASS

Generalmente se suele necesitar procesar fechas, por ejemplo, cuando se requiere

determinar edades, tiempo en que una deuda no se ha pagado, das transcurridos, etc.

Para poder realizar estas operaciones debemos tener en cuenta que, las fechas son

datos numricos y por lo tanto pueden sumarse, restarse y realizar con ellas cualquier

operacin aritmtica que se requiera.

Veamos el siguiente ejercicio

Microsoft Excel almacena las fechas como nmeros de serie

secuenciales para poder realizar clculos con ellos. Excel almacena la

fecha 1 de enero de 1900 como el nmero de serie 1 si el libro utiliza

el sistema de fechas 1900; pero si se utiliza el sistema de fechas 1904,

Excel almacena la fecha 1 de enero de 1904 como el nmero de serie 0 (2 de enero de

1904 es el nmero de serie 1). Por ejemplo, en el sistema de fechas 1900, Excel

almacena 1 de enero de 1998 como nmero de serie 35796 porque es 35.795 das

posterior al 1 de enero de 1900.

CALCULANDO LA EDAD Para el clculo de la edad observars que si restamos a la

fecha de hoy la fecha en que la persona naci obtendramos la

cantidad de das transcurridos desde la fecha en que naci la

persona a la fecha de hoy, entonces usamos nuestra

matemtica para definir la edad, as:

Si 1 ao equivale a 365 das, entonces podemos

determinar cuantos aos tiene la persona si se tiene cuantos das de vida tiene con slo

efectuar una regla de tres simple.

Sesin 04 - 2 -

Por lo tanto la frmula para calcular la edad del empleado Alor Cortez Fernando ser :

($B$1-B5)/365 y para el resto de los empleados usamos la tcnica de copiar.

Por qu usamos $ en la direccin B1 ? Qu ocurre si no usamos $ en la direccin B1 ? OBSERVACIN MUY IMPORTANTE:

Cuando se operan fechas en muchas ocasiones el resultado es otra fecha, y como es lgico, ese no es el resultado que esperamos, por lo tanto tienes que usar tus conocimientos en FORMATOS para cambiar este resultado de fecha a un valor numrico.

CALCULANDO LOS AOS QUE LLEVA TRABAJANDO Como seguramente te habrs dado cuenta el proceso es similar, as para determinar el

nmero de aos que lleva laborando usaremos la fecha de hoy y le restamos la fecha

de cuando ingres a trabajar. Por lo tanto la frmula para el empleado Alor Cortez

Fernando quedara as:

($B$1-C5)/365 CALCULANDO LOS AOS EN QUE LLEVA NOMBRADO En este caso basndonos en las dos frmulas anteriores la frmula para el empleado

Alor Cortez Fernando quedara as:

($B$1-D5)/365

Sesin 04 - 3 -

LABORATORIO DE PROBLEMAS INSTRUCCIONES

POR CADA UNO DE LOS PROBLEMAS, INGRESE 15 REGISTROS. ES IMPORTANTE DAR FORMATO A CADA UNO Y PROTEGER LAS CELDAS DE

INFORMACIN. NO OLVIDES EMPLEAR DIRECCIONES ABSOLUTAS CUANDO SE INDIQUE. 1. Una empresa tiene el problema constante de calcular moras por las deudas no

pagadas en su debido momento. Para ello, te ha solicitado elabores una hoja de clculo que consigne los siguientes datos e informacin:

Nombre del deudor Monto de la deuda Fecha de vencimiento (fecha en que debi pagar la deuda) Fecha de pago (fecha en que pag la deuda) Das de mora (das que han transcurrido desde la fecha en

que debi pagar y la fecha en que realmente pag)

Mora ( se aplica 5 soles por cada da de mora. Monto total (la suma de la deuda ms la mora)

Se solicita adems el total general del monto de la deuda, de las moras y del monto total. Usa el valor de 5 soles como direccin absoluta.

2. Una empresa desea establecer el inters por ahorros que va a otorgar a cada uno de sus clientes. Para ello te ha solicitado elabores una hoja de trabajo que permita determinar el inters basado en los das en que mantuvo el dinero ahorrado. Asume que cuando se retira ahorros, se retira todo el dinero. El formato que la empresa te da es el siguiente:

Nombre del cliente Monto de dinero depositado Fecha de depsito Fecha de retiro (fecha en que retir el dinero) Das transcurridos (das que han transcurrido desde que deposit hasta

que retir el dinero) Inters (monto de dinero que equivale al 2% por cada da transcurrido) Monto a retirar (la suma del dinero depositado ms el inters)

Se solicita tambin, el total general de dinero depositado, de intereses generados y total de dinero retirado. Usa el 2% como direccin absoluta.

GLOSARIO DE TERMINOS:

Libro de trabajo Es el nombre con el que se conoce al archivo en el que se trabaja y almacena datos.

Hoja de Clculo Hoja de trabajo- formada por filas y columnas.

Referencia Una referencia identifica una celda o un rango de celdas en una hoja de clculo.

Frmulas Una expresin que calcula algo, usualmente basada en los valores contenidos en las celdas.

Sesin 02 - 1

SESION 02

Objetivo:

Conocer el concepto y la aplicacin de las principales funciones de uso comn en el

entorno Ms Excel.

Definicin y aplicacin de las funciones en Excel. Las funciones son herramientas especiales que permiten clculos complejos de forma fcil y rpida. Son

como las teclas especiales de las calculadoras sofisticadas que calculan races cuadradas, logaritmos y

evaluaciones estadsticas.

Microsoft Excel dispone de ms de 300 funciones predefinidas que ejecutan un amplio rango de clculos.

Introduccin de Funciones en una hoja de Clculo:

Puede introducir funciones en una hoja escribiendo la funcin desde el teclado o escogiendo la orden funcin del Men Insertar. Si escribe la funcin, utilice minsculas. Cuando acabe de

teclear la funcin pulse enter o seleccione otra celda, Microsoft Excel cambiar el nombre de la

funcin a maysculas si la introdujo correctamente. Si las letras no se cambian es que probablemente habr introducido el nombre de la funcin de forma incorrecta.

Observemos Figura 1.8. Cuadro de dilogo Pegar funcin le ayudar a introducir los argumentos para la

funcin.

AAPPLLIICCAACCIIOONNEESS BBAASSIICCAASS DDEE LLAA HHOOJJAA

DDEE CCLLCCUULLOO

Seleccionar la funcin a emplear.

Sesin 04 - 2 -

FUNCIONES MATEMTICAS:

FUNCION SUMA: La funcin SUMA totaliza una serie de nmeros.

Sintaxis: =SUMA(nmeros)

Nmeros Es una serie que puede contener hasta 30 datos que pueden ser nmeros,

frmulas, rangos o referencias de celdas que produzcan nmeros.

FUNCION SUMAR.SI: La funcin SUMAR.SI suma las celdas que cumplen determinado criterio o condicin.

Sintaxis: =SUMAR.SI(rango,criterio,rango_suma)

Rango Es el rango de celdas que desea evaluar.

Criterio Es el criterio o condicin que determina qu celdas deben sumarse.

Rango_Suma Son las celdas que se van a sumar.

Problema: Se desea calcular la Sumatoria de las aportaciones de una AFP en particular

siendo esta Integra.

Observemos

La frmula a ingresar es la siguiente:

=SUMAR.SI(A2:A6,"=Integra",B2:B6)

FUNCION REDONDEAR: La funcin REDONDEAR redondea el nmero referido por su argumento a un nmero especificado de

cifras decimales.

Sintaxis: =REDONDEAR(nmero,nmdedecimales)

Nmeros Es una serie que pueden ser nmeros, frmulas, rangos o referencias de

celdas que produzcan nmeros.

Nmdedecimales El cual puede ser cualquier entero positivo o negativo, determina cuantos

lugares se redondearn.

Nota

La especificacin de un argumento nmdedecimales negativo, redondea ese nmero de cifras hacia la izquierda de la coma decimal, y la especificacin de dicho argumento como 0 redondea el entero ms prximo.

Observemos

Entrada Valor Calculado

=redondear(123.4567,-2) 100 =redondear(123.4567,-1) 120 =redondear(123.4567,0) 123

=redondear(123.4567,1) 123.5

Sesin 02 - 3 -

FUNCION ENTERO Y TRUNCAR: La funcin ENTERO redondea nmeros hasta el entero inferior ms prximo.

Sintaxis: =ENTERO(nmero)

Nmeros Es el nmero, para el cual desea encontrar el prximo entero ms bajo.

La funcin TRUNCAR trunca todo lo que se encuentra a la derecha de la coma decimal del nmero, sin importar su signo y sin redondear. Sintaxis: =TRUNCAR(nmero,nmdedecimales)

Nmeros Es una serie que pueden ser nmeros, frmulas, rangos o referencias de

celdas que produzcan nmeros.

Nmdedecimales El cual puede ser cualquier entero positivo o negativo, determina cuantos

lugares se redondearn.

FUNCIONES LOGICAS:

FUNCION SI: La funcin SI devuelve un nico si una condicin especificada se evala como verdadera y otro valor si

se evala como falsa.

Sintaxis: =SI(Prueba_lgica,valor_Si_Verdadero,valor_Si_Falso)

Prueba lgica Es cualquier valor o expresin que pueda evaluarse como Verdadero

o falso

Valor Si Verdadero Es el valor que se devolver si prueba lgica es Verdadera, si se

omite, devolver verdadero.

Valor Si Falso Es el valor que se devolver si prueba lgica es Falso, si se omite devolver Falso.

Problema : En el ejemplo se determina que se pagaran comisiones para un determinado

monto de ventas realizadas, y en algunos casos ninguna segn sea la

siguiente tabla.

Ventas inferiores a S/.20,000 no se paga comisin Ventas superiores a s/.20,000

pero inferiores a S/.30,000 un 1.8% de comisin.

Ventas superiores a S/.30,000

un 4% de comisin.

Observemos

la frmula a introducir es la siguiente:

=SI(A2

Sesin 04 - 4 -

=CONSULTAH(valor_buscado,matriz_buscar,indicador_filas,ordenado)

VVaalloorr bbuussccaaddoo Indicamos el valor a buscar

Matriz buscar Es una matriz o nombre de rango que define la tabla

Indicador columna

o fila

Designa la columna o fila de la tabla

ordenado Valor lgico que determina el ordenamiento de la tabla.

FUNCIONES DE FECHA Y HORA Las funciones de fecha y hora de Ms Excel permiten realizar clculos en la hoja de forma rpida y precisa. Por ejemplo, si utiliza la hoja de clculo para obtener las nminas mensuales de su empresa, puede emplear la funcin HORA, determinar el nmero de horas trabajadas cada da, etc.

FUNCION FECHA: La funcin bsica de FECHA, se puede emplear para introducir una fecha en una celda.

Sintaxis: =FECHA(ao,mes,da)

Ao Es un nmero entre 1900 y 9999 en Microsoft Excel para Windows.

Mes Es un nmero entre 1 y 12 que representa el mes del ao.

Da Es un nmero entre 1 y 31 que representa el da del mes. Observemos Por ejemplo, si emplea la frmula. =FECHA(97,6,19)

Para introducir la fecha 19 de junio de 1997, el resultado es el nmero de serie 35600, que Excel muestra como 19/6/97.

FUNCION HOY: La funcin HOY, es una variante especial de la funcin FECHA. Con la diferencia que siempre devuelve

el nmero de serie del da actual.

Sintaxis: =HOY() Aunque esta funcin no tiene argumentos, no se debe olvidar los parntesis vacos. FUNCIONES DE TEXTO Las funciones de texto convierten datos numricos de tipo texto en nmeros y datos numricos en cadenas de texto, y le permiten manipular las cadenas de texto entre si.

FUNCION MAYUSC, MINUSC Y NOMPROPIO: Hay tres funciones que manipulan el tipo de caracteres de las cadenas de texto. La funcin MAYUSC

convierte a maysculas todos los caracteres de una cadena de texto. La Funcin MINUSC convierte a

minsculas todos los caracteres de una cadena de texto y la funcin NOMPROPIO pone en mayscula la

primera letra de cada palabra.

Sintaxis: =MAYUSC(texto)

=MINUSC(texto)

=NOMPROPIO(texto)

Texto Es el texto o cadenas de texto a convertir.

FUNCION DERECHA E IZQUIERDA: La funcin DERECHA devuelve la serie de caracteres ms a la derecha de un argumento de cadena, mientras que la funcin IZQUIERDA devuelve la serie de caracteres ms a la izquierda de un argumento de cadena.

Sintaxis: =DERECHA(texto,nm_de_caracteres)

Sesin 02 - 5 -

=IZQUIERDA(texto,nm_de_caracteres)

Texto Es el texto o cadenas de texto a convertir.

Nm_de_caracteres Indica el nm de caracteres a extraer del argumento texto.

FUNCION CONCATENAR: Utilizar la funcin COCATENAR equivale a utilizar el carcter & para obtener cademas ms grandes a partir de cadenas ms pequeas.

Sintaxis: =CONCATENAR(texto1,texto2,...)

GLOSARIO DE TERMINOS:

funcin una expresin matemtica. En el caso de una hoja de clculos, una funcin siempre comienza con el signo igual, =

Referencia Una referencia identifica una celda o un rango de celdas en una hoja de clculo.

Frmulas Una expresin que calcula algo, usualmente basada en los valores contenidos en las celdas.

BIBLIOGRAFIA:

Guia de Microsoft Excel (Pablo Wong Dvila)

RUNNING Microsoft Excel (Mark Dodge, Chris Kinata y Craig Stinson)

Sesin 02

TABLA DE FUNCIONES

Fecha y Hora

Funcin Descripcin Sintaxis Ejemplo

Ao Permite mostrar el ao, correspondiente de una fecha. =Ao(nm_de_serie) =Ao(f4)

Hoy Permite mostrar la fecha Actual. =hoy() =Hoy()

Matemticas y Trigonomtricas

Redondear Permite redondear un nmero, especificando el nm. De decimales.

=Redondear(nmero,nmero_decimales) =Redondear(A5,1)

Suma Suma todos los valores de un rango de celdas =Suma(nmero1,nmer2) =Suma(J5:J19)

Sumar.si Suma determinadas celdas que cumplen una condicin o

criterio.

=Sumar.si(rango,criterio,rango_suma) =Suma(G25:H29,=Integra,H25:H29)

Producto Multiplica todos los nmeros especificados como

argumentos.

=Producto(nmero1,nmero2) =Producto(A15:A16)

Trigonomtricas

Contar Cuenta los nmeros de celdas que contengan nmeros y los nmeros de la lista de argumentos

=Contar(ref1,ref2) =Contar(A8:A18)

Contar.si Cuenta las celdas en el rango que coincidan a la

condicin dada

=Contar.si(rango,criterio) =Contar.si(B10:B18,>=15)

Contara Cuenta las celdas no vacas y los valores que hay en la lista de argumentos.

=Contara(valor1,valor2) =Contara(J25:J33)

Max Devuelve el valor mximo de una lista de valores =Max(nmero1,nmero2) =Max(D15:D22)

Min Devuelve le valor mnimo de una lista de valores =Min(nmero1,nmero2) =Min(D15:D22)

Promedio Devuelve el promedio(media aritmtica) de los

argumentos

=Promedio(nmero1,nmero2) =Promedio(F10:F15)

Sesin 02 - 7 -

TABLA DE FUNCIONES

Bsqueda y Referencia

Funcin Descripcin Sintaxis Ejemplo

CONSULTA

V

Busca un valor en la primera columna de la izquierda de

una tabla y luego devuelve un valor en la misma fila

desde una columna especificada.

=CONSULTAV(valor_buscado,matriz_buscar_

en

_indicador_columnas,ordenado)

=CONSULTAV(A2,A18:C25,2,0)

CONSULTA

H

Busca un valor en la primera fila de una tabla o matriz

de valores y devuelve el resultado en la misma columna

desde una fila especificada.

=

CONSULTAV(valor_buscado,matriz_buscar_e

n _indicador_filas,ordenado)

=

Lgicas

Si Comprueba si cumple una condicin y devuelve un valor

si se evala como VERDADERO y otro valor si se evala como FALSO

=Si(prueba_lgica,valor_si_verdadero,valor_

si_falso)

=Si(B26>=3000,10%*B26,)

Sesin 03

8

SESION 03

FORMATO CONDICIONAL Y VALIDACIN DE DATOS FORMATO CONDICIONAL Supongamos que al aplicar los formatos deseas, por la naturaleza

del problema, aplicarlos de acuerdo a su valor, por ejemplo un

valor menor a 200 que aparezca de color rojo Cmo hacerlo? te

lo explicamos. Antes que nada debes acordarte siempre que si

deseas aplicar formato condicional debes seguir los siguientes

pasos:

1. Seleccionar las celdas a afectar

2. Ingresar al men FORMATO y opcin FORMATO

CONDICIONAL

3. Indicar las condiciones.

Vamos a explicarlo usando un ejemplo: supongamos que

tenemos los datos de las ventas de la figura que se muestra, y

deseamos que las ventas mayores a 4000 soles aparezcan en color AZUL y en

negrita, las ventas entre 2500 y 4000 soles en color rojo con un fondo de color

amarillo y si es menor que 2500 en color rojo.

Cumplido el paso nmero 1, ingresamos a men FORMATO y la opcin FORMATO

CONDICIONAL.

La computadora mostrar el siguiente cuadro de dilogo:

Pulsa aqu para

escoger la condicin

Pulsa aqu para

escoger el formato

Pulsa aqu para agregar

mas condiciones

PPRROOTTEECCCCIIOONN YY VVAALLIIDDAACCIIOONN

Sesin 04 9

En muchas ocasiones nos encontramos con la necesidad de que los datos que se ingresen en

una determinada celda cumplan requisitos, como por ejemplo, ser nmeros enteros, reales, textos, estar en una rango determinado o pertenecer a una lista especfica. Para lograrlo, debemos usar la opcin VALIDACIN del men DATOS. Vamos a explicarte como hacerlo: Tomemos el siguiente diseo:

Para efectos de aplicar los requerimientos, en este ejercicio recomendamos agregar a este diseo los siguientes datos:

Para lograr lo que nos proponemos sigue los siguientes pasos:

1. Selecciona el conjunto de celdas que van a recibir los nombres. Digamos A4:A15.

2. Ingresa a la opcin Validacin del men Datos, a lo cual la computadora responder con el siguiente cuadro de dilogo

Aqu, por ejemplo, los nombres pueden quererse tomar de una lista, la edad mnima debe ser 15 y la mxima 70, el sueldo mnimo 457 soles y el mximo 15000 (qu bonito sera ganar

esta cantidad verdad?) , la fecha de nacimiento entre el ao 1950 y 2020 y la observacin no debera admitir ms de 27 caracteres.

Esta lista

se usar

para los

nombres

Estos para las edades y

los sueldos

Escoge esta opcin para permitir manejar listas

Selecciona este rango para indicarle al ordenador de donde

tomar los elementos de la lista

Sesin 03 10

Terminado este proceso, cada vez que te ubiques en una celda del rango establecido en el paso 1 se mostrar el siguiente aspecto

Para comprobar el efecto, da clic en el botn que se muestra en la celda A7 y este despliega lo siguiente:

Para el rango de la edad, ingresa a validacin y pon los siguientes datos:

Sin salir de la opcin ingresa a la FICHA MENSAJE DE ERROR y:

Esto ltimo va a tener el efecto de presentar un

mensaje de error cada vez que se escriba un dato fuera del rango.

Observa lo que se muestra al costado derecho de la celda A7

Escoge esta opcin para permitir slo

nmeros enteros

Escoge esta opcin

para manejar rangos

Estas opciones

representan los rangos mnimos y mximos

Escribe el mensaje que quieras por

ejemplo No est en el lmite establecido

Sesin 04 11

3. Seala el rango de sueldos e ingresa a la opcin Validacin de Datos e ingresa los siguientes datos.

Sin salir de la opcin ingresa a la FICHA MENSAJE ENTRANTE y:

4. Seala el rango para las fechas de nacimiento e

ingresa a la opcin Validacin de Datos.

5. Por ltimo seala el rango para las

observaciones y escoge dentro de la validacin longitud de texto, menor o igual que 27.

Escoge esta opcin

para permitir valores

numricos decimales

Escoge esta opcin para que se acepte rangos

Escoge los valores mnimo y mximo

Escribe el mensaje que va a aparecer cada vez que

ubiques el puntero de celda, por ejemplo Sueldo Base

Sesin 03 12

LA FICHA PROTEGER DEL CUADRO DE DILOGO DE FORMATO CELDAS

LA FICHA PROTEGER

Sirve para evitar que se realicen cambios o que no se vea el contenido de la celda. Seleccione BLOQUEADA para bloquear la celda y OCULTA para que su contenido no se vea. Sin embargo,

todo esto no basta, una vez que se selecciona bloqueada u oculta selecciona la opcin PROTEGER del men HERRAMIENTAS.

Adems de la proteccin mediante contraseas para los libros de trabajo, Excel2000 ofrece

varias rdenes para proteger las celdas del libro. Para ello tenemos que realizar dos

operaciones: la primera que consiste en proteger las celdas que no queremos que sufran

variaciones, y la segunda que consiste en proteger la hoja.

Cuando una celda est bloqueada no podr sufrir variaciones. Realmente por defecto todas las celdas estn protegidas o bloqueadas para que no sufran cambios, pero no nos

damos cuenta ya que la hoja no est protegida. Por lo tanto, lo que se debe hacer es desbloquear las celdas que queremos variar en algn momento. Para ello:

1 Seleccionar el rango de celdas que

queremos desbloquear para poder realizar variaciones.

2 Seleccionar el men Formato.

3 Elegir la opcin Celdas...

4 Hacer clic sobre la pestaa

Proteger.

Aparecer el cuadro de dilogo de la derecha:

5 Desactivar la casilla Bloqueada.

6 Hacer clic sobre el botn Aceptar.

Si se activa la casilla Oculta, lo que se

pretende es que la frmula o el valor de la celda no se pueda visualizar en

la barra de frmulas.

Las operaciones de la ficha Proteger no tienen efecto si no protegemos la hoja de clculo, por lo tanto a continuacin tendremos

que realizar los siguientes pasos:

1 Seleccionar el men Herramientas.

2 Elegir la opcin Proteger.

3 Seleccionar la opcin Proteger hoja....

Aparecer el cuadro de dilogo de la derecha:

5 Si queremos asignar una contrasea para que solamente pueda desproteger la hoja la persona que sepa la contrasea, escribirla en el recuadro Contrasea.

6 Hacer clic sobre el botn Aceptar.

Sesin 04 13

7 Si hemos puesto contrasea nos pedir confirmacin de contrasea, por lo tanto tendremos que volver a escribirla y hacer clic sobre el botn

Aceptar.

A partir de ahora la hoja activa se encuentra protegida, por lo que no se podrn modificar

aquellas celdas bloqueadas en un principio.

Si queremos desproteger la hoja, volveremos a realizar los mismos pasos que en la proteccin, es decir:

1 Seleccionar el men Herramientas.

2 Elegir la opcin Proteger.

3 Seleccionar la opcin Desproteger hoja....

4 Si habamos asignado una contrasea nos la pedir, por lo que tendremos que escribirla y hacer clic sobre el botn Aceptar. Si no haba contrasea asignada, automticamente la

desprotege.

Sesin 04 y 05

14