Funciones Archives - Página 4 de 7

La función Si en Excel evalúa una condición. Si el resultado de la condición es “Verdadero” realizará una tarea si es falso realizará otra tarea.

Función Si en Excel. Funciones condicionales.

Escrito por Iván Antona en 17 mayo, 2016. Publicado en Funciones, Funciones Lógicas.

Función Si en Excel. Para qué sirve.

La función Si en Excel evalúa una condición si el resultado de esta evaluación es “Verdadero” entonces realizará una tarea. Sin embargo si el resultado de la evaluación es falso entonces realizará otra tarea diferente. Esta función pertenece a la familia de funciones lógicas. Si el precio de una determinada acción baja un 5% con respecto a un precio de compra determinado entonces realizaremos una venta del valor bursátil.

Es importante conocer determinados operadores en Excel para poder construir la función condicional “Si”.

Argumentos de la función.

La función si admite tres argumentos. El primero es estrictamente obligatorio y el resultado de la función si no se utilizan los otros dos argumentos entonces será “TRUE”/“VERDADERO” o “FALSE”/”FALSO”

Comprobación_lógica: Realiza una comprobación que como resultado me devolverá un “Verdadero” o un “Falso”

Valor_si_verdadero: Acciones que queremos desencadenar en caso de cumplirse la condición. Puede ser un mensaje de texto que introduciremos entre comillas o la llamada a otra función o un cálculo.

Valor_si_falso: Acciones que queremos desencadenar en caso de NO cumplirse la condición. Puede ser un mensaje de texto que introduciremos entre comillas o la llamada a otra función o un cálculo.

Ejemplo de uso de la función Si.

Volvemos al ejemplo anterior. Tenemos una inversión sobre un determinado valor bursátil “Acme SA”. Sabemos que las acciones se compraron al precio de 10€. Además tenemos marcados los siguientes objetivos:

Si el valor supera en un 10% el precio de compra lo venderemos para ganar un margen.
Si el valor pierde un 5% con respecto al precio de compra lo venderemos para minimizar la pérdida.

Con estas premisas se realiza un chequeo diario de los valores bursátiles para conocer su precio de cierre y evaluar el resultado de nuestra inversión y así poder mediante la condición “SI” tomar una decisión.

El análisis inicial será el siguiente:

El resultado esperado debe ser una tabla como la que se muestra a continuación. En donde aquellas cifras que cumplan la condición desencadenan un mensaje o bien de venta por beneficio o bien de venta por pérdida.

En este caso “anidaremos” una función Si dentro de otra función Si. Anidar una función dentro de otra significa que uno de los argumentos del Si inicial será otra función Si.

SI( Prueba lógica; “Valor si se cumple”; “Valor si no se cumple”). Valor si no se cumple = SI(Prueba lógica; “Valor si se cumple”; ”Valor si no se cumple”)

Si (Prueba lógica; Valor si se cumple;SI(Prueba lógica;Valor si se cumple;Valor si no se cumple)

Y traducido con nuestras condiciones podemos escribir lo siguiente:

Si(Precio de cierre >= 10€+10€*10%; “Venta por Beneficio”;SI(Precio de cierre <= 10€ – 10€*5%; “Venta por pérdida”;”Mantener”))

Traducido en Excel tenemos la siguiente expresión:

=SI(E2<=C2-(C2*5%);"Vender por pérdida";SI(E2>=C2+(C2*10%);"Vender por Beneficio";"Mantener"))

Función Si en inglés

La función Si en inglés se denomina

IF (Logical_test;Value_if_true;Value_if_false)

Descarga el ejemplo de uso de la función Si.

Puedes descargar el ejemplo aquí:

Función-Si-en-Excel

El cuadro de amortización en Excel primero se modela la hoja y después se hace uso de la función Pago de Excel. Pago Mensual.Número de Pago. Interés.

Cuadro de amortización en excel de interés fijo

Escrito por Iván Antona en 16 mayo, 2016. Publicado en Excel Avanzado, Funciones, Funciones Financieras.

Crear el cuadro de amortización en Excel.

En primer lugar se modela la hoja creando los siguientes encabezados para poder crear el cuadro de amortización en Excel.

Pago Mensual (Mes).
Número de Pago.
Interés Principal (Intereses).
Cuota Mensual.
Monto Restante.

A parte crearemos encabezados adicionales que posteriormente necesitamos para completar la tabla.

Monto del préstamo (Préstamo).
Interés nominal anual (Interés).
Interés nominal mensual.
Mensualidades o periodos (Periodos).

Bajo el encabezado “Mes” rellenaremos las dos primeras celdas con los meses uno y dos del préstamo. Dicho de otra manera serán los meses en los que nos pasarán los dos primeros recibos. En este ejemplo el primer recibo se pasa en el mes de marzo-2015 y el segundo recibo en el mes de abril-2015. Copiaremos el valor de las dos celdas y arrastrando completamos las siguientes celdas hasta llegar al mes de finalización del préstamo. En el ejemplo que puedes descargar al final de esta entrada finaliza febrero-2022.

Bajo el encabezado “Núm.Pago” se rellena con los valores desde 1 hasta 84 y corresponde con los vencimientos de las cuotas del préstamo. Como el préstamo del ejemplo tiene una duración de siete años y realizamos pagos mensuales o dicho de otra manera los vencimientos son mensuales entonces en total tenemos 12×7=84 vencimientos para nuestro cuadro de amortización en Excel.

Bajo el encabezado “Intereses” figuran los intereses que pagamos en cada cuota. En este ejemplo se utiliza el sistema de amortización francés para generar el cuadro de amortización en Excel y en la hoja podemos utilizar la “Función PagoInt” para averiguar el valor de cada celda. La “Función PagoInt” la completamos como se indica en la imagen.

El primer argumento de la función pagoint de Excel responde al interés mensual, el segundo argumento de la función es el número de vencimiento que para el ejemplo correspondería con 1 o lo que es lo mismo el primer recibo, el tercer argumento se refiere al número total de cuotas que previamente habíamos calculado como 84. Finalmente el último argumento es el monto total del préstamo que debe llevar signo negativo.

Para calcular los valores referidos al encabezado “Principal” vamos a utilizar la “Función PagoPrin de Excel” muy similar a la “Función PagoInt”. Esta función nos permite averiguar de cada cuota que pagamos al banco que cantidad es la destinada a amortizar el capital prestado sin tener en cuenta los intereses ya que los hemos calculado con la función PagoInt de Excel previamente.

El primer argumento de la “Función PagoPrin de Excel” corresponde con el interés nominal mensual. El segundo argumento se refiere al vencimiento o mes, para este ejemplo nos encontramos en primer recibo luego seleccionamos el número de pago 1.

El cuarto argumento se refiere al número total de periodos, meses o cuotas del préstamos que como hemos indicado anteriormente se trata de 84. Para finalizar, el último argumento, se trata del monto total del préstamo solicitado al banco o entidad financiera y que igual que en la función PagoInt debe ir con signo negativo.

El encabezado referido a “Cuota” nos indica la cantidad total que debemos abonar en el recibo que nos domicilia la entidad financiera o banco. Podemos conseguir este valor con la suma de los valores anteriores los intereses de la cuota y el principal de la cuota. Como se trata de un interés fijo el valor de todas las cuotas será el mismo pero al amortizar mediante el sistema francés vemos como cada vez destinamos menos cantidad de la cuota en el pago de intereses. Es decir, los intereses relativos al préstamo, se pagan de más a menos. Por el contrario el dinero destinado en la cuota a amortizar el proyecto es creciente y se paga de menos a más.

Para averiguar los valores del encabezado “Monto restante”. Simplemente restaremos al capital del préstamo la cantidad destinada de nuestras cuotas a su amortización en nuestro cuadro de amortización en Excel.

Cuadro_Amortizacion_Excel

Para elaborar un cuadro de amortización a interés fijo en Excel debemos conocer los siguientes datos.El principal .El interés nominal. Y los periodos.

Cuadro amortización a interés fijo en excel

Escrito por Iván Antona en 15 mayo, 2016. Publicado en Funciones, Funciones Financieras.

Crear un cuadro de amortización a interés fijo en Excel.

Solicitar un préstamo a interés fijo es algo habitual cuando adquirimos un vehículo. También se suelen solicitar préstamos a interés fijo para realizar pequeños proyectos familiares o personales como una reforma de vivienda o cursar unos estudios veremos como crear un cuadro de amortización a interés fijo.
En las siguientes entradas de blog se desgrana un método para crear una “tabla de cuotas” o “cuadro de amortización a interés fijo en excel”. Este tipo de tablas son interesantes para conocer qué cantidad de dinero del que pagamos en cada cuota va destinada al pago de intereses y que cantidad va destinada a la amortización del préstamo solicitado para nuestro proyecto.
Para elaborar un cuadro de amortización en Excel con interés fijo debemos conocer los siguientes datos.

Principal o capital que solicitamos al banco o entidad financiera.
Interés nominal o interés que pagamos al banco o financiera.
Periodos o número de cuotas.

Toda esta información nos la suministra el banco antes de formalizar el préstamo y es fundamental y obligatorio conocerla antes de realizar la firma del contrato.
Por otro lado debemos conocer varias funciones de Excel que utilizaremos para realizar este cuadro de amortización.

Función PAGO em Excel.
Función PAGOINT en Excel.
Función PAGOPRIN en Excel.

Datos del modelo. Para el cuadro de amortización a interés fijo.

El préstamo, el tipo de interés que nos aplica el banco o la entidad financiera que nos presta el dinero, y el número de años o meses de duración que tendrá el préstamo deben quedar definidos en nuestro modelo para poder ser utilizados posteriormente. Todas las funciones que se utilizarán para calcular el cuadro de amortización precisan que el tipo de interés se encuentre calculado mensualmente. Es por ello que el interés nominal utilizado en los ejemplos que se presentan a continuación se divide entre 12 (meses).

La función indirecto devuelve el contenido de una celda referencianda desde un texto.La función es capaz de traducir una palabra a una referencia.

Función Indirecto Excel.

Escrito por Iván Antona en 13 mayo, 2016. Publicado en Funciones, Funciones Búsqueda y Referencia.

La función indirecto en excel devuelve el contenido de una celda pero recogiendo la referencia a dicha celda a partir de un texto. La función indirecto pertenece a la familia de funciones de búsqueda y referencia. La función indirecto es capaz de traducir una palabra o cadena de texto a una referencia. Como sabemos en Excel existen distintos tipos de datos, datos del tipo número, datos del tipo fecha, datos del tipo moneda, pero también datos en formato «texto». Palabras escritas en celdas. Si en una celda escribimos una referencia y queremos utilizar esa cadena de texto como referencia para un cálculo entonces tendremos que pasarla previamente por la función indirecto para que convierta ese «texto» en una referencia.

Una referencia es “el nombre y apellidos de una celda o rango”. Por ejemplo. Si nos referimos a los 13.722€ la forma de nombrar esa celda es con C4.

La forma más sencilla de utilizar la función “indirecto” sería = indirecto(“C4”) y nos devolverá el valor 13.722€. Sin embargo esta forma de trabajar no tiene mucho sentido ya que podremos obtener el dato mucho más rápido escribiendo =C4 en la celda.

Si nos fijamos bien en el ejemplo podemos observar que el argumento en este caso va entre comillas. Esto es así porque el argumento que le estamos pasando a la función indirecto es una cadena de texto. La función indirecto recibe como argumento un texto y lo convierte en una referencia. Así por ejemplo si nos referimos al rango A1:B2 para pasarlo como argumento a la función tendremos que hacerlo como =indirecto («A1:B2»). La función indirecto, internamente, elimina las comillas e interpreta el resto como una referencia.

Este ejemplo pretende demostrar que mediante un texto podremos hacer referencia a una celda. Luego a la pregunta ¿Funcionaría también para un rango? La respuesta es que sí. La función indirecto devuelve el contenido referenciado por el texto pasado en el argumento de la función.

Una forma muy habitual de utilizar indirecto es mediante el uso de listas enlazadas y rangos nombrados.

Función indirecto y rangos nombrados.

Podemos nombrar un rango con una etiqueta que sea fácil de recordar para nosotros y después utilizar esa etiqueta como argumento para la función. Por ejemplo en un listado de visitas y conversiones a un determinado negocio vamos a definir el rango de conversiones C2:C13 con la etiqueta de texto «conversión».

Ahora si hacemos uso de la función =indirecto(conversión) el resultado será el contenido de C2:C13 es decir un rango. No podemos devolver el resultado de un rango a una celda sin embargo las conversiones son datos numéricos que si podemos sumar. La suma de los elementos de un rango si que los podremos devolver a una celda. Podemos escribir la siguiente función =suma(indirecto(«conversión»)) y tendremos el resultado esperado.

La función Sumar.Si.Conjunto nos permite realizar sumas aplicando "criterios", o "filtros". Estos filtros son los criterios que se aplican a una columna.

Función Sumar.Si.Conjunto en Excel.

Escrito por Iván Antona en 12 mayo, 2016. Publicado en Funciones, Funciones Matemáticas.

Función Sumar.Si.Conjunto en Excel.

La función Sumar.Si.Conjunto nos permite realizar sumas aplicando «criterios», o «filtros». Entendemos estos filtros como los criterios que se aplican a una columna. Sumará los datos de una columna en base a los “filtros” que hemos decidido. Esta función pertenece a la familia de funciones matemáticas.

Argumentos de la función Sumar.Si.Conjunto.

La función necesita como argumentos la columna de datos a sumar, el criterio para realizar la suma y la columna que lo contiene, puede haber más de un criterio. Como mínimo tendremos que especificar tres argumentos. En su forma más sencilla. Aplicando un solo criterio, la función se comportará exactamente igual que su hermana pequeña, la función sumar.si.

Rango de suma: Es la columna que contiene los datos que deseamos sumar. Tienen que ser numéricos.
Rango de criterio 1: Es la columna que contiene el primer criterio a aplicar o columna de filtro.
Criterio 1: Es el criterio en sí o el filtro que aplicamos.
Rango criterio 2….
Criterio 2…

Ejemplo de uso de la función Sumar.Si.Conjunto.

Partiremos de un listado de ventas en el que encontramos transacciones de ventas realizadas por distintas empresas a lo largo del año. En la Fila2 podemos ver que en el mes de “Agosto”, la empresa “ACEITAR” cuyo código de tienda es “2” y que se encuentra ubicada en la zona “Centro” ha realizado una venta por valor de “515,75€”. En esta venta no se ha realizado ningún tipo de descuento. Se ha realizado un pago “Aplazado” y el cliente tenía “57 años”.

Si quisiéramos conocer que cantidad total de ventas se han realizado en el mes de “Enero” podríamos realizar un filtro en la columna A etiquetada como “mes” y tendríamos una visión como sigue a continuación. Una vez filtrados los datos podemos seleccionar todos los datos de la columna “Importe de Ventas” y visualizaremos el resultado de la suma de todos ellos en la barra inferior de Excel.

De esta manera hemos obtenido el resultado de una “Suma filtrada”. Si además ahora queremos realizar un estudio en mayor profundidad podremos aplicar un nuevo filtro pero esta vez sobre la columna D, “Zona”, para averiguar la suma filtrada de ventas realizadas en el mes de enero en cada una de las zonas geográficas. Así manteniendo el filtro de la columna A en “Enero” seleccionaremos el filtro “Norte” de la columna D. Una vez filtrados los datos seleccionaremos los datos de la columna de “Importe de Ventas” y obtendremos el resultado de sumar cada una de sus filas.

Podríamos realizar este mismo procedimiento para averiguar la suma filtrada para la zona “Centro y Sur”.

Una vez que hemos entendido el concepto de “Suma Filtrada” nos preguntamos ¿Qué función de Excel permite realizar una suma filtrada sin tener que aplicar el autofiltro y seguidamente seleccionar los datos para sumarlos?

Volvemos sobre la hoja de Excel. Y elaboramos una tabla como se muestra en la siguiente ilustración.

Nos situaremos sobre la celda K2. Y utilizaremos la función “SUMIFS” para obtener la suma filtrada por cada zona en el mes de Enero.

El primer argumento: Es el rango donde se encuentra la cifra de ventas que queremos que se sume.
El segundo argumento: Es la primera columna sobre la que vamos a aplicar el “Filtro” en este caso “Mes”
El tercer argumento: Es el filtro en sí que marcamos. En este caso “ENERO”
El cuarto argumento: Es la columna “Zona” sobre la que aplicaremos los filtros de zona.
El quinto argumento: Es la zona en sí.

Como resultado final obtendremos la suma para cada una de las zonas. Una vez obtenido el primer resultado debemos arrastrar hacia abajo la fórmula para que se auto rellene toda la tabla.

Cuidado con las referencias absolutas. El criterio “ENERO” debe ser fijo. Sin embargo el criterio de zona “Norte, Centro y Sur” queremos que avance cuando arrastramos hacia abajo.

Función Sumar.Si.Conjunto en Inglés.

La función Sumar.Si.Conjunto en inglés se denomina. SUMIFS(sum_range;criteria1_range1;criteria1;…)

También te puede interesar la función Contar.Si.Conjunto prácticamente igual a la función que acabamos de ver pero en vez de sumar el contenido de las celdas nos dice cuantas celdas tienen datos que cumplan los criterios.