Largos tiempos de cálculo con XLOOKUP frente a INDEX-MIN-COLUMN.
Estoy utilizando esta fórmula =IF(B24="","",IFERROR(INDEX(Sheet3!$C$3:$EE$3,,MIN(IF(Sheet3!$C$4:$EE$23=(Sheet2!C24&$K$18),COLUMN(Sheet3!$C:$EE)))-2),"NF")) para devolver un valor de celda en la fila superior de una matriz, en este caso una fecha.
El criterio de búsqueda es una combinación de un número de proyecto único y un código alfanumérico de estado de 2 dígitos para el proyecto. La matriz consta de 23 filas donde se encuentran combinaciones de los números únicos, cada una con diferentes códigos de estado.
Entonces, básicamente, estoy creando un panel de estado de proyecto FILTRADO que devuelve fechas vinculadas al estado de proyecto relevante.
El código anterior está inspirado en ( LINK ) que utiliza un diseño muy similar, pero utiliza suburbios de ciudad vinculados a códigos postales en lugar de números de proyecto y códigos de estado. La fórmula funciona bien (aunque no se ingresa como una fórmula de matriz), pero no tengo una sola fórmula en la hoja, tengo 3,300 ocurrencias de esta fórmula.
El problema surge cuando el usuario cambia el FILTRO: Excel recalcula todo el panel y esto puede llevar de 2 a 5 minutos. Puedes presionar el botón de escape para cancelar el cálculo después de configurar el filtro, pero Excel comienza a calcular de nuevo después de unos segundos. Después de eso, la respuesta de Excel es lenta y casi inutilizable. Sí, nuestro hardware es bastante débil…
También intenté XLOOKUP, pero no puedo establecer el “lookup_array” como una matriz (Sheet3!$C$4:$EE$23) porque no coincide con el “return-array” (Sheet3!$C$3:$EE$3). Concatenar las matrices de búsqueda con & funciona, pero luego tendrías que hacerlo para las 23 filas y, nuevamente, multiplicarlo por 3,300.
Pensé en crear una función definida por el usuario (UDF), pero la función aún se llamaría cada vez que Excel recalcula después de filtrar… 3,300 llamadas…
¿Alguna idea de cómo hacer que la versión INDEX se ejecute más rápido o hacer que XLOOKUP acepte el lookup_array como Sheet3!$C$4:$EE$23 con la esperanza de que se ejecute más rápido?
¡Gracias!
davy.ai
Parece que el problema con la fórmula actual es el hecho de que se usa 3,300 veces y hace que Excel recalcule todo el panel de control cada vez que se cambia el filtro. Una solución potencial podría ser utilizar una tabla dinámica en lugar de depender únicamente de fórmulas para mostrar el panel de estado del proyecto. Las tablas dinámicas están diseñadas para manejar grandes cantidades de datos y se pueden filtrar fácilmente sin causar una desaceleración significativa en el rendimiento de Excel. Además, si aún se necesita una UDF, puede ser útil usar VBA para crear una función personalizada que se pueda llamar dentro de la tabla dinámica en lugar de utilizar una fórmula en cada celda individual. Esto puede reducir potencialmente la cantidad de veces que se llama a la función y mejorar el rendimiento. Otra opción puede ser optimizar el hardware que se utiliza para ejecutar Excel. Actualizar a un procesador más potente y agregar más memoria RAM puede mejorar significativamente el rendimiento de Excel al trabajar con conjuntos de datos grandes.