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.
 
 
 
 

miércoles, 26 de diciembre de 2012

Extraer elementos no repetidos

Si en la columna B hay elementos repetidos y no repetidos y queremos extraer los que no están repetidos, podremos destacarlos con un color, utilizando Formato condicional, o copiarlos en otro lugar de la hoja usando fórmulas.

Daremos tres soluciones al ejercicio. La primera consistirá en poner un fondo verde a las celdas que cumplen el requisito mencionado Las otras dos consistirán en obtener sendas copias en las columnas D (orden invertido) y F (orden natural). Las columnas H:R se usarán para hacer cálculos intermedios. Finalizado el ejercicio podrán borrarse.
 
Primera solución: Poner fondo verde a los países no repetidos
 
Si prevemos que la lista vaya a ampliarse o reducirse, lo mejor será crear un nombre dinámico con la función DESREF. Este nombre nos servirá en cualquiera de los métodos que usemos para resolver el ejercicio.
 
Accedemos a Fórmulas + Administrador de nombres. Pulsamos el botón Nuevo y creamos el nombre Países:
 
NoRepetidos es el nombre de la hoja.
 
Vamos a aplicar el Formato condicional al rango B3:B25, pero si se van a añadir más nombres, tendremos que ampliarlo.
 
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:
 
 =CONTAR.SI(Países;B3)=1
 
Pulsamos el botón Formato y, en la pestaña Relleno del cuadro de diálogo Formato de celdas, elegimos el color verde.
 
Necesitamos una nueva condición para poner bordes a las celdas. Volvemos a crear una nueva regla con la fórmula siguiente:
 
  =NO(ESBLANCO(B3))
 
En este caso, hay que ir a la pestaña Bordes y elegir un color gris y la opción Contorno.
 
El resultado será, como se muestra en la primera figura, que las nombres no repetidos aparecerán sobre un fondo verde y las celdas tendrán un borde gris.
 
Segunda solución: Copiar los nombres no 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]
 
Con esta fórmula contamos el número de veces que aparece cada país en la lista. Si aparece una vez, la fórmula devuelve VERDADERO; en caso contrario, devuelve FALSO.
 
Seleccionamos I3:I16 y escribimos:
=(H3:H16)*FILA(Países)-2     [Terminar con Ctrl + Mayús + Intro]
 
FILA(Países)  devuelve el número de la fila de cada país. Si le restamos dos unidades obtendremos números consecutivos del 1 en adelante (el primer país está en la fila 3). Si multiplicamos la matriz (H3:H16) (recordemos que VERDADERO equivale a 1 y FALSO a cero) por la matriz FILA(Países)-2, habremos convertido la columna H en una serie de números que indican la posición en la lista (en el caso de que el dato VERDADERO) o -2 (si es FALSO).
 
Ahora, necesitamos una serie correlativa de números del 1 al 14 (es el total de elementos de la lista).
 
Seleccionamos J3:J16 y escribimos:
=FILA(Países)-2      [Terminar con Ctrl + Mayús + Intro]
 
Esta serie la usaremos en el siguiente paso. Vamos a utilizar K.ESIMO.MAYOR para ordenar la columna I de mayor a menor.
 
Seleccionamos K3:K16 y escribimos:
=K.ESIMO.MAYOR(I3:I16;J3:J16)      [Terminar con Ctrl + Mayús + Intro]
 
Finalmente, con la función INDICE determinaremos los valores de la columna B que están en las filas indicadas en la columna K.
 
Seleccionamos L3:L16 y escribimos:
=INDICE(Países;K3:K16)      [Terminar con Ctrl + Mayús + Intro]
 
Naturalmente, cuando intenta encontrar un dato en la fila -2, la fórmula no encuentra nada y devuelve un error. No importa porque vamos a crear una fórmula compuesta en D3:D25 y, luego, podremos borrar las columnas H:L.
 
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]
 
Tenemos que aplicar un formato condicional para poner bordes y ocultar los errores (fuente blanca).
 
La primera regla que hay que poner en D3:D25 es:
 
 =ESERROR(D3)
 
... y, en la pestaña Fuente, elegimos el color blanco.
 
La segunda regla es:
 
 =NO(ESERROR(D3))
 
... y, en la pestaña Bordes, elegimos el color gris y la opción Contorno.
 
Tercera solución: Copiar los nombres no repetidos en otra columna en orden natural
 
Aislamos los países no repetidos en N3:N16
 
Seleccionamos N3:N16 y escribimos:
=SI(CONTAR.SI(Países;Países)=1;Países;"")      [Terminar con Ctrl + Mayús + Intro]
 
A los países que hemos aislado les asociamos su posición en la lista original. A las celdas en blanco les asociamos un número muy grande; por ejemplo, 10300

Seleccionamos O3:O16 y escribimos:
=SI((N3:N16)="";10^300;FILA(Países)-2)     [Terminar con Ctrl + Mayús + Intro]
 
Generamos una lista de números del 1 al 14 (hay 14 países en la lista).
 
Seleccionamos P3:P16 y escribimos:
=FILA(Países)-2      [Terminar con Ctrl + Mayús + Intro]
 
Ordenamos la columna P de menor a mayor.
 
Seleccionamos Q3:Q16 y escribimos:
=K.ESIMO.MENOR(O3:O16;P3:P16)      [Terminar con Ctrl + Mayús + Intro]
 
Los números mayores (que corresponden a los países repetidos) están al final de la lista. Usando INDICE buscaremos los países que ocupan en la lista original las posiciones indicadas en la columna P. Lógicamente, no hay ningún país en la posición 10300 y, en consecuencia, se obtendrá un error.
 
Seleccionamos R3:R16 y escribimos:
=INDICE(Países;Q3:Q16)      [Terminar con Ctrl + Mayús + Intro]
 
Ponemos la fórmula definitiva en la columna F.
 
Seleccionamos F3:F25 y escribimos:
=INDICE(Países;K.ESIMO.MENOR(SI((SI(CONTAR.SI(Países;Países)=1;Países;""))="";10^300;FILA(Países)-2);FILA(Países)-2))      [Terminar con Ctrl + Mayús + Intro]
 
El formato condicional que debemos poner a la columna F es similar al de la columna D.
 
Seleccionamos F3:F25 y ponemos la primera regla:
 
 =ESERROR(F3)
 
... y, en la pestaña Fuente, elegimos el color blanco.

La segunda regla es:
 
=NO(ESERROR(F3))
 
... y, en la pestaña Bordes, elegimos el color gris y la opción Contorno.
 
 
 
 

jueves, 13 de diciembre de 2012

Utilización de símbolos para personalizar formatos

En Excel 2010, utilizando Formato condicional, es muy fácil asociar símbolos de diferentes formas y colores a las celdas según sus valores. Combinando varios formatos condicionales y utilizando Formato personalizado, podemos conseguir que los valores numéricos también cambien de color. Por ejemplo, en la lista de resultados del Gran Premio de Australia de F1 del año 2009, hemos añadido una columna para calcular, al final de la carrera, los puestos que adelantó cada piloto con respecto a su posición en la parrilla de salida.

El ejercicio lo resolveremos por tres métodos distintos (en la figura, son las columnas etiquetadas como Uno, Dos y Tres). Los pilotos que terminaron la carrera en el mismo puesto que tenían en la parrilla de salida están marcados en marrón; los que avanzaron algún puesto están en verde; y los que se retrasaron, aparecen en rojo.
 
Primer método: Formato condicional (no funciona en Excel 2003)
 
En J4:
=H4-B4    (Copiamos la fórmula hasta la fila 23)
 
Seleccionamos J4:J23, accedemos a Inicio + Formato condicional + Nueva regla y elegimos Aplicar formato a todas las celdas según sus valores. Rellenamos el cuadro de diálogo con estos valores:
 
El resultado será:
 
Para que los números queden del mismo color que las flechas podemos utilizar el formato condicional o el personalizado. Por el momento, seguiremos usando el condicional y dejaremos el personalizado para otro caso.
 
Volvemos a seleccionar J4:J23 y accedemos a Inicio + Formato condicional + Administrar reglas. Hacemos clic en Nueva regla y elegimos Utilice una fórmula que determine las celdas para aplicar formato. La fórmula que pondremos será: =J4=0
 
Pulsamos el botón Formato y, en el apartado Color de la pestaña Fuente, seleccionamos un color anaranjado. Terminamos pulsando Aceptar para pasar al siguiente formato.
 
Volvemos a pulsar Nueva regla, ponemos la fórmula: =J4>0 y elegimos una fuente de color verde. Para terminar, pulsamos de nuevo en Nueva regla, ponemos la fórmula: =J4<0 y seleccionamos la fuente roja.
 
Han sido necesarios cuatro formatos condicionales.
 
 
Segundo método: Formato personalizado (vale para cualquier versión de Excel)
 
En L4:
=H4-B4    (Copiamos la fórmula hasta la fila 23)
 
Necesitamos tres símbolos de tipo flecha; por ejemplo, de la fuente Arial podemos sacar ▲, ▼ y ►. Lo más cómodo es usar una calda vacía y colocar en ella los tres símbolos. Se hace así:
  • Hacemos clic en Q1 (vale cualquier celda vacía) y le aplicamos la fuente Arial.
  • Accedemos a Insertar + Símbolo y elegimos fuente Arial.
  • Hacemos doble clic en cada uno de los tres símbolos (o elegimos Insertar) y pulsamos Cerrar. En Q1 aparecerá: ▲▼►.
  • Nos ponemos en Q1 y pulsamos Ctrl + C para copiar los tres símbolos en el portapapeles. Ya estamos preparados para el siguiente paso.
Seleccionamos L4:L23 y pulsamos Ctrl + 1 para entrar en Formato de celdas. En la pestaña Número, elegimos la opción Personalizada y, en Tipo,  ponemos: [Verde]"▲ "0;[Rojo]"▼ "-0;[Color45]"► "0;
 
Lógicamente, los símbolos copiados en el portapapeles son para insertarlos en los lugares correspondientes del formato. Cuando tengamos que escribir uno de ellos, pulsamos Ctrl + V y se insertarán los tres. Luego, tendremos que eliminar los que sobren.
 
Para finalizar, borramos los símbolos de la celda Q1.
 
Tercer método: Formato condicional + 2 columnas (vale para cualquier versión de Excel)
 
En este método, los números y los símbolos van en celdas separadas. Podemos poner diferentes símbolos extraídos de distintas fuentes en una zona vacía de la hoja y utilizar los que queramos. Por ejemplo, en Q4:T6 ponemos:
 
Para insertar estos símbolos, a las celdas R6:T6 debemos ponerles formato Wingdings 3, ya que de no hacerlo, se insertará un carácter distinto. Lo mismo vale para los otros rangos.
 
Si vamos a usar los símbolos de la fuente Wingdings 3, al rango N4:N23 deberemos asignarle fuente Wingdings 3.
 
En O4:
=H4-B4     (Copiamos la fórmula hasta la fila 23)
 
En N4:
=ELEGIR(SIGNO(O4)+2;$S$6;$T$6;$R$6)     (Copiamos la fórmula hasta la fila 23)
 
La función SIGNO devuelve -1 (si el número es negativo), 0 (si es cero) ó 1 (si es positivo). Añadiéndole 2, nos aseguramos que SIGNO(O4)+2 sea 1, 2 ó 3. La función ELEGIR comprueba el valor de SIGNO(O4)+2 y devuelve el símbolo de S6 (si es 1), de T6 (si es 2) o de R6 (si es 3).
 
Para poner el color, seleccionamos N4:O23 y accedemos a Inicio + Formato condicional + Nueva regla. Siguiendo el procedimiento descrito en el primer caso, creamos las siguientes condiciones:
 
Si preferimos usar los símbolos de la fuente Wingdings tendremos que poner formato Wingdings en el rango N4:N23 y sustituir la fórmula de N4 por: =ELEGIR(SIGNO(O4)+2;$S$5;$T$5;$R$5)
 
Para los símbolos en Arial: =ELEGIR(SIGNO(O4)+2;$S$4;$T$4;$R$4)