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

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.

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:

Seleccionar Aleatoriamente un Valor de un Conjunto

"En una columna tengo un conjunto  de valores del tipo 1, 1, 2, 5, 6, 6, 8, 2, 8, 1, 5, etcétera, y quiero seleccionar de manera aleatoria uno de estos valores".

Para solucionar este problema utilizaremos las funciones DESREF, CONTAR y ALEATORIO.ENTRE .  Partimos del siguiente ejemplo:

Lo primero que hacemos es darle nombre al rango de valores. Seleccionamos desde B3 hasta B16, hacemos clic en el cuadro de nombres (a la izquierda de la barra de fórmulas) y escribimos el nombre: valores y pulsamos Enter. A continuación nos situamos en la celda D10 y escribimos la siguiente fórmula que explico a continuación:

=DESREF(B2;ALEATORIO.ENTRE(1;CONTAR(valores));) 

CONTAR(Valores) cuenta el número de valores que hay en dicho rango. En nuestro ejemplo el resultado será 14. Al anidar esta función dentro de la función ALEATORIO.ENTRE, estamos consiguiendo que genere un número aleatorio entre 1 y 14 (que es el número mínimo y máximo de filas de nuestro rango). El problema es que entre 1 y 14 hay valores que no se encuentran en nuestra lista, por ejemplo el 7, el 11, el 12, etcétera. Lo que hacemos ahora es utilizar el número aleatorio generado para ir a una posición de la lista que tenemos y obtener el número que se encuentre en dicha posición. Para ello utilizamos la función DESREF. Partimos de la celda B2 y, a partir de dicha celda, excel se posicionará en la fila del rango Valores que de manera aleatoria hemos generado con el resto de la fórmula ya explicada. Si, por ejemplo, el número generado es un 11, excel se desplazará 11 filas más abajo de la celda de partida (B2) y nos devolverá el valor de B13, esto es, 6. Pruebe a pulsar la tecla F9 y verá cómo se recalcula el número y siempre dentro de los existentes en la lista :