Cuadro de Amortización Automático

Descargar el fichero autocuadro.xlsm

Vamos a automatizar la generación de un Cuadro de Amortización de un Préstamo. Al cambiar el principal o el tipo de interés las celdas del cuadro se actualizan, como es lógico al tratarse de una hoja de cálculo. Pero lo que no se actualiza de forma automática es el número de filas del cuadro al variar la duración del préstamo. Para este caso se ha de utilizar una macro que cambie el número de filas del cuadro al cambiar la duración de la operación financiera.

El ejemplo se desarrolla en varias hojas:

1. anual: Préstamo francés con pago anual constante
2. mensual: Préstamo francés con pago mensual constante
3. carencia: Préstamo francés con meses de carencia
4. italiano: Préstamo de cuota de amortización constante y carencia

La macro que recalcula el cuadro de amortización es la siguiente:

Código:

Sub mCuadro()
Call mLimpia
Call mPeriodos
Call mCopia
Range("A1").Select
End Sub
Sub mLimpia()
Range("B12").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Clear
Range("A1").Select
End Sub
Sub mPeriodos()
Dim fin As Integer
fin = Range("G4").Value + 10
Range("B10:B11").Select
Selection.AutoFill Destination:=Range("B10:B" & fin)
Range("B10:B" & fin).Select
Range("A1").Select
End Sub
Sub mCopia()
Dim fin As Integer
fin = Range("G4").Value + 10
Range("C11:G11").Select
Selection.AutoFill Destination:=Range("C11:G" & fin)
End Sub

En las dos últimas hojas (carencia, italiano) la macro no se lanza pulsando con el ratón sobre el botón que se ha creado para lanzar la macro. En estos casos no existe botón, y la macro se lanza simplemente escribiendo el número de años del préstamo (celda D6). Inmediatamente despues de validar la celda la macro se ejecuta y se actualiza el cuadro de amortización.

La macro que permite esta ejecución automática no se programa en un Módulo sino que se ha de escribir en el apartado 'Microsoft Excel Objetos' y en la hoja de la que se trate. Es una macro que se lanza al producirse un evento. Concretamente el evento es un Worksheet_Change que permite lanzar un proceso al producirse un cambio en la hoja. Ese cambio en nuestro caso es un cambio en el Target que es la celda D6.

Código:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$6" Then
Call mCuadro
End If
End Sub

Generación de números aleatorios únicos (sin repetición)

Descargar el fichero RNDunicos.xlsm

Generar números aleatorios en Excel es tarea sencilla. Con la función =ALEATORIO(), con la función =ALEATORIO.ENTRE, y en lenguaje de Visual Basic con la función RND, que es la abreviatura de random. Por el contrario, si lo que deseamos es generar una serie de números aleatorios sin repetición la tarea se complica un poco, ya que debemos ir comprobando por cada número nuevo, si se encuentra entre los anterioremente generados y en caso afirmativo desecharle y generar otro.

La siguiente macro nos permite realizar la tarea descrita.

Código:

Sub unicos1()
Dim i As Integer, j As Integer
Dim A() As Long
Dim esta As Boolean
Dim x As Long, y As Long, z As Long, num As Long
x = Application.InputBox(prompt:="Entre el número aleatorio inicial" _
        , Title:="Generador de Números Aleatorios", Default:=1, Type:=1)
y = Application.InputBox(prompt:="Entre el número aleatorio final" _
        , Title:="Generador de Números Aleatorios", Default:=1000, Type:=1)
z = Application.InputBox(prompt:="¿Cuantos números aleatorios desea generar?" _
        & " (<15000 _="" default:="100," exit="" if="" sub="" then="" title:="Generador de Números Aleatorios" type:="1)" z=""> 15000 Then z = 15000
If z > y - x + 1 Then
    MsgBox "!Ha especificado más números " _
    & "de los que son posibles en el rango!"
    Exit Sub
End If
ReDim A(z)
Randomize
A(1) = Int((y - x + 1) * Rnd + x)
For i = 2 To z
    Do
        num = Int((y - x + 1) * Rnd + x)
        esta = False
        For j = 1 To i - 1
           If num = A(j) Then esta = True: Exit For
        Next j
    Loop While esta
    A(i) = num
Next i
For i = 1 To z
    Cells(i, 2) = A(i)
Next i
End Sub

Existe otra variante de esta macro que utiliza el comando Find para buscar el nuevo número aleatorio generado entre los anteriores. Si le encuentra entre los ya generados en la columna B buscará un nuevo número aleatorio. Al comando Find es necesario añadirle la propiedad LookAt:=xlWhole para que localice exactamente el número buscado. Si esto no se hiciera así, sucede que busca por las cifras, y si esta buscando el número 1, piensa que se encuentra, por ejemplo, en la cifra 107, o en la 819, esto es, siempre que aparezca un 1.

Código:

Sub unicos2()
    Dim x As Long, y As Long, z As Long, num As Long
    Dim control As Boolean
    Dim i As Long
    Dim celda As Range
    x = Application.InputBox("Entre el número aleatorio inicial" _
        , "Generador de Números Aleatorios", 1, , , , , 1)
    y = Application.InputBox("Entre el número aleatorio final" _
        , "Generador de Números Aleatorios", 1000, , , , , 1)
    z = Application.InputBox("¿Cuantos números aleatorios desea generar?" _
        & " (<15000 100="" 1="" _="" aleatorios="" de="" enerador="" exit="" if="" meros="" n="" sub="" then="" z=""> 15000 Then z = 15000
    If z > y - x + 1 Then
        MsgBox "!Ha especificado más números " _
            & "de los que son posibles en el rango!"
        Exit Sub
    End If
    Randomize
    Cells(1, 2) = Int((y - x + 1) * Rnd + x)
    For i = 2 To z
        Do
            control = False
            Randomize
            num = Int((y - x + 1) * Rnd + x)
            Set celda = Range("b1", Range("b1").End(xlDown).Address). _
            Find(num, LookIn:=xlValues, LookAt:=xlWhole)
            If Not (celda Is Nothing) Then
                control = True
            End If
        Loop Until Not control
        Cells(i, 2) = num
    Next
End Sub

Funciones matriciales en Excel

Puede descargar los siguientes archivos.

• matricial.xlsx
• frecuencia.xlsx

Las funciones matriciales en Excel permiten efectuar cálculos muy interesantes y proporcionan a la hoja de cálculo una enorme potencia. Las funciones matriciales, en general, abarcan varias celdas, aunque en algunos casos únicamente se centran en una única celda. Las fórmulas matriciales se identifican ya que se encuentran entre corchetes {}. Veamos algunas aplicaciones. 

Hoja 'Formula'

Las fórmulas matriciales pueden abarcar varias celdas o una sola. En esta hoja vamos a ver la potencias de cálculo de este tipo de expresiones cuando arrojan el resultado en una sola celda.

Para introducir una fórmula matricial se han de seguir tres pasos:

1. Seleccionar el rango en el que la fórmula matricial dejará sus valores. Si la fórmula matricial no abarca más de una celda simplemente no situaremos en esa celda.
2. Escribir la fórmula propiamente dicha
3. Para validar no se ha de pulsar INTRO, sino que lo que hemos de pulsar es la combinación de tres teclas: CONTROL+MAYUSCULAS+INTRO

Al ver una función matricial en Excel la verá ente llaves {}. Estas llaves no las debe introducir usted al escribir la fórmula, las introducirá Excel al validar la función pulsando CONTROL+SHIFT+INTRO.

Recuerde que la tecla de MAYUSCULAS también se la llama SHIFT.