Ejemplo con un alumno inventado: tu actividad se guarda solo en este navegador. Salir del ejemplo

Matemáticas 3.º ESO · Gráficos estadísticos con hoja de cálculo

Tablas, parámetros y gráficos con hoja de cálculo

Objetivo: Al terminar, sabrás organizar datos en una hoja de cálculo, construir tablas de frecuencias con fórmulas y usar las funciones PROMEDIO, MEDIANA, MODA, MAX, MIN, DESVEST.P y CUARTIL para calcular parámetros estadísticos.

Explicación

Organizar los datos

En una hoja de cálculo (Excel, LibreOffice Calc, Hojas de cálculo de Google):

Una columna por variable y una fila por individuo. En la fila 1, los títulos.
Cada casilla es una celda (B2 = columna B, fila 2). Un rango es un bloque de celdas: B2:B14 son las celdas de B2 a B14.
Las fórmulas empiezan por =. En las hojas en español, los argumentos se separan con punto y coma (;), porque la coma es la coma decimal.

Funciones estadísticas

Función	Qué calcula
`=CONTAR(B2:B14)`	número de datos ( $N$ )
`=SUMA(B2:B14)`	suma de los datos
`=PROMEDIO(B2:B14)`	media aritmética
`=MEDIANA(B2:B14)`	mediana (el valor central de los datos ordenados)
`=MODA(B2:B14)`	moda (el valor más repetido; también `MODA.UNO`)
`=MAX(...)`, `=MIN(...)`	dato mayor y dato menor; `=MAX(...)-MIN(...)` es el recorrido
`=DESVEST.P(B2:B14)`	desviación típica: mide cuánto se separan los datos de la media (cuanto mayor, más dispersos)
`=CUARTIL(B2:B14;1)`	primer cuartil: deja por debajo, más o menos, el 25 % de los datos (con `;3`, el tercer cuartil, el 75 %)

Tabla de frecuencias automática

Con los datos en A2:A26 y los valores distintos escritos en D2, D3…:

Primero fija el rango de datos para que no se mueva al arrastrar las fórmulas: selecciónalo y escribe un nombre (por ejemplo, tallas) en el cuadro de nombres, a la izquierda de la barra de fórmulas. (Otra forma: escribir el rango en la fórmula y pulsar F4, que lo convierte en referencia absoluta.)
$f_i$ : en E2, =CONTAR.SI(tallas;D2) y se arrastra hacia abajo.
$h_i$ : =E2/25, con formato de porcentaje si se quiere.
$F_i$ : en G2, =E2; en G3, =G2+E3, y se arrastra.
Para intervalos se usa =CONTAR.SI.CONJUNTO(alturas;">=150";alturas;"<160") (con el rango llamado alturas): cuenta los datos de $[150, 160)$ .

Después, Insertar → Gráfico para representar la tabla.

La hoja calcula, pero tú decides qué parámetro tiene sentido y cómo se interpreta; y conviene comprobar algún resultado a mano para detectar fórmulas mal escritas.

Puntos clave

Una columna por variable, una fila por individuo y títulos en la fila 1.
PROMEDIO, MEDIANA, MODA, MAX, MIN, DESVEST.P y CUARTIL calculan los parámetros de un rango.
CONTAR.SI, con el rango fijado (con un nombre o con F4), construye la tabla de frecuencias.
En español, los argumentos se separan con punto y coma.
Comprueba siempre algún resultado a mano.

Errores frecuentes

ErrorEscribir =PROMEDIO(B2;B14) en lugar de =PROMEDIO(B2:B14).
Cómo corregirloCon ; solo se usan las dos celdas B2 y B14; con : se usa todo el rango de B2 a B14.
ErrorArrastrar =CONTAR.SI(A2:A26;D2) sin fijar el rango: en la fila siguiente queda A3:A27 y se pierde un dato.
Cómo corregirloPonle un nombre al rango (por ejemplo, datos) y usa =CONTAR.SI(datos;D2), o fíjalo con la tecla F4 (referencia absoluta).
ErrorDejar el rango corto (por ejemplo, B2:B20 cuando los datos llegan a B31).
Cómo corregirloComprueba con =CONTAR(...) que el número de datos es el que esperas.

Ejemplos resueltos

Ejemplo 1. Los puntos del equipo de baloncesto tecnológico (parámetros con fórmulas)

Un equipo de baloncesto juvenil de Alcobendas ha anotado estos puntos en sus 13 partidos y los ha copiado en una hoja de cálculo:

	A	B
1	Partido	Puntos
2	1	68
3	2	75
4	3	82
5	4	71
6	5	90
7	6	75
8	7	64
9	8	79
10	9	86
11	10	75
12	11	70
13	12	93
14	13	77

Escribe las fórmulas para calcular el número de datos, la media, la mediana, la moda, el máximo, el mínimo, el recorrido, la desviación típica y los cuartiles primero y tercero. ¿Qué resultados da la hoja?

Resolución

Los datos están en el rango B2:B14:

Parámetro	Fórmula	Resultado
Número de datos	`=CONTAR(B2:B14)`	13
Media	`=PROMEDIO(B2:B14)`	≈ 77,31
Mediana	`=MEDIANA(B2:B14)`	75
Moda	`=MODA(B2:B14)`	75
Máximo	`=MAX(B2:B14)`	93
Mínimo	`=MIN(B2:B14)`	64
Recorrido	`=MAX(B2:B14)-MIN(B2:B14)`	29
Desviación típica	`=DESVEST.P(B2:B14)`	≈ 8,23
Primer cuartil	`=CUARTIL(B2:B14;1)`	71
Tercer cuartil	`=CUARTIL(B2:B14;3)`	82

Comprobación a mano de alguno: datos ordenados: 64, 68, 70, 71, 75, 75, 75, 77, 79, 82, 86, 90, 93. La mediana es el 7.º dato: 75 ✔. La moda es 75 (aparece 3 veces) ✔. Media: $\dfrac{1005}{13} \approx 77{,}31$ ✔.

Interpretación: el equipo anota de media unos 77 puntos; la mitad de los partidos, 75 o menos. La desviación típica, unos 8,2 puntos, indica cuánto se separan, más o menos, los partidos de la media.

Ejemplo 2. Tallas de calzado con CONTAR.SI tecnológico (tabla de frecuencias)
Las tallas de calzado de 25 alumnos están en A2:A26 de una hoja de cálculo:

38, 40, 37, 39, 41, 38, 40, 42, 39, 38, 40, 36, 39, 41, 40, 38, 37, 39, 40, 42, 39, 38, 41, 40, 39

Construye con fórmulas la tabla de frecuencias absolutas, relativas y acumuladas y di qué gráfico insertarías.

Resolución
1. En D2:D8 escribimos las tallas 36, 37, …, 42.
2. Llamamos tallas al rango A2:A26 (cuadro de nombres). En E2: =CONTAR.SI(tallas;D2) y arrastramos hasta E8.
3. En F2: =E2/25 y arrastramos.
4. En G2: =E2; en G3: =G2+E3 y arrastramos hasta G8.
Resultado:

D E F G

1 Talla $f_i$ $h_i$ $F_i$

2 36 1 0,04 1

3 37 2 0,08 3

4 38 5 0,2 8

5 39 6 0,24 14

6 40 6 0,24 20

7 41 3 0,12 23

8 42 2 0,08 25

9 Total 25 1

Comprobación: =SUMA(E2:E8) da 25 ✔. Gráfico: la talla es una variable discreta con pocos valores → diagrama de columnas (barras) seleccionando D1:E8.

	D	E	F	G
1	Talla	$f_i$	$h_i$	$F_i$
2	36	1	0,04	1
3	37	2	0,08	3
4	38	5	0,2	8
5	39	6	0,24	14
6	40	6	0,24	20
7	41	3	0,12	23
8	42	2	0,08	25
9	Total	25	1

Ejercicios

De menos a más. Intenta cada uno antes de mirar la solución.

1. Fácil
Los tiempos de 30 alumnos en una carrera están en las celdas B2:B31. Escribe las fórmulas que darían: a) el número de datos; b) la media; c) la mediana; d) la moda; e) el mejor tiempo (el menor); f) el recorrido; g) la desviación típica.

Ver solución
a) =CONTAR(B2:B31) b) =PROMEDIO(B2:B31) c) =MEDIANA(B2:B31) d) =MODA(B2:B31) e) =MIN(B2:B31) f) =MAX(B2:B31)-MIN(B2:B31) g) =DESVEST.P(B2:B31)
a) =CONTAR(B2:B31) (debe dar 30). b) =PROMEDIO(B2:B31). c) =MEDIANA(B2:B31). d) =MODA(B2:B31). e) El mejor tiempo es el menor: =MIN(B2:B31). f) =MAX(B2:B31)-MIN(B2:B31). g) =DESVEST.P(B2:B31).
2. Media
Lucas tiene los datos en A2:A26 y los valores de la variable en D2:D6. En E2 ha escrito =CONTAR.SI(A2:A26;D2) y la ha arrastrado hasta E6. Además, en H1 ha escrito =PROMEDIO(A2;A26). a) ¿Qué fórmula queda en E4? ¿Qué problema hay? b) ¿Qué calcula realmente =PROMEDIO(A2;A26)? c) Corrige las dos fórmulas.

Ver solución
a) =CONTAR.SI(A4:A28;D4): el rango se ha movido y no cuenta A2 y A3. b) La media de solo dos celdas, A2 y A26. c) Fijar el rango (con un nombre o con F4), por ejemplo =CONTAR.SI(datos;D2) con datos = A2:A26, y =PROMEDIO(A2:A26).
a) Al arrastrar dos filas, todas las referencias que no están fijadas bajan dos filas: =CONTAR.SI(A4:A28;D4). Ya no cuenta los datos de A2 y A3 (y mira celdas vacías). b) Con ; se pasan dos celdas sueltas: calcula la media de A2 y A26, no de todo el rango. c) Se fija el rango: se le pone un nombre (por ejemplo, datos para A2:A26) y se escribe =CONTAR.SI(datos;D2), o se fija con F4. Y =PROMEDIO(A2:A26).
3. Media
En una hoja de cálculo están las notas de un control de dos grupos pequeños: grupo A: 5, 6, 6, 7, 7; grupo B: 3, 5, 6, 8, 9. La hoja da =DESVEST.P ≈ 0,75 para A y ≈ 2,14 para B. a) Calcula a mano la media de cada grupo. b) ¿Qué grupo tiene las notas más parecidas entre sí? ¿Cómo lo ves en los datos y en la desviación típica?

Ver solución
a) Las dos medias valen 6,2. b) El grupo A: sus notas están entre 5 y 7, y su desviación típica es menor.
a) A: $\dfrac{5+6+6+7+7}{5} = \dfrac{31}{5} = 6{,}2$ . B: $\dfrac{31}{5} = 6{,}2$ . Tienen la misma media. b) En A las notas van de 5 a 7 (recorrido 2); en B, de 3 a 9 (recorrido 6). La desviación típica de A (0,75) es mucho menor que la de B (2,14): las notas de A están más agrupadas alrededor de la media.
4. Difícil
Las alturas (en cm) de 40 alumnos están en A2:A41, y a ese rango le has puesto el nombre alturas. Quieres la tabla de frecuencias con los intervalos $[150, 160)$ , $[160, 170)$ , $[170, 180)$ y $[180, 190)$ , con los extremos inferiores en D2:D5 y los superiores en E2:E5. a) Escribe en F2 una fórmula, que se pueda arrastrar, para contar los datos de cada intervalo. b) ¿Cómo comprobarías que no se ha quedado ningún dato fuera?

Ver solución
a) =CONTAR.SI.CONJUNTO(alturas;">="&D2;alturas;"<"&E2). b) Que =SUMA(F2:F5) dé 40 (igual que =CONTAR(A2:A41)).
a) =CONTAR.SI.CONJUNTO(alturas;">="&D2;alturas;"<"&E2): cuenta los datos mayores o iguales que el extremo inferior y menores que el superior, como en $[a, b)$ . El & une el signo con el número de la celda. Se arrastra hasta F5. b) =SUMA(F2:F5) debe dar 40, lo mismo que =CONTAR(A2:A41). Si da menos, algún dato está fuera de los intervalos (por ejemplo, un alumno de 190 cm o más).

Para practicar más

1. Media
En las celdas C2:C10 de una hoja de cálculo están los minutos que tardan 9 alumnos en llegar al instituto: 14, 9, 18, 12, 21, 15, 12, 17, 10. a) Escribe las fórmulas de la media, la mediana, la moda, el máximo, el mínimo y el recorrido, y calcula a mano lo que debería dar cada una. b) La hoja da =CUARTIL(C2:C10;1) = 12 y =CUARTIL(C2:C10;3) = 17. ¿Qué significan esos números en este contexto?

Ver solución
a) Media ≈ 14,22; mediana 14; moda 12; máximo 21; mínimo 9; recorrido 12. b) Más o menos una cuarta parte tarda 12 min o menos y tres cuartas partes, 17 min o menos.
a) Fórmulas: =PROMEDIO(C2:C10), =MEDIANA(C2:C10), =MODA(C2:C10), =MAX(C2:C10), =MIN(C2:C10), =MAX(C2:C10)-MIN(C2:C10). Ordenados: 9, 10, 12, 12, 14, 15, 17, 18, 21. Media: $\dfrac{128}{9} \approx 14{,}22$ min. Mediana: el 5.º dato, 14 min. Moda: 12 (aparece dos veces). Máximo 21, mínimo 9, recorrido $21 - 9 = 12$ min. b) Aproximadamente el 25 % de los alumnos tarda 12 min o menos, y el 75 %, 17 min o menos.
2. Fácil
Los datos de una encuesta sobre el número de veces que se ha ido al cine en el último mes (valores 0, 1, 2, 3 y 4) están en B2:B31. Los valores están escritos en D2:D6 y al rango B2:B31 le has puesto el nombre cine. Escribe las fórmulas de E2 (frecuencia absoluta), F2 (frecuencia relativa) y G2 y G3 (frecuencia acumulada) que se puedan arrastrar hacia abajo.
Ver solución
E2: =CONTAR.SI(cine;D2). F2: =E2/30. G2: =E2; G3: =G2+E3.
- E2: =CONTAR.SI(cine;D2) (el nombre fija el rango al arrastrar).
- F2: =E2/30 (hay 30 datos).
- G2: =E2; G3: =G2+E3, que al arrastrar suma cada vez la frecuencia de su fila a la acumulada anterior. Comprobación: G6 debe valer 30.
3. Media
Encuentra el error de cada fórmula y corrígela: a) =PROMEDIO(B2;B30) para la media de los datos de B2 a B30. b) =SUMA(B2:B30)/30 para la media de los datos de B2 a B30. c) =MODA(C2:C30), donde la columna C tiene el medio de transporte (Metro, autobús, a pie…). d) =CONTAR.SI(B2:B30,3) en una hoja en español.

Ver solución
a) Debe ser B2:B30. b) Son 29 datos, no 30: mejor =PROMEDIO(B2:B30). c) MODA solo funciona con números; hay que contar con CONTAR.SI cada medio y ver el mayor. d) El separador es ; : =CONTAR.SI(B2:B30;3).
a) Con ; solo promedia B2 y B30. Correcta: =PROMEDIO(B2:B30). b) De B2 a B30 hay 29 celdas, no 30. Correcta: =PROMEDIO(B2:B30) (o dividir entre CONTAR(B2:B30)). c) MODA trabaja con números; con palabras da error. Se hace la tabla con =CONTAR.SI(C2:C30;"Metro"), etc., y la moda es la categoría con mayor frecuencia. d) En español los argumentos se separan con ;: =CONTAR.SI(B2:B30;3).
4. Difícil
Quieres estudiar con una hoja de cálculo cuántos minutos tardan en llegar al instituto los alumnos de tu clase. Describe en 5 o 6 pasos cómo lo harías: cómo organizas los datos, qué fórmulas usas, qué tabla haces y qué gráfico insertas.
Ver solución
Respuesta abierta: datos en una columna con título; CONTAR, PROMEDIO, MEDIANA, MODA, MIN, MAX, DESVEST.P; tabla agrupada con CONTAR.SI.CONJUNTO; histograma o columnas; conclusión.
Ejemplo:
1. En A1 «Alumno» y en B1 «Minutos»; cada alumno en una fila (B2:B26).
2. =CONTAR(B2:B26) para comprobar que están todos.
3. Parámetros: =PROMEDIO(B2:B26), =MEDIANA(B2:B26), =MODA(B2:B26), =MIN(...), =MAX(...), =DESVEST.P(B2:B26).
4. Tabla agrupada (por ejemplo, de 5 en 5 minutos) con =CONTAR.SI.CONJUNTO(minutos;">="&D2;minutos;"<"&E2), después de llamar minutos al rango B2:B26.
5. Seleccionar la tabla → Insertar → Gráfico → columnas pegadas (histograma).
6. Escribir una conclusión: «de media tardamos unos … min; la mitad tarda menos de …».