Tenemos una serie de ventas para el año 2021, junto con unos productos, y, totales.
Queremos seleccionar un producto, y, que nos devuelva la fecha de venta, y, el total.
El modelo esta en formato de tabla, y, se llama ventas.
Con la función UNICOS, voy a crear una lista única de productos.
=UNICOS(Ventas[Producto])
En la celda E2, voy a crear una validación de datos, para seleccionar un producto, para ello, vamos a la pestaña de datos, dentro del grupo herramientas de datos, hacemos clic en validación de datos.
Desplegamos permitir, y, seleccionamos lista, y, en la ventana origen, seleccionamos los productos.
Aceptamos.
Seleccionamos un producto.
Obtengo una matriz desbordada con cada número de fila, desde la 2 hasta la 24.
Si la expresión anterior, la dividido entre los productos de la columna centro, que sea igual al seleccionado en la validación de datos.
=FILA(A2:A24)/(Ventas[Producto]=E2)
Obtengo una matriz desbordada con los números de fila donde hay coincidencia, y, error donde no hay coincidencia.
Tenemos que ordenar estos números de menor a mayor, ignorando los errores, por lo que voy a usar la función AGREGAR.
Después del signo igual, ponemos la función AGREGAR, y, abrimos un paréntesis.
En la ventana que se abre, seleccionamos la función K.ESIMO.MENOR, que es el número 15.
=AGREGAR(15
Punto y coma, seleccionamos omitir valores de error.
=AGREGAR(15;6
Como argumento matriz, dejamos la expresión anterior.
=AGREGAR(15;6;FILA(A2:A24)/(Ventas[Producto]=E2)
Ahora, viene el argumento K, es decir, la posición del numero menor a devolver, pero queremos hacerlo de forma matricial, es decir, que al pulsar enter, nos devuelva todos los registros que coincidan con el producto seleccionado.
La pregunta es ¿Cuántos elementos debe de devolver?
Si uso la función CONTAR en la operación, FILA(A2:A24)/(Ventas[Producto]=E2), obtendré el numero de valores que hay.
=CONTAR(FILA(A2:A24)/(Ventas[Producto]=E2))
Como tengo seleccionado aspiradora, me devuelve 6.
Si ahora uso la función SECUENCIA donde como argumento filas, uso la expresión anterior, obtendré una matriz desbordada empezando desde 1 hasta el número 6.
=SECUENCIA(CONTAR(FILA(A2:A24)/(Ventas[Producto]=E2)))
Pues esta expresión, va a ser el argumento K de la función K.ESIMO.MENOR.
=AGREGAR(15;6;FILA(A2:A24)/(Ventas[Producto]=E2);SECUENCIA(CONTAR(FILA(A2:A24)/(Ventas[Producto]=E2))))
Aceptamos, y, tenemos una matriz desbordada con los números de filas que corresponde al producto aspiradora ordenados de menor a mayor.
Ahora, usare la función INDICE, para rescatar los valores, para ello, después del signo igual, escribo la función INDICE, y, abro un paréntesis.
=INDICE(
Como argumento array, seleccionamos las columnas desde la A hasta la C.
=INDICE(A:C
Como argumento numero de fila, es la función AGREGAR.
=INDICE(A:C;AGREGAR(15;6;FILA(A2:A24)/(Ventas[Producto]=E2);SECUENCIA(CONTAR(FILA(A2:A24)/(Ventas[Producto]=E2))))
Queremos que nos devuelva la columna A, y, la columna C, es decir, fecha, y, total, por lo que vamos a usar una constante de matriz, separado cada numero por la barra invertida, porque estamos trabajando con columnas, cerramos paréntesis, y, aceptamos.
=INDICE(A:C;AGREGAR(15;6;FILA(A2:A24)/(Ventas[Producto]=E2);SECUENCIA(CONTAR(FILA(A2:A24)/(Ventas[Producto]=E2))));{1\3})
Ya tenemos las fechas de venta y los totales para el producto aspiradora.
El argumento K, de forma matricial, podemos de nuevo usar la función SECUENCIA, y, como argumento filas, uso el condicional SI para preguntar si el centro es igual al producto seleccionado que ponga el numero 1, en caso contrario, que ponga un texto en blanco, y, envuelvo el condicional SI en la función CONTAR.
=SECUENCIA(CONTAR(SI(Ventas[Producto]=E2;1;"")))
Quedaría:
=INDICE(A:C;AGREGAR(15;6;FILA(A2:A24)/(Ventas[Producto]=E2);SECUENCIA(CONTAR(SI(Ventas[Producto]=E2;1;""))));{1\3})
Vemos que obtenemos los mismos números de filas.
Ahora, queremos saber las cinco ventas mayores, de nuevo debemos de usar K.ESIMO.MAYOR, pero vamos a traernos cada numero mayor de la columna total, para ello, voy a contar con la función CONTAR, las cantidades numéricas de la columna total.
=CONTAR(Ventas[Total])
Como resultado, obtenemos 23, que son las filas que hay, esta función va a ser el argumento filas de la función SECUENCIA, donde crearemos una matriz desbordada desde el numero 1 hasta el número 23.
=SECUENCIA(CONTAR(Ventas[Total]))
Ahora, usamos la función K.ESIMO.MAYOR para obtener cada cantidad mayor, donde el argumento K, es la expresión anterior.
=K.ESIMO.MAYOR(Ventas[Total];SECUENCIA(CONTAR(Ventas[Total])))
Obtenemos una matriz desbordada con los totales de mayor a menor, pero solo queremos los cinco mayores, por lo que con el condicional SI, voy a preguntar que si el resultado de SECUENCIA es mayor a 5, que ponga un texto en blanco, en caso contrario, que ejecute la expresión anterior.
=SI(SECUENCIA(CONTAR(Ventas[Total]))>5;"";K.ESIMO.MAYOR(Ventas[Total];SECUENCIA(CONTAR(Ventas[Total]))))
Y ya tenemos los cinco totales mayores.
Lo ponemos en formato de moneda.
Vemos que usamos dos veces la función SECUENCIA, podemos usar la función LET, donde creamos una variable, y, almacenamos la función SECUENCIA, y, después la sustituimos por el nombre de la variable.
=LET(a;SECUENCIA(CONTAR(Ventas[Total]));SI(a>5;"";K.ESIMO.MAYOR(Ventas[Total];a)))
El resultado es el mismo, pero la sintaxis queda algo más corta.
Comments