Como optimizar tus fórmulas, hojas y libros Excel

Compatibilidad: Truco compatible con Excel 365Truco compatible con Excel 2016Truco compatible con Excel 2013Truco compatible con Excel 2010Truco compatible con Excel 2007

Si tienes libros pesados que tardan en abrirse o en actualizarse, te indicamos donde está el problema, como optimizar tus fórmulas, hojas y libros Excel.

Como optimizar tus fórmulas, hojas y libros Excel

Mediante las macros cronometramos el tiempo que tarda en ejecutarse los cálculos del libro, da cada hoja y de un rango, varias filas o columnas.

Debido a que Microsoft Windows es un sistema operativo multitarea, la segunda vez que se calcula el tiempo, puede ser más rápido que la primera vez. Para conseguir mayor precisión tomaremos varias mediciones y aplicaremos la media de los resultados.

  • CREAR MACRO

  1. Abrir el libro Excel deseado.

  2. Pulsar la tecla Alt y mantener pulsada / Pulsar la tecla F11. Mostrará el editor de Visual Basic.

  3. Ir a: Barra de Menú / Insertar / Módulo. Mostrará la ventana del módulo donde escribimos la macro.

    Explorador de Proyectos con módulo1 seleccionado
  4. Escribir el código de la macro:

    Private Declare Function getFrequency Lib "kernel32" _
    Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
    Private Declare Function getTickCount Lib "kernel32" _
    Alias "QueryPerformanceCounter" (cyTickCount As Currency) As Long

    Function MicroTimer() As Double
    Dim cyTicks1 As Currency
    Static cyFrequency As Currency

    MicroTimer = 0
    If cyFrequency = 0 Then getFrequency cyFrequency
    getTickCount cyTicks1
    If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency

    End Function

      

    Sub Tiempo_Rango()
    DoCalcTimer 1
    End Sub

    Sub Tiempo_Hoja()
    DoCalcTimer 2
    End Sub

    Sub Tiempo_Libro_Recalcular()
    DoCalcTimer 3
    End Sub

    Sub Tiempo_Libro()
    DoCalcTimer 4
    End Sub

      

    Sub DoCalcTimer(jMethod As Long)
    Dim dTime As Double
    Dim dOvhd As Double
    Dim oRng As Range
    Dim oCell As Range
    Dim oArrRange As Range
    Dim sCalcType As String
    Dim lCalcSave As Long
    Dim bIterSave As Boolean

    On Error GoTo Errhandl

    dTime = MicroTimer
    lCalcSave = Application.Calculation
    bIterSave = Application.Iteration

    If Application.Calculation <> xlCalculationManual Then
    Application.Calculation = xlCalculationManual
    End If

    Select Case jMethod
    Case 1

    If Application.Iteration <> False Then
    Application.Iteration = False
    End If

    If Selection.Count > 1000 Then
    Set oRng = Intersect(Selection, Selection.Parent.UsedRange)
    Else
    Set oRng = Selection
    End If

    For Each oCell In oRng

    If oCell.HasArray Then
    If oArrRange Is Nothing Then
    Set oArrRange = oCell.CurrentArray
    End If

    If Intersect(oCell, oArrRange) Is Nothing Then
    Set oArrRange = oCell.CurrentArray
    Set oRng = Union(oRng, oArrRange)
    End If

    End If

    Next oCell

    sCalcType = "Tiempo en calcular " & CStr(oRng.Count) & _
    " Celdas del rango seleccionado: "

    Case 2
    sCalcType = "Tiempo en abrir la hoja activa " & ActiveSheet.Name & ": "

    Case 3
    sCalcType = "Recálculo tiempo en abrir el libro: "

    Case 4
    sCalcType = "Tiempo en abrir el libro: "

    End Select

    dTime = MicroTimer
    Select Case jMethod

    Case 1
    If Val(Application.Version) >= 12 Then
    oRng.CalculateRowMajorOrder
    Else
    oRng.Calculate
    End If

    Case 2
    ActiveSheet.Calculate

    Case 3
    Application.Calculate

    Case 4
    Application.CalculateFull

    End Select

    dTime = MicroTimer – dTime
    On Error GoTo 0
    dTime = Round(dTime, 5)
    MsgBox sCalcType & " " & CStr(dTime) & " Segundos.", _
    vbOKOnly + vbInformation, "Test optimización Excel"

    Finish:

    If Application.Calculation <> lCalcSave Then
    Application.Calculation = lCalcSave
    End If

    If Application.Iteration <> bIterSave Then
    Application.Calculation = bIterSave
    End If

    Exit Sub

      

    Errhandl:

    On Error GoTo 0
    MsgBox "Error en el cálculo de tiempo " & sCalcType, _
    vbOKOnly + vbCritical, "Test optimización Excel"
    GoTo Finish

    End Sub

  5. Guardar el libro con la extensión *.xlsm. Libro habilitado para macros.

  • ANTES DE EJECUTAR LA MACRO

  1. Ir a: Cinta de opciones / Pestaña: Archivo.

  2. Ir a: Categoría Opciones. Mostrará la ventana de opciones de Excel.

  3. Ir a: Categoría Fórmulas / Opciones de cálculo / Activar la casilla: Manual.

  4. Pulsar el botón Aceptar. Cierra la ventana y elimina el cálculo automático.

  • EJECUTAR MACRO: TIEMPO_LIBRO

Identifica el tiempo necesario para calcular todas las fórmulas del libro. Será el tiempo más elevado de todas las macros.

  1. Ejecutar la macro "Tiempo_Libro".

  2. Verificar como muestra mensaje con el tiempo empleado en analizar todo el libro, en segundos.

Mensaje de macro mostrando el texto Tiempo en abrir el libro, XX segundos.
  • EJECUTAR MACRO: TIEMPO_LIBRO_RECALCULAR

Volver a calcular inmediatamente después de un cálculo, mostrará un mejor tiempo.

  1. Ejecutar la macro "Tiempo_Libro_Recalcular".

  2. Verificar como muestra mensaje con el tiempo empleado en analizar todo el libro, por segunda vez, en segundos.

Mensaje de macro mostrando el texto Recálculo tiempo en abrir el libro XX segundos.
  • EJECUTAR MACRO: TIEMPO_HOJA

Identifica el tiempo de carga para calcular la hoja activa del libro.

Debido a que sólo se vuelven a calcular el libro, este modo, la hora de volver a calcular para cada hoja de cálculo. Debe habilitar para determinar cuáles son las hojas de cálculo del problema.

  1. Abrir la hoja deseada. Para verificar su tiempo de carga.

  2. Ejecutar la macro "Tiempo_Hoja".

  3. Verificar como muestra mensaje con el tiempo empleado en analizar la hoja activa, en segundos.

  4. Repetir el proceso para el resto de la hoja. Para determinar cuáles son las hojas que producen los problemas.

Mensaje de macro mostrando el texto Tiempo en abrir la hoja activa, HojaX: XX segundos.
  • EJECUTAR MACRO: TIEMPO_RANGO

Identifica el tiempo de carga para calcular el rango seleccionado de la hoja activa.

  1. Ir a: la hoja que presenta anomalías.

  2. Seleccionar un rango, varias filas o varias columnas. Identificando el área que presenta anomalías, para posteriormente revisar las fórmulas que producen el problema.

  3. Ejecutar la macro "Tiempo_Rango".

  4. Verificar como muestra mensaje con el tiempo empleado en analizar todo el libro, en segundos.

  5. Repetir el proceso para el resto de rangos. Hasta analizar toda la hoja.

Mensaje de macro mostrando el texto Tiempo en calcular x celdas del rango seleccionado XX segundos.

  

Nivel de dificultad: Experto VBA-Macros Excel Experto VBA – Macros

3 comentarios en “Como optimizar tus fórmulas, hojas y libros Excel

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *