Convertir fórmula a VBA para minimizar peso de archivo
Necesito de la ayuda de un experto realmente porque mis conocimientos de excel y de vba son bastantes empíricos pero me defiendo en lo que necesito hasta donde puedo. Tengo la siguiente fórmula
=SI.ERROR(SI(Y($A$6=10,LARGO(Hoja3!B32)>=3,O(INDICE(Hoja5!32:32,B$29)=INDICE(Hoja3!32:32,B$5),INDICE(Hoja5!32:32,B$29)+0.00001=INDICE(Hoja3!32:32,B$5)),LARGO(INDICE(Hoja5!$MQ$10:$NF$46,B$4,B$7))=4),Hoja3!B32,""),"")
la formula va en cada celda y tengo una hoja con mas de 5.000 columnas con la misma formula, osea no la misma exactamente ya que al arrastrarla pues tambien cambian sus datos analizar. Pero basicamente hace la misma funcion, entonces queria saber si no era muy complicada pasarla a VBA para asi disminuen la formula los datos que estan con su signo "$" es porque siempre estan fijos, ya los otros calculos que no tienen en sus celdas el $ es porque al pegarlas en otras celdas pues se recalculan de acuerdo a como se vayan pegando en las otras celdas.
Pero me gustaría poder cambiarle manualmente a la macro lo que son las filas y las columnas, por si tengo algún ajuste que hacerle lo pueda reemplazar en la macro indicándole fila y columna.
Por ultimo que la macro siempre funcione en la fila donde este activo sin importar en que hoja este. Osea si estoy en una hoja cualquiera, la macro igual funcione en la hoja que le especifique en la macro.
2 respuestas
Con gusto te ayudo a reemplazar la fórmula con algo que se conoce como arreglos o matrices.
Todos los datos se cargan en memoria en las matrices, se realizan todos los cálculos en memoria y en un solo paso se ponen todos los datos en la hoja.
Dependiendo del número de registros, de la cantidad de fórmulas y de la complejidad de las fórmulas, es el tiempo en que tardará la macro en realizar los cálculos. Pero son muy rápidos.
De hecho ya te lo había propuesto:
Si tienes muchas fórmulas te puedo ayudar con una macro que reemplace todas las fórmulas, realice todos los cálculos en memoria y regrese los resultados a las celdas.
Las macros en memoria son muy rápidas, si necesitas actualizar datos, ejecutas nuevamente la macro.
Sube tu archivo en google drive, comparte el archivo para "Cualquier usuario de Internet que tenga el vínculo puede ver los elementos".
Copia el vínculo y lo pegas aquí.
NOTA: Si tienes información confidencial remplázala por datos genéricos.
Lo IMPORTANTE, es que se conserven las estructuras de las hojas.
En la primera hoja del libro describe brevemente qué deben realizar los cálculos y orden de ejecución de las macros.
Si puedes compartir 2 archivos, uno con los datos originales antes de procesar tus macros, y otro libro, con los resultados. Eso sería genial.
Dante ya organice el archivo. Realmente no tiene macros, ya que solo necesito pasar unas fórmulas a macros, espero sea sencillo, estoy muy atento si requieres información. Mil gracias
Vaya fórmula!
Pero no te preocupes, podemos pasar la fórmula a una macro.
No entiendo esta parte:
, y al ejecutar estando en la fila 32 no importa la hoja dondo yo este, osea si estoy en cualquier otra hoja y se ejecute, lo haga de acuerdo a la fila donde este activo.
Voy a hacer una secuencia de pasos.
- Seleccionas la hoja "10".
- Seleccionas la fila 32 de la hoja "10".
- Ahora seleccionas la "Hoja3".
- Seleccionas la fila 45 de la "Hoja3".
- Ejecutas la macro.
- Entiendo claramente, los resultados van en la hoja "10", pero en cuál fila quieres los resultados, ¿en la fila 32 o en la fila 45?
Otra duda.
Tu comentario:
Para este ejemplo solo tengo esa fila con formulas pero en el excel que la quiero usar quiero que tenga aprox 2.000 fillas
Necesito poner fórmulas en las filas 33, 34, 35, etc, para ver el comportamiento de los datos, las fórmulas y los resultados.
¿Qué debo hacer, simplemente copio las fórmulas de la fila 32 en la fila 33?
Si tengo más dudas regreso a preguntar, pero responde las 2 dudas anteriores los más breve posible.
Si la fórmula es algo grande.. por eso no puedo hacer más grande el archivo porque pesa mucho y no hay pc que lo abra. A tu primera duda:
Voy hacer una secuencia para expresarme mejor:
*Colocas en la celda A6 de la hoja 10 el numero 1.
*Te vas a la hoja que dice "explicación para hacer la macro" o cualquier otra hoja, ya que la idea es que la macro se ejecute no importa la hoja donde estés.. entonces te activas en la fila 32 de la hoja donde estés... hablo solo de esta fila ya que en este archivo es la única fila que tiene datos hoja 3 y la hoja 10. Pero pues en el archivo real la hoja 3 puede tener datos, ya que de esa hoja3 es donde la hoja 10 extrae los datos sus fórmulas..
*Ejecutas la macro que debería reemplazar las fórmulas que esta en la fila32 de la hoja 10. Ya que se supone que en esa fila estarías activo pero en otra hoja.
* Y que los resultados que arroje la macro queden en la fila 32 de la hoja 10, tal cual como lo hacen las fórmulas, que ella de acuerdo a las condiciones de las fórmulas, extraen los datos de la hoja3, de la misma fila. Ya que si miras las fórmulas extraen los resultados de esa misma fila de la hoja3,,
*Ahora en por ejemplo en el caso que yo este en la activo en la fila45 cualquier otra fila y le doy ejecutar a la macro, ella haga lo mismo que las fórmulas pero teniendo en cuenta si en la hoja3 de esa fila hubieran datos.. en el caso de que no pues no, osea las fórmulas extraen los datos de la hoja3 pero de la fila donde este activa.. como te dije anteriormente,, en el archivo que subí pues solo hay una fila con datos,, pero si hubieran más pues lo hiciera fila por fila
espero haberme hecho entender..
Para tu segunda duda:
Si, las fórmulas realmente de la oja10 están en la fila 32,33,34,35 etc.. y también la hoja 3 toda su fila tiene datos en esas mismas filas.. cada fila es diferente en datos en la hoja3..
SI QUIERES PUEDES COPIAR LOS DATOS DE LA FILA32 DE LA HOJA3 Y PEGARLOS EN LA 34,35,36,37, para probar la macro en esas otras filas al ejecutarla, debería darte los mismos resultados ya que serian los mismos datos en la hoja3. Pero pues digo para probar.. laidea es que las fórmulas de la hoja 10 de esa fila que se repite hacia las filas de abajo. Se pasen a macro para ejecutarlas y no quedar en fórmulas porque pesan muchosimo, ellas ejecutan y muestran su resultado rápido, pero pesan demasiado.
Quedo atento a tus dudas mil gracias :)
Por ultimo, si te fijas las formualas de la fila 32 de la hoja10, extraen datos si en la celda A6 dice el numero 1 o el 2 o el 3 hasta el 10. Cada que cambias un numero en esa celda podrás ver que las fórmulas arrojan datos diferentes dentro de la tabla, en la celda GSW de esa hoja, esta la suma de datos de esa fila, y cada que cambias un numero en esa celda las fórmulas actualizan y arrojan diferentes resultados,,, todos esos datos las fórmulas los extraen de la hoja 3. de esa misma fila claro,,, si pegas las fórmulas en la fila 33 pero en la hoja3 de esa fila esta vacía, no te va arrojar datos,, ya que las fórmulas extraen datos de acuerdo a esa fila de datos de la hoja3... es algo compleja,, no se es sea fácil pasar esa fila de fórmulas a una macro,,, creo que es algo tedioso..
Espero haberme hecho entender..
No, la verdad no te entiendo.
Te pedí que fueras lo más breve posible, pero te extiendes demasiado con la explicación.
Empecemos otra vez.
A qué te refieres con "entonces te activas en la fila 32"
Otra vez mi secuencia:
- Seleccionas la hoja "10".
- Seleccionas la fila 32 de la hoja "10".
- Ahora seleccionas la "Hoja3".
- Seleccionas la fila 45 de la "Hoja3".
Ya no existe la fila activa 32. La única fila activa es la 45.
Por eso no entiendo cuando dices "te activas en la fila 32"
No respondiste mi segunda duda.
Te vuelvo a preguntar:
Necesito poner fórmulas en las filas 33, 34, 35, etc, para ver el comportamiento de los datos, las fórmulas y los resultados.
Antes de pasar las fórmulas a una macro, debe analizar qué hace cada fórmula, Y debo probar también con otras filas. Entonces:
¿Qué debo hacer, simplemente copio las fórmulas de la fila 32 en la fila 33?
Tu respuesta breve.
Ya entendí, si cambias el número en la celda A6 se recalculan los valores. Eso ya lo entendí y no te estoy pidiendo que lo expliques.
Estás preocupado por el funcionamiento de la macro con 2000 filas. Y lo entiendo perfectamente. No te preocupes por eso. La macro va a funcionar con la fila 32, si en la hoja3 solamente tienes datos en la fila 32.
Si en la hoja3 tienes datos desde la fila 32 hasta la fila 2000, la macro va a generar los resultados en la hoja "10" desde la fila 32 y hasta la fila 2000.
Pero antes, mucho antes de que yo pueda hacer eso, tengo que entender la fórmula para poder replicarla en las macros.
Si la verdad me extendí demasiado..
- Seleccionas la hoja "10".
- Seleccionas la fila 32 de la hoja "10".
- Ahora seleccionas la "Hoja3".
- Seleccionas la fila 45 de la "Hoja3".
Ya no existe la fila activa 32. La única fila activa es la 45.
Por eso no entiendo cuando dices "te activas en la fila 32"
No para nada,, en ningún momento debes seleccionar la fila 45.. todo desde la fila 32. en todas las hojas..
¿Qué debo hacer, simplemente copio las fórmulas de la fila 32 en la fila 33?
Así es exactamente,, copia las fórmulas de la 32 en la 33 y así sucesivamente..
Si en la hoja3 tienes datos desde la fila 32 hasta la fila 2000, la macro va a generar los resultados en la hoja "10" desde la fila 32 y hasta la fila 2000.
Si exacto, pero que al ejecutar la macro solo en la fila donde este activo no toda la hoja de una vez.. si estoy activo en la 32 ejecute los datos de esa fila y de los resultados de esa fila y si estoy en otra fila, los haga con ls datos de esa fila donde este activo en ese momento..
Mil gracias. Espero haber sido claro.. si o aquí estoy pendiente,, bendiciones
No me queda clara tu idea de fila activa.
La única fila activa es la que tienes seleccionada en la hoja activa.
Y lo pongo en negritas, no existen 2 filas activas o 5 filas activas, la única fila activa es la que tienes en la hoja activa.
Si estás en la hoja3 y seleccionas la fila 34. Y ejecutas la macro, entonces la macro pondrá los valores en la fila 34 en la hoja "10".
¿A eso te refieres con fila activa?
Si o no
si exactamente Dante.. tal vez no me hecho entender pero es lo que he tratado de decirte..
En tus fórmulas tienes esto:
SI.ERROR(SI(Y($A$6=10...
Lo tienes para el 10, 9, 8 y así sucesivamente. Pero en algunas celdas solamente llega hasta el 5, en otras hasta el 2 y en otras hasta el 1.
¿Es correcto? Responde sí o no.
¿O todas las fórmulas deben llegar hasta el 1? Responde sí o no.
¿O todas las fórmulas deben llegar hasta el 1? Responde sí o no.
Si
Mejor y todas desde el 10 hasta el 1 para más seguridad...
Estoy revisando la posibilidad de pasar esas enormes fórmulas a una macro.
Pero, para que puedas trabajar con tu archivo mientras termino.
Ejecuta la siguiente macro.
Sub copiaformula()
Dim fila As Long
Dim sh As Worksheet
'
Application.ScreenUpdating = False
Set sh = Sheets("10")
fila = ActiveCell.Row
If fila < 33 Then
MsgBox "Debes seleccionar una fila mayor a 32"
Exit Sub
End If
sh.Rows(32).Copy sh.Rows(fila)
sh.Rows(fila).Copy
sh.Rows(fila).PasteSpecial xlValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
End SubFunciona de la siguiente manera:
- Las fórmulas de la fila 32 debes conservarlas. (Todavía no hago la macro que reemplace las fórmulas)
- Ya sea que te encuentres en la hoja "10" o en cualquier otra hoja, selecciona la fila a la cual quieres actualizar la información en la hoja "10".
- Por ejemplo, seleccionas la fila 34.
- La macro copia todas las fórmulas de la fila 32, las pega en la fila 34, se calculan los valores para la fila 34.
- El siguiente paso de la macro es copiar la fila 34 y pegarla como valores.
- De esa manera solamente tendrás la fila 32 con fórmulas.
El proceso de copiar y pegar, demora de 3 a 4 segundos.
Dame oportunidad de seguir analizando tus fórmulas para entregarte otra solución.
Dante no te preocupes, yo creo que esa solución que me diste me sirve sin necesidad de maravilla.. solo una ultima petición a esta macro que me acabas de enviar... podrías modificarla para que lo haga solo con las fórmulas que están en el rango de las columnas A hasta la GSV. De la hoja10.. ¿y no toda la fila?
Mil gracias, la probé y parece que funciona genial.. creo que podría servir solo esta.
Listo.
Te paso la macro para realizar los cálculos en memoria.
Option Explicit
'
Dim m1 As Variant, m2 As Variant, m3 As Variant, m4 As Variant
Dim n1 As Long, n2 As Long, n3 As Long, n4 As Long
'
Sub CalcularValores()
'Declaración de variables
Dim sh1 As Worksheet, sh3 As Worksheet, sh5 As Worksheet
Dim a1 As Variant, a3 As Variant, a5 As Variant, b1 As Variant
Dim i As Long, j As Long, fil As Long, col As Long
Dim n As Long
Dim dato3 As Variant, dato51 As Variant, dato52 As Variant, dato53 As Variant, dato54 As Variant
'
'Establecer hojas
Set sh1 = Sheets("10")
Set sh3 = Sheets("Hoja3")
Set sh5 = Sheets("Hoja5")
If ActiveCell.Row < 32 Then
MsgBox "Selecciona una fila mayor a 31"
Exit Sub
End If
i = ActiveCell.Row
n1 = 0: n2 = 0: n3 = 0: n4 = 0
'
'Cargar los datos en matrices
n = sh1.Range("A6").Value 'valor del 1 al 10, hoja "10" celda "A6"
a1 = sh1.Range("A1:GSV31").Value 'Matriz de la hoja "10"
a3 = sh3.Range("A" & i & ":GSV" & i).Value 'Fila de la hoja3
a5 = sh5.Range("A" & i & ":GSV" & i).Value 'Fila de la hoj5
Call CargarMatrices(sh5, n)
ReDim b1(1 To 1, 1 To UBound(a1, 2)) 'Matriz para almacenar el resultado
'
'Después de cargar los datos en matrices, el proceso se realiza en memoria
For j = 1 To UBound(a1, 2)
If Len(a3(1, j)) >= 3 Then
'Para leer el dato de la matriz mx
'se requiere la referencia de la fila y la columna de las celdas "A1:GSV31"
fil = a1(4, j)
col = a1(7, j)
'
dato3 = a3(1, a1(5, j)) 'de la hoja3, toma el dato según la columna: hoja10 fil 5 col j
'
'de la hoja5, toma el dato según la columna: hoja10 fil nx col j
If n1 <> 0 Then
dato51 = a5(1, a1(n1, j))
If (dato3 = dato51 Or dato3 = Val(dato51) + 0.00001) And _
Len(m1(fil, col)) = 4 Then 'obtiene el dato de Matriz 1
b1(1, j) = a3(1, j)
End If
End If
If n2 <> 0 Then
dato52 = a5(1, a1(n2, j))
If (dato3 = dato52 Or dato3 = Val(dato52) + 0.00001) And _
Len(m2(fil, col)) = 4 Then 'obtiene el dato de Matriz 2
b1(1, j) = a3(1, j)
End If
End If
If n3 <> 0 Then
dato53 = a5(1, a1(n3, j))
If (dato3 = dato53 Or dato3 = Val(dato53) + 0.00001) And _
Len(m3(fil, col)) = 4 Then 'obtiene el dato de Matriz 3
b1(1, j) = a3(1, j)
End If
End If
If n4 <> 0 Then
dato54 = a5(1, a1(n4, j))
If (dato3 = dato54 Or dato3 = Val(dato54) + 0.00001) And _
Len(m4(fil, col)) = 4 Then 'obtiene el dato de Matriz 4
b1(1, j) = a3(1, j)
End If
End If
End If
Next
'Se pasan los resultados a la hoja
sh1.Range("A" & i).Resize(1, UBound(b1, 2)).Value = b1
End Sub
Sub CargarMatrices(sh5 As Worksheet, n As Long)
Select Case n
Case 10
m1 = sh5.Range("MQ10:NF46").Value
m2 = sh5.Range("OM10:PB46").Value
m3 = sh5.Range("RO10:SD46").Value
n1 = 29: n2 = 30: n3 = 31
Case 9
m1 = sh5.Range("NG10:NV46").Value
m2 = sh5.Range("QY10:RN46").Value
n1 = 27: n2 = 28
Case 8
m1 = sh5.Range("MQ10:NF46").Value
m2 = sh5.Range("NW10:OL46").Value
m3 = sh5.Range("QI10:QX46").Value
n1 = 24: n2 = 25: n3 = 26
Case 7
m1 = sh5.Range("MA10:MP46").Value
m2 = sh5.Range("PS10:QH46").Value
n1 = 22: n2 = 23
Case 6
m1 = sh5.Range("MA10:MP46").Value
m2 = sh5.Range("MQ10:NF46").Value
m3 = sh5.Range("NG10:NV46").Value
m4 = sh5.Range("PC10:PR46").Value
n1 = 18: n2 = 19: n3 = 20: n4 = 21
Case 5
m1 = sh5.Range("MA10:MP46").Value
m2 = sh5.Range("OM10:PB46").Value
n1 = 16: n2 = 17
Case 4
m1 = sh5.Range("MA10:MP46").Value
m2 = sh5.Range("MQ10:NF46").Value
m3 = sh5.Range("NW10:OL46").Value
n1 = 13: n2 = 14: n3 = 15
Case 3
m1 = sh5.Range("MA10:MP46").Value
m2 = sh5.Range("NG10:NV46").Value
n1 = 11: n2 = 12
Case 2
m1 = sh5.Range("MA10:MP46").Value
m2 = sh5.Range("MQ10:NF46").Value
n1 = 9: n2 = 10
Case 1
m1 = sh5.Range("MA10:MP46").Value
n1 = 8
End Select
End SubFunciona de la siguiente manera:
Primero borra todas tus fórmulas, desde la fila 32 hacia abajo de tu hoja "10". La macro no necesita las fórmulas, ya que va a realizar todas las búsquedas en memoria.
1. Copia TODO TODO el código. No omitas ninguna línea.
2. Pon TODO el código en un módulo.
3. Selecciona la fila que quieras calcular.
4. Ejecuta la macro "CalcularValores"
5. El resultado es inmediato!
Nota: Hice pruebas con tu fórmula vs mi macro. Utilizando los valores que me enviaste, fui cambiando el valor de 1 hasta 10.
El único valor que no coincide es cuando pongo 3.
Realiza tus pruebas con la macro.
Otro detalle. También borra las fórmulas de la columna GSW
Me faltó el cálculo para contar.
Después de esta línea:
sh1.Range("A" & i).Resize(1, UBound(b1, 2)).Value = b1Agrega esta línea:
sh1.Range("GSW" & i).Value = WorksheetFunction.Count(sh1.Range("A" & i & ":GSV" & i))Listo, la hoja "10" está libre de fórmulas.
Y sí, puedes seleccionar la fila desde otra hoja. Los resultados quedarán en la hoja "10".
¡Gracias! Excelente. Funciono de maravilla ya la probé en todos los escenarios y funciono y muy rápida, así puedo reducir demasiado el peso del archivo...
Te hago una ultima consulta espero se pueda hacer fácil para no ocuparte más con esta pregunta:
Sub copiaformula()
Dim fila As Long
Dim sh As Worksheet
'
Application.ScreenUpdating = False
Set sh = Sheets("10")
fila = ActiveCell.Row
If fila < 33 Then
MsgBox "Debes seleccionar una fila mayor a 32"
Exit Sub
End If
sh.Rows(32).Copy sh.Rows(fila)
sh.Rows(fila).Copy
sh.Rows(fila).PasteSpecial xlValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Subesta macro que pasaste inicialmente me gusta mucho para poder utilizarla en muchas otras tablas que tengo y así reduciría muchísimo mas el peso del archivo.. como podría usarla pero indicando rango y no toda la fila?
Ejemplo en este caso que lo haga con las fórmulas desde la columna A hasta la GSV
mil gracias Dante...
Esta respuesta ya se hizo muy extensa.
El objetivo de esta pregunta, era pasar los fórmulas a macro y reducir el tamaño del archivo. Y con la macro "CalcularValores", se cambiaron 2500 fórmulas por un código que realiza los cálculos en memoria.
Te parece si creas un nueva pregunta para el tema de la macro "copiaformula".
En video 15 de mi canal dejé ejemplos de cómo trabajar con fórmulas en VBA.
03:10 Tres instrucciones ya sea que quieras dejar la fórmula o solo valores.
07:00 Cómo reemplazar partes de la fórmula por variables.
09:25 Cómo utilizar la grabadora de macros para obtener la sintaxis de tu fórmula y luego pasarla a la hoja, ya sea como fórmula o directamente el resultado.
Sdos.
Elsa
http://aplicaexcel.com