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
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.
No hay comentarios:
Publicar un comentario