Blog Cursos Excel para Empresas - Página 9 de 15

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).

Buenas prácticas Excel

Escrito por Iván Antona en 14 mayo, 2016. Publicado en Buenas prácticas, Excel Básico.

Utilizaremos la frase que sigue como doctrina para presentar en el conjunto de buenas prácticas Excel.

«Un ejército victorioso gana primero y entabla batalla después. Un ejercito derrotado lucha primero e intenta obtener la victoria después.» (Sun Tzu, El arte de la Guerra)

Buenas prácticas Excel – Pensar en el problema.

– Pensar en el problema que deseamos resolver con Excel: Antes de nada debemos entender qué tenemos que hacer y diseñar sobre papel o mentalmente cómo lo vamos a hacer. Ya sea presentar un informe o resolver un cálculo complejo es fundamental pensar en la estrategia a seguir antes de ponerse a trabajar. A veces invertimos mucho tiempo en hacer cosas sobre la hoja de Excel que posteriormente tienen poco valor.

Buenas prácticas Excel – Separar datos de cálculos.

– Separar los datos de las funciones o fórmulas: Es fundamental separar los datos de tipo «constante» de las funciones o fórmulas. El ejemplo por excelencia es el IVA. Veamos por ejemplo un cálculo del tipo =A2*21% donde el segundo factor (21%)es el IVA.

La forma que debiéramos seguir para ajustarnos a una buena práctica sería definir la celda G2 igual a 21%. Así podemos escribir el calculo anterior como =A2*$G$2. Es fundamental hacer un buen uso de las referencias. En este caso una referencia absoluta.

De esta forma si existe un cambio en la constante IVA al 17% o al 25% simplemente cambiaremos el valor en la celda B5 y automáticamente se actualizará en toda la hoja.

Buenas prácticas Excel – El diseño lo último.

– Dejar el diseño para el final: Otro detalle importante es dejar las florituras y el espíritu artístico para el final. En este caso nos interesa que el informe con los datos correctos estén listos lo antes posible. Una vez resulto el problema podremos utilizar los distintos asistentes de formato, colores, y plantillas disponibles para dar a nuestro trabajo un aspecto más profesional.

Teniendo en cuenta estos tres aspectos podremos afrontar cualquier problemática de oficina con la hoja de Excel y salir victoriosos antes de entablar la batalla.

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.

Nombrar rangos es hacer referencia a la información contenida en varias celdas mediante un nombre en vez de indicar las filas y columnas que lo componen.

Nombrar rangos en Excel

Escrito por Iván Antona en 12 mayo, 2016. Publicado en Excel Básico.

¿Qué es nombrar rangos en Excel?

Nombrar rangos es hacer referencia a la información contenida en varias celdas adyacentes mediante un nombre en vez de indicar las filas y columnas que lo componen. Por ejemplo el conjunto de filas A1:B7 lo podemos denominar Tot-Ventas.

Para asociar el nombre «Tot-Ventas» al rango especificado A1:B7 debemos hacer clic en el menú «Formulas» para acceder al asistente de Gestión de nombres. Es aquí en dónde se puede asignar un nombre a un rango.

¿Cómo nombrar rangos de forma automática?

Podemos nombrar un rango de forma automática seleccionando el origen de datos al completo como se muestra en la siguiente ilustración.

Una vez seleccionado el rango pulsaremos la combinación de teclas Control+Mayúsculas+F3.

Al dejar marcado «Fila superior» estaremos diciendo a Excel que queremos que nos nombre un rango para «Vendedores» y un rango para «Ventas». El rango de «Vendedores» estará compuesto por «Pedro, Juan, Rosa, Marta, Alvaro y María» Lo mismo ocurre con «Ventas» que en este caso estará formado por las cifras.

Se puede comprobar el funcionamiento del rango nombrado haciendo una suma de las «Ventas» para conocer las ventas totales. Para ello en la celda B9 se añade la función suma y como argumento en vez de pasar los datos de las celdas que contienen los valores de ventas escribiremos el nombre del rango «Ventas». Comprobando como aparece en el desplegable como si fuera un argumento más.

Para las filas ocurre lo mismo. Para Pedro el rango generado es B2:E2, para Juan será B3:E3. Podemos comprobar que se han creado todos los rangos haciendo clic en la pestaña de «Fórmulas» y seguidamente en «Administrador de nombres».

¿Y si quisiera el rango completo A1:B7?

En el caso de necesitar el rango completo sin diferenciar las etiquetas de columnas habría que seleccionar el rango y nombrarlo manualmente desde el Gestor de nombres que se encuentra en el menú de «Fórmulas».

Si quieres descargar el fichero con este ejemplo pulsa este enlace.