Saltar al contenido

Usando las matemáticas y las funciones estadísticas en Microsoft Excel

En esta sección examinaremos las siguientes funciones estadísticas: PROMEDIO, PROMEDIO, CONTABILIDAD, CONTABILIDAD, FRECUENCIA y PRONÓSTICO.

PROMEDIO

Como puedes adivinar, el promedio de un rango de valores que coinciden con una condición dada. Entre otras cosas, puede ayudarnos a determinar si nuestro negocio con un cliente dado es rentable o no, mirando el promedio de la cantidad de pedidos de ese cliente. En el C17, usamos esta función para calcular la cantidad media de pedidos hechos por clientes alemanes:

Usando las matemáticas y las funciones estadísticas en Microsoft Excel
Usando las matemáticas y las funciones estadísticas en Microsoft Excel

En el ejemplo anterior, el rango de búsqueda es G2:G831 (País) seguido de la condición (“Alemania” entre comillas dobles) y el rango promedio (D2:D831).

PROMEDIO

A pesar de lo útil que es el PROMEDIO, tendrá que utilizar el PROMEDIO si necesita calcular un promedio basado en dos o más criterios. En este caso, el primer argumento que se pasa a la función es el rango promedio, seguido por dos o más pares de rango de criterios y condiciones. Por ejemplo, para averiguar el promedio de pedidos de clientes de EE.UU. preparados por la empleada Nancy Davolio, utilice estas entradas:

  • Rango promedio: D2:D831
  • Primer rango de criterios: G2:G831
  • Primera condición: “USA”
  • Segundo rango de criterios: H2:831
  • Segunda condición: “Nancy Davolio”

COUNTIF y COUNTIFS

Para contar el número de valores que coinciden con una condición en un rango dado, podemos usar COUNTIF. Los argumentos requeridos son el rango de conteo y el valor de búsqueda, en ese orden. C23 muestra el resultado de contar el número de celdas donde aparece el nombre Margaret Peacock en el rango H2:H831:

Si estamos interesados en añadir otro filtro, como los pedidos no enviados todavía, podemos usar COUNTIFS en su lugar. En ese caso, cada par de rango/condición se añade en orden como argumentos de la función:

Como en el caso de los SUMIFS y los AVERAGEIFS, el COUNTIFS también admite tantas condiciones como sean necesarias, siempre que cada una vaya acompañada de su correspondiente rango de criterios.

FRECUENCIA

Para averiguar con qué frecuencia una serie de valores aparecen dentro de un rango, podemos usar la FRECUENCIA. Esta función requiere dos matrices como entradas: 1) un rango de datos, y 2) una referencia a los puntos de ruptura del intervalo que servirán como grupos para los puntos de datos en el primer arreglo.

Usando la FRECUENCIA, podemos responder a preguntas como “¿Cuántos pedidos hay donde la cantidad es superior a 2500 dólares?” o “¿Cuántos inferiores a 500 dólares?” y así sucesivamente. Al dividir las cantidades de los pedidos por intervalos de 500, como se muestra en la columna F de abajo, obtenemos lo siguiente en G3:G8 después de aplicar =FRECUENCIA(Pedidos!D2:D831, F3:F7) a G3:

Si colocas el cursor en cualquier celda entre G4 y G8, notarás que la fórmula es la misma que en G3 pero aparece en gris, lo que significa que no puede ser editada porque la matriz de salida se originó en G3:

Al principio, puede ser confuso ver que la FRECUENCIA devuelve una matriz cuya longitud es igual al número de puntos de ruptura del intervalo más uno. El último número de la salida de la función representa el número de órdenes donde la cantidad está por encima del último punto de ruptura (2500 en este caso).

PRONÓSTICO

Basándose en una serie conocida de x e y, el PRONÓSTICO devuelve la y esperada para una x numérica dada usando una regresión lineal.

Si inspeccionan los registros de las órdenes , verán que sólo hay unas pocas filas para el mes de mayo de 1998. Para ver cómo es la estimación de las ventas acumuladas de mayo y junio de 1998, insertaremos las siguientes fórmulas en H17 y H18:

  • H17: =FORECAST(G17, H13:H16, G13:G16)
  • H18: =FORECAST(G18, H13:H17, G13:G17)

donde:

  • G17 y G18 son los valores x para los que queremos predecir el resultado en cada caso. Son fechas que cumplen el requisito de que la x dada debe ser numérica, pero han sido formateadas como las primeras letras del mes en cuestión a efectos de visualización.
  • H13:H16 y H13:H17 son los conjuntos conocidos de y$0027s.
  • G13:G16 y G13:G17 son las correspondientes series de x conocidas.

Si dibujamos un gráfico utilizando las ventas acumuladas por mes, veremos una tendencia lineal que se extiende más allá de enero-abril (nuestro conjunto de datos iniciales) hasta mayo y junio (que estimamos anteriormente con el PRONÓSTICO).

Nota: En el Excel 2016 y más reciente, FORECAST ha sido reemplazado por FORECAST.LINEAR. Aunque el primero está disponible para compatibilidad con versiones anteriores y devuelve la misma salida que el segundo, Microsoft recomienda usar FORECAST.LINEAR en las últimas versiones. Esta práctica ayuda a evitar la confusión con las nuevas funciones de FORECAST, como FORECAST.ETS, que utilizan un algoritmo de predicción basado en el aprendizaje automático.