VISUAL BASIC en EXCEL y Ajuste de MINIMOS CUADRADOS

Mínimos Cuadrados en Visual Basic de Aplicaciones en Excel
por Alfonso Arivilca.

Una manera fácil y sencilla de obtener una recta de mínimos cuadrados según un ajuste de puntos es utilizando la hoja de Excel, pero con macros. Consiste en dibujar un formulario que se asemeje al proceso de cálculo realizado en papel, y utilizar los modelos matemáticos para dicha solución expresada en código Basic.

Sin embargo, hay limitaciones que deben tenerse en cuenta en este proyecto al utilizar Visual Basic en Excel:

La configuración regional del sistema operativo y de Excel enfatiza la distinción entre el punto (.) y la coma (,) para separar valores. Si la configuración regional del sistema operativo separa los valores con comas y al ingresar datos a través de formularios lo hacemos con puntos, Excel interpretará los valores como datos "String", lo que puede llevar a cálculos incorrectos u otras rutinas que no estén relacionadas con el desarrollo del código. Por lo tanto, recomiendo tener en cuenta este aspecto o, en el peor de los casos, indicar en las líneas de código cómo deben interpretarse los puntos y las comas.

Los programas realizados en Visual Basic para Excel pueden requerir que se desactive la seguridad del programa en otros equipos o que se otorgue un permiso previo para ejecutar la macro.

Se puede diseñar un software a medida según nuestras necesidades. Por ejemplo, podemos utilizar los valores obtenidos en el formulario a través del código y transferirlos a la hoja de Excel para que una macro pueda dibujar la gráfica con los puntos ajustados.

Una sugerencia de interfaz para el formulario en Visual Basic de Excel para ajustar puntos puede ser la siguiente:

El formulario contiene un text, para ingresar el numero de pares ordenados

contiene texts para ingresar el X y el Y por separado, el botón "ingresar" esta en la propiedad "default=true"

esto quiere decir que cuando termina de ingresar el X y el Y, presiona <enter> en el teclado y los datos se ingresan a las listas correspondientes sin necesidad de hacer click en el botón.

POR EJEMPLO:
tenemos los siguientes datos de incremento de velocidad de flujo :

si se quiere calcular de manera alternativa a las herramientas de excel la recta que definen los puntos, entonces utilizamos nuestro programa desarrollado en el editor de codigo de Visual Basic, y la hemos configurado para que responda a la instruccion de carga de "Ctrl + Y"

Inmediatamente ingresamos los datos de nuestra tabla al formulario:

se obtiene los valores de las sumatorias y una recta de :

Y = 6.6442 X + 40.5615

He programado la Opción a corregir algun valor de X o Y que no fuera correcto, esto sin embargo en una programación de codigo aun mas especifica podria se extraida de las celdas de excel,para evitar inconvenietes en el ingreso de valores al formulario.

si se dese modificar algun valor solo bastará con hacer doble click en las columnas permitidas que son X y Y se obtendra el siguiente formulario para la modificación, esto funciona incluso despues de haber realizado el cálculo:

He programado de esta manera, porque los requerimientos de calcular un ajuste de pares ordenados, no solo es de utilidad en excel sino en cualquier otra situación, muchas veces no informaticas sino en el papel. y el usuario final busca alternativas mas sencillas para la solución ciertas veces muy rutinarias.

Parte del codigo que hace posible funcionar este programa es el siguiente :

"divide y venceras" es mejor trabajar con procedimientos que pueden ser llamados desde cualquier parte del código, a trabajar con una sola estructura donde se intente ingresar todas las intrucciones, al final ese metodo fracasa. por ello he estructurado unas "sub rutinas" para el mejor control.

Algo de código de consistencia, es frecuente en los sistemas de programación consistenciar para evitar que el usuario final cometa errores que permitan que el programa se cuelgue y apague, por no decir necesario e imprecindible realizar la consistencia. aqui parte de ella, pero esto ya no pertenece a la parte matemática.

Este programa es sencillo pero de mucha aplicación en la representación de datos estadisticos, puede sin duda ser mejorado con el ingreso de algoritmos y códigos mas elaborados y convertirlo sin ningún problema en un potente simulador matemático.

Las aplicaciones son en todas las ingenierias.

Si deseas el programa puedes dejar un comentario o Enviar un email para enviartelo.

    © Derechos Reservados

    Diseño de interfaz y programación:     Ing. Jose Alfonso Arivilca.

     vbx_desarrollo@mailfence.com / pjose_alfonso@hotmail.com

     Ottawa Ca. - Arequipa Pe. 2024