Contar Número de Dígitos

"En una columna tengo numerosos registros de 5, 6, 7 y 8 dígitos. Necesito realizar un resumen que me indique cuántos registros hay de cada número de dígitos".

La solución es muy sencilla utilizando una sola fórmula matricial con las funciones SUMA, SI y LARGO. Partimos de la siguiente entrada de datos:

Nos situamos en la celda E5 y escribimos la fórmula:

=SUMA(SI(LARGO($B$5:$B$26)=D5;1;0))  y pulsamos Ctrl+Shift+Enter. De esta manera convertimos la fórmula en matricial y quedará así:

{=SUMA(SI(LARGO($B$5:$B$26)=D5;1;0))}

La función LARGO contará el número de dígitos de cada una de las celdas comprendidas en el rango B5:B26. En el caso de que coincida con el número señalado en la celda D5 (en nuestro ejemplo es 5) entonces le sumará 1 (cero en caso contrario). Al copiar la fórmula hasta E8, la referencia D5 irá cambiando a D6, D7 y D8 y, en consecuencia, nos mostrará un resumen de la cantidad de cifras que tienen 5, 6, 7 y 8 dígitos respectivamente:

Si el número de registros es grande, es importante verificar que la suma del rango E5:E8 es igual al número de cifras existentes. En nuestro ejemplo lo podemos resolver escribiendo la siguiente fórmula en una celda (E10, por ejemplo):

=CONTAR(B5:B26)=SUMA(E5:E8)  El resultado será VERDADERO.

Obtención Aleatoria de Valores de un Rango Sin Repetición

"En tu artículo Selección Aleatoria de un Valor de un Rango (2) nos explicaste cómo seleccionar 1 valor de manera aleatoria entre los valores existentes en un determinado rango y entre los que se incluyen celdas en blanco. En mi caso, necesito seleccionar 6 valores de dicho rango y que, además, no se repita ninguno de los seleccionados".

Como suelo decir, no problemo! Partimos de la siguiente lista de valores (y utilizaremos 4 columnas de procesos para conseguir nuestro objetivo):

Empezamos por el Paso 1 generando una lista de valores únicos. Para ello nos situamos en la celda C3 y escribimos la fórmula (que copiamos posteriormente hasta la celda C28):

=SI(B3="";"";SI(CONTAR.SI($B$3:B3;B3)>1;"";B3))

De esta manera ya tenemos nuestro listado original "filtrado" con los valores únicos. A continuación, en la celda D3 escribimos un 1 y en la celda D4 un 2. Seleccionamos ambas, y copiamos hasta la celda D28 para generar un número de orden:

Nos situamos ahora en la celda F3 y procedemos con la siguiente fórmula (que copiamos hasta F28):

=K.ESIMO.MAYOR($C$3:$C$28;D3)

De esta manera ya tenemos ordenada la lista de valores únicos de mayor a menor, dejando las celdas en blanco con el mensaje de error #¡NUM! agrupadas al final de dicha lista:

Seleccionamos ahora el rango E3:E28 y escribimos la fórmula: =ALEATORIO()  y acabamos pulsando Ctrl + Enter:

Ya tenemos todos los ingredientes para poder proceder con la "formulita final". Para ello preparamos la zona de salida de datos en la columna I:

Nos situamos en la celda I4 y escribimos:

=BUSCARV(JERARQUIA(E3;$E$3:DESREF($E$2;CONTAR($F$3:$F$28);));$D$3:$F$28;3;FALSO)

CONTAR nos permite saber cuántas celdas contienen un número (y por tanto no son un error tipo #¡NUM!). Con la función JERARQUIA vamos a obtener el puesto relativo que ocupa E3 dentro del rango dinámico de, en nuestro ejemplo, E3:E17 (ya que el resto de valores aleatorios se corresponden con un valor de error tipo #¡NUM!). Una vez obtenido el puesto relativo, por ejemplo si obtenemos el 5, le pedimos que, por medio de la función BUSCARV, busque dentro del rango D3:F28 en la primera columna dicho valor y nos devuelva su correspondencia en la tercera columna, que en nuestro ejemplo se corresponde con el valor 57.

Si copiamos la fórmula de I4 hasta I9 ya tendremos nuestros seis valores aleatorios sin repetición y evitando las celdas en blanco:

Pulsando F9 obtendremos distintas combinaciones aleatorias 

Selección Aleatoria de un Valor de un Rango (2)

"Necesito seleccionar aleatoriamente un número de un rango determinado. He visto una solución en tu blog en el post Seleccionar Aleatoriamente un Valor de un Conjunto. El problema que me encuentro es que si el rango en cuestión contiene celdas en blanco, entonces, la solución que propones devuelve valores cero. Me gustaría saber si existe la forma de que evite dichas celdas en blanco y elija un valor entre aquellas que contienen número".

Para solucionar esta variación sobre el caso visto en el post Seleccionar Aleatoriamente un Valor de un Conjunto, vamos a generar una tabla auxiliar que nos permita "apartar" las celdas que se encuentran vacías para que no se puedan generar valores cero en el resultado. Partimos del siguiente ejemplo:

Queremos obtener aleatoriamente un valor de esta lista pero sin considerar las celdas en blanco. En la solución proporcionada en la primera versión de este problema, generamos un número aleatorio de posición para que excel me devuelva el valor existente en dicha celda. Es decir, si mi lista tiene 20 valores, genero un número aleatorio entre el 1 y el 20 y le pido a excel que vaya a la celda del número obtenido, por ejemplo la 4, y me devuelva el valor de dicha celda. Aplicando dicha solución a esta lista, nos podríamos encontrar con que en la cuarta celda de la lista la celda esté vacía (obtendríamos valor cero).

Lo primero que hacemos es seleccionar el rango B5:B18 y le damos el nombre de Valores. A continuación generamos una nueva lista auxiliar de dos columnas, cuya primera columna es una serie de número de orden de menor a mayor (en este caso del 1 al 14, ya que tenemos 14 datos):

Nos situamos en la celda G5 y escribimos la fórmula:

=K.ESIMO.MAYOR(valores;F5)  y copiamos hasta G18

De esta manera hemos conseguido generar una nueva lista ordenada de mayor a menor con los valores de nuestra lista original pero ahora ya no tenemos celdas en blanco por el medio del rango, ya que hemos conseguido que se "vayan al final" de nuestra nueva lista y que se muestren como error del tipo #¡NUM!

Esto nos permite ahora, aplicando la función CONTAR, calcular cuántas celdas de mi nueva lista contienen un valor númerico. Si calculamos  =CONTAR(G5:G18) nos devolverá el resultado 10, porque es el número de valores existentes en dicho rango de 14 celdas (las otras 4 contienen el error #¡NUM!). De esta manera ya sé que debo generar un número aleatorio entre 1 y 10. Por seguir trabajando con nombres de rango, seleccionamos G5:G18 y le creamos el nombre NewLista.

Nos situamos en la celda D10 y escribimos la fórmula definitiva:

=DESREF(G4;ALEATORIO.ENTRE(1;CONTAR(NewLista));)

Cada vez que pulsemos la tecla F9, excel recalculará un valor aleatorio y lo mostrará en la celda D10. Una fórmula alternativa en D10 sería utilizar la función INDICE:

=INDICE(NewLista;ALEATORIO.ENTRE(1;CONTAR(NewLista))) 

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.

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 :

Promedio Acumulado Dinámico

"Tengo una tabla de ventas mensuales desde 2010. Cada mes introduzco la cifra correspondiente a dicho mes. Me gustaría calcular el promedio de ventas acumulados hasta el último mes del que haya introducido un dato y que me lo compare con el promedio acumulado al mismo mes de los años anteriores".

Para solucionar este caso vamos a utilizar las funciones PROMEDIO, DESREF y CONTAR. Partimos del siguiente ejemplo:

Lo que queremos conseguir es que en el rango O4:O6 nos aparezcan los promedios de ventas acumulados hasta agosto (ya que es el último mes introducido) de 2012, 2011 y 2010. En el momento que introduzcamos el dato correspondiente a las ventas de septiembre de 2012 que recalcule automáticamente el promedio acumulado hasta dicho mes para los 3 ejercicios.

Lo primero que debemos resolver es cuántos meses hemos ingresado. Para ello utilizaremos la función CONTAR. Si nos colocamos en la celda O4 y escribimos la fórmula: =CONTAR(B4:M4) el resultado será 8, ya que son las celdas con contenido (los datos de ventas de los 8 meses).

Para calcular el Promedio excel necesita un rango. En nuestro caso el rango será desde la celda B4 y hasta la última celda que tiene contenido. Con la función CONTAR hemos obtenido el número de celdas que debemos promediar. Haciendo uso ahora de la función DESREF solucionamos el problema. La fórmula definitiva en la celda O4 es:

=PROMEDIO(B4:DESREF(A4;;CONTAR($B$4:$M$4)))

Para terminar copiamos la fórmula en las celdas O5 y O6 y problema resuelto:

Contar Registros Únicos

"Tengo más de 1.000 registros (números) en una columna. Muchos de ellos están repetidos y lo que me gustaría es poder contar, con fórmulas, cuántos son únicos".

Esta es la pregunta de mi querido hermano Santi a quién, evidentemente, le dedico este post (que generoso soy...). Hay diversas soluciones. Una de ellas sería haciendo uso de los Filtros Avanzados, como ya describí en mi post "Copiar Registros Únicos". También podemos generar una Tabla Dinámica y aplicar la función CONTAR para ver el número de registros únicos. Pero buscamos una solución con fórmula (no con herramientas). Para ello vamos a utilizar las siguientes funciones: Y, CONTAR, CONTAR.SI y Fórmulas Matriciales. Partimos del siguiente ejemplo:

Lo que vamos a hacer en el rango C3:C20 es comprobar si cada uno de los valores que hay en el rango B3:B20 es único o no. Para ello vamos a utilizar una fórmula matricial que, como ya sabéis, se caracteriza porque al finalizar pulsamos Ctrl + Shift + Enter. Nos situamos en la celda C3 y escribimos:

=Y(B3<>$B$2:B2) y acabamos pulsando Ctrl+Shift+Enter, lo que convierte esta fórmula en:

{=Y(B3<>$B$2:B2)}   Copiamos C3 hasta C20.

En esta fórmula hay varias cuestiones importantes:

1. Al poner dólares (referencias absolutas) en el primer término del rango B2:B2, quedando como $B$2:B2 conseguimos que cuando copiemos esta fórmula hacia abajo el rango se vaya ampliando, ya que el origen se mantiene fijo ($B$2) mientras que el segundo término se va ampliando a B3, B4, etcétera.

2. Con la fórmula matricial conseguimos comparar una celda contra todas las que le "quedan por encima". Por ejemplo, en la celda C8 la fórmula que aparecerá será:

{=Y(B8<>$B$2:B7)}

Esta fórmula está comprobando si la celda B8 es distinta de B2, B3, B4, B5, B6 y B7. En el caso que esto sea cierto excel devolverá el resultado de VERDADERO (FALSO en el caso contrario) como se observa a continuación:

Una vez hemos conseguido diferenciar los registros únicos la solución es muy sencilla. Preparamos la siguiente salida de datos:

Escribimos las siguientes fórmulas:

En la celda F3, para contar los registros totales  =CONTAR(B3:B20)

En la celda F4, para contar los registros únicos  =CONTAR.SI(C3:C20;VERDADERO)

Como se puede ver, existen 18 registros en total pero sólo 9 son únicos, a saber: 10, 20, 30, 40, 50, 60, 70, 80 y 90.

He propuesto esta solución porque me parece razonablemente sencilla de comprender y desarrollar. Pero se podría solucionar con una única fórmula como propone JLD en su blog.  La fórmula sería: 

{=SUMA(1/CONTAR.SI(B3:B20;B3:B20))}

Por aquello de no apropiarme de lo que no es mío, puedes encontrar la explicación a esta fórmula en:
http://jldexcelsp.blogspot.com.es/2007/08/contar-valores-nicos-en-un-rango-de.html

Uso de la Función SUBTOTALES

Descargar el Archivo

"Tengo una tabla con mucha información sobre la que aplico habitualmente filtros para realizar cálculos ¿Hay alguna manera de, por ejemplo, sumar sólo las celdas que aparecen una vez filtrada la tabla?"

Para realizar esta labor vamos a ver la función SUBTOTALES. Partimos del siguiente ejemplo:

Como se puede apreciar en la imagen, he aplicado autofiltros a la tabla. Para ello sólo tenemos que situarnos en cualquier celda de dicha tabla, debajo de los rótulos (nombres de campo), e ir al menú Datos/Filtro/Autofiltro. La función SUBTOTALES está especialmente pensada para realizar cálculos en tablas. Veamos cómo:

1. Disponemos la siguiente salida de datos encima de nuestra tabla:

2. En la celda C2 escribimos la fórmula:

=SUBTOTALES(9;C$9:C$28)

La función SUBTOTALES tiene dos argumentos, a saber:

Num_funcion: Es un número del 1 al 11 o del 101 al 111 que indica que función debe ser aplicada a la lista. Las correspondencias de dichos números de función son las que se muestran a continuación:

Ref1: Es la referencia o rango de los que queremos calcular el subtotal (podemos incluir hasta 29 referencias o rangos).

De esta manera, la fórmula =SUBTOTALES(9;C$9:C$28) calculará la SUMA (el 9 es el número de función correspondiente a la suma) del rango C9:C28. Pero con una peculiaridad (ya que en caso contrario podríamos realizar directamente el sumatorio) y es que si procedemos a filtrar la tabla realizará la suma de los valores visibles. Para comprobarlo procedemos a filtrar el campo denominado Variable. Vamos a ver sólo aquellos cargos que tienen como sueldo variable 20.000€:

Como puede apreciar en la imagen, hay seis cargos con este variable y la función SUBTOTALES de la celda C2 nos muestra el sumatorio del sueldo bruto pero precisamente de sólo esos seis cargos (y no del total de la tabla).

3. Copiamos la fórmula de C2 en el rango C3:C6 y procedemos a sustituir el número de función para que realice el cálculo correspondiente:

En C3 =SUBTOTALES(1;C$9:C$28)

En C4 =SUBTOTALES(4;C$9:C$28)

En C5 =SUBTOTALES(5;C$9:C$28)

En C6 =SUBTOTALES(2;C$9:C$28)

4. Como hemos utilizado referencias mixtas en el rango de la función podemos proceder a copiar el rango C2:C6 en D2:D6. El resultado es el que podemos ver en la siguiente imagen:

Puede comprobar que si cambiamos el filtro y le pedimos que, por ejemplo, nos muestre los cargos con un sueldo variable mayor o igual que 15.000€ y menor o igual que 25.000€, la función SUBTOTALES recalculará y mostrará los siguientes resultados:

Enumerar Listas con Celdas Ocultas y Condiciones

Descargar el Archivo

"Mil gracias Kiko. Aprovechando el ejemplo de tu anterior artículo, qué ocurre si hay una fila de separación entre cada cinco o seis filas y la numeración no debe ir en las celdas en donde la celda de la derecha no hay valor".

En esta ocasión vamos a solucionar el problema con un par de fórmulas y las funciones ESNUMERO y CONTAR.

Partimos del siguiente ejemplo:

Se trata de generar una enumeración a partir de la celda B3 sin que afecten las celdas ocultas y siempre que en la columna C haya valores.

1. Nos situamos en la celda E3 y escribimos la siguiente fórmula:

=SI(ESNUMERO(C3);CONTAR($C$3:C3);0)

La función ESNUMERO comprueba si la celda de referencia (en nuestro ejemplo C3) es un número o no. Los resultados posibles son VERDADERO ó FALSO. La función CONTAR cuenta el número de celdas que contienen números en el rango indicado.

2. Copiamos la fórmula de E3 en el rango E4:E31 (en nuestro ejemplo). Obtendremos el siguiente resultado:

3. Nos situamos en la celda B3 y escribimos la siguiente fórmula:

=SI(ESNUMERO(C3);E3;"")

4. Copiamos la fórmula de B3 en el rango de nuestro ejemplo B4:B31 y problema resuelto:

Evidentemente podemos ocultar la columna E (o podríamos haberla desarrollado en otra hoja) para que no afecte a la presentación de la información.