martes, 17 de julio de 2018

Bucles For anidados

Puede descargar el archivo BuclesForAnidados.xlsm

Vamos a trabajar con dos bucles For...Next anidados. Realizaremos unos ejercicios con macros Excel programando en VBA.

Llegaremos a realizar la siguiente figura con números que van entre 1 y 9, tanto en horizontal como en vertical.


Pero antes de llegar a obtener esa imagen que hemos denominado 'Bandera Color' vamos a ir paso a paso.



Borra

Como vamos a trabajar en varios casos, antes de comenzar  nos interesa borrar el contenido del rango A1.I9. También vamos a borrar el color de fondo de las celdas.


Sub Borra()
Dim R As Range
Set R = Range("A1:I9")
R.ClearContents
R.Interior.Pattern = xlNone
End Sub


Completo

Anidamos dos bucles For para conseguir imprimir en cada una de las celdas del cuadrado de 9 filas y 9 columnas, los números i j.



Sub Completo()
Dim i As Byte, j As Byte
Call Borra
For i = 1 To 9
  For j = 1 To 9
    Cells(i, j) = Str(i) & " " & Str(j)
  Next j
Next i
End Sub


Caja

Ahora queremos que no se imprima el cuadrado completo, únicamente deseamos imprimir los bordes. Para ello precisamos incluir dentro de los bucles anidados un condicional if que imprima únicamente los índices i j cuando se cumpla que el primero es igual a 1 o 9, o bien el segundo sea igual a 1 o 9. De esta forma conseguimos imprimir solo el perímetro del cuadrado.



Sub Caja()
Dim i As Byte, j As Byte
Call Borra
For i = 1 To 9
  For j = 1 To 9
    If i = 1 Or i = 9 Or j = 1 Or j = 9 Then
      Cells(i, j) = Str(i) & " " & Str(j)
    End If
  Next j
Next i
End Sub


Diagonal 1

Queremos imprimir únicamente la diagonal primera que se consigue haciendo que el condicional if filtre únicamente aquellos valores donde los índices i j coincidan.




Sub Diagonal_1()
Dim i As Byte, j As Byte
Call Borra
For i = 1 To 9
  For j = 1 To 9
    If i = j Then
      Cells(i, j) = Str(i) & " " & Str(j)
    End If
  Next j
Next i
End Sub


Diagonal 2

La segunda diagonal se consigue buscando qué tienen en común los índices i j. Observamos que esta diagonal cumple que al sumar ambos índices la suma siempre es igual a 10. Esta será la condición que impondremos en el condicional if.



Sub Diagonal_2()
Dim i As Byte, j As Byte
Call Borra
For i = 1 To 9
  For j = 1 To 9
    If i + j = 10 Then
      Cells(i, j) = Str(i) & " " & Str(j)
    End If
  Next j
Next i
End Sub


Bandera


Deseamos imprimir únicamente los bordes y las dos diagonales. A esta figura la hemos llamado bandera. Observe que se consigue incluyendo en el if seis condiciones concatenadas con el operador lógico Or.



Sub Bandera()
Dim i As Byte, j As Byte
Call Borra
For i = 1 To 9
  For j = 1 To 9
    If i = 1 Or i = 9 Or j = 1 Or j = 9 Or i = j Or i + j = 10 Then
      Cells(i, j) = Str(i) & " " & Str(j)
    End If
  Next j
Next i
End Sub


Bandera Color

Nos gustaría que la bandera tuviera colores. Los colores de fondo de cada celda se consiguen con Interior.ColorIndex=número. Donde el número nos da el color.





Sub BanderaColor()
Dim i As Byte, j As Byte
Call Borra
For i = 1 To 9
  For j = 1 To 9
    If i = j Or i + j = 10 Then
      Cells(i, j) = Str(i) & " " & Str(j)
      Cells(i, j).Interior.ColorIndex = 3 'rojo
    ElseIf i = 1 Or i = 9 Or j = 1 Or j = 9 Then
      Cells(i, j) = Str(i) & " " & Str(j)
      Cells(i, j).Interior.ColorIndex = 6 'amarillo
    Else
      Cells(i, j).Interior.ColorIndex = 8 'azul
    End If
  Next j
Next i
End Sub

Ya tenemos bandera.

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.