Matemáticas con excel

Gráficas de funciones continuas  1	Gráfica de dos funciones 2	Gráficas de funciones discontinuas 4
Distribuciones estadísticas a	Gráfica de funciones trigonométricas 3	Correlación y regresión b

---

Gráficas de funciones continuas

En Excel el área de trabajo está denominado por filas (cada una tiene un número) y columnas (identificadas con letras) de modo parecido a un crucigrama. 

1. En la celda A1 escribe "x"
2. En la celda B1 pincha el menú Insertar- Objeto - Microsoft editor de ecuaciones. Aquí tienes el mismo editor de ecuaciones de Word. Escribe "y = 2x²+3x -5". Puedes ajustar el ancho de celda para que te quepa pinchando en la línea divisoria de las celdas B y C en la parte superior  no apta para escribir (parte sombreada) cuando te sale una cruz puedes cambiar el ancho a voluntad.
3. En la celda A2 escribe -6 e intro. Vuelve a la celda A2 y seleccionas el menú Edición - Rellenar - Series - Columna - Incremento =1 y en límite pon 4. (Esto tienes que saberlo tú para que te salga el vértice). Aceptar. Verás que te aparecen todos los números enteros desde -4 hasta 4. Si no te aparece sitúate en una celda y botón derecho - Formato de celdas y mira si tienes el formato general.
4. En la celda B2 escribe: =2*A2^2+3*A2-5 Intro. Verás que nada más escribir el = en la barra de fórmulas (está en la barra de herramientas) se escribe simultáneamente allí también. *= producto y ^= potencia.
5. Verás que te ha aparecido en B2 el valor correspondiente de la función. Sitúate en la parte inferior derecha de la celda B2 hasta que te aparezca una cruz y arrastra hasta B10 que es  donde le corresponde al último valor de la "x"
6. Pincha en el menú Insertar- Gráfico (o si tienes el botón de Insertar gráficos en la barra de herramientas ) y se te abre el asistente para crear gráficos.
7. En Tipo de Gráfico : elige DISPERSIÓN y eliges uno de los subtipos cuya muestra aparece. y Aceptar.
8. En Rango de datos: Pincha en su casilla y selecciona con el ratón desde las celdas A2B2 hasta A12B12 y botón Siguiente.
9. Título del gráfico: escribe Parábola .
10. En Eje de valores X puedes escribir el nombre de la magnitud que representa la X, y lo mismo en Y.
11. Pincha en las demás fichas : líneas de división, rótulos y datos y Leyenda y experimenta con las distintas opciones que te ofrecen
12. Finalizar y Aceptar. Ya tendrás un gráfico.
13. Vamos a mejorar un poco la apariencia: Pincha sobre el eje X de la figura (y cuando esté seleccionado) Botón derecho del ratón. Te aparece la ventana de Formato de Ejes, elige en grosor uno un poco más grueso para destacarlo de las demás líneas de división.
14.  En Fuente elige el tamaño de 8 y en la ficha Escala  si pones en unidad mayor 1 el gráfico te aparecerá con las divisiones de 1 unidad.
15. Haz lo mismo con el eje Y.
16. Pincha sobre la línea de la curva (verás que se te selecciona) Botón derecho del ratón y elige Formato de Serie de Datos : elige un color rojo para la curva y el mayor grosor.
17. Pincha sobre el fondo gris (por defecto) del gráfico (tienes que tener seleccionado el rectángulo gris) , Botón derecho del ratón y aparece la ventana de Formato de Área de Trazado y cambia el color de fondo ( por ejemplo amarillo claro)
18. Pincha sobre la Leyenda (si no la has suprimido pondrá Serie 1) y suprime.
19. Pincha sobre el título de la gráfica y botón derecho aparece Formato de título de gráfico, en la ficha Tramas  activa la casilla de sombreado y elige un color. También puedes hacer otros cambios.
20. Pinchando en las esquinas del gráfico puedes hacerlo mayor como si fuera una ventana de Windows. Tendrás una gráfica como ésta:

ARRIBA

---

Gráfica de dos funciones

Vamos a hacer la gráfica de dos funciones juntas, por ejemplo de la parábola que ya tenemos y de la recta y=3x+5. El rango de datos ha de ser el mismo para las dos funciones:

1. Escribimos en C1 : y= 3x+5 (aprovechamos los datos de la función anterior).

2. En C2 escribimos:  =3*A2

3. Pinchamos en la parte inferior derecha de la celda C2 y arrastramos hasta C12 (aparecen los valores de la nueva función). 

4. Pincha en cualquier espacio vacío y repite el proceso anterior para Insertar gráfico.

5. En rango de datos, pincha allí y luego selecciona con el ratón el rango A2B2C2 hasta A12B12C12.

6. Después pincha en   la ficha Serie. Estando seleccionada la serie1 rellena en la casilla de nombre de serie con: Parábola. Selecciona la serie2 y ponle de nombre y=3x+5.

7. Los pasos siguientes hasta finalizar el gráfico es igual que en la gráfica anterior.

8. En la parte inferior izquierda estará activa la Hoja1, puedes cambiar su nombre. Pincha encima de Hoja1 y botón derecho del ratón, elige Cambiar nombre y escribe parábola y recta. La gráfica que ha de salir sería como ésta:

Se puede dibujar la gráfica de cualquier función continua, lo que  debes ajustar tú es el dominio para que la curva sea representativa.

ARRIBA

---

 Gráfica de funciones trigonométricas

Para dibujar funciones trigonométricas el dominio ha de estar en radianes. Vamos a dibujar la función seno en el dominio [-180º,360º]:

1. Selecciona Hoja2 y cámbiale el nombre : Seno. El proceso se te ha explicado antes.

2. En  A1 escribimos "x".

3. En B1 escribimos : Radianes y en C1 escribimos :y=senx

4. En A2 escribe -180. Rellena con una serie hacia abajo e incremento 45 y el límite 360.

5. Ahora vamos a pasar estos grados a radianes. Pincha en B2, pinchar en menú Insertar - Función (Si tienes en la barra de herramientas el botón f_x atajas).

6. En la categoría de función elige : Matemáticas y trigonométricas. Se despliegan estas funciones y eliges Radian.

7. Si la ficha de función te molesta puedes arrastrarla para que te deje ver, también puedes escribir directamente en la barra de fórmulas. Selecciona la celda A2 e Intro , Se te habrán convertido en radianes.

8. Arrastra como ya te hemos explicado anteriormente hasta el final en la columna B.

9. En C2  Insertar- Función . Categoría Matemáticas y trigonométricas y eliges la función Seno. Seleccionas la celda B2 e Intro. 

10. Arrastra hasta el final de la columna C y obtienes todos los valores. 

11. Observa que en 0 y en 360 no tienes cero, pero si un número muy próximo a él (está en notación científica), puedes poner directamente tú cero. Como ves tienes que saber matemáticas para las utilidades del ordenador.

12. Ahora repites el proceso explicado anteriormente para insertar el gráfico, poniendo el rango de datos B2C2 hasta B14C14.

Tendrás una gráfica como ésta:

ARRIBA

---

Gráfica de funciones discontinuas

Para las gráficas de funciones discontinuas tienes que saber su dominio, con el procedimiento que hemos visto anteriormente la gráfica nos sale continua , pero con un pequeño "truco" podemos esconder el tramo de curva que corresponde a la discontinuidad.

1. En A1 escribe "x" y en B1 escribe

2. En A2 escribe -10 y rellena en columna con incremento 1 hasta -3. Llegará hasta la celda A9.

3. En A10 escribe -2,5 y en A11 escribe -1,5

4. En A12 escribe -1 y rellena una serie en columna hasta 10. Alcanzarás la celda A23.

5. En B2 escribe : =(A2-1)/(A2+2). Has de poner paréntesis porque Excel trabaja con la misma jerarquía de las calculadoras y el operador divide es /. Te saldrá el resultado. Arrastra hasta B23.

6. Pincha en una parte vacía y como siempre Insertar gráfico- Dispersión.

7. En rango de datos  pincha y selecciona el rango A2B2 hasta A23B23.

8. No pongas título ni leyenda, cambia el formato de ejes como siempre y Finaliza el gráfico.

9. Selecciona el fondo de gráfico y cámbialo a color amarillo claro.

10. Selecciona la curva y cambia el grosor de la línea y el color (por ejemplo rojo) Aceptar.

11. Selecciona la línea de la curva y estando seleccionada pinchar otra vez sobre el tramo de curva que nos sobra. Verás que ha quedado seleccionado este tramo, botón derecho del ratón y  Formato de punto de datos elige el mismo color amarillo claro que has puesto de fondo y cambia el grosor de la línea al más fino.

12. Selecciona la fórmula de la función que tienes en B1 , botón derecho del ratón y Copiar pincha sobre la parte superior de la gráfica y botón derecho del ratón y pegar.

La gráfica que tendrás será como la siguiente:

 

ARRIBA

---

Distribuciones estadísticas

Vamos a  resolver el siguiente problema estadístico en Excel:

En un grupo de ESO, se han obtenido los siguientes resultados en el área de matemáticas: 10,6,3,4,5,5,4,7,7,8,9,3,4,6,8,6,8,9. Determinar

•  La tabla de frecuencias.

• Medidas de centralización (Media, mediana y moda)

• Diagrama de barras

• Medidas de dispersión (R= recorrido o rango, D_x= desviación media , V= varianza y D_t=desviación típica)

Sigue los siguientes pasos:

1. Escribe en la celda A1 : Calificaciones . En A2 escribe la primera nota, es decir 10 e Intro. Coloca en la columna A todas las calificaciones. Viendo que llega a la columna 19 verás que el número de alumnos (calificaciones es 18).

2. Selecciona desde A2 hasta A19 y en el menú Datos - Ordenar - Opciones elige el orden ascendente (Botón de barra de herramientas flecha hacia abajo). Verás que se te han ordenado las calificaciones, con lo que te será más fácil hacer la tabla de datos.

3. En B1 escribe :X. En B2 escribe la nota más baja (3), en B4 la siguiente calificación (4) y así hasta el 10 que estará en la celda B10.

4. En la columna C escribiremos las frecuencias absolutas. En C1 escribe f y en C2 la frecuencia del 3 que es 2 y así con las demás. 

5. En C10 escribe: =Suma(C2:C9) (Si tienes un sigma en la barra de herramientas basta con dar a ese botón) y te aparece 18 que es el número de individuos de la muestra.

6. Para calcular la frecuencia acumulada en la columna D: en D1 escribe F y en D2 escribe la fórmula =C2, y en D3 escribe =C3+D2  e Intro. Ponte en la parte inferior derecha de D3 y arrastra hasta D9.Si lo has hecho bien en D9 has de tener 18.

7. Para calcular las frecuencias relativas en la columna E: escribe en E1  h( nos representará la frecuencia relativa). En E2 escribe: C2/$c$10 e intro. El símbolo $ antes de una fila o columna hace que al arrastrar la fórmula la fila y la columna sea siempre la misma. Para asegurar que lo has hecho bien en E10 haz la suma y el resultado ha de ser 1.

8. En la columna F vamos a calcular la frecuencia acumulada relativa. En F1 escribe H  (letra que representará la frecuencia acumulada relativa y en F2 escribe la fórmula: =D2/$C$10. Arrastra hacia abajo hasta F9 que ha de ser 1.

9. Para calcular los porcentajes en la columna G escribe en G1: p. En   G2 escribe la fórmula : =E2*100 e Intro. Arrastra la fórmula hasta G9. En G10 haz la suma y será 100.

10. En H1 escribe Media.

11. En H2: Insertar-Función. Elige en categoría de funciones Estadísticas y elige la función Promedio. Debes seleccionar las celdas desde A2:A19 (si te molesta la ventana de función arrástrala). Verás que te sale de media 6,2. Los rangos de las funciones estadísticas no son nunca los agrupados (los que tienes en columna B)

12. En I1 escribe Mediana. En I2: Insertar- función  y vuelve a elegir  la categoría Estadísticas. Elige la función Mediana y vuelve a seleccionar el rango A2:A19. Te saldrá el valor 6.

13. En J1 escribe Moda. En J2 vuelve a repetir el proceso anterior de insertar la función estadística Moda y seleccionando el mismo rango A2:A19 obtendrás el valor 4. Pero puedes observar que esta distribución es multimodal (hay más de una moda). La función estadística Moda sólo da la primera que encuentra. Por eso cuándo hay más de una moda no usamos funciones estadísticas, sino que la ponemos nosotros. Suprime el contenido de J2 y escribe: 4,6,8 .

14. Calculemos la desviación media en la columna k. En K1 escribe : D_x (Con el editor de ecuaciones que ya hemos usado en las funciones continuas) . En K2 Inserta la función estadística Desprom cuyo rango es A2:A19. El resultado será 1,64.

15. En la columna L pondremos la varianza. En L1 escribe : V . En L2 Inserta la función estadística Varp con rango A2:A19. Saldrá el valor 3,76.

16. En la columna M calcularemos la desviación típica. En M1 escribe: D_t (Insertar -Objeto- Microsoft editor de ecuaciones). En M2 Inserta la función estadística DESVESTP con rango A2:A19. El resultado será 2,096

17. Vamos a calcular el recorrido. En N1 escribe: R y en N2 escribe la siguiente fórmula: =Max(B2:B9)-Min(B2:B9) y te saldrá 7. Aquí podemos poner el rango de la columna B porque el recorrido solo depende de los valores que toma la variable y no de su frecuencia.

18. Gráfica:

    1. Insertar Gráfico. Elegir el tipo de Columnas y un subtipo. Siguiente

    2. Pincha en rango de datos  y selecciona el rengo C2:C9 (valores de las frecuencias absolutas). 

    3. En la ficha Serie pincha en rótulos del eje de categorías X y selecciona el rango B2:B9 ( valores de la variable). Siguiente.

    4.  En Título escribe: Calificaciones de Matemáticas. En eje de categoría X escribe : Calificaciones. En eje de valores Y escribe: Nº de alumnos. Siguiente y Finalizar.

    5. Puedes cambiar los colores de las barras y el fondo. Por ejemplo las barras correspondientes a las calificaciones de 3 y de 4 pueden ser de color verde. Para ello: Selecciona la gráfica y vuelve a pinchar en la columna del 3, verás que sólo está seleccionada ella y botón derecho del ratón y cambia el color. Repite el procedimiento para la columna del 4.

    6. Puedes cambiar el aspecto de cualquier elemento del gráfico seleccionándolo y botón derecho del ratón.

La gráfica sería así: 

ARRIBA

---

Regresión y Correlación

Vamos a trabajar con las distribuciones bidimensionales, es decir vamos a trabajar con dos variables estadísticas X e Y y vamos a ver si están relacionadas. Vamos a calcular la recta de regresión, la nube de puntos y también el coeficiente de correlación. Vamos a hacerlo con un ejemplo:

En   un grupo de ESO, formado por 18 alumnos, se han obtenido los siguientes resultados numéricos en una prueba escrita realizada por los Departamentos de Matemáticas, Física y Química. Las calificaciones han sido:

Construcción de la tabla de frecuencias Bidimensional y de las medidas de Centralización:

1. Escribe en la celda A4 : Matemáticas y en B4 Fca-Qca

2. En A5 escribe: X y en B5 escribe: Y. (la variable x=matemáticas y la y=Fca-Qca)

3. En la columna A a partir de A6 escribe  las calificaciones de matemáticas. A partir de B6 escribe las calificaciones de Física y Química.

4. Seleccionando desde A5B6 hasta A23B23 y ordena los datos. (como en distribuciones estadísticas)

5. La tabla de frecuencias se hace como en las distribuciones pero ahora habrá una frecuencia para X y otra para Y. Vamos a pasar de hacerla porque es repetir el apartado de distribuciones estadísticas.

6. En D1 escribe: Media(x) y en E2 escribe Media(y) .

7. En D2 Inserta función estadística Promedio Y con rango de datos A6:A23. En E2 haces lo mismo pero para el rango B6:B23. Verás que las dos tienen el mismo valor.

8. En F1 escribe: Covarianza. En F2: Inserta función estadística COVAR en matriz 1  selecciona el rango A6:A23 y en matriz2 selecciona el rango B6:B23 . (Te saldrá 2,9).

9. En G1 escribe: Coeficiente de Correlación. En G2 Inserta la función estadística COEF.DE.CORREL. En matriz 1 rellena con el rango A6:A23 y en matriz2 rellena con el rango B6:B23. Te saldrá el valor de 0,84 . (Si alguna de estas funciones no te sale, pincha en la celda y botón derecho y pon formato número)

10. Según el valor del coeficiente de correlación  (r) podemos valorar la relación mutua entre las variables X e Y. Una tabla resumen puede ser: 

    r=1 (ó 0,99<r<=1)	Dependencia funcional directa	Exacta
    0< r< 1	Dependencia aleatoria directa	Muy fuerte r de 0,9 a 0,99 Fuerte r de 0,7 a 0,9 Moderada r de 0,4 a 0,7 Débil r de 0,2 a 0,4 Mut Débil r de 0 a 0,2
    r=0	X,Y aleatoriamente independientes	Nula
    -1 < r < 0	Dependencia aleatoria inversa entre X, Y	Muy Débil r de -0,2 a 0 Débil r de -0,4 a -0,2 Moderada r de -0,7 a -0,4 Fuerte r de -0,9 a -0,7 Muy Fuerte r de -0,99 a -0,9
    r=-1 (ó -1<=r < -0,99)	Dependencia funcional Inversa	Exacta

11. La recta de regresión posee una expresión y = ax + b que podríamos usar para calcular el valor de la variable dependiente y que "correspondería" a un valor de la x que no estuviera en la tabla de valores. Sólo se pueden hacer estimaciones con garantía cuando la correlación sea fuerte, muy fuerte o exacta. Como nuestro coeficiente de correlación es 0,84 la estimación sería buena.

12. Calculemos la pendiente m en la columna H. En H1 escribe: m. En H2 Inserta la función estadística pendiente, en conocidoy  pon el rango B2:B23 y en conocidox pon el rango A6:A23. Te saldrá 0,6587. 

13. Para presentar los puntos del diagrama de dispersión (nube de puntos) y la recta de regresión que más se aproxima a ellos. Seleccionamos en el menú Insertar gráfico  (puedes emplear el icono de la barra de herramientas) y eliges el modelo de dispersión y el subtipo de puntos (el primero) y en rango de datos escribe el rango desde A6B6 hasta A23B23(basta con que pinches en rango de datos y selecciones con el ratón los valores de las dos variables). Siguiente.

14. En el eje X escribe Matemáticas y en el eje Y escribes Física y Química. Quita la leyenda. Siguiente y finalizar

15. Para presentar la recta de regresión, selecciona los puntos del gráfico (puede que alguno no se seleccione). Haz clic derecho con el ratón y elige AGREGAR LÍNEA DE TENDENCIA. En la ficha Tipo elige la opción de Lineal. En la ficha Opciones señala la casilla de Presentar ecuación en gráfico. El gráfico sería así:

 

1. Puedes estimar cuál será la nota de Fca-Qca de un alumno que ha tenido un 5,75 en matemáticas. Para ello en la celda J1 escribe :Valor de X para estimar Y y en J2 introduces el número 5,75.

2. En I1 escribe la fórmula de la recta de regresión. En I2 programas para que te salga el valor de y para 5,75 de la siguiente manera: 0,6587*J2+2,1236. Intro. Te saldrá la nota estimada para Física-Química.

3. También podemos estimar la nota de matemáticas para una determinada calificación en Física.. En la celda k1 escribe: Estimar x y en k2 introduce un 8 que es la nota de Física. (ya se que debe aparecer en esta celda la nota de matemáticas pero pronto se cambiará).

4. Arrastra la fórmula de I2 hasta I3, pero cambia J2 por k2 (te aparece la fórmula =0,6587*k2+2,1236).

5. En I3 menú Herramientas - Buscar objetivo. En definir celda escribe: I3. En Con el Valor : k2 . Por último en Para Cambiar Celda: k2. Aceptar . En k2 te aparece la nota de matemáticas 8,9. Creo que es más sencillo despejar en la fórmula de la recta de regresión.

ARRIBA