lunes, 4 de junio de 2018

Triángulo de Floyd

Puede descargar el archivo trianguloFloyd.xlsm

El triángulo de Floyd para cuatro filas es el siguiente.


Se construye con los números naturales en forma de triángulo rectángulo. Se suele utilizar como ejercicio para los que están aprendiendo a programar en un cierto lenguaje.

El triángulo de Floyd se resuelve con un algoritmo que ideó el Dr. Floyd, científico e informático que fue compañero de habitación de Carl Sagan cuando ambos estudiaban en la universidad.

Hoja 1

Resuelto usando fórmulas de Excel y sin usar macros.



La fórmula de la celda C5 calcula el máximo de la fila anterior para saber con qué número se ha de comenzar la fila actual.

=MAX(C4:P4)+1

La celda D5 suma 1 al valor previo y usa un condicional SI para saber hasta que valor se ha de continuar sumando 1.

=SI(CONTAR($C5:C5)<$A5;C5+1;"")

Hoja 2

También resuelve el triángulo con fórmulas y sin usar macros.

En este caso las celdas de la primera columna se obtienen con una fórmula que indica que nos da el último valor de la fila precedente y le suma 1. Se cumple que los últimos valores de cada fila son los llamados números triangulares, que se obtienen como n*(n+1)/2. Siendo n el número de fila.


En nuestro caso la celda C5 tiene la siguiente expresión.

=(A5*(A5-1)/2)+1

La celda D5 se calcula con la siguiente fórmula, que es igual a la empleada en la Hoja1.

=SI(CONTAR($C5:C5)<$A5;C5+1;"")

Hoja3

Se construye el triángulo de Floyd mediante macro.


Sub Floyd1()
Dim n As Long, i As Long, j As Long, n_max As Long
Dim R As Range
Worksheets("Hoja3").Activate
Set R = Range("B3")
n_max = 24: j = 1
R.Offset(1, -1).Resize(n_max+2, n_max+2).ClearContents
For n = 1 To n_max
  R.Offset(n, -1) = n 'imprimimos la columna A
  For i = 1 To n 'i recorre los elementos de cada fila
    R.Offset(n, i) = j 'imprimimos el valor de j en la celda que toca
    j = j + 1  'j proporciona los números naturales correlativos
  Next i
Next n
End Sub


Hoja4

Dada una fila n, podemos calcular el último valor de su fila usando los números triangulares que son los siguientes.


  1. Para n=1 el número triangular es 1
  2. Para n=2 el número triangular es 3
  3. Para n=3 el número triangular es 6
  4. Para n=4 el número triangular es 10
  5. Para n=5 el número triangular es 15

A esos valores e le suma 1 para saber cuál es el primer valor de la fila siguiente.

Sub Floyd2()
Dim n As Long, i As Long, j As Long, n_max As Long
Dim R As Range
Worksheets("Hoja4").Activate
Set R = Range("B3")
n_max = 24
R.Offset(1, -1).Resize(n_max+2, n_max+2).ClearContents
For n = 1 To n_max
  R.Offset(n, -1) = n
  'primera columan del triángulo
  R.Offset(n, 1) = (n * (n - 1) / 2) + 1
  For i = 2 To n
    R.Offset(n, i) = R.Offset(n, i - 1) + 1
  Next i
Next n
End Sub

Hoja5


Una pequeña variante para poder elegir el número de filas desde un control numérico que se encuentra en la propia hoja de cálculo.


También está disponible en un lenguaje de programación que está muy de moda.

domingo, 3 de junio de 2018

Seleccionar tabla sin cabecera

Puede descargar el archivo seleccionaTabla.xlsm

Primero generamos una tabla con valores aleatorios y con un número de filas que podemos elegir, con un control numérico, entre 1 y 20.


La macro que genera la tabla con valores aleatorios es la siguiente.

Observe que se define la celda B4 como la esquina superior izquierda y en base a ella se genera toda la tabla. Esta esquina sería fácil de variar en el código de la macro, gracias al uso de Offset.

Sub generaTabla()
Dim n As Byte, i As Byte
Dim R As Range
Set R = Range("B4")
n = [I2]
Range("B5:F24").ClearContents
For i = 1 To n
  R.Offset(i, 0) = i
  R.Offset(i, 1).Value = WorksheetFunction.Choose(Int(Rnd() * 3) + 1, "Norte", "Sur", "Centro")
  R.Offset(i, 2).Value = Date - i + 1
  R.Offset(i, 3).Value = WorksheetFunction.Choose(Int(Rnd() * 3) + 1, "Libros", "Comic", "Web")
  R.Offset(i, 4).Value = (Int(Rnd() * 100000) + 20000) / 100
Next i
End Sub


El código VBA que selecciona la tabla sin incluir la cabecera es la siguiente.

Sub seleccionaTablaSinCabecera()
Worksheets("Hoja1").Activate
'el cursor inicialmente tiene que estar dentro de la tabla
Range("B5").Select
Set R = ActiveCell.CurrentRegion
R.Offset(1, 0).Resize(R.Rows.Count - 1, R.Columns.Count).Select
End Sub

Aquí vemos otro uso estupendo de Offset combinado con Resize, propiedad de los rangos que hemos visto recientemente. Puede verlo en el post siguiente.

viernes, 1 de junio de 2018

Árbol binomial generado con una macro

Puede descargar el archivo arbolBinomial.xlsm

Vamos a crear un árbol binomial usando una macro de Excel, con un poco de código VBA. Lo interesante del caso es que al variar el número de periodos n el árbol se recalcula y se redimensiona en tamaño.

Partimos de un precio de una acción de S=100 €. Este precio puede subir o bajar en cada periodo. Suponemos que si sube lo hará con incrementos del 25% (u=1,25) y si baja lo hace con disminución del 20% (d=0,80).

Se cumple que d=1/u

1 / 1,25 = 0,80

n=1


El árbol para un periodo sería el siguiente.


Donde
125 = 100 * 1,25
80 = 100 * 0,80

Partiendo del precio inicial de 100, transcurrido un periodo pueden suceder dos cosas, o bien, el precio se incrementa y 25% pasando a ser 125 €, o bien se reduce un 20% pasando a ser 80 €.

n=2

Si hacemos el árbol para dos periodos.


Donde
156,25 = 125 * 1,25
100 = 125 * 0,80  o bien  100 = 80 * 1,25
64 = 80 * 0,80

Para el periodo 2, el valor mayor  (156,25) se obtiene incrementando un 25% más el precio superior del periodo anterior. También se puede ver como que 156.25 = 100 * 1,25 * 1,25, ya que se parte de un precio inicial de 100 y se experimentan dos incrementos del 25%.

El valor de 100 € del periodo 2 se alcanza por uno de los dos siguientes caminos.
  • 100 = 100*1,25*0,80 Partimos de 100, subimos a 125 y luego volvemos a bajar a 100.
  • 100 = 100 *0.80*1,25. Partimos de 100, bajamos a 80 y luego volvemos a subir a 100.
El valor de 64 se puede entender que se alcanza partiendo del precio inicial de 100 y experimentando dos reducciones consecutivas del 20%. 64 = 100 * 0,80 * 0,80

n=3

Si hacemos el árbol para tres periodos.


Donde
195,3125 = 100 * 1,253
125 = 100 * 1,252 * 0,80
80 =  100 * 1,25 * 0,802
51,2 = 100 * 0,803

Para n>3

La macro funciona hasta n=40, y no por la limitación de la propia macro sino porque hemos limitado hasta 40 el valor que se puede poner en la celda amarilla (C7) usando el control numérico que está a su lado.



Option Explicit
Public n As Integer 'número de etapas del arbor, hasta 40
Public A() As Double 'matriz que contiene el arbol

Sub arbol()
Dim i As Integer, j As Integer
Dim u As Double, d As Double
Worksheets("Hoja1").Activate
n = [C7]: u = [C4]: d = [C5]
Call borra
Call cabeceras
ReDim A(2 * n, n) 'el arbol tiene el doble de filas que de columnas
A(n, 0) = 100 'valor inicial en la columna cero
For j = 1 To n  'columnas de la matriz
  For i = 0 To 2 * n 'filas
    If j = n - i Then 'primero calculamos la diagonal superiro
      A(n - j, j) = A(n - j + 1, j - 1) * u
    ElseIf j >= n - i + 2 And j <= i + n Then 'calculamos el resto
      A(i, j) = A(i - 1, j - 1) * d
    End If
  Next i
Next j
Call imprimeA
End Sub
Sub borra()
Range("C10:AR91").Clear ' borra hasta n=40
End Sub
Sub cabeceras()
Dim i As Integer
Range("A1").Copy ' copiamos A1 para luego pegar el formato
'pegamos el formato de A1 a la columna C
Range(Cells(10, "C"), Cells(2 * n + 11, "C")).PasteSpecial Paste:=xlPasteFormats
'pegamos el formato de A1 a la fila 10
Range(Cells(10, 4), Cells(10, n + 4)).PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False 'elimina la fila de hormigas
For i = 0 To n
  'generamos los números 0 a 2n de la columna C
  Cells(i + 11, 3) = i 'vertical, los n primeros
  Cells(i + n + 11, 3) = i + n 'vertical, los n últimos
  'generamos los números 0 a n de la fila 10
  Cells(10, i + 4) = i 'horizontal
Next i
Range("C10").Activate 'situamos el cursor en C10
End Sub
Sub imprimeA()
Dim i As Integer, j As Integer
For j = 0 To n
  For i = 0 To 2 * n 'recorremos toda la matriz A
    If A(i, j) <> 0 Then 'imprimimos solo los no vacios
      Cells(i + 11, j + 4) = A(i, j)
    End If
  Next i
Next j
End Sub

La idea básica para hacer el árbol es que la diagonal superior se obtiene como el precio anterior por u. Y el resto de los valores del árbol se obtienen como el precio superior del periodo anterior por d.

Ejemplo para n=4.


244,1 se obtiene como 195,3 * 1,25
El resto de valores se obtienen multiplicando por 0,80, así tenemos los siguientes.
156,3 = 195,6 * 0,80
100 = 125 * 0,80
64 = 80 * 0,80
40,96 = 51,2  * 0,80

Este es método que usa la macro para obtener todos los valores del árbol binomial.

Lo que más esfuerzo ha costado es calcular bien las celdas donde se han de escribir los valores del árbol ya que al crecer n el árbol va aumentando de tamaño y es necesario ir bajando la celda inicial.

jueves, 31 de mayo de 2018

Manejar matrices con VBA

Puede descargar el archivo manejarMatrices.xlsm

Vamos a trabajar con matrices en Excel programando en VBA (Visual Basic for Applications).


Lo interesante de este caso es ver que para dejar el contenido de una matriz en la hoja de cálculo lo que debemos hacer es lo que nos dice la intuición.

Rango = Matriz 'respetando las dimensiones


Option Base 1 'Las matrices empiecen en 1 y no en 0

Sub manejaMatriz()
Dim A As Variant
Dim B As Variant
'para asignar un rango a una matriz
A = Range("B4:E9")
'para asignar una matriz a un rango
B = WorksheetFunction.Transpose(A)
'para imprimir una matriz en un rango
Range("B11:G14") = B
End Sub

Sub aleatorios()
Dim A(6, 4) As Double
Dim inicio As Range
Dim final As Range
Randomize 'para mejorar la aleatoriedad
For i = 1 To 6
  For j = 1 To 4
    'números aleatorios [0,1) a dos decimales
    A(i, j) = Int(Rnd() * 100) / 100
  Next j
Next i
Set inicio = Cells(4, "B")
Set final = Cells(9, "E")
Range(inicio, final) = A
End Sub

Sub multiplicaMatriz()
Dim A() As Variant
Dim B() As Variant
Dim C() As Variant
A = Range("B4:E9")
B = Range("B11:G14")
C = WorksheetFunction.MMult(A, B)
Range("B16:G21") = C
End Sub

Sub invierteMatriz()
Dim A() As Variant
A = Range("B23:D25")
Range("B27:D29") = WorksheetFunction.MInverse(A)
Range("B27:D29").Interior.Color = 6750156
Call extraeElementos
End Sub

Sub extraeElementos()
Dim A() As Variant
Dim origen As Range
Range("M4:AC30").Clear
Range("M4:AC30").Interior.Color = 13434879 'amarillo
Set origen = Range("L3")
A = Range("B27:D29")
fila = [L14]
columna = [S2]
For i = 1 To 3 'recorremos las 3 filas de la matriz
  For j = 1 To 3 'recorremos las 3 columnas de A
    With origen.Offset(fila + i - 1, columna + j - 1)
    .Value = A(i, j)
    .Borders(xlEdgeLeft).LineStyle = xlContinuous
    .Borders(xlEdgeRight).LineStyle = xlContinuous
    .Borders(xlEdgeTop).LineStyle = xlContinuous
    .Borders(xlEdgeBottom).LineStyle = xlContinuous
    End With
  Next j
Next i
Range(origen.Offset(fila + i - 4, columna + j - 4), origen.Offset(fila + i - 2, columna + j - 2)).Interior.Color = 6750156
End Sub

Sub copia()
Dim A As Variant
A = Range("B4:D6")
Range("B23:D25") = A
Range("B4:D6,B23:D25").Interior.ColorIndex = 8
End Sub

Sub Borra()
Range("B4:AD32").ClearContents
[S2] = 1: [L14] = 1
Range("B4:G29").Interior.Pattern = xlNone
Range("M4:AC30").Clear
Range("M4:AC30").Interior.Color = 13434879 'amarillo
End Sub

También es interesante ver el pos siguiente.

Propiedad Resize para manejar rangos en Excel

Puede descargar el archivo resize.xlsm

Cuando programamos macros en Excel usando VBA es muy habitual trabajar con rangos. Una propiedad de los rangos que modifica su tamaño es resize.

Rango.Resize(filas, columnas).Select

Veamos algunos ejemplos de su uso.



Resize1

Da color aleatorio a las celdas del rango B4:E8.

Sub Resize1()
Dim rojo As Byte, verde As Byte, azul As Byte
Dim R As Range
Set R = Range("B4")
Range("A1").Select
Randomize 'elegimos colores aleatorios
rojo = Int(Rnd() * 100) + 100
verde = Int(Rnd() * 100) + 100
azul = Int(Rnd() * 100) + 100
R.Resize(5, 4).Interior.Color = RGB(rojo, verde, azul)
End Sub

Observe que después de Resize(5.4) se ha de poner algo, o bien, .Select para seleccionar, o bien, .Interior.Color=RGB(rojo, verde, azul) como en este caso. Pero si después de Resize se deja sin poner nada nos dará error.

Resize2

Permite seleccionar un rango de 3 filas y 2 columnas usando como celda de origen la B4.

Range("B4").Resize(3, 2).Select



Sub Resize2()
'Seleccionamos un rango de 3 filas y 2 columnas
'La celda de origen es B4
Range("B4").Resize(3, 2).Select
End Sub

Resize3

Permite seleccionar un rango de 3 filas en la primera columna usando como celda de origen la B4.

Range("B4").Resize(3).Select



Sub Resize3()
'Seleccionamos un rango de 3 filas de la primera columna
'La celda de origen es B4
Range("B4").Resize(3).Select
End Sub

Resize4

Permite seleccionar un rango de 3 columnas en la primera fila usando como celda de origen la B4.

Range("B4").Resize(, 3).Select




Sub Resize4()
'Seleccionaremos un rango de 3 columnas de la primera fila
'La celda de origen es B4
Range("B4").Resize(, 3).Select
End Sub

Resize5

Permite seleccionar un rango de 2 columnas en la primera fila usando como celda de origen la B4 y hasta la fila 8, ya que el rango indicado es hasta la E8.

Range("B4:E8").Resize(, 2).Select



Sub Resize5()
Range("B4:E8").Resize(, 2).Select
End Sub

Resize6

Permite seleccionar un rango de 3 columnas en la primera fila usando como celda de origen la A4.

Range("B4").Offset(, -1).Resize(, 3).Select

Es una maravilla poder usar Offset para cambiar el rango de referencia, y usar, como en el ejemplo, valores negativos. Esto aporta flexibilidad al manejar gran cantidad de rangos cambiantes.


Sub Resize6()
Range("B4").Offset(, -1).Resize(, 3).Select
End Sub

Resize7

Copia el rango amarillo (B11:E15) en el rango superior (B4:E8).

Range("B4").Resize(5, 4) = A

Este es un uso muy útil de la propiedad Resize ya que nos permite depositar (imprimir) de golpe todo un rango o el contenido de una matriz.



Sub Resize7()
Dim A As Variant
Worksheets("Hoja1").Range("A1").Select
A = Range("B11:E15")
Range("B4").Resize(5, 4) = A
End Sub

Resize8

Hace lo mismo que la macro anterior, copia el rango amarillo (B11:E15) en el rango superior (B4:E8).

Range("B4").Resize(R.Rows.Count, R.Columns.Count) = A

Este procedimiento realiza el mismo trabajo que el anterior aunque lo hemos programado de otra forma. En este caso, creamos las variables n y m que calculan el número de filas y columnas respectivamente del rango R. De esta forma hacemos más flexible el código ya que evitamos tener que dar nosotros los parámetros a la propiedad Resize.

Sub Resize8()
Dim A As Variant
Dim R As Range
Worksheets("Hoja1").Range("A1").Select
Set R = Range("B11:E15")
A = R
n = R.Rows.Count
m = R.Columns.Count
Range("B4").Resize(n, m) = A
End Sub

Resize9

Genera una matriz de números aleatorios y la imprime en una posición inicial variable y con un tamaño variable.

Range(Cells(pf, pc), Cells(pf, pc)) .Resize(n, m) = A


Seguidamente se muestra el código de ejemplo utilizado para ilustrar la versatilidad de la propiedad Resize.

Sub Resize9()
Dim A As Variant
Dim R As Range
Dim tf As Byte, tc As Byte, pf As Byte, pc As Byte
Worksheets("Hoja2").Activate
Range("E7:AQ45").Clear
Randomize
Range("A1").Select
tf = Int(Rnd() * 20) + 1 'tamaño:fila
tc = Int(Rnd() * 20) + 1 'tamaño: columna
pf = Int(Rnd() * 20) + 7 'posición inicial: fila
pc = Int(Rnd() * 20) + 5 'posición inicial: columna
[C6] = tf
[D6] = tc
[C5] = pf
[D5] = pc
Set R = Range(Cells(pf, pc), Cells(pf + tf - 1, pc + tc - 1))
R.Interior.Color = RGB(0, 255, 100)
A = R
n = R.Rows.Count
m = R.Columns.Count
ReDim A(n, m)
For i = 1 To n
  For j = 1 To m
    A(i, j) = Int(Rnd() * 100)
  Next j
Next i
Range(Cells(pf, pc), Cells(pf, pc)).Resize(n, m) = A
End Sub

Veamos un GIF animado donde se aprecia que el rango varía en posición y tamaño de forma aleatoria.


Conviene ver cómo se resolvió en otros casos la necesidad de imprimir de golpe toda una matriz, incluso de gran tamaño. Recomiendo ver los siguientes enlaces a otros post publicados.