viernes, 30 de enero de 2015

Selección Aleatoria de un Valor de un Rango (2)

"Necesito seleccionar aleatoriamente un número de un rango determinado. He visto una solución en tu blog en el post Seleccionar Aleatoriamente un Valor de un Conjunto. El problema que me encuentro es que si el rango en cuestión contiene celdas en blanco, entonces, la solución que propones devuelve valores cero. Me gustaría saber si existe la forma de que evite dichas celdas en blanco y elija un valor entre aquellas que contienen número".

Para solucionar esta variación sobre el caso visto en el post Seleccionar Aleatoriamente un Valor de un Conjuntovamos a generar una tabla auxiliar que nos permita "apartar" las celdas que se encuentran vacías para que no se puedan generar valores cero en el resultado. Partimos del siguiente ejemplo:
Queremos obtener aleatoriamente un valor de esta lista pero sin considerar las celdas en blanco. En la solución proporcionada en la primera versión de este problema, generamos un número aleatorio de posición para que excel me devuelva el valor existente en dicha celda. Es decir, si mi lista tiene 20 valores, genero un número aleatorio entre el 1 y el 20 y le pido a excel que vaya a la celda del número obtenido, por ejemplo la 4, y me devuelva el valor de dicha celda. Aplicando dicha solución a esta lista, nos podríamos encontrar con que en la cuarta celda de la lista la celda esté vacía (obtendríamos valor cero).

Lo primero que hacemos es seleccionar el rango B5:B18 y le damos el nombre de Valores. A continuación generamos una nueva lista auxiliar de dos columnas, cuya primera columna es una serie de número de orden de menor a mayor (en este caso del 1 al 14, ya que tenemos 14 datos):
Nos situamos en la celda G5 y escribimos la fórmula:
=K.ESIMO.MAYOR(valores;F5)  y copiamos hasta G18
De esta manera hemos conseguido generar una nueva lista ordenada de mayor a menor con los valores de nuestra lista original pero ahora ya no tenemos celdas en blanco por el medio del rango, ya que hemos conseguido que se "vayan al final" de nuestra nueva lista y que se muestren como error del tipo #¡NUM!
Esto nos permite ahora, aplicando la función CONTAR, calcular cuántas celdas de mi nueva lista contienen un valor númerico. Si calculamos  =CONTAR(G5:G18) nos devolverá el resultado 10, porque es el número de valores existentes en dicho rango de 14 celdas (las otras 4 contienen el error #¡NUM!). De esta manera ya sé que debo generar un número aleatorio entre 1 y 10. Por seguir trabajando con nombres de rango, seleccionamos G5:G18 y le creamos el nombre NewLista.
Nos situamos en la celda D10 y escribimos la fórmula definitiva:
=DESREF(G4;ALEATORIO.ENTRE(1;CONTAR(NewLista));)
Cada vez que pulsemos la tecla F9, excel recalculará un valor aleatorio y lo mostrará en la celda D10. Una fórmula alternativa en D10 sería utilizar la función INDICE:
=INDICE(NewLista;ALEATORIO.ENTRE(1;CONTAR(NewLista))) 

viernes, 23 de enero de 2015

Intercalar 1 Fila en Blanco cada n Filas (sin macros)

"Necesito dejar una fila en blanco cada 4 filas, es decir, que aparezcan 4 registros y en el quinto que incorpore una fila en blanco, y así sucesivamente hasta un total de 5.000 registros ¿Hay alguna manera de hacerlo sin hacer uso de macros?".

Para evitar solucionarlo de manera manual, lo que nos llevaría bastante tiempo, existe una manera razonablemente sencilla y razonablemente automática y que no requiere de programación. Partimos de una tabla con distintos datos. En nuestro ejemplo utilizaremos 50 registros:
Vamos a manejar dos hojas dentro del archivo para mayor seguridad. La tabla de la imagen la tenemos en la hoja 2. Lo primero que debemos hacer es crear un número de orden para los registros. Para ello escribimos un 1 en A3, un 2 en A4 y seleccionamos ambos (A3:A4). Hacemos doble clic en la parte inferior derecha del rango seleccionado y de esta manera rellenamos la serie hasta el último dato. 
Nos vamos ahora a la HOJA1. Preparamos la siguiente entrada de datos:
En D1 introducimos el número de fila que debe quedar en blanco. Si queremos dejar en blanco la quinta fila de nuestra tabla escribiremos un 5. En D2 introducimos el valor del último dato de nuestra tabla de la HOJA2. En nuestro ejemplo hay 50 registros y, por lo tanto, el valor será 50.
En E4 vamos a calcular cuántas filas se van a insertar en total. La fórmula es:
=SI(RESIDUO(D2;D1-1)=0;(D2/(D1-1))-1;TRUNCAR(D2/(D1-1)))

En E5 calculamos cuál será el último número de nuestra nueva lista. La fórmula es:
=E4+D2  es decir, el número de filas inicial más las que se van a insertar.

Ahora a partir de la celda C8 generamos nuevamente la serie comenzando con el número 1 y acabando con el 50 (igual que hicimos en la hoja 2).
En D8 escribimos un 1. En D9 la siguiente fórmula:
=SI(RESIDUO(D8+1;$D$1)=0;D8+2;D8+1)  y hacemos doble clic para completar la Nueva Lista.
En E8 la siguiente fórmula:
=SI($D$1*C8<=$E$5;$D$1*C8;"- - -")   y hacemos doble clic para completar la Lista de Múltiplos. El resultado será el siguiente:

Copiamos la Nueva Lista y la pegamos COMO VALORES en la HOJA2 encima del rango A3:A52  y obtenemos lo siguiente:

Ya estamos terminando... Ahora volvemos a la hoja 1 y seleccionamos el rango que contiene números en nuestra Lista de Múltiplos. En nuestro caso el rango E8:E19 de la HOJA1. Pulsamos Copiar y vamos a la HOJA2 y lo pegamos COMO VALORES a continuación del último dato, es decir, a partir de la fila 53.

Ya sólo nos queda ir a Ordenar y filtrar y seleccionar Orden Personalizado. En la ventana que se abre seleccionamos Ordenar por la Columna A de menor a mayor:
Aceptamos y... problema resuelto! (podríamos ahora borrar los número de orden generados en la hoja2 en la columna A):

Aunque pueda parecer un poco tedioso, una vez realizada la plantilla no lleva más de 30 segundos resolver cada caso que se nos plantee.

jueves, 22 de enero de 2015

Copiar Registros Intercalados

"Tengo cientos de registros relativos a números de unidades vendidas y debajo de cada registro el porcentaje que representa cada uno respecto a la suma total. Necesito copiar un listado pero sólo de las unidades vendidas sin dejar filas intercalas en el medio". 

Tal y como les prometí, este post va dedicado a mis alumnos del Master in Management del IE Business School, que con tanta paciencia me soportan los lunes y martes...
Partimos del siguiente ejemplo:

Lo que queremos conseguir es hacer una sola fórmula que podamos copiar hacia abajo para obtener un listado como el que aparece en la siguiente imagen con las cantidades de cada dato:
Para ello vamos a utilizar varias funciones, a saber: INDIRECTO, DIRECCION, FILA y COLUMNA.
Nos situamos en la celda G4 y escribimos la siguiente fórmula que copiaremos hasta G11:

=INDIRECTO(DIRECCION(FILA(C4)*2-4;COLUMNA(C4)))

La función FILA nos devuelve el número de fila de la celda en cuestión. En nuestro ejemplo FILA(C4) nos devolverá el valor 4. multiplicamos el resultado por 2 para generar una serie de números pares. FILA(C4)*2=8; FILA(C5)*2=10; FILA(C6)*2=12; etc. Como nuestro primer dato se encuentra en la fila número 4 (y no en la 8) tendremos que restarle 4 para que la serie comience en dicho número, en 4: FILA(C4)*2-4=4; FILA(C5)*2-4=6; FILA(C6)*2-4=8; etc. Nuestro datos precisamente se encuentran en las filas 4, 6, 8, etcétera. La columna es siempre la misma COLUMNA(C4). Si colocamos estas funciones dentro de la función DIRECCION, lo que obtendremos es la dirección de las celdas en cuestión, a saber:
=DIRECCION(FILA(C4)*2-4;COLUMNA(C4)) resulta C4
=DIRECCION(FILA(C5)*2-4;COLUMNA(C5)) resulta C6
=DIRECCION(FILA(C6)*2-4;COLUMNA(C6)) resulta C8
=DIRECCION(FILA(C7)*2-4;COLUMNA(C7)) resulta C10, etcétera.

De esta manera ya tenemos las direcciones de las celdas que queremos obtener. Sólo nos falta utilizar una función que transforme el nombre de la celda en el valor que contiene la misma. Esta función es INDIRECTO. 
Aunque el problema ya está resuelto, nos podríamos encontrar un pequeño problema y es que si ahora añadimos filas por encima de la fila 4 entonces dejará de funcionar. Para evitar esto podemos hacer uso del siguiente "truco". Creamos un nombre para el primer dato. Nos situamos en la celda C4, vamos a la lista de nombres (a la izquierda de la barra de fórmulas) y escribimos el nombre, por ejemplo, Dato1 y pulsamos Enter. A partir de ahora la celda C4 se llama Dato1. Nos situamos en G2 y escribimos la fórmula:
=FILA(Dato1)
De esta manera obtendremos el número de fila en el que se encuentra el primer dato  de forma variable. Podemos ahora incorporarlo a nuestra fórmula original y problema resuelto:
=INDIRECTO(DIRECCION(FILA(C4)*2-$G$2;COLUMNA(C4)))