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 

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: