Este tutorial demuestra cómo usar las funciones AVERAGEIF y AVERAGEIFS de Excel en Excel y Google Sheets para promediar datos que cumplen con ciertos criterios.
Descripción general de la función AVERAGEIF
Puede usar la función AVERAGEIF en Excel para contar celdas que contienen un valor específico, contar celdas que son mayores o iguales a un valor, etc.
Para usar la función de hoja de cálculo de Excel AVERAGEIF, seleccione una celda y escriba:
(Observe cómo aparecen las entradas de la fórmula)
Sintaxis y argumentos de la función AVERAGEIF:
= PROMEDIO SI (rango, criterios, [rango_promedio])
distancia - El rango de celdas a contar.
Criterios - Los criterios que controlan qué células deben contarse.
rango promedio - [opcional] Las celdas a promediar. Cuando se omite, se usa rango.
¿Qué es la función AVERAGEIF?
La función AVERAGEIF es una de las funciones más antiguas que se utilizan en las hojas de cálculo. Se utiliza para escanear a través de un rango de celdas que verifican un criterio específico y luego dan el promedio (también conocido como la media matemática) si los valores en un rango que corresponden a esos valores. La función AVERAGEIF original se limitaba a un solo criterio. Después de 2007, se creó la función AVERAGEIFS que permite multitud de criterios. La mayor parte del uso general sigue siendo el mismo entre los dos, pero existen algunas diferencias críticas en la sintaxis que analizaremos a lo largo de este artículo.
Si aún no lo ha hecho, puede revisar gran parte de la estructura similar y ejemplos en el artículo COUNTIFS.
Ejemplo básico
Consideremos esta lista de ventas registradas y queremos saber el ingreso promedio.
Debido a que tuvimos un gasto, el valor negativo, no podemos simplemente hacer un promedio básico. En su lugar, queremos promediar solo los valores que son mayores que 0. El "mayor que 0" es lo que será nuestro criterio en una función AVERAGEIF. Nuestra fórmula para afirmar esto es
= PROMEDIO SI (A2: A7, "> 0")
Ejemplo de dos columnas
Si bien la función PROMEDIO original se diseñó para permitirle aplicar un criterio al rango de números que desea sumar, la mayor parte del tiempo necesitará aplicar uno o más criterios a otras columnas. Consideremos esta tabla:
Ahora, si usamos la función AVERAGEIF original para averiguar cuántos plátanos tenemos en promedio. Pondremos nuestros criterios en la celda D1 y tendremos que dar el rango que queremos promedio como último argumento, por lo que nuestra fórmula sería
= PROMEDIO SI (A2: A7, D1, B2: B7)
Sin embargo, cuando los programadores finalmente se dieron cuenta de que los usuarios querían dar más de un criterio, se creó la función AVERAGEIFS. Para crear una estructura que funcione para cualquier número de criterios, AVERAGEIFS requiere que el rango de suma se enumere primero. En nuestro ejemplo, esto significa que la fórmula debe ser
= PROMEDIO SI (B2: B7, A2: A7, D1)
NOTA: Estas dos fórmulas obtienen el mismo resultado y pueden verse similares, así que preste mucha atención a la función que se está utilizando para asegurarse de enumerar todos los argumentos en el orden correcto.
Trabajar con fechas, varios criterios
Al trabajar con fechas en una hoja de cálculo, si bien es posible ingresar la fecha directamente en la fórmula, es una buena práctica tener la fecha en una celda para que pueda hacer referencia a la celda en una fórmula. Por ejemplo, esto ayuda a la computadora a saber que desea usar la fecha 27/05/2020, y no el número 5 dividido por 27 dividido por 2022.
Veamos nuestra siguiente tabla que registra la cantidad de visitantes a un sitio cada dos semanas.
Podemos especificar los puntos de inicio y finalización del rango que queremos ver en D2 y E2. Entonces, nuestra fórmula para encontrar el promedio del número de visitantes en este rango podría ser:
= PROMEDIO SI (B2: B7, A2: A7, "> =" & D2, A2: A7, "<=" & E2)
Observe cómo pudimos concatenar las comparaciones de "=" con las referencias de celda para crear los criterios. Además, aunque ambos criterios se aplicaban al mismo rango de celdas (A2: A7), debe escribir el rango dos veces, una por cada criterio.
Varias columnas
Cuando utilice varios criterios, puede aplicarlos al mismo rango que hicimos con el ejemplo anterior, o puede aplicarlos a diferentes rangos. Combinemos nuestros datos de muestra en esta tabla:
Hemos configurado algunas celdas para que el usuario ingrese lo que desea buscar en las celdas E2 a G2. Por tanto, necesitamos una fórmula que sume el número total de manzanas recogidas en febrero. Nuestra fórmula se ve así:
= PROMEDIO SI (C2: C7, B2: B7, "> =" & F2, B2: B7, "<=" & G2, A2: A7, E2)
AVERAGEIFS con lógica de tipo OR
Hasta este momento, todos los ejemplos que hemos utilizado han sido comparaciones basadas en AND, en las que buscamos filas que cumplan con todos nuestros criterios. Ahora, consideraremos el caso en el que desea buscar la posibilidad de que una fila cumpla con uno u otro criterio.
Veamos esta lista de ventas:
Nos gustaría sumar las ventas promedio de Adam y Bob. Primero, una breve discusión sobre cómo tomar promedios. Si tiene un número impar de cosas, como 3 entradas para Adam y 2 para Bob, no puede simplemente tomar el promedio de las ventas de cada persona. Esto se conoce como tomar el promedio de promedios y termina dando una ponderación injusta al elemento que tiene pocas entradas. Si este es el caso de sus datos, deberá calcular un promedio de la forma "manual": tome la suma de todos sus artículos dividida por el recuento de sus artículos. Para revisar cómo hacer esto, puede consultar los artículos aquí:
Ahora, si el número de entradas es el mismo, como en nuestra tabla, entonces tiene un par de opciones que puede hacer. La más simple es sumar dos AVERAGEIFS juntos, así, y luego dividir por 2 (el número de elementos en nuestra lista)
= (PROMEDIO SI (B2: B7, A2: A7, "Adam") + PROMEDIO SI (B2: B7, A2: A7, "Bob")) / 2
Aquí, hicimos que la computadora calcule nuestros puntajes individuales y luego los sumamos.
Nuestra siguiente opción es buena para cuando tenga más rangos de criterios, de modo que no desee tener que volver a escribir toda la fórmula repetidamente. En la fórmula anterior, le dijimos manualmente a la computadora que agregue dos AVERAGEIFS diferentes juntos. Sin embargo, también puede hacer esto escribiendo sus criterios dentro de una matriz, como esta:
= PROMEDIO (PROMEDIO SI (B2: B7, A2: A7, {"Adam", "Bob"}))
Mire cómo se construye la matriz dentro de las llaves. Cuando la computadora evalúe esta fórmula, sabrá que queremos calcular una función AVERAGEIFS para cada elemento de nuestra matriz, creando así una matriz de números. La función PROMEDIO exterior tomará esa matriz de números y la convertirá en un solo número. Pasando por la evaluación de la fórmula, se vería así:
= PROMEDIO (PROMEDIO SI (B2: B7, A2: A7, {"Adam", "Bob"})) = PROMEDIO (13701, 21735) = 17718
Obtenemos el mismo resultado, pero pudimos escribir la fórmula de forma un poco más sucinta.
Lidiando con espacios en blanco
A veces, su conjunto de datos tendrá celdas en blanco que debe buscar o evitar. Establecer los criterios para estos puede ser un poco complicado, así que veamos otro ejemplo.
Tenga en cuenta que la celda A3 está realmente en blanco, mientras que la celda A5 tiene una fórmula que devuelve una cadena de longitud cero de "". Si queremos encontrar el promedio total de verdaderamente celdas en blanco, usaríamos un criterio de "=", y nuestra fórmula se vería así:
= PROMEDIO SI (B2: B7, A2: A7, "=")
Por otro lado, si queremos obtener el promedio de todas las celdas que visualmente se ven en blanco, cambiaremos el criterio para que sea "", y la fórmula se verá así
= PROMEDIO SI (B2: B7, A2: A7, "")
Vamos a darle la vuelta: ¿qué sucede si desea encontrar el promedio de celdas que no están en blanco? Desafortunadamente, el diseño actual no le permitirá evitar la cuerda de longitud cero. Puede utilizar un criterio de "", pero como puede ver en el ejemplo, todavía incluye el valor de la fila 5.
= PROMEDIO SI (B2: B7, A2: A7, "")
Si necesita no contar las celdas que contienen cadenas de longitud cero, debe considerar el uso de la función LEN dentro de un SUMPRODUCT
PROMEDIO SI en Hojas de cálculo de Google
La función AVERAGEIF funciona exactamente igual en Google Sheets que en Excel:
