Líneas de tendencia

Me preguntaba un familiar la manera de obtener una recta sobre una nube de puntos generados de la toma de muestras de un ejercicio de biologia. Había realizado tres mediciones en base a una concentraciones sobre cierta proteina, de las que obtuvo una medición de 'absorbancia'.

La idea es calcular una recta de regresión para estimar en un futuro nuevas medidas en base a alguna de las dos variables.

Para obtener la solución a nuestro problema optaremos por la vía gráfica (en otra entrada lo solucionaremos mediante funciones). Para ello deberemos seleccionar en nuestra hoja de cálculo el rango $A$1:$D$6, para posteriormente seleccionar el Asistente para gráficos. Marcaremos del Paso 1 el tipo de gráfico XY (dispersión).

No entraremos ahora en los detalles de cómo fabricar un gráfico y en todas sus opciones, tan solo mencionar que para resolver nuestra consulta deberemos desde el Paso 2 del Asistente generar una única serie con todos los datos.

donde los parametros de la serie son:

Nombre:= "Absorbancia"
Valores de X:=(Hoja2!$A$2:$A$6;Hoja2!$A$2:$A$6;Hoja2!$A$2:$A$6)
Valores de Y:=(Hoja2!$B$2:$B$6;Hoja2!$C$2:$C$6;Hoja2!$D$2:$D$6)

Con esto hemos convertido las tres series en una; presionaremos entonces directamente Finalizar desde este segundo paso del Asistente. Tenemos ya generado nuestro gráfico, punto de partida para obtener la recta que necesitamos.

Ahora sólo nos queda obtener la recta de regresion que mejor estime la relación entre las dos variables (Concentración y absorbancia). Haremos click con el botón derecho del ratón sobre la serie de datos del gráfico recién generada, y elegiremos la opción de 'Agrega linea de tendencia'. Desde la ventana de diálogo abierta seleccionaremos el Tipo de tendencia o regresión lineal (se nos pide que obtngamos una recta), y desde la pestaña de Opciones marcaremo el tick de 'Presentar ecuación en el gráfico'; también marcaremos el tick de 'Presentar el valor de R cuadrado'. Tras pulsar Aceptar tendremos lo que buscabamos, i.e., la recta de regresión para las series de Concentraciones-Absorbancias.

Por tanto la recta de regresión buscada es:
y = 0,0343x + 0,1153
con la que podríamos obtener valores estimados de ambas variables (Concentración y Absorbancia).