miércoles, 21 de febrero de 2018

Sucesión de Fibonacci en Excel

Puede descargar el archivo fibonacci.xlsm

Las sucesión de Fibonacci es bastante famosa. Los dos primeros valores son cero y uno, y los restantes se forman sumando los dos anteriores.

Estos son los números de Fibonacci.

0, 1, 1, 2, 3, 5, 8, 13, 21, 34, ... ... ... 

Vamos a crear una hoja de cálculo en la que vamos a calcular la serie de tres formas diferentes.
  1. Mediante fórmulas de Excel
  2. Mediante VBA, con la Macro 1
  3. Mediante VBA, con la Macro 2

Veamos las macros.


 Sub Borra()  
 Range("D5:E34").ClearContents  
 End Sub  
   
 Sub fibonacci1()  
 [D5] = 0  
 [D6] = 1  
 For i = 3 To 30  
  Cells(i + 4, 4) = Cells(i + 3, 4) + Cells(i + 2, 4)  
 Next i  
 End Sub  
   
 Sub fibonacci2()  
 Dim A(30) As Long  
 A(1) = 0: [E5] = 0  
 A(2) = 1: [E6] = 1  
 For i = 3 To 30  
  A(i) = A(i - 1) + A(i - 2)  
  Range("E" & i + 4) = A(i)  
 Next i  
 End Sub  


  1. En la Macro 1 denominada fibonacci1 trabajamos con cells.
  2. En la Macro 2 denominanda fibonacci2 trabajamos con una array, con la matriz A.

viernes, 22 de diciembre de 2017

martes, 5 de diciembre de 2017

Ciudades elegidas

Puede descargar el archivo ciudadesElegidas.xlsm

Hoja1

Disponemos de una lista de ciudades, son en total 735 ciudades, y deseamos elegir aleatoriamente unas cuantas de ellas. La elección se realizará por dos métodos, ambos con la posibilidad de que salgan ciudades repetidas. Si obtenemos ciudades repetidas las detectaremos con Formato condicional.

Los métodos empleados son los siguientes.
  • Método 1: con la función INDICE
  • Método 2: con la función DESREF



Hoja2

Disponemos de una lista de ciudades en la columna A y de ellas deseamos elegir aleatoriamente y sin repetición un cierto número de ellas. Vamos a resolver este caso con la ayuda de una macro de Excel.


 Sub EligeNombresAleatorios()  
 Borra  
 Dim numElegidas As Integer  
 Dim numNombres As Long  
 Dim numAlea As Integer  
 Dim Nombres() As String  
 Dim i As Byte, j As Byte  
 Dim Fila As Long  
 Dim Repe As Boolean 'para ver si está repetida la ciudad seleccionada  
 numElegidas = Range("C3").Value  
 Fila = 6  
 ReDim Nombres(1 To numElegidas)  
 numNombres = Application.CountA(Range("A:A")) - 1  
 For i = 1 To numElegidas  
   Do  
     Repe = False 'inicialmente supondremos que no está repetida la ciudad  
     numAlea = Application.RandBetween(1, numNombres)  
     'veamos si la ciudad seleccionada ya ha sido elegida  
     For j = LBound(Nombres) To UBound(Nombres)  
       If Nombres(j) = Cells(numAlea + 1, 1).Value Then Repe = True: Exit For  
     Next j  
   Loop While Repe  
   Nombres(i) = Cells(numAlea + 1, 1).Value ' Assign random name to the array  
 Next i  
 'Escribe las ciudades elegidas  
 For j = LBound(Nombres) To UBound(Nombres)  
   Cells(Fila, 3) = Nombres(j)  
   Fila = Fila + 1  
 Next j  
 End Sub  
   
 Sub Borra()  
 Range("C6").Select  
 Range(Selection, Selection.End(xlDown)).ClearContents  
 Range("C3").Select  
 End Sub  


Y en la Hoja2 tenemos el siguiente código.

 Private Sub Worksheet_Change(ByVal Target As Range)  
   If Target.Address = "$C$3" Then  
     Call EligeNombresAleatorios  
   End If  
 End Sub  

jueves, 23 de noviembre de 2017

Borrar celdas con macro

Puede descargar el archivo BorrarCelda.xlsm

Deseamos borrar el contenido de una celda o de un rango de celdas utilizando una macro de Excel con código VBA.

Si deseamos borrar la celda A1 podemos utilizar cualquiera de estas líneas.

1:  Range("A1") = Empty  
2:  Range("A1").ClearContents  
3:  Range("A1").ClearFormats  
4:  Range("A1").Clear  


  • Las líneas 1, 2 son equivalentes y borran únicamente el contenido de la celda, pero no borran el formato.
  • La línea 3 borra únicamente el formato de la celda.
  • La línea 4 borra todo, tanto el contenido como el formato.
En el siguiente caso disponemos de tres botones de macro. Veamos qué hace cada uno de ellos.

  • Rellenar Valores: deja la fórmula aleatorio.entre en las celdas amarillas, rellenando con valores aleatorios que van ente 200 y 300.
  • Borrar celda: elige una celda al azar de entre las amarillas y borra su contenido.
  • Borrar rango: borra el contenido del rango C5:C13.
Las celdas amarillas al quedar vacías adquieren color rosa. Esto se consigue utilizando 'Formato condicional'.


Veamos las macros sobre las que actúan los botones.


 Sub RellenarValores()  
 Dim i As Byte  
 For i = 1 To 9  
  Cells(i + 4, "C") = ["=RANDBETWEEN(200,300)"]  
 Next i  
 End Sub  
 Sub BorrarCelda()  
 Cells(Int(Rnd * 9 + 1) + 4, "C") = Empty  
 End Sub  
 Sub BorrarRango()  
 [C5:C13] = Empty  
 End Sub  

Buscar la última fila con macro

Puede descargar el archivo ultimaFila.xlsm

Cuando buscamos la última fila rellena de un rango de valores que se encuentran en una columna podemos usar el siguiente código.

 UltimaFila = Sheets("Hoja1").Range("A" & Rows.Count).End(xlUp).Row  



Este ejemplo está aplicado sobre la columna A. Nos dará la última fila de la columna A que contenga un valor no vacío, aunque en la columna A existan previamente huecos, esto es, aunque previamente existan celdas vacías.