• Author / Uploaded
• Lourdes Borrajo

Citation preview

FUNCIONES EN EXCEL Excel tiene más de 400 funciones que permiten efectuar desde los cálculos más sencillos hasta los más complejos. Este tema se centra en aprender a utilizar este recurso tan importante y útil.

1.1.

Tratamiento de las funciones

Una función es una fórmula abreviada que efectúa varias operaciones simultáneamente, devolviendo el resultado. Las funciones pueden utilizarse independientemente o como operadores dentro de una fórmula o incluso dentro de otra función (funciones anidadas). Por ejemplo, calcular el valor medio de las notas de los alumnos de un examen se puede hacer sumando todas las notas y dividiendo entre el número total de alumnos o utilizando la función Promedio, de una forma más directa, rápida de introducir y fácil de comprender, tal y como muestra la Figura 1.

Figura 1: Ejemplo de utilización de la función Promedio

Para comenzar a escribir una función en una celda, se debe de teclear el símbolo = y a continuación, sin espacios intermedios, el nombre de la función, y por último, si la función lo necesita, escribir (también sin espacios) los datos entre paréntesis.

=FUNCIÓN(datos) Por ejemplo, para calcular la anterior fórmula del promedio los pasos a seguir son los siguientes: 1. Colocar el cursor en la celda B12. 2. Teclear =PROMEDIO( 3. Hacer clic en B2 y arrastrar hasta B7. 4. Teclear ) y al terminar pulsar Enter. También se puede introducir una fórmula utilizando el asistente para funciones: 1. Colocar el cursor en la celda B12. 2. Hacer clic en el botón Insertar función situado en la barra de herramientas Estándar (también se puede seleccionar el menú Insertar/Función). Se visualizará el cuadro de diálogo mostrado en la Figura 2.

Figura 2: Cuadro de diálogo Insertar Función

3. Hacer clic en la categoría Estadísticas. La lista Seleccionar una función visualizará las funciones estadísticas disponibles. 4. Seleccionar la función Promedio. 5. Hacer clic en el botón Aceptar, con lo que se visualizará el cuadro de diálogo mostrado en la Figura 3.

Figura 3: Cuadro de diálogo Argumentos de función

6. Ubicarse en la casilla Número1, y a continuación seleccionar directamente en la hoja de cálculo el rango de datos del que se desea calcular el promedio, en este caso B2:B7. Si no se ve el rango de celdas que se quiere seleccionar porque lo impide el cuadro de diálogo, se puede minimizar pulsando el botón

con lo que la ventana tomará el siguiente aspecto:

De este modo, ahora se puede seleccionar sin problemas el rango de celdas. Para volver al volver al cuadro de diálogo, pulsar el botón . 7. Para terminar, pulsar el botón Aceptar con lo que la fórmula queda insertada en la celda actual. Excel mostrará en la celda el resultado mientras que en la barra de fórmulas muestra la fórmula contenida en esa celda. Si hay un error, por ejemplo, al introducir el rango de celdas, se puede borrar en la barra de fórmulas dicho rango, haciendo clic con el ratón entre los dos paréntesis de la fórmula y volver a seleccionar el grupo de celdas.

La función Suma, excepcionalmente, puede realizarse automáticamente con el botón Autosuma, que aparece en la barra de herramientas Estándar . Hay dos opciones en este caso: 1. Se puede presionar sobre el símbolo de sumatoria y Excel procederá a realizar un proceso de Autosuma como se indicará más adelante. 2. La otra opción consiste en presionar sobre la pequeña flecha que aparece a la derecha del botón Autosuma, la cual despliega un menú donde se muestran otras funciones a ejecutar, ya sea las de uso común u otras funciones al igual que en

(ver Figura 4).

Figura 4: Menú Autosuma

Como se puede observar este menú permite selecciones varias funciones directamente, pero si se selecciona la opción Más Funciones aparecerá el cuadro de argumentos de función mostrado en la Figura 3. Si lo que se desea es solo hacer una Autosuma, basta con seleccionar un rango de celdas y pulsar sobre el símbolo de sumatoria (Σ), con lo que aparece el resultado de sumar todos los datos. Este resultado se muestra una celda más abajo del rango seleccionado. Otra posibilidad consiste en colocarse en la celda en la que se quiere obtener la suma y pulsar el botón antes de seleccionar ningún rango de celdas, con lo que aparecerá una línea punteada seleccionando el rango de celdas que Excel sumará. Si el rango de celdas que se desean sumar no es el seleccionado por Excel, se puede elegir, seleccionando con el ratón, las celdas a sumar. Al terminar se pulsa Enter y se obtiene el resultado de la suma. Muchas de las funciones que se utilizan en Excel necesitan un rango de datos con los que operar. Este rango de datos tendrá que escribirse entre paréntesis o bien, se puede utilizar el ratón para seleccionar el conjunto de celdas y el propio Excel escribe el rango en la función. Cuando se haya seleccionado el rango, para terminar, sólo habrá que cerrar el paréntesis y pulsar Enter.

1.2.

Funciones matemáticas y trigonométricas

1.2.1. SUMA Suma los números proporcionados como argumentos. La Figura 5 muestra un ejemplo del uso de esta función.

Figura 5: Ejemplo de utilización de la función Suma

1.2.2. SUMAR.SI Suma las celdas que cumplen un determinado criterio o condición. Sintaxis: =SUMAR.SI(rango;criterio;rango_de_suma)   

Rango: es el rango de celdas que se desean evaluar. Criterio: es el criterio o condición que determina que celdas deben sumarse. Rango_de _suma: son las celdas que se van a sumar. Si se omite, se sumarán las celdas indicadas en rango.

La Figura 6 muestra un ejemplo en el que, a partir de los salarios de los empleados de los distintos departamentos de una empresa, se quiere obtener cuanto tiene que pagar cada departamento.

Figura 6: Ejemplo de utilización de la función Sumar.Si

1.2.3. PRODUCTO Multiplica los números proporcionados como argumentos. Por ejemplo, si se introduce en una celda la fórmula =PRODUCTO(10;30) se obtendrá como resultado 300. Esta fórmula se podría sustituir por =10 * 30.

1.2.4. RAIZ Extrae la raíz cuadrada del número proporcionado como argumento. La Figura 7 muestra un ejemplo.

Figura 7: Ejemplo de utilización de la función Raiz

1.2.5. FACT Calcula el factorial de un número entero y positivo. El factorial de N es igual a N * (N-1) * (N-2) * ... * 1. Por ejemplo, si se introduce en una celda la fórmula =FACT(4) se obtendrá el valor 24.

1.2.6. ABS Calcula el valor absoluto de un número. El valor absoluto de un número es el mismo número sin signo. Por ejemplo, si se introduce en una celda la fórmula =ABS(-10) se obtiene como resultado 10. De la misma forma, si se introduce la fórmula =ABS(10) se obtiene el mismo resultado.

1.2.7. ALEATORIO Devuelve un número aleatorio comprendido entre 0 y 1. Esta función no tiene argumentos. Esta función proporciona un nuevo número aleatorio cada vez que Excel efectúa un cálculo. Por ejemplo, la función =ALEATORIO() proporciona un número entre 0 y 1, mientras que la función =3 * ALEATORIO() proporciona un número entre 0 y 3.

1.2.8. PI Devuelve el número . Por ejemplo, si se introduce en una celda =PI() devolverá 3,14159265.

1.2.9. REDONDEAR Devuelve el primer argumento redondeando un número de decimales especificado en el segundo argumento. La Figura 8 muestra un ejemplo.

Figura 8: Ejemplo de utilización de la función Redondear

1.2.10. ENTERO Devuelve el entero por defecto. Por ejemplo, =ENTERO(1,99) devuelve como resultado 1, mientras que =ENTERO(-1,99) devuelve como resultado -2.

1.2.11. TRUNCAR Devuelve como resultado la parte entera de un número. Por ejemplo, =TRUNCAR(3,14159) devuelve como valor el número 3, mientras que =TRUNCAR(-3,14159) devuelve -3.

1.2.12. SENO Devuelve el seno de un ángulo en radianes expresado por el argumento. Por ejemplo:

1.3.

=SENO(PI())



0

=SENO(PI()/2)



1

Funciones estadísticas

Básicamente, las funciones estadísticas se pueden dividir en dos grupos: estadística descriptiva y estadística inferencial y a su vez, se pueden realizar analisis univariados o multivariados. En el caso de la estadística descriptiva simplemente se busca ver el posible comportamiento del fenómeno en estudio, como por ejemplo: 1. la concentración de datos 2. Alrededor de que valores giran los datos 3. Distribución de esos datos, etc.. En el caso de la estadística inferencial, lo que se busca es hacer inferencias sobre el posible comportamiento futuro de una variable o que tan similar es el comportamiento de una variable respecto a otra, la correlación que pueda existir entre ellas, etc., todo basándose en una información de muestra. A continuación, se mostrarán una serie de funciones que están relacionados con la estadística descriptiva (teniendo en cuenta que algunas variables serán continuas y otras discretas) y, a continuación, se mostrará una pequeña introducción a las herramientas para análisis inferencial.

1.3.1. Funciones de estadística descriptiva 1.3.1.1.

MAX

Devuelve el mayor número que contiene el argumento o conjunto de celdas. La Figura 9 muestra un ejemplo.

Figura 9: Ejemplo de utilización de la función Max

1.3.1.2.

MIN

Devuelve el menor número que contiene el argumento. La Figura 10 muestra un ejemplo.

Figura 10: Ejemplo de utilización de la función Min

1.3.1.3.

CONTAR

Cuenta el número de celdas cuyo contenido es un número del rango de celdas que se introduce como argumento de la función. La Figura 11 muestra un ejemplo.

Figura 11: Ejemplo de utilización de la función Contar

1.3.1.4.

CONTARA

Cuenta el número de celdas no vacías.

1.3.1.5.

CONTAR.SI

Cuenta las celdas en el rango que coinciden con la condición dada. En la Figura 12 se muestra un ejemplo de su utilización. Sintaxis: =CONTAR.SI(rango;condición)

Figura 12: Ejemplo de utilización de la función Contar.Si

1.3.1.6.

PROMEDIO

Devuelve la media aritmética. En la Figura 13 se muestra un ejemplo de su utilización.

Figura 13: Ejemplo de utilización de la función Promedio

1.3.1.7.

MEDIANA

Devuelve la mediana de un rango. La mediana es una medida de posición o promedio, que se define como el valor central de una distribución ordenada. En el caso de que la distribución tenga un número par de valores, la mediana es la medida aritmética de los dos centrales. En la Figura 14 se muestra un ejemplo de su utilización.

Figura 14: Ejemplo de utilización de la función Mediana

1.3.1.8.

DESVEST

Devuelve el valor de la desviación estándar para un rango de valores que corresponden a una muestra de una población. Sintaxis =DESVEST(Rango_de_datos)

1.3.1.9.

CURTOSIS

La curtosis es una medida de concentración de datos. Analiza la concentración de un rango de datos y, en función de ese valor, determina que tan dispersos o concentrados se encuentran los datos. Sintaxis =CURTOSIS(Rango_de_datos)

1.3.1.10. COEFICIENTE.ASIMETRIA Devuelve el coeficiente o índice de asimetría, dicho índice es muy útil a la hora de establecer el sesgo de los datos. Sintaxis = COEFICIENTE.ASIMETRIA (Rango_de_datos)

1.3.1.11. MODA Devuelve el valor que mas se repite dentro de un rango de datos. Hay que tener en cuenta que sólo hace un conteo de datos de forma discreta, no hace una estimación en caso de datos continuos. Sintaxis =MODA(Rango_de_datos)

1.3.1.12. VAR Esta función calcula la varianza. La varianza es el cuadrado de la desviación estándar (es decir, la desviación estándar es la raíz cuadrada de la varianza). Sintaxis =VAR(Rango_de_datos)

1.3.2. Funciones de estadística inferencial. En lo referente a las herramientas para análisis inferencial, Excel dispone tanto de funciones como de herramientas de análisis. Se pueden realizar pruebas estadisticas como la Prueba Z, t-student, F, etc., así como, también posee funciones que permiten realizar análisis de regresión o de series de tiempo. La Figura 15 muestra ejemplos de algunas de estas funciones.

Figura 15: Ejemplo de funciones de estadística inferencial

1.3.2.1.

COEF.DE CORREL

Mediante esta función se obtiene el coeficiente de correlación entre dos variables, una considerada dependiente y otra considerada independiente. Dicho coeficiente indica el grado de relación, no de causalidad, entre las dos variables. Se debe de tener en cuenta que los valores de cada variable se deben de colocar seguidos, ya sea en filas o columnas. Sintaxis =COEF.DE.CORREL(Rango1_variable_dependiente; Rango1_variable_independiente)

1.3.2.2.

COEFICIENTE.R2

Esta función devuelve el valor del coeficiente de terminación. Es equivalente al cuadrado del coeficiente de correlación, y representa el porcentaje de valores que se encuentran justificados en la variable independiente. Sintaxis =COEFICIENTE.R2(Rango1_variable_dependiente; Rango1_variable_independiente)

1.3.2.3.

ERROR.TIPICO.XY

Devuelve el valor del error típico de Y respecto a cualquier estimación basada en X en la regresión.

Sintaxis =ERROR.TIPICO.XY(Rango1_variable_dependiente; Rango1_variable_independiente)

1.3.2.4.

PEARSON

Devuelve el índice de correlación de Pearson Sintaxis =PEARSON(Rango1_variable_dependiente; Rango1_variable_independiente)

1.3.2.5.

Herramientas de análisis VBA

Excel también dispone de una serie herramientas de análisis denominadas Herramientas de análisis VBA, las cuales se activan en Herramientas/Complementos (ver y una vez ahí aparece el siguiente cuadro de dialogo:

Figura 16: Complementos en Excel

Una vez ahí, se deben activar los botones Herramientas para análisis y Herramientas para análisis VBA. Una vez activadas, se accede a dichas herramientas a través de Herramientas/ Análisis de Datos. Activando esa opción aparecerá el cuadro de dialogo mostrado en la Figura 17. Al desplazarse a través de él, se podrán observar las distintas herramientas de las que dispone Excel. El funcionamiento de dichas herramientas queda fuera del alcance de este documento, dado que su manejo requiere importantes conocimientos de estadística.

Figura 17: Análisis de datos

1.4.

Funciones lógicas

1.4.1. Función SI Mediante esta función se puede dotar de capacidad de decisión a las hojas de cálculo. Sintaxis: =SI(Condición; Valor si es verdadera; Valor si es falsa) La Figura 18 muestra un ejemplo en el que, en base a una serie de datos correspondientes a los importes de las compras realizadas por los clientes de un comercio, se calcula el descuento que se les aplica teniendo en cuenta que se les efectúa un descuento del 15% si superan los 300 euros de compra, y un 10% al resto. Para implementar esta situación donde la hoja debe decidir si descontar el 10% o el 15%, se tiene que utilizar la siguiente expresión: =SI(B2>300;15%;10%)

Figura 18: Ejemplo de utilización de la función SI

1.4.2. OPERADORES ESPECIALES Existen tres operadores especiales que permiten efectuar operaciones lógicas más sofisticadas. Se trata de los operadores Y, O, NO. Sintaxis: =Y(condición 1; condición 2;...) devuelve el valor verdadero si todas las condiciones son verdaderas. Se puede sustituir por =(condición 1 * condición 2 * ....) =O(condición 1; condición 2;...) devuelve el valor verdadero si alguna de las condiciones es verdadera. Se puede sustituir por =(condición

1 + condición 2 + ....)

=NO(condición 1) devuelve el valor verdadero si Condición 1 es falso. Ejemplos: Supóngase que la celda B1 contiene el valor 10 y la celda B2 contiene el valor 20, entonces: =Y(B1>B2;B1=10) devuelve falso. =Y(B1B2;B2>B1) devuelve verdadero. =NO(B1=50) devuelve verdadero. La Figura 19 muestra otro ejemplo: supóngase una empresa en la que hay que calcular el salario de cada empleado. Para ello se tendrán en cuenta los siguientes datos:    

Los Analistas tendrán un salario base de 200 € y el resto de 180 €. Plus Transporte: Tendrán un 10% del salario base los de Ourense o Lugo. Plus personal: Los administrativos de Ourense tendrán un plus de 20 € . El salario total se calcula sumando el salario base y los pluses de transporte y personal.

Figura 19: Ejemplo de utilización de la función SI con los operadores Y/O

1.4.3. Funciones SI anidadas Se pueden efectuar simulaciones más complejas anidando funciones SI. En el ejemplo de la Figura 20 se muestran los resultados de un examen. La función SI anidada permite evaluar si el alumno obtiene una calificación de suspenso, bien, notable o sobresaliente: Si la nota es