Rendimiento de Excel: mejorar el rendimiento del cálculo
Se aplica a: Excel | Excel 2013 | Excel 2016 | VBA
La "cuadrícula grande" de 1 millón de filas y 16 000 columnas en Office Excel 2016, junto con el aumento de otros muchos límites, aumenta enormemente el tamaño de las hojas de cálculo que puede crear con respecto a versiones anteriores de Excel. Ahora una única hoja de cálculo de Excel puede contener más de 1 000 veces la cantidad de celdas que en versiones anteriores.
En versiones anteriores de Excel, muchas personas creaban hojas de cálculo lentas y las hojas de cálculo más grandes suelen calcular más lentamente que las pequeñas. Con la introducción de la "Gran cuadrícula" en Excel 2007, el rendimiento importa mucho. La lentitud en el cálculo y las tareas de manipulación de datos, como ordenar y filtrar, hace que resulte más difícil para los usuarios concentrarse en la tarea en curso, y la falta de concentración aumenta el número de errores.
Las versiones recientes de Excel introdujeron varias características para ayudarle a controlar este aumento de capacidad, como la capacidad de usar más de un procesador a la vez para los cálculos y las operaciones comunes de conjunto de datos, como la apertura, ordenación y actualización de libros. El cálculo multiproceso puede reducir sustancialmente el tiempo de cálculo de la hoja de cálculo. Sin embargo, el factor más importantes que influye en la velocidad de cálculo de Excel sigue siendo la forma en que la hoja de cálculo está diseñada y creada.
Puede modificar la mayoría de las hojas de cálculo de cálculo lento para calcular decenas, cientos o incluso miles de veces más rápido. Al identificar, medir y mejorar los obstáculos de cálculo en las hojas de cálculo, puede acelerar el proceso de cálculo.
La importancia de la velocidad de cálculo
La baja velocidad de cálculo afecta a la productividad y aumenta los errores del usuario. La productividad del usuario y la capacidad de centrarse en una tarea se deteriora a medida que el tiempo de respuesta aumenta.
Excel tiene dos modos de cálculo principales que le permiten controlar cuándo se produce el cálculo:
Cálculo automático: las fórmulas se actualizan automáticamente cuando se realiza un cambio.
Cálculo manual: las fórmulas se actualizan cuando se solicita (por ejemplo, al presionar F9).
En los tiempos de cálculo de menos de una décima de segundo, para los usuarios parece que el sistema está respondiendo al instante. Pueden usar el cálculo automático incluso cuando escriben datos.
Entre una décima de segundo y un segundo, los usuarios pueden mantener correctamente el flujo de ideas, aunque notarán el retraso en el tiempo de respuesta.
A medida que aumenta el tiempo de cálculo (normalmente entre 1 y 10 segundos), los usuarios deben cambiar a cálculo manual cuando escriben datos. Los errores del usuario y los niveles de molestia empiezan a aumentar, especialmente en tareas repetitivas, y es difícil mantener la concentración.
Para tiempos de cálculo mayores de 10 segundos, los usuarios pasan a estar impacientes y normalmente pasan a otras tareas mientras esperan. Esto puede provocar problemas cuando el cálculo es parte de una secuencia de tareas y el usuario pierde el hilo.
Información sobre los métodos de cálculo de Excel
Para mejorar el rendimiento de cálculo en Excel, debe comprender los métodos de cálculo disponibles y cómo controlarlos.
Cálculo completo y dependencias de actualización
El motor inteligente de recálculo en Excel intenta minimizar el tiempo de cálculo mediante el seguimiento continuo de precedentes y dependencias de cada fórmula (las celdas a las que hace referencia la fórmula) y los cambios realizados desde el último cálculo. En la próxima actualización del cálculo, Excel solo vuelve a calcular lo siguiente:
Celdas, fórmulas, valores o nombres que han cambiado o se han marcado porque necesiten actualizarse.
Celdas dependientes de otras celdas, fórmulas, nombres o valores que necesitan actualizarse.
Funciones variables y los formatos condicionales visibles.
Excel sigue calculando celdas que dependen de celdas calculadas anteriormente, incluso si el valor de la celda calculada anteriormente no cambia cuando se calcula.
Puesto que en la mayoría de los casos solo cambia una parte de los datos de entrada o unas pocas fórmulas entre cálculos, esta actualización inteligente normalmente tarda solo una fracción del tiempo que se tardaría un cálculo completo de todas las fórmulas.
En el modo de cálculo manual, puede activar esta actualización inteligente presionando F9. Puede forzar un cálculo completo de todas las fórmulas presionando Ctrl+Alt+F9 o puede forzar una reconstrucción completa de las dependencias y un cálculo completo presionando Mayús+Ctrl+Alt+F9.
Proceso de cálculo
Las fórmulas de Excel que hacen referencia a otras celdas se pueden poner antes o después de las celdas de referencia (referencia hacia delante o referencia hacia atrás). Esto es porque Excel no calcula las celdas en un orden fijo ni por fila o columna. En su lugar, Excel determina dinámicamente la secuencia de cálculo en función de una lista de todas las fórmulas para calcular (la cadena de cálculo) y la información de dependencia sobre cada fórmula.
Excel tiene fases de cálculo diferentes:
Crear la cadena inicial de cálculo y determinar dónde empezar a calcular. Esta fase se produce cuando el libro se carga en la memoria.
Seguir las dependencias, marcar las celdas sin calcular y actualizar la cadena de cálculo. Esta fase se ejecuta en cada entrada o cambio de celda, incluso en el modo de cálculo manual. Normalmente, este paso se ejecuta tan rápido que no lo nota, pero en casos complejos, pueden tardar en responder.
Calcular todas las fórmulas. Como parte del proceso de cálculo, Excel reordena y reestructura la cadena de cálculo para optimizar futuros cálculos.
Actualizar las partes visibles de la ventana de Excel.
La tercera fase se ejecuta en cada cálculo o actualización. Excel intenta calcular cada fórmula en la cadena de cálculo una a una, pero si una fórmula depende de una o varias fórmulas que aún no se han calculado, la fórmula se envía hacia el final de la cadena para calcularse de nuevo más tarde. Esto significa que una fórmula se puede calcular varias veces en cada actualización.
La segunda vez que se calcula un libro suele ser considerablemente más rápida que la primera vez. Esto ocurre por varias razones:
Normalmente, Excel actualiza solo las celdas que han cambiado y sus celdas dependientes.
Excel almacena y vuelve a usar la secuencia de cálculo más reciente para que pueda omitir la mayor parte del tiempo que se utiliza para determinar el orden de cálculo.
En equipos de varios núcleos, Excel intenta optimizar la forma en que los cálculos están distribuidos entre los núcleos según los resultados del cálculo anterior.
En una sesión de Excel, tanto Windows como Excel almacenan en caché programas y datos usados recientemente para acelerar el acceso.
Calcular libros, hojas de cálculo y rangos
Puede controlar lo que se calcula mediante los diferentes métodos de cálculo de Excel.
Calcular todos los libros abiertos
Cada actualización y cálculo completo calcula todos los libros abiertos actualmente, resuelve las dependencias dentro de cada libro y entre libros y hojas de cálculo y restablece todas las celdas sin calcular anteriormente (incorrectas) como calculadas.
Calcular las hojas seleccionadas
También puede recalcular solo las hojas de cálculo seleccionadas mediante Mayús+F9. Esto resuelve las dependencias de la hoja de cálculo y restablece todas las celdas no calculadas (desfasadas) anteriormente según se calculó.
En versiones anteriores de Excel, el comportamiento era diferente y las celdas desfasadas no se establecieron como calculadas después de que se completara el cálculo. Si las funciones definidas por el usuario se basaban en este comportamiento, estas funciones se deben convertir en volátiles en su lugar, como se explica en la sección Funciones volátiles de este artículo.
Un rango de celdas
Excel también permite el cálculo de un rango de celdas usando los métodos Range.CalculateRowMajorOrder y Range.Calculate de Visual Basic para Aplicaciones (VBA):
Range.CalculateRowMajorOrder calcula el rango de izquierda a derecha y de arriba a abajo, ignorando todas las dependencias.
Range.Calculate calcula el rango resolviendo todas las dependencias del rango.
Puesto que CalculateRowMajorOrder no soluciona dependencias dentro del rango que se calcula, suele ser considerablemente más rápido que Range.Calculate. Sin embargo, debería usarse con cuidado porque puede no devolver los mismos resultados que Range.Calculate.
Range.Calculate es una de las herramientas más útiles en Excel para optimizar el rendimiento, ya que puede usarse para medir el tiempo y comparar la velocidad de distintas fórmulas de cálculo.
Para obtener más información, vea Rendimiento de Excel: mejoras de rendimiento y límites.
Funciones volátiles
Una función volátil siempre se recalcula en cada actualización, aunque no parezca tener ningún precedentes cambiado. El uso de muchas funciones variables ralentiza cada actualización, pero afecta en un cálculo completo. Puede hacer una función definida por el usuario volátil incluyendo Application.Volatile en el código de la función.
Algunas de las funciones integradas de Excel son obviamente volátiles: RAND(), NOW(), TODAY(). En otras es menos evidente: OFFSET(), CELL(), INDIRECT(), INFO().
Algunas funciones que anteriormente se han documentado como volátiles en realidad no lo son: INDEX(), ROWS(), COLUMNS(), AREAS().
Acciones volátiles
Las acciones volátiles son acciones que desencadenan un nuevo cálculo e incluyen las siguientes:
- Hacer clic en un divisor de fila o columna en modo automático.
- Insertar o eliminar filas, columnas o celdas en una hoja.
- Agregar, cambiar o eliminar nombres definidos.
- Cambiar el nombre o la posición de la hoja de cálculo en el modo automático.
- Filtrar, ocultar o mostrar filas.
- Abrir un libro en modo automático. Si el libro se ha calculado por última vez en una versión diferente de Excel, abrir el libro normalmente ocasiona un cálculo completo.
- Guardar un libro en modo manual si la opción Calcular antes de guardar está seleccionada.
Circunstancias de evaluación de fórmulas y nombres
Una fórmula o parte de una fórmula se evalúa (calcula) inmediatamente, incluso en el modo de cálculo manual, al realizar uno de estos procedimientos:
- Escribir o editar la fórmula.
- Escribir o editar la fórmula con el Asistente de funciones.
- Escribir la fórmula como argumento en el Asistente de funciones.
- Seleccionar la fórmula en la barra de fórmulas y presionar F9 (presione Esc para deshacer y revertir la fórmula) o hacer clic en Evaluar fórmula.
Una fórmula se marca como no calculada cuando hace referencia a (depende de) una celda o fórmula que tiene una de estas condiciones:
- Se ha insertado.
- Se ha cambiado.
- Está en una lista de Autofiltro y se ha habilitado la lista desplegable de criterios.
- Se ha marcado como no calculada.
Una fórmula que está marcada como no calculada se evalúa cuando se calcula o se vuelve a calcular la hoja de cálculo, libro o instancia de Excel que la contiene.
Las condiciones que hacen que un nombre definido se evalúe son distintas que para una fórmula en una celda:
- Un nombre definido se evalúa cada vez que se evalúa una fórmula que le hace referencia, por lo que usar un nombre en varias fórmulas puede hacer que el nombre se evalúe varias veces.
- Los nombres a los que no hace referencia ninguna fórmula no se calculan, ni siquiera en un cálculo completo.
Tablas de datos
Las tablas de datos de Excel (pestaña >Datos Herramientas de datos grupo > Tabla dedatos de análisis> de hipótesis) no deben confundirse con la característica de tabla (la pestaña>Inicio Estilos de grupo >Formato como tabla o Insertar tabla >grupo>tablas). Las tablas de datos de Excel realizan varios cálculos del libro, cada uno impulsado por los diferentes valores de la tabla. Excel calcula primero el libro normalmente. Después, para cada par de valores de fila y columna, sustituye los valores, hace un nuevo cálculo de subproceso único y almacena los resultados en la tabla de datos.
La actualización de tabla de datos siempre usa un solo procesador.
Las tablas de datos ofrecen una forma cómoda para calcular varias variaciones y ver y comparar los resultados de las variaciones. Use la opción de cálculo Automático excepto en tablas para evitar que Excel desencadene automáticamente los múltiples cálculos en cada cálculo mientras sigue calculando todas las fórmulas dependientes, a excepción de las tablas.
Control de las opciones de cálculo
Excel tiene una serie de opciones que le permiten controlar la forma en que calcula. Puede cambiar las opciones más usadas en Excel usando el grupo Cálculo en la pestaña Fórmulas en la cinta de opciones.
Figura 1. Grupo de cálculo en la ficha Fórmulas
Para ver más opciones de cálculo de Excel, en la ficha Archivo, haga clic en Opciones. En el cuadro de diálogo Opciones de Excel, haga clic en la ficha Fórmulas.
Figura 2. Opciones de cálculo en la ficha Fórmulas en Opciones de Excel
Muchas opciones de cálculo (Automático, Automático excepto para tablas de datos, Manual, Volver a calcular libro antes de guardarlo) y la configuración de la iteración (Habilitar cálculo iterativo, Iteraciones máximas, Cambio máximo) funcionan en el nivel de aplicación en lugar de en el nivel de libro (son las mismas para todos los libros abiertos).
Para ver las opciones de cálculo avanzadas de Excel, en la ficha Archivo, haga clic en Opciones. En el cuadro de diálogo Opciones de Excel, haga clic en Avanzadas . En la sección Fórmulas establezca las opciones de cálculo.
Figura 3. Opciones de cálculo avanzadas
Cuando inicia Excel o cuando se está ejecutando sin libros abiertos, el modo de cálculo inicial y las opciones de iteración se establecen a partir del primer libro que no sea plantilla ni complemento que abra. Esto significa que la configuración del cálculo en libros abiertos más adelante se ignora, aunque, por supuesto, puede cambiar manualmente la configuración de Excel en cualquier momento. Al guardar un libro, la configuración de cálculo actual se almacena en el libro.
Cálculo automático
El modo de cálculo automático significa que Excel actualiza automáticamente todos los libros abiertos en todos los cambios y al abrir un libro. Normalmente, cuando abre un libro en el modo automático y Excel lo actualiza, no verá el nuevo cálculo porque nada ha cambiado desde que se guardó el libro.
Puede que note este cálculo al abrir un libro en una versión de Excel posterior a la que usó la última vez que se calculó el libro (por ejemplo, Excel 2016 y Excel 2013). Puesto que los motores de cálculo de Excel son diferentes, Excel realiza un cálculo completo cuando se abre un libro que se guardó con una versión anterior de Excel.
Cálculo manual
El modo de cálculo manual significa que Excel actualiza todos los libros abiertos solo cuando se solicita presionando F9 o Ctrl+Alt+F9, o cuando guarda un libro. Para los libros que tardan más de una fracción de un segundo en actualizarse, debe establecer el cálculo en modo manual para evitar un retraso al realizar cambios.
Excel le indica cuando un libro en el modo manual necesita actualizarse mostrando Calcular en la barra de estado. Esta barra de estado también muestra Calcular si el libro contiene referencias circulares y se selecciona la opción de iteración.
Configuración de iteración
Si tiene referencias circulares intencionadas en el libro, la configuración de iteración le permite controlar el número máximo de veces que el libro se actualiza (iteraciones) y los criterios de convergencia (cambio máximo: cuándo parar). Desactive la casilla de iteración para que, si tiene referencias circulares accidentales, Excel le advierta y no intente resolverlos.
Propiedad de libro ForceFullCalculation
Al establecer esta propiedad de libro como True, la actualización inteligente de Excel se desactiva y cada vez que se vuelve a calcular se calculan todas las fórmulas de todos los libros abiertos. Para algunos libros complejos, el tiempo necesario para crear y mantener los árboles de dependencia necesarios para la actualización inteligente es mayor que el tiempo ahorrado con ella.
Si el libro tarda demasiado tiempo en abrirse o si realizar cambios pequeños toma mucho tiempo incluso en el modo de cálculo manual, puede ser preferible intentar usar ForceFullCalculation.
Calcular aparecerá en la barra de estado si la propiedad ForceFullCalculation del libro se ha establecido en True.
Puede controlar esta configuración con el VBE (ALT+F11), seleccionando ThisWorkbook en el Explorador de proyectos (Ctrl+R) y mostrando la Ventana Propiedades (F4).
Figura 4. Establecer la propiedad Workbook.ForceFullCalculation
Hacer que los libros calculen más rápido
Use los siguientes métodos y pasos para hacer que los libros calculen más rápido.
Velocidad del procesador y varios núcleos
En la mayoría de las versiones de Excel, un procesador más rápido, por supuesto, permitirá un cálculo de Excel más rápido. El motor de cálculo multiproceso introducido en Excel 2007 permite a Excel aprovechar los sistemas con varios procesadores y puede esperar mejoras de rendimiento importantes con la mayoría de libros.
Para la mayoría de los libros grandes, la mejora en el rendimiento de cálculo aumenta de forma casi lineal con el número de procesadores físicos. Sin embargo, el "hyper-threading" de procesadores físicos solo genera una pequeña ganancia de rendimiento.
Para obtener más información, vea Rendimiento de Excel: mejoras de rendimiento y límites.
RAM
La paginación a un archivo de paginación de memoria virtual es lenta. Debe tener suficiente RAM física para los libros, Excel y el sistema operativo. Si suele tener actividad en el disco duro durante el cálculo y no está usando funciones definidas por el usuario que activen la actividad del disco, necesita más memoria RAM.
Como se ha mencionado, las versiones recientes de Excel pueden realizar un uso eficaz de grandes cantidades de memoria y la versión de 32 bits de Excel 2007 y Excel 2010 puede controlar un único libro o una combinación de libros con un máximo de 2 GB de memoria.
Las versiones de 32 bits de Excel 2013 y Excel 2016 que usan la característica reconocimiento de direcciones grandes (LAA) pueden usar hasta 3 o 4 GB de memoria, dependiendo de la versión de Windows instalada. La versión de 64 bits de Excel puede controlar libros más grandes. Para obtener más información, vea la sección "Grandes conjuntos de datos, LAA y Excel de 64 bits" en rendimiento de Excel: mejoras de rendimiento y límite.
Una directriz aproximada para el cálculo eficaz es tener suficiente RAM para el conjunto más grande de libros que necesita tener abiertos al mismo tiempo, más entre 1 y 2 GB para Excel y el sistema operativo, más RAM adicional para otras aplicaciones en ejecución.
Medir el tiempo de cálculo
Para que los libros calculen más rápido, debe ser capaz de medir con precisión el tiempo de cálculo. Necesita un temporizador que sea más rápido y preciso que la función Time de VBA. La función MICROTIMER() que se muestra en el ejemplo siguiente usa llamadas API de Windows al temporizador de alta resolución del sistema. Pueden medir intervalos de tiempo de un pequeño número de microsegundos. Tenga en cuenta que como Windows es un sistema operativo multitarea y la segunda vez que se calcula un elemento será más rápido que la primera vez, el tiempo obtenido normalmente no se repetirá exactamente. Para lograr la mejor precisión, mida las tareas de cálculo varias veces y calcule el promedio de los resultados.
Para obtener más información sobre cómo el Editor de Visual Basic puede afectar significativamente al rendimiento de la función definida por el usuario de VBA, consulte la sección "Funciones de VBA definidas por el usuario más rápidas" en Rendimiento de Excel: Sugerencias para salvar los obstáculos de rendimiento.
#If VBA7 Then
Private Declare PtrSafe Function getFrequency Lib "kernel32" Alias _
"QueryPerformanceFrequency" (cyFrequency As Currency) As Long
Private Declare PtrSafe Function getTickCount Lib "kernel32" Alias _
"QueryPerformanceCounter" (cyTickCount As Currency) As Long
#Else
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
#End If
Function MicroTimer() As Double
'
' Returns seconds.
Dim cyTicks1 As Currency
Static cyFrequency As Currency
'
MicroTimer = 0
' Get frequency.
If cyFrequency = 0 Then getFrequency cyFrequency
' Get ticks.
getTickCount cyTicks1
' Seconds
If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency
End Function
Para medir el tiempo de cálculo, debe llamar al método de cálculo adecuado. Estas subrutinas le muestran el tiempo de cálculo para un rango, el tiempo de cálculo de una hoja o de todos los libros abiertos, o el tiempo de cálculo completo para todos los libros abiertos.
Copie todas estas subrutinas y funciones en un módulo de VBA estándar. Presione ALT+F11 para abrir el editor de VBA. En el menú Insertar, seleccione Módulo y, a continuación, copie el código en el módulo.
Sub RangeTimer()
DoCalcTimer 1
End Sub
Sub SheetTimer()
DoCalcTimer 2
End Sub
Sub RecalcTimer()
DoCalcTimer 3
End Sub
Sub FullcalcTimer()
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
' Initialize
dTime = MicroTimer
' Save calculation settings.
lCalcSave = Application.Calculation
bIterSave = Application.Iteration
If Application.Calculation <> xlCalculationManual Then
Application.Calculation = xlCalculationManual
End If
Select Case jMethod
Case 1
' Switch off iteration.
If Application.Iteration <> False Then
Application.Iteration = False
End if
' Max is used range.
If Selection.Count > 1000 Then
Set oRng = Intersect(Selection, Selection.Parent.UsedRange)
Else
Set oRng = Selection
End If
' Include array cells outside selection.
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 = "Calculate " & CStr(oRng.Count) & _
" Cell(s) in Selected Range: "
Case 2
sCalcType = "Recalculate Sheet " & ActiveSheet.Name & ": "
Case 3
sCalcType = "Recalculate open workbooks: "
Case 4
sCalcType = "Full Calculate open workbooks: "
End Select
' Get start time.
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
' Calculate duration.
dTime = MicroTimer - dTime
On Error GoTo 0
dTime = Round(dTime, 5)
MsgBox sCalcType & " " & CStr(dTime) & " Seconds", _
vbOKOnly + vbInformation, "CalcTimer"
Finish:
' Restore calculation settings.
If Application.Calculation <> lCalcSave Then
Application.Calculation = lCalcSave
End If
If Application.Iteration <> bIterSave Then
Application.Iteration = bIterSave
End If
Exit Sub
Errhandl:
On Error GoTo 0
MsgBox "Unable to Calculate " & sCalcType, _
vbOKOnly + vbCritical, "CalcTimer"
GoTo Finish
End Sub
Para ejecutar las subrutinas en Excel, presione ALT+F8. Seleccione la subrutina que desee y, a continuación, haga clic en Ejecutar.
Figura 5. La ventana de macros de Excel que muestra los temporizadores de cálculo
Buscar y priorizar obstáculos de cálculo
La mayoría de libros con cálculos lentos tienen solo unas pocas áreas problemáticas u obstáculos que consumen la mayoría del tiempo de cálculo. Si no sabe ya dónde se encuentran, use el método de obtención de detalles descrito en esta sección para encontrarlos. Si sabe dónde se encuentran, debe medir el tiempo de cálculo que usa cada obstrucción para poder establecer prioridades en el trabajo para eliminarlos.
Enfoque de obtención de detalles para encontrar obstrucciones
El enfoque de obtención de detalles empieza por medir el tiempo de cálculo del libro, el cálculo de cada hoja de cálculo y los bloques de fórmulas en hojas de cálculo lentas. Realice cada paso en orden y anote los tiempos de cálculo.
Para buscar obstrucciones con el método de obtención de detalles
Asegúrese de que tiene un único libro abierto y de que no se ejecutan otras tareas.
Establezca el cálculo en manual.
Realice una copia de seguridad del libro.
Abra el libro que contiene las macros de temporizador de cálculo o agréguelas al libro.
Compruebe el rango utilizado presionando Ctrl+End en cada hoja por separado.
Esto muestra dónde está la última celda utilizada. Si se trata de una posición más allá de la que esperaba, considere la posibilidad de eliminar las columnas y filas sobrantes y de guardar el libro. Para obtener más información, consulte la sección "Reducir el rango utilizado" en Rendimiento de Excel: Sugerencias para salvar los obstáculos de rendimiento.
Ejecute la macro FullCalcTimer.
El tiempo para calcular todas las fórmulas del libro suele ser el peor de los casos.
Ejecute la macro RecalcTimer.
Normalmente, una actualización después de un cálculo completo le muestra el mejor tiempo posible.
Calcule la volatilidad del libro como la relación de tiempo de actualización con el tiempo de cálculo completo.
Esto mide el grado en que las fórmulas volátiles y la evaluación de la cadena de cálculo son obstrucciones.
Active cada hoja y ejecute la macro SheetTimer por turnos.
Dado que acaba de volver a calcular el libro, esto le da el tiempo de actualización de cada hoja de cálculo. Esto debería permitirle determinar cuáles son las hojas de cálculo con problemas.
Ejecute la macro RangeTimer en bloques de fórmulas seleccionados.
Para cada hoja de cálculo con problemas, divida las columnas o las filas en un pequeño número de bloques.
Seleccione cada bloque uno a uno y ejecute la macro RangeTimer en el bloque.
Si es necesario, para profundizar, subdivida cada bloque en un número menor de bloques.
Establezca la prioridad de los obstáculos.
Acelerar cálculos y reducir obstáculos
No es el número de fórmulas o el tamaño de un libro lo que aumenta el tiempo de cálculo. Es el número de operaciones de cálculo y referencias de celda, junto con la eficacia de las funciones que se usan.
Puesto que la mayoría de las hojas de cálculo se crean mediante la copia de fórmulas que contienen una combinación de referencias absolutas y relativas, normalmente contienen un gran número de fórmulas que contienen cálculos y referencias repetidos o duplicados.
Evite megafórmulas complejas y fórmulas de matriz. En general, es mejor tener más filas y columnas y menos cálculos complejos. Esto proporciona a la actualización inteligente y al cálculo multiproceso de Excel una mejor oportunidad para optimizar los cálculos. También es más fácil de comprender y depurar. Los siguientes son algunas reglas para ayudar a acelerar los cálculos del libro.
Primera regla: quitar cálculos innecesarios, duplicados y repetidos
Busque cálculos duplicados, repetidos e innecesarios y calcule aproximadamente el número de referencias de celda y cálculos que necesita Excel para calcular el resultado de esta obstrucción. Piense en cómo puede obtener el mismo resultado con menos referencias y cálculos.
Normalmente, esto implica uno o varios de los pasos siguientes:
Reducir el número de referencias en cada fórmula.
Mueva los cálculos repetidos a una o más celdas auxiliares y, a continuación, haga referencia a las celdas auxiliares de las fórmulas originales.
Use filas y columnas adicionales para calcular y almacenar los resultados intermedios una vez, de modo que pueda volver a utilizarlos en otras fórmulas.
Segunda regla: usar la función más eficiente posible
Cuando encuentre un obstáculo que implique una función o fórmulas de matriz, determine si hay una forma más eficaz de obtener el mismo resultado. Por ejemplo:
Las búsquedas en datos ordenados pueden ser decenas o centenares de veces más eficientes que las búsquedas en datos no ordenados.
Las funciones definidas por el usuario de VBA son normalmente más lentas que las funciones integradas de Excel (aunque las funciones de VBA escritas cuidadosamente pueden ser rápidas).
Minimice el número de celdas usadas en funciones como SUM y sumar.Si. El tiempo de cálculo es proporcional al número de celdas usadas (se ignoran las celdas sin usar).
Considere la posibilidad de reemplazar las fórmulas de matriz lentas por funciones definidas por el usuario.
Tercera regla: hacer un buen uso de la actualización inteligente y el cálculo multiproceso
Cuanto mejor uso haga de la actualización inteligente y el cálculo multiproceso en Excel, menor es el procesamiento que debe realizarse cada vez que Excel actualiza, de este modo:
Evite funciones volátiles como INDIRECTO y DESREF donde pueda, a menos que sean significativamente más eficientes que las alternativas. (El uso bien diseñado de DESREF suele ser rápido).
Minimice el tamaño de los rangos que usa en fórmulas y funciones de matriz.
Divida las fórmulas de matriz y megafórmulas en filas y columnas auxiliares independientes.
Evite las funciones de subproceso único:
- PHONETIC
- CELL cuando se usa el argumento "format" o "address"
- INDIRECT
- GETPIVOTDATA
- CUBEMEMBER
- CUBEVALUE
- CUBEMEMBERPROPERTY
- CUBESET
- CUBERANKEDMEMBER
- CUBEKPIMEMBER
- CUBESETCOUNT
- ADDRESS donde se proporciona el quinto parámetro (sheet_name)
- Cualquier función de base de datos (BDSUMA, BDPROMEDIO, etc.) que haga referencia a una tabla dinámica
- ERROR.TYPE
- HYPERLINK
- Funciones definidas por el usuario de complementos de VBA y COM
Evite el uso iterativo de tablas de datos y las referencias circulares: estos dos cálculos siempre se calculan en un único subproceso.
Cuarta regla: mida el tiempo y pruebe cada cambio
Algunos de los cambios que realiza pueden sorprenderle, ya sea mostrando una respuesta que no esperaba o calculando más lentamente de lo esperado. Por lo tanto, debería medir tiempo y probar cada cambio, como sigue:
Mida el tiempo de la fórmula que quiere cambiar mediante la macro RangeTimer.
Realice el cambio.
Mida el tiempo de la fórmula cambiada con la macro RangeTimer.
Compruebe que la fórmula modificada todavía proporciona la respuesta correcta.
Ejemplos de reglas
Las secciones siguientes proporcionan ejemplos de cómo usar las reglas para acelerar el cálculo.
Sumas de período hasta la fecha
Por ejemplo, debe calcular las sumas de "período hasta la fecha" de una columna que contiene 2 000 números. Supongamos que la columna A contiene los números y que la columna B y la columna C deben contener los totales de "período hasta la fecha".
Podría escribir la fórmula utilizando SUMA, que es una función eficaz.
B1=SUM($A$1:$A1)
B2=SUM($A$1:$A2)
Figura 6. Ejemplo de fórmulas de suma período-a-fecha
Copie la fórmula hasta B2000.
¿Cuántas referencias de celda agrega SUMA en total? B1 hace referencia a una celda y B2000 hace referencia a 2 000 celdas. El promedio es 1 000 referencias por celda, por lo que el número total de referencias es 2 millones. Al seleccionar las 2 000 fórmulas y utilizar la macro RangeTimer se muestra que las 2 000 fórmulas en la columna B se calculan en 80 milisegundos. La mayoría de estos cálculos están duplicados muchas veces: SUMA agrega A1 a A2 en cada fórmula de B2:B2000.
Puede eliminar esta duplicación si escribe las fórmulas de la siguiente manera.
C1=A1
C2=C1+A1
Copie esta fórmula hacia abajo hasta C2000.
¿Ahora cuántas referencias de celda se agregan en total? Cada fórmula, excepto la primera fórmula, utiliza dos referencias de celda. Por lo tanto, el total es 1999 * 2 + 1 = 3 999. Este es un factor de 500 menos referencias de celda.
RangeTimer indica que las 2 000 fórmulas de la columna C se calculan en 3,7 milisegundos con respecto a los 80 milisegundos de la columna B. Este cambio tiene un factor de mejora del rendimiento de solo 80/3,7=22 en lugar de 500 porque hay una pequeña sobrecarga por fórmula.
Control de errores
Si tiene una fórmula de cálculo intensivo en la que desea que el resultado se muestre como cero si se produce un error (esto ocurre con frecuencia con las búsquedas de coincidencia exacta), puede escribir esto de varias formas.
Se puede escribir como una única fórmula, que es lento:
B1=IF(ISERROR(time expensive formula),0,time expensive formula)Puede escribirlo como dos fórmulas, que es rápido:
A1=time expensive formulaB1=IF(ISERROR(A1),0,A1)También puede usar la función SI.ERROR, que está diseñada para ser rápida y sencilla y es una única fórmula:
B1=IFERROR(time expensive formula,0)
Recuento único dinámico
Figura 7. Ejemplo de lista de datos para recuento único
Si tiene una lista de 11 000 filas de datos en la columna A, que cambia con frecuencia, y necesita una fórmula que calcule dinámicamente el número de elementos únicos de la lista y omita los espacios en blanco, a continuación se muestran varias soluciones posibles.
Fórmulas de matriz (use Ctrl+Mayús+Entrar); RangeTimer indica que esto le llevará 13,8 segundos.
{=SUM(IF(LEN(A2:A11000)>0,1/COUNTIF(A2:A11000,A2:A11000)))}SUMAPRODUCTO normalmente calcula más rápido que una fórmula de matriz equivalente. Esta fórmula tarda 10,0 segundos y proporciona un factor de mejora de 13,8/10,0 = 1,38, que es mejor, pero no es lo suficientemente bueno.
=SUMPRODUCT((A2:A11000<>"")/COUNTIF(A2:A11000,A2:A11000&""))Funciones definidas por el usuario. En el ejemplo de código siguiente se muestra una función definida por el usuario de VBA que usa el hecho de que el índice de una colección debe ser único. Para obtener una explicación de algunas técnicas que usan, vea la sección acerca de las funciones definidas por el usuario en la sección "Usar funciones de forma eficaz" en Rendimiento de Excel: Sugerencias para salvar los obstáculos de rendimiento. Esta fórmula,
=COUNTU(A2:A11000), tarda solo 0,061 segundos. Esto le proporciona un factor de mejora de 13,8/0,061=226.Public Function COUNTU(theRange As Range) As Variant Dim colUniques As New Collection Dim vArr As Variant Dim vCell As Variant Dim vLcell As Variant Dim oRng As Range Set oRng = Intersect(theRange, theRange.Parent.UsedRange) vArr = oRng On Error Resume Next For Each vCell In vArr If vCell <> vLcell Then If Len(CStr(vCell)) > 0 Then colUniques.Add vCell, CStr(vCell) End If End If vLcell = vCell Next vCell COUNTU = colUniques.Count End FunctionAgregar una columna de fórmulas. Si observa el ejemplo anterior de datos, puede ver que está ordenado (Excel tarda 0,5 segundos en ordenar las 11 000 filas). Puede aprovechar esto agregando una columna de fórmulas que compruebe si los datos de esta fila son los mismos que los datos de la fila anterior. Si son diferentes, la fórmula devuelve 1. De lo contrario, devuelve 0.
Agregue esta fórmula a la celda B2.
=IF(AND(A2<>"",A2<>A1),1,0)Copie la fórmula y, a continuación, agregue una fórmula para agregar la columna B.
=SUM(B2:B11000)Un cálculo completo de todas estas fórmulas tardará 0,027 segundos. Esto le proporciona un factor de mejora de 13,8/0,027=511.
Conclusión
Excel le permite administrar de manera eficaz hojas de cálculo mucho más grandes y ofrece mejoras importantes en la velocidad de cálculo en comparación con las versiones anteriores. Al crear hojas de cálculo de gran tamaño, es fácil crearlas de forma que provoque lentitud en los cálculos. Las hojas de cálculo lentas aumentan los errores porque a los usuarios les resulta difícil mantener la concentración mientras se produce el cálculo.
Al usar un conjunto de técnicas sencillo, puede acelerar la mayoría de las hojas con cálculo lento en un factor de 10 o 100. También puede aplicar estas técnicas al diseñar y crear hojas de cálculo para asegurarse de que calculan rápidamente.
Vea también
- Rendimiento de Excel: Mejoras de rendimiento y límites
- Rendimiento de Excel: Sugerencias para salvar los obstáculos de rendimiento
- Centro de desarrollo de Excel
Soporte técnico y comentarios
¿Tiene preguntas o comentarios sobre VBA para Office o esta documentación? Vea Soporte técnico y comentarios sobre VBA para Office para obtener ayuda sobre las formas en las que puede recibir soporte técnico y enviar comentarios.
Comentarios
Próximamente: A lo largo de 2024 iremos eliminando gradualmente GitHub Issues como mecanismo de comentarios sobre el contenido y lo sustituiremos por un nuevo sistema de comentarios. Para más información, vea: https://aka.ms/ContentUserFeedback.
Enviar y ver comentarios de