Proceso Masivo de Datos

En la actualidad, las empresas manipulan una cantidad de datos gigantesca para realizar sus operaciones. Esto es, encontrar información con cientos de miles de registros no es algo extraño, sino que es algo común.

Entonces, las empresas deben saber como procesar tremenda cantidad de datos de manera rápida y eficiente, porque de lo contrario, perderían tiempo valioso al intentar procesar la información. Con ello se quiere decir que de nada sirve tener una macro en Excel que procese cientos de miles de filas que demore una buena cantidad de minutos, ya que dicho programa no es para nada eficiente y puede llevar a generar pérdidas a la empresa por el simple hecho de que “Los resultados no se obtuvieron a tiempo”.

Con esta premisa se decidió elaborar un artículo que lidie con este problema al tratar de encontrar una solución óptima para procesar una tabla de cien mil registros.

La tabla que se posee es una tabla básica de Ventas por Cliente, en la que se le otorgará un descuento según la cantidad de productos que compró. En la siguiente imagen se puede apreciar un pequeño pedazo de la tabla:

 

Nuestro objetivo es hallar el subtotal, la cantidad que se le va a descontar y el total a pagar en un tiempo rápido y aceptable. Para ello se utilizarán 5 métodos para resolver el problema, además de usar los métodos para acelerar macros que ya fueron vistos en otro artículo de este blog.

Para poder hallar dichos resultados se definieron 3 funciones definidas por el usuario:

Entonces, sin más preámbulo, se presentarán los siguientes métodos:

• Usar únicamente cells y variables variant:

Este método de por sí es el más ineficiente debido a que se está forzando al programa a que use el método cells. El problema con este método es que el programa debe acceder a la hoja de cálculo, el archivo en el disco duro, y esta es una operación muy lenta, comparado con la velocidad de procesamiento de las computadoras actuales. Además, al usar variables variant, la computadora debe desperdiciar algunos nanosegundos en decidir que tipo de variable es el más adecuado para guardar la información.

En la siguiente imagen se puede apreciar el código:

Y se obtuvo el siguiente tiempo de ejecución: 

 

• Usar cells en conjunto con variables explícitamente definidas:

Este método es similar al anterior, la única diferencia es que guardamos en variables de tipo double y long los datos de los Precios unitarios y subtotales usando una única vez el método cells. También se guardan las respuestas en variables antes de escribirlas mediante cells.

De esta manera se evita abusar del método cells, evitando el acceso al disco duro, al usar variables definidas en la memoria de la computadora. Para estos dispositivos, trabajar en la memoria RAM es mucho más rápido que trabajar en el disco duro.

En la siguiente imagen se puede apreciar el código: 

Se obtuvo el siguiente tiempo de ejecución:

• Usar arreglos unidimensionales:

En esta solución se evita usar el método cells, reemplazando la lectura de datos por el método “value2” de un rango. Esto quiere decir que se pueden guardar los datos de un rango de celdas en un arreglo de tipo variant y, también, se pueden escribir arreglos directamente a un rango de celdas usando el método “value2”.

Cabe resaltar que escribir un conjunto de datos almacenados en un arreglo directamente a la hoja de cálculo, sin usar bucles, es mucho más optimo que recorrer el arreglo e imprimir los datos mediante cells. En otras palabras, este método es más rápido que el anterior.

En la siguiente imagen se puede apreciar el código:

Se obtuvo el siguiente tiempo de ejecución:

• Usar tablas de Excel y fórmulas de la hoja de cálculo en conjunto con VBA:

Esta solución es mucho más óptima que las anteriores porque utiliza las ventajas que nos otorgan las tablas de Excel, esto es, rangos dinámicos, auto rellenado de fórmulas, entre otros.

Una razón de porqué también este método es muy rápido es que, al escribir las fórmulas en las celdas, podemos utilizar la característica de Excel de utilizar todos los procesadores de la computadora. Entonces, si se posee una computadora I7, Excel utilizará las 8 CPU que posee, mientas que, si utilizásemos código de VBA puro, estamos limitados a usar solo 1 CPU, las demás no se utilizan. Claro, podemos utilizarlas si usamos programación multi hilo en el lenguaje VBA, pero coordinar la ejecución de un programa usando varios núcleos no es una tarea sencilla, aunque tampoco es imposible y tan complicada.

Algo que también se hizo fue reemplazar todas las fórmulas que se escribió en la hoja de cálculo mediante VBA por sus valores hallados. Se realizó esto porque al tener múltiples fórmulas en una hoja de cálculo hace que la eficiencia disminuya drásticamente, en especial si se tienen muchas fórmulas volátiles como “Aleatorio”, “Aleatorio.Entre”, “Indirecto”, etc.

En la siguiente imagen se puede apreciar el código:

Se obtuvo el siguiente tiempo de ejecución:

• Usar el procesamiento por lotes:

La última solución que presentaremos es una solución algo diferente, ya que requiere algo más de cálculo intermedio, sin embargo, es la más optima de todas las soluciones, el procesamiento por lotes.

Básicamente lo que se hace es procesar la tabla por bloques de diez mil filas hasta llegar al final. Este tamaño de bloque puede variar, pero lo común es usar diez mil. Todo este bloque, sin embargo, es procesado directamente en la memoria RAM usando arreglos de dos dimensiones, y no en la hoja de cálculo con el método cells. Tampoco se guarda la información en arreglos de 1 dimensión, línea por línea.

Este tipo de procesamiento es muy rápido porque procesa la grande cantidad de datos en la memoria RAM y no en la hoja de cálculo directamente.

En las siguientes imágenes se puede apreciar el código:

Se obtuvo el siguiente tiempo de ejecución:

En conclusión, una hoja de Excel puede ser optimizada de distintas formas, esto significa que no necesariamente se deba usar cada método expuesto por separado, sino que se pueden mezclar, según la solución lo requiera. Entonces, antes de decidir comprar una computadora más potente a la que se tiene en la empresa, o decidir desarrollar un sistema que deba hacer lo que ya se hace en Excel, sólo porque “El Excel está muy lento”, simplemente tome un respiro, cálmese, utilice una hoja binaria de Excel y optimice la hoja de cálculo. El causante más probable de la lentitud de su hoja de Excel es la falta de optimización de esta.

Puede ver el código completo de la macro en el archivo de Excel adjunto “Procesamiento_Masivo_De_Datos.xlsb”

Procesamiento_Masivo_De_Datos