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.

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.


viernes, 27 de octubre de 2017

Generación de tablas de bingo

Puede descargar el archivo: bingo.xlsm

Vamos a crear 6 tablas de bingo.
Para obtenerlas se exigen una serie de condiciones.
  • Cada tabla tiene 5 columnas
  • Cada columna contiene 5 números
  • La columna 1 contiene números entre 1 y 15
  • La columna 2 contiene números entre 16 y 30
  • La columna 3 contiene números entre 31 y 45
  • La columna 4 contiene números entre 46 y 60
  • La columna 5 contiene números entre 61 y 75
  • Los cinco números de cada tabla y columna no se repiten entre si
  • El conjunto de 5 números de cada tabla y columna no se repiten entre si


Código


 Sub generaBingo()  
 Dim i As Byte, j As Byte, k As Byte, n As Byte, m As Byte, p As Byte, num As Byte  
 Dim esta As Boolean  
 Dim linearepe As Boolean  
 Dim A(5, 5, 6) As Long  
 B = Array(2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47) 'quince primos  
 Randomize  
 Do  
  linearepe = False  
  For j = 1 To 5 'número de columna B=1 I=2 N=3 G=4 O=5  
   For k = 1 To 6 'número de tablas  
    A(0, j, k) = 1  
    For i = 1 To 5 'número de fila  
     A(i, j, k) = (Int(Rnd() * 15) + 1) + (j - 1) * 15  
     For n = 2 To i  
      Do  
       num = (Int(Rnd() * 15) + 1) + (j - 1) * 15  
       esta = False  
       For m = 1 To n - 1  
        If num = A(m, j, k) Then esta = True: Exit For  
       Next m  
      Loop While esta  
      A(i, j, k) = num  
     Next n  
     Cells(k * 7 - 5 + i, j + 1) = A(i, j, k)  
     'Producto de números primos  
     A(0, j, k) = A(0, j, k) * B(A(i, j, k) - 1 - (j - 1) * 15)  
    Next i  
    'Cells(k * 7 - 5, j + 1) = A(0, j, k) 'Imprime el producto de primos  
   Next k  
  Next j  
  For j = 1 To 5 'para cada columna  
   For k = 1 To 5 'analizamos cada tabla con las siguientes, por eso son 5  
    For p = k + 1 To 6  
     If A(0, j, k) = A(0, j, p) Then linearepe = True  
    Next p  
   Next k  
  Next j  
 Loop While linearepe  
 End Sub  

Hemos resuelto este caso usando los quince primeros números primos, que podemos ver en el array.

B = Array(2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47)

La idea es la siguiente.

  • Por cada columna, tenemos que estudiar si el conjunto de los cinco números de la tabla 1, son iguales o distintos de los cinco números de esa misma columna de las tablas siguientes, esto es de las tablas 2, 3, 4, 5, 6. 
  • Por cada columna, tenemos que estudiar si el conjunto de los cinco números de la tabla 2, son iguales o distintos de los cinco números de esa misma columna de las tablas siguientes, esto es de las tablas 3, 4, 5, 6.
  • Por cada columna, tenemos que estudiar si el conjunto de los cinco números de la tabla 3, son iguales o distintos de los cinco números de esa misma columna de las tablas siguientes, esto es de las tablas 4, 5, 6.
  • Por cada columna, tenemos que estudiar si el conjunto de los cinco números de la tabla 4, son iguales o distintos de los cinco números de esa misma columna de las tablas siguientes, esto es de las tablas 5, 6.
  • Por cada columna, tenemos que estudiar si el conjunto de los cinco números de la tabla 5, son iguales o distintos de los cinco números de esa misma columna de la tabla siguiente, esto es de la tabla 6.
Esto se podría resolver comparando número a número de entre los cinco de cada grupo, con los cinco correspondientes de las demás tablas y esto se ha de hacer por cada columna.

En este caso hemos optado por otro método que utiliza números primos. Siempre es más cómodo comparar número contra número que comparar grupo de cinco números contra otro grupo de cinco número y ver si todos ellos coinciden. Por tanto, lo que hemos hecho es convertir ese grupo de cinco números en un único número, para sí poder comparar de forma más sencilla.

¿Cómo convertimos un grupo de cinco números en un solo número?
Podríamos haber optado por sumar los cinco número y su resultado sería el número único que los representa a todos ellos. Pero esta opción, no sería correcta ya que se puede dar el caso de que varios conjuntos de cinco números tengan la misma suma. Por ejemplo, 1,2,3,4,6,7 suman lo mismo que 1,2,3,4,5,8. Por tanto, el método de la suma de los cinco número no conduce a un único valor que los represente.

Probemos multiplicando. Se trataría de multiplicar los cinco números de un grupo y ver si su producto es siempre distinto del producto de otro conjunto de cinco números distintos. Si el producto siempre fuera diferente en estos dos conjuntos de números habríamos encontrado un método para diferenciar ambos conjuntos mediante la comparación de un único valor, que en este caso sería su producto. Pero este método no es siempre cierto ya que por ejemplo los dos conjuntos de cinco números presentan el mismo producto: 1,2,7,4,6 y 1,2,7,8,3. El motivo es que 6 no es primo y es 3 por 2. Dejamos en su lugar el 3 y el 2 lo multiplicamos por cualquiera de los otros, por ejemplo por 4 para que de 8. De esta forma ambos productos coinciden y son iguales a 336. Esto nunca podría haber sucedido si todos los números fueran primos.

Aunque los números no son primos los podemos convertir en primos asignando cada valor que varía en el rango de 1 a 15 para la primera columna en cada uno de los primero quince números primos. Esto se haría para cada columna y así no aseguramos que el producto de los primos nunca podrá coincidir.

Por ejemplo, los números 1,2,7,4,6 se transforman en los siguientes primos, siguiendo el orden: 2,3,17,7,13. Y los números 1,2,7,8,3 se transforman en: 2,3,17,19,4. El producto del primer conjunto de primos es 9.282 y del segundo 7.752. Observamos que sus productos son diferentes y esto siempre se puede garantizar ya que operamos con números primos.


martes, 15 de noviembre de 2016

Función DESREF y función INDICE

Puede descargar el archivo:

Hoja1

Disponemos de una tabla de doble entrada con valores de ventas en cinco ciudades durante seis meses.

Partiendo de los datos anteriores, deseamos elaborar una tabla donde figure la ciudad, el mes y las ventas correspondientes.

En la celda L5 utilizamos la siguiente fórmula.

=INDICE($C$7:$H$11;COINCIDIR(J5;$B$7:$B$11;0);COINCIDIR(K5;$C$6:$H$6;0))

Ahora deseamos elaborar una tabla donde figuren las ventas máxima indicando en que ciudad y mes se alcanzaron.

La celda O5 es la siguiente.

=DESREF(L4;COINCIDIR(MAX(Ventas);Ventas;0);-2)

Hoja2

Planteamos un ejercicio para practicar el uso de DESREF y de INDICE.

Como datos nos proporcionan las averías que se producen en cada mes desde enero-2017 hasta diciembre-2025.
Nos piden que con Formato condicional marquemos la cifra de averías máximas y mínimas.

Queremos elaborar una tabla como la siguiente, donde se indiquen las averías máximas y mínimas y en que año y mes se producen, usando para el año y el mes la función DESREF.


Nos proporcionan el gráfico de la evolución de las averías a lo largo del tiempo.


Nos piden que volcar la base de datos anterior a una tabla de doble entrada, usando para ello la función INDICE.



Finalmente nos piden que partiendo de la tabla de doble entrada anterior construyamos una tabla igual a la que se utilizó inicialmente con los datos de partida. Para ello, volveremos a utilizar la función INDICE.

Hoja3

En esta hoja damos solución al ejercicio planteado en la hoja anterior.
La celda F9 nos proporciona el mes donde se alcanza el mínimo de averías, su fórmula es:

=DESREF($B$4;COINCIDIR(MIN(Averias);Averias;0);0)


Para elaborar esta tabla hemos realizado un cambio importante en la columna que recoge los meses. Inicialmente los meses venían en texto: ene, feb, mar, may, ... Lo que hemos hecho para que las fórmulas funcionen bien es cambiar esos textos por fechas reales con el formato que nos pide Excel para la fechas y luego con Formato de celda personalizado hemos pedido que se muestre únicamente el mes de esa fecha.


La celda Q5 que proporciona el primer número de averías (50.000) es la siguiente.

=INDICE(tabladoble;MES(P5);AÑO(P5)-2016)