El mètode dels mínims quadrats és un procediment matemàtic per construir una equació lineal que més es correspongui amb un conjunt de dues files de nombres. El propòsit d’aquest mètode és minimitzar l’error quadrat total. Excel té eines per utilitzar aquest mètode per als càlculs. Vegem com es fa això.
Ús del mètode en Excel
El mètode dels mínims quadrats (OLS) és una descripció matemàtica de la dependència d’una variable a la segona. Es pot utilitzar en la previsió.
Habilitació del complement "Cercador de solucions"
Per poder utilitzar OLS a Excel, heu d’habilitar el complement "Cerca d'una solució"que està desactivada per defecte.
- Aneu a la pestanya "Fitxer".
- Feu clic al nom de la secció "Opcions".
- A la finestra que s'obre, atureu la selecció a la subsecció Complements.
- En bloc "Gestió"que es troba a la part inferior de la finestra, poseu el commutador a la posició Complements d'Excel (si hi ha un altre valor establert en ell) i premeu el botó "Aneu ...".
- S'obre una petita finestra. Posem-hi una marca sobre el paràmetre "Trobar una solució". Premeu el botó "D'acord".
Ara funciona Trobar una solució Excel està activat i les seves eines apareixen a la cinta.
Lliçó: Cerqueu una solució a Excel
Condicions del problema
Es descriu l’ús de multinacionals amb un exemple concret. Tenim dues files de nombres x i y, la seqüència de la qual es presenta a la imatge següent.
La major dependència d'aquesta descripció pot descriure la funció:
y = a + nx
Al mateix temps, se sap que amb x = 0 y també igual 0. Per tant, aquesta equació pot ser descrita per la dependència y = nx.
Hem de trobar la suma mínima de quadrats de la diferència.
Solució
Anem a procedir a la descripció de l’aplicació directa del mètode.
- A l'esquerra del primer valor x posa el número 1. Aquest serà el valor aproximat del primer valor del coeficient. n.
- A la dreta de la columna y afegeix una columna més - nx. A la primera cel·la d’aquesta columna, escriviu la fórmula per multiplicar el coeficient n a la primera cel·la variable x. Al mateix temps, fem la referència al camp amb el coeficient absolut, ja que aquest valor no canviarà. Feu clic al botó Introduïu.
- Utilitzant el marcador d’ompliment, copieu aquesta fórmula a tot l’interior de la taula a la columna següent.
- En una cel·la separada, calculem la suma de les diferències de quadrats de valors. y i nx. Per fer-ho, feu clic al botó "Insereix la funció".
- A l’obertura "Mestre de funcions" buscant un registre "SUMMKVRAZN". Seleccioneu-lo i feu clic al botó. "D'acord".
- S'obrirà la finestra d'argument. Al camp "Array_x" introduïu l’interval de cel·les de la columna y. Al camp "Array_y" introduïu l’interval de cel·les de la columna nx. Per introduir valors, simplement poseu el cursor al camp i seleccioneu l’interval adequat al full. Després d'entrar, feu clic al botó "D'acord".
- Aneu a la pestanya "Dades". A la cinta del bloc d’eines "Anàlisi" premeu el botó "Trobar una solució".
- S'obrirà la finestra de paràmetres d'aquesta eina. Al camp "Optimitza la funció de destinació" especifiqueu l'adreça de la cel·la amb la fórmula "SUMMKVRAZN". En el paràmetre "Fins" Assegureu-vos de configurar el commutador a la posició "Mínim". Al camp "Canvi de cel·les" especifiquem l’adreça amb valor de coeficient n. Premeu el botó "Trobar una solució".
- La solució es mostrarà a la cel·la del coeficient. n. Aquest valor serà el quadrat més petit de la funció. Si el resultat satisfà l’usuari, feu clic al botó "D'acord" a la finestra addicional.
Com es pot veure, l'aplicació del mètode de mínims quadrats és un procediment matemàtic bastant complicat. Ho vam mostrar en acció amb l’exemple més simple i hi ha casos molt més complicats. Tanmateix, el conjunt d'eines de Microsoft Excel està dissenyat per simplificar els càlculs tant com sigui possible.
