Para el siguiente ejemplo, donde seguimos trabajando con el modelo que suelo usar habitualmente.
Tenemos una celda con un margen.
Quiere decir que vamos a hacer saltos de 300.
En una celda voy a extraer el valor máximo de la columna total, para ello, uso la función MAX.
=MAX(Table1[Total])
Tenemos el valor máximo de la columna total.
Voy a establecer un margen superior, para ello, voy a usar la función MULTIPLO.SUPERIOR, donde como argumento número es la celda G2, es decir, el valor máximo, y, como argumento cifra significativa, es la celda H2, que es el margen.
=MULTIPLO.SUPERIOR(G2;H2)
Obtenemos 1200.
Si vamos sumando 300 al margen, nos devuelve 600, si sumamos 300 a 600, nos devuelve 900, y, si sumamos 300 a 900, nos devuelve 1200, quiere decir que son cuatro pasos, queremos saber cuantas cantidades hay menores o igual a 300, 600, 900, y, 1200, si el margen es de 300.
Para que nos devuelva cuatro, voy a dividir el valor de la celda G2, entre el valor de la celda H2.
=I2/H2
Obtenemos 4, lo siguiente es crear un cuadro con los periodos, que, para este caso, serian, 300, 600, 900, y, 1200, para ello, vamos a hacerlo de dos formas, la primera de ellas va a ser multiplicando el valor de la celda H2 (margen), y, lo multiplicamos por el resultado de la función SECUENCIA, donde solo usamos el argumento filas, que es el valor de la celda J2, que son los periodos.
=H2*SECUENCIA(J2)
Tenemos nuestro cuadro con los cuatro periodos.
Si cambiamos el valor de margen, veremos como tendremos más o menos periodos.
Otra forma de hacerlo es con la función SECUENCIA, donde el argumento filas, es la celda J2, que son los periodos, el argumento columnas, lo saltamos, como argumento inicio, es la celda H2, que es el margen, y, como argumento paso, sigue siendo la celda H2, es decir, comienza en 300, y, da cuatro saltos, cada uno de ellos, de 300.
=SECUENCIA(J2;;H2;H2)
Tenemos los mismos resultados que hemos obtenidos anteriormente.
Lo siguiente es obtener el número de cantidades que tenemos en cada periodo, lo vamos a hacer con la función FRECUENCIA, pero si no disponemos de dicha función, podemos hacerlo con la función CONTAR.SI.CONJUNTO, debemos de usarla dos veces, la primera de ellas, debemos de preguntar si la columna total es menor o igual a 300, la sintaxis seria:
=CONTAR.SI.CONJUNTO(Table13[Total];"<="&H5)
Nos devuelve 90, es decir, hay 90 filas que son menores a 300.
La siguiente pregunta es que la columna total, sea mayor a 300, y, menor a 600, es decir, rescatar los valores que sean menores a 600, la sintaxis seria:
=CONTAR.SI.CONJUNTO(Table13[Total];">"&H5;Table13[Total];"<"&H6)
Arrastramos, y, tenemos el número de cantidades para cada periodo.
El problema de usar la función CONTAR.SI.CONJUNTO, es que no se actualiza de forma automática, es decir, si cambiamos el margen a 200, el numero de periodos aumenta en 1, pero no se ve reflejado hasta que no arrastremos.
Ahora, lo vamos a realizar con la función FRECUENCIA, esta función calcula las veces que se repite un valor dentro de un rango, el primer argumento es datos, que son los valores que queremos contar, pues es la columna de total.
=FRECUENCIA(Table13[Total]
Punto y coma, el siguiente argumento es grupos, que son los valores por los que queremos agrupar, seleccionamos la celda H5, junto con el operador de rango derramado (#), de esta manera, si aumentan o disminuyen los datos, serán seleccionamos de forma automática.
=FRECUENCIA(Table13[Total];H5#)
Ambos argumentos son obligatorios.
Cerramos paréntesis, y, aceptamos.
Vemos que tenemos los mismos resultados obtenidos anteriormente, pero la función FRECUENCIA, si se actualiza de forma automática.
Lo que ocurre es que nos devuelve una celda más con el valor cero.
Dicha celda no la necesitamos, podemos usar la función FILTRAR, donde como argumento array, es la función FRECUENCIA, y, como argumento Include, vuelve a ser la función FRECUENCIA, siempre que sea mayor a cero.
=FILTRAR(FRECUENCIA(Table13[Total];H5#);FRECUENCIA(Table13[Total];H5#)>0)
Aceptamos, y, vemos que ya no aparece el valor cero.
Como usamos dos veces la función FRECUENCIA, voy a usar la función LET, donde creo una variable, y, almaceno la función FRECUENCIA, y, después, la sustituyo.
=LET(a;FRECUENCIA(Table13[Total];H5#);FILTRAR(a;a>0))
También, podría hacerlo con la función INDICE, donde como argumento matriz, es la función FRECUENCIA, como argumento filas, usamos la función SECUENCIA, y, como argumento filas, seleccionamos los periodos.
=INDICE(FRECUENCIA(Table13[Total];H5#);SECUENCIA(J2))
Obtenemos los mismos resultados.
Ahora, vamos a crear un cuadro al lado de las cantidades que se repiten, para ver mas claro que significa cada cantidad, por ejemplo, la primera cantidad 90, quiere decir que hay 90 cantidades menores o igual, en este caso, de 300, la cantidad 93, quiere decir que hay menores a 300, y, menores o igual a 600, igual para el resto de cantidades, pues, es lo que queremos que aparezca al lado de cada cantidad.
Para ello, en la celda I13, ponemos entre comillas dobles, “<=”, y, lo concatenamos con el valor de la celda G5, junto con el operador de rango derramado (#).
Y ya lo tenemos.
Comentários