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.

Combinaciones con Aleatorio.Entre

"Necesito obtener 4 combinaciones aleatorias partiendo de 10 columnas con números del 1 al 10; del 11 al 20; del 21 al 30; etcétera (hasta el 100). De tal manera que de la primera columna obtenga un número aleatorio; de la segunda columna otro; y así hasta completar la combinación de los diez números".

Para solucionarlo utilizaremos la función ALEATORIO.ENTRE. Montamos la siguiente entrada de datos:

Lo que hemos hecho es introducir los límites inferiores y superiores de cada rango. A continuación nos situamos en la celda D7 y escribimos la fórmula:

=ALEATORIO.ENTRE(D$3;D$4)  y la copiamos hasta M3 y finalmente hasta M10:

Problema resuelto. Podemos pulsar la tecla F9 para generar nuevas combinaciones aleatorias.

Aleatorios Acotados con Promedio Objetivo

"Necesito generar números aleatorios entre valores determinados (por ejemplo, valores no inferiores a 498 y no superiores a 504) cuyo promedio sea un número concreto que se encuentre entre ambos límites (por ejemplo 500)".

Es un caso muy similar al resuelto en mi post Generar Aleatorios que Sumen una Cifra Objetiva, pero con algún ajuste que paso a comentar. Partimos del siguiente ejemplo y entrada de datos:

En la celda E7 escribimos la siguiente fórmula, que nos generará números aleatorios comprendidos entre los valores especificados en las celdas C4 y C5:

=ALEATORIO()*($C$4-$C$5)+$C$5  y copiamos hasta E21:

En la celda C7 escribimos la siguiente fórmula:

=((E7/SUMA($E$7:$E$21))*$C$2)*$C$3  y la copiamos hasta C21:

Podemos pulsar la tecla F9 para generar nuevos valores aleatorios y comprobar que la celda C23, que contiene la fórmula =PROMEDIO(C7:C21), mantiene el mismo valor que el objetivo indicado en la celda C2.

Desplegables sin Filas en Blanco

"Tengo una columna con registros y necesito generar una lista desplegable con dichos registros y en el mismo orden que aparecen en el listado original. El problema es que existen celdas en blanco y, en consecuencia, me aparecen dichos espacios en la lista desplegable".

Partimos del siguiente ejemplo:

En C2 queremos crear una lista desplegable con validación de datos, que presente los distintos códigos existentes en el rango E3:E20, en el mismo orden, pero sin los espacios en blanco. Lo primero que hacemos es generar un número de orden en la columna G (a partir de G3) del 1 al 18. En la celda H3 escribimos =E3 y copiamos hasta la celda H20, para generar un "espejo" de los códigos originales: 

Lo que vamos a hacer a continuación es "reordenar" estos códigos dándoles un nuevo número de orden. Para ello nos ponemos en la celda I3 y escribimos la fórmula:

=SI(H3=0;MAX($G$3:$G$20)+1;G3)  y la copiamos hasta I20.

Cunado el valor de la celda es 0 le estamos pidiendo que calcule el máximo de nuestro lista de orden (en nuestro ejemplo es 18) y le sume 1. De esta manera, todas las celdas en blanco de nuestra listado original de códigos, pasará a tener un nuevo número de orden que, en nuestro ejemplo, será de 19 (18+1). En el caso de que el valor de la celda no sea 0, le pedimos que escriba el valor número de orden que le corresponde a dicho código. Esto nos servirá para "reordenar" a continuación la información manteniendo el orden original pero evitando las celdas en blanco:

Ya sólo nos queda "recolocar" de menor a mayor estos nuevos números de orden y obtener el código correspondiente. Nos situamos en J3 y escribimos:

=SI.ERROR(BUSCARV(K.ESIMO.MENOR($I$3:$I$20;G3);$G$3:$H$20;2;FALSO);"X")

K.ESIMO.MENOR nos reordena de menor a mayor los números que hemos obtenido en el rango I3:I20. Al anidar esta función dentro de BUSCARV lo que hacemos es obtener el código relacionado con el número de orden. Finalmente, anidamos dentro de SI.ERROR para que aquellos valores que no se encuentran dentro de la tabla de búsqueda (en concreto el valor 19, que se corresponde con las celdas en blanco) no devuelvan el valor de error #N/A y se conviertan en X:

Ya sólo nos queda crear la fórmula en la validación de datos. Seleccionamos el rango J3:J20 y le creamos el nombre Listado. Desde C2 vamos a Validación de datos. En "Permitir" seleccionamos Lista y escribimos la siguiente fórmula:

=DESREF(J3;0;;CONTAR.SI(listado;"<>X"))

De esta manera obtendremos nuestra lista desplegable, manteniendo el mismo orden que el listado original, sin incluir ninguna fila en blanco:

Sumar Grupos de n Filas con DESREF

"Tengo un listado con muchos valores y tengo que realizar sumas de varias filas. Estas sumas pueden cambiar, es decir, en ocasiones necesito sumar de 2 en 2 valores; otras de 4 en 4 valores; etcétera. Me gustaría saber si se puede automatizar con fórmulas".

Se puede. Veamos cómo. Partimos del siguiente ejemplo, en el que vamos a manejar una lista de 30 valores:

En el rango B5:B34 generamos un número de orden del 1 al 30:

Habilitamos como entrada de datos el valor de "n", es decir, el valor de "cada cuántas filas" queremos sumar. Para ello disponemos una entrada de datos en las celdas B2 y C2:

Nos situamos en la celda D5 y escribimos la siguiente fórmula:

=SI(RESIDUO(B5;$C$2)=0;SUMA(DESREF(C5;;;-$C$2));"")

y la copiamos hasta el final de la lista de valores (en nuestro caso hasta la celda D34).

La primera parte de la fórmula comprueba si el número de orden es múltiplo del valor de n (el valor introducido en C2). Si no lo es, el residuo del cociente no será cero y, por lo tanto, no pondrá nada, ya que es lo que le hemos indicado en la segunda parte del condicional (que escriba ""). Si el número de orden es múltiplo del valor introducido en C2, entonces el residuo del cociente será cero y ejecutará la parte de la fórmula:

SUMA(DESREF(C5;;;-$C$2))  

En nuestro ejemplo, en la celda D7 excel se encontrará que el número de orden de esa fila es el 3 y que dicho número sí es múltiplo del introducido en C2, que es 3. Al estar 2 celdas más abajo que la fórmula original ésta se habrá transformado en (la parte que hace referencia a la suma) SUMA(DESREF(C7;;;-$C$2))  que significa que sume tres celdas hacia arriba desde C7 (incluyendo C7). Es hacia arriba porque al 4º argumento de la función DESREF, Alto, le hemos puesto signo negativo. Veamos el resultado con distintos valores en la celda C2 (valores de n):