Embed Size (px)
Citation preview
1
FUNCION SUMALa función suma es una de las más usadas, prueba de esto es que Excel tiene un icono especial para efectuar sumas rápidas, con esto quiero decir que no hace falta poner =SUMA() para efectuar la suma de un cierto rango, lo que por cierto ahorra tiempo y evita errores, este icono se llama autosuma y para usarlo basta con seleccionar el rango que queremos sumar hacer clic en y el resultado aparece en la celda inmediatamente inferior al rango, aunque si queremos el resultado en otro lado basta con seleccionar la celda, luego el rango que queremos sumar, Enter y listo( Excel 2003 y 2007)
fijemonos en la barra inferior de Excel y veremos siempre la suma ,el promedio y cuantas celdas se seleccionaron para efectuar la suma(Excel 2007).
Si tenemos varias columnas, seleccionamos esas columnas dejando espacio para poner los totales
y luego aplicamos autosuma
2
Si no seleccionamos nada Excel interpreta que lo que queremos sumar es la secuencia de números puesta verticalmente u horizontalmente( tiene que estar activa una cela en la columna o fila respectivamente), en estos casos inserta automáticamente la función suma con los rangos que interpreta queremos sumar, para obtener la suma solo nos falta apretar ENTER, para aclarar las cosas veamos los siguientes gráficos.
Apretamos ENTER y obtenemos la suma
y si la secuencia es horizontal
3
pretamos ENTER y obtenemos la suma
También podemos utilizar la autosuma para obtener subtotales por ejemplo si tenemos la siguiente tabla
Seleccionando los rangos de cada subtotal apretando la tecla control
4
y obtenemos los subtotales apretando e icono de autosuma
Si hubiéramos una gran cantidad de subtotales, hacerlo así sería muy engorroso entonces lo que hacemos es seleccionar el rango adecuado y usar "ir a" apretando F5( O) con lo que aparece
luego oprimimos Especial y vamos al siguiente pantalla en la que tildamos solo constantes y números
5
esto hace que se seleccionen solamente los números de los subtotales
luego apretamos en autosuma y obtenemos los subtotales
6
En Excel 2007 aparece en la solapa Inicio y en Excel 2003 en la barra iconos.
Pero la función suma es mucho más que una suma rápida, esta entrega la suma de los valores que están en las celdas a que hacen referencia los argumentos y estos pueden ser: un valor numérico, la referencia o el nombre de una celda, la referencia o el nombre de un rango, una fórmula matemática o una función, en este último caso estaríamos ante un Anidamiento de funciones , por otro lado los parámetros pueden ser todos lo que nos hagan falta, o sea
=SUMA(parametro-1, parametro-2,parametro-3,.............,parámetro- n..)Veamos el siguiente ejemplo
donde se ve que la expresión =SUMA(A1:C4;E1;230;E4*F4) ubicada en la celda A7 devuelve la suma de las celdas
7
1- A1,B1,C1,A2,B2,C2,A3,B3,C3.A4,B4,C42- La celda E13- El número 2304- El producto de las celdas E4 y F4Por último veanos un ejemplo mas práctico Se tiene una tabla con las ganancias semestrales por cereales
Se quieren los totales anuales por granos, los totales por semestre y el total final descontando los impuestos de un 36%
Los totales se hacen con autosuma y el total final con =SUMA(d4;-E4). Los impuestos se calcularon con una fórmula (=D4*$A$11) pero esto es otro tema
SUMAPRODUCTO Si en una Hoja de Excel tenemos las tablas A (con borde rojo) y B (con borde verde), las cuales tienen el mismo número de filas y de columnas, podemos definir celdas que ocupan la misma posición relativa respecto de A y B, a estas celdas se las denomina "celdas correspondientes". Por ejemplo en la figura
8
las celdas C5 y G5 son correspondientes.
Ahora estamos en condiciones de definir la función SUMAPRODUCTO.La función SUMA PRODUCTO multiplica el contenido de las celdas correspondientes de hasta 30 tablas y devuelve la suma de esos productos.
La sintaxis de SUMAPRODUCTO es:
SUMAPRODUCTO(taba1; tabla2; tabla3;.......)
En la figura de arriba tenemos un ejemplo con 2 tablas. Notar que hubiéramos llegado al mismo resultado con la función SUMA usando como argumentos los productos de las celdas correspondientes
Si en el argumento de SUMAPRODUCTO hay una sola tabla, el resultado es la suma de los elementos de ella
9
FORMULAS MATRICIALESINTRODUCCION:Con las fórmulas matriciales se pueden hacer muchas cosas, es una herramienta de gran potencia, en general estas fórmulas o funciones se usan para hacer 2 tipos de cosas.:
Ejecutar varias operaciones y devolver un único valor en la celda donde se la introduce. Ejecutar varias operaciones y devolver múltiples valores en distintas celdas.
Las fórmulas matriciales actúan en 2 o mas rangos de valores, los que se denominan, argumentos matriciales, los cuales tienen la característica de tener el mismo número de filas y de columnas, por ejemplo, podrían actuar sobre los rangos A1:A12 y BI:B12.
Una fórmula matricial se introduce de la misma forma que la fórmula común, la diferencia es que luego de introducirla hay que apretar las teclas Control+shift+ENTER, con lo que automáticamente es rodeada por llaves y es por eso que se las conoce como fórmulas CSE. Para una formula matricial multiplicar 2 argumentos matriciales, como A1:A12 *BI:B12. significa multiplicar las celdas A1*B1, A2*B2, A3*B3......A12*B12 si quiero sumar estos resultados parciales uso la formula matricial {SUMA(A1:A12*B1:B12)}, para aclarar los conceptos vamos a tener que hacer mas de un ejemplo, Empecemos por un ejemplo del tipo 1-.
El dueño de una mueblería quiere aumentar la variedad de los productos que vende para lo que decide comprara, parte de los tradicionales, muebles de computación, para lo que cuenta con la siguiente planilla
y quiere saber cuánto tiene que gastar. Decide tomar el camino corto y usa una simple fórmula matricial, veamos lo que hizo
10
se ve que introdujo la fórmula matricial
de esta forma hizo 3 pasos en uno. Los 3 pasos hubieran sido: 1- Introducir la fórmula =D2*E2 en la celda F2. 2- Arrastrar esta fórmula hasta la celda F7
3- Ubicarnos en la celda F8 y pulsar el icono
o ubicarnos en la celda D9( por ejemplo ) e introducir la función
=SUMA(D2*E2;D3*E3;D4*E4;D5*E5;D6*E6;D7*E7)
se ve que ambas maneras, si bien dan el mismo resultado, son mucho mas tediosas
Se puede aprovechar este mismo ejemplo para mostrar como usar las fórmulas matriciales que devuelven múltiples valores y así explicamos todo el proceso.Usando la misma tabla que al principio vamos a obtener todos los productos parciales
1º seleccionamos la columna donde queremos que aparezcan los valores
11
2º introducimos la fórmula, seleccionando los rangos D2:D7 y E2:E7 y multiplicándolos
3º apretamos las teclas Control+shift+ENTER
obteniendo los productos parciales y por lo tanto múltiples resultados como se ve en el recuadro rojo.
12
FUNCION SILa función SI sirve para tomar decisiones de acuerdo a una condición, por eso podríamos decir que es una función condicional, siendo la condición el resultado de la evaluación de una proposición lógica ( VERDADERO o FALSO), es decir; si el resultado es VERDADERO se hace una cosa, y si es FALSO se hace otra.. Esta función tiene 3 argumentos
Ejemplo:De acuerdo a un informe volcado a una tabla una empresa quiere saber en qué meses tuvo pérdidas o ganancias
para lo cual estos datos se ponen en una Hoja de Excel y se usa la función SI de la siguiente manera
13
arrastrando la función hasta completar, el resultado final es
a esta empresa no le fue demasiado bien-
FUNCION Y()La función Y() ,como O() es una función lógica ya que sus argumentos son proposiciones lógicas, la función evalúa los argumentos y devuelve un resultado VERDADERO o FALSO ( aclaro que esta función puede tener un solo parámetro sin dar error, aunque no tiene mucho sentido práctico)
Su sintaxis es:
Y(parámetro1;parámetro2;parámetro3;.....)
14
La función devuelve VERDADERO si la evaluación de todos los parámetros es VERDADERA y dará FALSO si la evaluación al menos uno de sus parámetros es FALSA o si todos son FALSOS.
Veamos un ejemplo
vemos que si cambiamos una desigualdad, o las dos el resultado es FALSO
FUNCION O()
Como Y() la función O() es una función lógica, porque sus argumentos son proposiciones lógicas o pruebas lógicas la función evalúa los argumentos y devuelve un resultado VERDADERO o FALSO., su sintaxis es
O(parámetro1;parámetro2;parámetro3;.....)
La función devuelve FALSO si la evaluación de todos los parámetros es FALSO y dara VERDADERO si la evaluación almenos uno de sus parámetros es VERDADERO o si todos son VERDADEROS.
Veamos un ejemplo
FUNCION NO()
La función NO() invierte el valor lógico de los argumentos de las funciónes Y() y O(), por consiguiente se utiliza en combinación con ellas, su sintaxis es:
NO(Y(argumento1; argumento2; argumento3;..........))
15
como puede verse en siguiente ejemplo
como se ve invierte el valor de verdad de los argumentos de Y()
ANIDAMIENTO DE FUNCIONES INTRODUCCION El anidamiento de funciones junto con la programación VBA es lo que más potencia da al programa EXCEL, aquí todo depende de a dónde nos pueda llevar nuestra habilidad e imaginación y es donde se convierte en un programa muy versátil pudiendo abarcar diversas disciplinas como son la Ingeniería, Estadística, Matemática, Finanzas, Contabilidad por decir algunas que se me ocurren.
El anidamiento de funciones no es otra cosa que ubicar una función en el argumento de otra de forma adecuada, dicho así parece muy simple pero veremos que la cosa puede complicarse mucho dado que la anidación pude hacerse en muchos niveles e involucrar a muchas funciones dando expresiones muy largas y difíciles de manejar, esto dista mucho de querer desalentar, más bien insta a la curiosidad y a la práctica.
Empezaremos por lo más simple para ir a lo más complejo en forma progresiva pero antes voy a aclarar esto de los niveles y el límite que hay y la forma adecuada de hacerlo, para esto, como siempre nada mejor que un ejemplo
Se sabe que el promedio de las temperaturas del año en curso de la provincia de Misiones es de 27º y se tiene una tabla con los promedios de las temperaturas de los meses del año anterior, se quiere saber si es verdadero que los 27º entran en el rango de los promedios de los meses del año anterior
16
se ve que 27º no entra en rango de las temperatura promedio de los meses del año anterior y que en la fórmula usada hemos anidado las funciones MAX() y MIN() en dos argumentos de una función Y() la que se denomina de primer nivel, siendo MAX() y MIN() de segundo nivel ya que forman parte de los argumentos de Y(). MAX() y MIN() están ubicadas correctamente pues forman parte de proposiciones lógicas que son las que aceptan los argumentos de Y().Por otra parte las funciones se pueden anidar hasta 64 veces en Excel 2007 y solo 7 veces en Exel 2003 y versiones anteriores.
FUNCION SI() ANIDADA CON LA FUNCIION Y() y O()
La función SI es una de las que más se usa para el anidamiento ya que su estructura es muy adecuada para esto: En el parámetro 1 hay que poner una fórmula lógica, un anidamiento con funciones lógicas o la propia función si actuando como función lógica.En loa parámetros 2 y 3 se pueden anidar una multitud de funciones, como funciones lógicas, lafunciñon si() actuando como función condicional o lógica etc.
Ahora vamos a ver un un ejemplo relativamente simple de la situación del punto 1.
Una empresa quiere promover a una nueva sección a los empleado que cumplan con las siguientes condiciones :
Pertenecer al turno mañana.Ser de la categoría 1 o que su sueldo sea menor o igual a 7.000$.
17
Para esto cuenta con la siguiente tabla que debe ser completada; donde los turnos son M,T ,N ,correspondientes a mañana, tarde y noche respectivamente y las secciones van de 1 a 4
Que se resuelve utilizando la fórmula
=SI(Y(O(E2=4;D2<=7000);Y(C2="M"));"PROMUEVE";"NO PROMUEVE")
como se ve, en el 1º parámetro tenemos una función Y que tiene anidadas en sus parámetros, una función O y otra función Y, lo que aumenta el número de posibilidades que se están evaluando o condiciones que se tienen que cumplir como:
ser del turno mañana (se tiene que cumplir siempre).
18
ser de la categoría 1 o que su sueldo sea <=7.000$ o ambas cosas.
Este es un ejemplo relativamente simple, anidando en el parámetro 1 y sin anidar nada en los otros dos, pero la complejidad puede aumentar grandemente.
PROMEDIO CON UNA CONDICION
Excel 2007 tiene una función que nos da el promedio de un conjunto de valores con una condición, tal función se llama PROMEDIO.SI, con las fórmulas matriciales podemos hacer lo mismo. Haremos un ejemplo con ambas opciones, aclarando que estando la función PROMEDIO.SI, siempre es preferible usar esta alternativa debido a que utiliza menos recursos de nuestro equipo.
Otra historia es para Excel 2003, puesto que en esta versión la única alternativa son las fórmulas matriciales, siendo la razón que PROMEDIO.SI directamente no existe.
Supongamos que una fábrica de autos, lanzó un nuevo modelo en el mes de Enero y quiere saber cual fue el promedio de ventas de los 3 primeros días del mes en cada una de las zonas en las que esta divide al país. Las zonas son: Norte, Sur, Este, Oeste y centro. Para lograr su objetivo se vuelcan los datos de las ventas de esos días en una tabla, con un sector a la derecha para los resultados
la fórmula, para la zona Norte es:
19
en la figura se hace la comparación con la función PROMEDIO.SI y se ve que coinciden
MAXIMO CON UNA CONDICION
Sería muy útil que Excel 2003/2007 tuviera la fórmula del tipo "máximo con una condición", algo como MAX.SI o MAXIMO.SI , pero si se fijan en el conjunto de funciones de Excel, esta no aparece nada parecido, ni siquiera en Excel 2007, esta es una mala noticia ya que, sin duda sería muy útil; hay muchos problemas que se podrían resolver. Por suerte podemos resolver esta falencia con las FORMULAS MATRICIALES.
Supongamos este problema:
Un grupo de alumnos rindió 3 materias, Matemática, física y química y quieren saber el puntaje máximo que sacaron.
Los datos se podrían representar en una tabla como la siguiente
Para responder a la inquietud de los alumnos se vuelca la tabla en una Hoja de Excel poniendo una tabla a la derecha para los resultados
20
donde la fórmula matricial usada es, por ejemplo para el alumno Marquez
FUNCION SUMAR.SI
INTRODUCCIONLa función SUMAR.SI permite sumar valores de un rango de acuerdo a un criterio o condición.
La función SUMAR.SI tiene 3 parámetros: El primero es la referencia o el rango que contiene los valore sobre los que se evaluará la condición. El segundo es el que contiene el criterio a aplicar con el objeto de determinar que se suma y que no El tercero es opcional, esto quiere decir que si la condición esta en el mismo rango donde se efectúa la suma, no hace falta el tercer parámetro, pero si el criterio esta en un rango y donde se hace la suma en otro (u otros )rangos, entonces tiene que colocarse el tercer parámetro.
Para aclarar las cosas que mejor que un ejemplo: Supongamos que una inmobiliaria tiene un listado con el valor de las propiedades que se vendieron en Enero y quiere saber la suma de aquellas que superaron los $160.000, para obtener la respuesta se emplea la función SUMAR.SI como se muestra en el gráfico
21
En este caso con dos parámetros alcanza puesto que el criterio está en la rango E2:E5, que el mismo rango donde se efectúa la suma con la condición dada y no hace falta poner =SUMA(E2:E5;">160000";E2:E5)..Si en cambio tenemos esta otra tabla
aquí si hace falta el tercer parámetro ya que el rango donde se efectúa el criterio (D2:D5) no es el mismo que el rango donde se efectúa la suma (E2:E5).
Dejo como ejercicio averiguar las comisiones que se cobran al vendedor por propiedades cuyo costo es inferior a $ 400.000.
FUNCION CONTAR.SIEsta función es una combinación de las funciones CONTAR y SI , tiene dos argumentos, el primero es el rango cuyas celdas se desean contar y el segundo es el criterio que determina que celda será contada o no
22
Con esta misma tabla podríamos preguntar cuántos hombres hay
FUNCION CONCATENARLa función CONCATENAR permite unir dos o más elementos de texto que están contenidos en celdas diferentes. También permite unir textos puestos entre comillas directamente en los argumentos de la función. Su sintaxis es:
=CONCATENAR(Texto1;Texto2;...)
Esta función puede tener como máximo 30 argumentos.
Ejemplos:
23
Si tenemos en la celda A1 el texto YAHOO y en la celda B5 ARGENTINA, nos queda YAHOO ARGENTINA en la celda donde se introduce la fórmula. Veamos
notar que el segundo argumento es un espacio (" ") para separar ambas palabras y que el formato color no se tiene en cuenta.
Uniendo textos directamente usando comillas
También se pueden concatenar textos, sin usar la función CONCATENAR y empleando en su lugar el símbolo ampersand ( & ), como podemos ver:
FUNCION INDICE
La función INDICE tiene la particularidad de tener dos sintaxis:
SINTAXIS MATRICIAL : devuelve un valor o matriz de valoresSINTAXIS REFERNCIAL: devuelve un rango o referencia.
SINTAXIS MATRICIAL : En matemática una matriz es un arreglo de números, una tabla de valores o dicho de otra manera una forma de ordenar números identificándolos por su ubicación en filas y columnas o mas precisamente por la intersección de una fila con una columna. En Excel, un rango, es lo que para la matemática una matriz, vayamos a una Hoja de Excel
24
aquí podemos identificar el rango B1:E5 ( recuadrado en rojo) con una matriz de 4 filas por 4 columnas donde estas se numeran, desde arriba y a la izquierda empezando por 1, en forma creciente, con lo que por ejemplo el numero 567 correspondería a la intersección de la fila 3 con la columna 2, el numero 23 con la intersección de la fila 1 con la columna 4 etc. Esto es lo que hace la función INDICE, devolver el numero que esta en la celda que es la intersección de una fila con una columna, aclaro que en este caso en la celda puede haber un numero, una cadena de caracteres, un mensaje de error, una formula etc. Dicho esto se entenderá mejor la sintaxis de la función INDICE
en este caso INDICE nos devuelve el valor 567
CASOS PARTICULARES
Si el primer argumento es una matriz columna ( 1columna por n filas) se omite el argumento columna.Si el primer argumento es una matriz fila ( 1 fila por n columnase) se omite el argumento filaSi el primer argumento es una matriz de n columnas por m filas y se pone cero como segundo argumento INDICE puede devolver una columna o una fila de la matriz n X m,para hacer esto INDICE se introduce como una FORMULA MATRICIAL
SINTAXIS REFERENCIAL:
Devuelve la referencia de la celda ubicada en la intersección de una fila y de una columna determinadas de un rango. Si hay mas de un rango se podrá elegir, mediante un tercer argumento llamado área, en cual de ellos se buscará la intersección de filas y columnas, el primer rango se relaciona con el área 1, el segundo rango con el área 2 y así sucesivamente.
25
Para el siguiente ejemplo
la sintaxis es
que da como resultado "autos"
FUNCION COINCIDIRLa función COINCIDIR es una función de búsqueda como BUSCARV pero a diferencia de esta, COINCIDIR no devuelve un valor sino una posición dentro de un rango, este rango puede ser una columna o una fila y contener números, palabras o una combinación de ambos . La sintaxis tiene 3 parámetros; el 1º es el valor referencia cuya posición se quiere encontrar, el 2º el rango y el tercero pude ser -1, 0 y 1, que tomen esos valores va a depender de:Si la lista está desordenada el tercer parámetro es 0, dando error si el numero no está en dicha lista.
26
Si la lista está ordenada en forma ascendente el valor es 1 o no se pone ninguno, si el valor no está pero se encuentra entre otros dos , o sea a<valor<b, se elige la posición del valor a.Si la lista está ordenada en forma descendente el valor es -1 y si el valor no está pero se encuentra entre otros dos valores o sea a>valor>b se da la posición del valor b)
Vamos a dar ejemplos para aclarar los conceptos:
Caso 1: lista desordenada
La lista está desordenada y el valor 325 se encuentra en la lista siendo su posición 2
Caso2: Lista ordenada en forma ascendente
como se ve el valor no está en la lista pero 50,6<81<84 y en este caso se da la posición de 50,6 que es 3
27
Caso 3: Lista ordenada en forma descendente
l valor no está pero se encuentra entre 50,6 y 80 por lo tanto la función da la posición de 80 que es 2 .
Los casos 2 y 3 tienen el problema de que no podemos saber de antemano si la función nos devolverá la posición del valor mas cercano al buscado, en los ejemplos mostrado tuvimos suerte, pero veamos este otro caso con una lista ascendente
COINCIDIR nos devolvió el valor 4 correspondiente a 25,3 que no es la posición del valor mas cercano al buscado, este valor tendría que haber sido 5 que es la posición de 100 que es el valor que mas se aproxima a 99. Este problema se soluciona con FORMULAS MATRICIALES
COMPROBACION: en los ejemplos anteriores se podría haber puesto una referencia en lugar del valore n sí, en el primer parámetro; se aconseja hacer esto para averiguar lo que devuelve COINCIDIR cuando se introducen valores que no están en la lista( para los 3 casos).
FUNCIONES FINANCIERASFunción PAGOPAGO es una función financieras, que ayuda excel 2010 considera muy importante, la cual calcula el importe del pago periódico motivado por un préstamo amortizable por el método francés. Es decir, calcula las anualidades
28
necesarias caracterizadas por términos de amortización periódicos, constantes que inlcuyen tanto la amortización del principal como de los intereses basados en una tasa de interés constante.
Para lograr entender mejor este tipo de funciones, debemos de tener conocimiento en lo que es Ingeniería Económica, Administración de Proyectos o Administración Financiera.
Sintaxis:PAGO(tasa, nper, va, vf, tipo)tasa es el tipo de interés por período de pago aplicable al prestamo. Un banco presta a una tasa de interés del 20% anual.
nper es el número total de período de pago en la vigencia del préstamo. El banco hace los préstamo por años o por meses. Esta valor debe de estar relacionado con los intereses. Es decir, si nos dan intereses del 2% mensuales, los cálculos que vamos a hacer para determinar el pago los vamos a trabajar con nper mensuales. No debemos utilizar intereses mensuales y hacer referencia a 3 años. Todo debe de estar en la misma medida por decirlo asi. Mes-mes Año-año.va es el importe del préstamo o del valor actual del préstamo. Este valor va a ser 0, si solo si el valor de pago a buscar es el valor futuro que se tendrá según una serie de depósitos realizados al banco. Es decir, si depositas 250 por 12 meses y quieres saber cuanto dinero será al final del año, nuestro va será 0.
vf es el valor futuro o saldo en efectivo que se desea lograr después de efectuar el último pago. Este valor será 0 si solo si se desea conocer el pago actual mensual que se necesita hacer para cancelar un préstamo.tipo es la modalidad de pago. Este parámetro permite 2 valores: 0 o dato omitido si los términos son pospagados, y 1 si son prepagables.
Ejemplo 1:Se desea determinar cuáles son los pagos mensuales durante 120 meses que se deben de hacer para saldar una hipoteca realizada al banco de 10,000 a una tasa de interes del 6% anual.
En primer lugar, para utilizar la función pago, debido a que nos piden el pago mensual y el banco nos dá una tasa anual debemos de encontrar la tasa mensual. Esto lo hacemos dividiendo el 6% entre los 12 meses del año, lo que da como resultado 0.05%.
Entonces, nuestra función y resultados son:
29
Si nos fijamos bien, el resultado es negativo. Esto es debido a que como estamos calculando un pago que vamos a realizar (es una salida de dinero), por tal motivo aparece en rojo.
Ejemplo 2: Queremos ahorrar dinero para que al final de 5 años tengamos 250,000. El banco nos ofrece un interés del 10% anual capitalizables anualmente. ¿Que cantidad de dinero debemos depositar en el banco al inicio de cada año para obtener los resultados deseados?
En este caso, vamos a poner un valor futuro (vf) y el valor actual (va) lo indicamos con un 0. Nuevamente se nos pone en negativo debido a que nosotros vamos a hacer un depósito al banco (tenemos una salida de dinero). En cambio, para el banco, este valor será positivo. Nosotros debemos depositar anualmente 40,949.37 para tener al final de los 5 años 250000.
Ejercicio 1
AUTOMÓVILES
MARCA PRECIO IVA 21%PRECIO
CONTADOINTERÉS
10%
PRECIO CON
INTERÉS
VALOR EN24 CUOTAS
VALOR EN36 CUOTAS
Chevrolet Corsa City $ 39.450,00 ? ? ? ? ? ?Citroen C4 $ 63.000,00 ? ? ? ? ? ?Fiat Palio Weekend $ 54.400,00 ? ? ? ? ? ?Fiat Siena $ 37.200,00 ? ? ? ? ? ?Ford Explorer XLT 4x4 $ 42.900,00 ? ? ? ? ? ?Ford Ranger XLT 4x4 $ 66.600,00 ? ? ? ? ? ?Peugeot 306 $ 25.000,00 ? ? ? ? ? ?Renault Laguna $ 29.500,00 ? ? ? ? ? ?Suzuki Fun $ 32.590,00 ? ? ? ? ? ?Volkswagen Gol $ 39.800,00 ? ? ? ? ? ?Volkswagen Suran $ 13.320,00 ? ? ? ? ? ?
TOTALES ? ? ? ? ? ? ?
Mayor precio coninterés ?Promedio valor en 24cuotas ?Promedio valor en 36cuotas ?
LÓPEZ, Liliana ADM secretaria 1800 casada 2MARTÍNEZ, Sebastián MKT diseñador 1750 solteroNUÑEZ, Cecilia RRHH gerente 4000 solteraPÉREZ, Daniel ADM auxiliar 890 casado 1RAMIREZ, Laura MKT secretaria 1700 solteraSUAREZ, Carlos RRHH auxiliar 780 casado 4
30
1. IVA 21%: Multiplicar el PRECIO por el 21%2. PRECIO CONTADO: Precio + IVA3. INTERÉS 10%: Calcular el 10% del PRECIO CONTADO multiplicando el precio por 10%4. PRECIO CON INTERÉS: Sumarle el PRECIO CONTADO + el Interés5. VALOR EN 24 CUOTAS: Dividir el PRECIO CON INTERÉS por 246. VALOR EN 36 CUOTAS: Dividir el PRECIO CON INTERÉS por 367. TOTALES: sumar los totales de cada columna (función SUMA o Autosuma)8. MAYOR PRECIO CON INTERÉS: calcular mediante la función MAX9. PROMEDIO VALOR EN 24 CUOTAS: aplicar la función PROMEDIO10. PROMEDIO VALOR EN 36 CUOTAS: ídem anterior
Ejercicio 25Consignas
1) Aplicarle a la tabla de datos formatos a elección2) Completar el Nº de legajo (con ayuda de la tecla Ctrl)3) Colocar los sueldos en formato Moneda, con dos decimales.4) Informar lo que se pide en cada caso, aplicando la función que corresponda.
LEGAJO DE PERSONAL
Nº de legajo APELLIDO Y NOMBRE SECTOR CARGO SUELDO ESTADO HIJOS25 DUARTE, Alberto MKT gerente 4500 casado
3
Cantidad de empleados sin hijos
Cantidad de empleados con hijos
Cantidad de empleados del sector
Marketing
Cantidad de empleados con sueldo
superior a $1000
Cantidad total de empleados
Total de sueldos
31
Ejercicio 26
12 Natación9 Tenis
11 Tenis7 Equitación
12 Tenis11 Tenis9 Tenis5 Equitación
12 Tenis12 Natación10 Equitación8 Tenis
12 Equitación8 Equitación
10 Tenis7 Natación
12 Natación
32
12 Natación6 Tenis5 Equitación
10 Tenis5 Tenis
12 Equitación11 Tenis12 Equitación12 Equitación5 Tenis
Cambiar el nombre a Hoja2 por CONTAR2
unción DB(costo;valor_residual;vida;periodo;mes)
Devuelve la depreciación de un bien para un período especificado, usando el método de depreciación de saldo fijo.
Costo = es el valor inicial del bien.
Valor_residual = es el valor al final de la depreciación del bien.
33
Vida = es el número de periodos durante el cual se deprecia el bien (también conocido como vida útil)
Periodo = es el periodo para el que se desea calcular la depreciación.
Mes = es el número de meses del primer año, si no se especifica, se asume que es 12
Ejemplo:
Hemos comprado un coche que vale 20.000 € y suponemos que a los 5 años su valor puede estar por 9.000 €. Queremos saber cual es su depreciación a los 6 meses de haberlo adquirido.
Si introducimos estos datos DB(20000;9000;5;1;6) nos debe dar como resultado 1.480 €, es decir a los seis meses de su compra el coche vale 18.520 €.
Función DDB(costo;valor_residual;vida;periodo;factor)
Devuelve la depreciación de un bien para un período especificado, mediante el método de depreciación por doble disminución de saldo u otro método que se especifique.
El método de depreciación por doble disminución del saldo calcula la depreciación a una tasa acelerada. La depreciación es más alta durante el primer período y disminuye en períodos sucesivos.
Costo = es el valor inicial del bien.
Valor_residual = es el valor al final de la depreciación del bien.
Periodo = es el periodo para el que se desea calcular la depreciación.
Factor = es la tasa a la que disminuye el saldo. Si factor se omite, se supondrá que es 2 ( el método de depreciación por doble disminución del saldo)
Ejemplo:
Sigamos con el ejemplo del coche.
Por tanto si introducimos estos datos DDB(20000;9000;5;1) nos debe dar como resultado 8.000 €, es decir en el primer año de su compra el coche vale 12.000€.
Función DVS(costo;valor_residual;vida;periodo_inicial;periodo_final;factor;sin_cambios)
34
Devuelve la depreciación de un bien para un período especificado, incluyendo periodos parciales, usando el método de amortización acelerada, con una tasa doble y según el coeficiente que especifique.
Las iniciales DVS corresponden a Disminución Variable del Saldo.
Costo = es el costo inicial del bien.
Valor_residual = es el valor final de la depreciación del bien.
Vida = vida útil del bien.
Periodo_inicial = es el periodo inicial para el que se desea calcular la amortización.
Periodo_final = es el periodo final para el que se desea calcular la amortización.
Factor = es la tasa a la que disminuye el saldo. Si el argumento factor se omite, se calculara como 2 ( el método de amortización con una tasa doble de disminución del saldo)
Sin_cambios = es un valor lógico que especifica si deberá cambiar el método directo de depreciación cuando la depreciación sea mayor que el cálculo del saldo.
Si el argumento sin_cambios es VERDADERO, no cambia al método directo de depreciación aun cuando ésta sea mayor que el cálculo del saldo en disminución.
Si el argumento sin_cambios es FALSO o se omite, cambia al método directo de depreciación cuando la depreciación es mayor que el cálculo del saldo en disminución.
Ejemplo:
Si introducimos estos datos DVS(5000;500;5*12;0;1) nos debe dar como resultado 166,67 €, es decir al primer mes de su compra el objeto vale 4833,33 € (166,67€ menos que cuando se compró).
Función INT.PAGO.DIR(tasa;periodo;nper;va)
Calcula el interés pagado durante un período específico de una inversión. Esta función se incluye para proporcionar compatibilidad con Lotus 1-2-3.
Tasa = es la tasa de interes de la inversión.
Periodo = es el período cuyo interés desea averiguar y debe estar comprendido entre 1 y el parámetro nper.
35
nper = es el número total de periodos de pagos.
va = es el valor actual de la inversión.
Por ejemplo: para la función INT.PAGO.DIR(8%/12;1;5*12;30000) el resultado debe ser -196,667 que es el interés pagado por el primer mes de un préstamo de 30.000 € a 5 años.
Función NPER(tasa;pago;va;vf;tipo)
Devuelve el número de pagos de una inversión, basada en pagos constantes y periódicos y una tasa de interés constante.
Tasa = es la tasa de interés por periodo.
Pago = es el pago efectuado en cada periodo, debe permanecer constante durante la vida de la anualidad (cuotas).
Va = es el valor actual o la suma total de una serie de futuros pagos.
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 cero.
Tipo = indica el vencimiento de los pagos (0 al final del periodo, 1 al inicio del periodo).
Por Ejemplo: para la función NPER(6%;-599,55;100000;0;0), debemos obtener 360, que son el número de cuotas para un préstamo de 100.000 € con un interés del 6% y una cuota de 599,55 mensual.
Función PAGO(tasa;nper;va;vf;tipo)
Devuelve el pago de un préstamo basado en pagos y tasas de interés constantes.
Esta función está más detallada en los ejercicios paso a paso que pueden ver al final de la página.
Función PAGOINT(tasa;periodo;nper;va;vf;tipo)
Devuelve el interés pagado por una inversión durante periodo determinado, basado en pagos constantes y periódicos y una tasa de interés constante.
36
Esta función está más detallada en los ejercicios paso a paso que pueden ver al final de la página.
Función PAGOPRIN(tasa;periodo;nper;va;vf;tipo)
Devuelve el pago de un capital de una inversión determinada, basado en pagos constantes y periódicos y una tasa de interés constante.
Esta función está más detallada en los ejercicios paso a paso que pueden ver al final de la página.
Función SLN(costo;valor_residual;vida_útil)
Devuelve la depreciación por método directo de un bien durante un periodo dado.
Costo = es el costo inicial del bien
Valor _residual = es el valor al final de la depreciciacion
Vida_útil = es el número de periodos durante el cual se produce la depreciación del bien. Cálculo sin tener en cuenta valor residual
Por ejemplo: para la función SLN(20000; 9000;5), debemos obtener 2.200 € que es la depreciación por año de vida útil del bien.
Función SYD(costo;valor_residual;vida_útil;periodo)
Devuelve la depreciación por método de anualidades de un bien durante un período específico.
Costo = es el costo inicial del bien.
Valor_residual = es el valor al final de la depreciación.
Vida_útil = es el número de periodos durante el cual se produce la depreciación del bien.
Periodo = es el periodo al que se quiere calcular.
Por Ejemplo: para la función SYD(20000;9000;5;2), debemos obtener 2.933,33 €, que es la depreciación resultante al 2 año.
37
se asume que el valor es cero.
Tipo = indica el vencimiento de los pagos (0 al final del periodo, 1 al inicio del periodo)
Estimar = es la estimación de la tasa de interés, si el argumento estimar se omite se supone que es 10%
Por Ejemplo: para la función TASA(360;-599,55;100000), debemos obtener el 0%, que es el interes mesual, para obtener el interes anual debemos multiplicar ese valor por 12 y el resultado multiplicarlo por 100 para saber el porcentaje.
Función TIR(valores;estimar)
Devuelve la tasa interna de retorno de una inversión para una serie de valores en efectivo.
Estos flujos de caja no tienen por que ser constantes, como es el caso de una anualidad. Pero si los flujos de caja deben ocurrir en intervalos regulares, como meses o años. La tasa interna de retorno equivale a la tasa producida por un proyecto de inversión con pagos (valores negativos) e ingresos (valores positivos) que ocurren en periodos regulares.
Sintaxis TIR(valores;estimar)
Valores = es una matriz o referencia a celda que contengan los números para los cuales se quiere calcular la tasa interna de retorno.
• El argumento valores debe contener al menos un valor positivo y uno negativo para calcular la tasa interna de retorno. De lo contrario devuelve el error #¡NUM!
• TIR interpreta el orden de los flujos de caja siguiendo el orden del argumento valores. Deben introducirse valores de los pagos e ingresos en el orden correcto.
Estimar = es un número que se estima que se aproxima al resultado TIR. En la mayoría de los casos no se necesita proporcionar el argumento estimar, se supone que es 0,1 (10%)
Por Ejemplo:
Para una tabla de inversión como la siguiente A B C D E F1 Ingresos2 Inv. Inicial 1º Año 2º Año 3º Año TIR TIR 2º Año 3 -60000 15000 20000 35000 7% -28%
38
Celda E3=TIR(A3:D3) y celda F3=TIR(A3:C3)
Función TIRM(valores;tasa_financiamiento;tasa_reinversión)
Devuelve la tasa interna de retorno modificada, para una serie de flujos periódicos, considerando costo de la inversión e interés al volver a invertir el efectivo.
Sintaxis TIRM(valores;tasa_financiamiento;tasa_reinversion)
Valores = es una matriz o una referencia a celdas que contienen números. Estos números representan una serie de pagos (valores negativos) e ingresos (valores positivos) que se realizan en períodos regulares.
El argumento valores debe contener por lo menos un valor positivo y otro negativo, para calcular la tasa interna modificada. De lo contrario TIM devuelve el valor de error #¡DIV/O!
Tasa_financiamiento = es la tasa de interés que se abona por el dinero utilizado en el flujo de caja.
Tasa_reinversion = es la tasa de interés obtenida de los flujos de caja a medida que se reinvierten.
Por Ejemplo:
Para una tabla de inversión como la siguiente A B C D E FG
1 Ingresos2 Inv. Inicial 1º Año 2º Año 3º Año Tasa interes Tasa Reinversión TIRM3 -160000 20000 35000 56000 10% 15% -8%
Celda G3=TIR(A3:D3;E3;F3)
Trabajando con funciones financieras (IV)
Función VA(tasa;nper;pago;vf;tipo)
Devuelve el valor actual de una inversión. El valor actual es el valor que tiene actualmente la suma de una serie de pagos que se efectúan en el futuro.
Sintaxis VA(tasa;nper;pago;vf;tipo)
39
Tasa = es la tasa de interés por periodo.
Nper = es el número total de periodos en una anualidad.
Pago = es el pago que se efectúa en cada periodo y que no cambia durante la vida de la anualidad.
Vf = es el valor futuro o saldo en efectivo que se desea lograr después de efectuar el ultimo pago. Si el argumento vf se omite, se considera que el valor es cero. (un préstamo por ejemplo)
Tipo = es el número 0 (vencimiento de los pagos al final del periodo), o 1 (vencimiento al inicio del periodo)
Por Ejemplo: Nos planteamos hacer un plan de jubilación que nos page 500 € mensuales durante 15 años. El plan nos cuesta 35.000 € y el dinero pagado devenga un interés anual de 10%. Utilizaremos la función VA para calcular si merece la pena hacer el plan de jubilación.
Por tanto si escribimos la función VA(10%/12;15*12;500), nos debe delvolver -46.528,72 € que sale en negativo porque es el dinero que se pagaría. Y podemos ver que realmente si merece la pena ya que el dinero invertido fue de 35.000 € y nos devuelven 46.528,72 €.
Función VF(tasa;nper;pago;vf;tipo)
Devuelve el valor futuro de una inversión basada en pagos periódicos y constantes más una tasa de interes constante.
Observaciones
Mantenga uniformidad en el uso de las unidades con las que especifica los argumentos tasa y nper. Si realiza pagos mensuales sobre un préstamo de 5 años con un interés anual del 10 por ciento, use 10%/12 para el argumento tasa y 5*12 para el argumento nper. Si realiza pagos anuales sobre el mismo préstamo, use 10 por ciento para el argumento tasa y 5 para el argumento nper.
Sintaxis VF(tasa;nper;pago;va;tipo)
Tasa = es la tasa de interés por periodo
Nper = es el número total de pagos de una anualidad
Pago = es el pago que se efectúa cada periodo y que no puede cambiar durante la vigencia de la anualidad.
40
Va = es el valor actual de la cantidad total de una serie de pagos futuros. Si el argumento se omite, se considera 0 (cero)
Tipo = indica cuando vencen los pagos(0 al final del periodo 1 al inicio del periodo). Si el argumento tipo se omite, se considera cero.
Por Ejemplo: Vamos a plantearnos ahorrar dinero hasta una fecha límite y con una fecha de inicio. Con un ingreso inicial de 2.000 €, sabemos que interes devengado por la cuenta de ahorro es del 7%, vamos a ingresar cada més 100 € y vamos a esperar 12 meses (1 año) a ver que resultado nos ofrece.
Utilizamos la función VF(7%/12;12;-100;-2000) y obtenemos como resultado 3.383,84 €, lo cual no está nada mal, ya que hemos ganado 183,84 € en un año sin hacer nada, simplemente ahorrando.
Función VNA(tasa;valor1;valor2;...)
Devuelve el valor neto actual de una inversión a partir de una tasa de descuentos y una serie de pagos futuros.
Sintaxis VNA(tasa;valor 1; valor 2;.......)
Tasa = es la tasa de descuento durante un periodo
Valor 1; valor 2..... son de 1 a 29 argumentos que representan los pagos e ingresos. Valor 1; valor 2.. deben tener la misma duración y ocurrir al final de cada periodo.
VNA usa el valor 1; valor 2; .... para interpretar el orden de los flujos de caja. Deberá introducirse los valores de pagos y de los ingresos en el orden adecuado.
Los argumentos que consisten en números, celdas vacías, valores lógicos, se cuentan, los argumentos que consisten en valores de error o texto que no se pueden traducir a números se pasan por alto.
Observaciones
La inversión VNA comienza un periodo antes de la fecha del flujo de caja de valor 1 y termina con el ultimo flujo de caja de la lista. Él cálculo VNA se basa en flujos de caja futuros. Si el primer flujo de caja ocurre al inicio del primer periodo, el primer valor se deberá agregar al resultado VNA, que no se incluye en los argumentos valores.
Por Ejemplo: Consideramos una inversión de 55.000 € y esperamos recibir ingresos en los próximos 5 años, la tasa de descuento anual es del 7%,
41
Tenemos la siguiente tabla de ingresos: A B C D E F1 Ingresos2 Inv. Inicial 1º Año 2º Año 3º Año 4º Año 5º Año 3 -55000 5000 8000 12000 14500 25000
Escribimos la función VNA(7%;B3:F3)+A3 y obtenemos -4.657 €, lo cual quiere decir que no hemos empezado a recibir ganancias todavía aunque las ganancias superan la inversión inicial
NIVEL MEDIO
42
Ejercicio 1:
Un comercio dispone de la siguiente tabla con las ventas del mes Enero de sus empleados correspondientes a las sucursales A y B
Se quiere saber:
1. La cantidad de empleados de cada sucursal (Función CONTAR.SI)2. La cantidad total de empleados. Usar la función CONTARA3. La cantidad total vendida (función SUMA)4. ¿Cuál fue la mayor venta ( función MAX )5. ¿Cuál fue la menor venta ( función MIN )6. El promedio de ventas de ambas sucursales ( función PROMEDIO)7. El promedio de ventas de la sucursal A.8. El promedio de ventas de la sucursal B.9. ¿Cuál fue la máxima venta de la sucursal A?10. ¿Cuál fue la máxima venta de la sucursal B? Para los puntos 7,8,9 y 10 usar
FORMULAS MATRICIALES
