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

- …