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
archivo 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: SUBTOTALES, SUBTOTALS
Este comentario ha sido eliminado por el autor.
ResponderBorrarA ver si me puedes ayudar con esto. Quiero hacer un sumaproducto sólo de celdas visibles. Es decir:
ResponderBorrarA B
10 8
20 8
30 9
Quiero la suma de A*B de todas las filas visibles.
¿Cómo puedo hacer esto?
Gracias!
Hola Felipe
ResponderBorraruna 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.
HOLA,
ResponderBorrarQUIERO 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.
Hola Mario
ResponderBorraruna 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.
Hola Jorge
ResponderBorrarNunca 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
Hola SL
ResponderBorrargracias por el aporte. No creo que esté mencionado en ningún lado.
Hola,
ResponderBorrarMenudo 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
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.
ResponderBorrarOtra 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.
Gracias Jorge por la respuesta.
ResponderBorrarCreo que me decantaré por emplear Subtotales junto Sumaproducto.
Un saludo
Fijate en la nota que publiqué ayer y en el comentario de César.
ResponderBorrarAcabo 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...
ResponderBorrarGracias
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.
ResponderBorrarEfectivamente, SUBTOTALES no funciona horizontalmente. Una función UDF posible sería
ResponderBorrarFunction 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.
Estimado Jorge.
ResponderBorrar¿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
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.
ResponderBorrarMuchas Gracias
ResponderBorrarSaludos cordiales