En el artículo Cómo crear un breve modelo de regresión lineal en Excel, lo que no se mostró fue cómo incluir una elipse en torno a los datos, es decir, crear un límite de datos equitativo, problema que ahora se ha resuelto. Para los recién llegados, los pasos 1 a 3 se repiten aquí y luego los nuevos pasos de modificación comenzarán con la Parte 3, paso 11 (de modo que si el trabajo ya se realizó según el artículo anterior, comience en la Parte 3, paso 11). Habrá nuevos datos que estarán más dispersos en términos de varianza, para ilustrar mejor la utilidad de un enfoque elíptico, que los nuevos datos se incluyen en la siguiente imagen.

Parte uno de tres:
El tutorial

  1. 1 Abra un nuevo libro de trabajo en Excel y cree 3 hojas de trabajo: Datos, gráficos y salvaciones. Guarde el libro de trabajo como Regresión lineal - Lección modificada, o algo similar, en una carpeta de archivos lógica.
  2. 2 Establecer Preferencias: Abre Preferencias en el menú de Excel. Configuración recomendada: configure General a R1C1 Desactivado y muestre los 10 documentos más recientes; Editar - establece todas las opciones principales para verificadas, excepto el sistema de fecha de conversión automática. Mostrar el número de decimales = en blanco (para los enteros preferidos), conservar la visualización de las fechas y establecer 30 para el límite del siglo XXI; Ver: muestra la barra de fórmulas y la barra de estado, desplázate para ver los comentarios y todos los objetos, mostrar las líneas de la cuadrícula y todos los cuadros debajo de ese auto o marcado; Gráfico: muestra los nombres de los gráficos y los marcadores de datos en el vuelo estacionario. Deje el descanso sin control por el momento; Cálculo: automáticamente y calcule antes de guardar, cambio máximo de .000,000,000,000,01 sin comas, ya que la búsqueda de objetivos se realiza mucho y guarda los valores del enlace externo y usa el sistema 1904; Comprobación de errores: comprueba todo; Guardar - guardar la imagen de vista previa con nuevos archivos y guardar Autorecover luego de 5 minutos; Cinta - todo marcado excepto Ocultar títulos de grupo y Desarrollador.
  3. 3Seleccione entre 1 y A en la esquina superior izquierda de la hoja de trabajo para seleccionar toda la hoja y haga Formato de alineación de celdas Horizontal Centro y Tamaño de fuente 9 o 10, o lo que sea más cómodo para ver.
  4. 4 Ingrese los encabezados de las columnas en la fila 1: B1: X; C1: Y; D1: X. E1: Y F1: Y. Ingrese los encabezados de las columnas de las filas 2: A2: Estudiante; B2 SAT; C2: GPA; D2: VARIACIÓN; E2: VARIACIÓN; F2: TENDENCIA, G2: Cambio.
  5. 5 Editar Ir al rango de celdas A3: A22, ingrese 1 y edite Columnas de la serie de relleno Valor de paso lineal 1 OK, hay 20 estudiantes que desean conocer la tendencia de sus GPA dependientes con la variable independiente del puntaje del examen SAT al ingresar a una nueva universidad, es decir, sus calificaciones ¿Es probable que se levante, caiga o permanezca más o menos igual?
  6. 6 Ingrese los datos para responder esta pregunta. En pares x, y como se muestra en la imagen debajo del Paso 22, o como sigue: para el estudiante 1, X, Y = 935 para X o SAT, 2.2 para Y o GPA; estudiante 2 1260,3,1; entonces 1105,2.6; 1320,3.3; 1450,3.8; 960,2.2; 1360,3.2; 900,2.2; 1020,2.2; 1380,3.6; 940,2.3; 1190,2.8; 1000,2.2; 945,2.3; 990,2.4; 1000,2.2; 1040,2.3; 1570,3.9; 1530,3.8; 980,2.4.
  7. 7 Seleccione la celda A23 e ingrese MEAN. Ingrese las fórmulas sin comillas en la celda B23 "= AVERAGE (B3: B22)" y cópielas y péguelas en la celda C23. Seleccione la celda C23 e Insertar nombre Definir nombre Y_Bar para la celda $ C $ 23. Seleccione la celda B23 e Insertar nombre Defina el nombre X_Bar para la celda $ B $ 23. Seleccione el rango de celda B23: C23 y Formato de celda Color de fuente Rojo y Negrita.
  8. 8 Editar Ir al rango de celdas D3: D22 y con la celda D3 la celda activa y con iluminación alta, ingresar w / o cita la fórmula "= (B3-X_BAR) ^ 2" y Editar Rellenar hacia abajo. Editar Ir al rango de celdas E3: E22 y con la celda E3 la celda activa y con iluminación alta, ingresar w / o cita la fórmula "= (C3-Y_BAR) ^ 2" y Editar Rellenar hacia abajo. Seleccione el rango de celda D3: D23 y haga Formato de celdas Número Número Decimales 4.
  9. 9 Seleccione la celda D23 e ingrese sin comillas la fórmula "= SUMA (D3: D22) / (20-1)" y cópiela y péguela en la celda E23. Seleccione el rango de celdas E3: E23 y haga Formato de celdas Número Número Decimales 6. Seleccione la celda A24 e ingrese VARIANCE y seleccione la celda B24 e ingrese la fórmula "= D23" y cópiela y péguela en la celda C24. Seleccione el rango de celda B24: D25 y haga Formato de celdas Número de número Lugares decimales 6.
  10. 10 Seleccione la celda A25 e ingrese STD DEV. (para la Desviación estándar) y seleccione la celda B25 e ingrese sin comillas la fórmula "= STDEVPA (B3: B22)" y cópiela y péguelo en la celda C25. Formatee las celdas Número de número Decimal Places 7 para el rango de celdas seleccionado B25: C25.
  11. 11 Seleccione la celda A27 e ingrese PRONOSTICO y seleccione la celda B27 e ingrese sin comillas la fórmula "= PREVISIÓN (1290, C4: C23, B4)", que es una fórmula de matriz, por lo que debe presionar CONTROL + MAYÚS + INTRO para ingresar correctamente la fórmula de matriz con corchetes. Esa es la proyección del GPA de un estudiante dado un SAT de 1290 y el resto de la población o datos de muestra. Puede esperar un GPA de 3.170409192 una vez que formatee el número de número de celdas Número decimal 9 (que se compara más adelante, exactamente). No es que un GPA tan preciso sea necesario, pero el objetivo es probar la fórmula, y eso requiere cierta precisión.
  12. 12Editar Ir al rango de celdas F3: F22 e ingresar la fórmula w / o cotizaciones "= TREND (C3: C22, B3: B22,, TRUE)" que es una fórmula de matriz por lo que debe presionar CONTROL + MAYÚS + INTRO para ingresar correctamente la matriz fórmula con corchetes. Esa es la proyección de los promedios de la población estudiantil dado su desempeño previo.
  13. 13 Editar Ir al rango de celdas G3: G22 y con G3 la celda activa iluminada, ingrese la fórmula sin las comillas "= F3-C3" y Editar Rellenar hacia abajo. Formatee el número de número de celdas personalizado +0.0; -0.0; +0.0.

Parte dos de tres:
Gráficos Explicativos, Diagramas, Fotos

  • (Depende de los datos del tutorial anterior)
  1. 1 Crea el cuadro. Seleccione el rango de celda B3: C23 y vaya a la cinta de opciones (o haga el Asistente de gráficos) y seleccione Gráficos, Todo, desplácese hacia abajo para Dispersar, Dispersión marcada ... Luego, modifique o copie el nuevo gráfico en la hoja de trabajo Gráficos. Haga el título del gráfico de diseño de gráfico - Título encima del gráfico y llénelo con "Regresión de línea - SAT vs. GPA" (sin comillas). Seleccionar líneas de cuadrícula Líneas de cuadrícula verticales Líneas de cuadrícula principales marcadas. Seleccione Título de Eje Horizontal Título de Eje, Título Título debajo de Eje y edite en "Puntaje SAT" (sin comillas). Seleccione Títulos de eje Título de eje vertical, Título horizontal y edite en "G.P.A." (sin comillas). Por convención, la variable independiente x va en el eje horizontal inferior y la variable y dependiente va en el eje vertical izquierdo.
  2. 2 Ubique el marcador de datos para la media en 1143.75,2.755 que indicará "Serie 1, punto 1143.75" (1143.75, 2.8) cuando esté sobre él o haya hecho clic en él. Haga clic en él y en el menú Formato, Marcador de punto de datos, Tamaño automático, Tamaño 9, luego Marcador, Color de relleno, Rojo.
  3. 3 Regrese a la hoja de trabajo de Datos para descubrir la Línea de Regresión de Y '= mX + b, donde m = la pendiente yb = la intersección en y. Al observar los datos del gráfico con valores bajos por debajo de 10 en la izquierda y valores de alrededor de 1000 en la parte inferior, uno esperaría una pendiente decimal muy pequeña y una intersección en y cercana a 0. Los gráficos escalados pueden ser engañosos en cuanto a la pendiente.
  4. 4 Seleccione la celda G1 e ingrese Y. Seleccione la celda H1 e ingrese m y copie H1 y péguelo en I1 mediante el comando c, seleccione I1, y comience v. Seleccione la celda H2 e ingrese Numerator y seleccione la celda I2 e ingrese Denominator. Seleccione el rango de celda H3: H22 y con H3 como la celda resaltada activa, ingrese w / o la fórmula "= (B3-X_BAR) * (C3-Y_BAR)". Editar Rellenar hacia abajo. Seleccione el rango de celda I3: I22 y con I3 como la celda activa resaltada, ingrese w / o la fórmula "= (B3-X_BAR) ^ 2". Editar Rellenar hacia abajo. Seleccione las columnas H e I y Formato de celdas Número de número Decimal Places 1.
  5. 5Seleccione la celda H23 e ingrese la fórmula sin las comillas "= SUMA (H3: H22)" y formatee el contorno negrita negrita de las celdas y cópielo a I23.
  6. 6 Seleccione la celda H24 e ingrese m y haga Formato de color de fuente de celda rojo. Cópielo en la celda H25 e ingrese b en la celda H25. Seleccione la celda I24 e ingrese w / o comillas la fórmula "= H23 / I23", que es la pendiente m, y luego seleccione la celda I25 e ingrese la fórmula sin las comillas "= Y_BAR-I24 * X_BAR", es decir, la intercepto y b = Y_Mean-m * X_Mean.
  7. 7 Copie la celda H25 y péguela en la celda I26 y entre en I26 Y '- mX + b. Seleccione la celda H27 e ingrese Previsión, luego ingrese la fórmula en I27 sin las comillas "= I24 * 1290 + I25". Su respuesta debe ser exactamente igual a la respuesta PRONOSTICO en B27 una vez que tenga Formato Número de Número de Celdas Número Decimales 9.
  8. 8 Edite la celda de copia I26 en el rango de celda H29: I29. En la celda H29 ingrese la Línea X y en la celda I29 ingrese la Línea Y. Ingrese 800 en la celda H30 y 1600 en la celda H31. Seleccione la celda I 27 y copie su fórmula en la barra de fórmulas - no copie la celda y péguela - eso no funcionará bien. Seleccione la celda I30 y pegue en la barra de fórmulas de edición la fórmula que acaba de copiar. Haga lo mismo con la celda I31. Edite la fórmula de la celda I31 para que lea "= I24 * H31 + I25" y presione enter y luego edite la fórmula de la celda I30 en la barra de fórmulas para leer "= I24 * H30 + I25" y presione enter. El resultado de I30 debe ser 1.7786108729206 y el resultado de I31 debe ser 4.05093465957812 (que me doy cuenta es superior a un GPA de 4.0, pero estamos creando una línea de regresión, por lo que no importa tanto).
  9. 9 Active la hoja de trabajo Chart y haga clic en el gráfico y en el menú, haga Chart Add Data y, en respuesta a la consulta de rango, regrese a la hoja de cálculo de datos y seleccione el rango de celda H30: I31. Ahora el mío sale mal y tengo que editar la serie. No es un gran trato. Seleccione el marcador de datos y en la barra de fórmulas, edite la serie para leer "= SERIE (, Hoja1! $ H $ 30: $ H $ 31, Hoja1! $ I $ 30: $ I $ 31,2)" y haga clic en el marcador de datos en 800,1.78 y haga que Línea roja y Peso 0.75 pt, luego Marker Fill Color Red, luego Marker Style Round Dot size 5. Sus datos y gráfico se verán así:
  10. 10 Ahora para las modificaciones, primero ingrese los nuevos datos emparejados SAT-GPA del estudiante:
    • Ingrese los datos para responder la pregunta. En parejas x, y como se muestra en la imagen en la parte superior de este artículo, o como sigue: para el estudiante 1, X, Y en las columnas B y C = 990 para X o SAT en B3, 2.2 para Y o GPA EN C3; estudiante 2 1150,3.2; entonces 1080,2.6; 1100,3.3; 1280,3.8; 990,2.2; 1110,3.2; 920, 2.0; 1000,2.2; 1200,3.6; 1000, 2.2; 1200, 3.6; 1000, 2.1; 1150, 2.8; 1070, 2.2; 1120, 2.1; 1250, 2.4; 1550,3.9; 1480,3.8; 1010, 2.0.
  11. 11 Seleccione el rango de celdas A24: C24 e Insertar celdas - Shift Cells Down. En la celda A24, ingrese Punto medio. En la celda B24, ingrese la fórmula sin citar "= xCenter" y seleccione la celda C24 e ingrese sin citar la fórmula "= yCenter". Seleccione la celda H32 e Insertar nombre Definir nombre xCenter en la celda $ H $ 32, y seleccione I32 e Insertar nombre definir Nombre yCentro en la celda $ I $ 32. En H32 ingrese la fórmula sin las comillas "= (H30 + H31) / 2" y en I32 ingrese la fórmula sin las comillas "= (I30 + I31) / 2".
  12. 12 Realice la búsqueda de objetivos en el rango de valores de la línea XY. En la celda J30, ingrese la fórmula "= 2-I30" y haga Herramientas Buscar objetivos Establezca la celda J30, en el valor 0, cambiando el valor en la celda: H30. H30 debería convertirse en aprox. 920.690991; vaya a la celda J31 ingrese la fórmula, "= 4-I30" y haga Herramientas Buscar objetivos Establezca la celda J311, en el valor 0, cambiando el valor en la celda: H31. H31 debería convertirse en aprox. 1212.61866
  13. 13 La siguiente información no se usa aún. Tiene que ver con el hecho de que cuando se agrega una línea a un círculo, el resultado es una elipse. Seleccione la celda I33 e ingrese el radio y alinee el centro y el subrayado. Seleccione la celda H34 e ingrese Dist de línea a, y seleccione la celda I34 e ingrese la fórmula sin comillas "= SQRT ((H31-H32) ^ 2 + (I31-I32) ^ 2)" - ese es el segmento largo o línea a. Debería = aprox. 291.9293847
  14. 14 En la fila 1, ingrese los nombres de variable definida de Ellipse. K1: Aj_X; L1: Aj_Y; M1: m_Slope_; N1: Stretch_x; O1: Stretch_y; P1: interceptar
  15. 15 Ingrese los siguientes valores en la fila 2: K2: 1200; L2: 4.15; M2: "= m"; N2: 0.0024950665406049 (logrado mediante búsqueda de objetivos); O2: "= m-0,0005"; P2: "= b". Insertar nombre Definir nombre m para celda I24 e Insertar nombre Definir nombre para b para celda I25.
  16. 16Seleccione el rango de celdas K1: P2 e Inserte nombres Crear nombres en la fila superior, OK.
  17. 17 Editar Ir al rango de celdas K4: K54 y en la celda superior, ingresar la fórmula "= -2 * PI ()" y luego presionar shift + tab para ir a la celda inferior e ingresar "= 2 * PI ()". Con el rango de celda K4: K54 seleccionado, haga Editar serie de relleno, columnas lineales, acepte el valor de paso propuesto o pulse Tendencia si el valor del paso propuesto es erróneamente = 1, OK.
  18. 18Omita una columna y edite Ir al rango de celdas M4: M54 y con M4 la celda activa, ingrese la fórmula sin las comillas "= SIN (K4)" y Editar Rellenar hacia abajo.
  19. 19Editar Ir al rango de celdas N4: N54 y con N4 la celda activa, ingrese la fórmula sin citar "= m_Slope_ * SIN ((K4-1))" y Editar Rellenar hacia abajo.
  20. 20Editar Ir al rango de celdas O4: O54 y con O4 la celda activa, ingrese la fórmula sin las comillas "= (M4) / Stretch_x + Aj_X" y Editar Rellenar hacia abajo.
  21. 21Editar Ir al rango de celdas P4: P54 y con P4 la celda activa, ingrese la fórmula sin citar "= (N4) / Stretch_y + Aj_Y + Intercepción" y Editar Rellenar hacia abajo.
  22. 22 Haga una pequeña tabla de hoja de trabajo. Seleccione el rango de celdas O4: P54 y use Chart Wizard o Ribbon do Charts All / Other y desplácese hacia abajo para Scatter Smoothed Line Scatter y aparecerá un pequeño gráfico sobre sus datos. Importante: Los valores de x deben oscilar entre 800 y 1600 y los valores de y deben oscilar entre 1.8 y 4.1; si este no es el caso, busque un punto decimal fuera de lugar en las filas superiores 2 de variables definidas, o una diferencia entre los cálculos de my b. De lo contrario, se trata de un error de ingreso de datos en algún momento a lo largo de la línea, para ser revisado una columna a la vez. Consulte también la sección de Advertencias a continuación sobre Errores.
  23. 23 Haga clic en el gráfico de la serie del nuevo pequeño gráfico en la Hoja de datos y Comando + c cópielo, luego vaya a la hoja de trabajo del gráfico y haga clic en el gráfico y Comando + v péguelo. Si funciona como el mío, es FUBAR y debe corregirse una serie a la vez.
  24. 24 Si la línea de regresión aún se muestra, edite su serie en la barra de fórmulas a la derecha del primer paréntesis ingresando con comillas "Línea de regresión" antes de la coma. A continuación, presione la flecha hacia abajo en su teclado para acceder a la Serie 1 y realice la edición de gráficos Line (Sin línea), Marker Style red size 5, Marker Fill - Red y edite en la serie en la barra de fórmulas pasando el primer paréntesis izquierdo con comillas , "Pares de SAT-GPA" antes de la primera coma.
  25. 25 Pulse Aceptar y luego la tecla Intro y la Tecla Abajo una o dos veces para acceder a la Serie 3, que es la Elipse. Haga selección de formato de diseño de gráfico, relleno de marcador azul-verde, línea de marcador automático con línea suavizada marcada, OK.
  26. 26 Active la hoja de cálculo de datos y seleccione el rango de celda B23: C24 y copie y active la hoja de trabajo de gráfico y Comando + v pegue el rango. No funciona bien para mí y debo editarlo y eliminar una serie adicional, además, lo que quiero terminar es series que se leen en la barra de fórmulas, "= SERIES (" Mean and Midpoint ", Data! $ B $ 23: $ B $ 24, datos! $ C $ 23: $ C $ 24,4) ", No Marker Line, Marker Style Round Dot, Tamaño 9, Marker Fill Dark Blue o Purple.
  27. 27Ir a Insertar Imagen de Arte de Palabras para crear un Título para el Gráfico que tiene cierto brillo.
  28. Elipse para ajustar datos de regresión 28
    ¡Hecho!    ! Buen trabajo. En un artículo futuro, cómo analizar los diversos sectores de la elipse puede tomarse. Por ahora, se ha logrado rodear el conjunto de datos, que era el objetivo. Para aquellos que están ansiosos por comenzar con este análisis, una pista: cuando calcule el área de un sector de elipse inclinado: Mire, imagine que está en el plano donde no está inclinado y es solo una elipse normal. Ese es el enfoque recomendado de mi mentor. Sugerencia n. ° 2: observe sus datos para encontrar la longitud de la línea b. En Consejos se incluyen los datos y el pequeño cuadro para la elipse inclinada no ajustada que puede usar como punto de partida en sus análisis.

Parte tres de tres:
Orientación útil

  1. 1 Haga uso de artículos auxiliares cuando proceda a través de este tutorial:
    • Para obtener una lista de artículos relacionados con Excel, arte geométrico y / o trigonométrico, gráficos / diagramas y formulación algebraica, vea el artículo Cómo crear una ruta de partículas espirales o una forma de collar o borde esférico.
    • Para obtener gráficos y gráficos más artísticos, también puede hacer clic en Categoría: Imágenes de Microsoft Excel, Categoría: Matemáticas, Categoría: Hojas de cálculo o Categoría: Gráficos para ver muchas hojas de cálculo y tablas de Excel donde Trigonometría, Geometría y Cálculo se han convertido en Arte. o simplemente haga clic en la categoría que aparece en la parte superior derecha blanca de esta página, o en la parte inferior izquierda de la página.