Aplicación en Excel para el cálculo de préstamos (todo en uno)

En esta ocasión, no vamos a hacer ninguna nueva aplicación, ni ninguna utilidad que no hayamos visto antes en el blog. Lo que voy a presentaros es simplemente un libro de Excel, donde tendremos integradas las siguientes aplicaciones:

- Cálculo de préstamos e hipotecas mediante el método francés (el común y usual en este tipo de operaciones): cuotas constantes, donde el capital que se amortiza va creciendo en cada cuota, y los intereses van decreciendo en cada cuota.

- Cálculo de préstamos según el método americano: cuotas constantes excepto la última que es donde se amortiza el principal del préstamo. Las cuotas constantes sólo llevan cargo de intereses.

- Cálculo de préstamos con amortización de capital constante: cuotas variables y decrecientes, con amortización de capital constante en cada cuota, e intereses decrecientes en cada cuota.

- Calcular una TAE (tasa anual equivalente, o tasa anual efectiva) y un interés nominal.

Todo esto ya lo hemos visto en el blog, en diferentes artículos, así que no volveremos a explicarlo. Simplemente he juntado todo en la misma aplicación, para tenerlo bien ordenadito, y no tener que abrir cuatro ficheros para calcular esas cuatro cosas diferentes.

De momento es la primera versión, con lo que no descarto ampliarla en un futuro.

Aquí os dejo un pantallazo de lo que se os cargará al abrir el fichero Excel. Recordad habilitar las macros, para que la aplicación sea totalmente funcional:

Desde aquí, podéis descargar el libro de Excel, con el ejemplo que hemos visto en este artículo.

Determinar las cabeceras de una tabla donde tenemos los valores buscados

En el artículo anterior, vimos un ejemplo un poco rebuscado, para determinar en qué columnas (A, B, C…) se encontraban una serie de valores que teníamos en una tabla. La utilidad de ese ejercicio era poca, para qué negarlo :-( pero fue un reto interesante, para saber hasta qué punto Excel puede convertirse en una herramienta a medida, en la que podemos implementar funciones que no vienen de forma nativa dentro de la aplicación.

Ahora veremos algo más interesante, y que probablemente alguna vez hayáis necesitado, y si no ha sido así, seguramente en alguna ocasión necesitaréis. Se trata de obtener los rótulos o cabeceras de una tabla, donde tenemos los valores que estamos buscando. Como siempre, lo veremos con un sencillo ejemplo. Imaginemos que tenemos tres zonas donde nuestra empresa vende: la zona A, la zona B, y la zona C. Las ventas las tenemos divididas en meses, de enero a diciembre, y pretendemos determinar qué mes es aquel que tiene las ventas máximas en cada zona. La tabla sería la siguiente:

Si nos fijamos bien, las ventas máximas de la zona A, son 78, y se producen en el mes de octubre. Pues precisamente eso, obtener el mes de octubre, es lo que pretendemos conseguir que nos determine Excel.

Como bien sabéis, obtener la cifra máxima de ventas para la zona A, sería tan sencillo, como poner esta fórmula:

=MAX(C5:N5)

Pero la pregunta es: ¿cómo hacemos para que Excel nos diga que ese valor máximo que obtenemos en la zona A, y que corresponde a unas ventas de 78, se producen en el mes de octubre (OCT)?. Pues de una forma bastante sencilla. Solo tendremos que utilizar la función MAX, para determinar el valor máximo de ventas.

También utilizaremos la función COINCIDIR que nos determinará el número de columna en la que tenemos el valor máximo, teniendo en cuenta que el número de la columna se determina en función del rango que escojamos, y tomando como columna inicial, aquella que corresponda a la primera celda del rango. ¿Esto último parece complicado?. No, no lo es. Si te digo que escogemos como rango de la zona A, el que va desde C5 hasta N5, la columna C sería la primera, y la columna N sería la duodécima. Con esta función, obtendremos que la columna cuyas ventas son máximas en la zona A, se corresponde con la número diez.

Y por último, necesitaremos convertir esa columna número diez, en la cabecera o rótulo correspondiente de la tabla, es decir, en OCT (mes de octubre). Para ello, nos bastará con utilizar la función INDICE.

Sería algo tan sencillo, como aplicar esta fórmula, para determinar el mes en el que se producen las ventas máximas de la zona A:

=INDICE(C4:N4;COINCIDIR(MAX(C5:N5);C5:N5;0))

Para obtener lo mismo de la zona B, sería esto:

=INDICE(C4:N4;COINCIDIR(MAX(C6:N6);C6:N6;0))

Y en la zona C, las ventas máximas se producen en el mes que nos devuelve como resultado esta fórmula:

=INDICE(C4:N4;COINCIDIR(MAX(C7:N7);C7:N7;0))

Y en esta imagen, podéis ver los resultados:

Desde aquí, podéis descargar el libro de Excel, con el ejemplo que hemos visto en este artículo.

¿En qué columnas tenemos los datos que buscamos?

En esta ocasión vamos a explicar, a través de una función personalizada, es decir, a través de una función no implementada en el propio Excel, sino que nosotros mismos vamos a programar, cómo conseguir descubrir en qué columnas se encuentra un valor que buscamos dentro de un rango de celdas.

Quizás leyendo el párrafo anterior, no os quede muy claro que es lo que queremos obtener, pero como siempre, vamos a hacerlo lo más ameno posible, explicándolo de una forma sencilla, para que sea más inteligible.

Concretamente, vamos a hacer algo como esto: Imaginemos que tenemos una tabla de Excel, donde hay diferentes valores, ya sean numéricos, alfanuméricos, o simplemente caracteres de texto. Lo que pretendemos, es buscar un valor determinado en esa tabla, para saber en qué columnas se encuentra. Veámoslo con una imagen:

Como veis en esa imagen, hay una serie de vendedores, con sus ventas mensuales, un total anual, y una desviación con respecto a las ventas medias por vendedor. Vamos a complicarnos la vida, y lo que vamos a buscar no son los meses en los que encontremos determinado valor, sino la columna de Excel donde estos se encuentran.

Veámoslo con un ejemplo: Supongamos que queremos buscar en qué columnas tenemos el valor 6.000,00, para el vendedor llamado Pedro. Si os fijáis en la tabla anterior, podemos localizar ese valor en el mes de septiembre, que corresponde a la columna K. Esto último es lo que pretendemos localizar.

Para hacerlo más complicado, y como es probable que el valor buscado no coincida con ninguno de la tabla (por ejemplo, el vendedor Rafael no tiene ventas por importe de 6.000,00), vamos a buscar también aquellas columnas que tengan el valor buscado o uno superior (que se cumpla cualquiera de esas dos condiciones), y también aquellas columnas que tengan un valor inferior. Si queréis, podéis adaptar vosotros este ejercicio, para buscar las columnas cuyos valores que sean iguales o inferiores (que se cumpla cualquiera de esas dos condiciones), o bien, buscar las columnas cuyos valores sean superiores al valor buscado, pues es muy sencillo hacerlo, con los pasos que os indico a lo largo de este artículo.

Vamos al lío… Vamos a construir nuestra función personalizada, para lo cual, en un módulo VBA, copiaremos esto, que nos servirá para localizar las columnas que contengan el valor exacto al valor que busquemos:

Function Localizar(rango As Range, valor As Variant) 'fichamos la celda donde están los datos 'inicial y final (el rango) mi_rango = rango.Address 'separamos los datos inicial y final coordenadas = Split(mi_rango, ":") inicio = coordenadas(0) fin = coordenadas(1) 'pasamos a una variable la dirección de la celda inicial celda = Range(inicio).Address 'para todo el rango de datos... For i = 0 To Range(fin).Column - Range(inicio).Column      'Comprobamos el valor introducido      If Range(celda) = valor Then          columna = columna & " " & Range(celda).Address          'eliminamos el signo de $ de referencia absoluta          columna = Replace(columna, "$", "")      End If      'pasamos a la siguiente columna      celda = Range(celda).Offset(0, 1).Address 'seguimos con el bucle Next 'Si no existe el dato en ninguna columna If columna = 0 Then columna = " No existe" 'eliminamos los números de la fila numeros = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9) For j = 0 To UBound(numeros)      columna = Replace(columna, numeros(j), "") Next 'asignamos a la función, el dato del contador 'eliminando previamente el primer espacio vacío Localizar = Mid(columna, 2) End Function

Vale, esto está muy bien, pero ¿cómo se utiliza esta función?. Pues así:

=localizar(rango_de_celdas_donde_buscaremos;valor_a_buscar)

Veámoslo con otro ejemplo:

En la tabla anterior, podemos ver que estamos buscando el valor 6.000,00 en toda la tabla que va, del rango de celdas C7, hasta la celda N12, pero lo hemos separado por vendedores, por lo que en el caso por ejemplo del vendedor Rafael, lo que haremos será buscar el valor 6.000,00 en el rango que va de C7 a N7. Para ello, la fórmula que hemos puesto en la celda C17 es esta:

=localizar(C7:N7;$D$3)

En este caso, como vemos, no hay ventas por valor de 6.000,00, aunque sí las hay por valores superiores e inferiores. Si lo que queremos es obtener las columnas donde haya valores superiores o iguales a 6.000,00, entonces crearemos otra función personalizada a la que llamaremos "localizarMasOIgual", donde copiaremos el código que os puse anteriormente, cambiando estas líneas que os añado a continuación.

Cambiaremos esta línea del código (ojo, que tenemos que llamar a esta nueva función con otro nombre, y yo he elegido el de "localizarMasOIgual"):

If Range(celda) = valor Then

Por esta otra:

If Range(celda) >= valor Then

Y también cambiaremos esta línea:

localizar = Mid(columna, 2)

Por esta otra:

localizarMasOIgual = Mid(columna, 2)

De esta forma, ya tendremos nuestra nueva función creada, donde buscaremos las columnas donde tengamos un valor igual o superior al buscado. Este será el resultado, después de aplicar la nueva fórmula, al rango de datos donde buscaremos las ventas superiores o iguales a 6.000,00:

En el caso de buscar las columnas con valores inferiores al valor buscado (en nuestro ejemplo, ventas inferiores a 6.000,00), crearemos una nueva función a la que llamaremos "localizarMenos". En esta nueva función, cambiaremos el código inicial que os puse de dos líneas, concretamente cambiaremos esta línea:

If Range(celda) = valor Then

Por esta otra:

If Range(celda) < valor Then

Y también cambiaremos esta línea:

localizar = Mid(columna, 2)

Por esta otra:

localizarMenos = Mid(columna, 2)

Y el resultado después de aplicar la nueva función, será este, si buscamos las columnas donde tenemos ventas de cada vendedor, inferiores a 6.000,00:

Como veis, es muy sencillo adaptar la función inicial, a vuestras necesidades. Si queréis, podéis probar a obtener las columnas donde se encuentran valores superiores al valor buscado. En nuestro ejemplo hemos aplicado la función "localizarMasOIgual", pero nos busca los valores superiores o iguales al valor buscado, y no solo los valores superiores, como os sugiero que hagáis. También podéis crear una función más, para localizar las columnas donde hay valores menores o iguales al valor buscado. En nuestro ejemplo hemos aplicado la función "localizarMenos", pero nos busca solo los valores inferiores al valor buscado.

Desde aquí, podéis descargar el libro de Excel, con el ejemplo que hemos visto en este artículo, para que lo analicéis, lo probéis, y veáis si esta utilidad tiene alguna funcionalidad en vuestra vida diaria, o simplemente os parece otro interesante ejemplo más de cómo crear funciones personalizadas en Excel.