ESTADÍSTICA EN EXCEL

ESCUELA SUPERIOR DE ADMINISTRACIÓN PÚBLICA ESAP

TERRITORIAL CAUCA

ESTUDIANTES DE III. SEMESTRE CETAP  CALDONO

Profesor: Fabián Orozco Méndez
Tutor.

Estadística aplicada

Práctica 1: INTRODUCCIÓN AL USO DE SOFTWARE ESTADÍSTICO

OBJETIVO: Los estudiantes deberán conocer el funcionamiento de la Hoja de Cálculo EXCEL y utilizarla para realizar estudios estadísticos, tanto de una como de dos variables, y de probabilidad, como los siguientes:

1. Obtener representaciones gráficas según el tipo de variable

2. Hacer los cálculos necesarios para obtener los distintos estadísticos (media, mediana, moda, cuantiles, varianzas, etc.). A partir de unos datos (o de una distribución de frecuencias) hacemos los cálculos necesarios para desarrollar una fórmula y paso a paso calcular hasta obtener el resultado final. (Sumas, productos, divisiones, potencias, raíces, etc.)

3. Obtener fácilmente estadísticos como promedios, varianzas, cuartiles, covarianzas, regresión, etc., aplicando una lista de funciones previamente programadas en la hoja de cálculo. (Insertar + funciones o el símbolo f_x de los botones de las barras de herramientas). (Ver listado de funciones y argumentos que requieren)

4. Utilizar en el menú de Herramientas la opción Analizar Datos para analizar un conjunto de información. Permite describir un conjunto de información correspondiente a una o varias variables. Los datos de entrada son con frecuencia unitaria (si un valor se repite varias veces aparece repetido ese número de veces). También permite aplicar técnicas estadísticas más sofisticadas (regresión, contrastes de hipótesis, estimación por intervalos, etc.).

INTRODUCCIÓN EN LA HOJA DE CÁLCULO  EXCEL

El programa Excel  es un programa muy difundido y conocido ampliamente. Dado que se trata de un programa del paquete Microsoft Office, su utilización es muy similar a cualquier otro del mismo, y quien no maneja Microsoft Word hoy en día. Un clic de ratón para seleccionar, un doble clic para accionar, el uso de teclado (flechas para desplazarse, enter para accionar, etc.) o la utilización de la Ayuda del programa son recursos que  guardan similitud con cualquier otro software del Entorno de Windows.

La hoja de cálculo, a pesar de su manejo sencillo, constituye una herramienta  muy potente de cálculo estadístico. Como su nombre indica, la hoja de cálculo nos permite calcular, hacer operaciones matemáticas, lógicas, estadísticas, financieras, etc.

                                  

Excel es una hoja de calculo que facilita los cálculos y la obtención de indicadores y estadísticos para estudiar el comportamiento de una o varias variables. Una hoja de cálculo se podría entender como una matriz de filas y columnas, donde  cada combinación de fila y columna se denomina celda,  por ejemplo la celda A1, B5, C15, etc.

Un conjunto de celdas se denomina rango, y en Excel se expresa como A1:C5. En cada celda se puede contener: número, fórmula, texto, fecha...

Con la hoja de cálculo EXCEL  se pueden obtener fácilmente estadísticos y medidas que resuman y caractericen una variable, una distribución de frecuencias, o una distribución bidimensional (n-dimensional) de frecuencias, es decir varias variables conjuntamente.

El fichero de Excel es el Libro (análogo a lo que es el documento en Word) y su extensión habitual será .xls (de igual forma que es .doc para Word). Cada libro puede contener un número ilimitado de hojas de cálculo.

Un primer recorrido por la hoja de cálculo nos permite conocer las:

- opciones de menú

- barra de herramientas

- barra de estado

 

Manejo de Excel

Introducir datos

Para introducir un dato numérico o textual en cada celda, se hace clic en la celda y se escribe.

Introducir fórmulas

Algunas de las operaciones matemáticas que puede hacer son:

Suma = ..+..

División =.. /..

Elevar al cuadrado =..^2

Elevar a potencia n =..^n

Raíz =raiz(..)

Calcular valor absoluto =abs(..)

Calcular raíz n-esima=... ^(1/n)

Calcular logaritmos =LN(..)

Etc.

Para introducir una fórmula en una celda, se hace clic en ella, se escribe el signo de igualdad (=) y, a continuación, la fórmula.

La mecánica es similar al procedimiento que utilizamos para resolver problemas de estadística utilizando una calculadora. Es importante  disponer de manera clara y ordenada los cálculos. Esto facilita su posterior uso e incluso la revisión de los procedimientos si se detectan errores.

Como ya se ha indicado, una celda puede contener un número o una fórmula.

Para crear una fórmula empezamos tecleando en una celda, por ejemplo la C1, el símbolo “=”, y la operación que deseamos realizar. Puede ser un simple cálculo matemático: =3+5, y nos da un resultado 8. Pero puede ser una fórmula =A1+B1. Si en la celda A1 hay un 3 y en la B1 un 5, aparecerá el mismo resultado 8, sin embargo ya no es un número, sino una fórmula. Esto tiene dos implicaciones importantes:

1.- Si cambian los valores de las celdas A1 y B1, cambia el resultado en C1 obteniendo la suma de los nuevos números.

2.- Cuando copiamos la celda C1 a otra celda, no copiamos el valor, sino la fórmula.

En algunas ocasiones nos interesa que  la referencia de una celda no se modifique cuando se copie la fórmula que la contiene. Podemos indicarle en la fórmula que mantenga fijo la fila, la columna o ambas. Para ello deberemos anteponer el símbolo $ antes de la letra (columna), o antes del número (fila) o antes de las dos (fila y columna, es decir celda) de la identificación de la celda.

Este tipo de referencias que se mantienen constantes al copiar la fórmula se denominan referencias absolutas a celdas o celdas absolutas. Ejemplo : $C$26 es una referencia absoluta, mientras  C26 es una referencia relativa.

Sumar un rango de valores con la auto suma (=suma(..), ó símbolo Σ)

Con ello se puede calcular: F_i, f_i, N_i, N, x_in_i, S_xy, r_xy, etc que son estadísticos que utilizaremos en los siguientes temas.

Seleccionar:

a)     Una celda: se hace clic en ella.

b)    Una fila: se hace clic en el número de cabecera.

c)     Varias filas seguidas : se hace clic en la primera y se arrastra hasta la última (también se puede hacer al revés).

d)    Una columna: se hace clic en la letra de la cabecera.

e)     Varias columnas: se hace clic en la primera y se arrastra hasta la última (también se puede hacer al revés).

f)     Un rango: se hace clic en la celda  de uno de los vértices y se arrastra hasta la celda      de la esquina opuesta.

Insertar hojas:

Para insertar una hoja nueva, se elige en la barra de menús Insertar /Hoja de cálculo

Mover hojas

Para mover una hoja, se selecciona en la parte inferior, en la barra de hojas del libro, y se arrastra al lugar correspondiente.

ACTIVIDAD 1: Se ha preguntado a 40 Estudiantes de III. Semestre de APT sobre el número de libros leídos en el último mes y se han obtenido los siguientes resultados: 

  1, 1, 2, 1, 1, 2, 1, 4, 2, 2, 1, 2, 1, 1, 0, 3, 1, 2, 1, 1

  2, 3, 2, 3, 0, 1, 1, 1, 2, 4, 1, 2, 2, 3, 3, 0, 2, 3, 4, 4

1.     Abre la Hoja de Cálculo Excel.

2.     Introduce todos los valores en el rango A4:H8
3.   Sume las Columnas y Filas
4. Sume los pares y divídalo entre la suma de los impares
5.  Reste la suma de los impares a la suma de los pares

CÁLCULOS EN EXCEL APLICANDO FUNCIONES.

Para poder aplicar funciones f_x las frecuencias han de ser unitarias.

En el menú insertar, seleccionar la opción función, después la categoría de Estadísticas y dentro de ellas la que interese.

Una vez introducidos los datos, utilizaremos las funciones que Excel nos proporciona. Por ejemplo, la función =SUMA(Rango) o =PROMEDIO(Rango) calculan  respectivamente la suma y el promedio de una muestra.

La función promedio, permite calcular la media aritmética. Aparece en la parte inferior del cuadro de diálogo una breve descripción de la función, y permite solicitar más información sobre ella en Ayuda sobre esta función.

En este caso los argumentos de la función, lo que habría que rellenar en el cuadro de diálogo es el conjunto de celdas en los que se encuentras los valores de la variable.

En Número1 habrá que indicarle el rango de celdas, por ejemplo A4:H8, o pinchando en el recuadro que hay a la derecha, marcar con el ratón dichas celdas.

Usando las funciones podremos calcular: máximo y mínimo valor, recuento de frecuencias, promedios, cuantiles, mediana, moda, media armónica, geométrica, media recortada, varianza y desviación típica, medidas de forma, etc. También podemos calcular algunos estadísticos bidimensionales: covarianza, correlación, estimación lineal, estimación logarítmica, tendencia, pronóstico, etc. que estudiaremos en los siguientes temas de la asignatura.

FUNCIONES ESTADÍSTICAS Y SU CORRESPONDENCIA CON LOS ESTADÍSTICOS QUE SE EXPLICARÁN EN TEORÍA.

COEF.DE.CORREL (r).

Dar rango de valores de las variables X e Y.

COEFICIENTE.ASIMETRIA (g₁).

Rango de valores de la variable.

COEFICIENTE.R2 (r²)

Dar rango de valores de las variables X e Y.

CONTAR

Rango de valores de la variable. Cuenta el número de valores.

CONTAR.SI

Especificar rango de valores de la variable y criterio. Por ejemplo número de veces que una variable se repite el 3. El criterio puede ser un número o una celda de la hoja de cálculo.

COVAR (S_xy)

Dar rango de valores de las variables X e Y.

CUARTIL (Q₁,Q₂,Q₃)

Hay que indicar el Rango de valores de la variable, y el número del cuartil que se busca: 1,2,3.

CURTOSIS (g₂)

Rango de valores de la variable.

DESVESTP (S_x)

Rango de valores de la variable.

DESVIA2

(Media de desviaciones respecto a la media al cuadrado) Rango de valores de la variable.

DESVPROM

(Media de valores absolutos de desviaciones respecto a la media al cuadrado). Rango de valores de la variable.

s teclas Control+Shift+Intro.

MAX

(Valor máximo) Rango de valores de la variable.

MEDIA.ACOTADA

(Media de valores centrales) El argumento Matriz, es el rango de valores de la variable. El argumento porcentaje es el % de valores que se excluyen. Si se pone 0,1 calcula la media aritmética del 90% de valores centrales. (quitando el 5% más altos y el 5% más bajos).

MEDIA.ARMO

(Media armónica) Rango de valores de la variable.

MEDIA.GEOM

(Media geométrica) Rango de valores de la variable.

MEDIANA

Rango de valores de la variable.

MIN

(Mínimo valor) Rango de valores de la variable.

MODA

Rango de valores de la variable.

NORMALIZACION

(Calcula valores tipificados). Habrá que darle como argumentos el valor de la variable y su correspondiente media y desviación típica.

PERCENTIL

Los argumentos son: Matriz, que es el rango de valores de la variable, y K que es el percentil que se busca. Si se desea el percentil 10, K valdrá 0,1. Si se desea el percentil 90, K vale 0,9.

PROMEDIO

(Media aritmética) Rango de valores de la variable.

PRONOSTICO

(Predicción a partir de la recta de regresión). Suponiendo que se conoce el valor de X, qué valor tomará Y según la recta de regresión. Los argumentos son: X que es el valor que toma la variable independiente, Conocido_X, que es el rango de valores de la variable X, y Conocido_Y, que es el rango de valores de la variable Y.

VARP

(Varianza) Rango de valores de la variable.

ACTIVIDAD 2: Con los datos de la Actividad 1, Calcule las Funciones Estadísticas anteriormente descritas en este documento.

"Obra de tal forma, que tu obrar, se convierta en norma universal"/FAOM.

ACTIVIDAD ESTUDIANTES DE III. SEMESTRE DE APT CETAP CALDONO

ESCUELA SUPERIOR DE ADMINISTRACIÓN PÚBLICA ESAP

ACTIVIDAD ESTUDIANTES DE III. SEMESTRE DE APT

CETAP CALDONO TERRITORIAL CAUCA

Profesor: Fabián Orozco Méndez                                                           Caldono, 16 de Marzo de 2019
Tutor.


Los Gráficos estadísticos

Existe una gran cantidad de gráficos para la representación de datos estadísticos, entre los principales tenemos:

a) Gráfico de Barras 

El gráfico de barras, como su nombre lo indica, está constituido por barras rectangulares de igual ancho, conservando la misma distancia de separación entre sí. Se utiliza básicamente para mostrar y comparar frecuencias de variables cuantitativas o comportamientos  en el tiempo, cuando el número de ítems es reducido.

Para elaborarlo debemos:

- Utilizar un sistema de coordenadas rectangulares y se llevan al eje de las "x" los valores que toma la variable en estudio y en el eje de las "y" se colocan las frecuencias de cada barra.

- Luego se construyen los rectángulos, tomando como base al eje de las abscisas, cuya altura será igual a cada una de las diferentes frecuencias que presentan las variables en estudio.

- La magnitud con que viene expresada la variable se observa en la longitud de las barras (rectángulos). Es importante destacar que solamente la longitud de las barras y no su anchura es lo que denota la diferencia de magnitud entre los valores de la variable. 

Todas las barras tienen que tener una anchura igual, separadas entre sí, preferiblemente por una longitud igual a la mitad del ancho de estas o distancias iguales entre barras.

Las barras se pueden graficar tanto verticalmente como horizontalmente. Se pueden elaborar barras compuestas y barras agrupadas.

Este tipo de gráfico se clasifican por:

- Barras simples: Compara valores entre categorías de una variable

- Barras dobles: Compara valores entre categorías de dos variables

- Barras múltiples: Compara valores entre categorías de dos o más variables.

- Barras verticales: Las categorías de la variable deben ubicarse en el eje x.

- Barras horizontales: Las categorías de la variable deben ubicarse en el eje y.

- Barras Aplicadas: Compara entre categorías el aporte de cada valor en el total.

b) Gráfico de sectores Circulares:

Usualmente llamado gráfico de torta, debido a su forma característica de una circunferencia dividida en sectores, por medio de radios que dan la sensación de un pastel cortado en porciones.

Se usa para representar variables cualitativas en porcentajes o cifras absolutas cuando el número de ítems no es superior a 5 y se quiere resaltar uno de ellos.

c) Gráfico de líneas o Tendencia:

Usado básicamente para mostrar el comportamiento de una variable cuantitativa a través del tiempo. El gráfico de líneas consiste en segmentos rectilíneos unidos entre sí, los cuales resaltan las variaciones de la variable por unidad de tiempo.

Cuando se tienen varias variables a representar, con el fin de establecer comparaciones entre ellas (siempre que su unidad de medida sea la misma); se utiliza plasmarlos en un solo gráfico, el cual es el resultado de representar varias variables en un mismo plano. A este tipo de gráfico se le llama gráfico de líneas compuesto.

Criterios para elaborar un gráfico de líneas:

1- La utilización de la escala que se utilizará en el plano cartesiano puede variar tomando en cuenta el fenómeno que se va a graficar. No es necesario que las abscisas (ejes x) y las ordenadas (eje y) del plano cartesiano lleven la misma escala; sin embargo, cuando las magnitudes de las variables no se diferencian sustancialmente es recomendable utilizar escalas iguales para obtener un gráfico con mayor precisión.

2- Cuando una de las variables en estudio se inicia con valores muy altos es recomendable no comenzar el eje por el origen cartesiano sino por un valor próximo o por el mismo valor por donde comienza la variable.

3- Es costumbre representar en el eje de las x del plano cartesiano la variable independiente del estudio que se realiza y en el eje de las y la variable dependiente.
En aquellos casos que se dificulta distinguir el tipo de variable se recomienda colocar en la ordenada del plano cartesiano las frecuencias de las variables en estudio y sobre la abscisa la variable cronológica (años, semanas, días, horas, etc.)

d) Histograma de frecuencias:

El histograma es un diagrama en forma de columna, muy parecido a los gráficos de barras. Se define como un conjunto de rectángulos paralelos, en el que la base representa la clase de la distribución y su altura la magnitud que alcanza la frecuencia de la clase correspondiente. Son barras rectangulares levantadas sobre el eje de las abscisas del plano cartesiano utilizando escalas adecuadas para los valores que asume la variable en la distribución de frecuencia.

El ancho de la base de los rectángulos es proporcional a cada clase de la distribución, de tal manera que, cuando la distribución tiene clases de igual tamaño, el tamaño de todos los rectángulos tendrá bases iguales. 

Los lados del rectángulo se levantan sobre los puntos del eje de las x que corresponden a los límites de cada clase y la longitud de los mismos será igual a la frecuencia que tenga esa clase, los lados por lo tanto corresponden a la frecuencia de cada clase de la distribución de frecuencia.

Cuando se elaboran gráficas estadísticas en el plano cartesiano es recomendable que en el eje de las ordenadas se representen las frecuencias y el  eje de abscisas las variables independientes.

e) Polígono de frecuencias:

Se utiliza básicamente para mostrar la distribución de frecuencias de variables cuantitativas. Para construir el polígono de frecuencia se toma la marca de clase que coincide con el punto medio de cada rectángulo de un histograma.

Pasos para elaborar un polígono de frecuencias:

1- Se dibuja un plano cartesiano.

2- Se traza sobre el eje de las abscisas, a distancias iguales, los puntos medios de las diferentes clases de la distribución de frecuencias.

3- Se levantan perpendiculares por cada una de las marcas de clase, con una longitud igual a la frecuencia de cada una de las clases que integran la distribución de frecuencia. Al final de cada perpendicular se marca un punto.

4- Los puntos resultantes se unen por medio de una línea recta obteniéndose una línea poligonal.

5- Con la finalidad de cerrar la línea poligonal se agrega una clase imaginaria con frecuencia cero a cada extremo de la distribución de frecuencia, por tal motivos ambos extremos del polígono se cortan con el eje de las abscisas.

También se puede elaborar un polígono de frecuencia después de haber graficado un histograma; si se determina el punto medio de cada rectángulo de un histograma y esos puntos medios se unen por medio de segmentos de recta dan como resultado el polígono de frecuencia.

f) Histograma de frecuencias acumuladas:

Se utiliza básicamente para mostrar la distribución  de frecuencias acumulada de variables cuantitativas. Es una gráfica que se elabora con los valores de las frecuencias acumuladas (menor que y mayor que) y los límites de las clases de una distribución de frecuencia. El polígono de frecuencia acumulada se le conoce comúnmente como ojiva.

La ojiva es una representación gráfica que consiste en una línea, que puede ser ascendente o descendente y se utiliza para representar las distribuciones de frecuencias acumuladas menor que y mayor que, según los datos utilizados. En los estudios de análisis estadísticos la ojiva es de gran utilidad porque permite obtener con gran aproximación cierta información requerida, en un momento determinado.

ACTIVIDAD EN GRUPO

Las calificaciones de 50 Estudiantes de Tercer Semestre de APT en Economía son:

5, 2, 4, 5, 3, 4, 5, 2, 5, 3, 3, 5, 5, 2, 1, 5, 2, 5, 4, 5, 3, 3, 4, 0, 4, 4, 4, 2, 2, 3, 3, 4, 3, 2, 4, 6, 7, 3, 5, 2, 5, 3, 1, 4, 2, 3, 5, 5, 3, 4.

Con ellas, construir:

1. La tabla de distribución de frecuencias.

2. Ordenar en Intervalos de Clase

3. Hallar Marca de Clase, Media, Mediana, Moda.

4. Consultar 6 tipos de gráfico adicionales a los del Ensayo.

5. Realizar un Gráfico de Barras que compare todas las notas con sus frecuencias absolutas

"La actitud es esa pequeña cosa que hace la gran diferencia".FAOM.

ACTIVIDAD PARA ESTUDIANTES DE III. SEMESTRE DE APT CETAP CALDONO

ESCUELA SUPERIOR DE ADMINISTRACIÓN PÚBLICA ESAP TERRITORIAL CAUCA

ACTIVIDAD ESTADÍSTICA I.

PARA ESTUDIANTES DE III. SEMESTRE DE APT CETAP CALDONO

Popayán, Marzo 9 de 20|19
Esp. Fabián Orozco Méndez
Tutor.

Trabajo en grupos de 4 estudiantes para ir desarrollándolo en la Sesión:

Los resultados de un test de aptitud tomado a un grupo de 100 Estudiantes de la ESAP se volcaron en la siguiente tabla:

Intervalos        f
20,5 – 25,5      28
15,5 – 20,5      32
10,5 – 15,5      21
5,5 – 10,5        12
0,5 – 5,5           7

I. 1. Determine la lista de datos de menor a mayor
2. Calcule las frecuencias:
Absoluta, relativa, Acumulada y Acumulada relativa
3. Defina El número de Intervalos, con la fórmula de Sturgess.
4. Calcule el Rango
5. Defina la Amplitud
6. Realice la sumatoria de las Marcas de Clase (X).

II. Exprese las siguientes operaciones en Notación Sumatoria y Notación Productoria respectivamente:

  

"El saber y la razón hablan, la ignorancia y el error gritan".faom.