excel - son - Intentando mejorar la eficiencia de la fórmula de matriz
posicion de una celda en excel (5)
Es posible que desee examinar el editor de macro de VB. En el Menú Herramientas, vaya a Macros y seleccione Editor Visual Basic. Esto proporciona un entorno de programación completo donde puede escribir su propia función.
VB es un lenguaje de programación simple y Google tiene todas las guías que necesita.
Allí, puedes escribir una función como MySum () y hacer que haga las matemáticas que realmente necesites, de una manera clara escrita por ti mismo.
Saqué esto de Google, y parece una buena guía para configurar todo esto. http://office.microsoft.com/en-us/excel/HA011117011033.aspx
Tengo una fórmula de matriz SUM que tiene múltiples declaraciones IF anidadas, por lo que es muy ineficiente. Mi fórmula abarca más de 500 filas, pero aquí hay una versión simple de ella:
{=SUM(IF(IF(A1:A5>A7:A11,A1:A5,A7:A11)-A13:A17>0,
IF(A1:A5>A7:A11,A1:A5,A7:A11)-A13:A17,0))}
Como puede ver, la primera mitad de la fórmula comprueba dónde la matriz es mayor que cero, y si lo son, suma los de la segunda parte de la fórmula.
Notarás que la misma instrucción IF se repite dos veces, lo cual para mí es ineficiente, pero es la única forma en que puedo obtener la respuesta correcta.
El ejemplo de datos que tengo es el siguiente:
Datos de muestra en la hoja de cálculo http://clients.estatemaster.net/SecureClientSite/Download/TempFiles/example.jpg La respuesta debería ser 350 en este caso usando la fórmula que mencioné anteriormente.
Si traté de poner una declaración MAX dentro de la matriz, eliminando la prueba para encontrar dónde era mayor que cero, entonces fue así:
{=SUM(MAX(IF(B2:B6>B8:B12,B2:B6,B8:B12)-B14:B18,0))}
Sin embargo, parece que solo calcula la primera fila de datos en cada rango, y me dio la respuesta incorrecta de 70.
¿Alguien sabe que puedo reducir el tamaño de la fórmula o hacerla más eficiente al no tener que repetir una declaración IF allí?
ACTUALIZAR
Palanqueta
La fórmula MAX que sugirió en realidad no funciona para todos los escenarios.
Si cambié mis datos de muestra en las filas 1 a 5 como se muestra a continuación (mostrando que algunos de los números son mayores que sus respectivas celdas en las filas 7 a 11, mientras que algunos de los números son más bajos)
La respuesta correcta que intento lograr es 310, sin embargo, sugirió que la fórmula MAX da una respuesta incorrecta de 275.
Supongo que la fórmula debe ser una función de matriz para dar la respuesta correcta.
¿Cualquier otra sugerencia?
Esto parece funcionar:
{=SUM(IF(A1:A5>A7:A11,A1:A5-A13:A17,A7:A11-A13:A17))}
EDITAR - no maneja los casos donde la resta termina negativa
Esto funciona, pero ¿es más eficiente?
{=SUM(IF(IF(A1:A5>A7:A11,A1:A5,A7:A11)>A13:A17,IF(A1:A5>A7:A11,A1:A5,A7:A11)-A13:A17,0))}
¿Qué hay de esto?
=MAX(SUM(IF(A1:A5>A7:A11, A1:A5, A7:A11))-SUM(A13:A17), 0)
Editar:
Woops: se perdió la parte de tirar negativos. ¿Qué hay de esto? No estoy seguro de que sea más rápido ...
=SUM((IF(A1:A5>A7:A11,IF(A1:A5>A13:A17,A1:A5,A13:A17),IF(A7:A11>A13:A17,A7:A11,A13:A17))-A13:A17))
Editar 2:
¿Cómo funciona esto para ti?
=SUM((((A1:A5>A13:A17)+(A7:A11>A13:A17))>0)*(IF(A1:A5>A7:A11,A1:A5,A7:A11)-A13:A17))
Una forma más eficiente en términos de cálculo (y especialmente de recalcular el cálculo) es usar columnas auxiliares en lugar de fórmulas de matriz:
C1: =MAX(A1,A7)-A13
D1: =IF(C1>0,C1,0)
copia ambos estos hacia abajo 5 filas
E1: =SUM(D1:D5)
Excel solo volverá a calcular las fórmulas dependientes de cualquier valor modificado, en lugar de tener que calcular todas las columnas virtuales implícitas en la fórmula de matriz cada vez que cambie un solo número. Y está haciendo menos cálculos incluso si cambia todos los números.
=MAX( MAX( sum(A1:A5), sum(A7:A11) ) - sum(A13:A17), 0)