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:

Promedio Móvil de x Meses

"Tengo un histórico con la cifra de ventas mensual y me gustaría poder calcular el promedio de ventas de los últimos x meses hasta la fecha de hoy".

No problemo. Lo resolveremos haciendo uso de las funciones SI.ERROR, PROMEDIO, DESREF, COINCIDIR, BUSCARV y HOY. Supongamos que tenemos la siguiente tabla con la fecha y su cifra de ventas. Preparamos además la entrada de datos, es decir, el número variable de meses para calcular el promedio:

Empezamos calculando la fecha actual. Para ello nos ponemos en la celda C4 y escribimos la fórmula  =HOY()

Seleccionamos el rango E3:E32 y le damos el nombre Datomes (como siempre lo podemos crear haciendo clic en el Cuadro de nombres, a la izquierda de la barra de fórmulas, escribiendo dicho nombre y pulsando Enter). Nos situamos ahora en C6 y escribimos la fórmula:

=SI.ERROR(PROMEDIO(DESREF(F2;COINCIDIR(BUSCARV(C4;Datomes;1);

Datomes);;-C2;));"No disponible")

Con la función BUSCARV localizamos la fecha actual en nuestra tabla de ventas. Al anidarla dentro de la función COINCIDIR, obtendremos el número de fila en el que se encuentra dicha fecha. A su vez, COINCIDIR se encuentra anidada dentro de la función DESREF y es el argumento de fila, esto es, partiendo de la referencia de celda F2 tiene que contar tantas filas como devuelva COINCIDIR(BUSCARV(C4;Datomes;1). Ya tenemos la fila referente al mes corriente, ahora nos queda indicarle desde qué mes queremos realizar el cálculo del promedio, es decir, de los últimos 12 meses, de los últimos 6 meses, etcétera. Esto lo resolvemos con el argumento alto de la función DESREF. En concreto tendrá que retroceder desde la fila de la fecha actual hasta el número de meses que le indiquemos en la celda de entrada C2, y por ello le ponemos signo negativo a dicha referencia (-C2).

Le he añadido la función SI.ERROR para que si indicamos un número de meses demasiado elevado no aparezca el error #¡REF! sino que aparezca un texto un poco más estético del tipo "No disponible":

Se me olvidaba... Para conseguir que el texto del rótulo de la media(B6) sea variable, es decir, que cambie en función del número de meses que escribamos en C2, tenemos que poner la siguiente fórmula en B6:  ="Media "&C2&" meses:"