En base a comentarios aclaro lo que pido.
Quiero obtener una fórmula anidada en varias celdas y aplanada en una sola fórmula.
El siguiente ejemplo es sólo para demostrar cómo puedes hacerlo manualmente. Sin embargo, las fórmulas pueden ser de cualquier tipo y no estar relacionadas en absoluto con el ejemplo siguiente.
Tenía curiosidad por saber si lo siguiente era posible usando el código/herramientas/excel mismo:
Como puedes ver en la imagen, la in hoursfórmula es bastante simple, pero en realidad está compuesta por un montón de fórmulas de las otras celdas. Me preguntaba si es posible deshacernos de los cálculos intermedios obteniendo una única fórmula generada mediante el uso de todas las fórmulas en una única fórmula gigante.
Si bien es fácil diseñar y depurar fórmulas grandes de esta manera, en producción, los usuarios inexpertos pueden destruir fácilmente partes de la fórmula al intentar modificar las hojas. Puede protegerlo bloqueando hojas y desbloqueando otras cosas, pero eso también tiene efectos secundarios no deseados de no poder expandir el rango de fórmulas o agregar nuevas filas... etc.
Entonces, para la producción, me preguntaba si era posible obtener una respuesta de fórmula única para algo compuesto de esta manera.
En el ejemplo anterior, intentamos calcular horas numéricas a partir de horas en cadena.
in hours = in seconds / 3600 = O2 / 3600
in seconds = HOURS * 3600 + MINUTES * 60 + SECONDS
HOURS = extract digits if they exist between 'h' and beginning of string
MINUTES = extract digits if they exist between 'h' and 'm'
SECONDS = extract digits if they exist between 'm' and end of string
Entonces, si sustituyeras las fórmulas manualmente, saldría con este aspecto.
in hours = (HOURS * 3600 + MINUTES * 60 + SECONDS) / 3600
= (extract digits if they exist between 'h' and beginning of string * 3600 + extract digits if they exist between 'h' and 'm' * 60 + extract digits if they exist between 'm' and end of string) / 3600
Como puede ver, este ejemplo es bastante simple, pero me preguntaba si era posible hacerlo para otras funciones compuestas complejas.
Excel ya hace todos estos cálculos por sí solo en algún tipo deASTSupongo que ya detecta dependencias cíclicas y demás. Entonces, ¿debe haber una manera de hacerlo nosotros mismos? ¿O engancharse a los fundamentos de Excel para extraer la misma fórmula?
Respuesta1
Probablemente sea posible hacer lo que busca, pero una UDF podría ser mejor si puede usarla.
Option Explicit
Function MyHrs(MyType As String, target As String) As Single
Dim x As Long, y As Long, z As Long
Dim Hrs As Long, Mns As Long, Ss As Long
On Error Resume Next
x = InStr(target, "h")
y = InStr(target, "m")
z = InStr(target, "s")
Hrs = Left(target, x - 1)
Mns = Mid(target, x + 1, y - x - 1)
Ss = Mid(target, y + 1, z - y - 1)
If MyType = "h" Or MyType = 1 Then MyHrs = Hrs + Mns / 60 + Ss / 3600
If MyType = "m" Or MyType = 2 Then MyHrs = Hrs * 60 + Mns + Ss / 60
If MyType = "s" Or MyType = 3 Then MyHrs = Hrs * 3600 + Mns * 60 + Ss
End Function
Abra la ventana del desarrollador usando ALT + F11, luego inserte un módulo y pegue el código. En la hoja utilícelo de la siguiente manera:
A este probablemente le vendría bien un poco más de codificación para evitar los errores generados, pero debería funcionar para demostrar el concepto.