ExcelMejorar rendimiento en Libros

Por Juan Astudillo

Presentación

Como Ingeniero de Sistemas uno de los problemas con que me he encontrado,

ha sido el problema de rendimiento de Excel en varias empresas.

He construido una compilación de las mejores técnicas para mejorar la

performance desde las básicas que son la gran mayoría hasta las avanzadas,

que es entrar de fondo en el problema, pero con un poco de orden y cambios

de reglas en la construcción de libros Excel por lo general se llega a buenos

resultados.

Espero les sirvan estos consejos.

Consejos Generales

1. Actualiza tu computador / Laptop: Excel depende en gran medida de RAM y procesador paravelocidad. Aplica las ultimas actualizaciones, Hotfix, etc. No te quedes con antiguo Hardware.

2. Cuando tengas problemas, ya comienza a hacer Insight de la idea de un Plan de rediseñar tu hojade cálculo: Antes de calcular, Excel crea una pila de cálculo, o un enfoque óptimo para el cálculo delas células basadas en las dependencias de celdas. Esto ayudará a Excel si las fórmulas no saltan portodo el libro (es decir .: utilizan un flujo lógico y mantienen dependencias en una sola hoja).

3. Establecer los cálculos a Manual: haga clic en fórmulas en la barra Ribbon -> opciones de cálculo -> Manual. Cuando tenga que actualizar una hoja, haga clic en Shift + F9 en el teclado.

4. Eliminar las celdas no utilizadas: Usa Ctrl + Fin para encontrar la última celda utilizada de lahoja. Si esta muy abajo de tus datos, tendrás que borrar las filas no utilizadas.

5. Usa VBA: Dependiendo de su nivel de comodidad, se puede usar VBA (Visual Basic For Aplications)para aumentar la eficiencia. Algunos ejemplos serían: apagar los cálculos de tablas dinámicasdurante las actualizaciones, la creación de una macro específica para tus necesidades, etc.

Mantener todo en el mismo Libro.

Hacer referencia a otros libros hará que Excel por lo general más

lento, incluso en un libro sin Formulas complicadas. Si es posible,

almacena todo en el mismo libro.

El uso de menos libros de trabajo de mayor tamaño será más eficiente

que usar varios libros vinculados más pequeños.

Cuando tengas que utilizar libros vinculados, abre todos ellos antes de

abrir los libros de trabajo que se unen - para mejorar el rendimiento.

Mantener todo en una sola Hoja.

En lo posible, guarde todo en la misma hoja. Excel tarda más tiempo

para calcular expresiones en que los valores se evalúan en otra hoja.

Si ya has difundido tu trabajo a través de varias hojas, el reordenar

todo, probablemente no vale la pena el esfuerzo.

Hay que tener ésto en cuenta en la planificación de construcción de

hojas. Mantenga expresiones y referencias en la misma hoja, si es

posible.

Trabaja desde Izquierda a Derecha.

Excel calculará expresiones desde la esquina superior izquierda de la

hoja primero y luego continuará hacia la derecha y luego hacia abajo.

Por esto, deberás almacenar valores independientes en la parte

superior izquierda de la hoja e ingresar expresiones (celdas

dependientes) a la derecha o por debajo de esos valores.

Limpia los objetos.

Lo que no se esté usando, se elimina. Crea una copia de seguridad

para que pueda recuperar la funcionalidad en una fecha posterior y

elimine todo lo que ya no usa.

De este modo, se minimizará el rango utilizado. Para determinar el

rango utilizado, pulse [Ctrl] + [Fin]. A continuación, elimina todas las

filas y columnas debajo ya la derecha de la última celda utilizada

real. A continuación, guarda el libro.

Convertir Fórmulas utilizadas.

Crea una copia de seguridad del archivo en primer lugar, por si acaso.

Si todavía hace referencia a valores derivados (los resultados de lasfórmulas), el punto de Limpiar los objetos no te será factible. Puedes, sinembargo, convertir las fórmulas a valores estáticos. Pero ello sólo si estásseguro de que nunca tendrás que volver a calcular las fórmulas quegeneran los valores en el primer lugar. Para convertir las fórmulas a susvalores estáticos, utilizar pegado especial y selecciona Valores parapegar.

Si lo haces, se sobreponen a las fórmulas con los resultados de esasfórmulas. Ten cuidado, verifica si las fórmulas realmente se han ido.

Evitar múltiples funciones volátiles.

Una función volátil vuelve a calcular cada vez que hay un cambio en la

hoja de trabajo, y que deteriora el rendimiento.

Una alternativa eficiente es para entrar en la función volátil en sí

misma y después hacer referencia a esa celda en sus fórmulas.

La función fija calculará como se esperaba, pero sólo una vez en lugar

de cientos de veces. Ejemplos de funciones volátiles son RAND (),

RANDBETWEEN (), NOW (), HOY (), OFFSET (), etc.

Evitar las Fórmulas de Matriz (Array

Formulas).

Gurús y usuarios avanzados aman por igual las matrices, y son unaherramienta poderosa. Por desgracia, son monstruos come-memoria.

Puede ser que sea difícil de creer, pero un par de fórmulas regularescalculará más rápido que su matriz equivalente.

Si las fórmulas de ayuda no son adecuadas, considere una funcióndefinida por el usuario. Además, es posible que pueda sustituir lasmatrices con nuevas funciones, como la función SUMIF(), de COUNTIF(), yAVERAGEIF. (Las fórmulas de matriz se ejecutan mejor en las versiones deExcel con barra Ribbon).

Evitar Fórmulas monstruosas.

Un asesino de rendimiento en la mayoría de los libros de trabajo es el

número de referencias de las celda y sus operaciones, no el número

de fórmulas.

Arrojarse a usar funciones ineficientes puede ralentizar las cosas lo

suficiente para que los usuarios se quejen.

Dos o tres fórmulas de ayuda son casi siempre más eficientes que una

fórmula enorme.

Use IF.ERROR () para actualizar viejas

fórmulas de enmascarado de error.

Si se ha actualizado a una versión de Ribbon de Excel, puede reemplazarla mayoría de sus IF () enmascarado con la Función IF.ERROR () :

= IF.ERROR (expresión, acción si error)

Esta función es más eficiente que la solución de pre-Ribbon de la utilizaciónla Función IF () de la siguiente forma:

= IF (ESERROR (expresión), True Action, false Action)

Si todavía está trabajando con una versión pre-Ribbon, considere unafórmula de ayuda. Dos columnas de fórmulas simples serán más eficientesque una sola columna de funciones IF () .

Límite de Formatos condicionales

Muchas técnicas se basan en gran medida en el formato condicional,

pero a veces a un costo, sobre todo de procesamiento. Cada formato

condicional se evalúa cada vez que el libro realiza cálculos.

Utilizar el formato condicional con moderación y prudencia.

Demasiados formatos condicionales puede poner lentas las cosas.

Utilizar Tablas de Excel y Rangos con

Nombre.

Usar Tablas de Excel y rangos con nombre son dos características sorprendentes que mantienen susdatos y hace referencia a súper fácil. Se puede tomar un tiempo para acostumbrarse a ellas, perocuando comienzas a usarlas, la vida se pone fácil y rápida. En la creación de cuadros de mandobasados en datos, es casi siempre una buena idea para convertir sus datos en una tabla de Excel.

También tiene una ventaja añadida de hacer tus fórmulas más comprensibles.

Por ejemplo, ¿qué es más fácil de entender?

= Precio de venta Menos precio de costo

O

= SUMA (A1: A10) - sum (G1: G10)

Controlar opciones de cálculo.

Tiene una gama de opciones que permiten controlar la forma en que

se calcula en Excel. Puede cambiar las opciones usadas con más

frecuencia en Excel a través del grupo de cálculo en la

ficha fórmulas, en la cinta de opciones.

La figura 1. Grupo de cálculo en la ficha fórmulas

Para ver más opciones de cálculo de Excel, haga clic en la ficha

de archivo y haga clic en Opciones. En el cuadro de diálogo Opciones

de Excel, haga clic en la ficha fórmulas.

Controlar opciones de cálculo.

Figura 2. Opciones de cálculo de la ficha Fórmulas, en Opciones de

Excel

Muchas opciones de cálculo (Automático, Automático excepto para

tablas de datos, manual, vuelva a calcular libro antes de guardar) y la

configuración de iteración (Habilitar cálculo iterativo, máximo de

iteraciones, cambio máximo) operar a nivel de aplicación en lugar de

a nivel de libro (que son los mismos para todos los libros abiertos).

Para encontrar opciones avanzadas de cálculo, haga clic en la pestaña

Archivo y haga clic en Opciones. En el cuadro de diálogo Opciones de

Excel, haga clic en Opciones avanzadas. Bajo las Formulas la sección

Configuración de opciones de cálculo.

Controlar opciones de cálculo. Figura 3. Opciones avanzadas de cálculo

Para las versiones de Excel anteriores a Excel 2007, seleccione Opciones en el menú Herramientas y,a continuación, haga clic en la ficha cálculo, que contiene todas las opciones de cálculo.

Al iniciar Excel, o cuando se está ejecutando sin los libros abiertos, la configuración inicial del modode cálculo y de iteración se establecen a partir de la primera plantilla no, no se suman en libro queabra. Esto significa que los ajustes de cálculo en los libros abiertos más tarde se ignoran, aunque,por supuesto, puede cambiar manualmente la configuración de Excel en cualquier momento. Cuandose guarda un libro, los ajustes actuales de cálculo se almacenan en el libro.

Recomendaciones desde Manual de

Microsoft.

Recomendaciones desde Manual de

Microsoft 1/2.

Resumen

En Microsoft Excel, el rendimiento del recálculo se ve afectado por la forma en que los datos y las fórmulas están distribuidas en la hoja: La lista siguiente contiene sugerencias para optimizar las hojas de cálculo y aumentar la velocidad de recálculo:

Organice sus hojas verticalmente. Utilice solamente una o dos pantallas de columnas, pero con tantas filas como sea posible. Con un esquema estrictamente vertical, obtendrá un flujo de cálculo más claro.

Cuando sea posible, una fórmula debe hacer referencia únicamente a las celdas que estén por encima de ella. Por tanto, los cálculos deben continuar estrictamente hacia abajo: los datos sin procesar estarán en la parte superior y los resultados de los cálculos en la parte inferior.

Si sus fórmulas requieren muchos datos sin procesar, es preferible mover esos datos a otra hoja y vincularla a la que contiene las fórmulas.

Las fórmulas deben ser lo más sencillas posibles para evitar cálculos innecesarios. Si utiliza constantes en una fórmula, calcule su valor antes de introducirlas en la fórmula, en lugar de hacer que Excel las calcule cada vez que realice un cálculo de toda la hoja.

Reduzca o elimine el uso de tablas de datos en las hojas de cálculo o defina el cálculo de tablas como manual.

Si sólo necesita que se recalculen unas cuantas celdas, reemplace los signos de igual (=) en las celdas que desee recalcular. Esta mejora sólo surtirá efecto si se va a calcular un porcentaje muy pequeño de las fórmulas de la hoja.

Recomendaciones desde Manual de

Microsoft 2/2.

Cuando se deba recalcular un grupo determinado de fórmulas muchas veces, puede ser conveniente reemplazar el signo de igual (=) de las fórmulas que no necesita recalcular por una única cadena que no aparezca en otra parte. Las fórmulas que no tienen el signo de igual (=) no se recalculan (ya no se consideran fórmulas). Cuando Microsoft Excel haya recalculado las fórmulas que aún contienen el signo igual, busque la cadena única y restaure el signo igual (=).

Active la opción de cálculo Automático excepto tablas. Para ello, siga el procedimiento apropiado para su versión de Microsoft Excel.

Microsoft Excel 5.0 y posteriores-----------------------------

1. En el menú Herramientas, elija Opciones y seleccione la ficha Cálculo.

2. En la ficha Cálculo, seleccione la opción Automático excepto tablas.

Microsoft Excel 4.x y anteriores-------------------------------

1. En el menú Opciones, seleccione Cálculo.

2. Seleccione la opción Automático excepto tablas.

No utilice la opción Precisión como se muestra de la ficha de Cálculo (el cuadro de diálogo Opciones de cálculo Microsoft Excel 4.0 y anteriores). Esta opción retrasará el cálculo, ya que Excel deberá redondear los resultados mientras calcula.

Fuente: https://support.microsoft.com/es-es/kb/72622

Revisión Básica de Procesador y Ram.

Revisión de Disco, Ram y CPU.

Veamos un Archivo “Hola Mundo” con formato Excel:

en disco, pesa 8kb, y funcionando en Excel funcionando al 0%, con 29,

9MB en Ram, un entorno saludable de trabajo.

Revisión de Disco, Ram y CPU.

Veamos un caso extremo en Excel:

en disco, pesa 3.8 MB, y funcionando en Excel funcionando al 0%,

pero ojo !!!!! 336.3 MB en Ram, un entorno pésimo de trabajo,

candidato a aplicar nuestras estrategias antes mencionadas.

Conclusiones.

Siga estas prácticas, mucho usuarios en Foros, Blogs, compañeros de

trabajo, etc. han ocupado estas estrategias y han dado buenos

resultados.

Averigua si este problema ocurre con otras personas que ocupen los

mismos Archivos podría ser algo puntual del computador o alguna otra

cosa.

Si no se pueden solucionar estos problemas, me llamas porque tu

libro Excel es candidato a ser convertido en una Aplicación de

Escritorio o una Aplicación web que yo puedo hacer …

… es broma … revísalo con el Área de Desarrollo de tu Empresa o tu

proveedor de Software Externo.

Gracias.

Microsoft Excel y Microsoft Office son Marcas Registradas

de Microsoft Corporation.

Presentación y pruebas realizadas con Software Microsoft Original y

Licenciado.