RESUMEN


Este mini-curso de Excel se centra sobre todo en la estadística y no pretende ser un manual exhaustivo del funcionamiento de Excel ni de todas sus posibles aplicaciones, la pretensión de este manual es dar a conocer los procedimientos básicos y la lógica de funcionamiento de este programa centrándose en el cálculo estadístico. En este manual tomaremos como referencia la versión Excel 2003, en otras versiones, como la 2010, cambia un poco la forma, pero las funciones y funcionamiento son similares.

ARTÍCULO: Curso básico de Excel.


¿Cómo funciona Excel?
 
Excel es una hoja de cálculo, por tanto un programa que permite la realización de múltiples y complejos cálculos matemáticos con gran rapidez. Este programa consta de una serie de menús desplegables similares a los que usan otros programas del paquete Office de Microsoft.
 
Lo primero que hay que decir es que los documentos de Excel tienen la extensión .xls, y que son compatibles con otros programas de análisis estadístico como SPSS.
 
Excel se organiza los datos en función de la posición que ocupan dentro de un sistema referencial formado por columnas, nombradas con letras, y filas, nombradas con números. Así cualquier celda puede ser identificada, por ejemplo como C5, siempre primero la letra y después el número.
 
Imagen 1.
 
Referencia de una celda en Excel
 
 
La lógica que sigue Excel es muy sencilla, o bien opera con los valores en cuanto tal, o bien opera con el contenido de las celdas, lo que es mas ventajoso para nuestros propósitos. Así podemos escribir en cualquier celda =3+2, pulsamos Intro y obtenemos 5. Pero lo mas interesante es operar con celdas, de esta forma en la celda A1 introducimos el valor 3, en la A2 introducimos el valor 2. Y en la celda B4, por ejemplo, introducimos =A1+A2 y seguidamente pulsamos Intro. El resultado que aparece en B4 es 5, pero eso no es todo, cuando cambiamos el valor de las celdas A1 y A2, también cambia el valor de B4. En la versión de Excel 2000 no era necesario poner el signo igual (=) delate de la formula, pero en la versión 2003 - 2010 si es necesario.
 
Esta es una sencilla manera de explicar como funciona Excel, pero con un ejemplo se entenderá mejor. Imaginemos que queremos hacer una calculadora que nos de el importe del IVA de un producto con solo introducir su precio.
 
En la imagen 2 podemos ver como tenemos el precio de un articulo en la celda A2, en la B2 tenemos el porcentaje de IVA. Con esos valores insertamos la formula que puede verse en la barra de formulas y obtenemos el importe del IVA. La formula lo que hace es multiplicar el valor de la celda A2 por el valor de la celda B2 e indicar que es un porcentaje con el símbolo de porcentaje, así de sencillo. Así si varia el IVA, variamos el valor de la celda B2, y se aplica directamente a la formula sin hacer mas cambios.
 
Imagen 2.
 
 
En la siguiente imagen podemos ver como ahora tenemos marcada la celda F2, y vemos en la barra de formulas =A2+D2, eso significa que se suman los valores que contienen esas celdas.
 
Imagen 3.
 
Por tanto, y resumiendo, si cambiamos únicamente el valor de la celda A2, veremos como cambian los demás valores. Así el caculo del IVA se hace mucho más cómodo con la hoja de cálculo que con la calculadora. La gran ventaja que tiene Excel sobre una calculadora es que siempre podemos revisar que los datos con los que operamos son los correctos, podemos modificar los datos, además de que podemos crear cualquier formula que necesitemos.
 
Si se ha entendido esto, se ha comprendido la lógica del funcionamiento de Excel, por tanto a partir de ahora todo será sencillo. Pero si no has comprendido esto, no pases de esta línea hasta que lo entiendas, no te servirá de nada.
 
Visto así, esto resulta interesante, pero también muy limitado, seria mas interesante poder calcular el IVA de mas productos, y después sumar todos los importes. La primera solución para esto seria crear tantas filas como importes de IVA quisiésemos calcular, es decir, introducir las formulas tantas veces como filas necesitemos. Si necesitásemos 10 filas, no seria gran problema esto, si necesitásemos 50 filas, el trabajo ya seria tedioso, pero si necesitásemos calcular el IVA de 3000 productos, pues estaríamos ante un serio problema.
 
Para estos casos Excel tiene una característica que permite arrastrar las formulas, de tal forma que introducida una vez la formula, solo queda arrastrar el ratón para que las formulas se creen en las sucesivas celdas, pero veamos un ejemplo.
 
Imagen 4.
 
Cuando tenemos una celda marcada, en este caso la D2, vemos que hay un punto en el vértice inferior derecho, pues bien, cuando colocamos el puntero del ratón sobre ese punto, vemos como la forma del puntero del ratón cambia, en ese momento pulsamos el botón izquierdo del ratón y manteniéndolo pulsado arrastramos hasta la celda D7, al soltarlo vemos que se ha extendido la formula de la celda D2 a todas las celdas que quedan bajo ella, hasta la D7. Si nos fijamos en las celdas con las que operan las formulas vemos que son las que corresponden con su fila, por tanto, ya sabemos como calcular el IVA de 3000 productos en unos segundos.
 
Imagen 5.
 
Si ahora quisiésemos saber la suma de todos los importes de IVA podríamos sumar las celdas una a una así: =D2+D3+D4+D5+D6+d7 y pulsando intro. Pero como podemos ver esto sirve si son pocos los valores que tenemos que sumar, si tuviésemos que sumar el importe de los 3000 artículos antes mencionados tendríamos un serio problema. La solución a esto son las funciones que contiene Excel que te permiten realizar de manera sencilla muchos cálculos. Así y para nuestro caso usaríamos la función SUMA. La expresión seria =SUMA(D2:D7), y suma todas los valores entre las celdas D2 y la D7, ambos incluidos.
 
En la versión 2003 la funciones se insertan desde el menú Insertar à Función, en la versión 2010 Formulas à Insertar función.
 
En la siguiente tabla, en la celda B9, podemos ver como la función suma nos da el resultado de la suma de una columna.
 
Imagen 6.
 
Como se puede imaginar, la función suma no es la única que tiene Excel, en la versión 2003, si abres el menú Insertar à Función verá una relación de todas las funciones que incorpora Excel, en la versión 2010 Formulas à Insertar función. Están divididas en categorías, de todas ellas las que mas interés van a tener para nosotros van a ser las estadísticas, las matemáticas y las lógicas. Así por ejemplo encontraremos la función PROMEDIO que nos calcula la media aritmética, veamos un ejemplo:
 
Imagen 7.
 
Hemos arrastrado la formula que nos calcula el precio con IVA, y hemos calculado la media aritmética de todos los precios con IVA, podemos ver la formula =PROMEDIO(F2:F7) en la barra de formulas.
 
Como podemos ver, la hoja de cálculo es muy superior a la calculadora cuando hay que hacer muchas operaciones, y en estadística, el número de operaciones a realizar es muy elevado, hasta que resulta casi imposible cuando tenemos tamaños muestrales grandes.
 
Un ejemplo típico de cálculo que resulta muy laborioso realizar con una calculadora es el cálculo de puntuaciones típicas, que como sabemos consiste en restar de la puntuación directa la media, y seguidamente dividir el resultado por la desviación típica. Veamos como hacer esto.
 
Imagen 8.
 
Para este ejemplo hemos tomado las calificaciones hipotéticas de 20 alumnos, la muestra tipificada puede ver en la columna C. Lo que hemos hecho en primer lugar es calcular la media y la desviación típica. Las expresión que calcula la media es como hemos dicho antes =PROMEDIO(A2:A21), y la que calcula de desviación típica es =DESVESTP(A2:A21). Como puede verse la forma de las expresiones son siempre la misma, Primero el signo igual, luego el nombre de la función y seguidamente, y entre paréntesis, el rango de celdas sobre el que se hace el cálculo.
 
Con lo que sabemos hasta ahora se nos ocurriría para calcular la primera puntuación típica la formula =(A2-B23)/B24, y seria correcta, pero solo nos calcularía correctamente la primera puntuación típica, por que a medida que baja de la celda A2 a la A3 y sucesivas, también bajaría la posición de la media y la desviación típica, por tanto debemos utilizar un recurso mas, las llamadas referencias absolutas.
 
En la imagen puede verse la formula de la desviación típica con referencias absolutas. En la formula =(A2-$B$23)/$B$24 lo que llama la atención es el símbolo dólar, lo que hace el símbolo dólar es fijar las filas, columnas o celdas con las que se opera. Si el símbolo dólar precede a la letra de la columna, fija la columna. Si el símbolo dólar precede al número de la fila, fija la fila con la que vamos a operar. Y si tenemos un símbolo dólar antes de la letra de la columna y otro antes del número de la fila, entonces tenemos fijada una celda. De esta forma podemos arrastrar la formula y siempre operaremos con los valores de media y desviación típica que tenemos en las celdas B23 y B24.
 
Otro problema común que suele presentarse en como operar con diferentes rangos de celdas. Un ejemplo típico seria el siguiente:
 
Imagen 9.
 
Podemos ver en la celda C14 como hemos sumado los rangos A1 hasta el A10 con el rango C1 a C10, puede ver la formula en la barra de formulas. La particularidad que tiene la formula es la separación de los diferentes rangos con punto y coma, así tenemos =SUMA(A1:A10;C1:C10), por supuesto, se pueden sumar tantos rangos como se desee. La suma de rangos contiguos es mas sencilla, así para sumar los rangos A1:a10 con el B1:B10 bastaría con la expresión =SUMA(A1:B10)
 
Como puede verse los cálculos no son complicados en absoluto, pero como también puede observarse que son muy tediosos lo que hace muy fácil cometer errores, y la omisión de decimales arrastrará errores en los cálculos. Es por esto, por lo que en la práctica, los cálculos estadísticos no se hacen, ni se deben hacer manualmente o mediante calculadoras, ya sean las típicas de oficina o las científicas o programables, se usaran estas últimas solo cuando no tengamos a mano un ordenador.
 
Con esto, se puede decir que ya se conoce la esencia de este extraordinario programa de cálculo, vamos a terminar con una relación de las funciones más usuales en estadística y con un ejemplo de cálculo paso a paso de la correlación de Pearson.
 
A continuación vamos a hacer una relación de las formulas y funciones mas comunes que se pueden usar en estadística.
 
Algunas formulas.
 
=A1+A2 – Suma los valores contenidos en las celdas A1 y A2.
 
=A1-A2 – Resta los valores contenidos en las celdas A1 y A2.
 
=A1*A2 – Multiplica los valores contenidos en las celdas A1 y A2.
 
=A1/A2 – Divide los valores contenidos en las celdas A1 y A2.
 
=A1^A2 – Calcula la potencia de base A1 y exponente B2
 
=A1^(1/A2) – Calcula la raíz de A1 e índice A2. La raíz cuadrada le corresponde el índice 2, a la raíz cúbica le corresponde el índice 3, a la raíz quinta le corresponde el índice 5, etc.
 
=A1*A2% - Calcula el porcentaje A2 de A1.
 
=(A1+A2)/A3 – Sumamos A1 con A2, y el resultado lo dividimos entre A3.
 
Algunas funciones matemáticas y trigonométricas de Excel.
 
=ABS(a1) – Nos devuelve el valor absoluto del número contenido en la celda a1.
 
=ALEATORIO.ENTRE(A1;A2) – Nos devuelve un valor aleatorio comprendido entre dos números.
 
=EXP(1) – Nos devuelve el valor de e elevado a 1, es decir, el valor de e, que es 2,718281828.
 
=FACT(A1) – Nos devuelve el factorial del número contenido en la celda A1. El factorial de 6 es 6*5*4*3*2*1 = 720
 
=PI() – Nos devuelve el valor de pi, es decir, 3,141592654.
 
=POTENCIA(2;3) – Nos devuelve el resultado de una potencia es decir, de elevar 23 = 8
 
=PRODUCTO(B1:B8) – Multiplica los números comprendidos entre las celdas B1 y B8.
 
=RAIZ(B1) – Devuelve la raíz cuadrada del valor contenido en la celda B1.
 
=REDONDEAR(numero;decimales) – Nos devuelve el número con un determinado número de decimales. Ejemplo: REDONDEAR(3.25185;2), devolverá 3,25.
 
=SIGNO(B1) – Devuelve el signo del número contenido en la celda B1. Devuelve 1 si es positivo, 0 si el número es cero, y -1 si el número es negativo.
 
=SUMA(A1:A8) – Devuelve la suma de los valores contenidos en el rango A1 hasta el A8.
 
=SUMA.CUADRADOS(B1:B8) – Eleva al cuadrado todos los números contenidos entre las celdas B1 y B8, y devuelve la suma de estos cuadrados.
 
=SUMAX2MASY2(A1:A5;B1:B5) – Eleva al cuadrado los rangos A1:A5 y B1:B5 y devuelve su suma.
 
Funciones estadísticas de Excel.
 
=COEFICIENTE.ASIMETRIA(A1:A50) – Devuelve la asimetría del conjunto de datos comprendido en el rango A1:A50.
 
=COEF.DE.CORREL(A1:A30;B1:B30) – Devuelve el coeficiente de correlación de Pearson de dos muestras posicionadas en los rangos A1:A30 y B1:b30.
 
=CONTAR(A1:A5) – Cuenta el número de valores numéricos comprendidos en un rango.
 
=CONTAR.BLANCO(A1:A5) – Cuenta el número de celdas que están en bacías en el rango A1:A5.
 
=CONTAR.SI(A1:A5;3) – Cuenta el número de celdas que contiene el valor 3 en el rango A1:A5.
 
=COVAR(A1:A5;B1:B5) – Devuelve la covarianza de dos rangos de datos.
 
=CUARTIL(A1:A5;2) – Devuelve el cuartil 2 (percentil 50) del rango A1:A5. El cuartil 1 equivale al percentil 25, y el cuartil 3 equivale al percentil 75.
 
=CURTOSIS(A1:A5) – Devuelve la curtosis del conjunto de datos situado en el rango A1:A5.
 
=DESVEST(A1:A5) – Devuelve la desviación típica de la muestra comprendida en el rango A1:A5.
 
=VAR(A1:A4) – Calcula la varianza de la muestra de un rango de datos.
 
=DESVESTP(A1:A5) – Devuelve la desviación típica de la población comprendida en el rango A1:A5.
 
=VARP(A1:A4) - Devuelve la varianza de la población comprendida en el rango A1:A4.
 
=DISTR.NORM.ESTAND(D3) – Devuelve la probabilidad en la distribución normal de obtener un valor menor o igual que el contenido en la celda D3.
 
=DISTR.NORM.ESTAND.INV(D3) – Nos da la puntuación de la distribución típica normalizada (valor crítico) correspondiente a una determinada probabilidad (celda D3).
 
=DISTR.T(estadistico;grados de libertad;colas) – Nos devuelve la probabilidad de obtener un estadístico t de Student mayor o igual que el hallado, también conocido como p-valor o valor p de Fisher.
 
=DISTR.T.INV(alpha;grados de libertad) – Devuelve el valor critico correspondiente a una distribución t de Student para un determinado alpha y grados de libertad.
 
=DISTR.CHI(estadístico;grados de libertad) - Nos devuelve la probabilidad de obtener un estadístico Ji (o Chi) de Pearson mayor o igual que el hallado, también conocido como p-valor o valor p de Fisher.
 
=PRUEBA.CHI.INV(alpha;grados de libertad) - Devuelve el valor critico correspondiente a una distribución Ji de Pearson para un determinado alpha y grados de libertad.
 
=DISTR.F(estadístico;g.l. numerador;g.l. denominador) - Nos devuelve la probabilidad de obtener un estadístico F de Snedecor mayor o igual que el hallado, también conocido como p-valor o valor p de Fisher.
 
=DISTR.F.INV(alpha; g.l. numerador;g.l. denominador) - Devuelve el valor critico correspondiente a una distribución F de Snedecor para un determinado alpha y grados de libertad.
 
=PENDIENTE(B1:B4;A1:A4) – Calcula la pendiente de una ecuación de regresión lineal simple donde el rango B1:B4 es la variable dependiente y A1:A4 es la variable independiente.
 
=INTERSECCION.EJE(B1:B4;A1:A4) – Calcula la ordenada de de una ecuación de regresión lineal simple donde el rango B1:B4 es la variable dependiente y A1:A4 es la variable independiente.
 
=INTERVALO.CONFIANZA(alpha;desviación típica;tamaño muestral) – Calcula el error máximo en torno a la media de una muestra distribuida normalmente. El limite inferior del intervalo es la media menos el error máximo, el limite superior es la media mas el error máximo.
 
=PROMEDIO(A1:A4) – Calcula la media aritmética de un rango de datos.
 
=MEDIA.ARMO(A1:A4) – Calcula la media armónica de un rango de datos.
 
=MEDIA.GEOM(A1:A4) – Calcula la media geométrica de un rango de datos.
 
=MEDIANA(A1:A4) – Calcula la mediana de un conjunto de datos.
 
=NORMALIZACION(X;media;desviación típica) – Calcula la puntuación típica correspondiente a la puntuación X con una determinada media y desviación típica.
 
=PERCENTIL(A1:A4;percentil) – Calcula el percentil del rango de datos A1:A4. Para el percentil, 25 (25/100 = 0,25) introducimos 0,25.
 
Bloqueo de celdas.
 
Uno de los aspectos más interesantes de Excel es que nos permite crear hojas de cálculo para cubrir nuestras propias demandas, de tal forma que nos vasta con introducir los datos, pulsar Intro y ya tenemos el resultado buscado. No obstante, si hacemos esto sin bloquear algunas celdas, nos puede ocurrir que eliminemos alguna formula de manera no intencionada.
 
Para conseguir nuestro objetivo de tener unas celdas modificables y otras no, seleccionamos la celda/s o rango de celdas que no interese mantener activa, es decir, aquellas por las que la hoja va a recibir los datos y por tanto es preciso poder escribir en ellas. Para ello pulsamos el botón derecho del ratón, seleccionamos Formato de las celdas y seleccionamos la pestaña Proteger, desmarcamos la opción Bloqueada y para finalizar pulsamos en Aceptar. Eso hará que cuando se proteja el documento, puedas escribir en las celdas de entrada de datos, y no puedas modificar accidentalmente las formulas. Para proteger la hoja de calculo nos vamos a Herramientas à Proteger y seleccionamos la opción proteger hoja o proteger libro (el libro se refiere a todas las hojas del archivo).
 
Imagen 10.
Descargar hoja de cálculo protegida
 
La imagen 10 presenta una sencilla hoja de cálculo para tipificar una muestra de resultados. Los datos se introducen en las celdas coloreadas en verde A1:A10, se calcula la media y desviación típica, y en la columna C aparecen las puntuaciones tipificadas. Si se intenta escribir en cualquier celda que no sean las coloreadas, el sistema te indicara que están protegidas y que no es posible la modificación de las celdas. Con esto ya tenemos nuestra hoja de cálculo a salvo de modificaciones accidentales.

 


REFERENCIAS

 

COMENTARIOS

Publica tu comentario sobre este artículo

excel
Publicado por benjamin daniel . Fecha: 05-09-2014 21:59.

buen aporte me ayudo mucho algunos puntos

 

SOBRE LA PUBLICACIÓN

¿El artículo ha sido revisado? NO

Sobre el autor

Volver atrás