Vamos a ver un ejemplo, donde queremos crear un reporte o informe del número de totales mayores que le indiquemos, es lo que se conoce como filtro TOP N.
Tenemos el siguiente modelo.
Vamos a crear nombres de rangos para cada columna, para ello, estando en una celda dentro del rango, pulsamos CTRL mas asterisco, y, se seleccionará todo el rango.
Vamos a la pestaña formulas, dentro del grupo nombres definidos, hacemos clic en crear desde la selección.
Se abre la ventana crear nombres a partir de la selección, dejamos marcado solo fila superior, para que tome el nombre de cada encabezado como nombre de rango.
Aceptamos.
Si desplegamos el cuadro de nombres, veremos los nombres de rangos, que acabamos de crear.
Este informe lo vamos a hacer en una hoja nueva.
En una celda vamos a poner el numero de totales mayores que queremos ver.
En principio, nos vamos a traer todas las columnas.
Para este ejemplo, vamos a usar la función dinámica FILTRAR.
En la celda C5 de esta hoja nueva, escribimos el signo igual, seguido de la función FILTRAR, y, abrimos un paréntesis.
=FILTRAR(datos
Punto y coma, para el argumento array, usamos el nombre de rango datos, que son todos los datos.
=FILTRAR(datos
Punto y coma, para el argumento include, usamos el nombre de rango total, y, lo vamos a igualar, usando la función K.ESIMO.MAYOR, para que nos devuelva el numero mayor de la columna total.
=FILTRAR(datos; Total=K.ESIMO.MAYOR(Total;C1))
Aceptamos, y, vemos que solo tenemos una línea de datos, esto es porque le hemos dicho que nos devuelva el valor que sea igual al valor máximo.
Vamos a cambiar el criterio, y, ponemos mayor o igual.
=FILTRAR(datos; Total>=K.ESIMO.MAYOR(Total;C1))
Ahora, tenemos el numero de totales indicado en la celda.
Lo siguiente que vamos a hacer es ordenar por la columna total de manera descendente, para verlo desde el valor menor al mayor.
=ORDENAR(FILTRAR(datos;Total>=K.ESIMO.MAYOR(Total;C1));5;1)
Ahora, a partir de la celda B2, vamos a crear un índice, donde usaremos la función SECUENCIA, formada por el valor de la celda C1.
=SECUENCIA(C1)
¿Qué ocurre si el usuario introduce un numero mayor al numero de filas del modelo?
El modelo tiene 22 filas, si, por ejemplo, escribo el número 23.
Nos aparece una secuencia de 23 números, empezando desde el numero 1, pero, la matriz desbordada, nos devuelve un error.
Con la función CONTARA puedo saber el numero de filas que tiene el modelo, por ejemplo, si en una celda quiero contar el número de celdas de la columna total, escribo la siguiente sintaxis:
=CONTARA(Total)
Me devuelve 21, porque no se cuenta el encabezado.
Quiere decir que el valor de filas a mostrar que introduzcamos no debe de ser superior a este valor.
Pues, vamos a usar el condicional SI para restringir este valor, vamos a preguntar que si el valor de la celda C1, es mayor, al resultado de CONTARA, que me devuelva la palabra “Error”, en caso contrario, me haga la secuencia.
=SI(C1>CONTARA(Total);"Error”; SECUENCIA(C1))
Aceptamos, y, volvemos a poner, por ejemplo, el número 23, y, vemos que ahora aparece la palabra “Error”, y, no la secuencia.
Pero, vemos que la columna fecha, también, nos devuelve un error, pues vamos a usar la función SI.ERROR, donde le decimos que si la expresión devuelve un error, que ponga un texto en blanco.
=SI.ERROR(ORDENAR(FILTRAR(datos;Total>=K.ESIMO.MAYOR(Total;C1));5;1);"")
Ahora, quiero saber qué porcentaje corresponde cada cantidad de total respecto al total general.
Para ello, he de dividir la primera cantidad entra el total, y, así con las demás cantidades.
Pero, tiene que ser dinámica, es decir, el porcentaje correspondiente con los valores que se muestren.
Lo primero que debemos de hacer es filtrar la columna de total por los valores máximos que hemos indicado, pero solo la columna de total.
=FILTRAR(Total; Total>=K.ESIMO.MAYOR(Total;C1))
Tenemos una copia de la columna total, pero no ordenada.
Pues, vamos a ordenar, con los argumentos predeterminados.
=ORDENAR(FILTRAR(Total; Total>=K.ESIMO.MAYOR(Total;C1)))
Vemos que tenemos los mismos totales.
Ahora, cada valor de esta matriz desbordada, lo debo de dividir entre el total de estos números, por lo que debemos de dividir la expresión anterior.
=ORDENAR(FILTRAR(Total;Total>=K.ESIMO.MAYOR(Total;C1)))/SUMA(ORDENAR(FILTRAR(Total;Total>=K.ESIMO.MAYOR(Total;C1))))
Lo ponemos en formato de porcentaje, y, ya tenemos los porcentajes, si sumamos todos los porcentajes, veremos que el total es el 100%, esto quiere decir que esta bien calculado, además, de ser dinámico.
Pero vemos que usamos dos veces la misma expresión, y, la sintaxis se hace un poco larga.
Podemos usar la función LET, y, almacenar en una variable la expresión, y, usarla las veces que necesitemos.
=LET(A;ORDENAR(FILTRAR(Total;Total>=K.ESIMO.MAYOR(Total;C1)));A/SUMA(A))
Aceptamos, y, seguimos teniendo el mismo resultado, pero, con una sintaxis más corta.
Damos por terminado nuestro ejemplo.
Comments