Facultad de Ciencias FísicasLaboratorio de Física I
Excel: Regresión Lineal
• Como usar Excel para resolver una regresión lineal usando la función predefinida estimacion.lineal.
• Como hacer la gráfica.
Facultad de Ciencias FísicasLaboratorio de Física I
Ejemplo•Los datos de la tabla adjunta, x e y exacto, cumplen la relación y=3x+2
•Los datos de la columna C se han generado aleatoriamente con unos ciertos márgenes Consideramos estos valores como errores aleatorios (perturbación)
•La columna D contiene la suma de y + perturbación
Facultad de Ciencias FísicasLaboratorio de Física I
Ejemplo: Cálculo recta regresiónVamos a calcular la recta de regresión lineal de y+perturbación sobre x.
seleccionamos un matriz de 5x2 en la hoja de Excel
Seleccionamos la función ESTIMACION.LINEAL, de modo análogo a los casos anteriores
Facultad de Ciencias FísicasLaboratorio de Física I
Ejemplo: Cálculo recta regresiónEn la ventana de argumentos de la función, seleccionamos la primera entrada y a continuación seleccionamos los valores de la columna y+ perturbación
Facultad de Ciencias FísicasLaboratorio de Física I
Ejemplo: Cálculo recta regresión
A continuación seleccionamos la segunda entrada y los valores correspondientes a la columna x
Facultad de Ciencias FísicasLaboratorio de Física I
Ejemplo: Cálculo recta regresiónDejamos en blanco la tercera entrada.Seleccionamos la cuarta entrada y escribimos en ella la palabra: VERDADEROEsto hace que excel calcule los valores estadísticos asociados a la regresión.Pulsamos el botón aceptarPara visualizar los resultados es preciso pulsar la tecla F2 y a continuación: Ctrl+mayúsculas+ retorno de carro.
Facultad de Ciencias FísicasLaboratorio de Física I
Ejemplo: Cálculo recta regresión
Pendiente: m Término independiente c
Incertidumbre en m Incertidumbre
en cCoeficiente de determinación r2
Suma de los residuos al cuadrado
OJO: Excel NO tiene en cuenta el
coeficiente de la “t de Student” en las
incertidumbres
Facultad de Ciencias FísicasLaboratorio de Física I
Ejemplo: Valores estimados de yPodemos obtener valores estimados de y, mediante la recta de regresión de mínimos cuadrados empleando la función
TENDENCIA.
Facultad de Ciencias FísicasLaboratorio de Física I
Ejemplo: Valores estimados de yVolvemos a seleccionar la columna que contiene los valores de y la que contiene los valores de x.
En nueva_matriz_x, se deben incluir los valores xi para los que queremos estimar y.
En este ejemplo se han empleado los mismos valores de x originales
Facultad de Ciencias FísicasLaboratorio de Física I
Ejemplo: Valores estimados de y
Para obtener los valores de y estimados, seleccionamos una región con tantas celdas como valores de x hemos incluido en nueva_matriz_x
A continuación se pulsa la tecla F2 y la combinación de teclas: Ctrl+mayusculas+intro, en la columna selecionada aparecen los valores estimados de y
Facultad de Ciencias FísicasLaboratorio de Física I
Ejemplo: gráficos de resultados•Vamos a emplear la posibilidades gráficas de Excel para representar los resultados obtenidos en el cálculo de la recta de regresión.
•Como ya se explicó en la introducción a Excel, elegimos un gráfico de dispersión
Facultad de Ciencias FísicasLaboratorio de Física I
Ejemplo: gráficos de resultados
Seleccionamos “Series” y luego “agregar” para definir los datos que vamos a representar.
Rellenaremos:
1- Nombre (lo escribimos)
2- Valores de X (seleccionamos)
3- Valores de Y (seleccionamos)
Pulsamos “Siguiente”
Facultad de Ciencias FísicasLaboratorio de Física I
Ejemplo: gráficos de resultadosEn la siguiente pantalla podemos elegir más detalles del gráfico.
Rellenaremos al menos
1- Título eje X (con unidades)
2- Título eje Y (con unidades)
Pulsamos “Siguiente”
Facultad de Ciencias FísicasLaboratorio de Física I
Ejemplo: gráficos de resultados
En la esta pantalla sólo seleccionamos donde se dibujará el gráfico
Seleccionamos una de las dos opciones, la que prefiramos.
Pulsamos “Finalizar”
Facultad de Ciencias FísicasLaboratorio de Física I
Ejemplo: gráficos de resultadosUna vez obtenido el gráfico podemos editarlo para mejorarlo.
Colocando el puntero sobre uno de los puntos, al hacer click con el botón derecho se abrirá una caja nueva.
Formato de serie de datos: permite añadir más información como barras de error
Agregar línea de tendencia: permite representar el ajuste
Facultad de Ciencias FísicasLaboratorio de Física I
Ejemplo: gráficos de resultadosEn este ejemplo hemos introducido dos columnas con las incertidumbres en X e Y respectivamente.
Las vamos a usar para dibujar las barras de error.
Seleccionamos “Barras de error X”
Facultad de Ciencias FísicasLaboratorio de Física I
Ejemplo: gráficos de resultadosEn este ejemplo hemos introducido dos columnas con unas posibles incertidumbres en X e Y respectivamente.
Las vamos a usar para dibujar las barras de error.
Seleccionamos “Barras de error X”
Y las opciones que aparecen en el dibujo.
Igualmente en Y
Facultad de Ciencias FísicasLaboratorio de Física I
Ejemplo: gráficos de resultadosObtenemos el siguiente gráfico, ya con “Barras de error”.
Volvemos a seleccionar un punto, hacemos click con el botón derecho y vamos ahora a “Agregar línea de tendencia” para dibujar la línea de regresión.
Facultad de Ciencias FísicasLaboratorio de Física I
Ejemplo: gráficos de resultadosEscogeremos el tipo de regresión “Lineal” (está por defecto).
En “Opciones” seleccionaremos las que aparecen marcadas:
Facultad de Ciencias FísicasLaboratorio de Física I
Ejemplo: gráficos de resultados
Luego podemos editar cada uno de los objetos que aparecen en la gráfica. Seleccionándolos con el ratón. Por ejemplo:
-La ecuación
- La posición de los títulos
- Los ejes
- …