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 

martes, 28 de abril de 2015

Evitar Registros en Blanco

"Tengo una tabla con registros y sus correspondientes valores. Lo que me gustaría conseguir, a ser posible sin macros, es que excel no permita introducir un nuevo valor si alguna o todas las celdas anteriores al mismo se encuentran en blanco".

En esta ocasión vamos a comenzar con una imagen de lo que queremos evitar:
Como se puede observar, el registro 4 y el 6 no tienen un valor asociado, pero el 5 y el 7 sí. De lo que se trata es de que excel no me permita introducir ni el registro 5 ni el 7 hasta que no "rellene" los anteriores. La solución sin utilizar macros es bastante sencilla. Utilizaremos la herramienta de Validación de datos. Partimos del siguiente ejemplo:

Seleccionamos el rango C4:C12 (NO incluimos C3) y vamos a la ficha Datos y seleccionamos Validación de datos. En configuración elegimos Permitir / Personalizada, y en Fórmula escribimos la siguiente (como se puede ver en la imagen):
=Y($C$3:C3<>"")
Con esta fórmula, en C4 excel evalúa si C3 es distinto de "", o lo que es lo mismo, si hay algo en C3. Si hay algo permite escribir y si no hay nada ("") entonces no permite escribir en C4. En la celda C5 excel evalúa si el rango $C$3:C4 es distinto de "". En C6 excel evalúa si el rango $C$3:C5 es distinto de "". Etcétera. Si intentamos introducir ahora un valor saltándonos alguna fila ocurrirá lo siguiente:

martes, 14 de abril de 2015

Valores Únicos No Repetidos

"Necesito encontrar valores únicos en una tabla (entendiendo por únicos aquellos valores que aparecen una y sólo una vez en el listado) y obtener un nuevo listado donde sólo se consideren los valores nunca repetidos (y que el resto de valores desaparezcan)".

Partimos del siguiente ejemplo:
Nos situamos en la celda D4 y escribimos la siguiente fórmula:
=SI(CONTAR.SI($B$4:$B$18;B4)>1;"";B4)  y la copiamos hasta D18:
Si queremos que excel añada un borde a las celdas que contienen números, podemos hacer uso del Formato Condicional. Para ello seleccionamos D4:D18 y vamos a Formato Condicional y seguimos los pasos que se muestran la siguiente imagen:
Tras escribir la fórmula, pulsamos el botón Formato... y en Bordes elegimos Contorno en color, por ejemplo, granate:
Pulsamos Aceptar y problema resuelto:

lunes, 16 de marzo de 2015

Desplegables Dependientes con Nombres con Espacios en Blanco

"Necesito crear listas desplegables dependientes con validación de datos. El problema es que algunos nombres tienen espacios en blanco y excel no me los reconoce".

Como siempre no hay problema. Pero antes de nada hoy toca felicitar a mi amigo del alma Don José: Muchas felicidades hermano!! Tendremos que celebrarlo, a ser posible en esa vuestra marisquería a la que tantos premios os dan...

Partimos del siguiente ejemplo:
Queremos generar una lista desplegable en B3 con los 4 modelos existentes, a saber, GT 100; ASIUS; PROMETEO; y TT 400. Para ello nos situamos en B3, vamos a Validación de datos y en Permitir elegimos Lista. En Origen marcamos el rango $F$3:$I$3  y aceptamos. Para que al seleccionar uno de los modelos aparezca el tipo de su grupo, debemos ahora crear nombres de dichos rangos. El problema es que en la lista original de modelos algún nombre tiene espacios (GT 100 y TT 400). Para evitar este problema, insertamos una fila entre la 3 y la 4 y copiamos los nombres originales pero sustituyendo ahora los espacios por guión bajo:
Seleccionamos ahora el rango F4:I7 y vamos a la ficha Fórmulas, grupo Nombres definidos y seleccionamos Crear desde la selección. Marcamos Fila superior y aceptamos:
Si nos fijamos en el cuadro de nombres veremos que ya disponemos de éstos:
Nos situamos ahora en D3 y vamos a Validación de datos. En Permitir seleccionamos Lista y en Origen escribimos la siguiente fórmula:
=INDIRECTO(BUSCARH($B$3;$F$3:$I$4;2;FALSO))  y aceptamos.
La parte de BUSCARH lo que hace es buscar el nombre en nuestra lista original y transformarlo en el nombre con guión bajo. Una vez hecho esto, INDIRECTO reconoce el nombre de la "sublista" y nos presenta los tipos relativos a cada modelo: