O método de menos cadrados é un procedemento matemático para construír unha ecuación lineal que correspondería o máis preto a un conxunto de dúas series de números. O propósito de aplicar este método é minimizar o erro cuadrático total. Excel ten ferramentas coas que pode usar este método nos seus cálculos. Vexamos como se fai isto.
Usando o método en Excel
O método de menos cadrados (o mínimo cadrado) é unha descrición matemática da dependencia dunha variable do segundo. Pódese usar na previsión.
Habilitar o complemento de busca de solucións
Para usar OLS en Excel, ten que habilitar o complemento "Buscar unha solución"que está desactivado de xeito predeterminado.
- Vai á lapela Arquivo.
- Fai clic no nome da sección "Opcións".
- Na xanela que se abre, detén a selección na subsección "Complementos".
- En bloque "Xestión"situado na parte inferior da xanela, configure o interruptor Complementos de Excel (se se establece outro valor nel) e fai clic no botón "Vaia ...".
- Ábrese unha pequena ventá. Coloque unha marca preto do parámetro "Buscar unha solución". Fai clic no botón "Está ben".
Funciona agora Busca unha solución activouse en Excel e as súas ferramentas apareceron na cinta.
Lección: Buscar unha solución en Excel
Condicións da tarefa
Describimos o uso de OLS nun exemplo específico. Temos dúas filas de números x e ycuxa secuencia móstrase na imaxe de abaixo.
A función pode describir esta dependencia con máis precisión:
y = a + nx
É máis, sábese que con x = 0 y igualmente igual 0. Polo tanto, esta ecuación pode ser descrita pola dependencia y = nx.
Temos que atopar a suma mínima dos cadrados da diferenza.
Solución
Voltamos á descrición da aplicación directa do método.
- Á esquerda do primeiro valor x pon o número 1. Este será o valor aproximado do valor do primeiro coeficiente n.
- Á dereita da columna y engade outra columna - nx. Na primeira cela desta columna, escribimos a fórmula de multiplicación de coeficientes n por cela da primeira variable x. Ao mesmo tempo, facemos a ligazón ao campo co coeficiente absoluto, xa que este valor non cambiará. Fai clic no botón Introduza.
- Usando o marcador de recheo, copia esta fórmula a todo o intervalo da táboa na columna seguinte.
- Nunha cela separada, calculamos a suma das diferenzas dos cadrados dos valores y e nx. Para iso, fai clic no botón "Función de inserción".
- Á intemperie "Asistente de funcións" buscando un rexistro SUMMKVRAZN. Seleccionaa e fai clic no botón. "Está ben".
- Ábrese a xanela do argumento. No campo "Array_x" introduza o rango de celas de columna y. No campo Array_y introduza o rango de celas de columna nx. Para introducir valores, simplemente colocamos o cursor no campo e seleccionamos o rango correspondente na folla. Despois de entrar, faga clic no botón "Está ben".
- Vai á lapela "Datos". Na cinta da caixa de ferramentas "Análise" fai clic no botón "Buscar unha solución".
- Ábrese a xanela de opcións desta ferramenta. No campo "Optimizar a función obxectiva" especifique o enderezo da cela coa fórmula SUMMKVRAZN. En parámetro "Para" Asegúrese de poñer o interruptor en posición "Mínimo". No campo "Cambiando células" especifique a dirección co valor do coeficiente n. Fai clic no botón "Buscar unha solución".
- A solución mostrarase na cela do coeficiente n. É este valor o que será o cadrado menos da función. Se o resultado satisfai ao usuario, faga clic no botón "Está ben" nunha xanela adicional.
Como podes ver, a aplicación do método dos menos cadrados é un procedemento matemático bastante complicado. Amosámolo en acción co exemplo máis sinxelo, e hai casos moito máis complexos. Non obstante, o kit de ferramentas de Microsoft Excel está deseñado para simplificar o máximo posible os cálculos.
