Funciones Estadísticas de Microsoft Excel ¿Qué son, para qué sirven y cómo aplicarlas en mis hojas de cálculo sin errores?

Cuando se trabaja con estos tipos de programas los usuarios tiene la oportunidad de contar con una gran cantidad de funciones que les permiten poder procesar datos, información y números de una forma mucho más fácil y práctica, sin la necesidad de tener conocimientos muy avanzados en el tema.

Es así como las funciones permiten procesar una gran cantidad de datos y obtener respuestas rápidamente. Pero no es un secreto para nadie que son muchas las funciones disponibles en el programa de Microsoft, por lo que resulta muy difícil poder conocerlas todas.

De acuerdo con esto y conociendo la importancia de cada uno de estos elementos, aquí te vamos a explicar un poco más el funcionamiento y cómo puedes aplicarlas en tu hoja de cálculo, para ello sigue detalladamente todo lo que te enseñaremos en el post.

¿Qué es una función estadística en Excel y para qué sirve en mis documentos?

Las funciones estadísticas de Excel te permiten poder calcular todas las operaciones matemáticas que se encuentren relacionadas con el análisis de datos almacenados en una hoja de cálculo. Todo esto ayuda a obtener el número de entrada de datos o el valor promedio de los mismos.

De esta manera se puede decir que la estadística es una disciplina matemática que permite estudiar las diferentes formas de resumir, recopilar y sacar conclusiones de una serie de datos que se estudia. Es así como esta función de Microsoft permite realizar un análisis estadístico de información, donde es necesario contar con fórmulas para poder conseguir los valores de la media, varianza media, desviación estándar, entre muchas otras.

Algunas de las funciones más utilizadas por los usuarios de este programa son el “Promedio, contar, MAX, MIN, moda, frecuencia” entre muchas otras que están allí disponibles. Además, hay que mencionar que estas herramientas están disponible prácticamente en todas las versiones de Excel, ya se tanto para las versiones gratuitas o de pago.

Lista de todas las funciones estadísticas de Excel y cómo aplicarlas de forma correcta en mis documentos

Como ya se mencionó anteriormente, existe una gran cantidad de funciones de estadísticas disponible en el programa, por lo que resulta prácticamente imposible poder conocerlas todas o por lo menos conocer exactamente el funcionamiento de cada una de ellas.

De acuerdo con esto, aquí te vamos a explicar las funciones más importantes de Excel y como puedes aplicarla de la forma correcta en tu hoja de cálculo.

Para ello sigue detalladamente la lista que te enseñaremos a continuación:

Desvprom

Devuelve el promedio de las desviaciones absolutas de la media de los puntos de datos. De esta manera, esta función se encarga de medir la dispersión de los valores en un conjunto de datos en específico. La fórmula debería quedar de la siguiente manera: =DESVPROM(número 1: número 2).

En este caso el número 1 es obligatorio, mientras que los siguientes números son opcionales, estos pueden ir entre 1 y 255 argumentos, esto dependerá de las desviaciones absolutas que se quieran calcular. Cabe mencionar que los argumentos de la función deben ser números, o en tal caso matriz, referencias o nombres que contengan números.

Para poder aplicar esta fórmula debes tener una serie de datos numéricos ya sea en una columna o una fila de tu hoja de cálculo, en este caso se tiene en la columna A entre las celdas A1 y A8, por lo que se aplica la fórmula de la siguiente manera: =DESVPROM(A2:A8), esto te generará el resultado que se ve en la imagen.

Promedio

En este caso con esta función se devuelve el promedio de los argumentos, es decir, si el intervalo está comprendido entre la celda A1:A20, la fórmula debe ser =PROMEDIO(A1:A20), de esta forma vas a obtener el promedio de todos los datos que estén contenido entre dichas celdas.

PromedioA

Esta función se encarga de calcular el promedio de la media aritmética de los valores en la lista de los argumentos. Por lo que la fórmula en este caso debe quedar de la siguiente manera: =PROMEDIOA(Valor1: Valor2).  El valor 1 es obligatorio, mientras que los valores subsiguientes son opcionales. Esto dependerá del rango que se quiera para obtener el promedio.

En esta ocasión los argumentos pueden ser números, referencias, matrices o nombres que contengan números, representaciones textuales de números, valores como verdadero o falso.  

En el caso de los argumentos que contienen verdadero se evalúan como 1, mientras que aquellos que contienen falso se toman como 0. Para los argumentos de referencias o matrices que contengan texto se tomarán como 0, y el texto vacío también se evaluará como 0.

Aquellos argumentos que son valores de error o texto no se pueden traducir a texto por lo que generará un error en el resultado. Y en el caso que no se quiera incluir valores lógicos, representaciones textuales de números como parte del cálculo, entonces se recomienda utilizar la función “Promedio”.

Promedio.si

Esta función se encarga de devolver el promedio de todas las celdas de un rango en específico, los cuales deben cumplir unos criterios determinados.

La fórmula en este caso queda de la siguiente manera:

• PROMEDIO.SI(rango; criterios; rango promedio).
• El rango es obligatorio, donde una o más celdas según sea el promedio que se quiera obtener, estos pueden incluir números, nombres, referencia o matrices que contengan números.
• El criterio también es obligatorio, el criterio puede ser en forma de número, referencia de celda o texto, expresión, entre otros. De esta forma se puede decir que los criterios pueden expresarse como 32, “32”, “>32”, “Peras” o B4.
• El rango promedio es opcional, este es el conjunto real de celdas cuyo promedio se va a calcular, si se omite, se utilizará el rango.

En cuanto a las observaciones se debe mencionar que en este caso no se toman en cuenta las celdas de rango que contienen valores verdaderos o falsos. Ahora si el rango es un valor en blanco o de texto, la función va a devolver el valor de error #¡DIV0!.

En cada uno de los criterios se puede llegar a utilizar caracteres comodines de signo de interrogación y asterisco. Tampoco es necesario que el rango promedio tenga el mismo tamaño y forma de rango.

Un ejemplo de todo esto es el siguiente:

Función contar

Con esta función vas a tener la oportunidad de contar la cantidad de celdas que contienen números y cuenta los números dentro de la lista de argumentos. De esta manera, se puede utilizar cuando se necesita obtener la cantidad de entradas de un campo de número de un rango o matriz de números.

De esta manera, se puede escribir la fórmula para contar los números en el rango A1:A20: =CONTAR(A1:A20). El valor 1 es obligatorio en la fórmula, primer elemento para referenciar el rango en el que se quiere contar los números. Mientras que para el valor 2 es opcional, puede contener hasta 255 elementos, celdas o referencias en los que se desea contar números.

Entre las consideraciones a tomar en cuenta hay que contar los argumentos que son números, fechas, representaciones de texto de los números. También se tienen en cuenta los valores lógicos y las representaciones textuales de números escritos directamente en la lista de argumentos.

Ahora si el argumento es una referencia o una matriz, solo se debe considerar los números de esta matriz o referencia. Lo que quiere decir que las celdas vacías o valores lógicos no se cuentan en estos casos. Para poder contar los valores lógicos, texto o valores de error será necesario que hagas uso de la función CONTARA.

Un ejemplo de esta función CONTAR es el siguiente:

Función CONTARA

Con la herramienta CONTARA vas a tener la oportunidad de llevar la cuenta de la cantidad de celdas que no están vacías en un intervalo. En este caso la fórmula se escribe de la siguiente manera: =CONTARA(valor 1: valor 2). El valor 1 es obligatorio, ya que será el primer argumento que representa los valores que desea contar. Mientras que el valor 2 es opcional, estos argumentos adicionales representan los valores que se desea contar.

Entre los argumentos principales de esta herramienta se tiene en cuenta que la función CONTARA cuenta las celdas que tiene cualquier tipo de información, incluyendo los valores de error y texto vacío. En el caso que no quieras contar los valores lógicos, texto o valores de error, entonces lo recomendable es que utilices la función CONTAR.

Un ejemplo de todo esto es lo siguiente:

CONTAR.SI

La función CONTAR.SI es una de las funciones de estadísticas de Excel más utilizada al momento de contar el número de celdas que cumplen un determinado criterio. La misma se puede utilizar para contar el número de veces que un producto aparece en una determinada lista.

Esta función indica lo siguiente: Al aplicarla vas a tener la oportunidad de realizar la búsqueda y que quieres buscar. La misma se puede escribir de la siguiente manera: =CONTAR.SI(A2:A5, “plástico”), o también seleccionando la celda donde se encuentra la palabra que se quiere buscar =CONTAR.SI(A2:A5;A4).

Para que todo esto pueda ser mucho más sencillo de entender, aquí te dejamos el siguiente ejemplo:

Función DESVIA2

Desvia2 se encarga de devolver la suma de los cuadrados de las desviaciones de los puntos de datos a partir del promedio que se muestra de la media.

Para este caso se debe utilizar la siguiente fórmula: DESVIA2(número 1, número 2,…) al igual que en todos los casos anteriores el número 1 es obligatorio, mientras que el número 2.. son opcionales. Entre los argumentos que se añadan a esta función se pueden encontrar números, referencias, nombres o matrices  que contengan números. También se tienen en cuentan los valores lógicos y las representaciones textuales de números escritos.

En el caso de los valores de error o texto no se pueden traducir ya que los mismos provocan errores en el resultado.

Para que se pueda entender esto un poco más aquí te dejamos este ejemplo:

Función pronóstico

En esta función vas a poder calcular o anticipar un valor futuro utilizando valores ya existentes, por lo que un valor futuro es un valor y para un valor X, los valores existentes son valores x y valores y conocidos. Por lo tanto, la función pronóstico se va a definir con la regresión lineal.

La aplicación de esta fórmula se hace principalmente para predecir las ventas futuras de un negocio, los requisitos de inventarios, la tendencia de los consumidores, entre otras opciones.

En el caso de esta función es necesario considerar que si el valor de X es un valor no numérico, se arrojará la #VALUE o #VALOR!. Ahora si el known y o known x está vacío, o uno de los dos tiene más punto que el otro. En este caso se devuelve el valor error #N/A. Y por último, si la varianza de known x es igual a cero, entonces se devuelve el #DIV/0! O #VALOR!.

Para que se pueda entender mejor aquí te dejamos este ejemplo:

Frecuencia

Cuando se aplica esta fórmula se hace con el objetivo de calcular la frecuencia con la que se producen los valores dentro de un rango específico de datos. En este caso devuelve una matriz vertical de números.

Uno de los motivos más comunes para utilizar esta función de frecuencia es para contar el número de resultados que se encuentran dentro de un rango en específico. De esta manera, la función frecuencia se especifica como una fórmula de matrices ya que la frecuencia devuelve una matriz.

Para poder comprender esto mejor, aquí te dejamos este ejemplo:

Estimación lineal

Sirve para poder calcular las estadísticas de una línea a través del método de los mínimos cuadrados, lo que permite calcular la línea que mejor se ajuste a los datos. Para que después devuelva una matriz que describe la línea.

También se puede combinar lo que es la estimación.línea con otras funciones para así poder calcular estadísticas de otros modelos que son lineales en los parámetros desconocidos.  Entre estos parámetros se incluyen series logarítmicas, exponenciales, de potencia y polinómicas. Todo esto se debe a que la función regresa una matriz de valores que debe ser especificada como fórmula de matriz.

En cuanto a las observaciones que se deben tener presente se puede describir cualquier línea recta con la pendiente y la intersección y:

• Pendiente (m): Se usa para buscar la pendiente de una línea, esta se escribe siempre o mayormente como m, toma dos puntos en la línea (X1, Y1) o (X2, Y2), la pendiente es igual a (Y2-Y1)/(X2-X1).
• Intersección Y (b): suele está definida mayormente con la letra (b), y es el valor de y en el punto donde la línea se cruza en el eje y.
• La ecuación de la línea recta es y=mx + b. esto quiere decir que si se conocen los valores de m y b se podrá calcular fácilmente. Esto permite calcular cualquier punto de la línea insertando los valores de Y o X en la ecuación.

Ahora si solo se conoce una variable X independiente, se puede llegar a obtener los valores de la pendiente y de la intersección utilizando cualquier de las siguientes fórmulas:

• Pendiente: =Indice (ESTIMACION.Lineal (known_y, known:x), 1)
• Intersección: =ESTIMACION.Lineal (known_y, known_x). 2)

Cuando se calcula la estimación.linea la exactitud de la línea calcula va a depender del grado de dispersión de todos los datos a evaluar. Si los datos son lineales más exacto será el modelo. De esta manera, la estimación.lineal hace uso del método de los mínimos cuadrados para así conocer el mejor ajuste para los datos.

Para poder entender esto un poco más aquí te dejamos este ejemplo:

Función MAX

Es utilizada frecuentemente para conocer el valor máximo de un conjunto de valores en específico. Mayormente se usa para saber que vendedor obtuvo más venta en un mes, cuál fue el producto que más se vendió durante todo el año, entre muchas otras  alternativas.

Los argumentos de esta función pueden ser números, nombres, matrices o referencias que tengan números. Además, se toma en cuenta los valores lógicos y las representaciones textuales de números escritos. En el caso que se trate de una referencia o una matriz se toma en cuenta solo los números contenidos allí.

Ahora si el argumento no contiene números, la función MAX arrojará cero 0. Si se trata de un valor de texto o error no se podrá traducir por lo que se generará un error.

Teniendo esto en cuenta, aquí te dejamos un ejemplo de dicha función MAX:

Función MIN

En este caso sucede lo contrario a la función anterior, se utiliza para conocer cuál es el valor mínimo de un conjunto de datos. Esto quiere decir que con ella puedes conocer qué vendedor te está generando menos ventas, que producto demora más en salir, entre muchas otras.

Al igual que la función MAX, los argumentos pueden ser números, matrices, nombres y referencias que contengan números. Aquí se toman en cuenta los valores lógicos y las representaciones textuales de números escritos en la lista de argumentos.

En el caso de las matrices y referencias solo se tomarán en cuenta si tienen números, todas las celdas vacías se tomarán como cero. Los argumentos que son de valores de texto o de error no se pueden traducir, por lo que generaran como resultado un error.

Para poder comprender todo esto mejor te dejamos el siguiente ejemplo:

Pendiente

Tiene como finalidad poder devolver la pendiente de una línea de regresión lineal que ha sido creada a través de los datos de los argumentos conocidos en X y en Y.

Por lo tanto, la pendiente es la distancia vertical dividida por la distancia horizontal entre dos puntos cualquiera de una recta. Todo esto corresponde a la tasa de cambio en el trayecto de la línea de regresión.

Esta función cuenta con los siguientes argumentos:

• Conocido_X: es obligatorio, y es el conjunto de puntos de datos independientes.
• Conocido_Y: también es obligatorio y es un rango o matriz de celdas de puntos de datos numéricos dependientes.

Al igual que en la mayoría de las funciones mencionadas anteriormente sus argumentos debe ser números o referencias que contengan números. En el caso que no contengan estas celdas tendrán un valor de cero.

Si los argumentos conocido_y y conocido_x se encuentra sin valor o tiene un número distintos de punto de datos, la Pendiente va a dar el valor error #N/A.

Para que puedas entender esto mucho mejor te dejamos este ejemplo:

Se puede decir que estas son algunas de las funciones más utilizadas en el programa de Microsoft Excel y las cuales te van a ayudar a facilitar ciertas operaciones matemáticas y de estadísticas al momento de querer conocer ciertos valores en específico.

Sin embargo, existen muchas otras funciones estadísticas disponibles las cuales te las presentamos de una forma mucho más resumida:

FUNCIÓN ESTADÍSTICA	DESCRIPCIÓN
DISTR.T.CD	Te devuelve la distribución de t de Studen.
INV.T.2C	Devuelve la función inversa de la distribución de t de Student.
INVT.T	En esta ocasión arroja el valor de t de la distribución t de Student en función de la probabilidad y los grados de libertad.
Tendencia	Te ofrece los valores de la tendencia lineal.
Media.acotada	Arroja la media del interior de un conjunto de datos.
Cuartil.exc	Devuelve el cuartil del conjunto de datos, esto están basados en los valores percentiles de 0 a 1.
JERARQUIA.MEDIA	Devuelve la jerarquía de un número en una lista de datos numéricos.
JERARQUIA.EQV	También devuelve la jerarquía de un número en una lista de números.
COEFICIENTE.R2	Regresa el cuadrado del coeficiente de momento de correlación de producto Pearson.
MINA	Devuelve el valor mínimo de una lista de argumentos, incluyendo números, texto y valores lógicos.
MODA.VARIOS	Regresa la matriz vertical de los valores que se repiten con mucha frecuencia en un rango de datos.
MODA.UNO	Regresa el valor más común de un conjunto de datos.
INV.NORM	Devuelve la función inversa de la distribución normal acumulativa.
DISTR.NORM.N	Devuelve lo que es la distribución normal acumulativa.
NEGBINOM.DIST	Devuelve la distribución binomial negativa.

Errores comunes al usar fórmulas estadísticas ¿Cuáles son y cómo solucionarlos?

Cuando se utiliza el programa de Excel es más que necesario tener que introducir algunas fórmulas para llevar a cabo distintas operaciones matemáticas.

Sin embargo, en muchas ocasiones cuando se introducen terminan generando un error, por lo que puede deberse a muchos motivos. Ya sea porque se ha introducido mal algún signo, un valor o cualquier otro carácter correspondiente de la misma.

De acuerdo con esto, aquí te presentamos los errores más comunes al momento de utilizar las fórmulas estadísticas:

#¡VALOR!

Es uno de los errores más comunes al momento de hacer uso de Excel, el mismo es provocado cuando se suman valores de texto, se ingresa un valor de texto en un parámetro que es un número o cuando se usan fórmulas matriciales sin utilizar la combinación de teclas “Control + Shift + Enter” para ingresar dicha fórmula.

Si este error te está afectando tu trabajo en la hoja de cálculo, la mejor forma de poder corregirlo es que solo sumes los valores numéricos de tu tabla de datos.

#¡DIV/O!

Este tipo de error se puede generar a través de diferentes motivos, ya sea porque se divide un número entre un valor cero, se divide entre una celda vacía o cuando se hace un promedio y existen números por promediar.

La mejor forma de poder solucionar esto es por la regla matemática, ya que no hay que dividir entre cero porque automáticamente se te arrojará un error.

#¿NOMBRE?

Otro de los errores más comunes que se presentan en las funciones de Excel es la de #¿NOMBRE?, el mismo se produce por diferentes motivos, ya sea cuando se usa una función que no existe en una versión del programa o cuando se hace una referencia a un nombre definido que existe en el archivo.

Si se te produce este error, entonces será necesario que revises que las funciones que estés utilizando estén disponibles en tu versión de Excel. La mejor solución de estos problemas es tener la actualización más reciente del programa en tu ordenador o móvil.

#N/A

Si te aparece este aviso entonces se puede deber a que no se encuentra un valor en las funciones BUSCARV, BUSCARH o COINCIDIR. También se genera cuando se realizan operaciones en un rango que tenga un valor N/A. Para solucionar estos inconvenientes es mejor que busques otro nombre que exista en la tabla.

#¡REF!

Este error se produce por lo general cuando se hace referencia a un rango que ya no existe. Es decir, cuando se usa en una fórmula y posteriormente se elimina una fila o columna de donde la fórmula hace referencia.

La forma de solucionar esto es evitando eliminar filas o columnas que sean referencia de alguna otra fórmula que se haya utilizado.

#¡NUM!

Este error suele aparecer cuando se terminan ingresando parámetros en las funciones sin respetar el orden o cuando se tiene un número muy grande. Hay que recordar que Microsoft Excel no permite utilizar números muy grandes.

Esto quiere decir que la forma de corregirlo es evitando los números grandes o modifican e intercambiando los parámetros.

#¡NULO!

Si te ha aparecido este error, entonces debes tener presente que se genera cuando existe un rango incorrecto en la fórmula o cuando no existe intersección entre dos rangos, un ejemplo de esto sería lo siguiente: =SUMA(E3 E4), lo que terminará generando el error.

La solución más viable para esto es que usar las fórmulas correctamente, especialmente al momento de separar las celdas, ya sea con dos puntos, coma o punto y coma.

#####

Aparece cuando el resultado que se genera de alguna fórmula es demasiado grande y no puede mostrase ya que no cabe en la celda. La solución para esto es ampliar el ancho de la columna hasta que te aparezca dicho resultado.