martes, 12 de enero de 2010

Función PROMEDIO.SI.CONJUNTO: un ejemplo.

En un correo un lector realizaba una consulta sobre la función PROMEDIO.SI.CONJUNTO, y la forma de replicarla en versiones anteriores a Excel 2007:

...Me gustaría que me pudierais solucionar una duda que tengo sobre la función promedio.si.conjunto.
En principio he descubierto que dichafunción no es posible para versiones de excel más antiguas al 2007. Por lo que me gustaría saber como puedo programar dicha función para versiones antiguas. Os paso la fórmula en concreto y si ésta se puede programar para versión 2000...


Efectivamente existen algunas nuevas funciones en Excel 2007 que no existen en versiones anteriores, en general, son funciones que nos permiten agrupar campos por distintos criterios; son funciones del estilo CONTAR.SI.CONJUNTO o SUMAR.SI.CONJUNTO.
En esta ocasión hablaremos de la función
=PROMEDIO.SI.CONJUNTO(rango valores; rango criterios1; criterio1; rango criterios2; criterio2; ...)
función que nos devolverá el promedio del rango de valores, es decir, la media aritmética, de todas las celdas que cumplan los múltiples criterios asignados.
Plantearé la siguiente tabla de datos a modo de ejemplo:


En esta tabla tenemos los datos de distintos hoteles de una localidad, definida de acuerdo a la Cadena de hoteles al que pertenece cada uno de ellos y la categoría por estrellas, viendo cuántas habitaciones tiene cada hotel.

Nos interesa conocer cuál es el promedio de los hoteles de la cadena Alfa; así como el promedio de los hoteles Alfa de 3 estrellas.
Para desarrollar esta tarea propondré tres posibilidades:
  • Función PROMEDIO.SI.CONJUNTO:
    Sobre la tabla de datos de la imagen anterior ejecutamos la siguiente función


    donde obtenemos un resultado de 158,75 es decir, la media aritmética de las habitaciones de los hoteles 'Alfa' es de 158,75. De igual forma, pero agregando a la función un nuevo criterio obtendríamos el promedio de los hoteles 'Alfa' de 3 estrellas:
    =PROMEDIO.SI.CONJUNTO($C$2:$C$13;$A$2:$A$13;"Alfa";$B$2:$B$13;"3x")
    esto es, una media de 205 habitaciones. Es decir, de los elementos o registros a estudio calculamos el promedio sólo de aquellos que cumplen ambos criterios a un tiempo; i.e., es un promedio condicional múltiple
  • Con Tabla dinámica:
    Ya hemos trabajado en ocasiones anteriores con tablas dinámicas, y sabemos que generando una tabla dinámica podremos llegar a los mismos resultados del punto anterior de manera sencilla. Construimos nuestra tabla dinámica con la siguiente estructura:


    es decir, en el área de columnas disponemos el campo 'Cadena-hotel', en el área de datos el campo 'Habitaciones' resumido por promedio y en el área de páginas el campo 'Categoría'; ajustamos los elementos para que sólo nos muestre los hoteles 'Alfa' de categoría '3x' o de todas las categorías.

  • Empleando funciones Matriciales:
    especialmente útiles cuando no dispongamos de la versión Excel 2007.
    Para calcular el promedio de habitaciones de los hoteles 'Alfa' escribiremos y ejecutaremos en forma matricial:
    {=PROMEDIO(SI($A$2:$A$13="Alfa";$C$2:$C$13))}
    igualmente, para obtener el segundo promedio, ampliaremos esta misma hasta
    {=PROMEDIO(SI($A$2:$A$13="Alfa";SI($B$2:$B$13="3x";$C$2:$C$13)))}

51 comentarios:

  1. por que no hacen una funcion asi pero la intentan hacer entre hojas para ver como se hace?

    ResponderEliminar
  2. Hola, buenas tardes,
    la verdad es que no habría mucha diferencia en la forma de trabajar entre distintas hojas con estas funciones matriciales.
    Por ejemplo, si estuviera trabajando en la 'Hoja2' con los datos a resumir en la 'Hoja1', la fórmula matricial quedaría:
    {=PROMEDIO(SI(Hoja1!A2:A13="Alfa";Hoja1!C2:C13))}

    Si te queda alguna duda o te surge algún problema concreto no dudes en contactar conmigo:
    excelforo@gmail.com

    Saludos

    ResponderEliminar
  3. y entre un archivo y otro con mas de dos condicionantes???

    ResponderEliminar
  4. Hola, buenas tardes.
    no me queda claro sobre qué preguntas, si es sobre el tema de este post, la respuesta no es diferente a la que dí en el comentario anterior, no existe diferencia por que sean dos libros diferentes, en todo caso, quizá se ralenticen algo los cálculos.
    Un cordial saludo

    ResponderEliminar
  5. buenos dias...

    me podran ayudar al sacar promedio me da este error, me gustaria que cuando las cantidades sean iguales 00:00 00:00 no apareciera el error si no un guion o en blanco como podre hacerle??
    inicio salida diferencia
    08:00 a.m. 08:00 a.m. 00:00

    promedio #¡DIV/0!

    ResponderEliminar
  6. Hola,
    para corregir el error prueba anidando la función promedio a SI.ERROR(valor; valor si error).
    Algo así:
    =SI.ERROR(PROMEDIO(rango);"_")

    Espero te sirva.
    Slds

    ResponderEliminar
  7. HOLA
    buen dia me sirvio de mucho con eso pude arreglar lo del error y agregue otra condicion para que no me promediera las celdas que contiene 0 o vacias, comparto al formula.
    ENTRADA SALIDA TOTAL
    8:00 8:00 00:00

    resultado arroja - con esa formula ya no me marca el erro de #¡DIV/0! y no me promedia las celdas vacias ni los que contegan 0

    =SI.ERROR(PROMEDIO.SI.CONJUNTO(G332:G347,G332:G347,"<>0"),"-")

    gracias por la ayuda
    saludos

    ResponderEliminar
  8. buenas tardes...
    ojala y me puedan ayudar
    quiero calcular cuantos meses hay entre dos fechas diferentes ejemplo
    01/05/2009 05/04/2011
    calculando manualmente son 23 meses .que formula utilizar que en vez de 05/04/2011 fuera 18/04/2011 que cuando los dias del 01 al 17 le reste un dia, y que del 18 al 30 o 31 no le descuente un dia.

    un coordial saludo

    ResponderEliminar
  9. Hola!!
    entiendo que son dos preguntas las que haces.
    Para calcular cuantos meses completos han transcurrido entre dos fechas puedes usar la función SIFECHA.Suponiendo tus fechas en A1 y B1, quedaría:
    =SIFECHA(A1;B1;"M")

    En la segunda cuestión que planteas, creo que sigues necesitando el cálculo de meses entre fechas, condicionado al día del mes, por lo que quedaría una fórmula así:
    =SIFECHA(A2;SI(Y(DIA(B2)>1;DIA(B2)<=17);B2-1;B2);"M")

    Espero haber entendido correctamente tu cuestión.
    Slds

    ResponderEliminar
  10. las dos formulas me dan el mismo resultado hay un detalle la formula empieza a contar desde el primer mes, si puedes apoyarme como hacerle que empieze a contar el siguiente mes, ejemplo:
    01-04-07 18/04/2011 aca esta bien son 48 meses
    01-08-07 17/05/2011 aca debe ser 44 meses y con la formula que me pasastes me da 45 meses
    01-10-08 05/06/2011 aca debe ser 31 meses y con la formula me da 32 meses
    01-05-09 05/04/2011 al igual resultado correcto 22 meses y con la formula me da un mes mas 23.
    necesito que empiece a contar el primer mes el siguinete mes que viene.
    ejemplo: si estamos en septiembre que empiece a contar el primer mes en octubre, y respetando el mismo rango del 01 al 17 que descuente 1 mes y del 18 al 31 no reste nada quede igual.
    espero haberme explicado.
    es para hacer calculos del impuesto nacional de precio al consumidor

    saludos

    ResponderEliminar
  11. Hola de nuevo,
    sinceramente no veo la lógica y el sentido de lo que planteas... me explico, para poder construir una fórmula es necesario conocer la norma de cálculo, y atendiendo a tus requerimientos, no veo posible cómo del 01/04/07 al 18/04/11 pasen 48 meses, y del 01/08/07 al 17/05/11 puedan ser 44 ???
    Seguro se me escapa algo, pero...
    Recuerda que la fórmula cuenta meses completos trancurridos.
    Revisa las explicaciones anteriores, y si te quedan dudas envíame un mail a:
    excelforo@gmail.com

    Un saludo

    ResponderEliminar
  12. podrian ayudar con alguna formula para hallar el promedio de 4 notas pero no sumar la nota menor, ejemplo

    14
    16
    12
    08
    Por favor alguna formula para hallar lo que les menciono lineas arriba, espero sus respuestas, gracias

    ResponderEliminar
  13. Hola,
    subiré un post explicándolo, pero lo más sencillo sería aplicar la función PROMEDIO.SI
    Si suponemos tu listado de notas en A1:A4, podríamos construir la siguiente fórmula en B1 (por ejemplo):
    =PROMEDIO.SI(A1:A4;"<>"&K.ESIMO.MENOR(A1:A4;1))
    Hay alguna forma más... pero ya la cuento en el post.
    Slds

    ResponderEliminar
  14. hola y como haría para calcular el promedio de acuerdo a una categoría ejemplo promedio de los hombres de un salón y promedio de todas las mujeres ...

    ResponderEliminar
    Respuestas
    1. Hola Edith,
      bueno, más o menos es el mismo ejemplo explicado en esta entrada del blog...
      No se cómo es tu origen de datos, pero supongo tendrás una columna o campo con el Sexo (Hombre - Mujer) y al menos otro campo más con valores de algún tipo. Así que te sugiero dos alternativas.
      Una es crear una tabla dinámica sobre el origen, llevar el campo Sexo al área de filas y el otro campo al área de datos, resumido por PROMEDIO, con lo que tendrías de un plumazo el promedio de ambas categorías.
      La alternativa dos sería emplear la función PROMEDIO.SI, por ejemplo, para obtener el promedio de mujeres escribirías:
      =PROMEDIO.SI(campo_sexo;"Mujer";campo_valor)
      Slds

      Eliminar
  15. Mi duda es la siguiente,
    quiero hacer la media aritmética de una serie de valores pero solo de los valores que están en un rango.
    Me explico mejor, he obtenido la frecuencia de los valores en el intervalo y ahora quiero el promedio de esos valores.
    =PROMEDIO.SI(Tabla6[T,Min];FRECUENCIA(Tabla6[T,Min];B64:B85))
    En la primera fila como es 0 me da un error, lo entiendo
    En la segunda obtengo un 4, no hace el promedio
    Luego todo errores


    Temperatura minima
    Intervalo de Temperatura Frecuencia Tmin Media
    0 1,99 0 #¡DIV/0!
    2 3,99 4 4
    4 5,99 29 #¡DIV/0!
    6 7,99 116 #¡DIV/0!
    8 9,99 296 #¡DIV/0!
    10 11,99 626 #¡DIV/0!
    12 13,99 813 #¡DIV/0!
    14 15,99 797 #¡DIV/0!
    16 17,99 509 #¡DIV/0!
    18 19,99 178 #¡DIV/0!
    20 21,99 27 #¡DIV/0!
    22 23,99 8 8
    24 25,99 1 #¡DIV/0!
    26 27,99 0 #¡DIV/0!

    ResponderEliminar
    Respuestas
    1. Hola popez84,
      mejor envíame el ejemplo a
      excelforo@gmail.com
      detellando, si es posible, algo más del ejemplo.
      Slds

      Eliminar
  16. ¡Saludos!

    Planteo una duda:

    ¿Cómo calcularías, en este caso, el promedio del número de habitaciones de la cadena "Alpha", ya sean de categoría "4x" o "5x"?

    Es decir. que en la columna B (Categoría), pudiese tener dos valores diferentes simultáneamente.

    Tengo que aplicarlo a una tabla similar y me estoy volviendo loco.

    ¡Gracias de antemano!

    ResponderEliminar
    Respuestas
    1. Hola!!
      si lo he entendido correctamente tendrías que aplicar (sobre el ejemplo del post) una 'reconstrucción' de un promedio:
      por una parte
      =SUMAR.SI.CONJUNTO(C1:C5;A1:A5;"Alfa";B1:B5;"5x")+SUMAR.SI.CONJUNTO(C1:C5;A1:A5;"Alfa";B1:B5;"5x")
      y por otra
      =CONTAR.SI.CONJUNTO(A1:A5;"Alfa";B1:B5;"4x")+CONTAR.SI.CONJUNTO(A1:A5;"Alfa";B1:B5;"5x")

      para terminar dividiendo uno por otro (o bien todo en la misma fórmula).

      O bien directamente con la tabla dinámica, llevando los campos a las mismas áreas que en el ejemplo, pero seleccionando las categorías 4x y 5x y resumiendo valores por 'promedio'.

      Espero te sirva.
      Slds

      Eliminar
    2. ¡Gracias!

      Me sirve, sí.
      Yo estaba comiéndome la cabeza con la fórmula del promedio en lugar de ir a su definición más básica.

      Eliminar
    3. Perfecto...
      es cierto que en ocasiones nos obcecamos y no vemos el bosque por concentrarnos en el árbol.
      Un cordial saludo

      Eliminar
  17. Hola!
    espero que puedas ayudarme

    Necesito obtener el promedio de cada dia

    en mi data tengo de un dia 03/11/2012 aproximadamente 144 datos y pasa el siguiente dia 04/11/2012 tambien con 144 datos, y asi por 3 meses.

    Y lo que necesito es el promedio de esos 144 datos de cada dia.

    Para obtener del dia 03/11/2012 un dato
    y del siguiente dia 04/11/2012 otro dato
    y asi sucesivamente

    Ojala me ayudes!

    ResponderEliminar
    Respuestas
    1. Hola!
      bueno, creo que lo más sencillo sería aplicar una tabla dinámica sobre el rango que tienes; llevando al área de filas los días, y al área de valores los importes; luego será suficiente Resumir por 'Promedio'... dará igual que tengas 144 que 10.000 registros por cada fecha, la TD siempre te calculará el promedio.
      Espero te sirva.
      Slds

      Eliminar
  18. COMO PUEDO SACER EL RESULTADO DE 158,75 TENGO QUE PASARME A OTRA HOJA O COMO ES QUE NO ENTIENDO

    ResponderEliminar
    Respuestas
    1. Hola, que tal estás?
      Un plaver igualmente saludarte, me alegro te encuentres bien.

      Como puedes ver en la explicación el resultado de 158,75 se llega tras aplicar la fórmula:
      =PROMEDIO.SI.CONJUNTO($C$2:$C$13;$A$2:$A$13;"Alfa";$B$2:$B$13;"3x")

      en este caso trabajando sobre la misma hoja, pero no habria ningún problema en hacerlo en hojas diferentes o incluso libros distintos.

      Un cordial saludo

      Eliminar
    2. HOLA PERO QUE NO ES ESA LA FORMULA(=PROMEDIO.SI.CONJUNTO($C$2:$C$13;$A$2:$A$13;"Alfa";$B$2:$B$13;"3x")
      esto es, una media de 205 habitaciones.) DE LA MEDIA QUE SACA EL 205 SOLO QUIERO SABER COMO SACAR EL 158,75. ES QUE YA LO INTENTE CON MUCHOS PROCEDIMIENTOS Y NO ME SALE, LA VERDAD.

      Eliminar
    3. Hola,
      discúlpame, pero no entiendo la pregunta.
      La función PROMEDIO.SI.CONJUNTO obtiene la media aritmética sólo de aquellos registros que cumplan las condiciones dadas... es decir, si tienes un total de 205 registros, pero sólo 4 cumplen las condiciones, sóloo sobre ellos aplica el promedio (sumándo sólo para esos 4 y diviendo por 4).
      Slds
      P.D.: por favor, no escribas en mayúsculas... es como si gritaras.

      Eliminar
  19. necesito ayuda en sacar promedio de 5 celdas pero sin tomar encuenta las celdas con valor cero, A1=2, C=2, E=0, G=0, I=2,,,, en una calculadora saldria como resultado 6 dividido entre 3 que son los que tienen datos superior a cero el resultado tendria que ser 2

    ResponderEliminar
    Respuestas
    1. Hola que tal,
      Una manera de aplicar promedios condicionados es emplear PROMEDIO.SI, por ejemplo
      =PROMEDIO.SI(A1:G1;"<>0")
      que devuelve el promedio de aquellas celdas distintas de cero.

      Slds

      Eliminar
  20. Necesito ayuda. SI el promedio entre seis notas valoradas de 1 a 100 es un número entre 91 y 100 vale 1 punto, si es entre 71 y 90 vale 0,75 puntos, si es entre 61 y 70 vale 0,50, si es entre 51 y 60 vale 0,25 puntos. Gracias

    ResponderEliminar
    Respuestas
    1. Hola, que tal estás...
      =SI(PROMEDIO(A1:F1)>=91;1;SI(Y(PROMEDIO(A1:F1)>=71;PROMEDIO(A1:F1)<91);0,75;SI(Y(PROMEDIO(A1:F1)>=61;PROMEDIO(A1:F1)<71);0,5;SI(Y(PROMEDIO(A1:F1)>=51;PROMEDIO(A1:F1)<61);0,25;"resto"))))

      Espero te sirva.
      Slds cordiales

      Eliminar
    2. Con una pequeña modificación, porque el enunciado no estaba completo, me sirvió perfectamente. Muchas gracias. Saludos

      Eliminar
    3. ;-)
      me alegro te sirviera... al menos la idea.
      Un cordial saludo

      Eliminar
  21. Hola

    Mi duda es bastante sencilla. Podrían decirme qué error hay en la siguiente fórmula.
    =PROMEDIO.SI(Enero!C4,Febrero!C4,Marzo!C4, Abril!C4, Mayo!C4, Junio!C4, Julio!C4,Agosto!C4,Septiembre!C4,Octubre!C4,Noviembre!C4,Diciembre!C4;">0")
    Trato solo de calcular el promedio de celdas de diferentes Hojas, sin que se tengan en cuentan aquellas que sean iguales o menores de 0.
    Muchísimas gracias.

    ResponderEliminar
    Respuestas
    1. Hola,
      pues no es nada sencilla. El error es que esta función PROMEDIO.SI no admite trabajar en diferentes hojas, es decir, no es una fórmula 3D
      Lee esta entrada
      http://excelforo.blogspot.com.es/2012/02/formulas-3d-tridimensionales-en-excel.html

      la solución será programar una función personalizada en VBA, puedes ver como, para este caso en concreto en
      http://excelforo.blogspot.com.es/2014/01/vba-una-funcion-personalizada-de.html

      Saludos

      Eliminar
  22. Hola buenas tardes, podria ayudarme con una formula, tengo que promediar seis sifras, 5, 9,6,0,8,2, pero que las que son menores que cinco las promedie como si fueran cinco.
    Gracias

    ResponderEliminar
    Respuestas
    1. Hola Alde,
      suponiendo en C4:H4 ejecutamos matricialmente la siguiente fórmula:
      =PROMEDIO(SI(C4:H4<5;5;C4:H4))

      recuerda presionar Ctrl+Mayusc+Enter en lugar de Enter.
      Saludos

      Eliminar
    2. Hola buenos dias, ya intente la formula y hasta con los mismos rangos que tiene como muestra y la verdad es que me marca error, igual presione ctrl+mayusc+enter.
      Espero su ayuda porfavor, saludos y gracias.

      Eliminar
    3. ??
      presionaste al tiempo Ctrl+Mayusc (NO BLOQUEO MAYUSCULA)+Enter??

      Eliminar
    4. Muchas gracias, es una maravilla todo esto.

      Eliminar
  23. Hola buenas tardes, yo de nuevo, tengo otra pequeña duda, de una relacion de alumnos de diferentes carreras y sus seis calificaciones, quisiera tener la cantidad de alumnos que reprobaron determinada materia, pero que al filtrarlos por especialidad me de la cantidad nadamas de determinada especialidad.
    Saluditos.

    ResponderEliminar
    Respuestas
    1. Hola,
      puedes aplicar la función CONTAR.SI.CONJUNTO con condiciones de notas y especialidad
      Saludos!

      Eliminar
  24. Hola!
    Fue de mucha ayuda su información, muchas gracias.

    ResponderEliminar
  25. Hola. existe alguna forma de realizar el promedio.si.conjunto de forma dinámica, es decir: en las celdas de la A1:A7 tengo los siguientes valores 1, 5, 8, 4, 10, 9, 3. el promedio a calcular esta en el rango (2-8) para la primer fila. en la segunda, tercera.., enésima fila los cambian entre distintos rangos, es posible que la formula sea dinámica y calcule el rango amarrado a el contenido de la celda que esta formulada.

    ResponderEliminar
    Respuestas
    1. Hola Edwin,
      existen algunas formas de construir rangos dinámicos.
      La más habitual es emplear la función DESREF...
      también la función DIRECCION e INDIRECTO... e incluso a veces la función INDICE.

      Lo más importante es saber cuál debe ser el rango para trabajar.
      El tu exposición no me queda claro cómo debe variar el rango.. y se hace complicado construirlo
      :(
      si puedes ser algo más claro, lo intentamos.
      Un cordial saludo

      Eliminar
  26. Diferencias entre la función promedio.si y promedio.si.conjunto
    QUIEN ME AYUDA

    ResponderEliminar
    Respuestas
    1. Hola Erick,
      PROMEDIO.SI calcula la media aritmética de los valores que cumplan UNA condición dentro del rango.
      PROMEDIO.SI.CONJUNTO calcula la media aritmética de los valores que cumplan VARIAS condiciones al tiempo dentro del rango.

      Obviamente las condiciones se indican como argumentos en estas funciones.
      Saludos

      Eliminar
  27. hola.
    Espero puedan ayudarme a despejar esta duda.
    ¿como hago para que la siguiente formula no me promedie los valores 0 ni espacios vacíos?
    =PROMEDIO.SI.CONJUNTO(Tabla1[Q/hr];Tabla1[lote];Tabla2[[#Esta fila];[lote]])
    los valores a promediar son Tabla1[Q/hr]

    ResponderEliminar

Nota: solo los miembros de este blog pueden publicar comentarios.