Mejora de Rendimiento y Aceleración de Microsoft Excel

Una presentación para ver las mejores estrategias de mejora y aceleración de performance de Excel. Algo muy habitual en las empresas que presentan problemas con Excel, algo que a veces es transversal a toda la organización.

1. Excel Mejorar rendimiento en Libros Por Juan Astudillo

2. 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.

3. Consejos Generales 1 . Actualiza tu computador / Laptop: Excel depende en gran medida de RAM y procesador para velocidad. 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 hoja de cálculo: Antes de calcular, Excel crea una pila de cálculo, o un enfoque óptimo para el cálculo de las células basadas en las dependencias de celdas . Esto ayudará a Excel si las fórmulas no saltan por todo 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 la hoja. 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ámicas durante las actualizaciones, la creación de una macro específica para tus necesidades, etc .

4. 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.

5. 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 .

6. 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.

7. 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.

8. 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 las fórmulas), el punto de Limpiar los objetos no te será factible. Puedes, sin embargo, convertir las fórmulas a valores estáticos. Pero ello sólo si estás seguro de que nunca tendrás que volver a calcular las fórmulas que generan los valores en el primer lugar. Para convertir las fórmulas a sus valores estáticos, utilizar pegado especial y selecciona Valores para pegar. Si lo haces, se sobreponen a las fórmulas con los resultados de esas fórmulas. Ten cuidado, verifica si las fórmulas realmente se han ido.

9. 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 .

10. Evitar las Fórmulas de Matriz ( Array Formulas). Gurús y usuarios avanzados aman por igual las matrices, y son una herramienta poderosa. Por desgracia, son monstruos come-memoria . Puede ser que sea difícil de creer, pero un par de fórmulas regulares calculará más rápido que su matriz equivalente. Si las fórmulas de ayuda no son adecuadas, considere una función definida por el usuario. Además, es posible que pueda sustituir las matrices con nuevas funciones, como la función SUMIF(), de COUNTIF(), y AVERAGEIF. (Las fórmulas de matriz se ejecutan mejor en las versiones de Excel con barra Ribbon ).

11. 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.

12. 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 reemplazar la 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ón la 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 una fórmula de ayuda. Dos columnas de fórmulas simples serán más eficientes que una sola columna de funciones IF () .

13. 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.

14. Utilizar Tablas de Excel y Rangos con Nombre. Usar Tablas de Excel y rangos con nombre son dos características sorprendentes que mantienen sus datos y hace referencia a súper fácil. Se puede tomar un tiempo para acostumbrarse a ellas, pero cuando comienzas a usarlas, la vida se pone fácil y rápida. En la creación de cuadros de mando basados 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)

15. 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 .

16. 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.

17. 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 modo de cálculo y de iteración se establecen a partir de la primera plantilla no, no se suman en libro que abra. 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. Cuando se guarda un libro, los ajustes actuales de cálculo se almacenan en el libro .

18. Recomendaciones desde Manual de Microsoft.

19. 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 .

20. 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

21. Revisión Básica de Procesador y Ram .

22. 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.

23. 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.

24. 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.

25. Gracias. Microsoft Excel y Microsoft Office son Marcas Registradas de Microsoft Corporation . Presentación y pruebas realizadas con Software Microsoft Original y Licenciado.