Contexto
Necesito tomar una serie de actividades con las fechas de inicio y finalización deseadas y verificar si son razonables dada la restricción de que solo 3 (por ejemplo) se pueden realizar simultáneamente. Dado que nunca se pueden realizar más de 3 actividades, dentro de mi agenda debo permitir que se retrasen las actividades. Si bien sé que esto se puede hacer en MS Project, necesito hacerlo específicamente en Excel.
Problema
Logré formular esto en Excel y funciona absolutamente según lo previsto para una pequeña cantidad de tareas durante un período pequeño (hasta aproximadamente 30,000 celdas). Sin embargo, necesito aplicarlo a muchas más actividades, durante una duración mucho más larga (potencialmente hasta 500.000 células en total, ¡lo que actualmente lleva unos 10 minutos!).¿Puedes recomendar alguna mejora a mi fórmula?Ya me he optimizado mucho e investigué algunas otras opciones (ver a continuación):
Mi método actual
Captura de pantalla del método actual con perfil indicativo
Tenga en cuenta que mi método se basa en que a las actividades se les asigne un número de prioridad y se ordenen de acuerdo con este (esta será simplemente la duración de "inicio" más temprana).
Explicación de la fórmula para generar el perfil/diagrama de Gantt:
- Si es la primera línea ($B9 = 1) del diagrama de Gantt, no lo pienses mucho ya que no hay otras actividades por delante: simplemente pon 1 en cada una de las celdas si estás dentro de las fechas deseadas (IF(AND (AO$3>=$C9,AO$3<=$D9),1,"")).
- Para otras filas... La fórmula verifica si (A) está dentro del rango de fechas correcto (B) si ya se están realizando 3 actividades por encima de usted (C) si ya ha puesto suficientes 1 en esta actividad.
(Estoy usando tablas de Excel donde quiero que la fórmula sea consistente en cada fila, por eso tengo el primer y segundo punto en la misma fórmula)
Intentos de mejora actuales:
- En lugar de sumar todo el rango, intenté configurar una SUM(OFFSET(...)) para que fuera necesario sumar una menor cantidad de celdas. Sin embargo, haber intentado esto solo significó que a la fórmula le faltaban algunas actividades y, por lo tanto, llevó a que algunas actividades comenzaran a pesar de la restricción o duraran más de la duración prevista.
- En lugar de hacerlo día a día, intenté hacerlo semanalmente/mensualmente. Si bien ciertamente aceleró el cálculo, este nivel de granularidad no daba resultados precisos, por lo que debo continuar diariamente.
TL:DR:
¿Hay alguna forma de optimizar aún más =IF($B10=1, IF(AND(AO$3>=$C10,AO$3<=$D10),1,""), IF(AND(AO$3>=$C10)? ,SUM(AO$4:AO9)<$D$13,SUM($F10:AN10)<$E10),1,"")). Gracias.
Respuesta1
Tiene una gran cantidad de celdas con las que trabajar, usaría columnas auxiliares para calcular las fechas de inicio y finalización reales y hacer que el cálculo en el lado derecho sea mucho más simple.
- Fórmula para el inicio real:
=IF(COUNTIF($F$1:F1,">="&B2)<$C$11,B2,LARGE($F$1:F1,$C$11)+1) - fórmula para el final real:
=E2+D2-1
Ahora en la parte del calendario puedes usar una fórmula como esta:=IF(AND(X$1>=$E2,X$1<=$F2),1,"")
Actualizar
Cómo funciona la fórmula:
COUNTIF($F$1:F1,">="&B2)- cuenta las actividades anteriores que terminan después del inicio deseadoCOUNTIF(... )<$C$11- lo compara con la restricción=IF(.... ,B2,...- mantener el inicio deseado de lo posible=IF(... ,... ,LARGE($F$1:F1,$C$11)+1)- es necesario retrasar, verifique qué actividad termina antes de las que limitan su inicio, +1 para comenzar al día siguiente
Respuesta2
Ganaría algo de tiempo de CPU eliminando el caso aislado de la fila 1, ya que no se requiere la comparación constante del 99,9% de las filas. Simplemente inserte una fila en blanco justo debajo de los encabezados y deseche el primer IF:
=IF(AND(AO$3>=$C10,SUM(AO$4:AO9)<$D$13,SUM($F10:AN10)<$E10),1,"")
Además, parecería que AO$3<=$D10ya SUM($F10:AN10)<$E10está haciendo la misma comparación de fechas de finalización. También podría seguir con la comparación única rápida sin suma:
=IF(AND(AO$3>=$C10,SUM(AO$4:AO9)<$D$13,AO$3<=$D10,1,"")
A continuación, defina una "restricción" de rango con nombre como una constante (se refiere a =3) para evitar una búsqueda de celda:
=IF(AND(AO$3>=$C10,SUM(AO$4:AO9)<constraint,AO$3<=$D10,1,"")
A continuación, opte por IF anidados en lugar de AND, de modo que Excel no necesite calcular la "SUMA" intensiva de la CPU cada vez, cuando las condiciones de la ventana ya sean FALSAS. Organice los IF en orden de mayor probabilidad de ser falsos:
=IF(AO$3<=$D10,IF(AO$3>=$C10,IF(SUM(AO$4:AO9)<constraint,1,""),""),"")
Y finalmente reemplace SUM con COUNT
=IF(AO$3<=$D10,IF(AO$3>=$C10,IF(COUNT(AO$4:AO9)<constraint,1,""),""),"")
Porque la apariencia también importa:
Como ya comentamos, el formato condicional está fuera de discusión. Sin embargo, puedes usar caracteres ascii como este "█" en lugar de un 1:
=IF(AO$3<=$D10,IF(AO$3>=$C10,IF(ROW(AO9)-ROW(AO$4)+1-COUNTBLANK(AO$4:AO9)<constraint,1,"█"),""),"")
Pero esto se produce a expensas de un recuento más complicado del número de "█" anteriores. Para una alternativa más rápida, use el número 4 formateado en fuente Webdings (se ve casi así ►):
=IF(AO$3<=$D10,IF(AO$3>=$C10,IF(COUNT(AO$4:AO9)<constraint,4,""),""),"")
Puede utilizar cualquier número del 0 al 9 y cambiar la fuente para obtener un efecto de "gráfico de barras" más visual. Tiene que ser un número; de lo contrario, CONTAR no funcionará y deberá recurrir a la fórmula más lenta de tipo "█", CONTAR EN BLANCO.
Respuesta3
Sólo un par de cosas simples deberían generar un gran aumento en la velocidad. El principal es reordenar la forma de analizar una celda.
Excel calcula, en la medida de lo posible, de izquierda a derecha, fragmento por fragmento encontrado, desviándose solo cuando la lógica de la fórmula lo obliga a hacerlo. Una desviación es que si realiza una prueba, pasa directamente al resultado consiguiente, SI eso es posible.
Entonces, si tiene una IF()prueba como primera parte de una fórmula larga y el resultado de la prueba es simple, nunca evalúa la otra bifurcación.You have such a thing that would cut away almost all of the calculating you are doing.
Ésa es la AO$4:AO4prueba contra la restricción. Si falla ese cálculo, obtendrá resultados inmediatos, no más procesamiento. Simplemente termina para esa celda. Así que reordena tu fórmula para probarla primero. De esa manera, solo tres filas ven más cálculos que esta prueba en lugar de cada fila.
(Hablando de "primero": como Mobusdice, deje de hacer el cálculo de la "primera línea" en cada celda. Utilice el rango que mostré arriba AO$4:AO4(obviamente diferente para cada columna) y trate la primera fila como lo haría con cualquier otra fila. Luego nunca forma parte de los cálculos en filas distintas a sí misma. El rango se ancla y se expande tal como lo hace ahora, y simplemente trata la "Fila 1" como cualquier otra fila. La optimización por su singularidad NO ayuda en este problema. )
A continuación, haga el cálculo de SUM()o COUNT()para una fila probando la finalización existente. Parece que tendría suficientes filas para que casi todas existieran en un estado completo, por lo que si prueba primero si la fecha es de interés, tendrá que verificarlas de todos modos. Hágalo primero y reduzca las fechas para probar a muy pocas. Nuevamente, ese cálculo se ejecutará de todos modos, moverlo no ganará ni perderá frente a él, pero hacerlo primero elimina muchos cálculos diferentes.
En las otras respuestas y/o comentarios se menciona la idea de un rango con nombre. Me refiero a los rangos con nombre y las columnas auxiliares (incluso las páginas auxiliares) también, pero su restricción existente en una celda en lugar de un rango con nombre simplemente no importa... si la mueve a un lugar donde no lo hará. Cambia de posición todo el tiempo. Excel crea un esquema de cálculo y, después del primer cálculo, sólo activa el cálculo para las bifurcaciones de aquellas en las que algo cambia. Coloque la restricción en una ubicación que no cambie y no la edite... Excel nunca necesitará perder tiempo recalculando, etc. Colóquelo debajo de la región de datos y haga que cambie de fila cada vez que inserte una nueva tarea y lo haría. Ponerlo en un lugar donde no se produzca ese tipo de cambio continuo y no sea necesario volver a calcularlo. Un rango con nombre es una manera fácil y maravillosa de hacerlo, pero puede simplemente reorganizar ese aspecto de su hoja de cálculo si lo prefiere y, si desea cambiarlo de vez en cuando, o más, es posible que desee que un usuario lo haga. entonces. Sin embargo, en este caso, su fórmula cambiaría por completo los resultados de su hoja de cálculo si cambia la restricción y no hay forma de que desee eso, entonces, ¿por qué mantenerla en la hoja en lugar de en un rango con nombre? Y los rangos con nombre tienen otras ventajas, pero no son importantes para este tema. Pero si lo hace, colóquelo en algún lugar donde su dirección no siga cambiando (en gran medida, "cambio es cambio" independientemente de lo que haya cambiado) y no importará el problema de la velocidad.
Nunca he leído algo que aborde directamente la aritmética SUM()frente a las operaciones con cuerdas como COUNT()la velocidad. Parece que otros han leído eso y que las operaciones de cadena son más rápidas. En ese caso, cambia todo usando SUM()para usar. COUNT()COUNTA()Habría adivinado que la aritmética ganaría, pero ahí lo tienes.
Sí, como dice su comentario, solo importa la fecha en que comenzó la tarea, no la fecha de finalización esperada. Así que lo único importante es comprobar eso. Esto se debe a que su problema no incluye renunciar a una tarea si es demasiado antigua. Entonces, pase lo que pase, si no se ha completado y es la tarea 1, 2 o 3, se realizará sin preocuparse por la fecha de finalización esperada.
Sin embargo, el cambio más grande de todos los que puede hacer, eclipsando todo lo anterior (incluidas otras respuestas) en efecto, es...STOP recalculating every line over and over again.
Una vez que se completa una tarea, NUNCA se volverá a abordar en la lógica presentada. Entonceswhy keep addressing it again and again???
Periódicamente (se sugieren semanalmente o mensualmente) copie todas las líneas completadas y pegue sus valores. Así que terminaron y terminaron, para siempre. Quizás le queden 100 filas con fórmulas en lugar de las 15.000 que ha acumulado. (Al hacer 3 tareas a la vez, claramente no puedes tener 2000 tareas alineadas, así que elegí 100, pero incluso 20 parece más plausible). Por lo tanto, esos cálculos NUNCA, NUNCA, NUNCA se volverán a hacer. Basta pensar en la mejora de velocidad allí.
Es una locura en comparación con la situación actual.
Entonces también tendría ventajas interesantes. La más importante sería que la restricción podría cambiar si la capacidad aumentara o disminuyera. Como se mencionó anteriormente, actualmente destruiría por completo los resultados pasados si se cambiara. Pero con la transición a los valores, el material antiguo no se vería afectado. ¿Quieres cambiarlo? Copie y pegue valores para todas las filas sobre el punto de cambio, cámbielo y avance hasta cambiarlo nuevamente.
Otra sería que la carga de cálculo más pequeña le permitiría usar cosas como el direccionamiento dinámico de una manera más generalizada, aunque ahora tendrían un significado mucho menor.
Por cierto, con las columnas auxiliares que se usan para acelerar las cosas, la idea es diferente a la habitual "realizar una tarea difícil de forma aislada" para facilitar una fórmula en otra columna. En este caso, el punto sería aislar las partes que no cambian de la fórmula principal en la columna auxiliar para que Excel las calcule una vez y solo si de alguna manera cambiaron. Entonces, en general, si tiene 11 parámetros en una fórmula, pero 7 nunca o rara vez cambian, actualice su fórmula para que sus efectos ocurran solo en las columnas auxiliares y la fórmula restante lea sus resultados como un solo parámetro. Eso... puede suponer mucha renovación, implicando una visión muy diferente de cómo procesar los parámetros, pero generalmente puedes hacerlo y luego esas partes nunca se vuelven a calcular, por lo que todo funciona mucho más rápido. Sin embargo, a veces no puedes {al igual que a veces IFERROR()simplemente no funciona y debes usar `IF(ISERROR())} para que lo que puedas hacer solo gane un poco. Pero en más de 500.000 fórmulas, un poco todavía ayuda mucho.
En cuanto al aspecto del diagrama de Gantt, imagino que ahí es donde el formato condicional ("CF") levanta su cabeza. (No vi ninguna referencia a eso, pero uno supone que un diagrama de Gantt con 1 no es tan probable como usar CF para hacer líneas bonitas. Como lo mencionó Mobus, hay mejores maneras que usar CF. Sumado a lo Mobusdicho, uno Puede elegir cualquier carácter de "bloque" adecuado y, si es necesario, simplemente usar la función "Rellenar" en Alineación|Horizontal para que llene una celda, por lo que hacer coincidir el tamaño y la forma sería menos importante, aunque aún tendría que ajustar la fuente para que coincida. el personaje a la altura de la fila REPT().
Pero para hacerlo "cantar" (especialmente porque usó lo anterior para reducir en gran medida los cálculos involucrados), podría presentar el resultado a través de la TEXT()función y crear su cadena de formato para incluir el color de fuente. Eso le permitiría especificar diferentes colores para la línea de cada fila, diferentes de la fila de arriba y de abajo, de modo que los colores nunca se coloquen uno al lado del otro y así mejorar la legibilidad. Tenga en cuenta que esa parte está en la fórmula del lado de la celda, no en CF con todos sus otros problemas, no solo sus efectos de velocidad.
Cambiar a Access o programas similares, además de no estar necesariamente disponibles para usted y sus usuarios, no parece ser una ayuda increíble, ya que las 500.000 celdas parecen ser en su mayoría "celdas a la derecha amontonadas" y no filas. amontonándose. Tener 100.000 tareas con cinco celdas cada una con cálculos exige Access o algo similar o quizás dedicado a proyectos. Pero tener 500 filas con tres años de fechas a la derecha (1000-1100 cálculos por fila) NO exige un programa de base de datos (aunque todavía necesita un software dedicado... pero eso simplemente no siempre es posible, duela o no). dolores). Contrariamente al "conocimiento" popular, Excel NO es una especie de máquina de cálculo lenta de "melaza en enero" y SQL no siempre es un niño prodigio brillante y brillante del siglo 41 que de alguna manera existe en nuestro mundo actual. Estás haciendo lo anterior de todos modos, por lo que la carga de cálculo se reduce a una pequeña parte de lo que es hoy, así que...