viernes, 14 de marzo de 2014

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.

jueves, 20 de febrero de 2014

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

miércoles, 19 de febrero de 2014

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))

viernes, 10 de enero de 2014

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:

lunes, 28 de octubre de 2013

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:"

sábado, 12 de octubre de 2013

Cálculo del NPS (Net Promoter Score)

"¿Podrías indicarme cómo calcular el Net Promoter Score (NPS) con excel?"

Curiosamente he recibido varios mails en las últimas semanas preguntándome diversas cuestiones sobre esta herramienta, el NPS. Aunque la mayoría quieren saber cómo se halla con excel, permitidme una pequeña introducción para aquellos que no sabéis de qué estamos hablando. El NPS es un indicador para medir la satisfacción del cliente en términos de si recomienda tu producto (promotor), le es indiferente (pasivo) o le disgusta tu producto (detractor). La idea es de Fred Reichheld y data de 2003. El modelo es muy sencillo: se les pregunta a los clientes si recomendarían tu producto/servicio o no y que lo valoren de 0 a 10, siendo el cero que no lo recomendarían en ningún caso y siendo el 10 que lo recomendarían seguro. Los valores 9 y 10 son los denominados promotores; los valores 7 y 8 son pasivos; y los valores por debajo de 7 son los detractores. La fórmula que se aplica al total de encuestas realizadas es la siguiente:
NPS= %Promotores - %Detractores
 
Vamos a calcular el NPS haciendo uso de las funciones CONTAR.SI y CONTAR partiendo del siguiente ejemplo:
Seleccionamos el rango C3:C22 y le damos el nombre PUNTOS. A continuación, nos situamos en F2 y escribimos la fórmula:
Indicar finalmente que Cualquier puntuación del NPS que supere el 0% se considera como buena, ya que cuando esto sucede significa que el número de personas que han dado puntuaciones de 9 ó 10 es superior al número de personas que han dado el resto de puntuaciones. A partir del 50% se considera un resultado excelente.

sábado, 14 de septiembre de 2013

Lista de Valores no Repetidos

"Necesito comparar dos columnas y crear una tercera columna en la que aparezcan los datos que no están repetidos. Es decir, si la columna A contiene números del 1 al 12 y la columna B contiene números del 1 al 10, necesito que en la columna C me aparezcan el 11 y el 12, ya que son los únicos dos valores que no están repetidos".

Partimos del siguiente ejemplo:
Vamos a generar una lista con los valores que no estén repetidos. Por otro lado, vamos a ordenar dichos valores de mayor a menor y, finalmente, vamos a resaltar con color de relleno cuáles son estos valores. Para ello trabajaremos con las funciones CONTAR.SI, SI.ERROR, SI, y K.ESIMO.MAYOR, y con la herramienta Formato Condicional.

Lo primero que hacemos es darle nombre al rango B3:C14 para lo que seleccionamos dicho rango y hacemos clic en el cuadro de nombres (a la izquierda de la barra de fórmulas) y escribimos Valores y pulsamos Enter. A continuación preparamos el rango donde aparecerán los valores no repetidos. Habilitamos 24 filas ya que en nuestro ejemplo partimos de 2 columnas con 12 datos cada una y, por lo tanto, podríamos llegar a tener 24 valores no repetidos. Le añadimos a la derecha un número de orden que utilizaremos posteriormente:
Nos situamos en la celda G3 y escribimos la fórmula:
=SI(CONTAR.SI(valores;B3)=1;B3;"") y la copiamos hasta la celda G14. 
De esta manera lo que estamos haciendo es pedirle que cuente en el rango llamado Valores cuántas veces se repite cada uno de los valores de la columna B. Si se repite sólo una vez que lo escriba y si se repite más veces que no ponga nada ("").
Nos situamos ahora en G15 y escribimos una fórmula casi idéntica:
=SI(CONTAR.SI(valores;C3)=1;C3;"") y la copiamos hasta la celda G26. Estamos haciendo lo mismo que antes pero ahora con los valores de la columna C. El resultado será el siguiente:
Como puede comprobar, ya hemos generado la lista de valores no repetidos. Para ordenarla de mayor a menor nos situamos en la celda E3 y escribimos:
=SI.ERROR(K.ESIMO.MAYOR($G$3:$G$26;H3);"")  y copiamos hasta la celda E26.
Con la función K.ESIMO.MAYOR ordenamos los valores de mayor a menor. En los valores que se encuentre en blanco nos devolverá el error N#A y por eso utilizamos la función SI.ERROR para que cuando aparezca dicho error simplemente lo mantenga como celda en blanco (para ser más correctos celda con ""). Aplicamos formato condicional a aquellas celdas distintas de "" (puede consultar cómo hacerlo en el post Lista de valores Únicos): 
Finalmente vamos a destacar en nuestra entrada de datos aquellos valores que no están repetidos. Seleccionamos el rango B3:C14 y vamos a Formato Condicional. Seleccionamos la opción Utilice una fórmula que determine las celdas para aplicar formato  y escribimos la fórmula  =CONTAR.SI($B$3:$C$14;B3)=1  Pulsamos el botón formato y elegimos que colores u otros formatos deseamos utilizar y acabamos pulsando Aceptar. El resultado final es el que se puede observar a continuación: