Resaltar Duplicados Concatenados

"Necesito una fórmula que localice códigos duplicados en la columna B y, si los encuentra, que los coloree sólo si en la columna C los nombres coinciden también, pero no puedo añadir columnas adicionales en la hoja."

Necesitamos concatenar la columna B y la C para comprobar si hay entradas duplicadas y, en tal caso, resaltar dichas celdas pero sin utilizar columnas adicionales en la hoja. Para ello formularemos directamente en la herramienta de Formato Condicional. Lo solucionaremos con la ayuda de la función SUMAPRODUCTO. Empezaremos formulando en la hoja para que se entienda mejor y luego pasaremos dicha formulación a la herramienta. Partimos del ejemplo de la primera imagen y queremos conseguir el resultado de la segunda imagen:

Para ello nos situamos en la celda E3 y escribimos la siguiente fórmula que copiaremos hasta la celda E10: 

SUMAPRODUCTO es una función que suma el producto de dos rangos (rangos que deben tener la misma dimensión). Si la fórmula fuese =SUMAPRODUCTO(B3:B10;C3:C10) excel ejecutaría (B3*C3)+(B4*C4)+(B5*C5)... Al introducir un criterio en la función (en nuestro caso el criterio es que el primer rango sea =$B3 y que el segundo sea =$C3) , excel genera una matriz de resultados tipo VERDADERO/FALSO que al multiplicarlo por 1 se convierte en una matriz del tipo 1/0. De esta manera estamos consiguiendo valores 1 para el rango B3:B10 en aquellos casos en los que un código esté repetido y valores cero para los que no lo estén.Y lo mismo en el rango C3:C10. Al combinar ambos resultados obtendremos valores mayores de 1 para aquellas combinaciones repetidas. Para aplicar esta formulación directamente en la herramienta de Formato Condicional debemos especificar una condición que, nuevamente, genere un resultado tipo VERDADERO/FALSO. Es por ello que introducimos el >1 del final de la fórmula. El resultado es el siguiente:

Como no podemos utilizar columnas adicionales en la hoja, seleccionamos ahora el rango B3:C10 y vamos a Formato condicional. Elegimos la opción de introducir una fórmula y escribimos (o copiamos y pegamos directamente) nuestra fórmula. En el botón Formato... damos la apariencia de relleno de color que deseemos y aceptamos:

Evidentemente, procedemos a borrar la formulación realizada en la columna E. Para finalizar correctamente el modelo debemos considerar que si dejamos celdas en blanco excel las rellenará con el formato que le hayamos asignado:

  Para evitar ésto, ampliamos la fórmula de la siguiente manera:

Destacar Datos Repetidos más de n-veces

"En una lista de datos, los cuales se repiten varias veces, necesito resaltar aquellos datos que se repitan mas de dos veces, pero que las dos primeras veces que aparezcan no se marquen". 

Partimos del siguiente ejemplo:

 Y queremos conseguir lo siguiente:

Para ello nos seleccionamos el rango B5:B24. Vamos a Formato Condicional y elegimos "Utilice una fórmula que determine las celdas para aplicar formato". En "Editar una descripción de regla" escribimos la siguiente fórmula:

=CONTAR.SI($B$5:B5;B5)>$C$2

Pulsamos el botón Formato... y seleccionamos el aspecto que queremos que tomen las celdas a destacar. Acabamos pulsando Aceptar y trabajo terminado.

Rellenar Hacia Arriba Celdas en Blanco

"Necesito rellenar celdas en blanco con el contenido de los rótulos que aparecen al final de cada grupo, es decir, en vez de hacia abajo tiene que ser hacia arriba."

Partimos del siguiente ejemplo:

Queremos que en el rango B2:B4 aparezca el rótulo Zona Norte; en el rango B5:B10 Zona Sur, etcétera.

Para ello seleccionamos primeramente el rango B2:B17. Vamos a la ficha Inicio al grupo Modificar y seleccionamos el icono de los prismáticos. Dentro de éste seleccionamos Ir a Especial... y marcamos Celdas en Blanco:

Obtenemos el siguiente resultado:

Tenemos las celdas en blanco seleccionadas y B2 como celda activa. Ahora CON LA TECLA Ctrl PULSADA, hacemos clic en la primera celda superior a nuestro primer rótulo. En nuestro caso el primer rótulo lo tenemos en B4 y, por lo tanto, hacemos clic en B3 (insisto en que debemos hacer dicho clic CON LA TECLA Ctrl PULSADA). Obtenemos lo siguiente:

Ahora la celda activa es B3. Directamente, sin tocar nada más ni hacer clic en ningún sitio, escribimos la fórmula: =B4   porque es el primer rótulo que tenemos, y acabamos pulsando Ctrl + Enter

Promedio de Valores Intermedios

"Necesito hacer un promedio de cifras relativas a 11 años, de los cuales debo eliminar los dos de valor superior y los dos de valor inferior, sean estos iguales o no".

Vamos a solucionar este problema utilizando las funciones INDICE, K.ESIMO.MAYOR y PROMEDIO. Partimos del siguiente ejemplo:

Lo primero que hacemos es crear una tabla como la que se muestra a continuación en la que en la primera fila escribiremos números de orden, en nuestro ejemplo 11. En la celda G4 escribimos la fórmula:

=K.ESIMO.MAYOR(B$4:B$14;$F4)

Con esta fórmula, que debemos copiar hasta I4 y finalmente hasta I14, ordenaremos de mayor a menor por columna los valores:

Una vez hecho esto los valores que nos interesan son los sombreados en verde, es decir, los 7 valores intermedios. Para calcular el promedio de estos valores utilizamos la fórmula siguiente que escribiremos en la celda B17 (y posteriormente copiamos hasta D17):

=PROMEDIO(INDICE(G$4:G$14;3):INDICE(G$4:G$14;9))

Producto Matricial Condicionado

"Tengo una columna de datos que necesito multiplicar entre si, pero sólo aquellos datos que superen una cierta cifra".

Para resolver este problema vamos a utilizar la función PRODUCTO dentro de una fórmula matricial con un condicional simple. Partimos del siguiente ejemplo:

Lo que queremos conseguir es multiplicar el rango B3:B16 pero sólo aquellos valores que superen la cifra límite que nos encontramos en D3, y que en nuestro ejemplo es el valor cero.

La función PRODUCTO multiplica todos los números proporcionados como argumento, ya sea de uno o varios rangos. Una solución "manual" de este caso sería la fórmula siguiente:

=PRODUCTO(B3;B5;B6;B8;B11;B12;B14;B16)

Evidentemente lo que estamos buscando es no tener que seleccionar manualmente los valores a multiplicar. Para ello nos situamos en la celda D6 y escribimos:

=PRODUCTO(SI(B3:B16>D3;B3:B16)) y finalizamos pulsando Ctrl + Shift + Enter  ya que se trata de una fórmula matricial. Quedará así:

{=PRODUCTO(SI(B3:B16>D3;B3:B16))}

Esta fórmula comprueba si los valores existentes en el rango B3:B16 son mayores que el valor en D3 (en nuestro caso este valor es cero). Si se cumple esta condición entonces aplicará el producto entre los valores de dicho rango que cumplan tal restricción:

Podemos cambiar el límite en D3 y automáticamente se recalculará el producto: