Comprendamos qué es en sí una búsqueda, cuando queremos encontrar alguna información de algo no buscamos directamente por lo que buscamos pues lo desconocemos, realizamos una búsqueda de una propiedad o algo similar que conocemos que puede tener lo que buscamos. Por ejemplo, si buscamos a una persona, describimos su aspecto físico, si buscamos el nº de teléfono de un restaurante, buscamos en la guía de teléfonos por el nombre del restaurante. Normalmente el dato que queremos encontrar no lo conocemos por eso buscamos por otros datos que sí conocemos.
Estas son las funciones disponibles por Excel para realizar búsquedas:
Función | Descripción |
AREAS(ref) | Devuelve el número de rangos de celdas contiguas |
BUSCAR(…) | Busca valores de un rango de una columna o una fila |
BUSCARH(valor_buscado;matriz_buscar_en;indicador_filas;ordenado) | Busca en la primera fila de la tabla o matriz de valores |
BUSCARV(valor_buscado;matriz_buscar_en;indicador_co-lumnas;ordenado) | Busca un valor en la primera columna de la izquierda |
COINCIDIR(valor_buscado;matriz_buscar_en;tipo_de_coin-cidencia) | Devuelve la posición relativa de un elemento |
COLUMNA(ref) | Devuelve el número de columna de una referencia |
COLUMNAS(matriz) | Devuelve el número de columnas que componen la matriz |
DESREF(ref;filas;columnas;alto;ancho) | Devuelve una referencia a un rango |
DIRECCION(fila;columna;abs;a1;hoja) | Crea una referencia de celda en forma de texto |
ELEGIR(num_indice;valor1;valor2;…) | Elige un valor o una accion de una lista de valores |
FILA(ref) | Devuelve el número de fila |
FILAS(matriz) | Devuelve el número de filas |
HIPERvínculo(ubicación_del_vínculo;nombre_descriptivo) | Crea un acceso directo a un documento |
IMPORTARDATOSDINAMICOS(camp_datos;tablas_dinámicas;campo1;elemento1;campo2;elemento2…) | Extrae datos almacenados en una tabla dinámica |
INDICE(matriz;num_fila;num_columna) | Devuelve el valor de una celda en la intersección de una fila y una columna |
INDIRECTO(ref;a1) | Devuelve una referencia especificada |
TRANSPONER(matriz) | Intercambia las filas por las columnas en una matriz |
Ejercicio paso a paso. Funciones que buscan. |
Objetivo. |
| ||||
Practicar el uso de las funciones que buscan valores en Excel2007.
Ejercicio paso a paso. |
| ||
Ahora vamos a utilizar la función TRANSPONER(), que es una poco más compleja, pero tampoco mucho.
Recordemos que transponer significa cambiar filas por columnas y viceversa. Es decir si tenemos los datos en fila, al transponer obtendremos los datos en columna.
Funciones financieras. |
Excel es una de las herramientas más potentes para trabajar con información y cálculos financieros, ofrece una amplia gama de funciones prediseñadas para crearte tu propia "caja de ahorros en casa".
Todas estas funciones están agrupadas en la categoría de Financieras.
Vamos a estudiar la amplia gama de funciones financieras que nos ofrece Excel:
Función | Descripción y Ejemplo |
DB(costo;valor_residual;vida;periodo;mes) | Devuelve la depreciación de un bien para un período especificado, método de depreciación de saldo fijo |
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 |
DVS(costo;valor_residual;vida;periodo_inicial;periodo_final;factor;sin_cambios) | Devuelve la depreciación de un bien para un período especificado, incluyendo periodos parciales |
INT.PAGO.DIR(tasa;periodo; nper;va) | Calcula el interés pagado durante un período específico de una inversión |
NPER(tasa;pago;va;vf;tipo) | Devuelve el número de pagos de una inversión |
PAGO(tasa;nper;va;vf;tipo) | Devuelve el pago de un préstamo basado en pagos y tasas de interés constantes |
PAGOINT(tasa;periodo;nper; va;vf;tipo) | Devuelve el interés pagado por una inversión durante periodo determinado |
PAGOPRIN(tasa;periodo;nper; va;vf;tipo) | Devuelve el pago de un capital de una inversión determinada |
SLN(costo;valor_residual;vida) | Devuelve la depreciación por método directo de un bien durante un período dado |
SYD(costo;valor_residual;vida;periodo) | Devuelve la depreciación por método de anualidades de un bien durante un período específico |
TASA(nper;pago;va;vf;tipo; estimar) | Devuelve la tasa de interés por periodo de un préstamo o una inversión |
TIR(valores;estimar) | Devuelve la tasa interna de retorno de una inversión para una serie de valores en efectivo |
TIRM(valores;tasa_financiamiento;tasa_reinversión) | Devuelve la tasa interna de retorno modificada |
VA(tasa;nper;pago;vf;tipo) | Devuelve el valor actual de una inversió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 interés constante |
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 |
Ejercicio paso a paso. Funciones financieras. |
Objetivo. |
| ||||
Practicar el uso de las Funciones Financieras en la creación de hojas de cálculo con Excel2007.
Ejercicio paso a paso. |
| ||
Vamos a desarrollar un ejemplo práctico para calcular el pago de un préstamo basándonos en pagos constantes y una tasa de interés constante.
Ahora que ya conocemos los parámetros que necesita la función, podemos crear el ejemplo:
Vamos a pedir un préstamo de C$100,000, en un período de 30 años, es decir 30*12=360 nº de pagos mensuales, a un interés del 6%.
Así pues, ya tenemos un ejemplo de un préstamo.
1. Sitúate en la celda A1 y escribe Préstamo
2. Sitúate en la celda A2 y escribe Tasa
3. Sitúate en la celda A3 y escribe Nº Pagos
4. Sitúate en la celda A4 y escribe Tipo
5. Sitúate en la celda A5 y escribe Cuota Mensual
6. Sitúate en la celda B1 y escribe C$ 100.000
7. Sitúate en la celda B2 y escribe 6 %
8. Sitúate en la celda B3 y escribe 360
9. Sitúate en la celda B4 y escribe 0
10. Sitúate en la celda B5 y escribe =PAGO(B2/12;B3;B1;0;B4)
Con esta función indicamos que el vencimiento del pago se realiza al final del período y que no existen cuotas al finalizar los pagos.
Como resultado debemos obtener C$ -599,55 que será la cuota mensual. El número sale negativo porque el efectivo que se paga, por ejemplo depósitos en cuentas de ahorros, cuotas de un préstamo, se representa con números negativos; el efectivo que se recibe, se representa con números positivos.
Con la función PAGO también podemos calcular qué cuota mensual debemos ingresar para ahorrar una cantidad de dinero en X años.
Vamos a calcular cómo podemos ahorrar C$ 30.000 en 5 años, con un interés del 6%.
11. Sitúate en la celda C1 y escribe Ahorro
12. Sitúate en la celda C2 y escribe Tasa Anual
13. Sitúate en la celda C3 y escribe Años
14. Sitúate en la celda C4 y escribe Ingresos Mensuales
15. Sitúate en la celda D1 y escribe C$30.000
16. Sitúate en la celda D2 y escribe 6%
17. Sitúate en la celda D3 y escribe 5
18. Sitúate en la celda D4 y escribe PAGO(D2/12;D3*12;0;D1)
Como resultado debemos obtener en la celda D4 la cantidad de C$ -429,98 .
Vamos a calcular ahora los intereses pagados en un período de tiempo por un préstamo, por ejemplo los intereses del primer ejemplo.
Para realizar este ejercicio utilizaremos la función PAGOINT
Esta función tiene la siguiente sintaxis
PAGOINT (tasa;periodo;nper;va;vf;tipo)
tasa = es el tipo de interés del préstamo.
período = es el período para el que se desea calcular el interés y debe estar entre 1 y el parámetro nper
nper = es el número total de pagos del préstamo.
va = es el valor actual del total de pagos
vf = es el valor futuro o un saldo en efectivo que se desea lograr trás el último pago. Si vf se omite, se asume que vale 0, es decir, trás el último pago no queda ningún saldo pendiente.
tipo = indica el vencimiento de pagos.
(tipo = 0) –> al final del período
(tipo = 1) –> al inicio del período
19. Sitúate en la celda E1 y escribe Préstamo
20. Sitúate en la celda E2 y escribe Tasa Anual
21. Sitúate en la celda E3 y escribe Interés en la Cuota Nº
22. Sitúate en la celda E4 y escribe Cantidad de Cuotas
23. Sitúate en la celda E5 y escribe Interés
24. Sitúate en la celda F1 y escribe C$ 100.000
25. Sitúate en la celda F2 y escribe 6%
26. Sitúate en la celda F3 y escribe 1
27. Sitúate en la celda F4 y escribe 360
28. Sitúate en la celda F5 y escribe PAGO(F2/12;F3;F4)
Esta función nos debe devolver -361,80 € que es el interés pagado en la primera cuota del préstamo. Cambiando el valor en F3 podrás ver el interés pagado en cada caso.
Vamos a calcular ahora las cuotas amortizadas para un préstamo, seguimos basándonos en el primer ejercicio.
Para realizar este ejercicio utilizaremos la función PAGOPRIN
Esta función tiene la siguiente sintaxis
PAGOPRIN(tasa;periodo;nper;va;vf;tipo)
tasa = es el tipo de interés del préstamo.
período = es el período para el que se desea calcular la amortización y debe estar entre 1 y el parámetro nper
nper = es el número total de pagos del préstamo.
va = es el valor actual del total de pagos
vf = es el valor futuro o un saldo en efectivo que se desea lograr trás el último pago. Si vf se omite, se asume que vale 0, es decir, trás el último pago no queda ningún saldo pendiente.
tipo = indica el vencimiento de pagos.
(tipo = 0) –> al final del período
(tipo = 1) –> al inicio del período
29. Sitúate en la celda A8 y escribe Préstamo
30. Sitúate en la celda A9 y escribe Tasa Anual
31. Sitúate en la celda A10 y escribe Cálculo amortización en cuota nº
32. Sitúate en la celda A11 y escribe Cuotas Totales
33. Sitúate en la celda A12 y escribe Amortizado
34. Sitúate en la celda B8 y escribe C$100.000
35. Sitúate en la celda B9 y escribe 6 %
36. Sitúate en la celda B10 y escribe 1
37. Sitúate en la celda B11 y escribe 360
38. Sitúate en la celda B12 y escribe PAGOPRIN(B9/12;B10;B11;B8)
Como resultado debemos obtener en la celda B12 la cantidad de C$ – 99,55.que si nos fijamos es el resultado de la diferencia de quitar los intereses a la cuota total del préstamo. Como vemos en la primera cuota de la amortización los intereses son más del 80% y la amortización propia del préstamo no llega al 20%.
39. Guarda el libro de trabajo en la carpeta Mis documentos del disco duro con el nombre de Funciones Financieras.
40. Cierra el libro de trabajo.
Otras funciones |
Además de las funciones anteriormente mencionadas, existe un gran abanico de funciones de diferentes categorías que nos pueden ser de gran utilidad.
Función | Descripción |
Funciones matemáticas y trigonométricas | |
ABS(número) | Devuelve el valor absoluto de un número |
ALEATORIO() | Devuelve un número entre 0 y 1 |
COMBINAT(número;tamaño) | Devuelve el número de combinaciones para un número determinado de elementos |
COS(número) | Devuelve el coseno de un ángulo |
ENTERO(número) | Redondea un número hasta el entero inferior más próximo |
EXP(número) | Realiza el cálculo de elevar "e" a la potencia de un número determinado |
FACT(número) | Devuelve el factorial de un número |
NUMERO.ROMANO(número,forma) | Devuelve el número pasado en formato decimal a número Romano |
PI() | Devuelve el valor de la constante pi |
POTENCIA(número;potencia) | Realiza el cálculo de elevar un número a la potencia indicada |
PRODUCTO(número1;número2;…) | Devuelve el resultado de realizar el producto de todos los números pasados como argumentos |
RAIZ(número) | Devuelve la raiz cuadrada del número indicado |
RESIDUO(número;núm_divisor) | Devuelve el resto de la división |
Funciones estadísticas | |
MEDIA.ARMO(número1;número2;…) | Devuelve la media armónica de un conjunto de números positivos |
MAX(número1;número2;…) | Devuelve el valor máximo de la lista de valores |
MIN(número1;número2;…) | Devuelve el valor mínimo de la lista de valores |
MEDIANA(número1;número2;…) | Devuelve la mediana de la lista de valores |
MODA(número1;número2;…) | Devuelve el valor que más se repite en la lista de valores |
PROMEDIO(número1;número2;…) | Devuelve la media aritmética de la lista de valores |
VAR(número1;número2;…) | Devuelve la varianza de una lista de valores |
K.ESIMO.MAYOR(matriz;k) | Devuelve el valor k-ésimo mayor de un conjunto de datos |
K.ESIMO.MENOR(matriz;k) | Devuelve el valor k-ésimo menor de un conjunto de datos |
Funciones lógicas | |
FALSO() | Devuelve el valor lógico Falso |
VERDADERO | Devuelve el valor lógico Verdadero |
SI(prueba_logica;valor_si_verdadero;valor_si_falso) | Devuelve un valor u otro, según se cumpla o no una condición |
NO(valor_lógico) | Invierte el valor lógico proporcionado |
Y(valor_logico1;valor_logico2;…) | Comprueba si todos los valores son verdaderos |
O(valor_logico1;valor_logico2;…) | Comprueba si algún valor lógico es verdadero y devuelve VERDADERO |
Funciones de información | |
ESBLANCO(valor) | Comprueba si se refiere a una celda vacía |
ESERR(valor) | Comprueba si un valor es un error |
ESLOGICO(valor) | Comprueba si un valor es lógico |
ESNOTEXTO(valor) | Comprueba si un valor no es de tipo texto |
ESTEXTO(valor) | Comprueba si un valor es de tipo texto |
ESNUMERO(valor) | Comprueba si un valor es de tipo numérico |
TIPO(valor) | Devuelve un número que representa el tipo de datos del valor |
Ejercicio paso a paso. Otras funciones. |
Objetivo. |
| ||||
Practicar el uso de otras funciones en Excel2007.
Ejercicio paso a paso. |
| ||
Autor:
Danny Aburto
| Página siguiente |