¿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.

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.