lunes, julio 16, 2012

La función SUBTOTALES con criterios.

Como ya sabemos, la función SUBTOTALES nos permite realizar cálculos con once operaciones distintas sin tomar en cuenta celdas ocultas. Esto es muy práctico cuando usamos Autofiltro



El resultado de la fórmula refleja sólo los valores de las filas visibles. Un lector me pregunta si se puede hacer el cálculo de tal manera que SUBTOTALES sólo tome en cuenta los valores positivos (o negativos, o distintos de cero, es decir, aplicando algún criterio al cálculo).

Una forma de hacerlo sería agregando una columna auxiliar y luego aplicar el filtro a la columna del criterio y a la columna auxiliar. Por ejemplo



También se puede hacer con fórmulas, que es lo que vamos a mostrar en esta nota. La ventaja de hacerlo con fórmulas es que podemos mostrar distintos resultados, por ejemplo el total de los montos positivos y el total de los negativos, en una misma vista de la hoja.

Supongamos que queremos ver el total de los montos positivos en la celda B1 y el total de los negativos en D1



La celda B1 contiene la fórmula

=SUMAPRODUCTO(SUBTOTALES(9,DESREF(B4,FILA($B$4:$B$14)-FILA($B$4),,1))*($B$4:$B$14>0))


La celda D1 contiene la fórmula

=SUMAPRODUCTO(SUBTOTALES(9,DESREF(B4,FILA($B$4:$B$14)-FILA($B$4),,1))*($B$4:$B$14<0))

Al filtrar por el criterio “a”, por ejemplo, veremos

subtcrit04

Analicemos la fórmula:

DESREF(B4,FILA($B$4:$B$14)-FILA($B$4),,1) crea una matriz que contiene los valores del rango B4:B14



La expresión ($B$4:$B$14<0) crea una martiz de valores FALSO o VERDADERO



Al multiplicar ambas expresiones entre sí obtenemos un matriz donde los valores negativos han sido remplazados por ceros



Usamos estas expresiones como argumentos de la función SUBTOTALES con el operador 9 (suma).

La función SUMAPRODUCTO opera en forma matricial, por lo que nos permite realizar todas estas operaciones en una única celda.

Para calcular el subtotal de los valores negativos usamos ($B$4:$B$14<0); si quisiéramos hacer cálculos que no incluya ceros usaríamos ($B$4:$B$14<>0).

47 comentarios:

  1. Buenos días. Excelente Sr. Jorge, sencillamente lo que necesitaba. Es un usted muy amable en compartir sus conocimientos y ayudarnos en nuestras inquietudes. Mil y mil gracias.

    Saludos

    Juan R Garces.

    ResponderEliminar
  2. Muy interesante. Gracias por sus aportaciones.

    ResponderEliminar
  3. Excelente aporte Jorge. Mi pregunta es: ¿Se podría hacer con un sumar.si y posteriormente añadir el autofiltro?

    ResponderEliminar
  4. Tal vez se podría usar SUMAR.SI en lugar de SUMAPRODUCTO pero no puedes prescindir de SUBTOTALES para sumar sólo celdas visibles.

    ResponderEliminar
  5. Nota super interesante. La he aplicado y me está ayudando a resolver un problema, pero en mi caso tengo una pequeña diferencia con tu ejemplo.
    Uso la función SUBTOTALES en la columna E el valor al que aplico la condición está en la columna C.

    En concreto sumo todos los importes de E y les resto todos los costes de E. La columna C me dicen si son costes o ingresos.

    Mi fórmula es
    =SUMAPRODUCTO(SUBTOTALES(9;DESREF(E$2;FILA($E$2:$E19)-FILA($E$2);;1))*(C$2:C19="Ingresos"))-SUMAPRODUCTO(SUBTOTALES(9;DESREF(E$2;FILA($E$2:$E19)-FILA($E$2);;1))*(C$2:C19<>"Ingresos"))


    El problema que tengo es que, si filtro por la columna C, todo funciona bien, pero si filtro por la columna B, por ejemplo, desaparece la fila de los subtotales....

    Ves algo raro en la fórmula. Muchas gracias

    ResponderEliminar
  6. Hola,
    si la fila de subtotales está por encima de la tabla, como en mi ejemplo, no veo como puede desaparecer. Te sugiero que me envíes tu cuaderno, o un ejemplo con el mismo problema para quepueda hacerme una idea más precisa del problema. La idrección del mail e instrucciones puedes ver en el enlace Ayuda en la parte superior del blog.

    ResponderEliminar
  7. Hola Jorge, bueno, yo el subtotal no lo pongo por encima de la tabla, eso no lo hice igual. Lo pongo bajo las columnas que me interesa sumar...
    Te envié un Excel con mi ejemplo.
    Muchas gracias, María

    ResponderEliminar
  8. Hay un problema con los subtotales. Funciona bien con los filtros o autofiltros, pero no cuando escondo una fila manualmente. ¿Hay alguna forma de que funcione? ¿Existe otra formula para eso?
    Gracias de antemano por tu respuesta
    Saludos

    ResponderEliminar
  9. Roger, la función SUTOTALES tiene dos grupos de argumentos para definir la operación a usar: de 1 a 11, para el so con Autofiltro y de 101 a 111 para filas ocultadas manualmente.

    ResponderEliminar
  10. Buenas tardes:

    He revisado variadas alternativas, visitado algunos foros donde explican el uso de DESREF de manera somera y otras opciones, ninguno me funcionó al 100, explico.
    La tarea es tener un conjunto de datos para graficar que muestren el acumulado de facturación en el tiempo pero filtrando por proveedor. Una función SUBTOTALES.SI.CONJUNTO hubiera sido ideal, pero no existe.
    Por lo tanto, la excelente explicación, el uso de recursos gráficos y su conocimiento me han ayudado a tener esta solución, que es la más óptima.
    Sufro un poco por la velocidad de procesamiento ya que es una gran base de datos, pero eso ya es lo de menos. Generalmente no escribo en foros, pero esta vez lo hice con el fin de decir GRACIAS.

    ResponderEliminar
  11. Antonio, gracias por los conceptos.
    Tengo la impresión que hay otras soluciones más eficientes a lo que querés hacer. Estásinvitado a contactarme en forma privado (fijate en el enlace Ayuda, en la parte superior del blog).

    ResponderEliminar
  12. Hola, buenos días, mi problema es que pongo la formula de subtotales y suma todo lo que tiene que sumar, pero cuando filtro por nombre de persona, que esta en otra columna que las cifras, me desaparece la casilla de subtotales. Me podeis ayudar? =SUBTOTALES(9;O3:O18)
    Me explico, tengo dos columnas, una con los nombre y otra con las cifras, pongo la formula y suma bien, pero a la que filtro por un nombre, la celda con la suma total o subtotal no aparece

    ResponderEliminar
  13. Cuando aplicas Autofiltro, Excel oculta todas las filas de que no cumplen con la condición elegida, no sólo las de la columna en cuestión. Por ese motivo conviene poner los totales calculado con SUBTOTAL en alguna fila por encima de la tabla a filtras o, mejor aún, usar Tablas.

    ResponderEliminar
  14. Me sirvió mucho la explicación para lo que necesitaba. Cambie la formula agregando valores de texto y utilice la función CONTARA en subtotales.

    Muchas gracias

    ResponderEliminar
  15. Me quito el sombrero.

    ResponderEliminar
  16. ¿porqué la formula no funciona cuando en subtotales se pone el 1 para calcular el promedio en lugar del 9 para calcular la suma? He probado el propio ejemplo y al poner el número de función 1, aparece #¡div0!

    ResponderEliminar
  17. Guillermo, el código de cada operación fue establecido por los programandores de Microsoft. No creo que el número de operación en si mismo tenga algún significado, aunque intituivamente atribuinos 1 a la suma por ser la operación mpas frecuente.
    El número de función 1 es PROMEDIO; el promedio es una división y si el divisr el 0, entonces el resultado será #¡DIV0!.

    ResponderEliminar
  18. Buenas tardes. Les agradecería mucho si pueden ayudarme:
    Tengo un filtro con rentabilidades con este formato (sin porcentaje)
    3
    -3
    5
    8
    7
    ...
    y quiero aplicar interés compuesto cuando hago filtros. Estoy intentándolo con VF.PLAN combinandolo con SUBTOTALES Y PRODUCTO, incluso introduciendo fórmulas matriciales y no puedo, da error.
    ¿Pueden ayudarme por favor?

    Debería funcionar así en forma matricial pero da error:

    SUBTOTALES(106; (B5:B100)/100+1)

    o VF.PLAN(1;B5:B100) pero en este caso solo debería de calcularse con las celdas que no ha ocultado el filtro.
    Incluso: {PRODUCTO((B5:B100)/100+1)} pero igual que en el caso anterior, no puedo quitar las celdas ocultas

    ResponderEliminar
  19. Jesús, fijate en las instrucciones que aparecen en el enlace Ayuda (en la parte superior del blog).

    ResponderEliminar
  20. Hola, tengo una tabla con una columna A de fechas (un año) y otra columna B con números aleatorios del 0 al 99. Necesito que al poner el filtro en la columna de fechas (ejemplo ultima semana, o últimos 3 meses) poder utilizar la fórmula CONTAR.SI para contar la solo las celdas visibles después de aplicar el filtro que cumplan con un criterio de la columna B (Ejemplo cantidad de veces que aparece el número 10 en las celdas visibles). Entiendo que la función subtotales omite valores ocultos pero esta solo me permite utilizarla con CONTAR, y no con CONTAR.SI... AYUDENME POR FAVOR!

    ResponderEliminar
  21. Flores, no queda claro. Si estás filtrando de acuerdo a un valor (por ejemplo, 10), la cuenta de las filas visibles te da el número de ocurrencias de ese valor. No hace falta CONTAR.SI.
    Podés usar CONTAR.SI para contar las ocurrencias de un valor sin necesidad de filtrar.

    ResponderEliminar
  22. Hola buenas tarde, mi pregunta es tengo una hoja de calculo donde manejo USD y MXN por lo que puse en mi columna tres filas para valores de dolares, pesos y la sumatoria, utilizando esta formula para pesos y dolares es =SUMAR.SI(G12:$G$185,$F$6,$H$12:$H$185) donde ,$F$6, es USD o MXN, pero no se como utilizar subtotal para que solo sume pesos o dolares cuando se necesite, por su atención muchas gracias

    ResponderEliminar
  23. De acuerdo a tu definición, la fórmula debe funcionar bien y dar el resultado en la celda donde la haya introducido.

    ResponderEliminar
  24. yo tengo dos columnas, una con el importe y otra con el tipo de moneda y hasta abajo tengo tres celdas con esta formula:
    =SUMAR.SI(Q$10:Q$317,"MXN",P$10:P$317)
    =SUMAR.SI(Q$10:Q$317,"USD",P$10:P$317)
    =SUMAR.SI(Q$10:Q$317,"EUROS",P$10:P$317)

    mi problema es cuando quiero filtrar por proveedor. me sigue sumando todo aun que está filtrado, me sigue sumando las que están ocultas, y yo quiere que me sume solo las que están visibles... como lo puedo solucionar...

    ResponderEliminar
  25. Podrías usar SUMA.SI.CONJUNTO aplicando dos criterios (moneda y proveedor) para obtener los resultados sin necesidad de filtrar. La mejor solución es usar una tabla dinámica, que es mucho más eficiente.

    ResponderEliminar
  26. Y casi me olvido: SUBTOTALES sólo calcula las celdas visibles de manera que al filtrar obtendrías el resultado buscado (no entiendo por qué usás SUMA.SI).

    ResponderEliminar
  27. Hola!
    Es un catálogo de conceptos y hasta abajo viene cuanto es el costo de todo el proyecto, pero hay precios en USD, MX y EUROS, antes tenia las tres columnas, una para cada moneda y ahí si usaba SUBTOTALES, pero los usuarios se equivocaban, les es más fácil usar las dos columnas, una con el importe y una con la moneda, es por eso que SUMAR.SI me funciona, pero cuando queremos o necesitamos aplicar un filtro y saber cuanto es solo de ese filtro ya no funciona.
    Nunca he usado SUMA.SI.CONJUNTO, como lo puedo hacer con dos criterios? y... ¿si me funcionaria cuando filtro?
    sobre las tablas dinámicas, me encantan, pero no saben los usuarios aplicarlas y tampoco tienen el interés de aprenderlas.
    Gracias

    ResponderEliminar
  28. Georgina, SUMA.SI.CONJUNTO es SUMA.SI con más de un criterio (fijate en la ayuda en línea de Excel). En cuanto a las tablas dinámicas, si los usuarios saben usar Autofiltro no veo por qué tendrán dificultades con el filtro de una tabla dinámica. Además, si usan Excel 2010 o 2013, puedes usar segmentación de datos (slicers) para filtrar, que es mucho más intuitivo y estético que el campo de filtro de la tabla dinámica.

    ResponderEliminar
  29. ¿Qué usas, Excel 1930?
    No funciona ninguna fórmula en Office 2010.

    ResponderEliminar
  30. Hola Anónimo,
    bueno, le han funcionado a los 16000+ personas que han leído el post hasta hoy (menos 1) y a los 29 que dejaron comentarios. Y ahora, sin ironías, estás invitado a contactarme (fijate en enlace Ayuda, en la parte superior del blog), a ver si puedo ayudarte a encontrar tu error.
    Ah, la nota fue desarrollada con Excel 2010.

    ResponderEliminar
  31. Hola buenas tardes. La que sume solo las celdas visibles pero en las celdas que va a sumar contienen fórmula y la formula de subtotal no la suma, que hago?

    ResponderEliminar
  32. El subtotal puede sumar celdas que contienen fórmula?

    ResponderEliminar
  33. Necesito una fórmula que me sumé las celdas visibles que contienen fórmulas

    ResponderEliminar
  34. Víctor Manuel, SUBTOTALES suma también celdas que contienen fórmulas. No tengo claro como está planteado tu modelo por lo que sugiero que me mandes la consulta, con el cuaderno, por mail privado (fijate en el enlace Ayuda, an la parte superior del blog).

    ResponderEliminar
  35. Muy interesante. Si se quiere tomar en cuenta un sólo valor, sólo hay que sustituir la expresión ($B$4:$B$14<0) por ($B$4:$B$14="valor"), por poner un ejemplo de texto.

    Gracias!

    ResponderEliminar
  36. Buenas! Tengo problemon, a ver si puedes ayudarme ;D

    Tengo esta formula:

    =SUMAPRODUCTO(SUBTOTALES(103;DESREF(DATOS[POTENCIAL RESTANTE 2016];FILA(DATOS[POTENCIAL RESTANTE 2016])-MIN(FILA(DATOS[POTENCIAL RESTANTE 2016]));;1))*(DATOS[TIPO]="CA")*DATOS[POTENCIAL RESTANTE 2016])

    Lo relevante es que la parte de (DATOS[TIPO]="CA") establece la condición para que sume, y funciona. El problema es que necesito 2 condiciones.

    He intentado la formula anterior con (Y(DATOS[TIPO]="CA";DATOS[ID]=1)) pero no funciona...

    Help me!!! Me salvarias la vida ;D

    ResponderEliminar
  37. En general es muy difícil revisar una fórmula si no se le ve en el contexto del modelo. Por eso en el enlace Ayuda (en la parte superior del blog) puse las instrucciones de cómo hacerlas.
    Sin ver elcuaderno no puedo ayudarte.

    ResponderEliminar
  38. Buenas no consigo hacer que funciones tengo una tabla con filtros donde tengo una columna que pone importe. Aqui le introduzco valores negativos como positivos. como puedo hacer que solo me sume los negativos? y que tambien solo me sume las celdas visibles no todas ya que lo tengo filtrado. muchas gracias.

    ResponderEliminar
  39. HOLA JORGE NECESITO SABER COMO CONTAR CELDAS VISIBLES QUE HAN SIDO FILTRADAS.EN ELLAS HAY TEXTO QUE FORMULA DEBERIA APLICAR. REALICE VARIOS FILTROS PERO NECESITO SABER CUANTOS MASCULINOS TENGO CUANDO APLICO LA FUNCION CONTAR.SI ME SUMA TODAS LAS CELDAS Y CUANDO QUIERO APLICAR LA FUNCION SUBTOTALES ...=SUBTOTALES(102,G2:G30) EL RESULTADO QUE ME DA ES 0. QUE ESTOY HACIENDO MAL? AYUDA. POR FAVOR

    ResponderEliminar
  40. Sin ver el cuaderno no puedo decirte qué es lo que estás haciendo mal.

    ResponderEliminar
  41. Hola, Jorge.
    Tengo una columna con cantidades y otras con el costo. Puedo aplicar subtotales a la suma de producto sin insertar una nueva columna?
    Gracias

    ResponderEliminar
  42. Bueno, es lo que se muestra en el post, en la segunda parte. Las columnas auxiliares están sólo a los efectos de mostrar cómo funcionan las fórmulas.

    ResponderEliminar
  43. Excelente. Muy buen aporte definitivamente!

    ResponderEliminar
  44. Cuando me encuentro con un problema en excel, que sale de lo que es una simple formulación, aquí si o si encuentro la solución.
    Jorge, un capo en todo lo referente a excel, ademas de eso, enseña de una manera que lo complicado, quede fácil. Muchas gracias!!!

    ResponderEliminar