jueves, 12 de noviembre de 2015

Combinaciones con Aleatorio.Entre

"Necesito obtener 4 combinaciones aleatorias partiendo de 10 columnas con números del 1 al 10; del 11 al 20; del 21 al 30; etcétera (hasta el 100). De tal manera que de la primera columna obtenga un número aleatorio; de la segunda columna otro; y así hasta completar la combinación de los diez números".

Para solucionarlo utilizaremos la función ALEATORIO.ENTRE. Montamos la siguiente entrada de datos:
Lo que hemos hecho es introducir los límites inferiores y superiores de cada rango. A continuación nos situamos en la celda D7 y escribimos la fórmula:
=ALEATORIO.ENTRE(D$3;D$4)  y la copiamos hasta M3 y finalmente hasta M10:
Problema resuelto. Podemos pulsar la tecla F9 para generar nuevas combinaciones aleatorias.

lunes, 9 de noviembre de 2015

Aleatorios Acotados con Promedio Objetivo


"Necesito generar números aleatorios entre valores determinados (por ejemplo, valores no inferiores a 498 y no superiores a 504) cuyo promedio sea un número concreto que se encuentre entre ambos límites (por ejemplo 500)".

Es un caso muy similar al resuelto en mi post Generar Aleatorios que Sumen una Cifra Objetiva, pero con algún ajuste que paso a comentar. Partimos del siguiente ejemplo y entrada de datos:
En la celda E7 escribimos la siguiente fórmula, que nos generará números aleatorios comprendidos entre los valores especificados en las celdas C4 y C5:
=ALEATORIO()*($C$4-$C$5)+$C$5  y copiamos hasta E21:
En la celda C7 escribimos la siguiente fórmula:
=((E7/SUMA($E$7:$E$21))*$C$2)*$C$3  y la copiamos hasta C21:
Podemos pulsar la tecla F9 para generar nuevos valores aleatorios y comprobar que la celda C23, que contiene la fórmula =PROMEDIO(C7:C21), mantiene el mismo valor que el objetivo indicado en la celda C2.

miércoles, 28 de octubre de 2015

Desplegables sin Filas en Blanco

"Tengo una columna con registros y necesito generar una lista desplegable con dichos registros y en el mismo orden que aparecen en el listado original. El problema es que existen celdas en blanco y, en consecuencia, me aparecen dichos espacios en la lista desplegable".

Partimos del siguiente ejemplo:
En C2 queremos crear una lista desplegable con validación de datos, que presente los distintos códigos existentes en el rango E3:E20, en el mismo orden, pero sin los espacios en blanco. Lo primero que hacemos es generar un número de orden en la columna G (a partir de G3) del 1 al 18. En la celda H3 escribimos =E3 y copiamos hasta la celda H20, para generar un "espejo" de los códigos originales: 
Lo que vamos a hacer a continuación es "reordenar" estos códigos dándoles un nuevo número de orden. Para ello nos ponemos en la celda I3 y escribimos la fórmula:
=SI(H3=0;MAX($G$3:$G$20)+1;G3)  y la copiamos hasta I20.
Cunado el valor de la celda es 0 le estamos pidiendo que calcule el máximo de nuestro lista de orden (en nuestro ejemplo es 18) y le sume 1. De esta manera, todas las celdas en blanco de nuestra listado original de códigos, pasará a tener un nuevo número de orden que, en nuestro ejemplo, será de 19 (18+1). En el caso de que el valor de la celda no sea 0, le pedimos que escriba el valor número de orden que le corresponde a dicho código. Esto nos servirá para "reordenar" a continuación la información manteniendo el orden original pero evitando las celdas en blanco:
Ya sólo nos queda "recolocar" de menor a mayor estos nuevos números de orden y obtener el código correspondiente. Nos situamos en J3 y escribimos:
=SI.ERROR(BUSCARV(K.ESIMO.MENOR($I$3:$I$20;G3);$G$3:$H$20;2;FALSO);"X")
K.ESIMO.MENOR nos reordena de menor a mayor los números que hemos obtenido en el rango I3:I20. Al anidar esta función dentro de BUSCARV lo que hacemos es obtener el código relacionado con el número de orden. Finalmente, anidamos dentro de SI.ERROR para que aquellos valores que no se encuentran dentro de la tabla de búsqueda (en concreto el valor 19, que se corresponde con las celdas en blanco) no devuelvan el valor de error #N/A y se conviertan en X:
Ya sólo nos queda crear la fórmula en la validación de datos. Seleccionamos el rango J3:J20 y le creamos el nombre Listado. Desde C2 vamos a Validación de datos. En "Permitir" seleccionamos Lista y escribimos la siguiente fórmula:
=DESREF(J3;0;;CONTAR.SI(listado;"<>X"))
De esta manera obtendremos nuestra lista desplegable, manteniendo el mismo orden que el listado original, sin incluir ninguna fila en blanco:

lunes, 26 de octubre de 2015

Sumar Grupos de n Filas con DESREF

"Tengo un listado con muchos valores y tengo que realizar sumas de varias filas. Estas sumas pueden cambiar, es decir, en ocasiones necesito sumar de 2 en 2 valores; otras de 4 en 4 valores; etcétera. Me gustaría saber si se puede automatizar con fórmulas".

Se puede. Veamos cómo. Partimos del siguiente ejemplo, en el que vamos a manejar una lista de 30 valores:
En el rango B5:B34 generamos un número de orden del 1 al 30:
Habilitamos como entrada de datos el valor de "n", es decir, el valor de "cada cuántas filas" queremos sumar. Para ello disponemos una entrada de datos en las celdas B2 y C2:
Nos situamos en la celda D5 y escribimos la siguiente fórmula:
=SI(RESIDUO(B5;$C$2)=0;SUMA(DESREF(C5;;;-$C$2));"")
y la copiamos hasta el final de la lista de valores (en nuestro caso hasta la celda D34).
La primera parte de la fórmula comprueba si el número de orden es múltiplo del valor de n (el valor introducido en C2). Si no lo es, el residuo del cociente no será cero y, por lo tanto, no pondrá nada, ya que es lo que le hemos indicado en la segunda parte del condicional (que escriba ""). Si el número de orden es múltiplo del valor introducido en C2, entonces el residuo del cociente será cero y ejecutará la parte de la fórmula:
SUMA(DESREF(C5;;;-$C$2))  
En nuestro ejemplo, en la celda D7 excel se encontrará que el número de orden de esa fila es el 3 y que dicho número sí es múltiplo del introducido en C2, que es 3. Al estar 2 celdas más abajo que la fórmula original ésta se habrá transformado en (la parte que hace referencia a la suma) SUMA(DESREF(C7;;;-$C$2))  que significa que sume tres celdas hacia arriba desde C7 (incluyendo C7). Es hacia arriba porque al 4º argumento de la función DESREF, Alto, le hemos puesto signo negativo. Veamos el resultado con distintos valores en la celda C2 (valores de n):


sábado, 17 de octubre de 2015

Series con Repetición

"Necesito generar una serie, con incremento de uno en uno, que repita los valores un determinado número de veces. Por ejemplo 1-1-1, 2-2-2, 3-3-3, 4-4-4, etc o, por indicar otro ejemplo, 4-4-4-4-4, 5-5-5-5-5, 6-6-6-6-6, etc".

Para resolver este problema, utilizaremos una única fórmula con las funciones SI, CONTARA y RESIDUO.

Partimos del siguiente modelo:
En la celda C3 tenemos el número de veces que queremos que se repita cada valor, y en E3 el número en el que queremos que comience la serie.
Nos situamos en la celda E4 y escribimos la siguiente fórmula (que copiamos hacia abajo hasta la celda que necesitemos en función del tamaño de la serie que queramos generar):

=SI(RESIDUO(CONTARA($E$3:E3);$C$3)=0;E3+1;E3)

sábado, 26 de septiembre de 2015

Insertar n Filas en Blanco Sin Macros

"Tengo más de 450 registros y necesito insertar 5 filas en blanco entre cada registro".

En mi post Insertar Filas Intercaladas,  vimos como insertar una fila en blanco entre registros de una manera muy sencilla. A continuación voy a explicar, de manera también muy sencilla y sin hacer uso de macros, como resolver este problema tan habitual. Partimos del siguiente ejemplo:

Y lo que queremos conseguir es lo siguiente:

* Para ello nos situamos en la celda B4 y escribimos un 1.
* En la celda B5 escribimos la fórmula =B4+6, es decir, la celda anterior más el número de filas que queremos que aparezcan en blanco más uno (en nuestro ejemplo son 6 porque 5 filas en blanco más 1).
* Copiamos hasta el final de nuestra tabla, esto es, hasta B13:

* Nos situamos en B14 y escribimos la fórmula =B4+1.
* Tenemos 10 registros y, además, queremos incluir 5 filas en blanco por registro. Por lo tanto: 10+(10*5) = 60. Por lo tanto copiamos la fórmula de B14 hacia abajo hasta alcanzar el resultado 60:
* Ahora seleccionamos B4:B63 y copiamos y pegamos como valores
* Finalmente, nos situamos en la celda B4 y vamos a ordenar de menor a mayor:
Y ya está. La filosofía es muy similar a la planteada en el post de Insertar Filas Intercaladas, pero este caso nos sirve para cualquier número de filas en blanco:

miércoles, 26 de agosto de 2015

Ordenar con Fórmulas en Base a dos Criterios

"Mi pregunta es la siguiente ¿Cómo solucionar el ordenar con fórmulas una lista con cifras repetidas utilizando una segunda variable? En el ejemplo que nos muestras en Ordenar Automáticamente una Lista con Valores Repetidos me interesa ordenar los casos repetidos teniendo en cuenta el número de empleados de cada zona. Es decir, que quede por delante, en caso de iguales ventas, la zona que menos empleados tenga".

Empecemos recordando el ejemplo en cuestión y añadiéndole, de paso, la columna de número de empleados:
Como se puede comprobar, hay zonas que obtienen idéntica cifra de ventas, como por ejemplo Galicia, Asturias y Madrid o Aragón y País Vasco, etcétera. En el post Ordenar Automáticamente una Lista con Valores Repetidos resolvimos la cuestión de ordenar automáticamente (con fórmulas) esta lista y, por otro lado, resolvimos el problema de encontrarnos con valores repetidos. En este caso lo que buscamos es ordenar por medio de fórmulas esta lista pero en base no a uno si no a dos criterios. Es decir, que primero ordene las zonas en base a la cifra de ventas y que en aquellos casos en los que haya empate sea el número de empleados el criterio a seguir para establecer el orden.
Lo primero que hacemos es crear una columna para realizar un proceso intermedio, a la que llamaremos Proceso1:
La fórmula que he escrito en la celda G3 y que después he copiado hasta G14 es:
=D3+1-(E3/10000)
Lo que hago es sumar a la cifra de ventas el número de empleados pero "ponderándolo". Al dividir la cifra de empleados por un número lo suficientemente grande lo que conseguimos es que al sumárselo a la cifra de ventas no altere la parte entera de dicho número (por lo que la cifra de ventas será el primer elemento de orden) pero pueda establecer una jerarquía con los decimales. El problema aquí es que queremos que aparezca primero la zona con más ventas PERO con MENOS empleados. Para conseguir esto lo que hacemos es restarle a 1 el resultado del cociente que acabo de explicar, o lo que es lo mismo:
1-(E3/10000). Con esta expresión no alteramos la parte entera de las ventas y conseguimos establecer un orden de menor a mayor con los decimales. Así las cosas, aplicamos ahora la función JERARQUIA: En la celda I3 escribimos la fórmula:
=JERARQUIA(G3;$G$3:$G$14)   y la copiamos hasta la celda I14:
Procedemos ahora a identificar cada una de estas cifras con la zona en cuestión. Para ello preparamos la siguiente salida de datos:
Nos situamos en la celda C18 y escribimos:
=INDICE($C$3:$C$14;COINCIDIR($B18;$I$3:$I$14;0))   y copiamos hasta C29:
En D18 escribimos:
=BUSCARV($C18;$C$3:$E$14;2;FALSO)   y copiamos hasta D29.
En E18 escribimos:
=BUSCARV($C18;$C$3:$E$14;3;FALSO)   y copiamos hasta E29:
Problema resuelto!

martes, 21 de julio de 2015

Máximo de un Alfanumérico

"Tengo un listado en el que llevo el seguimiento de varias ordenes. Todas ellas están compuestas por un código único alfanumérico de 7 caracteres. Los tres primeros son siempre el texto GIO y los otros cuatro son números. Necesito hallar el código más alto en función de su número".

Partimos del siguiente ejemplo:

Al tratarse de entradas alfanuméricas (texto y números) excel las considera texto y, en consecuencia, no podemos utilizar directamente la función MAX. Podemos resolver el problema de diferentes maneras. Una muy sencilla es "trocear" las entradas para separar la parte de texto de la de número. Para ello generamos una columna de proceso:
En la celda D6 escribimos la fórmula:
=VALOR(DERECHA(H6;4))   y la copiamos hasta D23.

De esta manera estamos obteniendo los 4 dígitos con la función DERECHA, y convirtiendo dichos dígitos, que hasta aquí excel trata como texto, a números con la función VALOR: 
Nos situamos ahora en la celda B3 y escribimos la siguiente fórmula:
Lo que estamos haciendo es CONCATENAR el texto "GIO", con el que comienzan todos los códigos del listado, con el valor MÁXIMO  de los números:
Podemos concluir aplicando Formato Condicional al rango B6:B23 para que destaque el máximo valor, como se muestra en la imagen.

martes, 30 de junio de 2015

Contar Duplicados en Distintas Hojas

"Tengo una lista con registros en la hoja 1 y otras listas en 6 ó 7 hojas más. Me gustaría saber cuántas veces se repiten los registros de la hoja 1 en cada una de las listas de todas las hojas".

Partimos del siguiente ejemplo. En la HOJA 1 tenemos los siguientes datos originales:
Tenemos además, en nuestro ejemplo, tres hojas con listas con registros. Lo que queremos es contar cuántas veces se repite cada uno de los números de la lista original en cada una de las listas de las distintas hojas:
Lo primero que vamos a hacer es dar nombre a las listas de las hojas 2, 3 y 4. Para ello vamos a la hoja 2 y seleccionamos el rango A1:A19 y vamos a la ficha Fórmulas y pulsamos en Crear desde la selección. En la ventana que se nos abre aceptamos con Fila superior marcado:
De esta manera ya tenemos el nombre LISTA1 creado. Hacemos lo mismo con las listas de las hojas 3 y 4. Volvemos a la primera hoja y preparamos la salida de datos:
Nos situamos en la celda D3 y escribimos la siguiente fórmula (que copiaremos hasta F3 y, posteriormente, hasta F20):

=CONTAR.SI(INDIRECTO(D$2);$B3)

De esta manera, estaremos contando cuántas veces se repiten cada uno de los registros del rango B3:B20 en las distintas listas de las otras hojas (la función INDIRECTO tomará el nombre del rango correspondiente de la fila 2):

lunes, 25 de mayo de 2015

Dígitos Duplicados en Distinto Orden


"Tengo un listado de valores de tres dígitos y necesito detectar cuáles están duplicados en el mismo o distinto orden. Por ejemplo 123, 231, 456, 456, 564, etcétera". 

Partimos del siguiente ejemplo:

Preparamos ahora las siguientes tablas para "manipular" los datos iniciales. Aunque el enunciado habla de 3 dígitos, preparamos tablas para contemplar hasta 6 dígitos:
Nos situamos en E3 y escribimos la fórmula(que copiaremos hasta J3 y, finalmente, hasta J17):
=SI.ERROR(VALOR(EXTRAE($B3;E$2;1));"")
De esta manera estamos extrayendo cada dígito y reconvirtiéndolo en VALOR (ya que la función EXTRAE nos lo devuelve como texto):
 Una vez hecho esto, nos situamos en L3 y escribimos la fórmula:
=SI.ERROR(K.ESIMO.MENOR($E3:$J3;L$2);"")
y copiamos hasta Q2 y finalmente hasta Q17:
De esta manera hemos reordenado de menor a mayor los dígitos. Procedemos ahora a unirlos de nuevo con la función CONCATENAR (&). En S3 escribimos:
y copiamos hasta S17:
Ahora ya podemos proceder a contar los números que se repiten en más de una ocasión y "asociarlos" con sus "originales". Nos situamos en la celda C3 y escribimos la fórmula:
=SI(CONTAR.SI($S$3:$S$17;S3)>1;"Repetido";"")    y copiamos hasta C17:

sábado, 23 de mayo de 2015

Obtención Aleatoria de Valores de un Rango Sin Repetición

"En tu artículo Selección Aleatoria de un Valor de un Rango (2) nos explicaste cómo seleccionar 1 valor de manera aleatoria entre los valores existentes en un determinado rango y entre los que se incluyen celdas en blanco. En mi caso, necesito seleccionar 6 valores de dicho rango y que, además, no se repita ninguno de los seleccionados".

Como suelo decir, no problemo! Partimos de la siguiente lista de valores (y utilizaremos 4 columnas de procesos para conseguir nuestro objetivo):
Empezamos por el Paso 1 generando una lista de valores únicos. Para ello nos situamos en la celda C3 y escribimos la fórmula (que copiamos posteriormente hasta la celda C28):
=SI(B3="";"";SI(CONTAR.SI($B$3:B3;B3)>1;"";B3))
De esta manera ya tenemos nuestro listado original "filtrado" con los valores únicos. A continuación, en la celda D3 escribimos un 1 y en la celda D4 un 2. Seleccionamos ambas, y copiamos hasta la celda D28 para generar un número de orden:
Nos situamos ahora en la celda F3 y procedemos con la siguiente fórmula (que copiamos hasta F28):
=K.ESIMO.MAYOR($C$3:$C$28;D3)
De esta manera ya tenemos ordenada la lista de valores únicos de mayor a menor, dejando las celdas en blanco con el mensaje de error #¡NUM! agrupadas al final de dicha lista:
Seleccionamos ahora el rango E3:E28 y escribimos la fórmula: =ALEATORIO()  y acabamos pulsando Ctrl + Enter:
Ya tenemos todos los ingredientes para poder proceder con la "formulita final". Para ello preparamos la zona de salida de datos en la columna I:
Nos situamos en la celda I4 y escribimos:
=BUSCARV(JERARQUIA(E3;$E$3:DESREF($E$2;CONTAR($F$3:$F$28);));$D$3:$F$28;3;FALSO)

CONTAR nos permite saber cuántas celdas contienen un número (y por tanto no son un error tipo #¡NUM!). Con la función JERARQUIA vamos a obtener el puesto relativo que ocupa E3 dentro del rango dinámico de, en nuestro ejemplo, E3:E17 (ya que el resto de valores aleatorios se corresponden con un valor de error tipo #¡NUM!). Una vez obtenido el puesto relativo, por ejemplo si obtenemos el 5, le pedimos que, por medio de la función BUSCARV, busque dentro del rango D3:F28 en la primera columna dicho valor y nos devuelva su correspondencia en la tercera columna, que en nuestro ejemplo se corresponde con el valor 57.
Si copiamos la fórmula de I4 hasta I9 ya tendremos nuestros seis valores aleatorios sin repetición y evitando las celdas en blanco:
Pulsando F9 obtendremos distintas combinaciones aleatorias