domingo, 26 de julio de 2015

Elegir elementos aleatorios de una lista

Descargar el archivo busca4aleatorios.xlsm

Dada una lista de 10 elementos deseamos elegir cuatro de ellos de forma aleatoria y sin repetición.

La lista puede estás formada por los elementos que sea. En este caso la lista está formada por los números del 0 al 9.

Esto se puede hacer programando en VBA una macro, pero deseamos hacerlo con la grabadora de macros para no tener que meternos a programar con código.


Hemos creado cinco macros con grabadora cuyas acciones son las siguientes.

  1. Macro1. Borra el rango D5:F14
  2. Macro2. Copia el rango C5:C14 que es donde se generan los aleatorios, en el rango D5:D14, y lo hace con pegado especial valores.
  3. Macro3. Copia el rango D5:D14 en el rango E5:E14, y lo hace con pegado especial valores.
  4. Macro4. Eliminamos duplicados del rango E5:E14
  5. Macro5.Tomamos únicamente los cuatro primeros valores de la columna E. Justamente tomamos el rango E5:E8 y lo pegamos en el rango F5:F8 que es el resultado final que buscábamos.
Creamos una macro denominada Obten que llama a las otras cinco macros en el orden correcto.

Sub Obten()
Macro1
Macro2
Macro3
Macro4
Macro5
End Sub


Creamos un botón que lanze la macro Obten.

Un posible problema


Este sistema tiene la ventaja de que no ha sido necesario programar una macro compleja con VBA y lo hemos hecho con la ayuda de la grabadora de macros. Existe un inconveniente que pudiera darse y es que si el número de valores de la lista que se repiten es muy alto en algún caso podríamos tener menos de 4 valores diferentes, por lo que se tendría que volver a generar el proceso ya que con tres valores, por ejemplo, no nos valdría.

Para detectar si este fenómeno raro se da con cierta frecuencia hemos creado una macro que al lanzarla busca incansablemente hasta que encuentra menos de cuatro valores distintos. La macro es la siguiente.

Sub busca()
Dim n As Byte
Do
  Obten
  n = Application.WorksheetFunction.Count(Range("E5:E14"))
  If n < 4 Then
    End
  End If
Loop
End Sub

He tenido corriendo esta macro unos minutos y no se ha parado, lo cual indica que no es fácil encontrar un caso donde el número de repetidos sea tan alto que nuestro procedimientos resuelto con grabadora de macros no sea efectivo.

Por cierto, para parar una macro de forma manual se han de pulsar las teclas Contro+Pausa La tecla pausa habitualmente se encuentra en el teclado en la parte superior derecha. En algunos ordenadores portátiles se ha de pulsar alguna tecla de función que suele ir marcada con algún color. Esto para cualquier macro que esté funcionando y deseemos detener. Es un STOP manual.

martes, 9 de junio de 2015

Formato condicional usando el operador lógico Y

Archivo de Excel utilizado: formato_condicional.xlsx

Veamos un caso de práctico del uso que se da a Formato Condicional cuando lo usamos con una fórmula que requiere el uso del operador lógico Y. Lo que hacemos es añadir dos condiciones lógicas de forma que se exige que se cumplan ambas. Cuando se piden ambas se requiere usar el Y.
  • pedimos que el valor sea inferior al promedio de su columna, y
  • pedimos que no se trate de una celda vacía


jueves, 28 de mayo de 2015

Ranking de ventas por año

Archivo de Excel utilizado: ranking_ventas.xlsx

Vamos a crear una Tabla Dinámica para jerarquizar a los vendedores según las venta realizadas en un par de años.

Disponemos de una base de datos con 1.000 registros donde la columna 'Año' se calcula mediante la función =AÑO(fecha)


Deseamos llegar a la siguiente Tabla Dinámica.


Método 1

El método 1 utiliza la creación de un Campo calculado.

Para lograr nuestro objetivo utilizamos la siguiente Lista de campos de tabla dinámica.


Observe que en Valores hemos añadido el Campo calculado %Imp. que nos dará el porcentaje del importe de las ventas sobre el total de ventas de su columna. Observe que abajo el total de cada columna es el 100%.

El campo calculado es el siguiente. En la fórmula simplemente introducimos el Importe.


En configuración de campo de valor elegimos % sobre el total de columnas.



Finalmente ordenamos de mayor a menor por las ventas del primer año.



Método 2

El método 2 logra el mismo objetivo si necesidad de crear un Campo calculado. Lo que hacemos es arrastrar el Importe dos veces a 'Valores'.

En este caso como el Campo calculado es tan simple que es únicamente el nombre de un campo y no se hace ninguna operación matemática con él, se puede hacer simplemente arrastrando nuevamente el campo Importe y a este segundo Importe luego le pedimos que se exprese en forma de porcentaje sobre el total de su columna.



jueves, 21 de mayo de 2015

Reordenar Tablas dinámicas

Archivo de Excel utilizado: ReordenarTabla.xlsx

Vamos a generar una Tabla dinámica partiendo de una Base de Datos. La idea es ver que las Tablas dinámicas nos permiten organizar los campos (columnas de la base de datos) como mejor nos parezca para llegar a obtener el informe deseado.

miércoles, 20 de mayo de 2015

Fechas vacaciones

Archivo de Excel utilizado: FechasVacaciones.xlsx

Cómo saber si se solapan las vacaciones de los trabajadores de una empresa.

Mostramos un caso donde se ve de forma gráfica si los periodos vacacionales se solapan o no.

Pulse la tecla F9 de recálculo manual para ver cómo cambia la simulación.


Se ve repetido el informe. Arriba todas las barras son azules. Abajo se ha marcado la zona de solapamiento en color naranja.

Los colores salen usando Formato condicional. La fórmula del Formato condicional para la celda F3 es la siguiente.

  • =Y(F$1>=$D3;F$1<=$E3)


Si se cumple esa condición se pondrá de color azul. Luego se copia o extiende ese formato a todo el rango F3:VN17.

Para los colores de la tabla inferior usamos dos Formatos condicionales para la celda F20 que son los de las dos fórmulas siguientes.

  • Para el azul  =Y(F$1>=$D20;F$1<=$E20)
  • Para el naranja  =Y(F$1>=$D20;F$1<=$E20;SUMA(F$3:F$17)>1)
Luego se copia o extiende el formato condicional al rango F20:VN34.