FUNCIONES ESTADÍSTICAS
CONCEPTOS:
Función MEDIANA
La función MEDIANA es una función estadística de Excel que obtiene la tendencia central, que no es nada más que la ubicación del centro de un grupo de números en una distribución estadística. La mediana es el número que se encuentra en medio de un conjunto de valores.
Sintaxis:
Argumentos:
- Nro1 Valor requerido.
- Nro2,… NroN son valores opcionales.
Los argumentos de la función MEDIANA pueden ser números, rangos con valores numéricos, matrices o referencias de celdas. El máximo número de argumentos que se pueden colocar en la función MEDIANA depende de la versión de Excel que estés utilizando. En Excel 2007 se pueden ingresar hasta 255 argumentos.
Si el total de elementos del conjunto de datos es par, MEDIANA calcula el promedio de los números centrales. Si el total de elementos es un número impar entonces la MEDIANA tomara el valor ubicado en el centro de la lista.
Si el argumento matricial o de referencia contiene texto, valores lógicos o celdas vacías, estos valores se pasan por alto; sin embargo, se incluirán las celdas con el valor cero.
Los argumentos que sean valores de error o texto que no se pueda traducir a números provocan errores.
Si desea incluir valores lógicos o de texto utiliza la función MEDIANAA.
Ejemplos de sintaxis de la función MEDIANA
| Tipo de Argumento | Ejemplo | Explicación |
| Referencia a Celdas | =MEDIANA(A1, B1) | Mediana entre los valores de las celdas A1 y B1 |
| Referencia a Rangos | =MEDIANA(A1:A10) | Mediana entre valores desde las celdas A1 hasta A10 |
| Referencia a Columnas | =MEDIANA(C:C) | Mediana entre valores de las celdas de la columna C |
| Referencia a Filas | =MEDIANA(1:1) | Mediana entre valores de las celdas de la fila 1 |
| Numeros | =MEDIANA(100, 200, A1) | Mediana entre 100, 200 y valor de la celda A1 |
| Multiple Columnas/Filas | =MEDIANA(A1:A10, C1:C10) | Mediana entre valores de las celdas desde A1 a A10 y desde C1 a C10 |
Ejemplo de uso de la función MEDIANA
Función MODA.UNO
La función MODA.UNO fue introducida en Excel 2010, pero puedes utilizar la función MODA para versiones anteriores. Esta función recibe como argumentos una lista de valores numéricos de los cuales se obtendrá el valor más frecuente. Por ejemplo, en la siguiente imagen puedes observar que la función MODA.UNO devuelve el valor 1, el cual se repite cuatro veces dentro del rango A1:A10.
Esta función siempre devolverá un solo valor y debemos tener en consideración los siguientes puntos al momento de utilizarla:
- Los argumentos pueden ser referencias a otras celdas o nombres de rango, pero las celdas deberán contener valores numéricos.
- Si alguna celda tienen un valor en texto o contiene un error, entonces la función devolverá el error #N/A.
- Si dentro de la lista no existen valores duplicados, entonces la función devolverá el error #N/A.
Como ya lo he mencionado antes, la función MODA.UNO siempre devuelve un solo valor pero ¿Qué sucede si tenemos dos valores numéricos que se repiten la misma cantidad de veces?
En el ejemplo anterior, tanto el número 2 como el número 3 se repiten 4 veces cada uno, sin embargo la función MODA.UNO solo nos puede devolver el número 2. Precisamente para resolver esta situación se creó la función MODA.VARIOS.
Función MODA.VARIOS
La función MODA.VARIOS es una función que nos devolverá múltiples valores por lo que debemos utilizarla como una fórmula matricial, es decir pulsando la combinación de teclas Ctrl + Mayús + Entrar al final de la fórmula. Pero antes de ingresarla debemos seleccionar el rango de celdas donde deseamos obtener los resultados. Para nuestro ejemplo seleccionaré el rango C1:C2:
Posteriormente en la barra de fórmulas introduciré la fórmula sin pulsar entrar:
=MODA.VARIOS(A1:A10)
Finalmente pulsaré la combinación de teclas Ctrl + Mayús + Entrar. Es importante mencionar que la tecla Mayús es también conocida por su nombre en inglés como la tecla Shift. Una vez ingresada la fórmula matricial, Excel desplegará los resultados en el rango previamente seleccionado:
A diferencia de la función MODA.UNO, la función MODA.VARIOS devuelve como resultado los valores que más se repiten dentro del rango indicado, que en este caso son los números 2 y 3. En este ejemplo hice una selección previa del rango C1:C2 pero en realidad eso no tiene relevancia, ya que si hubiera seleccionado una mayor cantidad de celdas, pero la fórmula no devuelve tantos resultados, entonces dichas celdas mostrarían el error #N/A sin afectar el resultado de la función:
Recuerda que la función MODA.VARIOS está disponible solamente a partir de Excel 2010 y lamentablemente no existe equivalencia para versiones anteriores. Solo la función MODA es equivalente a la función MODA.UNO.
Estadística descriptiva en
Excel
Existe una herramienta en Excel conocida como Estadística descriptiva que es muy útil para generar un resumen de datos estadísticos de nuestros datos de una manera rápida y sencilla. Para utilizar esta herramienta debes tener habilitadas las Herramientas de análisis de datos.
Habilitar las Herramientas de análisis de datos
Es importante verificar que tenemos habilitadas las Herramientas de análisis de datos y para ello debes ir a la ficha Datos y validar que tienes un grupo llamado Análisis y dentro un botón etiquetado como Análisis de datos.
Si no tienes las Herramientas de análisis de datos sigue los pasos del siguiente artículo para habilitarlas: Instalar las herramientas de análisis en Excel.
Utilizando la Estadística descriptiva en Excel
En el siguiente ejemplo podrás observar una lista de nombres y edades de las cuales necesitamos obtener un resumen de datos estadísticos.
Aunque podríamos utilizar fórmulas de Excel para obtener información como el valor máximo, el mínimo, la media, la suma, etc., podremos obtener toda esa información con solo utilizar la herramienta Estadística descriptiva.
El primer paso es pulsar el botón Análisis de datos de la ficha Datos y seleccionar la opción Estadística descriptiva.
Al pulsar el botón Aceptar se mostrará un nuevo cuadro de diálogo que nos permitirá hacer las configuraciones necesarias para obtener los datos estadísticos de nuestra información.
Las opciones dentro de este cuadro de diálogo a las que debes prestar especial atención son las siguientes:
- Rango de entrada: La columna que contiene los datos numéricos de los cuales se obtendrán los datos estadísticos.
- Agrupado por: Indica la orientación del rango de entrada. Para el ejemplo los datos están en una columna.
- Rótulos en la primera columna: Si dentro del rango de entrada está incluida la celda que contiene el título de la columna, entonces debes marcar esta caja de selección.
- Opciones de salida. Podrás elegir tres posibles opciones de salida: elegir un rango dentro de la misma hoja donde se colocarán los resultados, o elegir que los resultados se coloquen en una hoja nueva o en un libro nuevo.
- Resumen de estadísticas. Es necesario que esta opción esté seleccionada para obtener los datos estadísticos que necesitamos.
Una vez que has hecho las configuraciones necesarias en el cuadro de diálogo Estadística descriptiva pulsa el botón Aceptar para ver los resultados:
Así de fácil podemos obtener un resumen de datos estadísticos sin necesidad de utilizar funciones de Excel para calcular cada uno de los valores. Como puedes observar, con unos cuantos clics obtenemos la media, mediana, moda, desviación estándar, varianza, mínimo, máximo, etc.
¿Qué es un rango dinámico en Excel?
Cuando hablamos de rangos dinámicos en Excel nos referimos a una técnica especial que nos permitirá hacer referencia a un conjunto de celdas que se ajustará automáticamente al insertar o borrar datos por lo que siempre tendremos el cálculo adecuado sin necesidad de editar nuestras fórmulas.
Esta técnica implica el uso de dos funciones de Excel: DESREF y CONTARA. La función DESREF nos permite crear una referencia a un rango de celdas indicando la cantidad de filas y columnas a partir de una celda específica. La función CONTARA cuenta el número de celdas no vacías de un rango. Para entender mejor estas funciones haremos algunos ejemplos.
La función CONTARA
La función CONTARA nos devuelve la cantidad de celdas no vacías contenidas en un rango. Por ejemplo, en la siguiente imagen puedes observar que he introducido la función CONTARA con el rango A1:C5 como su argumento.
El rango A1:C5 está formado por 15 celdas, sin embargo la función CONTARA nos ayuda a saber que de todas esas celdas solamente 12 contienen un valor. Esta funcionalidad nos será de gran ayuda al momento de crear rangos dinámicos en Excel ya que utilizaremos esta función para contar las celdas que tienen valores dentro de una fila o de una columna. Por ejemplo, la siguiente fórmula nos ayuda a saber la cantidad de filas con datos dentro de la columna A:
=CONTARA($A:$A)
Observa el resultado de aplicar esta fórmula sobre los mismos datos del ejemplo anterior:
De esta manera podemos saber que nuestro rango tiene 4 filas con datos. Por otro lado, si queremos conocer las columnas de nuestro rango podemos utilizar la siguiente fórmula:
=CONTARA($1:$1)
Esta fórmula contará las celdas no vacías dentro de la fila 1 de nuestra hoja y al utilizarla con los datos de ejemplo tendremos el siguiente resultado.
En seguida combinaremos la función CONTARA con la función DESREF para crear rangos dinámicos en Excel.
Rangos dinámicos con DESREF
La función DESREF nos permite crear una referencia con tan solo indicar la celda donde comenzará el rango y posteriormente el número de filas y columnas que conforman dicho rango. Para nuestros datos de ejemplo, la celda inicial del rango se encuentra en la celda A1 por lo que comenzamos nuestra fórmula de la siguiente manera:
=DESREF($A$1,
El segundo y tercer argumento de la función DESREF nos sirven para indicar algún movimiento a partir de la celda inicial pero no son necesarios en la creación de rangos dinámicos así que los podemos indicar siempre como cero.
=DESREF($A$1, 0, 0,
Los argumentos importantes en la creación de rangos dinámicos son el cuarto y el quinto de la función DESREF ya que nos permiten indicar la cantidad de filas y columnas que deseamos incluir a partir de la celda inicial. El número de filas lo podemos obtener con la función CONTARA de la siguiente manera:
=DESREF($A$1, 0, 0, CONTARA($A:$A),
Y para obtener el número de columnas volvemos a utilizar la función CONTARA de la siguiente manera:
=DESREF($A$1, 0, 0, CONTARA($A:$A), CONTARA($1:$1))
Si introducimos esta fórmula por sí sola en una celda obtendrás un error #¡VALOR! ya que nuestra fórmula no devuelve un valor sino una referencia. Pero si colocamos la fórmula anterior dentro de la función SUMA le estaremos pidiendo a Excel que sume todos los valores del rango devuelto por la función DESREF.
=SUMA(DESREF($A$1, 0, 0, CONTARA($A:$A), CONTARA($1:$1)))
Al momento de ingresar esta fórmula obtendremos la suma de los valores en todo el rango:
Pero lo mejor de esta fórmula es que sumará automáticamente los datos adicionales que introduzcamos. Por ejemplo, en la fila 5 colocaré los valores 2, 4 y 6 y la suma se actualizará automáticamente:
Podrás introducir más datos hacia abajo y la suma siempre se actualizará automáticamente. De igual manera podrás insertar nuevas columnas de datos y de igual manera serán consideradas en el cálculo sin tener que modificar la fórmula.
Rango dinámico nombrado
Una manera de mejorar la administración de los rangos dinámicos en Excel es asignando un nombre a la referencia devuelta por la función DESREF. Para crear un nombre debemos ir a la ficha Fórmulas y pulsar el botón Asignar nombre que se encuentra dentro del grupo Nombres definidos.
Como resultado se mostrará un cuadro de diálogo que nos permitirá crear un nuevo Nombre y como referencia colocaremos la misma función DESREF creada en nuestro ejemplo anterior.
Al pulsar el botón Aceptar se creará el nombre MiRangoDinamico que hará referencia al mismo rango dinámico del ejemplo anterior. La ventaja de utilizar este método será que ya no tendremos que ingresar la función DESREF cada vez que queramos utilizar nuestro rango dinámico sino que será suficiente con introducir el nombre recién creado.
Para futuras modificaciones del rango dinámico recién nombrado podrás utilizar el Administrador de nombres de Excel que te permitirá modificar o eliminar dicho nombre.
Otro ejemplo de rangos dinámicos
El rango dinámico creado en el ejemplo anterior considera la adición de nuevas filas y de nuevas columnas pero hay ocasiones en las que será suficiente con vigilar solamente la adición de nuevas filas. En ese caso nuestra fórmula se simplifica ya que solo tendremos que utilizar la función CONTARA una sola vez.
En el siguiente ejemplo tenemos una lista de personas con sus respectivas edades y nos interesa tener una fórmula que obtenga el promedio de dichas edades conforme se agregan nuevos datos.
Ya que las edades que deseamos considerar en el cálculo se encuentran en la columna B, podemos utilizar la función DESREF de la siguiente manera:
=DESREF($B$1, 0, 0, CONTARA($B:$B))
Observa que en este caso he omitido el quinto argumento de la función DESREF porque no me interesa considerar nuevas columnas sino solamente nuevas filas y eso es precisamente lo que hace el cuarto argumento donde he colocado la función CONTARA. Ahora podemos crear un nuevo nombre utilizando la fórmula anterior.
Ya con el nombre creado podremos utilizarlo en la fórmula de la celda E1 para obtener el promedio de las edades de la siguiente manera:
Podemos aumentar la lista de edades y la fórmula calculará automáticamente el promedio considerando los nuevos datos ingresados.
Rangos dinámicos con encabezados
Hasta ahora nuestros ejemplos han sido con rangos de celdas que no tienen encabezados, pero es muy común que los datos en Excel tengan títulos en la primera fila por lo que tendremos que modificar un poco la manera de crear nuestro rango dinámico. En la siguiente imagen puedes ver los mismos datos del ejemplo anterior pero en esta ocasión la fila 1 tiene encabezados para cada columna.
La fórmula que utilizaremos para definir nuestro rango dinámico tendrá algunas modificaciones y será la siguiente:
=DESREF($B$2, 0, 0, CONTARA($B:$B)-1)
En primer lugar nuestro rango dinámico comenzará en la celda B2 y además haremos una resta del valor 1 al cuarto argumento de la función DESREF. Esto se debe a que la función CONTARA devolverá el valor 9 como la cantidad de celdas con datos en la columna B, y eso incluirá a la celda B1, pero necesitamos descartar el encabezado de la columna y por eso hacemos la resta. Si definimos el nombre Edades_2 utilizando esta fórmula, entonces obtendré el resultado adecuado al calcular el promedio.
Función FRECUENCIA
¿PARA QUÉ SIRVE LA FUNCIÓN FRECUENCIA EN EXCEL?
La función FRECUENCIA nos permite contar el número de veces que un número se da en un intervalo dentro de una serie de valores.
Por ejemplo, si tuviéramos la altura de 100 individuos, podríamos crear unos rangos de altura como estos: <150cm, 150-160, 160-170, 170-180 y 180-190. Pero, ¿qué pasaría si tenemos un valor mayor de 190cm?, ¿no se contabilizaría?. La función FRECUENCIA tiene todo esto pensado y por tanto puede calcularlos en el último de los valores de la lista. Pero bueno, será mucho más sencillo si vemos un ejemplo.
SINTAXIS
La sintaxis de esta función no es nada complicada pero hay que tener un pequeño conocimiento de trabajar con vectores/matrices en Excel (ver más). La sintaxis de esta función se compone de dos partes:
=FRECUENCIA( serie_de_datos;grupos/intervalos)
- serie_de_datos= la serie de datos es aquel rango de valores numéricos de los que queremos saber la frecuencia.
- grupos/intervalores= son los valores en los que queremos agrupar la serie_de_datos.
En la siguiente imagen veréis cómo utilizar los grupos o intervalos y qué significan.
En la imagen anterior vemos una breve explicación de cómo funcionan los grupos/intervalos en la función FRECUENCIA. Como puede verse el primer valor se utiliza para los valores menores a los que indicamos en el grupo y el último intervalo, el que está vacío, se reserva para los valores mayores.
EJEMPLO DE LA FUNCIÓN FRECUENCIA
EN EXCEL
Siguiendo el ejemplo de la altura que comentábamos previamente hemos creado una serie de 100 valores en la columna A donde tenemos valores aleatorios de la altura de un grupo de persona que van desde 140 cm hasta los 200. En la columna F pondremos los intervalos/grupos en los que queremos agrupar los datos de la columna A como puede verse en la imagen anterior. La fórmula que deberemos escribir es la siguiente:
=FRECUENCIA($A$2:$A$101;$F$3:$F$7)
En esta fórmula, la primera parte son los valores de las alturas y en la segunda parte los grupos de frecuencia donde queremos agrupar nuestros valores.
Para introducir la fórmula deberemos ingresar la fórmula a la vez en todo el rango equivalente en filas + 1 a la de los intervalos igual que en la siguiente imagen. Para ello seleccionaremos dicho rango, escribiremos la fórmula y la ingresaremos. En la siguiente imagen puede verse claramente como el recuadro en verde es la selección donde escribiremos nuestra fórmula.
Finalmente, como es una fórmula matricial, una vez escrita, en vez de ingresarla apretando Enter, deberemos hacerlo apretando Ctrl + Mayusc + Enter. Esto creará nuestra fórmula matricial… que si hemos escrito correctamente nos mostrará la fórmula entre paréntesis como puede ver se en la siguiente imagen:
Frecuencia Relativa en Excel
En el siguiente artículo resolveremos ¿Cómo obtener la frecuencia relativa en Excel? y ¿Cómo obtener la frecuencia absoluta en Excel?
Observemos en el ejemplo como encontramos una relación de documentos, los montos de cada documento y el estado en que se encuentra cada uno de estos documentos.
Frecuencia Absoluta en Excel
La frecuencia absoluta, corresponde al número de veces que aparece cada uno de los elementos a ser analizados, el el caso del ejemplo correspondería a los valores que se encuentran en la columna G.
Por medio de la fórmula empleada en G4:
=CONTAR.SI(D4:D11,F4)
Se esta identificando el número de documentos “Emitidos”, de forma similar se podría emplear algo como:
=CONTAR.SI(D4:D11,"=Cobrado")
Este fórmula sirve para determinar todas las celdas que sean iguales al texto “Cobrado”.
Fórmula para la Frecuencia Relativa
La frecuencia relativa permite identificar que porcentaje representa la cantidad del elemento analizado respecto del total de elementos analizados, para efectos del ejemplo la cantidad de documentos en estado “Emitido” son 4 mientras que la cantidad total de documentos es de 8, por lo que la frecuencia relativa sería = 4/8, lo cual es el 50%.
En el ejemplo, la fórmula necesaria para determinar a frecuencia relativa en Excel, sería la siguiente:
=CONTAR.SI($D$4:$D$11,F4)/CONTARA($D$4:$D$11)
De esta forma la función CONTAR.SI permite determinar la cantidad de documentos emitidos, mientras que la función CONTARA permite identificar la cantidad total de documentos existentes.
FRECUENCIA ABSOLUTA
La frecuencia absoluta (ni) de un valor Xi es el número de veces que el valor está en el conjunto (X1, X2,…, XN).
Ejercicio
Frecuencia absoluta acumulada
La suma de las frecuencias absolutas de todos los elementos diferentes del conjunto debe ser el número total de sujetos N. Si el conjunto tiene k números (o categorías) diferentes, entonces:
La frecuencia absoluta (ni) de un valor Xi es el número de veces que el valor está en el conjunto (X1, X2,…, XN).
Ejercicio
Frecuencia absoluta acumulada
La suma de las frecuencias absolutas de todos los elementos diferentes del conjunto debe ser el número total de sujetos N. Si el conjunto tiene k números (o categorías) diferentes, entonces:
Un profesor tiene la lista de las notas en matemáticas de 30 alumnos de su clase. Las notas son las siguientes:
Se realiza el recuento de la variable que se estudia (notas) para ver el número de veces que aparece cada nota.
Una vez realizado el recuento, se representan las frecuencias absolutas de cada una de las notas (ni). Las frecuencias son: n1(3)=2, n2(4)=4, n3(5)=6, n4(6)=7, n5(7)=5, n6(8)=3, n7(9)=2 y n8(10)=1.
Un profesor tiene la lista de las notas en matemáticas de 30 alumnos de su clase. Las notas son las siguientes:
Se realiza el recuento de la variable que se estudia (notas) para ver el número de veces que aparece cada nota.
Una vez realizado el recuento, se representan las frecuencias absolutas de cada una de las notas (ni). Las frecuencias son: n1(3)=2, n2(4)=4, n3(5)=6, n4(6)=7, n5(7)=5, n6(8)=3, n7(9)=2 y n8(10)=1.
La frecuencia absoluta acumulada(Ni) de un valor Xi del conjunto (X1, X2,…, XN) es la suma de las frecuencias absolutas de los valores menores o iguales a Xi, es decir:
La frecuencia absoluta acumulada(Ni) de un valor Xi del conjunto (X1, X2,…, XN) es la suma de las frecuencias absolutas de los valores menores o iguales a Xi, es decir:
