Rendimiento de Excel: Sugerencias para salvar los obstáculos de rendimiento

Artículo
04/07/2023

Se aplica a: Excel | Excel 2013 | Office 2016 | VBA

Las siguientes sugerencias le servirán de ayuda para salvar muchos de los obstáculos de rendimiento que suelen producirse en Excel.

Optimizar referencias y vínculos

Obtenga información sobre cómo mejorar el rendimiento relacionado con los tipos de vínculos y referencias.

No usar referencias hacia delante ni referencias hacia atrás

Para aumentar la claridad y evitar errores, diseñe las fórmulas para que no hagan referencia (a la derecha o a la siguiente) a otras fórmulas o celdas. Normalmente, las referencias hacia delante no afectan al rendimiento de cálculo, salvo en casos extremos en el primer cálculo de un libro, donde se puede tardar más en establecer una secuencia de cálculo acertada si es necesario diferir el cálculo de un gran número de fórmulas.

Minimizar el uso de referencias circulares con iteración

El cálculo de referencias circulares con iteraciones es lento porque es necesario hacer varios cálculos y esos cálculos son de subproceso único. Habitualmente se pueden "deshacer" las referencias circulares con fórmulas algebraicas, de modo que no ya no sea necesario el cálculo iterativo. Por ejemplo, en los cálculos de flujo de efectivo e interés, pruebe a calcular el flujo de efectivo antes que el interés, calcule el interés y, después, calcule el flujo de efectivo incluyendo el interés.

Excel calcula las referencias circulares hoja por hoja sin tener en cuenta las dependencias. Por ello, el cálculo suele ser lento si las referencias circulares abarcan más de una hoja de cálculo. Pruebe a mover los cálculos circulares a una sola hoja de cálculo u optimice la secuencia de cálculo de la hoja de cálculo para evitar cálculos innecesarios.

Antes de que se inicien los cálculos iterativos, Excel tiene que volver a calcular el libro para identificar todas las referencias circulares y las celdas dependientes. Este proceso es equivalente a dos o tres iteraciones del cálculo.

Después de identificar las referencias circulares y las celdas dependientes, cada iteración requiere que Excel calcule todas las celdas de la referencia circular y, además, todas las celdas que dependen de las celdas de la cadena de referencias circulares, junto con las celdas volátiles y las celdas dependientes correspondientes. Si tiene un cálculo complejo que depende de las celdas de la referencia circular, puede ser más rápido aislarlo en un libro cerrado distinto y abrirlo para volver a hacer el cálculo cuando el cálculo circular haya convergido.

Es importante reducir el número de celdas en el cálculo circular y el tiempo de cálculo que toman estas celdas.

Evitar los vínculos entre libros

Evite los vínculos entre libros cuando sea posible; pueden ser lentos, se rompen fácilmente y no siempre es fácil encontrarlos y corregirlos.

Por lo general, aunque no siempre, es mejor usar unos pocos libros grandes que muchos libros más pequeños. No obstante, hay algunas excepciones, por ejemplo; cuando hay un gran número de cálculos de front-end que se recalculan tan pocas veces que tiene sentido colocarlos en un libro independiente, o cuando no se dispone de suficiente memoria RAM.

Pruebe a usar referencias de celdas directas y sencillas que funcionen en libros cerrados. Al hacer esto, podrá evitar que vuelvan a calcularse todos los libros vinculados al volver a calcular cualquier libro. Asimismo, podrá ver los valores que Excel ha leído en el libro cerrado, lo que suele ser importante para depurar y auditar el libro.

Si no puede evitar el uso de libros vinculados, intente que estén todos abiertos en lugar de cerrados y abra los libros vinculados de destino antes de abrir los libros que contienen los vínculos de origen.

Minimizar los vínculos entre hojas de cálculo

El uso de muchas hojas de cálculo puede hacer que el libro sea más fácil de manejar, pero en general es más lento calcular las referencias a otras hojas de cálculo que las referencias dentro de las hojas de cálculo.

Minimizar el rango usado

Con el fin de ahorrar memoria y reducir el tamaño del archivo, Excel intenta almacenar solamente la información del área que se ha usado de la hoja de cálculo. Se denomina rango usado. En ocasiones, algunas operaciones de edición y formato extienden el rango usado mucho más allá del rango que se supone que se está usando. Esto puede causar obstáculos de rendimiento y obstáculos de tamaño de archivo.

Para comprobar el rango usado visible de una hoja de cálculo, use la combinación de teclas Ctrl+Fin. Donde sea excesivo, es aconsejable eliminar todas las filas y columnas que aparecen debajo y a la derecha de la última celda que se ha usado realmente y, después, guardar el libro. Cree primero una copia de seguridad. Si tiene fórmulas con rangos que extienden hasta el área eliminada o hacen referencia a esta, se reducirá el tamaño de esos rangos o estos se cambiarán a #N/A.

Permitir datos adicionales

Si agrega filas o columnas de datos a las hojas de cálculo con frecuencia, debe encontrar la manera de hacer que las fórmulas de Excel hagan referencia automáticamente a la nueva área de datos, para no tener que buscar las fórmulas y modificarlas cada vez.

Para ello, puede usar en las fórmulas un rango amplio que se extienda mucho más allá de los límites de los datos existentes. No obstante, esto puede impedir que el cálculo sea eficaz en determinadas circunstancias. También es difícil de mantener, ya que al eliminar filas y columnas puede reducirse el rango sin que el usuario lo note.

Usar referencias de tabla estructuradas (recomendado)

En Excel 2007 y las versiones posteriores, se pueden usar referencias de tabla estructuradas, que se expanden y contraen automáticamente a medida que se amplía o se reduce el tamaño de la tabla de referencia.

Esta solución tiene varias ventajas:

Tienen menos desventajas de rendimiento que las referencias de columna completa y los rangos dinámicos.
Es fácil tener varias tablas de datos en una sola hoja de cálculo.
Las fórmulas incrustadas en la tabla también se amplían y contraen con los datos.

También puede usar referencias de filas y columnas completas

Un método alternativo es usar una referencia de columna completa, por ejemplo $A: $A. Esta referencia devuelve todas las filas de la columna A. Por lo tanto, puede agregar todos los datos que quiera, ya que la referencia los incluirá siempre.

Esta solución tiene ventajas y desventajas:

Muchas de las funciones integradas de Excel (SUMA, SUMAR.SI) calculan las referencias de columna completa eficazmente, ya que reconocen automáticamente la última fila usada de la columna. Sin embargo, algunos cálculos de matriz, como SUMAPRODUCTO, no pueden administrar referencias de columna completas ni calcular todas las celdas de la columna.
Las funciones definidas por el usuario no reconocen automáticamente la última fila usada en la columna y, por lo tanto, calculan con frecuencia referencias de columna completa de forma ineficaz. No obstante, es fácil programar las funciones definidas por el usuario para que reconozcan la última fila utilizada.
Es difícil usar referencias de columna completa cuando tiene varias tablas de datos en una sola hoja de cálculo.
En Excel 2007 y en las versiones posteriores, las fórmulas de matriz pueden administrar referencias de columna completa, pero es algo que fuerza el cálculo de todas las celdas de la columna, incluidas las celdas vacías. Esto puede ralentizar el cálculo, sobre todo para un millón de filas.

Como alternativa, use rangos dinámicos

Si usa las funciones DESREF o INDICE y CONTARA en la definición de un rango con nombre, puede hacer que el área a la que hace referencia el rango con nombre se expanda y contraiga de forma dinámica. Por ejemplo, puede crear un nombre definido mediante una de las siguientes fórmulas:

  =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
  =Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A)+ROW(Sheet1!$A$1) - 1,1)

Cuando se usa el nombre del rango dinámico en una fórmula, esta se amplía automáticamente para incluir las nuevas entradas.

Por lo general, en un rango dinámico es preferible usar la fórmula INDICE en lugar de la fórmula DESREF, ya que DESREF tiene la desventaja de que es una función volátil que se calculará en cada actualización.

El rendimiento disminuye porque la función CONTARA de la fórmula del rango dinámico debe examinar muchas filas. Para minimizar esta disminución del rendimiento, almacene la parte CONTARA de la fórmula en una celda independiente o un nombre definido y luego haga referencia a la celda o el nombre en el rango dinámico:

 Counts!z1=COUNTA(Sheet1!$A:$A)
 OffsetDynamicRange=OFFSET(Sheet1!$A$1,0,0,Counts!$Z$1,1)
 IndexDynamicRange=Sheet1!$A$1:INDEX(Sheet1!$A:$A,Counts!$Z$1+ROW(Sheet1!$A$1) - 1,1)

También puede usar funciones como INDIRECTO para crear rangos dinámicos, pero INDIRECTO es volátil y siempre calcula en subproceso único.

Los rangos dinámicos tienen las siguientes ventajas e inconvenientes:

Los rangos dinámicos funcionan bien para limitar el número de cálculos que realizan las fórmulas de matriz.
El uso de varios rangos dinámicos en una sola columna requiere funciones de recuento de funcionalidad específica.
El uso de muchos rangos dinámicos puede disminuir el rendimiento.

Mejorar el tiempo de cálculo de búsqueda

En Office 365 versión 1809 y posteriores, las funciones BUSCARH, BUSCARV y COINCIDIR para buscar una coincidencia exacta en los datos sin ordenar son más rápidas que nunca al buscar en varias columnas (o filas con BUSCARH) en el mismo rango de tabla.

Dicho esto, en el caso de las versiones anteriores de Excel, las búsquedas son con frecuencia considerables obstáculos en el cálculo. Afortunadamente, hay muchas formas de mejorar el tiempo de cálculo de la búsqueda. Si usa la opción de coincidencia exacta, el tiempo de cálculo de la función es proporcional al número de celdas que se analizan para encontrar una coincidencia. En el caso de los rangos amplios, el tiempo de búsqueda puede ser considerable.

La búsqueda con las opciones de coincidencia aproximada de BUSCARV, BUSCARH y COINCIDIR en datos ordenados es rápida, y el tiempo de búsqueda no aumenta significativamente por la longitud del rango en el que se hace la búsqueda. Las características son las mismas que en la búsqueda binaria.

Entender las opciones de búsqueda

Asegúrese de que comprende las opciones de tipo de coincidencia y búsqueda en rango de las funciones COINCIDIR, BUSCARV y BUSCARH.

En el siguiente ejemplo de código, se muestra la sintaxis de la función COINCIDIR Para obtener más información, vea el método COINCIDIR del objeto WorksheetFunction.

  MATCH(lookup value, lookup array, matchtype)

MatchType=1 devuelve la coincidencia mayor que es menor o igual que el valor de búsqueda cuando la matriz de búsqueda está ordenada de forma ascendente (coincidencia aproximada). Si la matriz de búsqueda no está ordenada de forma ascendente, COINCIDIR devolverá una respuesta incorrecta. La opción predeterminada es coincidencia aproximada en orden ascendente.
MatchType=0 requiere una coincidencia exacta y supone que los datos no están ordenados.
Matchtype=-1 devuelve la coincidencia menor que es mayor o igual que el valor de búsqueda cuando la matriz de búsqueda está ordenada de forma descendente (coincidencia aproximada).

En el siguiente ejemplo de código, se muestra la sintaxis de las funciones BUSCARV y BUSCARH. Para obtener más información, vea los métodos BUSCARV y BUSCARH del objeto WorksheetFunction.

  VLOOKUP(lookup value, table array, col index num, range-lookup)
  HLOOKUP(lookup value, table array, row index num, range-lookup)

Range-lookup=TRUE devuelve la coincidencia mayor que es menor o igual que el valor de búsqueda (coincidencia aproximada). Esta es la opción predeterminada. La matriz de tabla debe estar ordenada en orden ascendente.
Range-lookup=FALSE requiere una coincidencia exacta y supone que los datos no están ordenados.

Evite realizar búsquedas en datos sin ordenar siempre que sea posible, ya que es lento. Si los datos están ordenados, pero desea una coincidencia exacta, vea Usar dos búsquedas para datos ordenados con valores que faltan.

Usar INDICE y COINCIDIR o DESREF en lugar de BUSCARV

Pruebe a usar las funciones INDICE y COINCIDIR en lugar de BUSCARV. Aunque la función BUSCARV es ligeramente más rápida (aproximadamente un cinco por ciento más rápida) y usa menos memoria que una combinación de las funciones COINCIDIR e INDICE o DESREF, la flexibilidad adicional que ofrecen las funciones COINCIDIR e INDICE permite ahorrar mucho tiempo. Por ejemplo, puede almacenar el resultado de una coincidencia exacta de la función COINCIDIR en una celda y volver a usarla en varias instrucciones INDICE.

La función INDICE es rápida y no es una función volátil, lo que agiliza el cálculo. La función DESREF también es rápida, pero es una función volátil y, a veces, aumenta considerablemente el tiempo que se tarda en procesar la cadena de cálculo.

Es fácil convertir VLOOKUP a INDEX y MATCH. Las dos instrucciones siguientes devuelven el mismo resultado:

 VLOOKUP(A1, Data!$A$2:$F$1000,3,False)

 INDEX(Data!$A$2:$F$1000,MATCH(A1,$A$1:$A$1000,0),3)

Agilizar las búsquedas

Como las búsquedas de coincidencia exacta pueden ser lentas, tenga en cuenta las siguientes opciones para mejorar el rendimiento:

Usar una hoja de cálculo. Es más rápido mantener las búsquedas y los datos en la misma hoja.
Cuando sea posible, ORDENE primero los datos (la función ORDENAR es rápida) y use la coincidencia aproximada.
Cuando tenga que usar una búsqueda de coincidencia exacta, restrinja al mínimo posible el rango de celdas que se analizarán. Use tablas y referencias estructuradas o nombres de rango dinámicos en lugar de hacer referencia a un gran número de filas o columnas. En ocasiones, se puede calcular previamente un límite de rango inferior y un límite de rango superior para la búsqueda.

Use dos búsquedas para los datos ordenados en los que falten valores

En un número amplio de filas, la búsqueda de dos coincidencias aproximadas es significativamente más rápida que la búsqueda de una coincidencia exacta. (El punto de equilibro es de aproximadamente 10-20 filas).

Si puede ordenar los datos, pero sigue sin poder usar la coincidencia aproximada porque no sabe con certeza si el valor que busca se encuentra en el rango de búsqueda, puede usar esta fórmula:

  IF(VLOOKUP(lookup_val ,lookup_array,1,True)=lookup_val, _
      VLOOKUP(lookup_val, lookup_array, column, True), "notexist")

La primera parte de la fórmula hace una búsqueda aproximada en la columna de búsqueda.

  VLOOKUP(lookup_val ,lookup_array,1,True)

Puede comprobar si la respuesta de la columna de búsqueda es la misma que el valor de búsqueda (en cuyo caso tiene una coincidencia exacta) mediante la siguiente fórmula:

  IF(VLOOKUP(lookup_val ,lookup_array,1,True)=lookup_val,

Si esta fórmula devuelve Verdadero, ha encontrado una coincidencia exacta, por lo que puede realizar la búsqueda aproximada de nuevo, aunque esta vez devolviendo la respuesta de la columna que desea.

  VLOOKUP(lookup_val, lookup_array, column, True)

Si la respuesta de la columna de búsqueda no coincide con el valor de búsqueda, falta un valor y la fórmula devuelve "notexist".

Tenga en cuenta que recibirá un error si busca un valor menor que el valor menor de la lista. Puede controlar este error con la función SI.ERROR, o bien mediante la adición de un pequeño valor de prueba a la lista.

Usar la función SI.ERROR para los datos sin ordenar en los que faltan valores

Si tiene que usar la búsqueda de coincidencia exacta en datos sin ordenar y no sabe con certeza si existe el valor de búsqueda, a menudo tendrá que controlar el valor #N/A que se devuelve cuando no se encuentra ninguna coincidencia. En Excel 2007 y las versiones posteriores, se puede usar la función SI.ERROR, que es sencilla y rápida.

  IF IFERROR(VLOOKUP(lookupval, table, 2 FALSE),0)

En las versiones anteriores, se puede usar una función SI que contenga dos búsquedas. Es un método sencillo, pero lento.

  IF(ISNA(VLOOKUP(lookupval,table,2,FALSE)),0,_
      VLOOKUP(lookupval,table,2,FALSE))

Puede evitar la búsqueda exacta doble si usa COINCIDIR una vez, almacena el resultado en una celda y luego comprueba el resultado antes de usar la función INDICE.

  In A1 =MATCH(lookupvalue,lookuparray,0)
  In B1 =IF(ISNA(A1),0,INDEX(tablearray,A1,column))

Si no puede usar dos celdas, use la función CONTAR.SI. Por lo general, es más rápido que una búsqueda exacta de coincidencias.

  IF (COUNTIF(lookuparray,lookupvalue)=0, 0, _
      VLOOKUP(lookupval, table, 2 FALSE))

Usar las funciones COINCIDIR e INDICE para las búsquedas de coincidencia exacta en varias columnas

A menudo, se puede reutilizar varias veces una búsqueda COINCIDIR de coincidencia exacta. Por ejemplo, si está realizando búsquedas exactas en varias columnas de resultado, puede ahorrar tiempo si usa una instrucción COINCIDIR y muchas instrucciones INDICE en lugar de muchas instrucciones BUSCARV.

Agregue una columna adicional para COINCIDIR para almacenar el resultado (stored_row) y, en cada columna de resultado, use lo siguiente:

  INDEX(Lookup_Range,stored_row,column_number)

También puede usar BUSCARV en una fórmula de matriz. (Las fórmulas de matriz deben especificarse mediante Ctrl+-Mayús+Entrar. Excel agregará { y } para mostrar que se trata de una fórmula de matriz).

  {VLOOKUP(lookupvalue,{4,2},FALSE)}

Usar INDICE para un conjunto de filas o columnas contiguas

También puede devolver varias celdas con una operación de búsqueda. Para buscar varias columnas contiguas, puede usar la función INDICE en una fórmula de matriz para devolver varias columnas a la vez (use 0 como número de columna). También puede usar la función INDICE para devolver varias filas al mismo tiempo.

  {INDEX($A$1:$J$1000,stored_row,0)}

Esto devuelve de la columna A a la columna J de la fila almacenada que se creó con una instrucción COINCIDIR anterior.

Usar COINCIDIR para devolver un bloque rectangular de celdas

Use las funciones MATCH y OFFSET para devolver un bloque rectangular de celdas.

Usar COINCIDIR e INDICE para búsquedas en dos dimensiones

Para realizar una búsqueda bidimensional en una tabla, puede hacer búsquedas independientes en las filas y columnas de la tabla mediante una función INDICE con dos funciones COINCIDIR incrustadas, una para la fila y otra para la columna.

Usar un rango de subconjunto para una búsqueda de varios indices

En hojas de cálculo grandes, es posible que tenga que buscar con frecuencia mediante varios índices, como buscar volúmenes de productos en un país o región. Para ello, puede concatenar los índices y llevar a cabo la búsqueda usando valores de búsqueda concatenados. Sin embargo, esto es poco eficaz dos motivos:

La concatenación de cadenas es una operación de cálculo que consume muchos recursos.
La búsqueda cubrirá un amplio rango.

A menudo es más eficaz calcular un intervalo de subconjuntos para la búsqueda (por ejemplo, al buscar la primera y la última fila del país o región y, a continuación, buscar el producto dentro de ese intervalo de subconjuntos).

Considere la opción de usar una búsqueda tridimensional

Para buscar la tabla que se va a usar además de la fila y la columna, puede usar las técnicas siguientes, que se centran en cómo hacer que Excel busque o seleccione la tabla.

Si cada tabla en la que desea buscar (la tercera dimensión) está almacenada como un conjunto de tablas estructuradas con nombre, nombres de rango o una tabla de cadenas de texto que representan rangos, es posible que pueda usar las funciones ELEGIR o INDIRECTO.

El uso de la función ELEGIR y nombres de rango puede ser un método eficaz. La función ELEGIR no es volátil, pero es más adecuada para un número relativamente pequeño de tablas. En este ejemplo, se usa dinámicamente TableLookup_Value para elegir el nombre del rango (TableName1, TableName2, ...) que se va a usar en la tabla de búsqueda.
```
  INDEX(CHOOSE(TableLookup_Value,TableName1,TableName2,TableName3), _
  MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))
```
En el siguiente ejemplo, se usa la función INDIRECTO y TableLookup_Value para crear dinámicamente el nombre de la hoja que se usará para la tabla de búsqueda. Este método tiene la ventaja de que es sencillo y puede administrar un gran número de tablas. Dado que la función INDIRECTO es una función volátil de subproceso único, la búsqueda se calcula en un subproceso único en cada cálculo incluso aunque no hayan cambiado los datos. Este método es lento.
```
  INDEX(INDIRECT("Sheet" &amp; TableLookup_Value &amp; "!$B$2:$Z$1000"), _
  MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))
```
También puede usar la función BUSCARV para buscar el nombre de la hoja o la cadena de texto que se va a usar en la tabla y, luego, usar la función INDIRECTO para convertir el texto resultante en un rango.
```
  INDEX(INDIRECT(VLOOKUP(TableLookup_Value,TableOfTAbles,1)),MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))
```

Otra técnica consiste en agregar todas las tablas en una tabla grande que tenga una columna adicional que identifique las tablas individuales. Después puede usar las técnicas de búsqueda de varios índices que se muestra en los ejemplos anteriores.

Usar la búsqueda con caracteres comodín

Las funciones MATCH, VLOOKUP y HLOOKUP permiten usar los caracteres comodín ? (cualquier carácter único) y * (ningún carácter o cualquier número de caracteres) en coincidencias exactas alfabéticas. En ocasiones, puede usar este método para evitar coincidencias múltiples.

Optimizar las fórmulas de matriz y la SUMAPRODUCTO

Las fórmulas de matriz y la función SUMAPRODUCTO son eficaces, pero es necesario controlarlas con cuidado. Una sola fórmula de matriz puede requerir varios cálculos.

La clave para optimizar la velocidad de cálculo de las fórmulas de matriz es asegurarse de que el número de celdas y expresiones que se evalúan en la fórmula de matriz sea lo más pequeño posible. Recuerde que una fórmula de matriz es algo así como una fórmula volátil: si alguna de las celdas a las que hace referencia ha cambiado, es volátil o se ha calculado de nuevo, la fórmula de matriz calcula todas las celdas de la fórmula y evalúa todas las celdas virtuales que necesita para realizar el cálculo.

Para optimizar la velocidad de cálculo de las fórmulas de matriz:

Mueva las expresiones y referencias de rango de las fórmulas de matriz a columnas y filas auxiliares independientes. Este es un uso mucho más adecuado del proceso de actualización inteligente en Excel.
No haga referencia a columnas completas o a más filas y columnas de las necesarias. Las fórmulas de matriz deben calcular todas las referencias de celda de la fórmula, incluso aunque las celdas estén vacías o no se utilicen. Con el millón de filas que hay disponibles en Excel 2007 y las versiones posteriores, el cálculo de una fórmula de matriz que haga referencia a una columna completa es extremadamente lento.
En Excel 2007 y las versiones posteriores, use referencias estructuradas donde pueda para mantener al mínimo el número de celdas que se analizan con una fórmula de matriz.
En ediciones anteriores a Excel 2007, use nombres de rango dinámicos siempre que sea posible. Aunque son volátiles, merece la pena porque minimizan el tamaño de los rangos.
Tenga cuidado con las fórmulas de matriz que hacen referencia a una fila y una columna: esto obliga a calcular un rango rectangular.
Use SUMAPRODUCTO si es posible; es ligeramente más rápida que la fórmula de matriz equivalente.

Considere la opción de usar la función SUMA para las fórmulas de matriz de varias condiciones

Siempre que se pueda, deben usarse las funciones SUMAR.SI.CONJUNTO, CONTAR.SI.CONJUNTO y PROMEDIO.SI.CONJUNTO en lugar de las fórmulas de matriz, ya que el cálculo es mucho más rápido. Excel 2016 presenta las funciones rápidas MAX.SI.CONJUNTO y MIN.SI.CONJUNTO.

En las versiones anteriores a Excel 2007, las fórmulas de matriz suelen usarse para calcular una suma con varias condiciones. Esto es relativamente fácil, sobre todo si usa el Asistente para suma condicional en Excel, pero a menudo es lento. Por lo general, hay modos mucho más rápidos de obtener el mismo resultado. Si tiene solamente unas pocas sumas de varias condiciones, puede usar la función DSUMA, que es mucho más rápida que la fórmula de matriz equivalente.

Si necesita usar fórmulas de matriz, a continuación puede ver algunos métodos adecuados para agilizar el cálculo:

Use nombres de rango dinámicos o referencias de tabla estructuradas para minimizar el número de celdas.
Divida las distintas condiciones en una columna de fórmulas auxiliares que devuelvan Verdadero o Falso para cada fila y, luego, haga referencia a la columna auxiliar en una fórmula SUMAR.SI o fórmula de matriz. Es posible que no parezca que se reduce el número de cálculos para una sola fórmula de matriz, pero la mayor parte del tiempo permite que el proceso de actualización inteligente recalcule solamente las fórmulas de la columna auxiliar que es necesario actualizar.
Considere la opción de concatenar todas las condiciones en una sola condición y luego usar la función SUMAR.SI.
Si los datos pueden ordenarse, cuente grupos de filas y limite las fórmulas de matriz a los grupos de subconjuntos.

Priorice las funciones SUMAR.SI.CONJUNTO Y CONTAR.Si.CONJUNTO de varias condiciones y otras funciones de la familia SI.

Estas funciones evalúan cada una de las condiciones de izquierda a derecha, por turnos. Por lo tanto, es más eficaz colocar la condición más restrictiva en primer lugar, de modo que las condiciones posteriores solo tengan que buscar en un número menor de filas.

Considere la opción de usar la función SUMAPRODUCTO para las fórmulas de matriz de varias condiciones

En Excel 2007 y las versiones posteriores, debería usar siempre las funciones SUMA.SI.CONJUNTO, CONTAR.SI.CONJUNTO y PROMEDIO.SI.CONJUNTO. Y, en Excel 2016, las funciones MAX.SI.CONJUNTO y MIN.SI.CONJUNTO en lugar de las fórmulas SUMAPRODUCTO siempre que sea posible.

En las versiones anteriores, el uso de SUMAPRODUCTO en lugar de las fórmulas de matriz SUMA tiene algunas ventajas:

SUMAPRODUCTO no requiere la entrada mediante Ctrl+Mayús+Entrar.
SUMAPRODUCTO es, por lo general, ligeramente más rápida (del 5 al 10 por ciento).

Use SUMPRODUCT para fórmulas de matriz de varias condiciones como se indica a continuación:

  SUMPRODUCT(--(Condition1),--(Condition2),RangetoSum)

En este ejemplo, Condition1 y Condition2 son expresiones condicionales como $A$1:$A$10000<=$Z4. Dado que las expresiones condicionales devuelven Verdadero o Falso en lugar de números, deben convertirse en números en la función SUMAPRODUCTO. Para ello, puede usar dos signos menos (--), agregar 0 (+ 0), o multiplicar por 1 (x1). El uso de -- es ligeramente más rápido que + 0 u x1.

Tenga en cuenta que el tamaño y forma de los rangos o matrices que se usan en las expresiones condicionales y el rango que se va a sumar deben ser los mismos, y no pueden contener columnas enteras.

También puede multiplicar directamente los términos en SUMAPRODUCTO en lugar de separarlos por comas:

  SUMPRODUCT((Condition1)*(Condition2)*RangetoSum)

Esto suele ser ligeramente más lento que si se usa la sintaxis de coma y muestra un error si el rango que se va a sumar contiene un valor de texto. No obstante, es algo más flexible en cuanto a que el rango que se va sumar puede tener, por ejemplo, varias columnas, mientras que las condiciones solo tienen una columna.

Usar SUMAPRODUCTO para multiplicar y agregar rangos y matrices

En los casos de cálculos de medias ponderadas, en los que se necesita multiplicar un rango de números por otro rango de números y sumar los resultados, el uso de la sintaxis de coma en SUMAPRODUCTO puede resultar entre un 20 % y un 25 % más rápido que la función SUMA introducida en forma de matriz.

  {=SUM($D$2:$D$10301*$E$2:$E$10301)}
  =SUMPRODUCT($D$2:$D$10301*$E$2:$E$10301)
  =SUMPRODUCT($D$2:$D$10301,$E$2:$E$10301)

Todas estas tres fórmulas producen el mismo resultado, pero la tercera fórmula, que usa la sintaxis de coma para SUMAPRODUCTO, solo requiere el 77 % del tiempo de cálculo necesario para las otras dos fórmulas.

Tener en cuenta los posibles obstáculos de cálculo de matrices y funciones

El motor de cálculo de Excel está optimizado para aprovechar las fórmulas de matriz y las funciones que hacen referencia a rangos. Sin embargo, algunas de disposiciones poco usuales de estas fórmulas y funciones pueden causar a veces, aunque no siempre, un aumento significativo del tiempo de cálculo.

Si encuentra un obstáculo de cálculo relacionado con las fórmulas de matriz y las funciones de rango, debe buscar lo siguiente:

Referencias que se superponen parcialmente.
Fórmulas de matriz y funciones de rango que hacen referencia a una parte de un bloque de celdas que se calculan en otra fórmula de matriz o función de rango. Esta situación se puede producir con frecuencia en el análisis de series temporales.
Un conjunto de fórmulas que hacen referencia por fila y un segundo conjunto de fórmulas que hacen referencia al primer conjunto por columna.
Un conjunto amplio de fórmulas de matriz de una única fila que abarcan un bloque de columnas, con funciones SUMA al pie de cada columna.

Usar las funciones eficazmente

Las funciones aumentan significativamente las capacidades de Excel, pero la forma en la que se usan puede repercutir en el tiempo de cálculo.

Evitar las funciones de subproceso único

La mayoría de las funciones nativas de Excel funcionan bien con el cálculo de subprocesos múltiples. No obstante, si es posible, evite usar las siguientes funciones de subproceso único:

Automatización y funciones de VBA definidas por el usuario, aunque las funciones definidas por el usuario basadas en XLL pueden ser de subprocesos múltiples
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

Usar tablas para las funciones que controlan rangos

En el caso de las funciones como SUMA, SUMAR.SI y SUMAR.SI.CONJUNTO que controlan rangos, el tiempo de cálculo es proporcional al número de celdas utilizadas que están sumando o contando. No se examinan las celdas sin usar, por lo que las referencias de columna completas son relativamente eficaces, pero es mejor asegurarse de que no se incluyen más celdas usadas de las que necesita. Usar tablas o calcular rangos de subconjunto o rangos dinámicos.

Reducir las funciones volátiles

Las funciones volátiles pueden ralentizar la actualización porque aumentan el número de fórmulas que deben recalcularse en cada cálculo.

A menudo, se puede reducir el número de funciones volátiles mediante el uso de INDICE en lugar de DESREF, y ELEGIR en lugar de INDIRECTO. No obstante, DESREF es una función rápida y a menudo se puede usar de forma creativa para realizar cálculos rápidos.

Usar las funciones de C o C++ definidas por el usuario

Las funciones definidas por el usuario que se programan C o C++ y que usan la API de C (funciones de complemento XLL) suelen ser más rápidas que las funciones definidas por el usuario que se desarrollan con VBA o la automatización (XLA o complementos de automatización). Para obtener más información, vea Desarrollo de XLL de Excel 2010.

El rendimiento de las funciones de VBA definidas por el usuario depende del modo en el que se programen y se llame a las mismas.

Usar funciones de VBA definidas por el usuario más rápidas

Normalmente es más rápido usar los cálculos de fórmulas de Excel y las funciones de hoja de cálculo que usar funciones definidas por el usuario de VBA. Esto se debe a que existe una pequeña sobrecarga para cada llamada a la función definida por el usuario y una sobrecarga significativa en la transferencia de información de Excel a la función definida por el usuario. Sin embargo, las funciones definidas por el usuario pueden ser mucho más rápidas que las fórmulas de matriz complejas si están bien diseñadas y se llama a las mismas de un modo adecuado.

Asegúrese de que ha puesto todas las referencias a las celdas de la hoja de cálculo en los parámetros de entrada de la función definida por el usuario en lugar de hacerlo en el cuerpo de la función definida por el usuario, para evitar agregar Application.Volatile innecesariamente.

Si necesita una gran cantidad de fórmulas que usen funciones definidas por el usuario, asegúrese de que está en el modo de cálculo manual y de que el cálculo se inicia desde VBA. Las funciones de VBA definidas por el usuario calculan mucho más lentamente si el cálculo no es llamado desde VBA (por ejemplo, en el modo automático o al presionar F9 en el modo manual). Esto es especialmente cierto cuando el Editor de Visual Basic (Alt+F11) está abierto o se ha abierto en la sesión actual de Excel.

Puede interceptar F9 y redirigirlo a una subrutina de cálculo de VBA como se indica a continuación. Agregue esta subrutina al módulo ThisWorkbook.

  Private Sub Workbook_Open()
      Application.OnKey "{F9}", "Recalc"
  End Sub

Agregue esta subrutina a un módulo estándar.

  Sub Recalc()
      Application.Calculate
      MsgBox "hello"
  End Sub

Las funciones definidas por el usuario en los complementos de Automation (Excel 2002 y versiones posteriores) no incurren en la sobrecarga del Editor de Visual Basic porque no usan el editor integrado. Otras características de rendimiento de las funciones de Visual Basic 6 definidas por el usuario en los complementos de automatización son similares a las funciones de VBA.

Si la función definida por el usuario procesa todas las celdas de un rango, declare la entrada como rango, asígnela a una variante que contenga una matriz y recorra la misma. Si desea controlar las referencias de columna completa de forma eficaz, debe crear un subconjunto del rango de entrada y dividirlo en su intersección con el rango utilizado, como en este ejemplo.

  Public Function DemoUDF(theInputRange as Range)
      Dim vArr as Variant
      Dim vCell as Variant
      Dim oRange as Range
      Set oRange=Union(theInputRange, theRange.Parent.UsedRange)
      vArr=oRange
      For Each vCell in vArr
          If IsNumeric(vCell) then DemoUDF=DemoUDF+vCell
      Next vCell
  End Function

Si la función definida por el usuario está usando funciones de hoja de cálculo o métodos de modelo de objetos de Excel para procesar un rango, es más eficaz que conserve el rango como una variable de objeto que transferir todos los datos de Excel a la función definida por el usuario.

  Function uLOOKUP(lookup_value As Variant, lookup_array As Range, _
                   col_num As Variant, sorted As Variant, _
                   NotFound As Variant)
      Dim vAnsa As Variant
      vAnsa = Application.VLookup(lookup_value, lookup_array, _
                                  col_num, sorted)
      If Not IsError(vAnsa) Then
          uLOOKUP = vAnsa
      Else
          uLOOKUP = NotFound
      End If
  End Function

Si se llama a la función definida por el usuario de forma temprana en la cadena de cálculo, puede pasarse como argumentos sin calcular. En una función definida por el usuario, puede detectar celdas no calculadas mediante la siguiente prueba para las celdas vacías que contienen una fórmula:

  If ISEMPTY(Cell.Value) AND Len(Cell.formula)>0 then

Cada llamada a una función definida por el usuario y cada transferencia de datos de Excel a VBA suponen una sobrecarga de tiempo. A veces, una fórmula de matriz de varias celdas definida por el usuario puede ayudarle a minimizar estas sobrecargas mediante la combinación de varias llamadas a la función en una sola función con un rango de entrada de varias celdas que devuelve un rango de respuestas.

Minimizar el rango de celdas a las que hacen referencia las funciones SUMA Y SUMA.SI

Las funciones SUMA y SUMA.SI de Excel se usan con frecuencia en un gran número de celdas. El tiempo de cálculo para estas funciones es proporcional al número de celdas cubiertas, por lo que debe intentar minimizar el rango de celdas a las que hacen referencia a las funciones.

Puede usar las funciones SUMAR.SI, CONTAR.SI, SUMAR.SI.CONJUNTO y CONTAR.SI.CONJUNTO con caracteres comodín y otras funciones SI.

Use los caracteres comodín ? (cualquier carácter único) y * (sin carácter o ningún número de caracteres) en los criterios de los intervalos alfabéticos como parte de las funciones SUMIF, COUNTIF, SUMIFS, COUNTIFS y otras funciones IFS .

Elegir un método para sumas de período hasta la fecha y acumuladas

Hay dos métodos para realizar las sumas de período hasta la fecha o acumuladas. Supongamos que los números en los que quiere hacer la SUMA acumulativa se encuentran en la columna A, pero quiere que la columna B contenga la suma acumulativa; puede realizar cualquiera de las siguientes acciones:

Puede crear una fórmula en la columna B como =SUM($A$1:$A2) y arrastrarla hacia abajo hasta donde sea necesario. La celda inicial de la SUMA está fijada en A1, pero debido a que la celda final tiene una referencia de fila relativa, se incrementa automáticamente para cada fila.
Puede crear una fórmula como =$A1 en la celda B1 y =$B1+$A2 en la celda B2 y arrastrarla hacia abajo hasta donde sea necesario. Esto calcula la celda acumulativa agregando el número de esta fila a la SUMA acumulativa anterior.

En 1000 filas, Excel tiene hacer alrededor de 500 000 cálculos con el primer método. Pero, con el segundo método, solo tiene que hacer aproximadamente 2000.

Calcular sumas de subconjuntos

Cuando se tienen varios índices ordenados en una tabla (por ejemplo, sitos en el área), a menudo se puede ahorrar mucho tiempo de cálculo por medio del cálculo dinámico de la dirección de un rango de subconjunto de filas (o columnas) para usarla en la función SUMA o SUMAR.SI.

Para calcular la dirección de un rango de subconjunto de filas o columnas:

Cuente el número de filas de cada bloque de subconjunto.
Agregue los recuentos de manera acumulativa para cada bloque para determinar su fila de inicio.
Use DESREF con la fila de inicio y el recuento para devolver un rango de subconjunto a la función SUMA o SUMAR.SI que cubra solamente el bloque de subconjunto de filas.

Usar SUBTOTALES para las listas filtradas

Use la función SUBTOTALES para las listas filtradas de SUMA. La función SUBTOTALES es útil porque, a diferencia de la función SUMA, omite lo siguiente:

Filas ocultas resultantes de filtrar una lista. En Excel 2003 y las versiones posteriores, también puede hacer que la función SUBTOTALES ignore todas las filas ocultas, no solo las filas filtradas.
Otras funciones de SUBTOTALES

Usar la función AGREGAR

La función AGREGAR es muy eficaz para calcular 19 métodos diferentes para agregar datos (por ejemplo, SUMA, MEDIANA, PERCENTIL y K.ESIMO.MAYOR). AGREGAR tiene opciones para ignorar filas ocultas o filtradas, valores de error y funciones de SUBTOTALES y AGREGAR anidadas.

Evitar usar funciones BD

Las funciones BD, BDSUMA, BDCONTAR, BDPROMEDIO, etc., son significativamente más rápidas que las fórmulas de matriz equivalentes. La desventaja de las funciones BD es que los criterios deben estar en un rango separado, por lo que muchas veces no es práctico usarlas y mantenerlas. En Excel 2007 y las versiones posteriores, debería usar las funciones SUMAR.SI.CONJUNTO, CONTAR.SI.CONJUNTO y PROMEDIO.SI.CONJUNTO en lugar de las funciones BD.

Crear macros de VBA más rápidas

Use las siguientes sugerencias para crear macros de VBA más rápidas.

Desactive todo lo que no sea básico mientras se ejecuta el código

Para mejorar el rendimiento de las macros de VBA, desactive explícitamente la funcionalidad que no es necesaria mientras se ejecuta el código. A menudo, solo es necesario recalcular o actualizar una vez después de que se ejecute el código, lo que mejora el rendimiento. Una vez que se ejecute el código, restaure la funcionalidad a su estado original.

Por lo general, pueden desactivarse las siguientes funcionalidades mientras se ejecuta la macro de VBA:

Application.ScreenUpdating Desactivar la actualización de la pantalla. Si Application.ScreenUpdating está establecida en Falso, Excel no actualiza la pantalla. Mientras se ejecuta el código, la pantalla se actualiza rápidamente y, por lo general, no es necesario que el usuario vea esas actualizaciones. Actualizar la pantalla solo una vez, después de que se ejecute el código, mejora el rendimiento.
Application.DisplayStatusBar Desactivar la barra de estado. Si Application.DisplayStatusBar está establecida en Falso, Excel no muestra la barra de estado. La configuración de la barra de estado es diferente de la configuración de actualización de la pantalla, de modo que puede seguir mostrando el estado de la operación actual incluso aunque la pantalla no se actualice. Sin embargo, si no necesita mostrar el estado de cada operación, desactivar la barra de estado mientras se ejecuta el código también mejora el rendimiento.
Application.Calculation Cambiar al cálculo manual. Si Application.Calculation está establecida en xlCalculationManual, Excel solo calcula el libro cuando el usuario inicia el cálculo explícitamente. En el modo de cálculo automático, Excel determina cuándo se hace el cálculo. Por ejemplo, cada vez que cambia un valor de celda relacionado con una fórmula, Excel recalcula la fórmula. Si cambia el modo de cálculo a manual, puede esperar a que se actualicen todas las celdas asociadas a la fórmula para volver a calcular el libro. Recalcular el libro solo cuando sea necesario mientras se ejecuta el código puede mejorar el rendimiento.
Application.EnableEvents Desactivar eventos. Si Application.EnableEvents está establecida en Falso, Excel no crea eventos. Si hay complementos escuchando para detectar eventos de Excel, estos complementos consumen recursos del equipo cuando graban los eventos. Si no es necesario que el complemento registre los eventos que se producen mientras se ejecuta el código, desactivar los eventos mejora el rendimiento.
ActiveSheet.DisplayPageBreaks Desactivar los saltos de página. Si ActiveSheet.DisplayPageBreaks está establecida en False, Excel no muestra los saltos de página. No es necesario volver a calcular los saltos de página mientras se ejecuta el código y calcular los saltos de página después de ejecutar el código mejora el rendimiento.

Importante

Recuerde restaurar esta funcionalidad a su estado original una vez que se ejecute el código.

En el siguiente ejemplo, se muestra la funcionalidad que puede desactivar mientras se ejecuta la macro de VBA.

  ' Save the current state of Excel settings.
  screenUpdateState = Application.ScreenUpdating
  statusBarState = Application.DisplayStatusBar
  calcState = Application.Calculation
  eventsState = Application.EnableEvents
  ' Note: this is a sheet-level setting.
  displayPageBreakState = ActiveSheet.DisplayPageBreaks 

  ' Turn off Excel functionality to improve performance.
  Application.ScreenUpdating = False
  Application.DisplayStatusBar = False
  Application.Calculation = xlCalculationManual
  Application.EnableEvents = False
  ' Note: this is a sheet-level setting.
  ActiveSheet.DisplayPageBreaks = False

  ' Insert your code here.

  ' Restore Excel settings to original state.
  Application.ScreenUpdating = screenUpdateState
  Application.DisplayStatusBar = statusBarState
  Application.Calculation = calcState
  Application.EnableEvents = eventsState
  ' Note: this is a sheet-level setting
  ActiveSheet.DisplayPageBreaks = displayPageBreaksState

Leer y escribir grandes bloques de datos en una sola operación

Optimice el código reduciendo explícitamente el número de veces que se transfieren datos entre Excel y el código. En lugar de recorrer las celdas de una en una para obtener o establecer un valor, puede obtener o establecer los valores de todo el rango de celdas en una línea, con una variante que contenga una matriz bidimensional para almacenar valores según sea necesario. En el siguiente ejemplo de código, se comparan estos dos métodos.

En el siguiente ejemplo de código, se muestra código no optimizado que recorre las celdas de una en una para obtener y establecer los valores de las celdas A1:C10000. Estas celdas no contienen fórmulas.

  Dim DataRange as Range
  Dim Irow as Long
  Dim Icol as Integer 
  Dim MyVar as Double 
  Set DataRange=Range("A1:C10000") 

  For Irow=1 to 10000 
      For icol=1 to 3
          ' Read the values from the Excel grid 30,000 times.
          MyVar=DataRange(Irow,Icol) 
          If MyVar > 0 then 
              ' Change the value.
              MyVar=MyVar*Myvar 
              ' Write the values back into the Excel grid 30,000 times.
              DataRange(Irow,Icol)=MyVar
          End If 
      Next Icol 
  Next Irow

En el siguiente ejemplo de código, se muestra código optimizado que usa una matriz para obtener y establecer los valores de las celdas A1:C10000 al mismo tiempo. Estas celdas no contienen fórmulas.

  Dim DataRange As Variant
  Dim Irow As Long 
  Dim Icol As Integer 
  Dim MyVar As Double 
  ' Read all the values at once from the Excel grid and put them into an array.
  DataRange = Range("A1:C10000").Value2 

  For Irow = 1 To 10000 
      For Icol = 1 To 3 
          MyVar = DataRange(Irow, Icol) 
          If MyVar > 0 Then 
              ' Change the values in the array.
              MyVar=MyVar*Myvar 
              DataRange(Irow, Icol) = MyVar 
          End If 
      Next Icol 
  Next Irow 
  ' Write all the values back into the range at once.
  Range("A1:C10000").Value2 = DataRange

Usar .Valor2 en lugar de .Valor o .Texto al leer datos de un rango de Excel

.Texto devuelve el valor con formato de una celda. Es lento, puede devolver ### si el usuario aleja la vista y se puede perder precisión.
.Valor devuelve una variable de VBA de fecha o de moneda si el rango tenía formato de fecha o moneda. Es decir, se puede perder precisión y puede causar errores al llamar a las funciones de la hoja de cálculo.
.Valor2 es rápido y no altera los datos que se recuperan de Excel.

Evitar seleccionar y activar objetos

El proceso de seleccionar y activar objetos consume más recursos que la referencia directa a objetos. Si se hace referencia directamente a un objeto como, por ejemplo, un Rango o una Forma, se puede mejorar el rendimiento. En el siguiente ejemplo de código, se comparan estos dos métodos.

En el siguiente ejemplo de código, se muestra código no optimizado que selecciona cada forma de la hoja activa y cambia el texto a "Hola".

  For i = 0 To ActiveSheet.Shapes.Count
      ActiveSheet.Shapes(i).Select
      Selection.Text = "Hello"
  Next i

En el siguiente ejemplo de código, se muestra código optimizado que hace referencia a cada forma directamente y cambia el texto a "Hola".

  For i = 0 To ActiveSheet.Shapes.Count
      ActiveSheet.Shapes(i).TextEffect.Text = "Hello"
  Next i

Usar estas optimizaciones de rendimiento adicionales de VBA

La siguiente es una lista de las optimizaciones de rendimiento adicionales que se pueden usar en el código de VBA:

Devolver resultados mediante la asignación directa de una matriz a un Rango.
Declarar las variables con tipos explícitos para evitar la sobrecarga de determinar el tipo de datos, posiblemente varias veces en un bucle, durante la ejecución del código.
Para las funciones sencillas que use con frecuencia en el código, implemente usted mismo las funciones en VBA en lugar de usar el objeto WorksheetFunction. Para obtener más información, vea Usar funciones de VBA definidas por el usuario más rápidas.
Usar el método Range.SpecialCells para definir el ámbito de la cantidad de celdas con las que interactúa el código.
Tener en cuenta las mejoras de rendimiento si ha implementado la funcionalidad a través de la API de C en el SDK de XLL. Para obtener más información, consulte la documentación del SDK de XLL de Excel 2010.

Tener en cuenta el rendimiento y el tamaño de los formatos de archivo de Excel

En Excel 2007 y las versiones posteriores, Excel contiene una amplia variedad de formatos de archivo en comparación con las versiones anteriores. Si no se tienen en cuenta las variantes de formato de archivo XPS, macro, plantilla, complemento y PDF, los tres formatos principales son XLS, XLSB y XLSX.

Formato XLS

El formato XLS tiene el mismo formato que en las versiones anteriores. Cuando se usa este formato, se está limitado a 256 columnas y 65 536 filas. Cuando se guarda un libro de Excel 2007 o Excel 2010 en formato XLS, Excel ejecuta una comprobación de compatibilidad. El tamaño de archivo es prácticamente el mismo que el de versiones anteriores (se puede almacenar algo de información adicional) y el rendimiento es ligeramente más lento que en las versiones anteriores. Todas las optimizaciones multiproceso que hace Excel con respecto a las órdenes de cálculo de celdas no se guardan en el formato XLS. Por lo tanto, el cálculo de un libro puede ser más lento tras guardar el libro en el formato XLS, cerrarlo y volver a abrir el libro.
Formato XLSB

XLSB es el formato binario presente en Excel 2007 y las versiones posteriores. Está estructurado como una carpeta comprimida que contiene muchos archivos binarios. Es mucho más compacto que el formato XLS, pero la cantidad de compresión depende del contenido del libro. Por ejemplo, diez libros muestran un factor de reducción de tamaño que oscila entre dos y ocho con un factor de reducción promedio de cuatro. En Excel 2007 y las versiones posteriores, el rendimiento de los procesos de abrir y guardar solo es ligeramente más lento que en el XLS.
Formato XLSX

XLSX es el formato XML de Excel 2007 y las versiones posteriores. También es el formato predeterminado en Excel 2007 y las versiones posteriores. El formato XLSX es una carpeta comprimida que contiene un gran número de archivos XML (si cambia la extensión del nombre del archivo a. zip, puede abrir la carpeta comprimida y examinar su contenido). Por lo general, el formato XLSX crea archivos más grandes que el formato XLSB (1,5 más grandes en promedio), pero son aún mucho más reducidos que los archivos XLS. Es normal que los tiempos de apertura y guardado sean ligeramente más largos que los de los archivos XLSB.

Abrir, cerrar y guardar libros

Es posible que se tarde mucho más en abrir, cerrar y guardar libros que en calcularlos. En ocasiones esto se debe a que el libro es de gran tamaño, pero también pueden existir otros motivos.

Si uno o más libros tardan en abrirse y cerrarse más de lo razonable, puede deberse a uno de los siguientes problemas.

Archivos temporales

Los archivos temporales se pueden acumular en el directorio \Windows\Temp (en Windows 95, Windows 98 y Windows ME) o en el directorio \Documents and Settings\User Name\Local Settings\Temp (en Windows 2000 y Windows XP). Excel crea estos archivos para el libro y para los controles utilizados por los libros abiertos. Los programas de instalación de software también crean archivos temporales. Si Excel deja de responder por cualquier motivo, es posible que tenga que eliminar estos archivos.

Si hay demasiados archivos temporales pueden producirse problemas, por lo que tendrá que eliminarlos en ocasiones. No obstante, si ha instalado software que requiere reiniciar el equipo y aún no lo ha realizado, tendrá que reiniciarlo antes de eliminar los archivos temporales.

Una forma sencilla de abrir el directorio de archivos temporales es desde el menú Inicio de Windows: haga clic en Inicioy luego haga clic en Ejecutar. En el cuadro de texto, escriba %temp% y, después, haga clic en Aceptar.

Hacer un seguimiento de los cambios en un libro compartido

El seguimiento de los cambios en un libro compartido hace que el tamaño del archivo del libro aumente rápidamente.
Archivo de intercambio fragmentado

Asegúrese de tener el archivo de intercambio de Windows en un disco con mucho espacio y de desfragmentar el disco periódicamente.
Libro con estructura protegida con contraseña

Se abre un libro que tiene su estructura protegida con una contraseña (menú >HerramientasProtección>Proteger libro> escriba la contraseña opcional) y se cierra mucho más lentamente que la que está protegida sin la contraseña opcional.
Problemas con el rango usado

Los rangos usados de tamaño excesivo pueden causar que el libro tarde más en abrirse y que tenga más tamaño, sobre todo si es debido a filas o columnas ocultas que no tienen un alto o ancho estándar. Para obtener más información sobre los problemas con los rangos usados, vea Minimizar el rango usado.
Gran número de controles en las hojas de cálculo

Si hay un gran número de controles (casillas de verificación, hipervínculos, etc.) en las hojas de cálculo, el libro puede tardar más en abrirse debido al número de archivos temporales que se usan. Es posible que también se produzcan problemas al abrir o guardar el libro en una WAN (o incluso en una LAN). Si tiene este problema, considere la opción de rediseñar el libro.
Gran número de vínculos a otros libros

Si es posible, abra los libros vinculados de destino antes de abrir el libro que contienen los vínculos de origen. A menudo es más rápido abrir un libro que leer los vínculos de un libro cerrado.
Configuración del antivirus

Algunas opciones de configuración del antivirus pueden causar problemas o lentitud al abrir, cerrar o guardar archivos, especialmente en un servidor. Si cree que ese puede ser el problema, pruebe a desactivar temporalmente el antivirus.
Cálculo lento que hace que los archivos tarden en abrirse y guardarse

En determinadas circunstancias, Excel actualizará el libro al abrirlo o guardarlo. Si el tiempo de cálculo es largo y está causando algún problema, establezca el cálculo manual, y considere la opción de desactivar la opción calcular antes de guardar (Herramientas>Opciones>Cálculo).
Archivos de la barra de herramientas (.XLB)

Compruebe el tamaño del archivo de la barra de herramientas. El tamaño del archivo de la barra de herramientas suele oscilar entre 10 KB y 20 KB. Para encontrar los XLB, busque *.xlb mediante Windows Search. Cada usuario tiene un único archivo XLB. Al agregar, cambiar o personalizar las barras de herramientas, aumenta el tamaño del archivo toolbar.xlb. Si elimina el archivo, se eliminarán todas las personalizaciones de la barra de herramientas (es más seguro cambiar su nombre a "Toolbar.OLD"). Se creará un nuevo archivo XLB la próxima vez que abra Excel.

Llevar a cabo las siguientes optimizaciones de rendimiento adicionales

Puede hacer mejoras de rendimiento en las siguientes áreas.

Tablas dinámicas

Las tablas dinámicas son muy eficaces para resumir grandes volúmenes de datos.
- Totales como resultados finales. Si tiene que producir totales y subtotales como parte de los resultados finales del libro, pruebe a usar tablas dinámicas.
- Totales como resultados intermedios. Las tablas dinámicas son excelentes para crear informes de resumen, pero es mejor que evite crear fórmulas que usen resultados de tablas dinámicas como totales y subtotales intermedios en la cadena de cálculo a menos que pueda garantizar las condiciones siguientes:
- La tabla dinámica se ha actualizado correctamente durante el cálculo.
- La tabla dinámica no se ha modificado, por lo que la información sigue siendo visible.
Si aún así sigue queriendo usar tablas dinámicas como resultados intermedios, utilice la función IMPORTARDATOSDINAMICOS.
Formatos condicionales y validación de datos

Los formatos condicionales y la validación de datos son excelentes, pero si usa demasiados podría ralentizar considerablemente el cálculo. Si se muestra la celda, todas las fórmulas de formato condicional se evalúan en cada cálculo y cuando se actualiza la presentación de la celda que contiene el formato condicional. El modelo de objetos de Excel tiene una propiedad Worksheet.EnableFormatConditionsCalculation que le permite activar o desactivar el cálculo de formatos condicionales.
Nombres definidos

Los nombres definidos son una de las características más eficaces de Excel, pero requieren un tiempo de cálculo adicional. Usar nombres que hagan referencia a otras hojas de cálculo agrega un nivel adicional de complejidad al proceso de cálculo. Asimismo, debería intentar evitar los nombres anidados (nombres que hacen referencia a otros nombres).

Dado que los nombres se calculan cada vez que se calcula la fórmula que hace referencia a ellos, debería evitar incluir fórmulas o funciones que consuman muchos recursos en los nombres definidos. En estos casos, puede ser mucho más rápido poner la fórmula o función que consume recursos en una celda que no se utilice y hacer referencia a esa celda, ya sea directamente o mediante un nombre.
Fórmulas que solo se usan de forma ocasional

Muchos libros contienen un número considerable de fórmulas y búsquedas que se ocupan de obtener los datos de entrada en la forma adecuada para los cálculos, o que se usan como medida defensiva contra los cambios en el tamaño o la forma de los datos. Si tiene bloques de fórmulas que solo se usan de forma ocasional, puede copiar y pegar valores especiales para eliminar temporalmente las fórmulas, o bien ponerlas en un libro independiente que no se abra con frecuencia. Dado que los errores de las hojas de cálculo a menudo se producen por no darse cuenta de que las fórmulas se han convertido en valores, es posible que el método del libro independiente sea el más recomendable.
Usar memoria suficiente

La versión de 32 bits de Excel puede usar hasta 2 GB de RAM o hasta 4 GB de memoria RAM para la característica de compatibilidad con direcciones largas de las versiones de 32 bits de Excel 2013 y 2016. No obstante, el equipo que ejecuta Excel también consume recursos de memoria. Por lo tanto, si solo tiene 2 GB de RAM en el equipo, Excel no puede aprovechar los 2 GB en su totalidad, ya que una parte de la memoria se asigna al sistema operativo y a los otros programas que se están ejecutando. Para optimizar el rendimiento de Excel en un equipo de 32 bits, recomendamos que el equipo tenga al menos 3 GB de RAM.

La versión de 64 bits de Excel no tiene un límite de 2 GB o de hasta 4 GB. Para obtener más información, vea la sección "Grandes conjuntos de datos y la versión de 64 bits de Excel" en Rendimiento de Excel: Mejoras de rendimiento y límites.

Conclusión

En este artículo se han tratado las formas de optimizar las funcionalidades de Excel de vínculos, búsquedas, fórmulas, funciones y código VBA, entre otras, para salvar los obstáculos comunes y mejorar el rendimiento.

Vea también

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.