viernes, 28 de diciembre de 2012

Extraer elementos repetidos

Vamos a continuar con la idea expresada en el último artículo, Extraer elementos no repetidos, pero, ahora, extraeremos los elementos repetidos. El problema se puede definir de esta manera: dada una lista de nombres, extraer, únicamente, aquéllos que estén repetidos.

La hoja donde vamos a hacer el ejercicio se llama Repetidos.
 
Primera solución: Poner fondo amarillo a los países repetidos

Accedemos a Fórmulas + Asignar nombre + Definir nombre y creamos el nombre Países con la siguiente definición:

 Países  =DESREF(Repetidos!$B$2;1;0;CONTARA(Repetidos!$B:$B)-1;1)

Seleccionamos B3:B25 y vamos a Inicio + Formato condicional + Nueva regla. Elegimos Utilice una fórmula que determine las celdas para aplicar formato y ponemos la fórmula siguiente:

 =Y(CONTAR.SI(Países;B3)<>1;B3<>"")

... pulsamos el botón Formato y, en la pestaña Relleno, elegimos el color amarillo.

Creamos una segunda regla con esta fórmula:

 =NO(ESBLANCO(B3))

... pulsamos el botón Formato y, en la pestaña Bordes, elegimos el color gris y Contorno.

Segunda solución: Copiar los nombres repetidos en otra columna en orden invertido

Seleccionamos H3:H16 y escribimos:
=CONTAR.SI(Países;Países)<>1      [Terminar con Ctrl + Mayús + Intro]
 

Seleccionamos I3:I16 y escribimos:
=(H3:H16)*FILA(Países)-2      [Terminar con Ctrl + Mayús + Intro]
 

Seleccionamos J3:J16 y escribimos:
=FILA(Países)-2      [Terminar con Ctrl + Mayús + Intro]
 

Seleccionamos K3:K16 y escribimos:
=K.ESIMO.MAYOR(I3:I16;J3:J16)      [Terminar con Ctrl + Mayús + Intro]
 

Seleccionamos L3:L16 y escribimos:
=INDICE(Países;K3:K16)      [Terminar con Ctrl + Mayús + Intro]
 
Omito la justificación de estos pasos porque ya se han explicado en el artículo Extraer elementos no repetidos.
 

Seleccionamos D3:D25 y escribimos:
=INDICE(Países;K.ESIMO.MAYOR((CONTAR.SI(Países;Países)<>1)*FILA(Países)-2;FILA(Países)-2))     [Terminar con Ctrl + Mayús + Intro]
 
Los formatos condicionales para el rango D3:D25 son: 

 =ESERROR(D3)
 
... y, en la pestaña Fuente, color blanco.

 =NO(ESERROR(D3))
 
... y, en la pestaña Bordes, color gris y Contorno.
 
Esta solución tiene el inconveniente de que los nombres aparecen repetidos tantas veces como lo están en la lista original. Quizás sería mejor que sólo apareciesen una vez, y esto es lo que vamos a tratar de conseguir con la tercera solución.
 
Tercera solución: Copiar una sola vez los nombres repetidos en orden natural
 
Aislamos los nombres de los países repetidos.
 
En N3:
=SI(B3="";"";SI(CONTAR.SI(Países;B3)<>1;B3;""))    [Copiamos la fórmula hasta la fila 25]
 
Vamos contando las veces que cada nombre va apareciendo a medida que bajamos en la lista de la columna N. También contaremos las apariciones de las celdas en blanco aunque después tendremos que desestimarlas.
 
En O3:
=CONTAR.SI($N$3:N3;N3)    [Copiamos la fórmula hasta la fila 25]
 
Nos quedamos con los nombres que han aparecido la primera vez.
 

En P3:
=SI(Y(N3<>"";O3<>1);"";N3)    [Copiamos la fórmula hasta la fila 25]
 
Si en la columna P hay un nombre, ponemos su posición en la lista original; en caso contrario, ponemos un número muy grande (10300).
 

En Q3:
=SI((P3)="";10^300;FILA()-2)     [Copiamos la fórmula hasta la fila 25]
 
Generamos una lista de números consecutivos del 1 al 23.
 

En R3:
=FILA()-2     [Copiamos la fórmula hasta la fila 25]
 
Ordenamos los números de la columna Q.
 

En S3:
=K.ESIMO.MENOR($Q$3:$Q$25;R3)      [Copiamos la fórmula hasta la fila 25]
 
Extraemos los nombres de los países.
 

En F3:
=INDICE(Países;S3)      [Copiamos la fórmula hasta la fila 25]
 
Ponemos en la columna F un formato condicional similar el de la columna D y el ejercicio quedará terminado.
 
Si utilizamos la segunda solución podremos eliminar las columnas H a L porque hemos creado una fórmula matricial compuesta en la columna D. En la tercera solución no he encontrado una fórmula que permita eliminar las columnas auxiliares.
 
 
 
 

No hay comentarios:

Publicar un comentario