Uso de la función SUBTOTALES de MS Excel

domingo, abril 30, 2006

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: ,

18 comments:

Cavmi 01 mayo, 2006 18:20  
Este blog ha sido eliminado por un administrador de blog.
Jorge L. Dunkelman 09 mayo, 2006 22:46  
Este comentario ha sido eliminado por el autor.
Felipe Bianchi 08 abril, 2008 00:34  

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!

Jorge L. Dunkelman 08 abril, 2008 20:11  

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.

Mario Orellana 29 septiembre, 2008 07:13  

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.

Jorge L. Dunkelman 29 septiembre, 2008 16:06  

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.

Anónimo,  20 octubre, 2009 17:58  

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

Jorge L. Dunkelman 20 octubre, 2009 21:23  

Hola SL

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

Anónimo,  04 noviembre, 2009 11:46  

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

Jorge L. Dunkelman 04 noviembre, 2009 18:43  

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.

Anónimo,  05 noviembre, 2009 12:04  

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

Jorge L. Dunkelman 05 noviembre, 2009 14:49  

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

Josep,  16 noviembre, 2009 13:25  

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

carlos,  26 noviembre, 2009 15:53  

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.

Jorge L. Dunkelman 26 noviembre, 2009 17:21  

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.

Rubén Rielo 20 agosto, 2015 15:48  

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

Jorge Dunkelman 20 agosto, 2015 16:55  

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.

Rubén Rielo 21 agosto, 2015 00:55  

Muchas Gracias
Saludos cordiales

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP