O método dos mínimos cadrados é un procedemento matemático para construír unha ecuación lineal que máis correspondería cun conxunto de dúas filas de números. O propósito deste método é minimizar o erro cadrado total. Excel ten ferramentas para usar este método para os cálculos. Vexamos como se fai isto.
Usar o método en Excel
O método dos mínimos cadrados (OLS) é unha descrición matemática da dependencia dunha variable no segundo. Pode usarse na previsión.
Activando o complemento "Buscador de solucións"
Para usar OLS en Excel, cómpre habilitar o complemento "Buscar unha solución"que está desactivado por defecto.
- Vaia á pestana "Ficheiro".
- Fai clic no nome da sección "Opcións".
- Na ventá que se abre, pare a selección na subsección Complementos.
- En bloque "Xestión"que se atopa na parte inferior da xanela, axuste a opción na posición Complementos de Excel (se hai outro valor nel) e preme o botón "Vaia ...".
- Ábrese unha pequena ventá. Poñemos un tick na parte sobre o parámetro "Buscar unha solución". Pulsamos o botón "OK".
Agora funciona Buscar unha solución Excel está activado e as súas ferramentas aparecen na cinta.
Lección: Busca unha solución en Excel
Condicións do problema
Describimos o uso de multinacionais cun exemplo específico. Temos dúas filas de números x e y, a secuencia de que se presenta na imaxe de abaixo.
A maior dependencia deste xeito pode describir a función:
y = a + nx
Ao mesmo tempo, sábese que con x = 0 y igual 0. Polo tanto, esta ecuación pode ser descrita pola dependencia y = nx.
Temos que atopar a suma mínima de cadrados da diferenza.
Solución
Pasemos á descrición da aplicación directa do método.
- Á esquerda do primeiro valor x poñer o número 1. Este será o valor aproximado do primeiro valor do coeficiente. n.
- Á dereita da columna y engadir unha columna máis nx. Na primeira cela desta columna, escriba a fórmula para multiplicar o coeficiente n na primeira cela variable x. Ao mesmo tempo, facemos unha referencia absoluta a un campo cun coeficiente, xa que este valor non cambiará. Fai clic no botón Intro.
- Utilizando o marcador de recheo, copie esta fórmula en todo o intervalo da táboa na columna de abaixo.
- Nunha cela separada, calculamos a suma das diferenzas dos cadrados dos valores. y e nx. Para facelo, fai clic no botón "Inserir función".
- No aberto "Mestre das funcións" buscando un rexistro "SUMMKVRAZN". Selecciona e fai clic no botón. "OK".
- Abrirase a ventá de argumento. No campo "Array_x" Introduza o intervalo de cela da columna y. No campo "Array_y" Introduza o intervalo de cela da columna nx. Para introducir valores, só tes que axustar o cursor no campo e selecciona o intervalo axeitado na folla. Despois de introducir prema no botón "OK".
- Vaia á pestana "Datos". Na cinta do bloque de ferramentas "Análise" prema o botón "Buscar unha solución".
- Ábrese a ventá de parámetros desta ferramenta. No campo "Optimizar a función de destino" especifique o enderezo da cela coa fórmula "SUMMKVRAZN". No parámetro "Ata" Asegúrese de axustar o cambio na posición "Mínimo". No campo "Cambiar celas" especificamos o enderezo con valor de coeficiente n. Pulsamos o botón "Atopar unha solución".
- A solución mostrarase na cela de coeficiente. n. Este valor será o cadrado máis pequeno da función. Se o resultado satisfai ao usuario, fai clic no botón "OK" na xanela adicional.
Como podemos ver, a aplicación do método dos mínimos cadrados é un procedemento matemático bastante complicado. Mostrámolo en acción sobre o exemplo máis sinxelo e hai casos moito máis complexos. Non obstante, o conxunto de ferramentas de Microsoft Excel está deseñado para simplificar os cálculos na medida do posible.
