19 may 20226 Min. de lectura

Ejemplo de FILTRAR y FRECUENCIA

Actualizado: 23 mar 2023

Vamos a trabajar con el modelo de CONTOSO.

El modelo de CONTOSO está compuesto por varias, en cada, hoja tenemos los datos de una categoría, como puede la hoja tienda, donde están todos los datos correspondientes a una tienda, tenemos otra hoja, que es clientes, donde tenemos todos los datos correspondientes a un cliente, y, tenemos algunas más, pero, tenemos una hoja llamada ventas, que es donde se van guardando todos los movimientos, o, ventas que se realizan, en esta hoja, se trabaja con los ID de cada hoja.

En este ejemplo, lo primero que queremos saber, es cual es la tienda de más ventas.

Tenemos una función matricial llamada FRECUENCIA, que nos dice las veces que aparece un valor dentro de un rango en una matriz vertical.

Esta función tiene dos argumentos, que es datos, que es la matriz donde vamos a buscar la frecuencia, o, repetición, y, grupos, que son los valores que queremos ver su frecuencia.

Si uso la siguiente expresión, donde en ambos argumentos, utilizo la columna IDTienda de la hoja ventas.

=FRECUENCIA(Tabla1[IDTienda];Tabla1[IDTienda])

Me va a devolver una matriz derramada vertical con las veces que se repite cada id de tienda.

Por ejemplo, me dice que el IDTienda 55, aparece 43 veces, que el IDTienda 186, aparece 49 veces, así con el resto de los ID.

Si después del signo igual uso la función MAX me va a devolver el valor máximo de las repeticiones.

=MAX(FRECUENCIA(Tabla1[IDTienda];Tabla1[IDTienda]))

Me devuelve el valor de 58.

Lo siguiente que voy a hacer es preguntar si el valor máximo devuelto por FRECUENCIA, donde usamos la función MAX para ello, es igual a uno de los valores devueltos también por FRECUENCIA, pues que me devuelva el idtienda, en caso contrario, que ponga un texto en blanco.

=SI(MAX(FRECUENCIA(Tabla1[IDTienda];Tabla1[IDTienda]))=FRECUENCIA(Tabla1[IDTienda];Tabla1[IDTienda]);Tabla1[IDTienda];"")

Como resultado, tenemos una matriz desbordada con blancos, y, el valor máximo, pues vamos a usar la función CONCAT, para eliminar los espacios en blanco.

=CONCAT(SI(MAX(FRECUENCIA(Tabla1[IDTienda];Tabla1[IDTienda]))=FRECUENCIA(Tabla1[IDTienda];Tabla1[IDTienda]);Tabla1[IDTienda];""))

Ya tengo el idtienda de más ventas, que es el ID 35.

Pero vemos que la función FRECUENCIA la usamos más de una vez, por lo que vamos a usar una función LET y almacenamos la función FRECUENCIA, en una variable.

=LET(a; FRECUENCIA(Tabla1[IDTienda];Tabla1[IDTienda]);CONCAT(SI(MAX(a)=a;Tabla1[IDTienda];"")))

Si nos fijamos el resultado devuelto está en formato de texto, vemos que se alinea a la izquierda, por lo que vamos a usar la función VALOR, para pasarlo a formato de número.

=VALOR(LET(a;FRECUENCIA(Tabla1[IDTienda];Tabla1[IDTienda]);CONCAT(SI(MAX(a)=a;Tabla1[IDTienda];""))))

También, podíamos hacerlo con la función CONTAR.SI.

Eta función nos cuenta las coincidencias dentro de un rango que cumpla con el criterio que especifiquemos.

Si escribo la siguiente expresión, voy a obtener los mismos resultados que tuvimos con la función FRECUENCIA, es decir, me va a decir las veces que aparece cada idtienda.

=CONTAR.SI(Tabla1[IDTienda];Tabla1[IDTienda])

Voy a sacar el valor máximo con la función MAX.

=MAX(CONTAR.SI(Tabla1[IDTienda];Tabla1[IDTienda]))

Ahora, voy a preguntar si el valor máximo es igual a uno de los valores devueltos por la función CONTAR.SI, que me devuelva el idtienda, en caso contrario, que devuelva un texto en blanco.

=SI(MAX(CONTAR.SI(Tabla1[IDTienda];Tabla1[IDTienda]))=CONTAR.SI(Tabla1[IDTienda];Tabla1[IDTienda]);Tabla1[IDTienda];"")

Nos quedamos con los valores únicos, para ello, usamos la función UNICOS.

=UNICOS(SI(MAX(CONTAR.SI(Tabla1[IDTienda];Tabla1[IDTienda]))=CONTAR.SI(Tabla1[IDTienda];Tabla1[IDTienda]);Tabla1[IDTienda];""))

Usamos la función CONCAT para quitar espacios.

=CONCAT(UNICOS(SI(MAX(CONTAR.SI(Tabla1[IDTienda];Tabla1[IDTienda]))=CONTAR.SI(Tabla1[IDTienda];Tabla1[IDTienda]);Tabla1[IDTienda];"")))

Lo siguiente es usar la función LET, y, almacenamos en una variable la función CONTAR.SI.

=LET(a;CONTAR.SI(Tabla1[IDTienda];Tabla1[IDTienda]);CONCAT(UNICOS(SI(MAX(a)=a;Tabla1[IDTienda];""))))

Vemos que tenemos el mismo idtienda.

Lo siguiente es extraer el nombre de la tienda, y, el tipo de tienda, donde vamos a usar la función BUSCARV, donde vamos a buscar el valor de la celda C3, es decir, el IDTienda que hemos obtenido anteriormente, lo buscamos en la hoja tienda, entre las columnas A, y, N, como argumento número de columnas, vamos a usar una constante de matriz, para que nos devuelva las columnas 4 y 3.

=BUSCARV(C3;Tienda!A:N;{4;3};FALSO)

Ya tenemos nuestras columnas.

También, lo podríamos hacer con la función FILTRAR, donde filtramos las columnas C, y, D, de la hoja tienda, y, como argumento include, o, incluir, comparamos la columna A de la hoja tienda, con el valor máximo de IDTienda obtenido anteriormente.

=FILTRAR(Tienda!C:D;Tienda!A:A=Hoja1!C3)

Transponemos.

=TRANSPONER(FILTRAR(Tienda!C:D;Tienda!A:A=Hoja1!C3))

Pero en este caso, tenemos primero el tipo de tienda, y, después el nombre de la tienda.

Lo siguiente que queremos saber es la cantidad máxima vendida.

Para ello, vamos a usar la función BYCOL, donde al final del artículo, dejare un enlace para ver el video sobre la función BYCOL.

El primer argumento es array, pues filtramos de nuevo la columna cantidad por el id de tienda que tenemos.

Ya sabemos que BYCOL, va a tomar cada columna del rango seleccionado de forma independiente.

=BYCOL(ORDENAR(FILTRAR(Tabla1[Cantidad];Tabla1[IDTienda]=C3));

El siguiente argumento es la función LAMBDA, donde definimos una variable, que almacenara la columna cantidad y como calculo, sacamos el valor máximo.

=BYCOL(ORDENAR(FILTRAR(Tabla1[Cantidad];Tabla1[IDTienda]=C4));LAMBDA(a;MAX(a)))

Ya lo tenemos.

Ahora vamos a extraer las fechas donde se han vendido dicha cantidad y el precio.

Vamos a usar la función FILTRAR, donde vamos a filtrar la columna fechapedido de la hoja ventas, hasta, la columna preciounitario, para el argumento include, o, incluir, vamos a comparar el IDTienda de la hoja ventas con el valor de C3, que es IDTienda, que hemos obtenido anteriormente, y, la columna cantidad de la hoja ventas, debe de ser igual a la cantidad máxima vendida obtenida anteriormente, como son dos condiciones, debemos de poner cada condición entre paréntesis, y, el símbolo de asterisco entre cada condición.

=ORDENAR(FILTRAR(Tabla1[[FechaPedido]:[PrecioUnitario]];((Tabla1[IDTienda]=C3)*(Tabla1[Cantidad]=C7))))

Aceptamos.

Tenemos una matriz desbordada, con tres columnas, que es la fecha de ventas, la cantidad, y, el precio.

Ahora, nos vamos a traer a que producto corresponde estas cantidades.

Para ello, vamos a filtrar la columna idproducto, por la fecha de pedido que tenemos, y, el idtienda que también tenemos.

Volvemos a usar la función FILTRAR, en este caso, vamos a filtrar la columna IDproducto de la hoja ventas, como argumento include, o, incluir, tenemos que volver a poner dos condiciones, la primera de ellas, vamos a comparar la columna fechapedido de la hoja ventas con la celda B11 de esta hoja, que es la primera fecha que hemos obtenido, y, después comparamos el Idtienda de la hoja ventas, con C3 de esta hoja, que es el Idtienda mayor que obtuvimos, igual que antes, ponemos las dos condiciones entre paren tisis, junto con el símbolo de asterisco.

=FILTRAR(Tabla1[IDProducto];(Tabla1[FechaPedido]=Hoja1!A11)*(Tabla1[IDTienda]=Hoja1!$C$3))

Con esto obtenemos el IDproducto.

Pero, queremos el nombre del producto, por lo que vamos a usar la función BUSCARX, el primer argumento, es valor buscado, pues el valor que vamos a buscar es el resultado de la función FILTRAR, como argumento matriz de búsqueda, lo vamos a buscar en la columna A de la hoja producto, y, como argumento matriz devuelta, es la columna B de la hoja producto.

=BUSCARX(FILTRAR(Tabla1[IDProducto];(Tabla1[FechaPedido]=Hoja1!A11)*(Tabla1[IDTienda]=Hoja1!C3));Producto!A:A;Producto!B:B)

Aceptamos, y, ya tenemos el nombre del producto.

Lo siguiente es arrastrar, pero antes de fijar algunas celdas, en este caso, solo debemos de fijas la celda C3, que es valor máximo de Idtienda, el cual, no debe de cambiar al arrastrar.

=BUSCARX(FILTRAR(Tabla1[IDProducto];(Tabla1[FechaPedido]=Hoja1!A11)*(Tabla1[IDTienda]=Hoja1!$C$3));Producto!A:A;Producto!B:B)

Aceptamos, y, ya tenemos los nombres de todos los productos.

También, lo podemos hacer con el condicional SI y CONCAT.

Usamos el condicional SI, para preguntar que si la columna fechapedido de la hoja ventas es igual al valor de la celda B11, que es la primera fecha, en caso, de que se cumpla la condición, vamos a volver a preguntar si el idtienda de la hoja ventas es igual al valor de la celda C3, que es el idtienda máximo, en ese caso que nos devuelva el IDproducto de la hoja ventas, en caso contrario, que devuelva un texto en blanco.

= SI(Tabla1[FechaPedido]=A11;SI(Tabla1[IDTienda]=C3;Tabla1[IDProducto];"");"")

Obtenemos una matriz desbordada, con celdas en blanco y la coincidencia, pues usamos la función CONCAT, para eliminar las celdas en blanco.

=CONCAT(SI(Tabla1[FechaPedido]=A11;SI(Tabla1[IDTienda]=$C$3;Tabla1[IDProducto];"");""))

Obtenemos el mismo IDproducto que en el caso anterior, pero, en formato de texto, por lo que vamos a usar la función VALOR, para pasarlo a formato de número.

=VALOR(CONCAT(SI(Tabla1[FechaPedido]=A11;SI(Tabla1[IDTienda]=$C$3;Tabla1[IDProducto];"");"")))

Pero, queremos el nombre, por lo que vamos a usar la función buscarx para encontrarlo.

=BUSCARX(VALOR(CONCAT(SI(Tabla1[FechaPedido]=A11;SI(Tabla1[IDTienda]=C3;Tabla1[IDProducto];"");"")));Producto!A2:A2518;Producto!B2:B2518)

Aceptamos.

Ahora, tenemos que arrastrar, pero, igual que antes debemos de fijar C3.

=BUSCARX(VALOR(CONCAT(SI(Tabla1[FechaPedido]=A11;SI(Tabla1[IDTienda]=$C$3;Tabla1[IDProducto];"");"")));Producto!A2:A2518;Producto!B2:B2518)

Y ya tenemos el nombre de los productos.

Vamos a calcular el promedio de los totales.

=PROMEDIO(valores)

Arrastramos.

Por último, vamos a crear un gráfico de columnas 2D junto con el promedio, para ello, seleccionamos los precios, vamos a la pestaña insertar, dentro del grupo gráficos, seleccionamos grafico de columnas agrupadas.

Quitamos título, y, líneas de cuadricula, para ello, teniendo seleccionado el grafico, hacemos clic en el símbolo (+) que aparece en la parte superior derecha del gráfico, y, desmarcamos líneas de cuadricula.

Vamos a agregar el promedio, que será una línea horizontal continua, porque todas tienen el mismo valor.

Para ello, vamos a la pestaña diseño de gráfico, y, hacemos clic en seleccionar datos.

Se abre la ventana seleccionar origen de datos, pues, hacemos clic en agregar.

Se abre la ventana modificar serie, pues, hacemos clic en el siguiente icono.

Seleccionamos los datos, que son los promedios, y, hacemos clic en el siguiente icono.

Aceptamos, y, vemos el promedio graficado, pero, en columnas, por lo que hacemos clic con el botón alternativo del ratón sobre una columna, y, seleccionamos cambiar tipo de grafico de series.