domingo, abril 30, 2006

Uso de la función SUBTOTALES de MS Excel

Ya hemos hablado sobre el uso del comando Subtotales del menú Datos. Por lo general, esta es la forma más sencilla y segura de utilizar la función SUBTOTALES para consolidar datos.
Pero la función tiene algunas características que la tornan muy útil, en especial en combinación con el comando AUTOFILTRO.
La sintaxis de la función SUBTOTALES es la siguiente:

SUBTOTALES(núm_función;ref1,ref2,...)

Donde núm_función es el argumento que determina que tipo de operación efectuará SUBTOTALES

1 - PROMEDIO
2 - CONTAR
3 - CONTARA
4 - MAX
5 - MIN
6 - PRODUCTO
7 - DESVEST
8 - DESVESTP
9 - SUMA
10 - VAR
11 - VARP

Una característica importante de SUBTOTALES es que la función pasa por alto las filas ocultas. Esto es importante cuando sólo desea obtener el subtotal de los datos visibles que resulta de una lista filtrada.

En todos los ejemplos que aparecen en el
fnc_subtotal_sparchivo con ejemplos de la función SUBTOTALES, se puede apreciar la importancia de esta característica. Por ejemplo, si aplicamos AUTOFILTRO a una lista de ventas por productos, podemos obtener un total de cada producto al ocultar los restantes con el filtro usando la función SUMA (9). Lo mismo se puede hacer con PROMEDIO (1) y demás funciones.
Por ejemplo, si tenemos esta lista de edades de empleados por departamentos



Aplicando PROMEDIO (1), al auto filtrar la lista podemos ver la edad promedio de cada departamento:



Un uso interesante es con CONTARA (3). En el
archivo podemos ver un ejemplo donde cada vez que filtramos la lista, el número de orden se mantiene correlativo. La función utilizada en la celda A7 es =SUBTOTALES(3,$C$7:C7) que luego copiamos al resto del rango.
Prestar atención a la notación del rango en el segundo argumento de la función: el primer miembro del rango ($C$7) es una referencia absoluta, mientras que el segundo es una referencia relativa que cambia al copiar la función a lo largo del rango.




Categorías: Funciones&Formulas_

Technorati Tags: ,

17 comentarios:

  1. Este comentario ha sido eliminado por el autor.

    ResponderBorrar
  2. A ver si me puedes ayudar con esto. Quiero hacer un sumaproducto sólo de celdas visibles. Es decir:

    A B
    10 8
    20 8
    30 9

    Quiero la suma de A*B de todas las filas visibles.

    ¿Cómo puedo hacer esto?

    Gracias!

    ResponderBorrar
  3. Hola Felipe
    una posibilidad sería crear una columna auxiliar con la fórmula A*B.
    Luego, usando Autofiltro para ocultar las filas que quieres ocultar, puedes usar la función SUBTOTALES para sumar los valores visibles de la columna auxiliar.

    ResponderBorrar
  4. HOLA,
    QUIERO QUE EL RESULTADO DE UNA FUNCION SI, ME DE COMO RESULTADO EL OCULTAR O TRASLADO DE UN GRUPO DE CELDAS A OTRA HOJA, OJALA ME PUEDAS AYUDAR MUCHAS GRACIAS.

    ResponderBorrar
  5. Hola Mario

    una función da como resultado un valor, ya sea éste texto, númerico o lógico. Eso es todo lo que una función puede hacer. Para ocultar un resultado tal vez puedas usar formato condicional (depende por lo que entiendas por ocultar). Para trasladar un grupo de celdas a otra hoja el único camino, fuera de hacerlo manualmente, es usar una macro.

    ResponderBorrar
  6. Hola Jorge
    Nunca entendí por qué se podía poner un 9 o un 109 para la opción Suma (argumento 1) de la función Subtotales. Si el 9 es más cómodo que el 109, para qué existían las 2 posibilidades?
    Hasta que un día descubrí que Subtotales (con el 9) saca la suma de las celdas visibles en el contexto de un filtrado (autofiltro o filtro avanzado) pero si ocultamos manualmente las filas, esta suma de celdas visibles no se produce sino que sale el total de todas las celdas, visibles o no visibles.
    Decidí probar entonces con el 109 y ahí encontré la diferencia (no sé si habrá otra): el 109 sirve para sumar las celdas visibles con independencia de que la ocultación se haya producido por un filtrado o por una acción manual mientras que el 9 sólo actúa mediante un filtrado. Lo mismo ocurre con cualquiera de las otras funciones resumidas, obviamente.
    No sé si esto lo has comentado en alguna otra entrada, pero como no lo he visto hasta ahora, me pareció que podía ser un elemento importante a tener en cuenta en algún caso (raro) de ocultar filas manualmente y querer obtener la suma contextual.
    Un saludo,
    SL

    ResponderBorrar
  7. Hola SL

    gracias por el aporte. No creo que esté mencionado en ningún lado.

    ResponderBorrar
  8. Hola,
    Menudo Blog de Excel acabo de descubrir!!
    Como podria indicarle a la funcion Subtotales que me calcule el promedio sin tener en cuenta los ceros. Quiero decir, emplear la funcion PROMEDIO.SI en lugar de PROMEDIO dentro de la funcion Subtotales.
    Gracias

    ResponderBorrar
  9. Se me ocurren dos posibilidades. Una es creando una columna auxiliar que convierta los resultados 0 en vacíos (""). Luego podemos usar la columna auxiliar como argumento en SUBTOTALES.
    Otra posibilidad sería combinando SUBTOTALES con SUMAPRODUCTO para lograr el mismo efecto de la columna auxiliar pero sin necesidad de agregarla.
    Creo que publicaré una nota sobre el tema.

    ResponderBorrar
  10. Gracias Jorge por la respuesta.
    Creo que me decantaré por emplear Subtotales junto Sumaproducto.
    Un saludo

    ResponderBorrar
  11. Fijate en la nota que publiqué ayer y en el comentario de César.

    ResponderBorrar
  12. Acabo de ver el comentario, y teneis razón, es más fácil filtrarlo en otra columna. Asi que dejo a un lado lo de Sumaproducto que me ha mareado sola al verlo...

    Gracias

    ResponderBorrar
  13. Subtotales(109;...) funciona sólo cuando ocultas filas. ¿Alguien sabe si alguna otra función funciona con un listado en horizontal, cuando ocultas columnas? Creo que hay que usar VBA,pero no sé si hay alguna otra función. Se me ocurre alguna que informe si una celda está visible o no, para combinarla con un sumar.si, pero no encuentro que ninguna función dé esa información.

    ResponderBorrar
  14. Efectivamente, SUBTOTALES no funciona horizontalmente. Una función UDF posible sería

    Function sum_ocult_hor(rngSum As Range)
    Dim cell As Range, resTemp

    resTemp = 0

    For Each cell In rngSum
    If cell.EntireColumn.Hidden = False Then
    resTemp = resTemp + cell.Value
    End If
    Next cell

    sum_ocult_hor = resTemp

    End Function

    No la he probado, pero supongo que funcionará. El único detalle es que al ocultar filas hay que apretar F9 (recalcular) para actualizar el resultado.

    ResponderBorrar
  15. Estimado Jorge.
    ¿Cuál sería la diferencia de la opción 2 CONTAR y la 3 CONTARA, ya que aparentemente arrojan el mismo resultado, contando las celdas ocupadas en una columna filtrada.
    Muchas Gracias

    ResponderBorrar
  16. CONTAR cuenta sólo valores numéricos; CONTARA cuenta también valores de texto. Te sugiero que veas la descripción de las funciones en el asistente de funciones.

    ResponderBorrar
  17. Muchas Gracias
    Saludos cordiales

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.