¿Hay alguna forma de evaluar matrices de otras hojas de diferentes tamaños con una función simple?
Tengo una función que crea una función personalizada y una matriz basada en circunstancias cambiantes en los datos de una hoja. Si trato de evaluarlo en mi hoja principal, devuelve #valor, pero si lo evalúo en la hoja de la que proviene la matriz, devuelve el resultado correcto. ¿Alguna idea de cómo solucionar esto si el tamaño de la matriz 3-d está cambiando?
La matriz se devuelve porque estoy trabajando con una función de sumproducto complicada. La función de sumproducto es la única función anidada dentro de la fórmula de evaluación.
Mi Evaluar VBA actualmente se ve así:
Function Makeformula(Ref As String)
Application.Volatile
Makeformula = Evaluate(Ref)
End Function
Hola Ben,
Resumen: (1a) y (2a) a continuación obtendrán el resultado correcto en la Hoja 1 si copio y pego la salida de (1) y (2) en nuevas celdas y ejecuto el texto como una función (agregando el signo igual) . (1) funciona con cualquiera de las soluciones presentadas y (2) no como está. (2) es una matriz ya que el sumproducto extrae dos valores para el sumproducto.
Intenté las dos respuestas que diste en vano. Adjuntaré un código a continuación para mostrarle y en negrita la fórmula Long para facilitar las cosas a la vista.
De manera similar a antes, sus respuestas funcionan cuando mi sumproducto finalmente termina generando el valor de texto que se ve en (1), pero no (2). La única diferencia es que (1) no es una matriz multidimensional, mientras que (2) lo es. También vale la pena señalar que cuando copio y pego (1) y (2) independientemente como valores de texto en celdas en blanco y pongo un signo igual al principio, obtienen el valor correcto. Sin embargo, (2) no se evalúa correctamente con ninguna de las funciones de Makeformula que proporcionó, aunque se puede evaluar como texto puro insertado en una nueva celda después de generar la fórmula de Sumproduct. (2) conduce a un #Valor! error como está.
A continuación se muestra cómo se ingresan en la celda con la función MakeFormula. Usé "Interfaz de entrada de fin de mes" para el segundo argumento en su función porque eso es a lo que el sumproducto hace referencia en última instancia si se ejecuta de forma independiente como se ve en (1a) y (2a). Cambiar el segundo argumento de su primera solución a "Month End Backend" no funciona tan bien.
(1) = MakeFormula2 ( ("SUMPRODUCT ((" & VLOOKUP (VLOOKUP ('Inc Stmnt' '20 vs '' 19 '!$C11,'Month End Input Backend'!$segundo$4:$re$78,3,FALSE),'Month End Input Backend'!$H$4:$K$27,3,FALSE)&"="&CHAR(34)&U$7 & CHAR (34) & ") (" & LEFT (myvlookup ($C11,'Month End Input Backend'!$segundo$4:$mi$78,2,INDIRECT(VLOOKUP(VLOOKUP('Inc Stmnt ''20 vs ''19'!$C11, 'Backend de entrada de fin de mes'!$B$4:$D$78,3, FALSE), 'Backend de entrada de fin de mes'!$H$4:$K$27,4, FALSO))), LEN (myvlookup ($C11,'Month End Input Backend'!$segundo$4:$mi$78,2,INDIRECT(VLOOKUP(VLOOKUP('Inc Stmnt ''20 vs ''19'!$C11, 'Backend de entrada de fin de mes'!$B$4:$D$78,3, FALSE), 'Backend de entrada de fin de mes'!$H$4:$K$27,4, FALSE)))) - 1) & ") (" & VLOOKUP (VLOOKUP ('Inc Stmnt' '20 vs '' 19 '!)$C11,'Month End Input Backend'!$segundo$4:$re$78,3,FALSE),'Month End Input Backend'!$H$4:$K $ 27,2, FALSO) & "))") , "Interfaz de entrada de fin de mes")
^ Este funciona
(2) = MakeFormula2 ( ("= SUMPRODUCT ((" & VLOOKUP (VLOOKUP ('Inc Stmnt' '20 vs '' 19 '! $ C35,' Month End Input Backend '!$B$4:$D$78,3, FALSE), 'Backend de entrada de fin de mes'!$H$4:$K$27,3, FALSE) & "=" & CHAR (34) & U $ 7 & CHAR (34) & ") (" & LEFT (myvlookup ($ C35, 'Month End Input Backend'!$B$4:$E$78,2, INDIRECTO (VLOOKUP (VLOOKUP ('Inc Stmnt' '20 vs '' 19 '!$C35,'Month End Input Backend'!$segundo$4:$re$78,3,FALSE),'Month End Input Backend'!$H$4:$K$27,4,FALSE))),LEN(myvlookup($C35, 'Backend de entrada de fin de mes'!$B$4:$E$78,2, INDIRECTO (VLOOKUP (VLOOKUP ('Inc Stmnt' '20 vs '' 19 '!$C35,'Month End Input Backend'!$segundo$4:$re$78,3,FALSE),'Month End Input Backend'!$H$4:$K $ 27,4, FALSO)))) - 1) & ") (" & VLOOKUP (VLOOKUP ('Inc Stmnt' '20 vs '' 19 '! $ C35,' Month End Input Backend '!$B$4:$D$78,3, FALSE), 'Backend de entrada de fin de mes'!$H$4:$K$27,2, FALSE) & "))") , "Interfaz de entrada de fin de mes")
^^ Este no lo hace.
A continuación se muestran los resultados de las porciones de sumproducto de las fórmulas que pueden extraer los valores correctamente en ambos casos si se copian y pegan como valores de texto en nuevas celdas.
(1a) SUMPRODUCT(('Month End Input Frontend'!$C$12:$Q$12="Jan 2020")*(('Month End Input Frontend'!B13:B89=" Sales of Product Income"))*('Month End Input Frontend'!$C$13:$Q$89))
(2a) SUMPRODUCT(('Month End Input Frontend'!$C$12:$Q$12="Jan 2020")*(('Month End Input Frontend'!B13:B89=" Billing Fee Expense")+('Month End Input Frontend'!B13:B89=" Medical Billing and Collection"))*('Month End Input Frontend'!$C$13:$Q$89))
Respuestas
Evaluate(u Application.Evaluate) opera en el contexto de la hoja activa. Desea usar Worksheet.Evaluate, por lo que debe pasar a la función una referencia al archivo Worksheeten cuestión. Tus opciones incluyen:
- Pase el nombre de la hoja como
String:
Public Function MakeFormula(ByVal Ref As String, ByVal SheetName As String) As Variant
Application.Volatile
Makeformula = ThisWorkbook.Worksheets(SheetName).Evaluate(Ref)
End Function
- Pase una
Rangereferencia y useRange.Parent. Tu fórmula se vería así=MakeFormula("yourlongformula", 'Sheet 2'!A1)
Public Function MakeFormula(ByVal Ref As String, ByVal Cell As Range) As Variant
Application.Volatile
Makeformula = Cell.Parent.Evaluate(Ref)
End Function