Upload
alecoxsdb
View
468
Download
4
Embed Size (px)
Citation preview
PAGINA- 1 -CAPITULO 6 – EXCEL 2007–
NOMBRE: ___________________________________ CURSO: _______________
COMPUTO – ALEX ZAMBRANO – SAN JUAN BOSCO
MANUAL
EXCEL 2007
CAPITULO 6
COLEGIO SAN JUAN BOSCO
GRADO: 11____
NOMBRE: __________________________________
SANTIAGO DE CALI, 2011-2012
PAGINA- 2 -CAPITULO 6 – EXCEL 2007–
NOMBRE: ___________________________________ CURSO: _______________
COMPUTO – ALEX ZAMBRANO – SAN JUAN BOSCO
6.1 Funciones de fecha y hora
6.1.1 Función Hoy
Esta función permite introducir la fecha del sistema en la celda activa. (Es decir mantiene actualizada la fecha, a medida que cambia de día)
=HOY ()
6.1.2 Función Fecha
Permite que el usuario introduzca una fecha cualquiera diferente a la del sistema.
=Fecha(Año,Mes,Día)
Año: Es el año correspondiente y puede estar entre 1900 y 9999
Mes: Es un número entre 1 y 12. Ejemplo: 4 corresponde al mes de Abril.
Día: Es un número entre 1 y 30 ó 31 dependiendo del mes. Ejemplo: 20, es el día 20 del mes.
La fecha quedaría de la siguiente forma:
=FECHA (96, 4,20) Corresponde al 20 de abril de 1996.
PAGINA- 3 -CAPITULO 6 – EXCEL 2007–
NOMBRE: ___________________________________ CURSO: _______________
COMPUTO – ALEX ZAMBRANO – SAN JUAN BOSCO
6.1.3 Función Hora
Permite introducir una hora determinada.
=nshora(hora,minutos,segundos)
Hora: Es un número entre 0 y 23. En donde los números entre 0 y 11 representan a.m.. Los
números entre 12 y 23 representan p.m..
Minutos: Es un número entre 0 y 59.
Segundos: Es un número entre 0 y 59.
La hora quedaría de la siguiente forma:
=NSHORA (15,30,00) Corresponde a las 3:30 p.m.
6.1.4 Función Ahora
Esta función introduce automáticamente en la celda activa la fecha y hora que tiene el sistema del computador. (Fecha y hora actual)
=AHORA ()
PAGINA- 4 -CAPITULO 6 – EXCEL 2007–
NOMBRE: ___________________________________ CURSO: _______________
COMPUTO – ALEX ZAMBRANO – SAN JUAN BOSCO
6.2 FUNCIONES FINANCIERAS
6.2.1 FUNCION PAGOINT
Devuelve el interés pagado en un período específico (normalmente en un mes) por una inversión (un préstamo) basándose en pagos periódicos constantes (normalmente mensuales) y en una tasa de interés constante.
Sintaxis
PAGOINT(tasa;período;nper;va;vf;tipo)
Tasa: es la tasa de interés por período. (% cobrado mensual, si el periodo es un mes)
Período: es el período para el que se desea calcular el interés y deberá estar entre 1 y el número
nper. (Último periodo)
Nper: es el número total de períodos de pago en una anualidad (plazo del préstamo).
Va: es el valor actual de la suma total de una serie de pagos futuros. (Cantidad que prestaron)
Vf: es el valor futuro o saldo en efectivo que desea obtener después de efectuar el último pago
(es decir lo que quiere quedar debiendo).
Lo que quiere quedar debiendo, Si vf se omite, se calculará como 0 (por ejemplo, el valor futuro
de un préstamo es 0.
Tipo: es el número 0 ó 1 e indica cuándo vencen los pagos. Si tipo se omite, se calculará como 0.
Defina Tipo como Si los pagos vencen
0 Al final del período
1 Al principio del período
PAGINA- 5 -CAPITULO 6 – EXCEL 2007–
NOMBRE: ___________________________________ CURSO: _______________
COMPUTO – ALEX ZAMBRANO – SAN JUAN BOSCO
6.2.2 FUNCION PAGO
Calcula el pago de un préstamo basándose en pagos constantes y en una tasa de interés constante. (Es decir en pagos e interés constante durante todo el préstamo)
Sintaxis
PAGO(tasa;nper;va;vf;tipo)
Tasa: Es la tasa de interés del préstamo.
Nper : Es el número total de pagos del préstamo. (tiempo del crédito)
Va: Es el valor actual o lo que vale ahora la cantidad total de una serie de pagos futuros. (lo que se prestó)
Vf: Es el valor futuro o saldo en efectivo que desea lograr después 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 préstamo es 0).
Tipo: Es el número 0 (cero) o 1 e indica el vencimiento de pagos.
Defina tipo como Si los pagos vencen
0 u omitido Al final del período
1 Al inicio del período
Observaciones
Sugerencia Para encontrar la cantidad total que se pagó durante la duración del préstamo, multiplique el valor devuelto por la función PAGO (cuota mensual) por el argumento nper. (Numero de periodos).
PAGINA- 6 -CAPITULO 6 – EXCEL 2007–
NOMBRE: ___________________________________ CURSO: _______________
COMPUTO – ALEX ZAMBRANO – SAN JUAN BOSCO
Ejemplos
La siguiente fórmula devuelve el pago mensual de un préstamo de $1.000.000 con una tasa de interés anual del 48% (4% mensual) pagadero en 6 meses:
PAGO(4%; 6; -1000000,0,0) = $190.762 (cuota mensual)
Usando el mismo préstamo, si los pagos vencen al comienzo del período, el pago es:
PAGO(4%; 6; -1000000; 0; 1) $183.425 (cuota mensual)
La siguiente fórmula devuelve la cantidad que se le deberá pagar cada mes si presta 500.000 ante
un plazo de cinco meses a una tasa de interés del 3%:
PAGO(3%; 5; -500000) $109.177 (cuota mensual)
EJERCICIOS
1. Se desea hacer un préstamo, y se debe calcular la cuota mensual, los intereses, el abono a capital y saldo, mes a mes.
LIDEM 21 INTERES 4% PLAZO:(MESES) 6
Alex Zambrano PRESTAMO: -$ 1,000,000 Cuota: $ 190,762
$ 1.144.571 $1,000,000 $ 144,571
FECHA CONCEPTO DEBITO CREDITO ABONO K INTERES SALDO
26-Abr-00 PRESTAMO $ - -$ 1,000,000 $ - $ - -$ 1,000,000
26-May-00 1 cuota $ 190,762 1 $ 150,762 $ 40,000 -$ 849,238
26-Jun-00 2 cuota $ 190,762 2 $ 156,792 $ 33,970 -$ 692,446
26-Jul-00 3 cuota (julio) $ 190,762 3 $ 163,064 $ 27,698 -$ 529,382
26-Ago-00 4 cuota (agosto) $ 190,762 4 $ 169,587 $ 21,175 -$ 359,795
26-Sep-00 5 cuota (sep) $ 190,762 5 $ 176,370 $ 14,392 -$ 183,425
26-Oct-00 6 cuota (oct) $ 190,762 6 $ 183,425 $ 7,337 -$ 0
2. Luego de hacer el anterior ejercicio, copie la hoja (toda la hoja, no seleccionar y pegar el rango) 9 veces más, en cada una cambie el interés, el préstamo, el plazo o todos.
PAGINA- 7 -CAPITULO 6 – EXCEL 2007–
NOMBRE: ___________________________________ CURSO: _______________
COMPUTO – ALEX ZAMBRANO – SAN JUAN BOSCO
6.3. Funciónes LOGICAS
6.3.1 FUNCION Si
Es una función creada para manejar situaciones especiales que dependen del valor de verdad de una condición(es decir si es verdadero o falso), escogiendo una operación determinada para cuando el resultado de la evaluación sea verdadera y otra para cuando sea falsa. (Según una
condición, hace una operación)
Esta función se puede utilizar de cinco formas diferentes:
6.3.1.1 SI sencillo
Con esta función se evalúa una condición y se asigna valor verdadero o valor falso.
Sintaxis:
=SI(Prueba Lógica, Valor si Verdadero, Valor si falso)
Prueba Lógica: Es una pregunta que se hace acerca del valor de verdad de una operación o celda determinada de la misma hoja electrónica o de otra del mismo libro; está compuesta por tres partes:
Dato a comparar: Es la celda, conjunto de celdas u operación entre celdas por la cual se está indagando.
Operador relacional: Son los operadores que permiten realizar comparaciones:
PAGINA- 8 -CAPITULO 6 – EXCEL 2007–
NOMBRE: ___________________________________ CURSO: _______________
COMPUTO – ALEX ZAMBRANO – SAN JUAN BOSCO
> Mayor que
< Menor que
>= Mayor o igual
<= Menor o igual
= Igual
<> Diferente de
Dato de comparación: Es el valor, cadena de caracteres o celda contra la cual se
compara.
Valor_si_Verdadero: Es la operación que se debe realizar cuando la condición sea verdadera. Puede ser un valor, una operación matemática, una palabra o una
operación entre celdas.
Valor_si_Falso: Operación que se realiza en caso de que la condición sea falsa.
Ejemplo:
Fig. 6.8 - Ejemplo de Si sencillo
La fórmula planteada en la columna C1 tiene la siguiente estructura:
=SI(A1<40000,B1*10%,B1*5%)
Si el valor de la columna A es menor que cuarenta mil, entonces se dará el 10% del valor de la columna B; si no se cumple la condición, se calculará el 5%.
PAGINA- 9 -CAPITULO 6 – EXCEL 2007–
NOMBRE: ___________________________________ CURSO: _______________
COMPUTO – ALEX ZAMBRANO – SAN JUAN BOSCO
EJERCICIO
En una empresa tiene 3 vendedores y el contrato de comisiones especifica que si se vende un millón o más el porcentaje es del 5%, si no es del 3%.
VENDEDOR VENTAS COMISIÓN
JUAN $ 1,500,000 $ 75,000
PEDRO $ 500,000 $ 15,000
MIGUEL $ 2,000,000 $ 100,000
6.3.1.2 SI con operadores lógicos
Con esta función se pueden evaluar dos condiciones unidas con un operador lógico.
CON Y: Operador de inclusión, si se unen dos condiciones con Y, deben cumplirse
ambas para que el resultado de la condición sea verdadero.
Sintaxis:
Y(Prueba Lógica 1, Prueba Lógica 2, …prueba Lógica n)
Si todas las condiciones se cumplen devuelve verdadero.
Dentro de la función SI quedaría:
=SI (Y(Prueba Lógica 1, Prueba Lógica 2), Valor si Verdadero, Valor si Falso)
Ejemplo:
=SI (Y(A1=50000,B1>500),A1/B1,A1+B1)
Su resultado se muestra en la columna C del siguiente cuadro.
PAGINA- 10 -CAPITULO 6 – EXCEL 2007–
NOMBRE: ___________________________________ CURSO: _______________
COMPUTO – ALEX ZAMBRANO – SAN JUAN BOSCO
Fig. 6.9 - Condicional con operador lógico "Y"
EJERCICIO
En la misma empresa los vendedores deben cumplir con
dos condiciones para las comisiones, para poder darle el 5% debe tener más de un año de antigüedad en la empresa y haber vendido un millón o más; de lo contrario solo le
corresponde el 3%.
VENDEDOR ANTIGÜEDAD VENTAS COMISIÓN
JUAN 2 $1,500,000 $ 75,000
PEDRO 1 $ 500,000 $ 15,000
MIGUEL 0.5 $2,000,000 $ 60,000
CON O: Operador de exclusión, con dos o más condiciones; con que una de ellas se cumpla, toda la condición será verdadera.
Sintaxis:
O(Prueba Lógica 1, Prueba Lógica 2, … Prueba Lógica n)
Y
A B A Y B
V V V
V F F
F V F
F F F
PAGINA- 11 -CAPITULO 6 – EXCEL 2007–
NOMBRE: ___________________________________ CURSO: _______________
COMPUTO – ALEX ZAMBRANO – SAN JUAN BOSCO
Con sólo una de las condiciones que se cumpla devuelve Verdadero. Su planteamiento es el siguiente:
=SI(O(Prueba Lógica 1, Prueba Lógica 2), Valor_si_Verdadero, Valor_si_Falso)
Ejemplo:
=SI(O(A1>20000,B1<2000),A1-B1,A1+B1)
Fig. 6.10 - Condicional con operador lógico "O"
EJERCICIO
En la misma empresa se venden dos tipos de artículos, si en al menos en
uno de los se venden más de un millón, la comisión será del 5% sobre el total de ventas, si no será del 3%.
VENDEDOR ARTICULO A ARTICULO B COMISIÓN
JUAN $ 500,000 $ 1,500,000 $ 100,000
PEDRO $ 1,000,000 $ 500,000 $ 45,000
MIGUEL $ 1,500,000 $ 2,000,000 $ 175,000
O
A B A Y B
V V V
V F V
F V V
F F F
PAGINA- 12 -CAPITULO 6 – EXCEL 2007–
NOMBRE: ___________________________________ CURSO: _______________
COMPUTO – ALEX ZAMBRANO – SAN JUAN BOSCO
CON NO : Operador de negación, para preguntar negando, se hace de la siguiente manera:
=SI(NO(Prueba Lógica), Valor si Verdadero, Valor si Falso)
Ejemplo:
=SI(NO(A1<30000),1,0)
Fig. 6.11 - Condicional con negación
Las forma como trabajan los operadores lógicos es la siguiente:
EJERCICIO
Si no se cumple la condición de que los vendedores hayan vendido más de un millón en total el porcentaje es del 3%, de lo contrario es del 5%
NO
A NO A
V F
F V
VENDEDOR ARTICULO A ARTICULO B TOTAL COMISIÓN
JUAN $ 500,000 $ 1,500,000 $ 2,000,000 $ 100,000
PEDRO $ 1,000,000 $ 500,000 $ 1,500,000 $ 75,000
MIGUEL $ 1,500,000 $ 2,000,000 $ 3,500,000 $ 175,000
PAGINA- 13 -CAPITULO 6 – EXCEL 2007–
NOMBRE: ___________________________________ CURSO: _______________
COMPUTO – ALEX ZAMBRANO – SAN JUAN BOSCO
SI V
PROCESO 1
F
SI V F
PROCESO 2 PROCESO 3
6.3.1.3 SI anidado
Algunas veces se presentan casos en los cuales al utilizar la función Si en la condición se presenta varias probabilidades.
Cuando se hace un SI normal se tienen dos posibilidades, que la condición se cumpla o no, pero cuando existen más de dos comparaciones, un solo SI no es
suficiente ya que si una posibilidad se cumple o no, también hay que verificar que puede pasar con las otras posibilidades.
En estos casos hay que utilizar los SI anidados (para n posibilidades se utilizan (n- 1)
SI. Dos posibilidades, un solo SI, lo normal.
Sintaxis:
=SI(Prueba Lógica 1, proceso 1, SI(Prueba Lógica 2, proceso 2, SI(Prueba lógica 3, proceso 3, proceso 4)))
La última posibilidad no necesita condición ya que por defecto, si no se cumple ninguna de las anteriores, la única que queda es ésa.
Ejemplo:
Forma de pago (FP) puede ser 1, 2 ó 3 y de acuerdo con ella, se obtendrá el valor del descuento, así:
Si FP es 1 el descuento es 15% de la venta, si es 2 el descuento será de 10% y si es
3 no se dará descuento. La función condicional quedaría:
=SI(A2=1,B2*15%,SI(A2=2,B2*10%,0))
PAGINA- 14 -CAPITULO 6 – EXCEL 2007–
NOMBRE: ___________________________________ CURSO: _______________
COMPUTO – ALEX ZAMBRANO – SAN JUAN BOSCO
Fig. 6.12 - Ejemplo de la Función Si anidados
EJERCICIO
Si los vendedores venden entre 1 millón y 2, se ganan el 4%, si venden entre 2 y 5 millones, el 5%, si es más es el 7%
VENDEDOR ARTICULO A ARTICULO B TOTAL COMISIÓN
JUAN $ 500,000 $ 1,500,000 $ 2,000,000 $ 100,000
PEDRO $ 1,000,000 $ 500,000 $ 1,500,000 $ 60,000
MIGUEL $ 7,000,000 $ 2,000,000 $ 9,000,000 $ 630,000
PAGINA- 15 -CAPITULO 6 – EXCEL 2007–
NOMBRE: ___________________________________ CURSO: _______________
COMPUTO – ALEX ZAMBRANO – SAN JUAN BOSCO
6.4 FUNCION DE BÚSQUEDA
6.4.1 FUNCION BUSCAR
Devuelve un valor de un rango de una fila o de una columna o de una matriz (FILAS Y
COLUMNAS). La función BUSCAR tiene dos sintaxis, vectorial y matricial. La forma vectorial de BUSCAR busca un valor en un rango de una fila o de una columna (vector) y devuelve un valor desde la misma posición en un segundo rango de una fila o de una columna.
La forma matricial de BUSCAR busca el valor especificado en la primera fila o en la primera columna de la matriz y devuelve el valor desde la misma posición en la última fila o columna de la matriz.
La función BUSCAR tiene dos formas de sintaxis: vectorial y matricial.
Un vector es un rango que contiene una sola fila o una sola columna. La forma vectorial de BUSCAR busca en un rango de una fila o de una columna un valor (vector) y devuelve un valor
desde la misma posición en un segundo rango de una fila o de una columna. Utilice esta forma de la función BUSCAR cuando necesite especificar el rango que contiene los valores que desea hacer coincidir. La otra forma de BUSCAR, busca automáticamente en la primera fila o en la
primera columna.
Sintaxis 1
Forma vectorial
BUSCAR(valor buscado; vector de comparación; vector resultado)
Valor buscado es un valor que BUSCAR busca en la matriz. Valor buscado puede ser un número, texto, un valor lógico, o un nombre o referencia que se refiera a un valor.
Vector de comparación: es un rango que sólo contiene una columna o una fila. Los valores en
el vector de comparación: pueden ser texto, números o valores lógicos.
Importante Los valores en el vector de comparación deberán colocarse en orden ascendente: ...;-2; -1; 0; 1; 2;...; A-Z; FALSO; VERDADERO; de lo contrario, BUSCAR puede dar un valor
incorrecto. El texto en mayúsculas y en minúsculas es equivalente.
PAGINA- 16 -CAPITULO 6 – EXCEL 2007–
NOMBRE: ___________________________________ CURSO: _______________
COMPUTO – ALEX ZAMBRANO – SAN JUAN BOSCO
Vector resultado es un rango que sólo contiene una columna o una fila. Debe ser del mismo tamaño que vector de comparación.
Si BUSCAR no puede encontrar el valor buscado, utilizará el mayor valor de vector de
comparación que sea menor o igual al valor buscado.
Si valor buscado es menor que el valor más bajo de vector de comparación, BUSCAR
devuelve el valor de error #N/A.
Ejemplos
Se necesita encontrar con el código del cliente, cuanto es el saldo y sus datos actuales
CLIENTE A NOMBRE SALDO FECHA DIRECCION TELEFONO
BUSCAR -
- - -
111110003 PEDRO $ 860,000 Feb-12 DIAG. 4 No 7-89 223-89-56
COD CLIENTE NOMBRE SALDO FECHA DIRECCION TELEFONO
111110001 PABLO $ 500,000 Dic-03 CRA 8 No 4 -65 444-55-78
111110002 MIGUEL $ 250,000 Ene-05 CLLE 7 No 89-77 556-89-78
111110003 PEDRO $ 860,000 Feb-12 DIAG. 4 No 7-89 223-89-56
111110004 JUAN $ 780,000 Mar-15 TRAN 3N No 54-78 778-89-45
111110005 CARLOS $ 1,450,000 Abr-15 CLLE 17 No 9-77 556-89-45
111110006 LILIANA $ 2,580,000 May-30 CRA 8 No 34-89 335-56-48
111110007 MARITZA $ 5,600,000 Jun-17 CLLE 7 No 44-55 336-98-65
1. el dato es el código del cliente. 2. la formula de búsqueda para el nombre del cliente es la siguiente:
=BUSCAR($A$5,$A$8:$A$14,B8:B14)
3. la del saldo es:
=BUSCAR($A$5,$A$8:$A$14,C8:C14)
4. La de la fecha es:
=BUSCAR($A$5,$A$8:$A$14,C8:C14) 5. y la de la dirección y teléfono respectivamente son:
=BUSCAR($A$5,$A$8:$A$14,E8:E14)
=BUSCAR($A$5,$A$8:$A$14,F8:F14)
PAGINA- 17 -CAPITULO 6 – EXCEL 2007–
NOMBRE: ___________________________________ CURSO: _______________
COMPUTO – ALEX ZAMBRANO – SAN JUAN BOSCO
EJERCICIO
FECHA CODIGO NOMBRE PRESTAMO CUOTA INTERES PAGO/TOTAL
04-Ago-00 10000999 pedro picapiedra $ 2,000,000 $ 381,524 $ 289,143 $ 2,289,143
fecha codigo nombre Prestamo Cuota Interes Pago/Total
18-Dic-00 10000993 gustavo arbelaez $ 2,000,000 $ 381,524 $ 289,143 $ 2,289,143
30-Jul-00 10000994 juan carlos clavijo $ 1,000,000 $ 197,017 $ 182,105 $ 1,182,105
30-Dic-99 10000995 Liliana escobar $ 600,000 $ 118,210 $ 109,263 $ 709,263
14-Ago-00 10000996 miguel magone $ 1,500,000 $ 286,143 $ 216,857 $ 1,716,857
05-Oct-00 10000997 pablo marmol $ 5,000,000 $ 953,810 $ 722,857 $ 5,722,857
09-Feb-00 10000998 pablo milanes $ 800,000 $ 142,821 $ 56,924 $ 856,924
04-Ago-00 10000999 pedro picapiedra $ 2,000,000 $ 381,524 $ 289,143 $ 2,289,143
EN OTRAS PALABRAS LA SINTAXIS ES:
BUSCAR(LO QUE QUIERO BUSCAR;DONDE LO QUIERO BUSCAR;Y LO QUE QUIERO QUE ENCUENTRE)